Unexpected Behaviour – Query and a Table | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Unexpected Behaviour – Query and a Table

Hi All, I have a table (say Table1) with around 182,000 records and it has about 64 rows. Difference between below 2 queries is significant and difficult to understand why. SELECT TOP 134 * FROM Table1 ORDER BY Field1, Field2, Field3 – Above completes in about 3 seconds SELECT TOP 135 * FROM Table1 ORDER BY Field1, Field2, Field3 – Above takes about 4 mints & 15 secs. Field1 – varchar(25) Field2 – varchar(5) Field3 – varchar(5) The table is indexed on above 3 columns individually but there’s no compound index on all 3 columns. Avg row size returned is around 694. [Well, we don’t really run this query in our production environment but I noticed above behaviour when I was working with the above table and wonder what could possibly causing the above behaviour. Execution plans don’t provide any clue re this!] Any hints or tips that can be helpful to identify the problem is highly appreciated! Thanks! Ranil. Ranil
i suspect there is a change in the execution plan between the two cases,
click the "Display Estimated Execution Plan" button in Query Analyzer.
the first may show an index seek from the nonclustered index on F1 followed by loop joins to the index on F2 and F3?
the second plan is probably a table scan. SQL Server has a number of these type of circumstances where it switches execution plans at a certain point, but there is a drastic in cost. you might expect that the switch over points occurr when two different plans have the same cost, unfortunately the optimizer is seriously old,
Hi, Actually, the execution plan for both queries are identical. [both estimated & actual execution plans]
They both have clustered index scan followed by a sort opn operation. Any tips to diagnose this problem is highly appreciated! Ranil. Ranil
How about to defrag index and test again?
Luis Martin
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
is there is difference in disk activity between the two?
i am wondering if SQL Server for some reason does the 1st in memory, but spools to the temp table for the second, i am not sure why one and not the other, but check if there is disk activity on the disk holding the tempdb
Defrag index if it is there on the table and then run the query again.
Otherwise you can find out the fragmentation through DBCC SHOWCONTIG command.
One more reason may be th table is partioned. -Anup
Also if the query you pasted is exactly the one youre using, whittle your field list down to just those that you require, rather than using *.
Depending on circumstances, this alone may change the execution plan