Who is overriding MAXDOP 1 on the instance?

In Monday’s newsletter I discussed an email question I’d been sent and promised to blog about it. The question was “how can latch waits for ACCESS_METHODS_DATASET_PARENT occur when the instance MAXDOP is set to 1?” This shouldn’t happen, as that latch is only used for coordination between threads performing a parallel table/index scan. However, just […]

Investigating the proportional fill algorithm

This is something that came up recently on the Microsoft Certified Master DL, and is something I discuss in our IEPTO1 class because of the performance implications of it, so I thought it would make an interesting post. Allocation Algorithms The SQL Server Storage Engine (SE) uses two algorithms when allocating extents from files in […]

Capturing spinlock statistics for a period of time

This is a quick post to blog a script that allows spinlock statistics to be captured for a defined period of time (as I need to reference it in my next post). Enjoy! Example output (trimmed to fit here):

Announcing the comprehensive SQL Server Wait Types and Latch Classes Library

It’s finally ready! For the last two years, I’ve been working on-and-off on a new community resource. It was postponed during 2015 while I mentored 50+ people, but this year I’ve had a bunch of time to work on it. I present to the community a comprehensive library of all wait types and latch classes that […]

Updated sys.dm_os_waiting_tasks script to add query DOP

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] A question came up in class today about easily seeing the degree of parallelism for parallel query plans, so I’ve updated my waiting tasks script to pull in the dop field from sys.dm_exec_query_memory_grants. I’ve also added […]

SQL Server health check by SQLskills: Spring discount offer

Through the end of July, we’re offering a complete single-instance health-check for a flat fee of US$2,500…  One of the most popular services we provide to new clients is a SQL Server Health Check, whether the new client is a Fortune-25 company or a small company with only a handful of employees. There’s a lot […]

On index key size, index depth, and performance

In my Insider newsletter a couple of weeks ago, I discussed how index fragmentation is often considered when designing indexes, but index depth often isn’t. In the newsletter I said I’d do a more comprehensive blog post with some data, so this is it. Fanout and Index Depth The index depth is determined by the fanout of […]

New course: Index Fragmentation Internals, Analysis, and Solutions

My latest Pluralsight course has been published! It’s called SQL Server: Index Fragmentation Internals, Analysis, and Solutions and is just over 2.5 hours long. The modules are: Introduction Index Structure and Index Uses Types of Index Fragmentation Causes of Index Fragmentation Detecting Index Fragmentation Avoiding Index Fragmentation Removing Index Fragmentation Check it out here. We now […]

New course: part 2 of Kimberly’s Optimizing Stored Procedure Performance

Kimberly’s latest Pluralsight course is live! This is part 2 of her very popular course: SQL Server: Optimizing Stored Procedure Performance. The course is more than 3 hours long and has the following modules: Introduction Session Settings Session Settings (section 2) Special Considerations Troubleshooting Techniques Check it out here. We now have more than 130 hours […]

Identifying queries with SOS_SCHEDULER_YIELD waits

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] One of the problems with the SOS_SCHEDULER_YIELD wait type is that it’s not really a wait type. When this wait type occurs, it’s because a thread exhausted its 4ms scheduling quantum and voluntarily yielded […]

New SQLskills Pluralsight courses

We’ve released a couple of new Pluralsight courses recently: The first is the conclusion of Glenn’s DMVs course series, SQL Server 2014 DMV Diagnostic Queries – Part 3, based on the very popular set of DMV queries he publishes every month. The second course is Jonathan’s long-awaited SQL Server: Change Data Capture course. The short abstract is: Learn […]

Updated sys.dm_os_waiting_tasks script

Over the holidays I was playing around with parallelism and updated my sys.dm_os_waiting_tasks script to add in the scheduler distribution of the waiting threads. Here it is for your use. Enjoy! (Note that ‘text’ on one line does not have delimiters because that messes up the code formatting plugin):

Problems from having lots of server memory

A month ago I kicked off a survey asking how much memory is installed on your largest server that’s running SQL Server. Thank you to everyone that responded. Here are the results:                       The “other” values are: 3 more for the ‘128 GB or more, […]

Delayed Durability in SQL Server 2014

One of the cool new features in SQL Server 2014 is delayed durability (available in all Editions), which is described in detail in Books Online here. I think I’m going to see a lot of people turn this on, as you can get a profound increase in transaction throughput with the right workload. However, I also […]

Capturing IO latencies for a period of time

In both my wait statistics pre-conference workshops at the PASS Summit and SQLintersection I promised to do a bunch of blog posts. The second one on the list is a simple script to allow you to capture all the reads, writes, and I/O latencies that occurred over a period of time. The script does the […]

Capturing wait statistics for a period of time

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] In both my wait statistics pre-conference workshops at the PASS Summit and SQLintersection I promised to do a bunch of blog posts. The first one on the list is a simple script to allow […]

New SQLskills Pluralsight courses

We’ve just released two new courses on Pluralsight: The first new course is the conclusion of Joe Sack’s query-tuning course: SQL Server: Common Query Tuning Problems and Solutions – Part 2. The second new course is Kimberly’s long-awaited SQL Server: Optimizing Stored Procedure Performance. It’s more than seven hours long and the abstract is: When using stored […]

Revamped IE1 and IE2 classes open for registration in October

I’ve just released our final US classes this year for registration! We’ve revamped our IE1 and IE2 classes so they’re now both focused on performance and together they form a comprehensive, 10-day performance tuning and optimization course. IE1 is now IEPTO1: Immersion Event on Performance Tuning and Optimization – Part 1 (see here for the […]

Most common wait stats over 24 hours and changes since 2010

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] Back in February I kicked off a survey asking you to run code that created a 24-hour snapshot of the most prevalent wait statistics. It’s taken me a few months to provide detailed feedback to […]

Causes of IO_COMPLETION and WRITE_COMPLETION SQL Server wait types

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] In many of the sets of wait statistics I’ve been analyzing, the IO_COMPLETION and WRITE_COMPLETION waits show up (but never as the most prevalent wait type). The official definition of these wait types are: […]

A cause of high-duration ASYNC_IO_COMPLETION waits

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] In some of the wait statistics data I’ve been analyzing, some servers have been showing very long ASYNC_IO_COMPLETION waits, which I had a hunch about but wanted proof. The official definition of ASYNC_IO_COMPLETION is […]

How to determine what causes a particular wait type

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] Wait statistics, as you know, are one of my favorite things to do with SQL Server, along with corruption, the transaction log, and Kimberly (but not necessarily in that order :-) One of the […]

What is the most worrying cause of log growth (log_reuse_wait_desc)?

Two weeks ago I kicked off a survey that presented a scenario and asked you to vote for the log_reuse_wait_desc value you’d be most worried to see on a critical database with a 24×7 workload. Here are the results: Another very interesting spread of responses – as always, thanks to everyone who took the time to […]

What is the most worrying wait type?

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] Two weeks ago I kicked off a survey that presented a scenario and asked you to vote for the wait type you’d be most worried to see after a code roll-out to production. Here […]

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 […]

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 […]

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 Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our Immersion Event for The Accidental/Junior DBA, which we present several times each year. If you know someone who would benefit from […]

DBCC CHECKDB scalability and performance benchmarking on SSDs

Back in February I ran a bunch of performance tests of DBCC CHECKDB on SSDs, to evaluate the effects of degree-of-parallelism (DOP) and various options and traceflags, and now I’m finally getting around to presenting the results. Make sure to also read the recent post where I talk about the detrimental effect of computed-column indexes on […]

DBCC CHECKDB performance and computed-column indexes

[Edit 2016: The team ‘fixed’ the problem in SQL Server 2016 by skipping consistency checking these indexes unless WITH EXTENDED_LOGICAL_CHECKS is used.] It’s no secret that DBCC CHECKDB has some performance quirks based on the schema of the database being checked and various kinds of corruptions. I was recently doing some scalability testing of DBCC […]

Make life easier on yourself, get a baseline!

At the SQL Connections conference earlier this month, at the start of my talk on Making SQL Server Faster, Part 1: Simple Things, I talked about the importance of having a performance baseline so you can measure the effect of any changes made to your environment. A month ago I kicked off a survey about […]

Survey: do you have a baseline?

For the last few weeks I've been thinking about performance baselines, mostly because I've been producing Erin's new Pluralsight course on Benchmarking and Baselining. It got me thinking that it would be interesting to know how many of you are using baselines, and what for, and if not, why not. So I present to you […]

Most common latch classes and what they mean

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] Back in May I kicked off a survey about prevalent latches on SQL Server instances across the world (see here). It’s taken me quite a while to get around to collating and blogging about […]

Understanding data vs log usage for spills in tempdb

Earlier this morning I noticed a discussion on the SQL MCM distribution list (that all the original MCM instructors are part of) that was trying to make sense of a huge disparity between tempdb data file usage and log file usage. I explained the answer and thought I'd share it with you all too. The […]

Survey: most prevalent latch waits (code to run)

I first started blogging about latches and some of the deeper parts of SQL Server internals last year (see Advanced performance troubleshooting: waits, latches, spinlocks) and now I’d like to pick up that thread (no scheduling pun intended :-)) and blog some more about some of the common latches that could be a performance bottleneck. […]

Cool free tool to parse and analyze SQLIO results

During every one of our Immersion Events, we designate Thursday evening as 'open mic' night where anyone can do a 15-minute presentation on anything they want (to do with SQL Server) to the class. We usually have 4 or 5 people who entertain us with interesting talks, and our recent classes in Chicago were no […]

Max server memory configuration survey results

A long time ago, in a galaxy far, far away I kicked off a survey about memory configuration. Actually it was back at the start of January and I've been terribly remiss about posting the survey results! I was interested in how the setting of Max Server Memory (which controls the maximum size of the […]

Code to list potential cluster key space savings per table

Back in January I posted the results of the cluster key size survey I ran in 2011 and explained how the larger the cluster key is on your table, the more space is being wasted in all the nonclustered index rows. Check it out if you haven't already. I've finally put together the code that […]

How cluster key size can lead to GBs of wasted space

Back in November I kicked off a survey that had you run some code to get some details about your cluster keys, nonclustered indexes, and table size. I got results from more than 500 systems across the world, resulting in 97565 lines of data – thanks! The purpose of the survey is to highlight one […]

Survey: wasted space from cluster keys (code to run)

Continuing with my "index health" series, I've got another piece of code for you to run. This time I'm interested in the number of columns in your clustered indexes and the consequent amount of nonclustered index space used by the clustered index keys. Again, you're going to be really interested to see the results on […]

Code to list index counts per table

Yesterday I blogged about how having too few or too many nonclustered indexes can be a big problem for performance (see here). Today I'm posting some code you can run which will print out the number of indexes for each table in each database on an instance. I made it print a result set per […]

Over and under indexing – how bad is it out there?

Back at the start of August I kicked off a survey (see here) that gave you some code to run to produce an aggregate list of the number of tables on your server with different numbers of nonclustered indexes. I got back results from more than 1000 servers across the world – a big thank you […]

Page Life Expectancy isn’t what you think…

Updated 5/11/2015… There’s a lot of controversy about the Buffer Manager performance object counter Page Life Expectancy – mostly around people continuing to quote 300 as a threshold for starting to worry about there being a problem (which is just utter nonsense these days). That’s far too *low* to be the point at which to […]

Capturing wait stats for a single operation

This is a performance tuning post that's been on my to-do list for quite a while. Wait stats analysis is a great way of looking at the symptoms of performance problems (see my Wait Stats category for more posts on this) but using the sys.dm_os_wait_stats DMV shows everything that's happening on a server. If you […]

New whitepapers on latches and spinlocks published

Over the last few months I’ve been blogging occasionally about some pretty deep performance tuning topics, namely latches and spinlocks (see my blog categories Wait Stats, Latches, and Spinlocks). Ewan Fairweather and Mike Ruthruff of the SQLCAT team have written some really excellent whitepapers on interpreting and dealing with latch and spinlock issues, which I […]

MAXDOP configuration survey results

A month ago I kicked off a survey about MAXDOP setting – see here for the survey. I received results for 700 servers around the world! Here they are:   The X-axis format is X-Y-Z, where X = number of cores, Y = number of NUMA nodes, Z = MAXDOP setting. I didn't differentiate between […]

Performance issues from wasted buffer pool memory

Back in April I kicked off a survey where I asked you all to send me some information about your buffer pools – how much memory is being used for data file pages and how much of that memory is storing empty space. I got back data from 1394 servers around the world – thanks! […]

Survey: cores vs. MAXDOP

I have a few surveys to editorialize but I'd like to kick off another one where you have to run a bit of code and send the results. For this one I'm interested in how you have your system configured for parallelism, given the number of processor cores and NUMA nodes configured. For systems using […]

Benchmarking: Multiple data files on SSDs (plus the latest Fusion-io driver)

It’s been a long time since the last blog post on SSD benchmarking – I’ve been busy! I’m starting up my benchmarking activities again and hope to post more frequently. You can see the whole progression of benchmarking posts here. You can see my benchmarking hardware setup here, with the addition of the Fusion-io ioDrive Duo […]

SOS_SCHEDULER_YIELD waits and the LOCK_HASH spinlock

Continuing my series on advanced performance troubleshooting – see these two posts for the scripts I’ll be using and an introduction to the series: Wait statistics, or please tell me where it hurts Advanced performance troubleshooting: waits, latches, spinlocks In this blog post I’d like to show you an example of SOS_SCHEDULER_YIELD waits occurring and […]

Survey: how much server memory is being wasted? (code to run)

In this survey I'd like you to run some code and then send me the results (and I'm sure someone will put together a PowerShell script to make it easy to run on multiple instances). I want to know how much of your precious server memory is being wasted storing empty space on data file […]

Advanced SQL Server performance tuning

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] It’s all very well having whizz-bang 3rd-party performance monitoring and troubleshooting tools, but sometimes you have to get deeper into what’s going on with SQL Server than any of these tools can go. Or […]

How to download a sqlservr.pdb symbol file

I just had to figure out how to do this so I figured a quick blog post is in order to save other people time in future. If you ever need to use windbg to debug a SQL Server crash dump, or you want to capture call stacks using extended events (e.g. when debugging excessive […]

How to examine IO subsystem latencies from within SQL Server

Edit: I blogged an update script to capture a snapshot of I/O latencies in this blog post. Over the last few months I’ve been lecturing at classes and conferences about getting SQL Server’s view of the I/O subsystem and what latencies it is experiencing, so time for a blog post to help everyone else. Most […]

Survey results: Common causes of performance problems

A couple of week ago I kicked off a survey about common causes of performance problems – see here for the survey. Firstly I asked what was the root cause for the most recent performance problems you looked at – here are the results:   Secondly I asked what you think the overall most common […]

Survey: have you ever used these DMVs?

In this week's survey I've got four mini-surveys for you, all to do with in-depth performance analysis. I'd like to know whether you've ever used each of four DMVs that look progressively more deeply into the workings of the database engine. I'll report on the results in a week or two and start blogging about […]

Survey: what are the most common causes of performance problems?

This survey follows on from the survey results I just blogged about, and is particularly apt given we're here in Dallas this week teaching our Immersion Event on Performance Tuning. I'm interested to know the root causes of your last few performance problems. I know that we all debug multiple performance problems every week, so […]

Query plan analysis first steps

About a month ago I kicked off a survey asking what you look for when first analyzing a plan for a poorly performing query. You can see the original survey here. Here are the survey results:   The "Other" values are as follows: 13 x "Most expensive as percentage of total cost of batch" 7 […]

Ever had poor performance saving files in SSMS over slow network?

I just about beat my fists against my laptop this afternoon while updating a couple of demo scripts for our Performance class in Dallas this week. Well, not quite because I really like my new laptop. But you get the idea. I'm working over a 3G USB wireless connection and every time I want to […]

Survey: how is your tempdb configured?

In this week's survey, I want to know how you've got tempdb configured compared to the number of processor cores SQL Server thinks it has. I'll correlate, analyze, and present the results like the log file survey I did last year where I got results for 17000 databases. The code I'd like you to run […]

Importance of network latency when using database mirroring

Last week I kicked off a survey about network latencies and database mirroring. See here for the original post. Here are the results of the survey:   I was really interested to see whether the proportion of people doing asynchronous mirroring became higher as the network latency increased. Although this isn't a statistically valid sampe […]

Survey: what’s your plan for a plan?

In my survey for this week I'm interested in what you look for first when analyzing a query plan. I'll report on the results around mid-February. Thanks! PS Post comments are disabled to avoid skewing the results.

TechNet Magazine: January 2011 SQL Q&A column

The January edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Diagnosing I/O subsystem bottlenecks Capacity planning for transaction logs Why there are no non-logged operations in user databases Check it out at http://technet.microsoft.com/en-us/magazine/gg552991.aspx.

Adventures in query tuning: non-seekable WHERE clause expressions

I've been doing a lot of performance tuning work over the last couple of months and this weekend found something that's very pervasive out in the wild. Kimberly was helping me optimize a gnarly query plan and spotted something in the code I hadn't noticed that was causing an index scan instead of an index […]

Results of CPU power saving survey

At the end of December I showed you how to discover if power saving is enabled on your server, which can lead to variable and often degraded performance. I also included a survey to let me know what you found after running the free CPU-Z tool on your servers. See here for the original post. […]

Are your CPUs running slowly? Tool tip and survey

(Yes, I know I haven't editorialized the last survey on What's in a Job Title – I will in the New Year.) Over the last couple of weeks I've signed up a bunch of new customers for maintenance/ops audits and general perf work and I've had all of them check whether power saving mode is […]

Wait statistics, or please tell me where it hurts

[Edit 2016: Check out my new resource – a comprehensive library of all wait types and latch classes – see here.] How many times have you walked up to a SQL Server that has a performance problem and wondered where to start looking? One of the most under-utilized performance troubleshooting methodologies in the SQL Server […]

Survey: what is the highest wait on your system?

I've recently been creating some content about wait stats analysis and I think it would be really interesting to see what kind of waits people are seeing out there in the wild. Hopefully it'll also introduce a bunch of people to the waits-and-queues performance troubleshooting methodology and how it can be really useful to them. […]

Benchmarking: Introducing SSDs (Part 3: random inserts with wait stats details)

Last time I posted about SSDs I presented the findings from sequential inserts with a variety of configurations and basically concluded that SSDs do not provide a substantial gain over SCSI storage (that is not overloaded) – see this blog post for more details. You can see my benchmarking hardware setup here, with the addition of […]

Survey results around purchase and use of SSDs

Back at the start of July I kicked off a survey around your plans for SSDs (see here) and now I present the results to you. There's not much to editorialize here, but the numbers are interesting to see.   The "other" answers were (verbatim): 3 x 'have bought and am trying them out' 3 […]

Benchmarking: Introducing SSDs (Part 2: sequential inserts)

Over the last month we've been teaching in Europe and I haven't had much time to focus on benchmarking, but I've finally finished the first set of tests and analyzed the results. You can see my benchmarking hardware setup here, with the addition of the Fusion-io ioDrive Duo 640GB drives that Fusion-io were nice enough […]

Benchmarking: do multiple data files make a difference?

Many times I'm asked whether having multiple data files can lead to an improvement in performance. The answer, as with all things SQL (except concerning auto-shrink) is a big, fat "it depends." It depends on what you're using the database for, and the layout of the files on the IO subsystem, and the IO subsystem […]

Benchmarking: Introducing SSDs (Part 1b: not overloaded log file array)

In the previous post in the series I introduced SSDs to the mix and examined the relative performance of storing a transaction log on an 8-drive 7.2k SATA array versus a 640-GB SSD configured as a 320-GB RAID-1 array. The transaction log was NOT the I/O bottleneck in the system and so my results showed […]

Benchmarking: Introducing SSDs (Part 1: not overloaded log file array)

Well it's been almost 6 weeks since my last benchmarking blog post as I got side-tracked with the Myth-a-Day series and doing real work for clients :-) In the benchmarking series I've been doing, I've been tuning the hardware we have here at home so I can start running some backup and other tests. In the last […]

Adventures in query tuning: unexpected key lookups

I'm starting a new blog category to talk about some of weird and confusing stuff I see while query tuning. First up is the case of the unexpected Key Lookup (Clustered) in a query that looks like it should be covered. This is a follow on from the post Missing index DMVs bug that could […]

Missing index DMVs bug that could cost your sanity…

Here's yet another reason to be very careful when using the missing index DMVs… There's a bug in the missing index DMVs that could end up causing you to knock your head against a brick wall and question your sanity. I know I did. The bug is this: the missing index code may recommend a nonclustered […]

A SQL Server DBA myth a day: (28/30) BULK_LOGGED recovery model

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) The BULK_LOGGED recovery model continues to confuse people… Myth #28: […]

A SQL Server DBA myth a day: (26/30) nested transactions are real

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) Nested transactions are an evil invention designed to allow developers […]

Survey: how are your transaction logs configured?

Over the last few months there's been some noise (mostly of my making) on Twitter about the number of VLFs in transsction logs. Given the large numbers of people who read the blog and follow me on Twitter, I thought it would be very interesting to collect some statistics from a few hundred of you […]

A DBA myth a day: (13/30) you cannot run DMVs when in the 80 compat mode (T-SQL Tuesday #005)

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) This blog post is part of two series – my […]

A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) This is a myth I hear over and over and […]

Benchmarking: 1-TB table population (part 5: network optimization again)

Blog posts in this series: For the hardware setup I'm using, see this post. For an explanation of log growth and its effect on perf, see this post. For the baseline performance measurements for this benchmark, see this post. For the increasing performance through log file IO optimization, see this post. For the increasing performance through separation […]

Inside sys.dm_db_index_physical_stats

Way back in the mists of time, at the end of the last century, I wrote DBCC SHOWCONTIG for SQL Server 2000, to complement my new invention DBCC INDEXDEFRAG. I also used to wear shorts all the time, with luminous orange, yellow, or green socks. Many things change – I now have (some) dress sense, […]

Benchmarking: 1-TB table population (part 4: network optimization)

Blog posts in this series: For the hardware setup I'm using, see this post. For an explanation of log growth and its effect on perf, see this post. For the baseline performance measurements for this benchmark, see this post. For the increasing performance through log file IO optimization, see this post. For the increasing performance through separation […]

Using diskpart and wmic to check disk partition alignment

(Be sure to join our community to get our monthly newsletter with exclusive content, advance notice of classes with discount codes, and other SQL Server goodies!)  A while ago I blogged about disk partition alignment, and how the default alignment of 31.5Kb on Windows Server 2003 can lead to enormous I/O performance problems (see Are […]

Benchmarking: 1-TB table population (part 3: separating data and log files)

Blog posts in this series: For the hardware setup I'm using, see this post. For the baseline performance measurements for this benchmark, see this post. For the increasing performance through log file IO optimization, see this post. In the previous post in the series, I optimized the log block size to get better throughput on the transaction […]

Benchmarking: 1-TB table population (part 2: optimizing log block IO size and how log IO works)

(For the hardware setup I'm using, see this post. For the baseline performance measurements for this benchmark, see this post.) In my previous post in the series, I described the benchmark I'm optimizing – populating a 1-TB clustered index as fast as possible using default values. I proved to you that I had an IO bottleneck […]

Benchmarking: 1-TB table population (part 1: the baseline)

(For the hardware setup I'm using, see this post.) As part of my new benchmarking series I first wanted to play around with different configurations of data files and backup files for a 1-TB database to see what kind of performance gains I can get invoking the parallelism possible when backing up and restoring the database. […]

Interesting case of watching log file growth during a perf test

I'm running some performance tests on the hardware we have (more details on the first of these tomorrow) and I was surprised to see some explosive transaction log growth while running in the SIMPLE recovery model with single row insert transactions! Without spoiling tomorrow's thunder too much, I've got a setup with varying numbers of […]

Turning off the ghost cleanup task for a performance gain

I've blogged about ghost records and the ghost cleanup task a couple of time before (the only place it is really explained AFAIK), but one of my fellow MVPs was asking me some questions about it today for a customer of theirs and couldn't find the trace flag to turn it off. My previous blog […]

Interesting 2008 partitioned view perf bug fixed in SP1 CU4

This is an interesting performance bug concerning a broken query optimizer rule in 2008. Thanks to Dan Shargel (Twitter) for sending me info on this and letting me use some of the stuff he sent. The scenario involves using MIN or MAX in a query against a partitioned view. In 2005 the query plan includes […]

Benchmarking hardware setup

It's been a few weeks since my last posts but I've got a bunch in the pipeline coming up. Firstly, I've got it together to start using the hardware we got a while back. I'm going to be doing some benchmarking, perf testing and playing with various HA technologies, and of course blogging a bunch about what […]

Tracking expensive queries with extended events in SQL 2008

As part of the 2008 DBA class we’re teaching down here in Melbourne, I did a demo of using predicates and file targets with extended events, so I want to blog the script for people to play with. For background info on extended events see: My TechNet Magazine article from 2008: Advanced Troubleshooting with Extended […]

Which index will SQL Server use to count all rows

This is a question that came up yesterday on Twitter: will SQL Server always do a table scan to find the result of SELECT COUNT (*) FROM mytable? The answer is no. The query processor will use the index with the smallest number of pages – i.e. with the least I/O cost. Let me quickly […]

Survey: how do you create and maintain statistics

This survey is based on a suggestion from Jonathan Kehayias (blog/Twitter). In this survey I'm asking two questions: When you're designing/implementing a database, do you manually create statistics or let SQL Server create them automatically? How do you maintain statistics? Manually or letting SQL Server do it for you? Or both?  I'll probably collaborate with Kimberly on […]

Important considerations when performance tuning

In last week's survey I asked what you think is the most important thing when it comes to performance tuning, if you *had* to choose one – see here for the survey. Here are the results as of 6/7/09. Now of course you're all calling 'foul' because I didn't put an 'it depends' option on […]

Weekly survey: what’s the most important thing when performance tuning?

This week's survey is going to provide some fun debating topics. I'd like to know what the number 1 thing is that YOU go after when performance tuning – what do you think is most important? (Assuming you're not just randomly walking up to a server to do some perf tuning – there's an actual […]

Investigating locking and deadlocking with %%lockres%%

I’ve just read a very good, very deep, and very interesting blog post by James Rowland-Jones. In the post, James investigates some locking issues using a variety of means and explains about the undocumented %%lockres%% function with you can use to figure out what the wait resource will be for individual table rows (basically the […]

An example of a nasty cluster key

I'm teaching a class this week on database maintenance, for DBAs inside Microsoft. One of the things we're discussing today is index fragmentation and how poor cluster key choice can lead to page splits, poor performance, index fragmentation, and so on – not just in the clustered index, but also in nonclustered indexes. One of […]

Clustered or nonclustered index on a random GUID?

Quickie this morning to start the day off. I saw a question on a forum: if I *have* to use a GUID and *must* have a primary key, should I make the primary key clustered or nonclustered? Now, I'm not getting into the whole GUID vs. bigint identifier, or random GUID vs. GUID generated by […]

Are your disk partition offsets, RAID stripe sizes, and NTFS allocation units set correctly?

I've just been setting up some of our new hardware, and wanted to do some background reading to ensure I use the correct disk partition offset, RAID stripe size, and NTFS allocation unit size to enable the best possible performance for the volumes I'm creating. You may not of heard about this (or your disk […]

How expensive are page splits in terms of transaction log?

Page splits are always thought of as expensive, but just how bad are they? In this post I want to create an example to show how much more transaction log is created when a page in an index has to split. I'm going to use the sys.dm_tran_database_transactions DMV to show how much more transaction log […]

What is FSAgent.exe and why does it have lots of waits?

Another quickie today. If you're using Activity Monitor in SQL Server 2008, or Performance Data Collection with the Server Activity system data collection set, then you may see a constant, and large number of 'Other' SQL Server Waits. When you drill into these, you see that the highest number of waits are for FSAgent. The FSAgent is […]

Misconceptions around adding columns to a table

This is a quick follow-on from my Misconceptions around null bitmap size post. The null bitmap is *always* present in a data record (i.e. records in a heap or the leaf-level of a clustered index) except when all columns are defined as SPARSE in SQL Server 2008 onwards, but is optional in index records if all […]

Why did the Windows 7 RC download failure happen?

5/11/09: Little bit of a rewrite today as it seems some people are taking what I'm saying the wrong way. Over on Ed Bott's blog, last week he showed some inside info about the notorious "SQL Server problem" that caused the Windows 7 RC downloads to be so slow. The reason given was that SQL […]

Performance bug: NOLOCK scans involving off-row LOB data

Here's an interesting bug that was just recently fixed, and someone actually hit it today, so it's time to blog it. Bottom line is that if you're doing NOLOCK scans of tables involving LOB data, the perfmance might suck. 2005 SP3 CU3 has the bug fix, but unfortunately the KB article was written by someone […]

Physical database layout vs. database size

A couple of weeks ago I kicked off the latest survey, on what the physical layout of your databases are and why you have them that way (see here for the survey). I let the survey run for a while to get a good sampling, and I wasn't disappointed, with over 1000 responses! Here are […]

MS announces support for ‘lock pages in memory’ for Standard Edition

Finally! All those customers out there who are frustrated by not being able to turn on the 'lock pages in memory' option to protect the SQL working set on Standard Edition – the wait is nearly over. Bob Ward (Principal Escalation Engineer in CSS, and a good friend of mine) announced at PASS Europe that […]

Misconceptions around TF 1118

There’s been a recent flurry of confusion and misconceptions about trace flag 1118 in SQL Server 2008. This trace flag switches allocations in tempdb from single-page at a time for the first 8 pages, to immediately allocate an extent (8 pages). It’s used to help alleviate allocation bitmap contention in tempdb under a heavy load […]

Why could restoring a log-shipping log backup be slow?

A short post to start the day (in India) and then at lunchtime I'll do how it works: FILESTREAM garbage collection. This is a question that came up recently on an MVP discussion list: why does a 200MB log backup take 40 minutes to restore on my log-shipping secondary? First answer I thought of was […]

Updated whitepaper: Troubleshooting Performance Problems in SQL Server 2008

Microsoft's popular performance troubleshooting whitepaper has been updated for SQL Server 2008. You can download Troubleshooting Performance Problems in SQL Server 2008 at http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/TShootPerfProbs2008.docx. I've also added it to our whitepaper-links collection page at http://www.sqlskills.com/whitepapers.asp.

New research paper: Migrating Server Storage to SSDs: Analysis of Tradeoffs

At the last few conferences I've presented at, there have been questions about using SSDs (Solid-State Drives) for enterprise storage and whether that will change some of the database maintenance practices. My answer to that is "I don't know" (ha – bet you thought I was going to say "It depends!") because adoption of SSDs […]

Weekly survey: does size really matter – or is it what you do with it?

This week's survey is a little more complicated. I'm interested in the physical layout of your databases. I've got four surveys, for a variety of database sizes. Please vote multiple times in each survey, as you see fit – and by all means forward this link to your friends/clients/etc or re-blog/tweet it. I'm going to report […]

Importance of choosing the right LOB storage technique

Last week's survey was on how you should store large-value character data in SQL 2005+ (see here for the survey). Here are the result as of 4/3/2009 – and I think my favorite answer is starting to catch-on: My favorite answer is, of course, it depends! For all those who didn't answer 'it depends', your […]


(Continuing my habit of blogging while Kimberly's presenting – at least I'm not on stage this time…) In early versions of SQL Server, it was sometimes necessary to 'pin' the pages from a heavily used table in the buffer pool to help performance. In later versions, the buffer pool became very sophisticated and does an […]

TechNet Magazine: April 2009 SQL Q&A column

The April edition of TechNet Magazine is available on the web now and has the latest installment of my regular SQL Q&A column. This month's topics are: Disappearing errors with DBCC CHECKDB Provisioning tempdb when moving from 2000 to 2008 Does fillfactor prevent fragmentation and should it be set instance-wide Avoiding FILESTREAM performance problems Check […]

New whitepaper on tuning Change Data Capture performance

Microsoft’s Steffen Krause has written an excellent whitepaper on Tuning the Performance of Change Data Capture in SQL Server 2008, that I technically reviewed, and it was published late last year. To get an overview of Change Data Capture (CDC) before reading the whitepaper, see the TechNet Magazine article I wrote for the November issue, […]

Performance impact of lots of VLFs in the transaction log

One of the things I mentioned in my recent TechNet Magazine article on Understanding Logging and Recovery was the need to manage the transaction log so the number of VLFs (virtual log files) does not get too large (with too large being more than, say, 100 VLFs). Linchi Shea (a fellow MVP) has just posted […]

Comprehensive tempdb blog post series

Over the last few weeks Sunil Agarwal (from the SQL Storage Engine team) has posted a great series of blog articles about tempdb and the version store, over on my old stomping ground – the Storage Engine blog. The articles are well worth reading – the links are: Managing tempdb part 1 and part 2 Version […]

TechNet Magazine: December 2008 SQL Q&A column

It's really scary how quickly time flies – seems like it was just last week when I last blogged about TechNet Magazine (actually it was a month ago when I blogged about my 2008 Change Tracking article – see here). Anyway, a new issue of TechNet Magazine has just come out and this one has the […]

New minimally-logged insert functionality in SQL Server 2008

During early SQL Server 2008 development, the Storage Engine team introduced additional minimally-logged functionality. And then it was removed again for RTM. Now it seems that they've bowed under customer pressure and have made the functionality available again (see Sunil's announcement here). Using trace flag 610 in the RTM build, you enable the potential for […]

High-performance FILESTREAM tips and tricks

Over the last few weeks I’ve had lots of questions about FILESTREAM performance and how to coax NTFS into scaling nicely. I just finished writing a 30-page whitepaper about FILESTREAM for the SQL Server team, which should be published before PASS in November (I’ll blog the link when I have it). Although my whitepaper isn’t […]

Can GUID cluster keys cause non-clustered index fragmentation?

At the user group meeting on Monday I spent some time explaining how GUIDs can cause fragmentation in clustered indexes AND in non-clustered indexes, even if the GUID isn’t specifically included in the non-clustered index key. GUIDs are essentially random values (pseudo-random in ranges if generated using NEWSEQUENTIALID) that are also unique. Their uniqueness is […]

User groups and classes in August and September

Fresh off a week of teaching classes on the Microsoft campus, we’ve finalized some user group dates. Here’s what we have coming up: Monday August 18th: user group meeting in Redmond .NET Developer’s Association Database Meeting at the Microsoft Redmond Campus Topic: Index Internals and Maintenance Monday September 1st to 3rd: public class in England In […]

How hard is it to pick the right non-clustered indexes?

Kimberly and I were presenting at our local (Redmond) .Net Developers Association on Monday and the following question came up while Kimberly was talking about missing and extra indexes (paraphrasing): What’s the best non-clustered index to use for the query with a predicate WHERE lastname = ‘Randal’ AND firstname = ‘Paul’ AND middleinitial = ‘S’? […]

SQL Server 2008: FILESTREAM performance

During the various courses I've been teaching, people are interested in how FILESTREAM performance compares with storing BLOBs in the database itself. I have some performance graphs based on measurements the dev team made – these have been presented publicly by myself and the dev team so I can share them with you here. There […]

CHECKDB From Every Angle: Example DBCC CHECKDB run-times

Almost a year ago to the day I asked a question on my old blog – how long does *your* CHECKDB take to run? Well, I had a bunch of replies but I never got around to posting the results. I was reminded of this recently so threw together some graphs of the results. I've […]

The frustration of sweeping generalizations – follow on from Search Engine Q&A #12

A bit more traffic on the thread (see previous post here) prompted me to give my thoughts on the many sweeping generalizations that plague the computer industry and make it difficult sometimes to give advice in forums and blogs. I’d like to repost here (with a few tweaks for clarity). Some examples of questions that […]

Search Engine Q&A #12: Should you create multiple files for a user DB on a multi-core box?

There’s been a very interesting discussion going on over at SQLServerCentral.com about whether to create multiple files for a user database because the server has multiple CPUs – see the thread here. I wrote a couple of long responses during the thread that I wanted to duplicate here as I think it’s of broad interest. My […]

Whitepaper on Predeployment I/O Best Practices, plus a few urban legends around SQL Server IO

There’s a new whitepaper on TechNet that I’ve just come across (even though it was published 6 months ago!) called Predeployment I/O Best Practices. It’s really good – discussing the following: Guidelines for determining I/O capacity Disk configuration best practices and common pitfalls Using SQLIO to determine capacity and interpreting its results Using System Monitor […]

SQL Server 2008: Hot-Add CPU (and affinity masks)

Quickie today as I’m preparing to speak at the Pacific Northwest SQL Server User Group meeting tonight on the MS Campus here in Redmond (my trademark Detection and Recovery from Database Corruptions talk). SQL Server 2005 introduced the concept of hot-add memory, to allow for dynamic workload handling. SQL Server 2008 increases these capabilities by […]

SQL Server 2008: Backup Compression CPU Cost

Back in September last year I blogged about the native Backup Compression that is in SQL Server 2008 (see here) and promised to blog more when CTP-5 came out and I ran some tests. Well, it’s here and I have so here’s some data for you. I expanded the AdventureWorks database to be 322Mb (random […]

Paul and Kimberly interview on RunAs Radio – What’s New in SQL Server 2008?

While we were in Barcelona we sat down with Richard Campbell and Greg Hughes from RunAs Radio to record a 1/2 hour interview on SQL Server 2008. We touch on a ton of different features (look at the number of Categories I’ve tagged this with!) and have a bunch of laughs along the way – […]

CHECKDB From Every Angle: How long will CHECKDB take to run?

This is a subject I posted about last year on my old blog but it came up at SQL Connections last week several times so I want to repost it for those who’ve just started following my blog. There’s only one time when you should be trying to work out how long a CHECKDB is […]

Auto-shrink – turn it OFF!

I’m in the middle of a flight from Washington D.C. to Zurich on the way to Barcelona for TechEd IT Forum and I can’t sleep – Kimberly’s out like a light so what else is there to do except write another blog post? :-) OK – actually posting this from Barcelona on Tuesday before our […]

Conference Questions Pot-Pourri #4: How many databases can you really mirror per instance?

This is a question I was asked multiple times over the last week: the Microsoft guidelines for database mirroring say not to mirror more than 10 databases per instance – why is that and is it true? The answer is my favorite 'it depends!'. The number 10 is a rough guess at the sweet-spot for […]

How can data-type choice affect performance?

In one of the sessions Kimberly and I taught this week at SQL Connections, we discussed how to choose efficient data-types – I’d like to share the discussion here with an example. The crux of the matter is in the amount of space required to store the data-types in the schema, and ensuring that the […]

More on Database Mirroring performance and index maintenance

There’s been some discussion over on the SQL Server Central forums about database mirroring performance, and one question in particular on how to do index rebuilds on mirrored VLDBs. Remember that in database mirroring, the database has to be in the full recovery mode, so all index rebuilds are fully logged. It all comes down […]

SQL Server 2008: Performance boost for Database Mirroring

As I mentioned in a previous post, one of the new features for database mirroring in SQL Server 2008 is log stream compression. There’s a good explanation of what this is (along with some example workloads and performance graphs) on the SQL Customer Advisory Team blog so I’m not going to duplicate all that here. Basically, the […]

Search Engine Q&A #5: Do multi-core CPUs perform better than single-core CPUs?

Here’s an interesting question I was sent by my friend Steve Jones over at SQL Server Central – will a single CPU with dual-cores perform better than two single-core CPUs? Both have two processing cores but the hardware architecture is different – which one will make SQL Server perform better? Well, there’s no hard and […]

Indexes From Every Angle: How can you tell if an index is being used?

Whenever I’m discussing index maintenance, and specifically fragmentation, I always make a point of saying ‘Make sure the index is being used before doing anything about fragmentation’. If an index isn’t being used very much, but has very low page density (lots of free space in the index pages), then it will be occupying a […]

SQL Server 2008: Parallelism improvements for partitioning

In SQL Server 2005, queries over partitioned tables use a single-thread per partition. This can cause performance problems under certain circumstances: On systems with many CPUs, if the table schema has less partitions than there are CPUs, then not all the CPUs will be used to process the query. Some examples: On a 32-way box, […]

SQL Server 2008: New Performance Counters for Database Mirroring

Part of the improvements to database mirroring in SQL Server 2008 are the addition of 10 new performance counters under the Database Mirroring Performance Object. These add some cool troubleshooting capabilities. SQL Server 2005 provided the following 11 counters (from Books Online): Name Description Bytes Received/Sec Number of bytes received per second. Bytes Sent/sec Number […]