locked
Need suggestions on creating Indexes RRS feed

  • Question

  • Hi All,

      We have built our application on top of a transactional database. There are no primary key or foriegn key relationship at the database level. Everything is handled at the cube level. Now the records are increasing day by day and hence we are finding it difficult to run the queries. Now we have decided to create indexes .

    1) Can i create a clustered index without a primary key in a table.
    2) Can i have non clustered index created without having a clustered index in a table.

    I have gone thru some articles which said that as we enable a primary key on a table by default a clustered index is created. but i dont want to enable any primary keys at the backend.

    Please suggest me how to spped up my application.

    Regards,
    ram
    Friday, December 18, 2009 10:18 AM

Answers


  • 1) Can i create a clustered index without a primary key in a table.
    2) Can i have non clustered index created without having a clustered index in a table.

    3) I have gone thru some articles which said that as we enable a primary key on a table by default a clustered index is created. but i dont want to enable any primary keys at the backend.


    1. You can use a UNIQUE KEY constraint with non-clustered unique index Primary Key, demo follows.

    -- SQL Server 2008 nonclustered primary key demo
    USE tempdb;
    CREATE TABLE Product ( 
    ProductID int identity(1,1) PRIMARY KEY NONCLUSTERED ,
    ProductName varchar(32) UNIQUE,
    ProductDesc varchar(64),
    ListPrice smallmoney,
    Color varchar(16),
    ModifiedDate date default (getdate()));
    GO
    CREATE CLUSTERED INDEX idxDescClust on Product(ProductDesc);
    GO
    INSERT Product (ProductName, ProductDesc, ListPrice, Color)
    SELECT ProductNumber, Name, ListPrice, Color
    FROM AdventureWorks2008.Production.Product
    ORDER BY ProductID
    GO
    SELECT TOP(5) *
    FROM Product ORDER BY NEWID()
    GO
    /*
    ProductID	ProductName	ProductDesc	ListPrice	Color	ModifiedDate
    466	BK-T18Y-44	Touring-3000 Yellow, 44	742.35	Yellow	2009-12-23
    482	BK-R19B-58	Road-750 Black, 58	539.99	Black	2009-12-23
    426	TT-M928	Mountain Tire Tube	4.99	NULL	2009-12-23
    262	BK-R68R-48	Road-450 Red, 48	1457.99	Red	2009-12-23
    92	LI-1200	Internal Lock Washer 4	0.00	NULL	2009-12-23
    */
    DROP TABLE Product
    

    2. You can, but usually you want to take advantage of a clustered index to speed up query(s).

    3. See the demo above. The default is clustered, you need to select (in GUI) nonclustered or use "nonclustered" modifier in script.


    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by Zongqing Li Thursday, December 24, 2009 8:47 AM
    Wednesday, December 23, 2009 4:07 PM

All replies

  • Yes and yes.

    However, I strongly encoourage you to define primary keys and foreign keys. I'm sure others will chime in here, but I can't stress enough what a mess we usually end up with for such databases.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Friday, December 18, 2009 10:25 AM
  • In SQL Server Management Studio click Tools menu then select Database Engine Tuning Advisor and follow dialogs. it will suggest you good indexes.

    With best regards, Yasser Zamani
    Friday, December 18, 2009 10:29 AM
  • Hi All,

      thanks a lot for the replies.

    I ran the database tuning advisor. but the result i got was
    Estimated Improvement  0%
    Under Index recommendation : nothing was shown.

    I ran the DTA as follows

    Using SQL profiler i captured my Fact query  for 10 min and this became my source file for my DTA.
    Using this file i ran the DTA.

    but i got the above results.


    Please let me know have i missed something.

    Regards,
    Ram
    Friday, December 18, 2009 12:29 PM
  • Hi Ram,

    1) Open Profiler and give the name for the trace file
    2) Select save to file and provide the name
    4) Click Run
    5) Open SSMS, execute the query
    6) Once the query executed successfully, switch to Profiler and stop the profiler.
    7) Close the SSMS
    8) Open DTA and specify the trace file or workload file and select "show all events" options
    9) Expand Security and Audit, Select all the check boxes(No need but I forget the exact event name)
    10) Go for the next tab and make sure Clustered and Non Clustered check boxes are selected
    11) Back to page and execute...

    I'm sure now u will see the recommendations...

    Initially I too faced the same problem....

    Let me know if u struck at any point....



    Friday, December 18, 2009 2:28 PM
  • Table should have primary key
    2. Table should have minimum of one clustered index
    3. Table should have appropriate amount of non-clustered index
    4. Non-clustered index should be created on columns of table based on query which is running
    5. Do not to use Views or replace views with original source table
    6. Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
    7. Remove any adhoc queries and use Stored Procedure instead
    8. Check if there is atleast 30% HHD is empty – it improves the performance a bit
    9. If possible move the logic of UDF to SP as well
    10. Remove * from SELECT and use columns which are only necessary in code
    11. Remove any unnecessary joins from table
    12. If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)

    -Vijay PMP, MCTS http://www.my-sql-share.blogspot.com
    Friday, December 18, 2009 6:52 PM
  • I agree with all that database should have referential integrity defined, but I too am trying to improve performance on a poorly designed system.  You can not always control that. 

    Clustered -- remember that for the cluster to help it is the statement that you are grabbing in ranges.  also when you apply this all the rows of the table are moved into that order, an expensive operation (although not unnessarily).  Don't pick a nonsequential cluster index or the ETL load or insert statements will have to insert the rows into the data pages according to the index and you will get fragmentation (lastname for example -- every new customer will be inserted into the data pages and if there is not enough room on the data page it will perform page split operation). 

    Non-clustered -- I know the tuning advisor is good, but sometimes we are better ;)  Look at your WHERE clauses.  Consider an index on columns in the where clause.  If their are a different set of queries that run at months end -- I would create the indexes on those WHERE clause columns, but disable them until the 25th-ish of the month and disable again on the 5th-ish...

    Friday, December 18, 2009 7:03 PM

  • 1) Can i create a clustered index without a primary key in a table.
    2) Can i have non clustered index created without having a clustered index in a table.

    3) I have gone thru some articles which said that as we enable a primary key on a table by default a clustered index is created. but i dont want to enable any primary keys at the backend.


    1. You can use a UNIQUE KEY constraint with non-clustered unique index Primary Key, demo follows.

    -- SQL Server 2008 nonclustered primary key demo
    USE tempdb;
    CREATE TABLE Product ( 
    ProductID int identity(1,1) PRIMARY KEY NONCLUSTERED ,
    ProductName varchar(32) UNIQUE,
    ProductDesc varchar(64),
    ListPrice smallmoney,
    Color varchar(16),
    ModifiedDate date default (getdate()));
    GO
    CREATE CLUSTERED INDEX idxDescClust on Product(ProductDesc);
    GO
    INSERT Product (ProductName, ProductDesc, ListPrice, Color)
    SELECT ProductNumber, Name, ListPrice, Color
    FROM AdventureWorks2008.Production.Product
    ORDER BY ProductID
    GO
    SELECT TOP(5) *
    FROM Product ORDER BY NEWID()
    GO
    /*
    ProductID	ProductName	ProductDesc	ListPrice	Color	ModifiedDate
    466	BK-T18Y-44	Touring-3000 Yellow, 44	742.35	Yellow	2009-12-23
    482	BK-R19B-58	Road-750 Black, 58	539.99	Black	2009-12-23
    426	TT-M928	Mountain Tire Tube	4.99	NULL	2009-12-23
    262	BK-R68R-48	Road-450 Red, 48	1457.99	Red	2009-12-23
    92	LI-1200	Internal Lock Washer 4	0.00	NULL	2009-12-23
    */
    DROP TABLE Product
    

    2. You can, but usually you want to take advantage of a clustered index to speed up query(s).

    3. See the demo above. The default is clustered, you need to select (in GUI) nonclustered or use "nonclustered" modifier in script.


    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by Zongqing Li Thursday, December 24, 2009 8:47 AM
    Wednesday, December 23, 2009 4:07 PM