There are a gigantic number of tools out there that are free and extremely helpful. Since I’ve been away for a while with other issues (prepping a presentation for SQL Pass – hope it’s accepted; new boss at work; two kids graduating high school; and more), I thought that I would break the ice by discussing three essential DBA tools and lauding their creators.
The free tools listed in this article help my team and me manage well over 100 databases running in 70+ instances, totaling 110+ TB in storage. However, they would help anyone, no matter the size of the shop.
#4: Pinal Dave’s missing and unused index scripts.
The missing index script looks at the DMV in SQL 2008+ and will generate not just a list, but the CREATE INDEX statements themselves. You can look at each index in script form and then decide whether to combine for covering indexes, and whether or not the index is right for the shop.
Where I work, we use a modified version of this script monthly on all production databases. Our version scripts the indexes with all of our standard options, and even generates a proposed name according to our standards. We of course analyze each candidate, and more often than not we end up combining several recommendations into covering indexes, or modifying one existing index to better cover, and of course analyzing if performance (select AND insert/update) needs dictate adding the index at all.
Pinal’s script makes the job of analyzing indexing needs ten thousand times easier.
Pinal’s unused index script views the DMV and determines what indexes are getting no activity. It’s easy to configure to thresholds in our shop, which are usually absolute zero index use in selects, prioritized down by number of updates to the affected index.
Good DBA will save these stats and accumulate them (DMV are only collected since last SQL start) and decide what indexes to drop. Pinal’s script generates a nice DROP INDEX statement.
I salute Pinal Dave for keeping up these wonderful scripts.
#3: Michelle Ufford’s dba_indexdefrag script
This script creates a stored procedure that can be scheduled to reorganize or rebuild indexes. It is loaded with options, including options to check and not actually rebuild or reorganize. It schedules easily into a job, can generate verbose results, and is vital in our OLTP environment where data changes constantly befoul query plans. This script has been frequently updated (It’s on Version 4.1 as of this writing), and it works flawlessly.
Michelle has created an invaluable utility for any production SQL Server DBA.
#2: sp_blitz by Brent Ozar
this script installs a stored procedure into master that runs a very comprehensive health check of any SQL server. You can get a monster output. It catches things that most DBA never check. Examples include: logins with sysadmin access, configuration parameters that are not set to their defaults, users with db_owner roles in a database, even databases running on a compatibility level older than that of the detected SQL Server.
sp_blitz catches databases without a recent integrity check. Many messages are just reminders for things that are unusual (max file size settings are an example). The tool gives an astoundingly thorough analysis of a given SQL Server instance; I recommend running it semi-monthly and reading the report carefully. Even if you are aware of setting X, it is good to be reminded and to see if anything is slipping under your radar.
You can even have sp_Blitz output a count of messages, and set this as a custom counter in many monitoring tools, and get an alert if the count goes up. This tool is amazing; Brent Ozar could have charged for it and gotten real money from it; He does not. Kudos!
#1: sp_whoisactive by Adam Machanic
Here is the tool that saves my butt. In a hotly-used OLTP environment, we can have poorly-formed queries that burn up tempdb, or we can have blocking.
Using this tool, which puts a stored procedure in master, one can see just what is running now. Not a sp_who2 with 400 SPIDs through which you have to sort, you can see the running queries at the moment the SP is executed. You can see the query that is running. You can see how much resources (reads, tempdb, CPU, waits, the login and hostname for the running SPID, and much more.
sp_whoisactive is so fast that you can repeatedly execute in management studio and see the query traffic passing through. As I said, this has helped me isolate more than one “query of death,” and assist the developers in remediating the offensive parts of the query.
sp_whoisactive is my big winner among these.
I respectfully suggest that you go and look at each of these helpful things, and I would wager that you would end up with most, if not all of them on your servers.