February 15, 2016 at 10:21 pm
Hello,
Do we have any std DMVs to join and get missing stats likewise we get missing indexes?? I'm not talking about outdated stats though. Even it is also known that a stat is created once an index is created if AUTO_CREATE_STATISTICS option is set as On. I'm talking abt the stats created manually.
Thanks.
February 16, 2016 at 1:59 am
Stats are created with indexes regardless of whether autocreate stats is on or off. Autocreate determines whether SQL will automatically create 'missing' stats or not.
No, there isn't a DMV. With default settings SQL will automatically create any single-column stats it considers to be 'missing'
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
February 16, 2016 at 8:35 am
Thanks Gila.
Wanted to add that, DTA sometimes recommends to create custom stats based on the workload data. Even in many cases, DBA/Dev create custom stats for the query optimizer to use the path.
Question is, is there any good doc available which can help provide more details like when to create stats and how that can benefit the overall query performance? Please advise!
Thanks.
February 16, 2016 at 12:48 pm
For statistics, I'd do what DTA said. For indexes, definitely not, all DTA index recommendations should be thoroughly reviewed before creating/implementing. But for stats, if SQL "says" it needs them, I'd create them.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 16, 2016 at 3:51 pm
SQL-DBA-01 (2/16/2016)
Thanks Gila.Wanted to add that, DTA sometimes recommends to create custom stats based on the workload data. Even in many cases, DBA/Dev create custom stats for the query optimizer to use the path.
Question is, is there any good doc available which can help provide more details like when to create stats and how that can benefit the overall query performance? Please advise!
Because this is completely situational, I would only ever measure before and after creating the statistics in order to determine if the statistics are useful or not. Same thing as the index suggestions. Some of the suggestions are good. Some are not. Testing is the only way to be sure.
"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
February 16, 2016 at 8:52 pm
As Gail said, SQL server creates missing statistics automatically.
Check sysindexes to see which "missing" statistics have been created by the server in the background. Their names start with "_WA_Sys_"
_____________
Code for TallyGenerator
February 17, 2016 at 1:40 am
sys.stats. sysindexes is deprecated. (yes, it holds most of the info, but has none of the post-SQL 2000 properties)
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
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply