Primary Key Vs Clustered Index: Performance Tuning

  • I've a performance related issue.

    I've one big table A which is been used in one query having join with another big table B. Table A has no Primary key but a clustered index defined on four columns (composite) and two more nonclustered indexes. Table B also has no primary key but a clustered index with five columns (again composite) and three non clustered indexes. All of the indexes are on more than one column.

    While running the query, it taking too much time and execution plan shows 23% of sort (execution plan shows its in one of the case statements with "in (select ")) on one column of table A which is part of clustered index. How should I resolve it ? Secondly, what is the significance of PK and clustered index separately ? Table A comprises of 35% of index seek and Table B shows 18% of index seek.

    The query comprises of joins between these two tables and inserting in another table. Lots of columns in select statement and also few case statements with "exists (select" in them.

  • Can you post the actual execution plan?

    You may also take a look at this article on how to post performace problems:

    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    -- Gianluca Sartori

  • Not possible to send execution plan here. I created two new tables form above ones without any indexes and tried after replacing those into the query for a common set of parameters. Created primary clustered key constraint on both tables (with three columns on each table). Thereafter created one non clustered index for the three columns showing 18% in Hash Join in Table A (new one). Still taking approx same time (Just 20 seconds efficiency). Kindly suggest.

  • Without seeing the exec plan and the table/index definitions, it's really hard to answer these kind of questions. You're just not posting enough information for us to be able to do more than wildly guess.

    Why no exec plan?

    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
  • Without a query plan to look at, I would end up with a useless suggestion.

    I'm sure you can live without it.

    -- Gianluca Sartori

  • For more information, I took help of sys.dm_db_missing_index_details and other DMVs to check out for missin index details. Table A and B both contains around 64 Lac records and their join result shows around 25k records. Select statement contains few case statements and overall query is taking 3 and half minutes to complete. Table A clustered index seek shows 66% participation.

  • Here's the execution plan.....

  • Table and index definitions please?

    I'm guessing you didn't read the article that Gianluca posted.

    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
  • Here they are...

  • Any suggestions ???? Eagerly waiting your advice... Already tried and tested lots of things but query taking around 3:15 mins at best....

  • I think you should try to filter as much as possible the input with an appropriate index, and then join the correlated table.

    Given that usually the primary key is the best possible clustered index, I would work on the nonclustered index side.

    However, I see that you're selecting out a lot of columns from table "A", so that the optimizer could decide to avoid CI lookups and use the clustered index directly. I don't have your data volumes in place, I can't say without testing.

    However, based on your query, I would try to see if a couple of NC Indexes can help.

    I would create these two:

    -- NC Index on Table A

    -- KEY COLUMNS:

    A.ProfileName

    A.DomainName

    A.TaskId

    A.BookName

    A.RunVersion

    -- INCLUDE COLUMNS:

    A.ProductType

    A.PayReceive

    -- NC Index on Table B

    -- KEY COLUMNS:

    B.RunVersion

    B.COBDate

    --INCLUDE COLUMNS:

    B.Side

    B.Currency

    B.PaynonPVaccrual

    B.Notional

    B.RecnonPVaccrual

    B.Mtm3

    Also, you could transform the expression on the COBDate as follows:

    --AND CONVERT(VARCHAR, B.COBDate, 112) = '20101116'

    AND B.COBDate = CONVERT(datetime, '20101116',112)

    Hope this helps. This is the best I can do from here.

    Gianluca

    -- Gianluca Sartori

  • Thanks mate. Seems promising. Will try and let you know. However, the sizes of both tables I've already mentioned in earlier posts. Both Table A and B contains around 6.5 Million records.

  • ankit.dhasmana (12/22/2010)


    Both Table A and B contains around 6.5 Million records.

    I see. However I have no clue of how the optimizer will decide to work with the new indexes, beacause I'm working on empty tables.

    -- Gianluca Sartori

  • I'll check and surely let you know the outcome....

Viewing 14 posts - 1 through 13 (of 13 total)

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