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.

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.

Some common blog posts I refer folks to

First up, I want to list here some blog posts to which I often find myself referring people. (Yes, grammar police. I'm correcting that dangling preposition at end of the section title just then.)

• Showing long-running SQL Server queries: First is a post of my own, Free, simple code to find out what SQL statements are running slow in SQL Server right now, with some SQL code that helps you readily see what queries are running right now. Sometimes SQL Server monitoring tools may NOT show any queries running at a point in time, when your own observation of an app, or better monitoring tools for ColdFusion (or whatever app server you use) may say that they ARE waiting for the DB to finish some long-running query or stored procedure. Being able to confirm if there IS a query running at such time can be valuable. And if there is not, then it points to a possible networking issue, in CF's inability to talk to the DB server.
• Showing outdated SQL Server statistics: Second is a post on the blog of a really great daily SQL blogger, Pinal Dave (A former CFer) who runs the SQLAuthority blog. While he has hundreds of posts each year that are valuable, one in particular that I find myself pointing people to is one done actually by a guest blogger, Stuart Ainsworth (who I've known from our days in the Atlanta SQL Server user group, atlantamdf.com). I mentioned briefly above how outdated SQL Server statistics can hurt, as they're used by the query analyzer to create execution plans. Well, Stuart wrote some nifty code to check how out-dated your SQL Server stats may be, across all tables and all databases in your server. Very eye-opening. (And a couple other resources here can help you further if that's an issue.)
  • Note a point about that code: if your database names have special characters (even a simple "."), the offered SQL will fail. To fix it, change this line
    DECLARE @dynsql NVARCHAR(4000) = 'use ?
    to this:
    DECLARE @dynsql NVARCHAR(4000) = 'use [?]
    (putting brackets around the "?"). I have added that as a comment on the post and hope that Pinal will correct it there, bit if not I wanted to offer this here.
• Free SQL Server Statistics book: For instance, for those who need more help on that topic see a blog post I did pointing out "Understanding SQL Server Statistics": A great, free, relatively brief ebook .
  Updates since original post:
  • For those who prefer video over reading, here is a nice 11-part series of brief videos on better understanding the value of keeping updated statistics in SQL Server.
  • And here is an interesting blog post on how SQL Server statistics have changed over the years in various SQL Server versions.
• See more shared in comments below, as new info since posting this

Some SQL Server troubleshooting resources everyone should know about

Second are some troubleshooting resources that I think everyone should know about.

• Ola's scripts: First among those is actually a set of resources to better maintain your SQL Server database, the real crux of "tending the garden" that I refer to in the blog post title here. And for that, check out the free and award-winning maintenance scripts and resources offered by Ola Hollengren. Everyone responsible for maintaining SQL Server (databases, indexes, statistics, and such) should check these out, and even if you have "someone who does that", still check them out in case you can help them do that job better.
• Glen Berry's scripts: Yet another guy with some wonderful SQL Server resources is Glen Berry, known as Dr DMV (which refers to Data Management Views, a feature added in SQL Server 2005 which offers easy access via SQL to a tremendous amount of performance information). He has a large set of DMV scripts, organized into important topics, and he updates them regularly, as posted at the category about them at his blog. He has also recently been doing a daily detailed overview of them, and also occasionally does presentations where he reviews them. Again, everyone who has any troubles at all that they think are in SQL Server should check these out, and again even if it's "someone else's job to keep an eye on such things".
• SQL monitoring tools: And just as I show people daily in my work the value of monitoring tools for ColdFusion (like FusionReactor, so too do I often help them see the need of monitoring tools for SQL Server (or whatever DB server they run). Often the built-in tools are not good enough, as in SQL Server Management Studio and its Activity Monitor (which can under-report issues), or SQL Server Profiler (which can be overwhelming and overkill for many). There's especially value in something that tracks and logs all slow queries (not just those slow "on average", as in Activity Monitor), as well as something that tracks overall resource usage on the SQL Server machine, which some DB monitoring tools do, and otherwise there are tools that focus just on that. And I list dozens of examples of each, whether free or commercial, in categories of my CF411 site (of tools and resources of interest to CFers, and others), specifically Database/SQL Monitoring Tools and System Monitoring Tools. I really believe that anyone suffering what seem to be DB troubles should be monitoring their DB and DB server, to know what the problem is.
• Network monitoring tools: Finally, just as I mentioned at the outset that sometimes a DB tool will help prove that a problem is NOT in the database after all, but perhaps a networking issue, I would also recommend that anyone facing that problem should have a good network monitoring tool, to keep an eye on the success of communication between the machine running CF (or whatever app server) and the machine running the DB. I list such tools among those System Monitoring Tools mentioned above, but one I would highlight, which is free for most uses, is PRTG.

Some older blog posts from myself and others

Finally, there are some older blog posts that some may benefit from, though not necessarily updated recently/regularly:

• New for CF9 (and 9.0.1): a query timeout that really works, with a caveat
• Resources for getting a much greater understanding of the SQL Server query plan/procedure cache
• Several SQL Server Performance Tuning how to's
• Helpful info on SQL Server Diagnostics
• Helpful info on SQL Server Diagnostics: Part II
• .NET Performance Issue: What if I suspect that my performance problem is in SQL server?
• Got problems with SQL Server tempdb? Here are some resources
• Have you sought a keyboard shortcut to "open table" in SQL Server Server Management Studio?
• New (free) Performance Dashboard for SQL Server 2005 SP2
• Solving error connecting to SQL Server 2005 from CFMX 6.1/7 on Localhost

Hope those resources may help folks, and if you think there are some that are also "don't miss"-type resources for SQL Server troubleshooting, let me know and I may add them to the list.

For more content like this from Charlie Arehart:

• Signup to get his blog posts by email:
• Follow his blog RSS feed
• View the rest of his blog posts
• View his blog posts on the Adobe CF portal

Need more help with problems?

• If you may prefer direct help, rather than digging around here/elsewhere or via comments, he can help via his online consulting services
• See that page for more on how he can help a) over the web, safely and securely, b) usually very quickly, c) teaching you along the way, and d) with satisfaction guaranteed