Querying the Execution Plan for Subtree Cost - Take 2

A few weeks ago I shared a query for probing the execution plan for the Estimated Subtree Cost value.

That query was rather simple but it got the job done. In that post I also explained how, although there are several ways of finding missing index suggestions, they were not always very helpful. After posting I was thinking that it would be helpful to combine the two, or at least display the results so that it is easy to determine which execution plans include missing index warnings.

Querying the Execution Plan XML for Subtree Cost

There are quite a lot of great tools and queries available to the DBA that aid in troubleshooting and performance tuning. The list is incredibly long, but I mainly use RedGate's SQL Monitor (which has come on in the last 5 years in leaps and bounds), Glenn Berry's DMV queries, Adam Machanic's sp_whoisactive and Brent Ozar Unlimited's sp_Blitz suite (predominantly sp_Blitz and sp_BlitzIndex). In writing this, I see that Brent Ozar has expanded his offering, so it looks like I have a weekend project ahead of me!

Cleanup SQL Server Logins

Cleaning up after someone leaves. We all do it right? Our companies all have water tight policies and procedures to handle that, or maybe they have policies but not procedures. Or maybe they just kind of don't.

Well, if your company is anything like most of the places I've worked, it's somewhere in the middle. And from a DBA perspective, especially on non-production environments, your list of logins and users becomes a bit long and messy after a while.

Compress your database tables

SQL Server has offered data compression since SQL 2008. It is a very good feature but, as with almost everything SQL Server, it needs to be deployed judiciously. Data compression has been written about and reviewed by many SQL experts who are much more qualified and knowledgeable than me, so I won't go into it too much.

First, a quick summary of what the feature does as well as its benefits and effects:
As the feature's name suggests, when it is enabled on a table and/or its index(es), SQL server compresses (or shrinks) the data before it is written to the database. There are two types: ROW and PAGE.

Query To Find SQL Audit Details

I've been deploying a SQL Server auditing solution for our new SQL 2014 estate. I know, we're a few years behind. But if you saw some of the legacy systems I work with you'd understand that migrating to SQL 2014 is quite a coup!

Anyway, I found a really great audit solution that I have used as a base on Colleen Morrow's blog. I will detail what I have added to her solution in a later post. But, for now, I would like to share a query that displays some basic information about existing audits, both at the server and database level.

I put this query together for the purposes of documentation. Our 2014 estate is getting large fast. And with so many installations, we need to keep track of what is installed and configured on all our servers.

Databases With No Recent Backups

I've written the script below to easily identify databases that have not had backups taken within a certain amount of time. This could be useful if you need to build your own monitoring. However, even if you use a third party monitoring tool,as I do, it is helpful to run this query on the server for which I've received an alert.

Many of the servers I manage have a large number of databases. The alerts I receive for overdue backups are database specific. But, in general, if one database is overdue, chances are something has happened on the server. So this query can give me a quick overview.

Finding When A SQL Agent Job Ran

A quick post for today since I haven't added anything in quite a while.

Recently, I was investigating a deadlock that was occurring, like clockwork, on a particular server. A trace revealed that it was being executed by the SQL Server Agent account. The host machine that executed a query involved in the deadlock is our SSIS host. Therefore, there are loads of jobs with a dizzying array of schedules to weed through.

SQL Server Optimization

This post aims to aggregate a lot of the best practice or otherwise recommended configuration for SQL Server performance. It is meant as a summary or quick-reference sheet. All of these concepts are discussed at length all over the internet, so I will not spend much time explaining them. 

As with any recommendations of this sort please use them carefully and test before deploying into a production environment. Also, depending on your specific environment's configuration, not all of the below may be advisable or bring about clear benefits. To quote Jonathan Kehayias: "While there is a lot of good guidance available for how to best configure SQL Server, the specifics of any given implementation is a very big “It Depends…”" What this post should achieve is providing the reader with an awareness of features and options that influence a SQL Server instance's operation. 

Add an Operator to All SQL Server Agent Alerts

I was investigating a new server I'd been assigned, looking for gaps in configuration using the trusty sp_Blitz tool. The organisation that owns this server instance is small and doesn't use third-party monitoring. I decided to add some basic alerting as recommended by Brent Ozar and Co. They've kindly provided a script to do this: Blitz Result: No SQL Server Agent Alerts Configured.

A little later on I realised that I needed to add an additional operator to these alerts and was loath to step through them one at a time and tick the appropriate box. So, I knocked out a quick and simple script (I've included two versions, one that adds the operator to all alerts and one that allows you to select a subset). I hope it is helpful.

SQL Login and User for Performance Tuning

I've run into an interesting dilemma: as a new staff member in a security conscious organisation, I can't really get any work done. There is, however, a lot of work a DBA can do in terms of analyzing SQL Server instances right from day one. There are lots of best practice recommendations that can be made that are application or db design agnostic. Alternately, it can be possible to investigate the database design without access to the underlying data itself. With that all in mind, I have put some scripts together that creates login/user permissions and roles that can give a SQL consultant useful, but security minded, access to a SQL instance.

Database Backup Monitoring

Not everyone has access to off-the-shelf monitoring tools. One of my clients is a very small shop with only a few databases and 2 database servers. But that doesn't mean they don't need monitoring. Over the past few weeks I've been implementing various system alerts and now I've deployed the backup monitoring stored procedure below.

Tracking Page Splits

As a DBA, performance tuning is a regular ongoing task. In my experience it is both a reactive and proactive task. The goal, though, is for your proactive efforts to minimise the time spent on the reactive. This has lead me to focus more on page splitting and index fill factor. Specifically, their interaction. There is a lot of information on the web about the benefits and dangers of fill factor and the performance implications of high rates of page splitting, so I will not go into it too much. I will discuss what I've done to monitor page splits. I then tweak fillfactor on the worst offending tables and indexes.

Running DBCC CHECKDB on TEMPDB

First, a confession. I've been doing it wrong for a long time. Only recently have I learned that TempDB should be included in regular CHECKDB maintenance. Ok, now that I've got that off my chest, I can continue.

So now I've been diligently setting up this task on all servers. NB: TempDB is not available within Maintenance Plans, so you'll need to write the tsql in a job. I add it as a second step in my dbcc maintenance plan job.

Get table and row size data

It often happens that you need to know how big a table is. There are many reasons for this: finding which tables have the most rows; understanding which tables tend to grow the fastest; find which table is using the most space.

I found a nice query on stackexchange.com that listed the tables, row counts and data sizes in a database:

Emergency Access to SQL Server

There are times when you need to gain access to SQL Server in an emergency. It doesn't happen very often, but it does happen. Actually, part of the problem is that it happens so rarely! We all get to grips with the various back-door commands required of us DBAs when we're training or taking a certification course. But after six months, even two or more years, it all gets a bit rusty.

Therefore, I decided to put together an emergency cheat-sheet. All of these processes are very well documented around the web. My purpose here is purely to list the necessary steps to get access to your server quickly. So I won't be explaining how things work. I will list different options of getting the various steps done.

Automating Sliding Partition Windows - Part 4

Recurring Tasks

In the previous posts of this series I both explained the concept of the sliding window partition as well as the way I have configured my system for an automated sliding window. This post will delve into the automated part of the process, the tasks that recur on a regular, in my case monthly, basis.

I have decided to use an SSIS package to execute the various steps of the sliding window, but it is just as valid to wrap the steps into cursors or loops (obviously, you only need a loop if you have more than one table, partition scheme and/or function in your database). Depending on how much you plan on automating there are some challenges in using SSIS. I will be explaining how I've constructed my SSIS package, but it's outside the scope of this post series to explain how to use SSIS. There are many resources on the web that can help you if needed.

Automating Sliding Partition Windows - Part 3

It's been a long while since I've worked on this - and believe me I want to as every month I'm spending too much time manually sliding my various partitions! Which is very prone to mistakes.

In the meantime I found a really good post describing an automated sliding partition window that achieves something a little different. It works on the following premise:

• 7 day partitions that, at the end of each week, are merged into
• 5 weekly partitions that are then merged into 
• 6 monthly partitions that are finally merged into 
• 1 large archive partition

I'm looking at implementing something like this in my company's OLAP database and combined with SQL Server 2012's columnstore indexes I am hoping to get some great performance improvement!

Here's the post:
http://blogs.msdn.com/b/menzos/archive/2008/06/30/table-partitioning-sliding-window-case.aspx

Modify Logshipping Restore Job

In my previous post I shared a stored procedure that dynamically creates and executes a restore log script in order to ease the recovery of logshipping after one or more files have been added to a logshipped database.

As I described in that post I was running into this problem on a monthly basis due to a sliding partition window scenario I maintain on several databases. After I wrote the stored procedure it was a lot easier to fix the problem after new files were added. But I still needed to wait for the logshipping restore job to fail and then intervene and execute the SP manually to recover from the failure. Being busy, not to mention forgetful, there were times when logshipping would be broken for several hours at a time.

Unfortunately, you cannot pro-actively inform the failover server that new files are on their way. But what finally did occur to me was to modify the job that restores the transaction log to include the stored procedure. In short, I added a second step that is run only if the first step fails. It works a treat.

An SP to Fix Logshipping After Adding a New Database File

UPDATE: 22 Jan 2014 - Fixed some bugs, added multi-file support and added a few notes

Every month I need to maintain the various sliding partition windows on several databases. If you've read some of my previous posts you may remember that this involves adding new files for the upcoming partitions. My company's databases are also logshipped as part of the disaster recovery strategy.

What invariably happens every month is I add new files make the necessary partition changes and then wonder why, a little later in the day, I'm receiving alerts for failed transaction log restores. The logshipped database obviously doesn't "know" about the new file and I end up with an error message like this:

Database Snapshot Stored Procedure

We've been trying to tighten up the database release process at work. This involves lots of big and small changes including things like using version control software better, documenting database code as well as putting together some default scripts that run with every release.

It occurred to me that even if you have a good process and set of check lists for the release cycle it is still quite easy to miss out on small but ultimately important steps. Things get hectic, last minute changes are made, etc, etc., and the next thing you know you've forgotten a basic, but important, step like creating a database snapshot or disabling scheduled jobs or revoking user access for the duration of the release. There are probably a dozen other small tasks that can be left out.