Home > Grade of the Steel, SQL Server > Bottleneck Diagnosis on SQL Server – New Scripts

Bottleneck Diagnosis on SQL Server – New Scripts

Finally, I have found some time with my good colleagues at Fusion-io to work on some of my old SQL Scripts.

Our first script queries the servers for wait stats – a very common task for nearly all SQL Server DBAs. This is the first thing I do when I meet a new SQL Server installation and I have generally found that I apply the same filters over and over again. Because of this, I worked with Sumeet Bansal to standardise our approach to this.

You can watch Sumeet introduce the script in this YouTube video: http://youtu.be/zb4FsXYvibY. A TV star is born!

We have already used this script at several customers in a before/after Fusion-io situation. As you may know, the tuning game changes a lot when you remove the I/O bottleneck from the server.

Based on our experiences so far, I wanted to share some more exotic waits and latches we have been seeing lately.


This is not exotic at all you may say. In fact, it is probably the most common wait type in most SQL Server installations.

Here is what you may not know: This wait does NOT measure the I/O latency of the log drive. To get that, you have to query the log file in sys.dm_io_virtual_file_stats – which you may notice often reports a lower wait time than WRITELOG. Just something to think about when measuring log latency as we see a larger and large discrepancy between this wait and the actual I/O wait.

I have provided more details on this in my SQLBits presentations:


We are seeing this latch wait a lot, especially in data warehouse environments. It is related to parallelism. While lowering MAXDOP is one way to remove it – it is not always the optimal way and you may not get the most out of your hardware if you do.

We typically see this wait happening in two different situations:

  1. When doing table scans
  2. When running INSERT…SELECT as part of an ETL process

In both cases, the wait indicates that you are hitting some form of scalability issue. My friend Henk van der Valk blogged about how to work around the table scan problem:

Henk used another type of non volatile storage: battery backed DRAM; the lesson he learned is equally applicable to Fusion-io.

I have written about optimising INSERT…SELECT in this whitepaper:

    There is probably another blog entry to be written one day.


Typically, we see this wait when a filegroup in a database doesn’t have enough files allocated. It is easy to diagnose: just look for PFS pages in the resource_description. Yes, this applies to user databases too, not just tempdb.

How many data files you should have in a database or filegroup has been debated a lot in the SQL Server community, even by me. When running on spindles (or spinning rust as we like to call it in Fusion-io) the answer is a big “it depends”. But on flash our experience and our benchmarking has shown that more is better, especially under high concurrency workloads.


Above: Runtime and PAGELATCH_UP waits when adding more data files. Notice the sharp drop from 1 to 8 and that the runtime curve continue to drop as you add more files

There is enough material for another blog entry (and for some nice new scripts) on this subject.


Another common wait. Typically, this occurs in two situations:

  1. When you issue UPDATE statements a lot against small tables
  2. When you run many concurrent INSERT statements into a table that has an index on an IDENTITY or NEWSEQUENTIALID column
  3. When using a table as the backing store for a message queue

Ad 1) The solution to situation 1 is normally to “pad” the table with a CHAR(4000) NOT NULL column to make every single row fit only one page. However, this is only viable on small tables – but those are the ones that tend to see this effect.

Ad 2) For high scale systems powered by flash memory, our recommendation for generating keys goes directly against the typical approach taken by the SQL Server community. DO NOT use IDENTITY, SEQUENCER or NEWSEQUENTIALID to generate keys. In fact, we have found that the old and despised  NEWID is significantly faster than NEWSEQUENTIALID. The numbers speak for themselves, here is the runtime of a workload that inserts 160M rows:


Above: Myth busting “best practises” for generating keys.

There is a very interesting solution to the key generation problem that has been blogged about by another friend of mine, here is the link to Rick’s very elegant key generator:

Ad 3) I have previously blogged about the design pattern that solves this problem


As we install more and more Fusion-io into SQL Servers and remove the I/O problem, we continue to learn about new bottlenecks in typical customer workloads and how to solve them. Through our understanding of the full application stack, we are helping customers get more out of their new hardware. I run a mentoring program inside the company where our field unit, always eager for more knowledge, learn how to tune even very complex problems in SQL Server.

In Fusion-io, we live every day in the world beyond the I/O problem, and we are privileged to be the first to witness the disruption NAND flash is creating in the market. We are especially excited about Project Hekaton and how that is going to change the game once again. As we explore Hekaton, we will adjust our guidance. The SQL community has a lot to look forward to from Microsoft and we look forward to continuing to provide acceleration in this space.

I would like to thank my colleagues in Fusions Data Propulsion Labs for helping making all this come together – especially Sumeet Bansal the new face of our SQL Server YouTube series.

  1. John Alan
    April 15, 2013 at 09:33


    Is there a link to the scripts mentioned above?

    • Thomas Kejser
      April 15, 2013 at 12:15

      Coming up very soon 🙂

  2. April 18, 2013 at 19:38

    “But on flash our experience and our benchmarking has shown that more is better, especially under high concurrency workloads.” I like it already 🙂 I always get concerned when I see recommendations for some super-duper storage solution that somehow removes the queuing/locking concerns at every level from database page latches to storage driver to to storage media. You don’t need more lanes than you need… but if you need speed you probably need more than one and they probably have to be balanced.

    • Thomas Kejser
      April 19, 2013 at 09:03

      Yep, more lanes, especially if they have affinity, is generally a good thing for scale. Especially in these days where cores are plentiful, but single threaded clock cycles are hard to get.

  3. April 18, 2013 at 20:15

    Thinking about this a bit more. The ultra-low latency and the absence of rotational or actuator latency remove most of the concerns around multiple database files on the same LUN, and interleaved file contents on HDD media. Similarly, concerns about extent fragmentation get lower – the benefit that HDD gets from sequential IO disappear.
    But I don’t think index fragmentation is completely removed from consideration. Rather, it probably moves the results of (reorg + stats update with up to 100% sample) to the same level as results of online index rebuild. Both conserve flash storage and server memory by reaching fill_factor in most of the blocks, and both update stats and trigger plan recompiles. But, since extent fragmentation isn’t as much of a performance liability for flash storage, the reorg with stats update may be just as good in almost all cases, with less database file and transaction log file write load.
    If my thinking about that is correct, the fusion io storage can be managed based on projected database data growth, instead of padded to handle the online index rebuild of the largest index or largest set of indexes for a given table.
    The lower level of transaction log activity of reorg vs rebuild will be good for its own sake, but also because of the growing interest in Always On replication and the heavy log activity of index rebuilds in full recovery mode.

    • Thomas Kejser
      April 19, 2013 at 09:12

      The real question is: why do you even bother defragging/rebuilding?

      Yes, there is a space concern if most pages are empty. But if your most common pattern is index seeks and small range scans (OLTP in other words) – why bother?

  4. May 6, 2013 at 17:53

    Is this question for me? “why do you even bother defragging/rebuilding?”
    I think the easiest answer is that I am primarily concerned with high concurrency ETL and batch workloads. My focus systems and workloads don’t completely conform to a data warehouse model, but they are much closer to data warehouse model than OLTP model. And almost none of these systems is on dedicated storage, so to the extent that they can increase transfer size (on spinning or flash media) and increase locality (on spinning media to reduce actuator movement), these systems become better neighbors.

  5. Thomas Kejser
    May 8, 2013 at 08:35

    Hi SQL.Sasquatch

    Increasing transfer size is not always a good idea. For flash, the only effect you will have is to increase the latency of the transfer (since you need to move the data over the bus). This blocks the requesting thread longer and the tradeoff in saved IOPS may not be worth that.

    On spinning media, sure… Go ahead and do chunky transfer (in fact, I would have liked SQL Server to support 512K page sizes for this purpose)

  6. May 10, 2013 at 21:11

    Good point about the difference between maximizing transfer size between hard disk and flash. We’ll have to watch it real close.

    • Thomas Kejser
      May 21, 2013 at 15:52

      Yep – I think a new flash optimised storage engine would greatly change the picture here. The OSS community is already exploring this

  7. CDK
    May 13, 2013 at 15:21

    Are these scripts available yet? (As requested by John above)

  8. May 24, 2013 at 16:06

    Great write up Thomas! Any update on when the scripts will be public available? Very interested in getting my hands on them. 🙂

  9. John Alan
    May 30, 2013 at 08:48

    Thanks Thomas.
    I’ve just executed it on a 2008R2 instance.
    Before closing the SSMS query window it asks what to do with the open transaction (!?)

    • Thomas Kejser
      May 31, 2013 at 11:59

      Which version of the script? The 2005 or 2008?

      • John Alan
        June 1, 2013 at 11:59

        Tried both 2008R2 and 2012. Adding a ‘rollback’ at the end fixes it, but are they supposed to be opening a tx?

        Your blog rocks by the way. I’d place it in the Top 10 SQL blogs, due to depth and dealing with subjects that nobody else does.

  10. CDK
    May 30, 2013 at 08:52

    Thanks for the script! Also, for the great blog. 🙂 Have added it to my RSS feed reader!

  11. John W
    June 13, 2013 at 02:26

    The implicit transaction is due to the use of SET ANSI_DEFAULTS ON, which sets IMPLICIT_TRANSACTIONS to ON. The creation of the first temp table triggers an implicit transaction.

    Two items I noted while looking at this:

    1) I think that there is a missing wait type for filestream – there shoudl be one for FSAGENT.
    2) The #ignore_res table should be dropped at the end of the script.

    Minor nitpicks on an extremely handy utility. Thank you.

    • Thomas Kejser
      June 17, 2013 at 09:11

      Thanks John. Will correct in next update

  12. Bill
    September 25, 2013 at 05:58
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s