Tending the SQL Server garden: tuning and maintenance resources (updated and useful)
Note: This blog post is from 2016. Some content may be outdated--though not necessarily. Same with links and subsequent comments from myself or others. Corrections are welcome, in the comments. And I may revise the content as necessary.Often in my role as an independent consultant providing ColdFusion server troubleshooting services, I find myself helping folks determine whether or that a problem is in their database.
And though it may surprise many, I generally do not find the problem to be very often with the SQL they are running. More often it's about configuration issues. For instance, it may be that they have not been keeping their indexes or statistics updated, or it may be that they need indexes that they don't have, or have too many that they don't really need. Or it may be that they have not done any tuning at all and the default settings for their database server are not be well suited to them.
And though such problems are common to any database server (MySQL, Oracle, PostgreSQL, and so on), I find that an awful lot of my clients (for whatever reason) do run on Windows and do use SQL Server (and that doesn't make them stupid or lazy. Different strokes for different folks).
So in this post I want to focus on some SQL Server resources, related to such matters, which I find that I refer folks to often. Some are blog posts, some are code you can run, and some are tools (mostly free).
People using other DBs would do well to read along and could find corresponding resources/tools for the same problems in their respective DB servers.
https://sqlperforman...
It covers the evolution of SQL Server statistics update processes and features from SQL Server 7, forward. Interesting to get that perspective, as sometimes people operate from old info and don't realize there was some significant change years ago that should change their assumptions about an aspect of the topic.
https://www.brentoza...
Of course, Bret has many, many other resources that touch on the topic that I could list (as do still other folks). I just saw this one today and was motivated to add it here, if it may help anyone (and it shows that issues related to statistics are still present, nearly 4 years after I wrote this post).
https://www.youtube....
This would be a great intro for anyone new to the matters I allude to in the post above. Right from the first video, he is showing real examples of what can go wrong when statistics are incorrect.
See especially the demo in just the session's first few mins, showing how disastrous it can be when the the execution plan for a very slow query shows that the "estimated number of rows" the optimizer thought it would be processing was 1, while the "actual number of rows" that WERE processed was 10 million. Ouch!
https://www.mssqltip...
Many thanks to Charlie who have helped me a lot when I started out.