Post reply

SQL Server Indexing

  • January 14, 2010 at 11:56 pm

    #215432

    Hi to all,

    I have a table in which 20 Million rows are stored.

    The table contains a primary key int and 2 fields RecordId=int and CreationDate=smalldatetime. RecordId and CreationDate are indexed

    A stored procedure selects rows in the following manner:

    where RecordId=@RecordId and CreationDate=@CreationDate.

    The query plan told me that first the CreationDate is used and then a keylookup against the recordId is made. If I use a queyr hint to force the recordId index to use first, the query is much faster.

    Now my question. Can someone tell me if an covered index (RecordId,CreationDate) increase the query performance ?

    Thanks in advance

    Flecki

  • January 15, 2010 at 1:33 am

    #1104085

    scziege (1/14/2010)

    Can someone tell me if an covered index (RecordId,CreationDate) increase the query performance ?

    Yes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 15, 2010 at 3:48 am

    #1104109

    Is it possible to check the improvement of an covered index without the creation of this index ?

  • January 15, 2010 at 5:10 am

    #1104126

    You mean test how much it will help without creating it? No.

    Create the index on a test system, test the queries, see if the new index improves them, if so create it on the production server.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply