March 30, 2011 at 8:36 am
I have an index which when slightly fragmented causes my query to take about 8 minutes to run but after rebuilding the index the same query takes about 10 seconds. The query accesses two tables, the details of their indexes (using sys.dm_db_index_physical_stats) are as follows:
Table A:
1CLUSTERED INDEX1.2427777172135618346
Table B:
1CLUSTERED INDEX03151
So in this state the query takes 8 minutes. After rebuilding the index on Table A the details are:
Table A:
1CLUSTERED INDEX0.027252411838447718347
If I re-run the same query in now executes within 10 seconds. ????
March 30, 2011 at 8:51 am
Stale statistics perhaps? Rebuilding the index would update them.
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
March 30, 2011 at 9:15 am
Just updated the statistics manually but the query still took an age to run, rebuilt the index straight after and it returned within the 10 seconds again.
March 30, 2011 at 10:08 am
Is it the exact same query with the same parameters?
Are you getting the same execution plan each time?
What are the results of SET STATISTICS IO ON? Are you getting physical reads the first time and logical reads the next?
Is there anything else going on on the server that could be causing blocking one time and not the next?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 30, 2011 at 2:13 pm
Jack Corbett (3/30/2011)
Is it the exact same query with the same parameters?Are you getting the same execution plan each time?
What are the results of SET STATISTICS IO ON? Are you getting physical reads the first time and logical reads the next?
Is there anything else going on on the server that could be causing blocking one time and not the next?
One of the parameters is different between each execution. The parameter is based upon one of the columns that forms the primary key of Table A.
The execution plans are different; when the index is fragmented the execution plan shows lots of "Nested Loops" whereas when the index is rebuilt the execution plan shows no "Nested Loops". There are possibly other differences but that was the most notable.
Unfortunately I cannot give you the output of SET STATISTICS IO ON because the query takes much longer than my earlier quote of 8 minutes, the last time I ran the query after the index was fragmented I got to 36 minutes and then canceled it.
A bit of background info on what is causing the index fragmentation; one of the operations we have is to copy a subset of the data in Table A and paste it back into Table A but with a different ID. When I say paste I mean as in an INSERT INTO .....SELECT FROM Table A etc.
March 31, 2011 at 9:18 am
I think I've found the problem. The index on Table A did not have the "Automatically Recompute Statistics" option ticked. I ticked this and now it seems to return the query results much faster.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply