Database maintenance does not have to be expensive. There are free tools out there that will make your life easier. Of course, commercial DBA tools are worth their price – they are paid for a reason. Software like SolarWinds Database Performance Analyzer and Redgate SQL Monitor are two examples of SQL Server tools that can make anyone a better database steward.
If you do not have access to these tools, that doesn’t mean you’re stuck with your homebrewed solutions. If you are on a shoestring budget and commercial tools are not included in your annual budget allocation, the following free tools can help you survive your day-to-day job.
Glenn Berry’s Diagnostic Queries
If you want to know more about the configuration of your SQL Server instance, these are the queries you need. It’s not just configuration but you can also glean the overall health of your SQL Server from the various information that these queries provide.
The queries provide the following basic configuration information:
- SQL Server Version, Update Level, and OS Information
- CPU information
- Global Trace Flags
- Memory
- SQL Server Agent Alert Info
- Volume Info for all LUNs
- Drive Latency Info
- Etc.
It also provides performance related info:
- IO Requests
- Missing Indexes
- CPU Utilization
- Buffer Usage
- Wait Stats
- Etc.
Ola Hallengren SQL Server Maintenance Solution
Just like Glenn Berry’s Diagnostic Scripts, Ola Hallengren’s solution is a group of queries “for running backups, integrity checks, and index and statistics maintenance.” If you have a big daily job that gets slower and slower over time, I suggest that you set up the Index and Statics maintenance to run on regular schedule.
Here’s a typical command to execute;
EXEC dbo.IndexOptimize @Databases = N'MyDB', -- nvarchar(max) @FragmentationLow = NULL, -- nvarchar(max) @FragmentationMedium = N'INDEX_REBUILD_ONLINE,INDEX_REORGANIZE', -- nvarchar(max) @FragmentationHigh = N'INDEX_REBUILD_ONLINE,INDEX_REORGANIZE', -- nvarchar(max) @FragmentationLevel1 = 5, -- int @FragmentationLevel2 = 30, -- int @PageCountLevel = 1000, -- int @UpdateStatistics = N'ALL', -- nvarchar(max) @OnlyModifiedStatistics = N'Y', -- nvarchar(max) @Indexes = N'ALL_INDEXES', -- nvarchar(max) @TimeLimit = 10800 -- int 10800= 3 hours
You can also set up a SQL Server Integrity Check for the databases, tables, filegroups, indexed views, etc.
Brent Ozar’s sp_Blitz
The sp_Blitz suite has become a day-to-day tool for me. Whether you’re checking the overall health of your SQL Server (sp_Blitz) or maintaining your Indexes (sp_BlitzIndex), this sp_Blitz has got you covered. Want to know the worst-performing stored procedure? sp_BlitzCache can help you with that. Is your SQL Server slow? Let’s do sp_BlitzFirst on that. sp_Blitz seems to have covered all the important parts of database maintenance.
Adam Machanic’s sp_whoisactive
If we talk about helpful tools on SQL Server or ask data pros about their favorite tools, I’m sure the sp_whoisactive stored procedure comes up in the conversation. In fact, it is part of my heuristic approach to performance tuning, which you can read about here.
Paul Randal’s Wait Stats Scripts
There are great scripts on Paul Randal’s blog but the Waits Stats scripts have got to be my favorite. Waits is probably part of everybody’s performance tuning method. Waiting tasks can reveal the pain points of your SQL Server.
SentryOne Plan Explorer
Who still uses the built-in SSMS Execution Plan viewer nowadays? If you do a lot of query optimization or troubleshooting, this is the most important tool you need. There is just a lot of features in this tool that you can’t find in the SSMS Execution Plan Viewer. No database maintenance tools stash is complete without Plan Explorer.
To sum up, you don’t need to shell substantial amount of money to have a decent set of tools for database maintenance. There are lots of great tools like the ones listed above. What is your favorite tool? Share it in the comments below.
