August 16, 2011 at 10:58 am
Hi All
If it needs to be in other forum, please let me know.
I have situation that I have non-clustered index (Prod_ID,Cat_id), I ran estimated execution plan on Stage (10.50.1617) and Test (10.50.1617).
On Stage it show Missing Index (Impact 91.6327) and it shows I need to set up index for Prod_ID, time to run this script 10 hrs (sorry, I can not show the script, it is just update). On Test, same script, show same estimated execution plan, but does not show any Missing Index, run only 10 minutes.
Data is Stage is 10% more than in Test.
Both show exclamation mark on Clustered Index Scan on the same table, both show "warning column with no statistics: product_id"
Index(Prod_id,Cat_id) is on both Server. Stats was updated on both server.
The question is, why it show missing index in Stage but not in Test?
Thank you in advance.
August 16, 2011 at 12:11 pm
August 16, 2011 at 12:33 pm
Thank you for your reply.
Yes it is obviously that it has missing index.
But why it is not show up in Test and it is show up in Stage?
It has composite index((Prod_ID,Cat_id) on both server.
This company is furious about their information so I can not post the actual execution plan (I am so sorry).
I believe something from SQL Engine but I do not know why it goes different.
Thank again.
August 16, 2011 at 1:17 pm
"Missing indexes" for execution plans are pulled from a DMV, sys.dm_db_missing_index_details, which is the culmination of the stored information SQL Server is keeping about the queries the optimizer receives from applications and the like.
The recommendations by the Missing Index Feature will be different between two instances so long as the load is different and the servers see different queries and numbers of them.
Note: Those missing index statistics are also reset each time the instance is restarted, so if you want to keep a history, you're going to need to export the results somewhere.
HtH,
-Patrick
Patrick Purviance, MCDBA
August 16, 2011 at 1:21 pm
August 16, 2011 at 1:30 pm
Yes, I have same index in both Test and Stage and they are up to date.
August 16, 2011 at 2:30 pm
Is the execution plan the same between the STG and Test? Hopefully they both are leveraging that same index?
Any idea on what the fragementation levels of the affected indexes in STG are?
August 16, 2011 at 2:59 pm
I use DBCC Showcontig(tablename, indexname)
Stage
Composite Index(Prod_id, Cat_id)
- Pages Scanned................................: 4152
- Extents Scanned..............................: 524
- Extent Switches..............................: 4151
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 12.50% [519:4152]
- Logical Scan Fragmentation ..................: 99.35%
- Extent Scan Fragmentation ...................: 47.90%
- Avg. Bytes Free per Page.....................: 3539.8
- Avg. Page Density (full).....................: 56.27%
Test
Composite Index(Prod_id, Cat_id)
- Pages Scanned................................: 2315
- Extents Scanned..............................: 297
- Extent Switches..............................: 317
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 91.19% [290:318]
- Logical Scan Fragmentation ..................: 1.81%
- Extent Scan Fragmentation ...................: 31.31%
- Avg. Bytes Free per Page.....................: 67.7
- Avg. Page Density (full).....................: 99.16%
You are correct it is totally different.
Any suggestion?
August 16, 2011 at 3:21 pm
It seems like the index in stage is totally fragmented. This leads the query optimizer to the conclusion that the index (being good enough in test) is not good enough in stage and might be replaced with another index as suggested. Based on what you've posted it seems like the query itself only need an index on the Prod_ID column.
The index with (Prod_ID,Cat_id) is not as dense as it would be if only be based on Prod_ID, but it is considered being "good enough" if the fragmentation level is low.
one possible solution: rebuild the index.
One possible scenario for a heavy index fragmentation is a process that insert a large amount of data followed by a delete of a large amount, whereas the data inserted/deleted are in "random order" as far as the index is defined. The insert process would usually cause a page split (and increase fragmentation) and the delete would remove rows from the index and increase fragmentation even more. (of course, this is just one possible scenario but the "stage" term implies something...).
You might also want to search for "index fragmentation script" to find some good solutions for index maintenance.
Edit: I would not add another index since it would be heavily fragmented soon, too. I'd prefer the index maintenance approach.
August 16, 2011 at 3:26 pm
Thank you for all.
Problem solved.
Just reindex it.
Thanks again.
August 16, 2011 at 3:30 pm
Well, that could definitely have an effect on performance. A lot of extra I/O is going to occur due to the data being spread all over the place and random reads galore.
How big is this index?
Either way, if you're running SQL Server 2005/2008 Enterprise, Rebuild the index online:
ALTER INDEX [indexname] ON [dbname].[dbo].[tablename] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
If you're on Standard (or less?), find a quiet time and rebuild offline (lots of locking -> blocking) or reorganize (virtually no blocking, but not as good of an end result):
ALTER INDEX [indexname] ON [dbname].[dbo].[tablename] REORGANIZE
or
ALTER INDEX [indexname] ON [dbname].[dbo].[tablename] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = OFF)
Sorry about the big code boxes...can't figure out how to keep it from expanding when I go over a single line :unsure:
As a plug for another great resource, I would recommend reading up on and leveraging the defragmentation solution from Ola Hallengren (http://ola.hallengren.com) to maintain those indexes on a fairly frequent basis.
-Patrick
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply