March 27, 2012 at 6:05 am
What would cause table statistics to be dropped, short of a command to do so?
I have two local databases that I test with. When I left last night, sys.dm_db_index_usage_stats showed all sorts of statistics for both databases. I left the computer on all night, but with no software running. I just came in this morning -- one database has statistics, the other does not. I have no clue what could have caused all the statistics in that database to be dropped.
I'm at a loss. What should I be looking at here?
thanks
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
March 27, 2012 at 6:08 am
Any job which dropped and recreated tables?
March 27, 2012 at 6:11 am
Pradyothana Shastry (3/27/2012)
Any job which dropped and recreated tables?
Nope, nothing like that. Just two databases sitting there unattended, minding their own business.
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
March 27, 2012 at 6:20 am
I have more information. I launched our application against the db that lost its stats. I opened SSMS, opened a new query connected to that database, and queried sys.dm_db_index_usage_stats -- there was data. I shut down our application and re-queried -- there was still data. I switched the query to a different database and back -- all of the statistics were gone.
It appears all statistics are being dropped when the last connection is dropped. Does that raise any red flags?
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
March 27, 2012 at 6:30 am
Ok, terminology problem here...
Statistics in SQL Server are objects that are stored in the database, you;'ll see them listed in sys.stats. So when you said 'statistics getting dropped', people would think you meant DROP STATISTICS <statistic name>
But you don't mean that? You mean that the index usage stats data is getting cleared?
Is auto-close on for that database?
The data in indx_usage stats and a number of other DMVs is not persisted, it's only kept until the database is shut down by a server restart, the database being closed, taken offline, detached or restored. If autoclose is on, then the database is automatically closed when the last connection closes and re-opened when a new connection is established.
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 27, 2012 at 6:37 am
GilaMonster (3/27/2012)
Ok, terminology problem here...Statistics in SQL Server are objects that are stored in the database, you;'ll see them listed in sys.stats. So when you said 'statistics getting dropped', people would think you meant DROP STATISTICS <statistic name>
But you don't mean that? You mean that the index usage stats data is getting cleared?
That is what I mean. Sorry for my incorrect terminology.
Is auto-close on for that database?
Ah. Turns out it is. I will have to be sure to make sure that is set properly for our customers and in-house personnel.
Thank you!!!
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply