SQL Swiss Army Knife #15 - Handling duplicate indexes

Hello all, Part of my job is to help customers find and fix performance issues, and this post comes after one such engagement. A customer was using another of the SQL Swiss Army Knife series scripts to find info on indexes on all the databases in a given instance – the one referenced in the…

0

Too many single use plans, now what?

Hello, I’ve been asked the question in title at least twice in the past month, but the question is really incomplete – I would rather be asked “I have too many single use plans taking up a lot of memory in my server, can I do something about it?”. The answer would be the classic…


SQL Swiss Army Knife #13 – Exploring the plan cache – Part 2

Hello all, This is long overdue, but here it is, the follow up on plan cache exploration queries part 1 post. There are many “hidden” gems inside a query plan XML that allow us to know our workloads in greater and better detail. Here are a few more xqueries snippets for various purposes. These are…


SQL Swiss Army Knife #13 – Exploring the plan cache – Part 1

Hello all, In the last few months I’ve been creating a few snippets of code to extract as much useful information from the plan cache as possible. Why to do this? We at Microsoft GBS PFE deliver a type of service called a SQL Performance Tuning and Optimization Clinic, which is directed at finding and…


SQL Swiss Army Knife Series is indexed

Hello all, By popular demand, I’m creating this post to serve as the central location for all past and future posts of the “SQL Swiss Army Knife” series, which should also make it easier to find and new updates. Tag Script Initial Release Latest Update Securables    #1 – Scripting Securables 26 Apr 2010 18 Nov…

4

About Maintenance Plans – grooming SQL Server

Latest update: 10/24/2016 (Check change log) Download scripts on GitHub. Hello all, I admit I’m not a big fan of Maintenance Plans (the feature that goes by that name) mainly because of its “do or die” approach. So, it was not by chance that I authored and keep updating the AdaptiveIndexDefrag procedure. Good maintenance (or lack…


SQL Swiss Army Knife #11.1 - Locking, blocking and active transactions

Latest update: 12/2/2016 (Check change log) Download on GitHub Hello all, Following the series “SQL Swiss Army Knife”, here is another version of this script that I’ve been using and tweaking for years now. This way I can get a quick overview of query execution on a given SQL Server instance, from SQL Server 2005…

3

SQL Swiss Army Knife #12 - Index information galore!

Download scripts here: view_IndexInformation.sql (for all DBs at once), view_IndexInformation_CurrentDB.sql (for a DB in scope) and view_IndexCreation.sql (for relevant missing indexes) Hello all, Here is another post on SQL scripts that may help DBAs, following the series “SQL Swiss Army Knife”. I’ve been using and tweaking this script for years now, and with a recent update for SQL…


The SQL Swiss Army Knife #3 - View I/O per file - Updated

Hello all, Here is another one focusing on SQL scripts that may help on everyday DBA tasks, following the series “SQL Swiss Army Knife”. This script will return, very promptly, the overall I/O statistics for all databases in your server, ordered by stalled I/O, and is based on the sys.dm_io_virtual_file_stats DMV. This DMV returns I/O…


SQL Swiss Army Knife #10 - VLFs again. What’s your current status?

EDIT (11-01-2011): A parenthesis was missing making the log_size_MB value skewed. Thanks Luis! EDIT (26-03-2012): Updated script for SQL 2012 support. EDIT (19-11-2012): Added information from Fixing VLFs post, namely potential log size after fix, actual number of VLFs, amount of used VLFs, potential number of VLFs after fix, how many growth iterations to achieve…