This month’s T-SQL Tuesday is brought to you by Jens Bestergaard – the subject is Essential SQL Server tools, the original invitation can be found here.

Here is how my day of using SQL tools went:

It all started at 00:00 this morning with planned maintenance, I was adding a bunch of Clustered indexes to databases that were missing when compared to a template database – for this I used an adapted version of Kendra Little’s script to script out all indexes.

During the creation of these indexes to keep an eye on things I was using SentryOne

Top SQL for running queries and completed queries

Blocking  tab – for any blocking I may have been causing

Dashboard (History mode) To check the overall impact on the server whilst creating these indexes and also to keep an eye on the Forward recs graph which was dropping nicely as each index was added

Finished up around 2:30am.

10am ish I checked out the Undercover Inspector email reports for all SQL stacks,  every day the inspector provides us with some of the information we need to keep housekeeping in check such as:

Drive space checks including estimated daily growth and last 5 days drive usage – a great way to keep an eye on potential drive space issues.

Failed agent jobs

Database ownership

Backup sizes by day aggregated information

Database states and much more all in one email, or log the html report to the ReportData table if preferred.

If you like the look of our very own Inspector report why not grab the source code from Git and check out the Getting Started quick guide.

12pm ish I needed to parse a text file containing log information from a SQL data extract , I wanted to see just the errors only but this log file contained ALOT of log information. A quick bit of Powershell code allowed me to parse the entire file and output to a new file including just the errors! I really should spend more time learning powershell!

1pm Lunchtime – So I visited our Github repo to remind myself I need to pull my finger out and get the issues I raised coded and closed off! I really like Git , whilst still new to Git its pretty easy to pick up and well worth investing some time in.

Approx 2:30pm I needed to review the Sync/Async settings for all AGs on one of the SQL stacks – I used sp_AGReconfigure @CheckOnly = 1 to show the current configuration for all AGs on the Primary server. If I needed to change any sync settings or readable secondary settings then I could even use the same proc to produce the statements to do so – for more info on the proc see our blog post here. This stored procedure has saved us a lot of time especially when secondary nodes are getting patched and rebooted.

Approx 3pm – Used Query Store to check the top resource consuming queries on the databases I added the clustered indexes to – there was a number of Stored procedures that were previously performing badly , was great to see the improvement within query store, it represents the query information nicely and if required its easy to lock in plans, another reason to love SQL Server 2016+

Last 2.5 hours of the day – Used sp_Blitzcache – Who doesn’t? great proc for when I need to get plan info and session SET options for specific plans in the cache by stored procedure name , I have also been using it lately for all queries based on CPU usage just so many ways to run this proc! I love this proc! I really need to spend more time working with this proc because it provides so much information, absolutely love the recent changes which flag Non sargables.

Used sp_WhoIsActive  – Again who doesn’t? A great stored proc for checking out what’s running right now, I have used this so many times without even realizing that there is a huge parameter offering , it is well worth reading over exactly what each parameter can do for you, some of my goto Parameters are:

@filter_type – I tend to use ‘Database’ , ‘program’ or ‘Login’ for specific searching otherwise I stick with the default ‘Session’

@get_outer_command – Love this one! gets the associated outer ad hoc query or stored procedure call

These are my goto configurations but this proc allows you to do so much! I reckon I probably use it daily.

Used sp_Failedlogins to check for failed logins, it is a lot quicker than manually opening the SQL server error log and applying a filter. When a user has an error logging into SQL this is my goto proc to get a quick answer whether it is a login based issue.

These are just some of the tools I use, there are so many other great tools available to use – if I started listing them out I do not think I would get this post out in time

Thanks for reading.