SQL Server Performance

What is taking so long?

Discussion in 'General Developer Questions' started by brimba, Jan 25, 2008.

  1. brimba New Member

    I have a query that takes 1,5second to execute, but only 150ms of CPU. The query is quite simple, just one where statement against a clustered index.
    SQL Server Execution Times:
    CPU time = 156 ms, elapsed time = 1595 ms.

    SELECT column1, column3, column4-10 FROM table WHERE column2 IN (37, 41, 43, 45, 49, 53, 55) ORDER BY column3 DESC
    |--Sort(TOP 1000, ORDER BY:(.[LastActivityDate] DESC))
    |--Clustered Index Seek(OBJECT:([MP].[dbo].[__searchtest].[cix___searchtest_] AS ), SEEK:(.[searchparamid]=37 OR .[searchparamid]=41 OR .[searchparamid]=43 OR .[searchparamid]=45 OR .[searchparamid]=49 OR .[searchparamid]=53 OR .[searchparamid]=55 OR .[searchparamid]=59) ORDERED FORWARD)

    I have tried to rewrite the query to an INNER JOIN instead.

    |--Sort(TOP 1000, ORDER BY:(.[LastActivityDate] DESC))
    |--Nested Loops(Inner Join, OUTER REFERENCES:([spal].[number]))
    |--Index Seek(OBJECT:([MP].[dbo].[__search_parameters_lookup].[IX___search_parameters_lookup] AS [spal]), SEEK:([spal].[hash]=-1726604993) ORDERED FORWARD)
    |--Clustered Index Seek(OBJECT:([MP].[dbo].[__searchtest].[cix___searchtest_] AS ), SEEK:(.[searchparamid]=[spal].[number]) ORDERED FORWARD)
    but the query still takes 1,5 seconds.

    It spends 59% (according to execution plan) of sorting. 14% for the index seek of the __search_parameters_lookup table and then 24% of a clustered index seek of the __searchtest table.
    How come it only uses that small of CPU but it still takes 1,5 seconds? It seems to be reading from memory as well so it shouldnt be an IO-problem?
    The index I have on the table is a clustered index on (column 2).

    Any ideas of how I can improve this?

  2. Luis Martin Moderator

    Try with one non clustered index on column3.
  3. brimba New Member

    No difference. Same execution plan as well.
  4. Luis Martin Moderator

    Did you try with ITW. Better with DTA if you have 2005 tools.
  5. brimba New Member

    Yep. Im using the 2005-tools but the DTA recommends the clustered index that I described before.

  6. Luis Martin Moderator

    So, wait for one developer expert member (I'm not) because the only way is, may be, write a different code.
  7. brimba New Member

    If I remove some columns from the selectlist the query will execute alot faster.
    SQL Server Execution Times:
    CPU time = 32 ms, elapsed time = 32 ms.
    So booth the CPU time goes down, but also the total elapsed time appears to be more normal.
    So it seems to be a network latency problem or some kind of IO/transfer trouble. But I have tried to normalize the table and when I do that the query executes faster (~400 ms), but it booth takes 400ms and the elapsed time is also 400ms.
    Booth the normalized and the denormalized solutions are returning identical data, so I cannot understand why the denormalized takes 150ms CPU but 1500ms total time?

Share This Page