tag:blogger.com,1999:blog-213131476646021708.post7459993251251213590..comments2023-06-28T12:23:57.400+02:00Comments on SQL Server Online: Multiple statistics sharing the same leading columnHolger Schmelinghttp://www.blogger.com/profile/09685478805011670145noreply@blogger.comBlogger10125tag:blogger.com,1999:blog-213131476646021708.post-58636943213912410862013-07-06T08:36:43.451+02:002013-07-06T08:36:43.451+02:00I remember an article on simple-talk.com that expl...I remember an article on simple-talk.com that explains how to guess the size of the stats.<br />Please do yourself a favor and do NOT delete those.<br /><br />Regards,<br />HolgerHolger Schmelinghttps://www.blogger.com/profile/09685478805011670145noreply@blogger.comtag:blogger.com,1999:blog-213131476646021708.post-54472880026572149262013-07-05T00:14:40.985+02:002013-07-05T00:14:40.985+02:00Hi, is there a way to estimate the size of these s...Hi, is there a way to estimate the size of these stats objects in the database?<br />Knowing that adds to the argument for dropping them.<br /><br />Thank you for this post,<br />Marios PhilippopoulosAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-213131476646021708.post-1865158501443925872013-01-02T15:01:33.199+01:002013-01-02T15:01:33.199+01:00Mark,
I have no idea. Sorry. Are you on SQL Server...Mark,<br />I have no idea. Sorry. Are you on SQL Server 2008 (R2) or later? I forgot to mention that I engineered the script for SQL Server 2008.<br /><br />Regards,<br />HolgerHolger Schmelinghttps://www.blogger.com/profile/09685478805011670145noreply@blogger.comtag:blogger.com,1999:blog-213131476646021708.post-32334386941775659922012-12-28T22:48:25.014+01:002012-12-28T22:48:25.014+01:00When I execute the appropriate DROP STATISTICS com...When I execute the appropriate DROP STATISTICS commands for the auto-created statistics based on these findings, I get this error for each one:<br /><br /> Cannot drop the statistics , because it does not exist or you do not have permission.<br /><br />However, they no longer exist. Do you know why it would say it cannot drop them and then do it anyway?<br /><br />- MarkMark Freeman (@m60freeman)https://www.blogger.com/profile/11475488917130342878noreply@blogger.comtag:blogger.com,1999:blog-213131476646021708.post-50911101253141486542011-12-22T12:20:57.627+01:002011-12-22T12:20:57.627+01:00Ok, thanks. Yes the indexes are already there, did...Ok, thanks. Yes the indexes are already there, did help in the past, but not anymore. The advisor also doesn't suggest any new indexes anymore so I guess it thinks we've already got them all. I'll guess I'll have to look into the slowest and most executed queries and determine which multi-column-stat hint could help on those.<br />Thanks for the answer because now I can stop looking at a way to get more info out of the DETA :).A. Schaepnoreply@blogger.comtag:blogger.com,1999:blog-213131476646021708.post-34115636276724842912011-12-22T10:23:12.747+01:002011-12-22T10:23:12.747+01:00To me, the DTA seems somewhat aggressive regarding...To me, the DTA seems somewhat aggressive regarding statistics recommendations. May be that's because of multi-column-stats which will not be added automatically during query execution. <br />When evaluating DTA hints, I usually start with indexes, leaving stats aside.<br />All in all, there's no rule of thumb on how to "do it right". You'll need some sure instincts and experience for this task...Holger Schmelinghttps://www.blogger.com/profile/09685478805011670145noreply@blogger.comtag:blogger.com,1999:blog-213131476646021708.post-87687261274007575752011-12-21T19:32:15.746+01:002011-12-21T19:32:15.746+01:00Thanks, these three posts are great. Spend a day r...Thanks, these three posts are great. Spend a day reading them and enjoying playing with statistics and indexes.<br />But then I ran SQL Profiler for 10 minutes on a production database, and then ran the trace through the tuning wizard. It came up with suggestions to add 90 stats (every time with different columns) on 1 table. <br />It correctly selected the most heavily used table for its suggestions so its somewhat correct. But creating 90 stats on 1 table? It says those will do about 80% performance improvement. But how do I know it is 1% per stat, or that 5 stats will do 50% and the rest all together 30%? How can I evaluate which of the suggestions the tuning advisor reports are the ones with the most impact?A. Schaepnoreply@blogger.comtag:blogger.com,1999:blog-213131476646021708.post-57099772448523855562011-12-21T15:55:50.147+01:002011-12-21T15:55:50.147+01:00Hmmm I got two similar database (same tables, abou...Hmmm I got two similar database (same tables, about the same amount of content rows but different rows and different views). On the one db this 'get dual statistics' query takes 19 seconds to return 22 results, on the other it takes like no time to come up with 40 results.<br />Something is wrong in the first DB, but finding out what could be a nice puzzle :)A. Schaepnoreply@blogger.comtag:blogger.com,1999:blog-213131476646021708.post-57242273389950322452010-12-02T06:59:27.730+01:002010-12-02T06:59:27.730+01:00Randy,
are you on SQL Server 2005? The filtered i...Randy,<br /><br />are you on SQL Server 2005? The filtered index (and also filtered stats) feature is new for 2008.<br /><br />Regards,<br />HolgerHolger Schmelinghttps://www.blogger.com/profile/09685478805011670145noreply@blogger.comtag:blogger.com,1999:blog-213131476646021708.post-89332157256087523602010-12-02T00:08:49.073+01:002010-12-02T00:08:49.073+01:00I can only run this by removing references to has_...I can only run this by removing references to has_filterRandyhttps://www.blogger.com/profile/16504844681754360503noreply@blogger.com