SQL Server Performance

Clustered and Non-clustered Index

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by plugsharma, Dec 17, 2008.

  1. plugsharma New Member

    <p>&nbsp;<br>Hi,<br><br>I have one table having more than 3 millions rows with following columns:<br><br>[SITE_ID]<br><br>[USER_NAME]<br><br>[ACTIVITY_NAME]<br><br>[PAGE_TYPE]<br><br>[ACTION]<br><br>[DOC_ID]<br><br>[FROM_PAGE]<br><br>[TO_PAGE]<br><br>[ORG_NAME]<br><br>[LOG_DATE_TIME]<br><br>[SESSION_ID]<br><br>[IP]<br><br>[MACHINE_ID]<br><br><br><br>I am using 8 different queries, all thru stored procedure, to fetch data.<br><br>SITE_ID, USER_NAME, ORG_NAME and ACTION are in all queries WHERE clause.<br><br>I have created SITE_ID as non-unique clustered index and USER_NAME, ORG_NAME and ACTION as non-unique non-clustered index.<br><br>Problem is that data fetching without indexs is faster than when creating idex.<br><br>Is there any problem in index columns.<br><br>Can you please suggest me a better index plan.<br><br>I have attached the queries / column table image file. <br><br>Images file described columns called details</p><p><br>&lt;br /&gt;&lt;img src='<img src="http://xs434.xs.to/xs434/08513/queries-columns997.jpg" mce_src="http://xs434.xs.to/xs434/08513/queries-columns997.jpg" width="941" height="301">'&gt;<br>(http://xs434.xs.to/xs434/08513/queries-columns997.jpg or <br>http://xs.to/xs.php?h=xs434&amp;d=08513&amp;f=queries-columns997.jpg)<br><br><br>Please help..<br><br><br>Thanks<br><br>Sharma</p>
  2. Adriaan New Member

    Does the table have a unique key?
    If it does, then does this have a PK constraint?
    If there is no unique key to your table then add an INT column with identity, just so you do have a unique key. Make this (1) the PK, and (2) the clustered index.
  3. plugsharma New Member

    Thanks for reply. No unique key for this table. Its a logging table. Only insert and select task.
    If i create a unique clustered index column will it enhance the speed. How about non-clustered index of frequent called columns.
    Will composite index work?
  4. Adriaan New Member

    You need a PK on large tables, even if they are heaps.
    Only INSERTs and SELECTs? Then identity column is perfect candidate for clustered index. All the other indexes will benefit.
  5. FrankKalis Moderator

    Can you please post 1 or 2 queries that you run against this table? Would make sense if this could be the most frequently executed ones or the most crucial ones.
    Guessing what the indices should be without seeing how you query the data is like a shot in the dark to me. [:)]

Share This Page