September 14, 2010 at 3:03 pm
I have two tables with same data and same structure. Query on 1 runs in 1 sec and on 2 in 55 secs.Only difference i see is logical scan fragmentation is 0.91 for slow running and 0.01 for fast running. Mentioned below is the data.
DBCC SHOWCONTIG scanning 'Tabkle 1' table...
Table: 'Table 1' (101575400); index ID: 1, database ID: 32
TABLE level scan performed.
- Pages Scanned................................: 1657389
- Extents Scanned..............................: 207174
- Extent Switches..............................: 207173
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [207174:207174]
- Logical Scan Fragmentation ..................: 0.01%
- Extent Scan Fragmentation ...................: 0.13%
- Avg. Bytes Free per Page.....................: 731.2
- Avg. Page Density (full).....................: 90.97%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'Table2' table...
Table: 'Table 2' (2112612830); index ID: 1, database ID: 34
TABLE level scan performed.
- Pages Scanned................................: 1522475
- Extents Scanned..............................: 191237
- Extent Switches..............................: 191281
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.49% [190310:191282]
- Logical Scan Fragmentation ..................: 0.91%
- Extent Scan Fragmentation ...................: 0.37%
- Avg. Bytes Free per Page.....................: 78.6
- Avg. Page Density (full).....................: 99.03%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
thanks
September 14, 2010 at 3:05 pm
That's less than 1% difference, so no, that won't really affect it.
It sounds more like Table 2 is missing indexes or statistics. Take a look at the execution plans for each query and see where they differ.
September 14, 2010 at 3:21 pm
the only difference i see is that the non-clustered indexes is set to fill factor 90% and is checked the option "Use Index'. I am trying now on the slow one to see if that helps..thanks
September 15, 2010 at 1:59 am
Derrick Smith (9/14/2010)
It sounds more like Table 2 is missing indexes or statistics.
How did you get this from above first post ( from DBCC results)?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 15, 2010 at 7:43 am
Bhuvnesh (9/15/2010)
Derrick Smith (9/14/2010)
It sounds more like Table 2 is missing indexes or statistics.How did you get this from above first post ( from DBCC results)?
Because there aren't many logical reasons why a query running on 2 identical, unfragmented tables would perform so differently unless it was using extremely old statistics, missing indexes, one table had activity that blocked/slowed down a query, etc....statistics/indexes are just the most likely culprits here.
Need more info from the OP though about table definitions, and the query he's executing.
September 15, 2010 at 8:13 am
selecting the option "Use Index" has helped. I am not sure why did it help. I have another post with more detail results. Please check http://www.sqlservercentral.com/Forums/Topic980901-146-1.aspx.
Thanks
September 15, 2010 at 9:23 pm
Derrick Smith (9/15/2010)
statistics/indexes are just the most likely culprits here.
But the above DBCC results show that table is not fragmented and statistics are also updated ( from scan density value and logical fragmentation %)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 15, 2010 at 9:29 pm
Bhuvnesh (9/15/2010)
Derrick Smith (9/15/2010)
statistics/indexes are just the most likely culprits here.But the above DBCC results show that table is not fragmented and statistics are also updated ( from scan density value and logical fragmentation %)
It shows that the indexes are not fragmented.
It does not show which indexes exist, and whether or not they are enabled.
September 15, 2010 at 9:33 pm
Derrick Smith (9/15/2010)
It shows that the indexes are not fragmented.
I am sorry , but which value showed it ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 15, 2010 at 9:38 pm
Bhuvnesh (9/15/2010)
Derrick Smith (9/15/2010)
It shows that the indexes are not fragmented.I am sorry , but which value showed it ?
Well DBCC SHOWCONTIG isn't really the best tool to use any more, but logical fragmentation shows that.
September 15, 2010 at 10:03 pm
Derrick Smith (9/15/2010)
Bhuvnesh (9/15/2010)
Derrick Smith (9/15/2010)
It shows that the indexes are not fragmented.I am sorry , but which value showed it ?
Well DBCC SHOWCONTIG isn't really the best tool to use any more, but logical fragmentation shows that.
FROm msdn link http://msdn.microsoft.com/en-us/library/ms175008.aspx
Logical Scan Fragmentation is
Percentage of out-of-order pages returned from scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.
please correct me if i am wrong
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 15, 2010 at 10:27 pm
Bhuvnesh (9/15/2010)
Derrick Smith (9/15/2010)
Bhuvnesh (9/15/2010)
Derrick Smith (9/15/2010)
It shows that the indexes are not fragmented.I am sorry , but which value showed it ?
Well DBCC SHOWCONTIG isn't really the best tool to use any more, but logical fragmentation shows that.
FROm msdn link http://msdn.microsoft.com/en-us/library/ms175008.aspx
Logical Scan Fragmentation is
Percentage of out-of-order pages returned from scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.
please correct me if i am wrong
I'm not exactly sure if you're disputing that logical fragmentation shows the fragmentation of an index, or something else..like that line says, the only time it's not relevant is on a heap table.
September 15, 2010 at 10:30 pm
Derrick Smith (9/15/2010)
the only time it's not relevant is on a heap table.
is thats the case with Heap table then i got your point...thanks
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 16, 2010 at 6:58 am
Are the two tables in the same filegroup? Apart from the possibility that the two are on different disks (or arrays) and one is much slower or busier than the other, I agree that it seems that there may be a missing index or stat.
One thing though that the OP didn't say, but I'm assuming is that these results are consistent across multiple runs.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
September 16, 2010 at 7:26 am
DCPeterson (9/16/2010)
Are the two tables in the same filegroup? Apart from the possibility that the two are on different disks (or arrays) and one is much slower or busier than the other, I agree that it seems that there may be a missing index or stat.One thing though that the OP didn't say, but I'm assuming is that these results are consistent across multiple runs.
The OP had posted this question twice and linked to it earlier in this thread. The issue in fact was that an index was disabled.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply