New course on Optimizing Ad Hoc Statement Performance

Kimberly’s second Pluralsight online training course has just gone live and is available for watching!

Her course is SQL Server: Optimizing Ad Hoc Statement Performance and is a mammoth 7.5 hours long and full of extensive demos. The modules are:

  • Introduction (21 mins)
  • Statement Execution Methods (90 mins)
  • Estimates and Selectivity (98 mins)
  • Statement Caching (92 mins)
  • Plan Cache Pollution (101 mins)
  • Summary (32 mins)

Check it out here.

We now have almost 100 hours of SQLskills online training available, all for as little as $29/month through Pluralsight (including more than one thousand other developer and IT training courses). That’s unbeatable value that you can’t afford to ignore.


Two new courses on Advanced Corruption Recovery Techniques and Maintenance Plans

I’ve just heard from our good friends at Pluralsight that our two latest online training courses are now live and available for watching!

My latest course is SQL Server: Advanced Corruption Recovery Techniques which follows on from my earlier SQL Server: Detecting and Recovering from Database Corruption course.

The course is 4 hours long and has the following modules:

  • Introduction
  • DBCC CHECKDB Internals and Performance
  • Useful Undocumented DBCC Commands
  • Dealing with Transaction Log Problems
  • Advanced Restore Techniques
  • Advanced Repair Techniques

Check it out here.

Jonathan’s latest course is SQL Server: Maintenance Plans. The course is two and a half hours long and has the following modules:

  • Introduction
  • Approaches to Database Maintenance
  • Configuring SQL Server Agent Settings
  • Common Maintenance Tasks
  • Other Tasks
  • Creating Maintenance Plans
  • Maintenance Plan Challenges and Alternatives

Check it out here.

Now you have something to do this weekend :-) Enjoy!

Are I/O latencies killing your performance?

In this post I explain some methods for investigating and reducing high tempdb and transaction log I/O latencies that can severely hamper the performance of your workload.

Back at the end of August I kicked off a survey asking you to run some code to calculate average I/O latencies and send me the results. I have to apologize for taking two months to get to this editorial as I kept putting off spending the time to collate all the results I was sent. I made up for it by putting in lots of useful information :-)

I received results from 1094 random servers around the world (thank you all!) hosting 25445 databases and it took me a while to pull all the results into SQL Server so I could aggregate the data. Here it is.

What is Good or Bad?

For everything we’re talking about, you have to consider two things:

  • What is good or bad for I/O latency?
  • Even if you have “bad” I/O latency, do you care?

Everyone has their idea of what constitutes good or bad I/O latency, and here’s my take:

  • Excellent: < 1ms
  • Very good: < 5ms
  • Good: 5 – 10ms
  • Poor: 10 – 20ms
  • Bad: 20 – 100ms
  • Shockingly bad: 100 – 500ms
  • WOW!: > 500ms

You may want to argue that my numbers are too high, too low, or wrong, based on what you’ve seen/experienced, and that’s fine, but this is my blog and these are my numbers :-)

Even if your I/O latency is in what I consider the “bad” category, it could be that your workload is performing within the acceptable bounds for your organization and your customers, and so you’re comfortable with that. I’m OK with this, as long as you are aware of your I/O latencies and you’ve consciously decided to accept them as they are. Being ignorant of your I/O latencies and just accepting the workload performance as it is, because that’s just what the performance is, is not acceptable to me.

On to the survey data…

Tempdb Data Files

For this data I worked out the average read and write latency over all tempdb data files for each instance. I didn’t see any instances where one tempdb data file had a huge latency compared to the others so I believe this is a valid approach.

TempdbAvgRead Are I/O latencies killing your performance?

The tempdb data file read latencies aren’t too bad, to be honest, with more than 93% of all the servers in the survey having read latencies less than 20ms.

TempdbAvgWrite Are I/O latencies killing your performance?

This is very interesting – almost 42% of all the servers in the survey had average tempdb data file write latency of more than 20ms, and just over 12% of all servers had average tempdb data file write latency of more than half a second per write – that’s ridiculously high!

To be honest, I’m pretty shocked by these results. especially the relatively high number of servers with multi-second average write latencies for tempdb.

So if you check your average I/O latency for tempdb (using a script such as the one I blogged here, using sys.dm_io_virtual_file_stats) and find that it’s really high on my (or your) scale, what can you do?

Well, there are four approaches I can think of:

  1. Don’t do any investigation and just immediately move tempdb to a faster I/O subsystem, such as two SSD cards in a RAID-1 configuration (remember, one SSD is RAID-0, and that’s not good enough because if tempdb is corrupt or unavailable, your instance shuts down!). You might think that this is the lazy approach, but if you *know* you can’t make any changes to workload, this may be your only option. I bumped into this scenario while working with a client that supplies casino software. The optimal solution was to change some SPs to reduce tempdb usage, but that was going to take 18 months to be approved by the Gaming Commission in the state where the client’s client was located. The only solution in the meantime? Pony up for a faster tempdb I/O subsystem.
  2. Investigate the I/O subsystem where tempdb is located, looking for things like (non-exhaustive list):
    • Pathing/network issues, such as having a 1Gb switch in the middle of a 4Gb path, or having mismatched jumbo frame settings in your iSCSI configuration, or where the network to the SAN is being saturated by something other than SQL Server I/O traffic.
    • Incorrect SAN settings, such as not having write-caching enabled for a write-heavy workload, incorrect queue depth compared to your SAN vendor’s recommendations, or tempdb stuck on slow storage on an auto-tiering SAN because of incorrect SAN training.
    • Multiple users of the portion of the I/O subsystem where tempdb is located, such as having tempdb lumped in with other volatile databases, or even having LUNs shared between SQL Server and other applications like Exchange or IIS.
    • Only having a single tempdb data file so missing out on the usual higher I/O subsystem performance gained from having multiple data files in a filegroup (note: don’t confuse this with adding more tempdb data files to reduce PAGELATCH_XX contention on in-memory allocation bitmaps.)
  3. Try to reduce the usage of tempdb, by looking for (non-exhaustive list):
    • Spill warnings in query plans (hash, sort, or exchange) indicating that there was not enough query execution memory and an operator had to spill results to tempdb. See these blog posts for more information: here, here, here, and a blog post of mine explaining how to understand the data vs. log usage for a memory spill to tempdb here.
    • Incorrect, excessive usage of temp tables, such as *always* using a temp table when it may be better sometimes to not do so, pulling more columns or rows into a temp table than are actually required (e.g. SELECT * into the temp table from a user table, with no WHERE clause), creating nonclustered indexes on a temp table that are not used. I’ve seen this over and over with client code.
    • Index rebuilds that use SORT_IN_TEMPDB.
    • Using one of the flavors of snapshot isolation and allowing long-running queries that cause the version store to grow very large.
    • There are lots of useful queries and other information in the whitepaper Working with tempdb in SQL Server 2005 (which is still applicable to all current versions.)
  4. A combination of #2 and #3, and then maybe you just have to move to a faster I/O subsystem, as in #1.

One other thing to consider is the risk of making a change to your code and/or the cost of the engineering effort (dev and test) to do so. It may be cheaper and less risky to move to a faster I/O subsystem. Your call. Another issue you may have is that the bad code is in a 3rd-party application that you have no control over. In that case you may have no choice except to throw hardware at the problem.

Transaction Log Files

For this data I considered each database separately rather than aggregating per instance.

LogReadAvg2 Are I/O latencies killing your performance?

LogWriteAvg2 Are I/O latencies killing your performance?

