August 1, 2011 at 6:41 am
Hello
SQL Server 2008, 2008 R2.
Does the SQL server engine ever deletes a system-created statistic automatically if it dedects that if hasn't been used for some time?
Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -
August 1, 2011 at 6:48 am
Not that I know off. The real quesion is why do you care about that?
Each stats is very small on the HD.
August 1, 2011 at 7:09 am
Ninja's_RGR'us (8/1/2011)
Not that I know off. The real quesion is why do you care about that?Each stats is very small on the HD.
One of our clients restored their production DB onto a test server a week ago and we discovered that a number of auto-generated statistics are missing from the test server DB while they do actually exist on the production DB. The test DB is just a restore of the production DB and was wandering if SQL Server does any cleanup by itself. I have never encountered this issue.
Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -
August 1, 2011 at 7:18 am
That actually can't happen. The restore of a backup is a perfect copy of the DB (including index and stats).
Either the backup is older than the db itself or someone ran a script to drop stuff (maybe altering /dropping columns?!?! which might explain missing stats)
August 1, 2011 at 7:48 am
Ninja's_RGR'us (8/1/2011)
That actually can't happen. The restore of a backup is a perfect copy of the DB (including index and stats).Either the backup is older than the db itself or someone ran a script to drop stuff (maybe altering /dropping columns?!?! which might explain missing stats)
Found this white paper. related to SQL Server 2000.
To avoid long term maintenance of unused statistics, SQL Server 2000 ages the automatically created statistics (only those that are not a byproduct of the index creation). After several automatic updates the column statistics are dropped rather than updated. If they are needed in the future, they may be created again. There is no substantial cost difference between statistics that are updated and created. The aging does not affect user-created statistics.
Not sure if it still applies to SQL Server 2008 since I have another white paper on Statistics and there is no mention of this behaviour.
The scripts which have been executed on the test DB were all related to indexes, specifically index column position changed. No table columns have been deleted.
Thanks for your replies.
Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -
August 1, 2011 at 7:54 am
Well for sure editing an index could cause this.
This is the first I hear of stats being dropped. Tho I can understand the decision behind the behavior.
Other than a change in the meta-data of the DB, what other problems is this causing for you?
August 1, 2011 at 8:01 am
So, where/how does it keep track if stats are no longer being used ??
SELECT quotename(object_schema_name(OBJECT_ID)) + '.' + quotename(object_name(OBJECT_ID)) as ObjectName
, quotename(name) AS statistics_name
, STATS_DATE(OBJECT_ID, stats_id) AS statistics_update_date
, *
FROM sys.stats
-- WHERE OBJECT_ID = OBJECT_ID('[dbo].[objects]')
order by statistics_update_date desc
, ObjectName ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 1, 2011 at 8:07 am
Which internal expert should I call in for this one?? I'm drawing blanks ATM.
August 1, 2011 at 8:17 am
Ninja's_RGR'us (8/1/2011)
Well for sure editing an index could cause this.This is the first I hear of stats being dropped. Tho I can understand the decision behind the behavior.
Other than a change in the meta-data of the DB, what other problems is this causing for you?
It's not really causing any issues, just curious as to what happened with those system-created statistics. I was not aware that dropping and re-creating all indexes on a table will cause it's statistics (especially system generated) to be dropped!
Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -
August 1, 2011 at 8:19 am
ALZDBA (8/1/2011)
So, where/how does it keep track if stats are no longer being used ??
SELECT quotename(object_schema_name(OBJECT_ID)) + '.' + quotename(object_name(OBJECT_ID)) as ObjectName, quotename(name) AS statistics_name
, STATS_DATE(OBJECT_ID, stats_id) AS statistics_update_date
, *
FROM sys.stats
-- WHERE OBJECT_ID = OBJECT_ID('[dbo].[objects]')
order by statistics_update_date desc
, ObjectName ;
Can't really rely on the statistics_update_date column since this would get updated if running any statistics maintenance jobs.
Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -
August 1, 2011 at 8:22 am
brian118 (8/1/2011)
Ninja's_RGR'us (8/1/2011)
Well for sure editing an index could cause this.This is the first I hear of stats being dropped. Tho I can understand the decision behind the behavior.
Other than a change in the meta-data of the DB, what other problems is this causing for you?
It's not really causing any issues, just curious as to what happened with those system-created statistics. I was not aware that dropping and re-creating all indexes on a table will cause it's statistics (especially system generated) to be dropped!
Well my understanding is that if you alter an index, the stats should be dropped and recreated to match the new definition. So those stats would still be there, just under a new name / id.
August 1, 2011 at 8:34 am
Ninja's_RGR'us (8/1/2011)
brian118 (8/1/2011)
Ninja's_RGR'us (8/1/2011)
Well for sure editing an index could cause this.This is the first I hear of stats being dropped. Tho I can understand the decision behind the behavior.
Other than a change in the meta-data of the DB, what other problems is this causing for you?
It's not really causing any issues, just curious as to what happened with those system-created statistics. I was not aware that dropping and re-creating all indexes on a table will cause it's statistics (especially system generated) to be dropped!
Well my understanding is that if you alter an index, the stats should be dropped and recreated to match the new definition. So those stats would still be there, just under a new name / id.
Agreed, but here you're not refering to system-generated statistics, No?
A system generated statistics is always created on a single column and I have > 20 statistics created on columns which are not part of any index!
Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -
August 1, 2011 at 8:37 am
brian118 (8/1/2011)
Ninja's_RGR'us (8/1/2011)
brian118 (8/1/2011)
Ninja's_RGR'us (8/1/2011)
Well for sure editing an index could cause this.This is the first I hear of stats being dropped. Tho I can understand the decision behind the behavior.
Other than a change in the meta-data of the DB, what other problems is this causing for you?
It's not really causing any issues, just curious as to what happened with those system-created statistics. I was not aware that dropping and re-creating all indexes on a table will cause it's statistics (especially system generated) to be dropped!
Well my understanding is that if you alter an index, the stats should be dropped and recreated to match the new definition. So those stats would still be there, just under a new name / id.
Agreed, but here you're not refering to system-generated statistics, No?
A system generated statistics is always created on a single column and I have > 20 statistics created on columns which are not part of any index!
I've never studied that or read anything on the subject. I'll ping other old members incase they know something I don't.
I'd do test scripts but I don't have time ATM.
August 1, 2011 at 12:09 pm
Stats can be manually created on non-indexed columns, so far as I know, which is what the Create Statistics statement is for. The key info to know is that stats can be created on any column that an index can be created on. BOL doesn't say anything about requiring an index on the stat column and I don't seem to recall being informed about needing an index when I played with the statement way back when.
This is the first I've heard of any stats being dropped, though the auto-stat dropping makes sense. If you have stats that need to port over from one instance to another, you could script them out, make them part of the restore. But that doesn't explain manual stats getting dropped.
August 1, 2011 at 12:14 pm
I did like the free ebook "SQL Server Statistics" available at red-gate ebook store. http://www.red-gate.com/our-company/about/book-store/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply