Blog

Ah, spring, when a young man’s fancy lightly turns to thoughts of updating his DMV queries.

sp_Blitz®, our free health check stored procedure, brings several new checks and a whole crop of fixes and improvements. I’d like to call your attention to one in particular.

Julie Citro fixed check 1, THE VERY FIRST CHECK IN THE SCRIPT, which had a logic bug when looking for databases that had never been backed up. Sure, the next line had a workaround, but nobody caught this for years. All of you who ever read the source code, Julie Citro is officially a better tester than you.

sp_BlitzCache™, our performance tuning tool, has new updates too.

You can now run with @reanalyze = 1 first and sp_BlitzCache™ won’t break. You can run sp_BlitzCache™ from multiple SPIDs! If you had triggers, sp_BlitzCache™ would start to pick itself up – this has stopped.

Also, an integer overflow has been fixed – queries using more than 28,000 days of CPU since start up will no longer cause an arithmetic overflow.

You can grab the updated scripts in our First Responder Kit.

Basic VMware and Hyper-V Terminology for the SQL Server DBA

Last Updated July 13, 2017

Virtualization

SQL Server DBAs often need to work with virtualization– and frequently need to work with multiple virtualization platforms. Many platforms have similar features, but they often have different names, and it’s hard to remember what everything’s called.

I’ve got a little cheat sheet for you for two of the platforms! (No offense, Xen fans, please don’t come after me.)

What is it?	VMware name	Hyper-V name
Management Tools, Free	vSphere / web client	Hyper-V Manager (snap-in)
Management Tools, Paid	vCenter Server	System Center Virtual Machine Manager
Automatic migration to another host if one fails	High Availability (HA)	High Availability (configured via a role in a Windows Failover Cluster)
Moving a VM from host to host	vMotion	Live Migration
Moving a VM from one storage subsystem to another	Storage vMotion	Storage Migration
Automatic load balancing	DRS (Distributed Resource Scheduler)	Dynamic Optimization in VMM (Virtual Machine Manager, a part of System Center)
Rules to keep VMs from being too close	Affinity Rules for DRS	Availability Sets, part of Intelligent Placement in VMM

Microsoft SQL Server Licensing Simplified into 7 Rules

Last Updated August 4, 2025

Learn more, see sample deliverables, and book a free 30-minute call with Brent.

Licensing, SQL Server

203

Licensing is really complex, but as long as you know these seven rules, you can go a long way:

If you query it, you have to license it. (Backups and corruption checks are not considered querying.)
You have to license all of the processor cores that the operating system sees, even if you’re not allowing SQL Server to use all of them with tricks like processor affinity masking.
Standard Edition costs about $2k USD per core, but caps out at 24 cores and 128GB RAM.
Enterprise Edition costs about $7k USD per core.
Software Assurance is an ongoing maintenance fee that gives you the right to upgrade to newer versions of SQL Server, plus…
If you’re covered under Software Assurance, you get two free standby servers of equivalent size. (Standby means you’re not querying it, other than offloading backups or checkdb.)
If you’re covered under Software Assurance, and you want to cram a bunch of VMs or containers onto a single host, you can license all of the CPU cores in the host with SQL Server Enterprise Edition and Software Assurance, and then run an unlimited number of VMs with SQL Server on that host.

Then to learn more, get Microsoft’s free SQL Server 2022 Licensing Guide. It’s really straightforward, and I’ve used that many times to quickly disprove wacky claims from sales reps.

Got a question not answered by these rules? Head over to the Microsoft licensing chat.

Want help? Talk to Brent for free.

If you need to reduce your Enterprise Edition licensing exposure, we can help.

Our SQL Critical Care® is a quick, easy process that identifies your top bottlenecks and gives you the easiest, cheapest way past them. We can help you reduce the number of CPU cores you need to run your workloads.

Should You Be a SQL Server DBA? (video)

Last Updated April 23, 2015

Have you ever wondered if you should be a SQL Server Database Administrator? If you think this might be the right career path for you, watch this video and take our quiz!

Got limited time or need to restart the quiz? No problem, we’ve got a table of contents with time indicators below.

https://www.youtube.com/watch?v=zYI7UGi14jw

“Should You Be a SQL Server DBA?” Table of Contents:

00:21 – Getting the wrong job stinks
00:44 – Getting the right job is awesome
01:44 – Get paper and pen, it’s quiz time!
02:11 – Question 1
02:38 – Question 2
03:09 – Question 3
03:22 – Question 4
03:39 – Question 5
03:59 – Question 6
05:39 – Question 7
06:41 – Question 8
07:26 – Time to score this thing!
07:33 – Scoring Question 1
08:45 – Scoring Question 2
10:25 – Scoring Question 3
11:38 – Scoring Question 4
12:49 – Scoring Question 5
13:43 – Scoring Question 6
15:37 – Scoring Question 7
17:25 – Scoring Question 8
19:28 – What does this all mean?
20:29 – Traits of a happy DBA
21:33 – Learn more at BrentOzar.com/go/Career

Brent says: if you think this quiz is just for people who aren’t in the DBA business already, think again. Kendra’s title is “SHOULD you be a DBA?” And, uh, some of us probably shouldn’t. (I love this stuff though.)

Are Index ‘Included’ Columns in Your Multi-Column Statistics?

Last Updated July 13, 2017

Indexing, SQL Server

Internals Cat populates the density vector

When you create an index in SQL Server with multiple columns, behind the scenes it creates a related multi-column statistic for the index. This statistic gives SQL Server some information about the relationship between the columns that it can use for row estimates when running queries.

But what if you use ‘included’ columns in the index? Do they get information recorded in the statistics?

Here’s my Index

To test, we’ll create an index with multiple key columns and included columns. I’m using a restored copy of the StackOverflow database.

/* FavoriteCount and LastEditDate aren't in the index key */
/* They'll just hang out in the leaf of the index */
CREATE INDEX ix_Posts_OwnerUserId_PostTypeId_INCLUDES 
    ON dbo.Posts (OwnerUserId, PostTypeId) 
    INCLUDE (FavoriteCount, LastEditDate);
GO

/* FavoriteCount and LastEditDate aren't in the index key */

/* They'll just hang out in the leaf of the index */

CREATE INDEX ix_Posts_OwnerUserId_PostTypeId_INCLUDES

ON dbo.Posts (OwnerUserId, PostTypeId)

INCLUDE (FavoriteCount, LastEditDate);

First Stop: sys.Stats and Friends

The sys.stats DMV lets me query metadata out about my statistics, and I can join up to find out how many columns it has, what order they are in, when they were updated, and all sorts of info:

SELECT so.name,
    st.name,
    st.stats_id,
    sc.stats_column_id,
    c.name as column_name,
    st.auto_created,
    st.filter_definition,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.steps,
    sp.modification_counter
FROM sys.stats AS st
JOIN sys.stats_columns AS sc on st.object_id=sc.object_id and st.stats_id=sc.stats_id
JOIN sys.columns as c on sc.object_id=c.object_id and sc.column_id=c.column_id
JOIN sys.objects as so on st.object_id=so.object_id
CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) sp
WHERE so.name='Posts'
ORDER by so.name, st.stats_id, sc.stats_column_id;
GO

SELECT so.name,

st.name,

st.stats_id,

sc.stats_column_id,

c.name as column_name,

st.auto_created,

st.filter_definition,

sp.last_updated,

sp.rows,

sp.rows_sampled,

sp.steps,

sp.modification_counter

FROM sys.stats AS st

JOIN sys.stats_columns AS sc on st.object_id=sc.object_id and st.stats_id=sc.stats_id

JOIN sys.columns as c on sc.object_id=c.object_id and sc.column_id=c.column_id

JOIN sys.objects as so on st.object_id=so.object_id

CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) sp

WHERE so.name='Posts'

ORDER by so.name, st.stats_id, sc.stats_column_id;

Here’s the first few columns of the results:

This doesn’t show FavoriteCount or LastEdit date. My index key columns are in the statistic, in the same order they appear in the index.

What About DBCC SHOW_STATISTICS?

We can see a representation of the statistic with more information. We just plug in the name of the table and the statistic to DBCC SHOW_STATISTICS, like this:

DBCC SHOW_STATISTICS ('Posts', 'ix_Posts_OwnerUserId_PostTypeId_INCLUDES');
GO

1 2	DBCC SHOW_STATISTICS ('Posts', 'ix_Posts_OwnerUserId_PostTypeId_INCLUDES'); GO

This returns a few result sets. We get information about the header of the statistics, some density information about the relationship between the columns, and a histogram that shows the distribution of rows for the leading column in the statistic.

That Density Vector Has Three Rows!

Check that out, the density vector has a third row to help SQL Server estimate data distribution for OwnerUserId, PostTypeId, and the Id column. We didn’t specify the Id column anywhere in our index!

The Id column is the key of the clustered index on this table. SQL Server decided that it would also be interesting to know the density information of that as a third column.

What if the Index Was Unique? Would it Still Sneak In the Clustering Key?

The index above doesn’t have unique data, but let’s test a different index:

CREATE UNIQUE INDEX ix_VoteTypes_Name on dbo.VoteTypes (Name);
GO

DBCC SHOW_STATISTICS ('VoteTypes', 'ix_VoteTypes_Name');
GO

CREATE UNIQUE INDEX ix_VoteTypes_Name on dbo.VoteTypes (Name);

DBCC SHOW_STATISTICS ('VoteTypes', 'ix_VoteTypes_Name');

Here’s the density vector:

SQL Server decided that tracking the density information for the combination of Name and Id was still useful, even though this is a unique index and SQL Server didn’t have to sneak that column into the key of the index to make it unique behind the scenes.

Included Columns Weren’t in the Density Vector, but My Clustering Key Was!

In other words, included columns won’t be in the density vector unless they happen to also be in the key of your clustered index.

And Party and Alt Shift

Last Updated April 14, 2015

Erik Darling

This is a cool SSMS trick I picked up a while back

Though not nearly as far back as I wish I had. It’s so cool I made a .gif of it in action. When you’re done putting your socks back on, I’ll tell you how it works.

AltShiftDemo — This .gif was brought to you by the Cool SSMS Tricks Foundation, in association with Worldwide .gifs

Pure ALT+SHIFT magic.

Hold down both keys at the same time, and use your up and down arrow keys to navigate vertically. There will be a thin grey line showing you exactly which rows you’ve grabbed. Then Just type normally. I uh, simulated a typing error, to illustrate that you can also delete text doing this. Yeah.

It really makes doing simple multi-line edits a breeze, especially if you don’t feel like setting up Excel formulas to do similar tasks. These are random Massachusetts zip codes, which is why they get a leading zero, and quotes.

Can you feel the efficiency?!

Kendra says: What in the…. holy cow, that actually works!

Brent says: I knew about that trick, but ZOMG PEOPLE THERE IS A PRODUCTIVITY GIF IN OUR BLOG

Happy Fourth Birthday to Us, 40% Off Presents for You

Last Updated February 12, 2017

Company News, SQL Server

Four years ago this month, we turned this blog into a consulting company.

We’ve had so much fun over the last few years, and we’re really proud of what we’ve built:

Over 500 SQL Critical Care® patients
Thousands of training video customers
Thousands of in-person training attendees at dozens of classes and pre-cons
Hundreds of free YouTube videos watched by over a million people
Almost 50,000 email subscribers
Several million web site viewers
A handful of awesome full time employees

Let’s celebrate. Between now and Tuesday 4/28, discount code Our4thBirthday gets you 40% off online sales of our training videos. Enjoy!

How to Evaluate NoSQL Case Studies [Video]

Last Updated April 14, 2015

Every now and then, one of our clients considers adopting an alternative database platform – sometimes NoSQL, sometimes a brand new relational database. They’ll ask for our help in evaluating the vendor’s solution.

One of the best ways to do it is ask the database vendor to set us up on a WebEx or GoToMeeting with one of their happy customers. I want to hear from the technical folks, not management.

I run the call with six PowerPoint slides – here’s how:

https://www.youtube.com/watch?v=fUaY3AacyGw&amp

When is a Hyper-V Virtual Machine Not Really a Virtual Machine?

Last Updated April 17, 2015

When you set up a Windows Server, you might be tempted to add a bunch of roles, just in case you want to use them in the future. Don’t do it: things can get weird.

If you add the Hyper-V role to Windows Server 2012 and higher, it changes some things about how Windows behaves. Your server is now a host. And the host starts to behave in some ways like a virtual machine — it’s now the “Parent Partition”, which is a “quasi-virtual machine”.

There are limits to the number of logical processors that any virtual machine partition might see, so if you have more than 64 logical processors, this can limit what’s available in the Parent Partition / host operating system, and really confuse you.

When SQL Server starts up, it checks to see if you’re virtualized. If the Hyper-V role is enabled, it will think it’s running in a VM. This could end up in a sticky situation if you have a licensing audit and hyper-threading is enabled — you’re supposed to license all the logical processors in a virtual machine regardless of hyper threading.

But good news, this is an easy problem to solve:

Don’t install the Hyper-V role “just in case” on a SQL Server
If someone else sets up your SQL Servers, check the installed roles before going live
Don’t ever design an architecture where a Hyper-V host does double-duty and is also a SQL Server
If you log onto a Hyper-V host, look at ‘Logical processors’ to see the total count (‘Host logical processors’ may be lower)

“Breaking” News: Don’t Install SQL Server 2014 SP1

Last Updated September 21, 2017

Always On Availability Groups, Breaking News, SQL Server

Yesterday, Microsoft announced availability of Service Pack 1, saying:

As part of our continued commitment to software excellence for our customers, this upgrade is available to all customers with existing SQL Server 2014 deployments via the download links below.

Yeah, about that commitment to software excellence.

This morning, the download is gone:

Notice: The SQL SSIS team has found an issue with SP1 installation if SSIS catalog is present in the SQL Server instance.They are currently investigating this issue including possible workarounds and fixes.

Oof – the term “possible workarounds and fixes” doesn’t sound good for those who jumped in and applied the patch. A commenter on the MS Data Platform Insider blog reported that it ~~hosed the master database~~ broke the instance in a way suspiciously similar to a similar bug in SQL Server 2012 SP2.

(And jeez, what is it with service packs lately? Remember the SQL 2012 SP1 100% CPU issue? I’m starting to think you’re safer with cumulative updates than with service packs.)

Remember, kids, don’t rush into patching. If your servers are mission critical, test in your staging environment first – staging is the DBA’s development. (No, your development environment isn’t staging – because your developers make their living in the dev environment, and if you broke that with SP1 yesterday, you’ll be slaving away today to get your dev instance back up and running.)

Update: the fix is in. If you applied SP1, follow the instructions in this StackExchange post.

Moving Databases Made Easy – SQL Server on a File Share

Last Updated February 13, 2017

Jeremiah Peschka

Storage

We’ve all had to move a database. For databases above a small size, this is a painful process. While there are a number of ways that you can move a database without downtime, they all involve a lot of extra work. What if there were something easier?

What’s the Real Problem?

For most people, moving a database is a one time thing – you’re moving to a new server. That’s pretty much it. I spent 5 minutes staring at my ceiling trying to figure out other reasons that most people might move a database from one SQL Server to another.

For some people, though, databases could move on a regular basis. Think about it like this – you host a software as a service (SaaS) application. You’ve got one database per relative unit of work (customer, forum, cat picture collection). These databases can sit idle for months before, suddenly, there’s tremendous activity.

Normally, you wouldn’t make each SQL Server capable of handling peak load for any section of your SaaS. Instead, you would probably have a few servers ready to handle the worst load and move your busiest database over to those servers.

Typically, this is going to involve a small outage and setting up something like database mirroring or log shipping in order to move the database with minimal down time.

SMB to the Rescue!

Microsoft has supported housing SQL Server databases on a file share for a while now. If you’re not familiar with the idea, I recommend checking out the MSDN article Install SQL Server with SMB Fileshare as a Storage Option.

Moving a database with SMB is straight forward – detach the database from the old server, attach the database to the new server, bring the database online. For most of us, this quick change is much preferred to other methods of moving the database.

SMB File Shares and You

Although SMB file shares are a new technology, they’re worth considering for new SQL Servers. SMB file shares make it easy to move databases between SQL Servers. If you want to learn more about using SMB check out the MSDN article on using SMB and SQL Server.

Brent says: If your storage area network (SAN) is using older connectivity methods like 2Gb or 4Gb FC, and your Ethernet is 10Gb, you might even see a performance boost.

SELECT INTO and non-nullable columns

Last Updated April 9, 2015

Erik Darling

SELECT…INTO is one of my favorite SQL Server features.

It’s great for creating table skeletons with false WHERE clauses (1=2), moving a real table to a staged/temp location for testing, etc.

In SQL Server 2014

It acquired the ability to go parallel, which is pretty neat, but that’s not what we’re talking about here.

It has some limitations

Chief among them is this:

Indexes, constraints, and triggers defined in the source table are not transferred to the new table, nor can they be specified in the SELECT…INTO statement. If these objects are required, you can create them after executing the SELECT…INTO statement.

Which is… Sort of true. There’s a trick, and I’ll show you a quick example here with another of my favorite things: a Numbers table.

;WITH E1(N) AS (
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL 
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL 
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL 
    SELECT NULL  ),                          
E2(N) AS (SELECT NULL FROM E1 a, E1 b, E1 c, E1 d, E1 e, E1 f, E1 g, E1 h, E1 i, E1 j),
Numbers AS (SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM E2)
SELECT [N].[N]
INTO #NumbersTest
FROM [Numbers] N

ALTER TABLE #NumbersTest ADD CONSTRAINT [PK_Numbers] 
PRIMARY KEY CLUSTERED (N) WITH (FILLFACTOR = 100)

;WITH E1(N) AS (

SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL

SELECT NULL ),

E2(N) AS (SELECT NULL FROM E1 a, E1 b, E1 c, E1 d, E1 e, E1 f, E1 g, E1 h, E1 i, E1 j),

Numbers AS (SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM E2)

SELECT [N].[N]

INTO #NumbersTest

FROM [Numbers] N

ALTER TABLE #NumbersTest ADD CONSTRAINT [PK_Numbers]

PRIMARY KEY CLUSTERED (N) WITH (FILLFACTOR = 100)

Trying to add the PK constraint here fails, because the column is NULLable

Msg 8111, Level 16, State 1, Line 37 Cannot define PRIMARY KEY constraint on nullable column in table '#NumbersTest'. Msg 1750, Level 16, State 0, Line 37 Could not create constraint or index. See previous errors.

We can verify this by looking at the table metadata:

SELECT [columns].[name], [columns].[is_nullable]
FROM tempdb.sys.columns 
WHERE [object_id] = OBJECT_ID(N'tempdb..#NumbersTest');

SELECT [columns].[name], [columns].[is_nullable]

FROM tempdb.sys.columns

WHERE [object_id] = OBJECT_ID(N'tempdb..#NumbersTest');

name is_nullable N 1

So how do we fix this? We could alter the table, but that won’t leave us with the lasting satisfaction of proving BOL wrong on a technicality. We’ll adjust our code a bit, and try again.

;WITH E1(N) AS (
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL 
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL 
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL 
    SELECT NULL  ),                          
E2(N) AS (SELECT NULL FROM E1 a, E1 b, E1 c, E1 d, E1 e, E1 f, E1 g, E1 h, E1 i, E1 j),
Numbers AS (SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM E2)
SELECT 
ISNULL([N].[N], 0) AS N  /* &lt;--- The magic is here! */
INTO #NumbersTest_IN
FROM [Numbers] N

ALTER TABLE #NumbersTest_IN ADD CONSTRAINT [PK_Numbers] 
PRIMARY KEY CLUSTERED (N) WITH (FILLFACTOR = 100)

;WITH E1(N) AS (

SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL

SELECT NULL ),

E2(N) AS (SELECT NULL FROM E1 a, E1 b, E1 c, E1 d, E1 e, E1 f, E1 g, E1 h, E1 i, E1 j),

Numbers AS (SELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM E2)

SELECT

ISNULL([N].[N], 0) AS N /* <--- The magic is here! */

INTO #NumbersTest_IN

FROM [Numbers] N

ALTER TABLE #NumbersTest_IN ADD CONSTRAINT [PK_Numbers]

PRIMARY KEY CLUSTERED (N) WITH (FILLFACTOR = 100)

This time, with the addition of an ISNULL check on the column, it ‘inherits’ the not NULLable property, and the PK constraint adds successfully. We can verify that by checking the table metadata, if you don’t believe me:

name is_nullable N 0

Note that this same behavior does not occur if you replace ISNULL() with COALESCE()

And with that, you can clean up the test tables. Or keep them around. Do some math. Whatever blows your hair back.

Got a favorite use for a numbers table outside of string splitting or doing date math? Let me know in the comments! I may write a follow up.

