August 22, 2011 at 11:02 am
Yes plz.
August 22, 2011 at 11:09 am
Here is the Code.
WITH autostats ( object_id, stats_id, name, column_id )
AS ( SELECT sys.stats.object_id ,
sys.stats.stats_id ,
sys.stats.name ,
sys.stats_columns.column_id
FROM sys.stats
INNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_id
AND sys.stats.stats_id = sys.stats_columns.stats_id
WHERE sys.stats.auto_created = 1
AND sys.stats_columns.stats_column_id = 1
)
SELECT OBJECT_NAME(sys.stats.object_id) AS [Table],
sys.columns.name AS [Column],
sys.stats.name AS [Overlapped],
autostats.name AS [Overlapping],
'DROP STATISTICS [' + OBJECT_SCHEMA_NAME(sys.stats.object_id) + '].[' + OBJECT_NAME(sys.stats.object_id) + '].[' + autostats.name + ']'
FROM sys.stats
INNER JOIN sys.stats_columns ON sys.stats.object_id = sys.stats_columns.object_id
AND sys.stats.stats_id = sys.stats_columns.stats_id
INNER JOIN autostats ON sys.stats_columns.object_id = autostats.object_id
AND sys.stats_columns.column_id = autostats.column_id
INNER JOIN sys.columns ON sys.stats.object_id = sys.columns.object_id
AND sys.stats_columns.column_id = sys.columns.column_id
WHERE sys.stats.auto_created = 0
AND sys.stats_columns.stats_column_id = 1
AND sys.stats_columns.stats_id != autostats.stats_id
AND OBJECTPROPERTY(sys.stats.object_id, 'IsMsShipped') = 0
-Roy
August 22, 2011 at 11:16 am
tx.
Only 7K stas to drop. Yes I said 7K.
😀
I'll need to analyze this a little further!
August 22, 2011 at 11:23 am
7K. That is a small number.. 😀
-Roy
August 22, 2011 at 11:25 am
Roy Ernest (8/22/2011)
7K. That is a small number.. 😀
Less than 20% actually.
80% of that number on tables with no data. So no real rush :w00t:.
August 22, 2011 at 12:38 pm
i believe brent ozar also mentions this in his performance tuning for race car drivers video. he mentions the overlapping stats and some other good stuff. (but not much about finding unused statistics)
August 22, 2011 at 6:07 pm
nawillia (8/22/2011)
i believe brent ozar also mentions this in his performance tuning for race car drivers video. he mentions the overlapping stats and some other good stuff. (but not much about finding unused statistics)
Got a link to that video?
How do you figure which one to drop (assuming 1 is bad an another 1 is good)?
August 22, 2011 at 6:23 pm
Ninja's_RGR'us (8/22/2011)
nawillia (8/22/2011)
i believe brent ozar also mentions this in his performance tuning for race car drivers video. he mentions the overlapping stats and some other good stuff. (but not much about finding unused statistics)Got a link to that video?
How do you figure which one to drop (assuming 1 is bad an another 1 is good)?
Between an index an auto-generated stats? I'd drop the auto-generated stats.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 23, 2011 at 3:39 am
Grant Fritchey (8/22/2011)
Ninja's_RGR'us (8/22/2011)
nawillia (8/22/2011)
i believe brent ozar also mentions this in his performance tuning for race car drivers video. he mentions the overlapping stats and some other good stuff. (but not much about finding unused statistics)Got a link to that video?
How do you figure which one to drop (assuming 1 is bad an another 1 is good)?
Between an index an auto-generated stats? I'd drop the auto-generated stats.
Ok but what makes you take that call? How can you tell that it's not the index that's @ fault to generate the bad plan?
I'm putting myself in my own shows. At least 1500 decisions to make, not going to do 1500 trace replays to figure out what's best...
Right now the only good decisions seems to do nothing :-).
August 23, 2011 at 4:14 am
Ninja's_RGR'us (8/23/2011)
Ok but what makes you take that call? How can you tell that it's not the index that's @ fault to generate the bad plan?
Stats tell the distribution of data in the column.
An index tells the distribution of data in the column and allows seek operations to find it rows.
Drop the one that does less (it's redundant anyway) and then, if there's still a bad plan, look for and fix the root cause.
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
August 23, 2011 at 5:43 am
GilaMonster (8/23/2011)
Ninja's_RGR'us (8/23/2011)
Ok but what makes you take that call? How can you tell that it's not the index that's @ fault to generate the bad plan?Stats tell the distribution of data in the column.
An index tells the distribution of data in the column and allows seek operations to find it rows.
Drop the one that does less (it's redundant anyway) and then, if there's still a bad plan, look for and fix the root cause.
Ditto.
In general you'd have to be in a situation where you saw a need for an index so you added it, but there was already a set of statistics, auto-generated, that are stepping on your execution plan. I'm fairly certain it wouldn't run the other way with statistics being generated on top of index statistics that already exist (although, probably, anything is possible). But it's that order that suggests to me the proper choice. I decided to put the index on, so that's the one I want used.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 23, 2011 at 7:26 am
Awesome info.
You guys should really get together and write books or talk @ PASS sometimes :hehe:.
Now what could you possibly talk about? :Whistling:
August 23, 2011 at 7:53 am
Ninja's_RGR'us (8/23/2011)
Awesome info.You guys should really get together and write books or talk @ PASS sometimes :hehe:.
Now what could you possibly talk about? :Whistling:
That's a great idea! Maybe next year, huh Gail?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 23, 2011 at 8:00 am
Grant Fritchey (8/23/2011)
Ninja's_RGR'us (8/23/2011)
Awesome info.You guys should really get together and write books or talk @ PASS sometimes :hehe:.
Now what could you possibly talk about? :Whistling:
That's a great idea! Maybe next year, huh Gail?
Ya, it's better to have a real DBA on board... I've heard from good sources that Authors don't know squat :-P.
August 23, 2011 at 8:49 am
Authors know only to speak. They have no practical experience... 😀
just kidding... I have to sit a whole day at Seattle for a pre-con with these two. So I better watch what I say about them.
-Roy
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply