[Looking for Charlie's main web site?]

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.

[....Continue Reading....]

Comments
Here's another useful resource related to management of SQL Server statistics, which may be of interest to readers of this post:

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.
Here's yet another interesting resource related to the above:

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).
Still another new resource for understanding SQL Server statistics: a friendly and helpful 11-part video series on youtube (about 2-3 hours total), from Doug Lane, available via BrentOzar's youtube channel. Here's the playlist:

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!
Here's another useful (and rather brief) resource with helpful info on understanding and diagnosing outdated sql server statistics:
https://www.mssqltip...
I have built my own SQL Server Monitoring tool in Coldfusion. Been a DBA since 2002 and found no good tools for monitoring SQL at that time, so decided to build my own. Been improving and changed it to version 8.1.9 at the moment as new versions of SQL and DMW arrrives and yes, regardless what, you need a tool to tell you when something goes wrong aand fast as well. If you know of a SQL guy have him build the queries for you and you are on the way.
Many thanks to Charlie who have helped me a lot when I started out.
# Posted By Robert Nimström | 7/1/23 10:56 AM
Glad to have helped, but as for your tool, is it something others can use? Did you mean to share a url?
Copyright ©2024 Charlie Arehart
Carehart Logo
BlogCFC was created by Raymond Camden. This blog is running version 5.005.
(Want to validate the html in this page?)

Managed Hosting Services provided by
Managed Dedicated Hosting