DROP TABLE [#NumbersTest]
DROP TABLE [#NumbersTest_IN]

1 2	DROP TABLE [#NumbersTest] DROP TABLE [#NumbersTest_IN]

Brent says: Wow. That is a really slick trick.

Kendra says: I know some people have bias against SELECT INTO because it seems sloppy and you have to go the extra mile to get the right types, but it can be a great tool. Don’t rule it out.

Jeremiah says: I really like this method – it’s a quick way to copy tables without using other cumbersome techniques

The @DBAreactions Guide to In-Memory OLTP (Hekaton)

Last Updated February 13, 2017

In-Memory OLTP (Hekaton), SQL Server

At SQLbits last month, I presented a new session: Cool Story, Bro – The DBAreactions Guide to SQL Server 2014. I wanted to have some fun while educating folks about the surprise gotchas of the newest features.

Here’s the In-Memory OLTP (Hekaton) section of the session:

Our sp_Blitz® has long warned you if Hekaton is in use, and its Hekaton detail page shows some of the limitations.

Can DBCC SHRINKFILE Cause Blocking in SQL Server?

Last Updated January 11, 2018

Locking, Blocking, and Isolation Levels, SQL Server

It sure can.

The lock risks of shrinking data files in SQL Server aren’t very well documented. Many people have written about shrinking files being a bad regular practice— and that’s totally true. But sometimes you may need to run a one-time operation if you’ve been able to clear out or archive a lot of data. And you might wonder what kind of pains shrinking could cause you.

One pain it could cause you is blocking and lock waits. It’s easy to reproduce, here’s how.

A Recipe for LCK_M_X Lock Waits and Blocking

I was able to easily reproduce blocking in my restored copy of the StackOverflow database on SQL Server 2014 by doing a few things.

I rebuilt a non-clustered index on the Posts table with this command:

ALTER INDEX [kl_Posts_OwnerUserId_CreationDate] on Posts REBUILD WITH (ONLINE=ON);
GO

1 2	ALTER INDEX [kl_Posts_OwnerUserId_CreationDate] on Posts REBUILD WITH (ONLINE=ON); GO

This ate up some space, and gave shrink something to do!

I then started a data modification and left an open transaction running:

BEGIN TRAN
DELETE FROM dbo.Posts where Id=16;

1 2	BEGIN TRAN DELETE FROM dbo.Posts where Id=16;

And then, in another session, I started to shrink the data file:

DBCC SHRINKFILE ('StackOverflow');
GO

1 2	DBCC SHRINKFILE ('StackOverflow'); GO

Behold, Blocking!

Shrink starts up and slogs its way through things, and soon enough, lock waits appear. This view is from Adam Machanic‘s sp_WhoIsActive:

If I run that with @get_locks=1, I can see details on exactly what lock SHRINKFILE is after:

&lt;Database name="StackOverflow"&gt;
  &lt;Locks&gt;
    &lt;Lock request_mode="S" request_status="GRANT" request_count="1" /&gt;
  &lt;/Locks&gt;
  &lt;Objects&gt;
    &lt;Object name="(null)"&gt;
      &lt;Locks&gt;
        &lt;Lock resource_type="FILE.FILE_SHRINK" request_mode="X" request_status="GRANT" request_count="1" /&gt;
      &lt;/Locks&gt;
    &lt;/Object&gt;
    &lt;Object name="Posts" schema_name="dbo"&gt;
      &lt;Locks&gt;
        &lt;Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" /&gt;
        &lt;Lock resource_type="PAGE" page_type="*" index_name="kl_Posts_OwnerUserId_CreationDate" request_mode="X" request_status="GRANT" request_count="3" /&gt;
        &lt;Lock resource_type="PAGE" page_type="*" index_name="kl_Posts_OwnerUserId_CreationDate" request_mode="X" request_status="WAIT" request_count="1" /&gt;
      &lt;/Locks&gt;
    &lt;/Object&gt;
  &lt;/Objects&gt;
&lt;/Database&gt;

<Locks>

</Locks>

<Locks>

</Locks>

</Object>

<Locks>

</Locks>

</Object>

</Objects>

</Database>

Yep, it’s waiting on a page lock in kl_Posts_OwnerUserId_CreationDate.

But How Many Locks did SHRINKFILE Really Need?

Maybe it needed just a few locks… right?

Well, I ran a trace against my session that did that shrinkfile command, and here’s the number of locks by database and type that it caught…

And this was a FAST run, except for that blocking!

Shrinking files is painful

It can actually be worse than this — on a few runs, I was able to reproduce blocking with SCH_M locks that could cause even nastier blocking chains.

Shrinking is slow, tedious work. It eats CPU, steals your IO, and always takes longer than you want. And if it has work to do and you’ve got users or processors modifying that data, lock contention ensues.

Worst of all, at the end of shrinking a database, nobody will ever thank you. Be careful out there, and if you must run it make sure you don’t leave it unattended.

Brent says: whoa, I never even thought about that. Maybe we should design indexes to improve DBCC SHRINKDB speed.

Erik says: if you do this more than once, I will come find you.

Is Your SAN Administrator Out to Get You?

Last Updated April 1, 2015

DBAs often complain about SAN Administrators. “You can’t trust them.” I’ve seen established DBAs write publicly that they resort to lying about how SQL Server works to get what they want from a SAN Administrator.

That’s pretty toxic. Other DBAs start to think it’s normal to have a terrible relationship with your SAN Administrator.

But it’s not normal. And if there’s a terrible relationship between DBAs and the SAN team, that’s the DBA team’s fault, too.

The first SAN team I ever worked with

I’m pre-disposed to like SAN Administrators because I’ve worked with great people. When I started out with SQL Server at a small dot com, we had a two person SAN team and many terabytes of data across both SQL and NOSQL solutions. Our software team and our data grew insanely quickly.

The SAN team had more fun than almost anyone in the building. They had to work super hard. They got paged a lot. They were sometimes grumpy on the phone at 3 am, just like the DBAs. But they were funny and smart and the kind of people who could magically turn a crappy situation into a good time.

They didn’t always want to make everything RAID 10, and they didn’t always automatically believe the problem was the SAN when I said, “the SQL Server’s slow.” But they worked with me every time when I had a problem, and we always found a solution.

Over time, I learned ways to show them real metrics when I really needed more storage speed rather than just saying, “it’s slow.”

Most SAN Administrators I Work With Today

I still work with SAN Administrators frequently. They’re usually helpful – in fact, they’re often happy that someone would like to hear how the SAN is configured and why it’s set up that way.

Most SAN Admins I meet work alone or in small groups. They’re super busy, and sometimes mistakes get made (just like DBAs). But also like DBAs, I’ve found them to be pretty happy when there’s a real reason that justifies investing in better hardware. They’re able to admit when something’s not right, fix it, and move on.

Remember, The SAN Admin had just as much training as you did

That’s right, they probably didn’t get any training either. The storage world changes fast, and they have to try to keep up.

Yes, they get taken out to nice dinners by the SAN vendor, and you don’t. But think about how your job looks to the people over at the helpdesk. Ever gotten a bottle of scotch from the developers as a reward for saving the day? Ever had flextime and rolled into the office late? It’s not just the SAN admins who have some perks.

Your SAN Admin isn’t out to get you. They just have a lot of customers.

Your mission as a DBA is to make your databases perform as well as the business needs them, and protect the RPO and RTO of your customers. The SAN Administrator’s goal is to provide enough storage capacity and performance as the business needs. They’ve got a lot of customers– the performance of every one of your databases isn’t at the top of their list. When the database is slow, it’s hard for them to know that the issue is the storage.

I’m not saying that there aren’t bad SAN Admins out there. There certainly are.

But don’t be the person who misrepresents things and thinks someone’s out to get them. Aspire to be more like my old SAN team: the kind of person who can turn a crappy situation into a good time. That’s usually a lot more effective.

Brent says: wait a minute – I never got a bottle of Scotch from my developers. I mean, I got hit over the head with a bottle once, but there wasn’t any alcohol left in it.

How Do I Know My Query Will Be Fast In Production?

Last Updated April 1, 2015

Jeremiah Peschka

We’ve all had this problem before – the production server is significantly more powerful than the development server. You tune a query and you’re pretty sure you’ve made it faster, but there’s that doubt in the back of your mind. There are so many things that can change performance – memory, CPU count, disk performance – how can you tell that you’ve made a query better?

Measuring Slowness

When you know you have a slow query that you need to tune, run it in production one last time. But, before you do that, gather CPU and IO metrics using STATISTICS IO and STATISTICS TIME:

SET STATISTICS IO, TIME ON; 

EXEC dbo.MyAwfulQuery @slow = 'yes';

SET STATISTICS IO, TIME ON;

EXEC dbo.MyAwfulQuery @slow = 'yes';

You’ll get a bunch of jibber jabber in the “Messages” tab that documents how much CPU time was spent compiling and running your query as well as information about logical IO. Save this off to a text file for now.

Measuring Fastness

Go to your dev server. Tune your query. I’ll wait.

As you’re tuning your query, pay attention to the CPU time and logical IO numbers that you’re getting back from STATISTICS IO and STATISTICS TIME. These are a relative indication that things are getting better. If there are CPU differences, you’re likely to see different CPU numbers on that dev machine, but that’s OK. We’re looking to compare total logical IO and total CPU time in milliseconds.

If you want an easy route to compare your results, you can paste the output from STATISTICS IO and STATISTICS TIME into statisticsparser.com. This will go through the results and push the data into a nice table for your perusal.

How Much Faster Will my Query Be?

Using STATISTICS IO and STATISTICS TIME to tune helps me figure out how well I’m doing compared to where I started from. Some queries move around a lot of data. In these cases, it can help to know how much faster dev is than production.

If you really want a reasonable comparison between dev and production, you can find some guidance in the Fast Track Data Warehouse Reference Guide. There’s a section of this document about measuring the MCR (Maximum Consumption Rate) and BCR (Base Consumption Rate) of your SQL Server. These numbers reflect 100% cache reads and 100% disk reads, respectively.

Once you have MCR and BCR, you make effective predictions about how query performance could differ between the two. This gives you the power to predict, with some margin of error, how fast newly developed queries can run.

But is it Faster?

Between STATISTICS IO and STATISTICS TIME you can determine if a query is going to use fewer resources. The MCR and BCR give you relative numbers for how fast a server can process data. Between these measurements, you can predict how a query will perform between environments and what kind of impact your changes will have.

Brent says: as you get used to doing these measurements, you’ll also be able to tell if a new dev-only query is going to be fast enough in production, too. You’ll know how many logical reads is slow (or fast) in your environment.

Erik says: The hardest thing for me to ever get was commensurate data to test on in dev. If you don’t have it, get it. If you have it, don’t waste it.

CTEs, Inline Views, and What They Do

Last Updated March 12, 2018

Erik Darling

By now, you have probably heard of CTEs

And you may have even heard them referred to as Inline Views. Really, an Inline View can be any type of derived table. It’s very easy to illustrate when one may turn into a performance problem with CTEs, if you aren’t careful.

A lot of people think that when you call a CTE, the results are somehow persisted in a magical happy place and the underlying query just hangs back admiring the output as it sails into the upper deck.

Take the following example, which serves no real purpose.

WITH t1
    AS ( SELECT u.Id FROM dbo.Users AS u WHERE u.Id = 1 )
SELECT *
FROM   t1;

WITH t1

AS ( SELECT u.Id FROM dbo.Users AS u WHERE u.Id = 1 )

SELECT *

FROM t1;

It has a perfectly reasonable execution plan, and will lead a happy life.

I am from the future

Now, let’s join that to itself.

WITH t1
    AS ( SELECT u.Id FROM dbo.Users AS u WHERE u.Id = 1 )
SELECT *
FROM   t1
JOIN   t1 AS t2
    ON t2.Id = t1.Id;

WITH t1

AS ( SELECT u.Id FROM dbo.Users AS u WHERE u.Id = 1 )

SELECT *

FROM t1

JOIN t1 AS t2

ON t2.Id = t1.Id;

Doesn’t get much easier than that. But what happened with the plan?

Huh. That’s a whole other index operation. So just like when you join to a view, the view has to be executed and returned. In fact, if you keep throwing joins that reference the original CTE, you’ll keep getting more index operations.

WITH t1
    AS ( SELECT u.Id FROM dbo.Users AS u WHERE u.Id = 1 )
SELECT *
FROM   t1
JOIN   t1 AS t2
    ON t2.Id = t1.Id
JOIN   t1 AS t3
    ON t3.Id = t1.Id;

WITH t1

AS ( SELECT u.Id FROM dbo.Users AS u WHERE u.Id = 1 )

SELECT *

FROM t1

JOIN t1 AS t2

ON t2.Id = t1.Id

JOIN t1 AS t3

ON t3.Id = t1.Id;

Did someone say they wanted another index operation? Because I thought I heard that.

To sum things up, CTEs are a great base

From which you can reference and filter on items in the select list that you otherwise wouldn’t be able to (think windowing functions), but every time you reference a CTE, they get executed. The fewer times you have to hit a larger base set, and the fewer reads you do, the better. If you find yourself referencing CTEs more than once or twice, you should consider a temp or persisted table instead, with the proper indexes.

Find Corrupted Indexed Views with DBCC CHECKDB

Last Updated February 13, 2017