Author: Arthur Daniels

This post is going to be a change of pace for the blog. I want to talk about how I approach performance tuning, specifically when talking about queries. This won’t be a tutorial, just somewhat of a journal post.

The other day, I was working on optimizing a query in a development environment with another database administrator (DBA). We were comparing execution plans for a new index and looking to see if our query wanted the index. We created several variations of the index and measured whether SQL Server would use the index when running a SELECT statement against the table. Once the query used the index, we decided to look at SQL Server’s IO Statistics . We compared several variations of the index before I noticed that we had been running a SELECT * against the table.

I think this was an example where we should have been testing with an actual workload. My thought process is that a SELECT * is usually not a good representation of the table. If a table is very wide (meaning it has many columns), SQL Server may choose to scan the Clustered Index of a table instead of using a well-designed Nonclustered Index. In the system I was working on, most queries will have a specific set of columns that they want to load. We didn’t keep our testing consistent with the actual system workload.

I’m going to try to make a demo of this scenario in a future blog post. In the mean time, here’s a list of the things that I think are important for consistent testing of performance:

• Same version of SQL Server, or at least set to similar server settings
• Table structure including data types
• Same indexes
• Same set of data used for testing
• If possible, same system memory and CPU
• If possible, similar existing Execution Plan Cache

I listed the last two as possible because sometimes it’s not possible to have the same hardware in Dev as Prod, and I don’t think it’s realistic to expect that on larger SQL Servers. In this case, simply generating an Estimated Execution Plan from the production environment can be good enough.

As promised in my session, I’m uploading my demo scripts here. You’ll also need a copy of the AdventureWorks 2014 demo database. That can be downloaded from Microsoft’s site.

I’ve compressed the demos to one zip file.

Execution Planning for Success Demos

Please comment here if you have any questions.

I’ll be at Gravitate (Link) on Thursday, 2/23/2017 at 6 PM for my first presentation! The subject is “Execution Planning for Success!” Click here to download my presentation:

Execution Planning for Success

I’ll be posting the demos after the presentation for anyone who would like to run the demos themselves.

I’m writing this post for myself 3 years ago. I had just graduated college and received my first job offer, Associate BI Developer. I knew nothing about SQL Server as a product, I had just used Management Studio a lot. In this series of posts, labelled by day number, I want to talk about the things that a new Database Administrator would find useful.

Let’s talk about the backups. All databases have a setting called recovery model. As of SQL Server 2016, there are only 3 choices for this setting. Full, simple, and bulk-logged. At the bottom of this post, I’ll provide some useful articles on database recovery models. For now, we need to know why this important.

When you pick your recovery model, you’re making a big decision for SQL Server. The choice of recovery model will decide how much data you can recover from your backup files.

I’m going to demo the different backup tables in my test virtual box. You can create one just like mine (for free!) by following this post.

In this script, I create a backup of the master database, one of the five system databases on SQL Server. This database will exist on every installation of SQL Server.

Here is the script for creating a backup:

USE master
GO

BACKUP DATABASE master TO DISK = ‘C:\SQLData\master1.bkp’

The Service Account that your SQL Server uses must have permission to the location.

Now, I’ll show how to find this backup without knowing how the backup was taken. The system database, MSDB, has useful Dynamic Management Views, or DMVs, where you can find this information. The DMV I use to find SQL Server backups is msdb.dbo.backupmediafamily.

Query this DMV with a query like this:

SELECT * FROM msdb.dbo.backupmediafamily

In my environment, this returned two rows. The column I want to see is “physical_device_name” and it says:

C:\SQLData\master1.bkp

If there is a lot of activity on a SQL Server, this table will contain a lot of rows. This is a good place to start looking for backups, and the other tables related to backupmedia and backupsets within MSDB will fill in the other information on native SQL Backups.

So what if there is no information inside this table for your backups? This could mean many things. It could mean that backups are being taken by a third party program that doesn’t update SQL tables when it takes backups. It could also mean that the databases aren’t being backed up. It’s important to look for other reasons before assuming there are no backups, but this is a good place to start.