For the transaction log, you really want the average write latency to be in the 0-5ms range, and it’s good to see more than 79% of transaction log files in the survey are achieving that. I would say that write latency for the transaction log is much more important than read latency, as write latency slows down transactions in your workload. That’s not to say that you should ignore high read latencies, as these slow down log readers (such as log backups, transactional replication, change data capture, asynchronous database mirroring/availability groups), but log read latencies don’t usually slow down your workload unless you have transactions that are rolling back (the only time that transactions will cause log reads) or you rely heavily on change data capture.

So you’re focusing on write latency. Again, there are multiple approaches that are the same as #1-#4 above. What you’re looking for in approach #3 is different though. I’ve written several detailed posts about transaction log performance tuning (including reducing the amount of log generated and changing the log block flush pattern) for SQL Sentry’s blog so rather than duplicate those, I’ll point you to them:

And then there’s my 8-hour Pluralsight online training class that covers SQL Server: Understanding Logging, Recovery, and the Transaction Log.


It’s really important that you pay attention to the read and write I/O latencies for critical components of your SQL Server environment: tempdb and transaction logs. I’ve given you a lot of info above on what you can do inside and outside SQL Server to reduce these latencies and increase your workload throughput.

Unless you have no choice, don’t just throw some SSDs into the mix without first figuring out whether there are some things you can do inside SQL Server to reduce the I/O load, and if you do throw in some SSDs, make sure that you’re using them to host whatever files are your biggest I/O bottleneck, and make sure you’re using at least two of them in a RAID-1 configuration to protect against failure.

I hope this has been a useful read – happy tuning!

Why is log_reuse_wait_desc saying LOG_BACKUP after doing a log backup?

Here’s an interesting situation that sometimes crops up: you’re performing log backups, your transaction log isn’t growing, and yet the value of log_reuse_wait_desc in sys.databases for your database says LOG_BACKUP.

What’s going on?

I was actually just teaching this stuff today here in our last class of the year in Chicago.

Firstly, what is log_reuse_wait_desc? It’s a field in sys.databases that you can use to determine why the transaction log isn’t clearing (a.k.a truncating) correctly. Usually you query it when a log file is growing and you want to know why, using code like:

SELECT [log_reuse_wait_desc]
	FROM [master].[sys].[databases]
	WHERE [name] = N'Company';

You can read about all the possible values in the Books Online topic Factors That Can Delay Log Truncation.

One important thing to bear in mind is that the value returned is the reason that log clearing did not clear any VLFs (Virtual Log Files – see Understanding Logging and Recovery in SQL Server) that last time that log clearing was attempted. For instance, you may see a value of ACTIVE_BACKUP_OR_RESTORE but you know that your full backup has completed. This means that the last time log clearing was attempted, the backup was still running.

Back to the original question. If you have a transaction log that is not growing, and you’re taking regular log backups, but the log_reuse_wait_desc remains LOG_BACKUP, this is because zero VLFs were cleared when the previous log backup was performed.

How can that happen?

Imagine a database where there’s very little insert/update/delete/DDL activity, so in between your regular log backups there are only a few log records generated, and they’re all in the same VLF. The next log backup runs, backing up those few log records, but it can’t clear the current VLF, so can’t clear log_reuse_wait_desc. As soon as there are enough changes in the database that the current VLF fills up and the next VLF is activated, the next log backup should be able to clear the previous VLF and then the log_reuse_wait_desc will revert to NOTHING. Until the next log backup occurs and isn’t able to clear the current VLF, in which case it will go back to LOG_BACKUP again.

So LOG_BACKUP really means “either you need to take a log backup, or the log records that were backed up were all in the current VLF and so it could not be cleared.”

Hope this helps!


SQLskills community mentoring – round 5

We’ve had a mentoring program here at SQLskills for a couple of years (the brainchild of Jonathan), where each of us can pick someone who’s attended one of our Immersion Events in the past and offer to be their mentor for six months.

It’s time to kick off the fourth mentoring session. You can read about the previous mentees below:

I’m the only one mentoring this time. I’ve found it really interesting helping my four prior mentees with a variety of personal and professional goals and getting to know them better. This time I’ll be mentoring Denis Tolstonogov who took IE1 in Tampa and IE2 in Chicago earlier this year. In his own words:

I was born in Russia, a small town called Kurgan, and came to USA 10 years ago. I have not regretted it yet! Back in Russia, I graduated from Kurgan State University with an MBA but never used it. I’ve been always interested in computers but never had a chance to actually study for it. After coming to the US, I started with attending an ESL school in Manhattan to learn how to speak English, still learning. After graduating the ESL school, I transferred to our local Community College where I continued to take English classes and majored in Computer Networking. I’ve started my IT career as a Network Admin, which I did for about 9 years, so I am one of those Accidental DBAs! I’ve been always fascinated by SQL Server, its complexity and the challenges that it presented. Any SQL project that I had to deal with was frustrating but always interesting! Frustrating only because I was lacking the knowledge. In 2009, my boss asked me if I’d be interested in spending my time only on SQL Server because we run a web site with SQL Server database as the back end but there is nobody in our company who knows SQL Server in depth and cannot answer why something works today but doesn’t work tomorrow. So my dream came true and I was given the opportunity to start learning how SQL Server works full time. In 2011, I took 2 week class in NYC which didn’t teach me much, only how to pass SQL Server exams, and I did. The most valuable things I took from this class was that one day the teacher mentioned about Paul Randal and how great his blog was. I’ve started to read Paul’s blog and found enormous amount of useful information for myself. Then I found out that Paul and Kim are married and they teach together Immersion Events! So in 2013, I had a chance to take IE1 in Tampa and IE2 in Chicago, met them and SQLskills gang in person! What an amazing fun people! The best learning experience ever!

Also, I love animals, nature, and traveling. Almost all of my free time I devote to our local Humane Society. Love to go hiking, camping, kayaking, you name it! As far as traveling, all inclusive resorts or laying 7 days on the beach — not for me, Nome AK — yes please! Dreaming about Norway right now!

Congratulations to Denis – I’m looking forward to working with him over the next six months.

PS I’m often asked how to get into our mentoring program – it’s simple: attend one of our Immersion Events and get to know one of us, then ask and see if there’s a match that works.

New RunAsRadio podcast with me, Kimberly, Brent

At the end of every SQLintersection conference we have a closing Q&A session where we answer audience questions about anything to do with SQL Server. It’s very popular with attendees and we have a lot of fun on stage. Last week when we did it, our good friend Richard Campbell (of .Net Rocks! fame, which we’ve been on quite a few times), MC’d the session and we recorded everything as a RunAsRadio podcast. We’re going to do that every time we do a conference as it worked out really well and lets us share some of the conference with all of you.

The recording has now been published. It’s about an hour long and you can get it here.


Inside the Storage Engine: How are allocation unit IDs calculated?

It’s been a long time since I’ve written a post about pure internals, but every so often I get asked how an allocation unit ID is calculated from the m_objId and m_indexId fields that are stored in the header of every page.

When DBCC PAGE dumps a page header’s contents, it does the necessary calculations and metadata look-ups to be able to tell you the allocation unit ID, partition ID, relational object ID, and relational index ID. Basically everything prefixed with ‘Metadata:’ in the DBCC PAGE output below is NOT stored on the page itself:

Page @0x00000004ED8A2000

m_pageId = (1:445)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0xa000
m_objId (AllocUnitId.idObj) = 97    m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594044284928
Metadata: PartitionId = 72057594039304192                                Metadata: IndexId = 0
Metadata: ObjectId = 599673184      m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 8                         m_slotCnt = 1                       m_freeCnt = 8069
m_freeData = 121                    m_reservedCnt = 0                   m_lsn = (225:443:22)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1

The formula is as follows:

  • Take the m_indexId and left-shift by 48, giving value A
  • Take the m_objId and left-shift by 16, giving value B
  • AllocUnitId = A | B (where | is a logical OR operation)

Using the page above:

  • A = 256 << 48 = 72057594037927936
  • B = 97 << 16 = 6356992
  • AllocUnitId = 72057594044284928

You can do this using SQL Server using the POWER function as a left shift of X bits is the same as multiplying by 2-to-the-power-X:

SELECT 256 * CONVERT (BIGINT, POWER (2.0, 48)) | 97 * CONVERT (BIGINT, POWER (2.0, 16));

And then you can perform the various look-ups using sys.system_internals_allocation_units and sys.partitions like so:

	[a].[container_id] AS [Partition ID],
	[p].[object_id] AS [Object ID],
	[p].[index_id] AS [Index ID]
FROM sys.system_internals_allocation_units [a]
JOIN sys.partitions [p]
	ON [p].[partition_id] = [a].[container_id]
	[a].[allocation_unit_id] = 72057594044284928;
Partition ID         Object ID   Index ID
-------------------- ----------- -----------
72057594039304192    599673184   0

And you can see that the values match the DBCC PAGE output.

To convert from an allocation unit ID to what you should see in the DBCC PAGE output:

  • m_indexId = AllocUnitId >> 48
  • m_objId = (AllocUnitId – (m_indexId << 48)) >> 16

The T-SQL for this involves floating point math as we need to use the reciprocal of POWER:

DECLARE @alloc BIGINT = 72057594044284928;

SELECT @index =
		CONVERT (FLOAT, @alloc)
			* (1 / POWER (2.0, 48)) -- right shift, reciprocal of left shift
		CONVERT (FLOAT, @alloc - (@index * CONVERT (BIGINT, POWER (2.0, 48))))
			* (1 / POWER (2.0, 16)) -- right shift, reciprocal of left shift
	) AS [m_objId],
	@index AS [m_indexId];


m_objId              m_indexId
-------------------- --------------------
97                   256

An example of when you might use this information/code is during programmatic analysis of a corrupt database that DBCC CHECKDB cannot process to allow you to extract data as a last resort.


Limiting error log file size in SQL Server 2012

It’s quite well known that you can optimize error log file management using SSMS to change the maximum number of error log files to 99 and running sp_cycle_errorlog every day at midnight (see this post on my old SQL Server Magazine blog for graphics). This works in all current versions of SQL Server.

One thing that hasn’t been possible before is setting the maximum size of individual SQL Server error logs before SQL Server triggers cycling to a new error log. Well it turns out that in SQL Server 2012 you can!

While in my post-con workshop at SQL Intersection in Las Vegas last week, Jan Kåre Lokna (a former Immersion Event attendee from Norway) discussed some code he’s been experimenting with and I just heard from him that he got it to work.

The following code will set the maximum size of each error log file to 5MB on a SQL Server 2012 instance:

USE [master];

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
    N'ErrorLogSizeInKb', REG_DWORD, 5120;

I’ve tested this on SQL Server 2008 R2 and it does not work there, so the registry key must be new for SQL Server 2012. This is really useful to protect against gigantic error log files caused by repeated crash dumps, for instance when messing around with DBCC WRITEPAGE :-)

You can read a more in-depth description on Jan’s blog here.

2014 Australia classes open for registration

We’ve finalized the details of our two classes in Australia next year and they’re open for registration. Kimberly and I will be presenting our popular IE1: Immersion Event on Internals and Performance back-to-back in Sydney and Melbourne. You can read more about the locations and costs in:

These are the only courses we’ll be presenting outside of the US in 2014.

We really hope to see you there!

New online course: Why Physical Database Design Matters

Kimberly’s first Pluralsight online training course went live today: SQL Server: Why Physical Database Design Matters

It’s 4 hours long and is the first in a multi-part series on performance.

The modules in this course are:

  • Introduction
  • Data Types and Row Size
  • Data Types and Index Size
  • Data Types and Query Performance

You can get to the course here.

Pluralsight starts at US$29/month, for more than 80 hours of SQLskills SQL Server training (growing all the time) and more than 770 total developer and IT Pro courses(also growing all the time).