SQL Server Performance

Two indexs on a column : 1billion rows

Discussion in 'Performance Tuning for DBAs' started by deepakontheweb, Apr 14, 2005.

  1. deepakontheweb New Member

    Hi all: I have a table which contains more then one billion records.. in it there is a integer column which is frequently being used for table join as well as in where conditions.<br /><br />is it good idea to create one more index on this column? will this can improve performance considering fact in mind that this column has less updates/insert/delete.<br /><br />before testing even..i need comments on it as this table is on production.<br /><br />Thanks <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Deepak Kumar
  2. FrankKalis Moderator

    Sorry, am I understanding you right?
    You have now a table with that much rows, join and search frequently on that column and don't have an index on it?

    --
    Frank Kalis
    SQL Server MVP
    http://www.insidesql.de
  3. satya Moderator

    In the terms of performance when using decision-support-system applications for which joins and grouping are frequently required. Create multiple nonclustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns.

    Determine the state of your indexes and understand whether that state should be changed. Start by identifying them using sp_help @tablename and sp_helpindex, and then evaluate the construction and location of your system's indexes and decide if its necessary to adjust their fill factors and padding. These modifications will impact your system.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. deepakontheweb New Member

    I mean, on that table's single column i have a clustered index already on the same RAID 10 arrey where physical data file is placed.

    i want to create one more non clustered index on this column on same arrey.. will that help me?



    Deepak Kumar
  5. Luis Martin Moderator

    If you were looking execution plan for some query and you think a non clustered index will help, find out how frequently that query is used.
    Is that your case?



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  6. derrickleggett New Member

    It won't help you to have two indexes on the exact same column. It actually might hurt, as you could get issues with recompilations and bad query plans.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  7. deepakontheweb New Member

    What if on same exact column.. i create one clustered index on one RAID arrey and second non-clustered index on another RAID arrey.. in that scenario is this possible at a given point of time two diff queries picks first and second respectively.<br /><br /><img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><br />Deepak Kumar
  8. FrankKalis Moderator

    Now I understand. I think it's a bit redundant to say the least, but yes, there are cases where SQL Server would favor one index over the other. This actually depends on your queries and your SARGs. For example, when you issue a SELECT keycolumn FROM table WHERE keycolumn=? SQL Server is likely to use the nonclustered index. When you issue a SELECT * FROM table WHERE keycolumn =? I would also expect SQL Server to use the nonclustered index. However, when most of your queries look something like SELECT a.*, b.* FROM table a JOIN table b ON a.keycolumn = b.keycolumn WHERE... it is very likely that SQL Server uses the clustered index. In either case it really doesn't matter on which physical location you've placed the index. This won't be considered when deciding which index to use.

    --
    Frank Kalis
    SQL Server MVP
    http://www.insidesql.de
  9. FrankKalis Moderator

  10. mmarovic Active Member

    quote:When you issue a SELECT * FROM table WHERE keycolumn =? I would also expect SQL Server to use the nonclustered index.
    I would expect clustered index to be used in that case.
  11. FrankKalis Moderator

    Try this out<br /><pre id="code"><font face="courier" size="2" id="code"><br />CREATE TABLE test<br />(<br /> c1 INT <br /> , c2 INT DEFAULT 0<br /> , c3 INT DEFAULT 1<br />)<br />CREATE CLUSTERED INDEX ci_test ON test(c1)<br />CREATE INDEX ni_test ON test(c1)<br />DECLARE @i INT<br />SET @i=0<br />WHILE @i &lt;= 2000<br />BEGIN<br /> INSERT INTO test(c1) VALUES(@i)<br /> SET @i = @i+1<br />END<br /><br />SELECT * FROM test WHERE c1 = 1000<br />DROP TABLE test<br /></font id="code"></pre id="code"><br />There is both a clustered and a nonclustered index on that column. When you run this, and examine the execution plan, you'll see something like this<br /><pre id="code"><font face="courier" size="2" id="code"><br />StmtText <br />--------------------------------------------------------------------------------------------------------------- <br /> |--Index Seek(OBJECT<img src='/community/emoticons/emotion-6.gif' alt=':(' />[Northwind].[dbo].[test].[ni_test]), SEEK<img src='/community/emoticons/emotion-6.gif' alt=':(' />[test].[c1]=Convert([@1])) ORDERED FORWARD)<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br /><br /><br /><br />--<br />Frank Kalis<br />SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  12. FrankKalis Moderator

    Forgot to add: The nonclustered index is highly selective in this case. that's why SQL Server chooses it. I've also found the explanation in my link above very helpful.

    --
    Frank Kalis
    SQL Server MVP
    http://www.insidesql.de
  13. FrankKalis Moderator

    Hm, now this is strange. I don't get consistent plans here. Sometimes SQL Server uses the clustered index, sometimes not [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />--<br />Frank Kalis<br />SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  14. FrankKalis Moderator

    OKay, I stand corrected here. Cleared cache and the rest and now do get always clustered index seeks. Sorry, if this has lead to confusion!

    --
    Frank Kalis
    SQL Server MVP
    http://www.insidesql.de
  15. mmarovic Active Member

    No problem with me, I standed corrected here more then once. Although I didn't like it at the moment, discussion (and correction) on this board helped me improving my knowledge and skills.
  16. deepakontheweb New Member

    Well, Suppose i have only one index and if i keep on defragmenting that index on said column.. using DBCC INDEXDEFRAG and DBCC INDEXDEFRAG on daily/weekly basis..

    Then can one index be sufficient? or having 2 indexs will help more.

    Deepak Kumar
  17. mmarovic Active Member

    I don't think another index will help, because I don't think query optimizer takes into account index fragmentation.

Share This Page