Inconsistent Index Stats Results?

Last Post 22 Dec 2010 06:33 AM by gunneyk. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
rerichards
New Member
New Member

--
21 Dec 2010 08:07 AM
We are running SQL Server 2008 64 bit Enterprise, Service Pack 1, Cumulative 7, Version 10.0.2766.
 
We do about 10 million transactions a day over16 servers and 900 databases, with each database having about 400 tables and about 600 stored procedures.

I run a stored procedure daily to gather index analaysis statistics and recently noticed that sys.dm_db_missing_index_group_stats has no records in about 700 of the 900 databases, and these servers have not had a service restart for over 3 months, some have been up for over 6 months.

For instance when I execute the following two statements on a database:
SELECT * FROM sys.dm_db_missing_index_group_stats --Returns 0 records
SELECT * FROM sys.dm_db_missing_index_groups --Returns 600 records

I have seen FIX 969844 [http://support.microsoft.com/kb/969844] and some of the symptoms apply, but not all. Also, according to the fix, since we are running SQL Server 2008 SP1 CU7, it should be fixed.

We do not use any start up parameters that would disable the gathering of index stats.

I am wondering if this seems strange? Any ideas on what might be the issue if it does seem strange?
Tags: Index stats dmv
rm
New Member
New Member

--
21 Dec 2010 12:13 PM
Better to contact MS support on this.
gunneyk
New Member
New Member

--
21 Dec 2010 01:10 PM
Sounds like it may be a bug but also keep in mind that these statistics are very sucsecptable to memory pressure and may be cleared if other processes need memory. Also if the db is closed the stats will go out of scope. Is autoclose turned on for these databases?
rerichards
New Member
New Member

--
21 Dec 2010 03:16 PM

Thanks for the replies.

Regarding memory pressure, I do not believe that is the case since our monitors all show our servers running at right at, or near 1 GB of available memory 24X7.

Our databases are NOT set to Auto Close.

Based upon this I will most likely open a support case with Microsoft. Thanks again.

gunneyk
New Member
New Member

--
22 Dec 2010 06:33 AM
By memory pressure I mostly meant from within SQL Servers memory space itself. For instance if you have relatively small amounts of memory and lots of adhoc queries that take up lots of plan cache or lots of scans that may try to get more memory for the buffer cache. But yes it is likely a bug.
You are not authorized to post a reply.

Acceptable Use Policy