Recently I saw a tweet that said, “If you can’t read a query execution plan, you’re not a production DBA.”
I love execution plans, and there are really great reasons to learn to read execution plans, but I disagree completely.
Database Administration is not all about performance
Performance is only one component to database administration. Let’s take Jill as an example.
Jill is a SQL Server DBA who focuses on High Availability and Disaster Recovery for a large company. She works to keep her knowledge and skills sharp on backups and restores, Windows Failover Clustering, and High Availability Groups. She designs mission critical systems to meet high uptime requirements. She also designs and runs a lab where she proactively causes outages and troubleshoots them, corrupts databases and repairs them. She uses these experiences to build processes for front-line engineers to respond to production incidents and trains them to keep systems online as much as possible. Jill is excited about learning more about virtualization and SAN replication in the next year and has created an aggressive learning plan.
Jill’s pretty awesome, isn’t she? Is Jill magically not a Production DBA if she looks at an execution plan and doesn’t know what it means?
If you’re just getting into database administration, we don’t recommend that you start with performance as your primary focus. We recommend that you start at the base of the database Hierarchy of Needs. As you work your way up, decide where to specialize.
Don’t de-legitimize DBAs who are Different than You Are. Talk to Them!
It’s great to love what you do. Just remember that database administration is a really broad field, and the way you DBA isn’t necessarily the way everyone has to DBA. (Sure, DBAing is a verb. Why not?)
Let’s not fall into the trap of thinking “I’ve got to have this one skill so I can get into the cool club.” Instead, let’s get excited about the fact that there’s so much to learn that we all have to make tough choices. Let’s seek out the people who choose differently than us, and learn from them, too.
Sometimes I run into a query plan that just shocks me. It’s like looking up and suddenly seeing an adult dressed as a squirrel riding a unicycle down a busy city street. You have to stop and ask yourself, “Did that really just happen?” (I live in Portland, Oregon, so yeah, I’ve seen that.)
A while back I blogged about how to write a query to demonstrate a big memory grant against the AdventureWorks2012 database. The trick was to stuff the query’s joins full of functions so that the query optimizer became baffled about how many rows might come out of the join. The query is terrible, but it’s useful for demonstrating some problematic situations with memory on a test server.
Recently I dug out the query to set up a demo on SQL Server 2014 and something strange happened: it saw through the awfulness of my query. It made my horrible TSQL fast.
Let’s feed my terrible query to SQL Server 2014
First, make sure the new cardinality estimator will be used for your query. You do this by setting the database compatibility level to 120:
ALTER DATABASE AdventureWorks2012 SET COMPATIBILITY_LEVEL=120; GO
Now we’ll run this terrible, awful, no good query:
SELECT e.[BusinessEntityID], p.[Title], p.[FirstName], p.[MiddleName], p.[LastName], p.[Suffix], e.[JobTitle], pp.[PhoneNumber], pnt.[Name] AS [PhoneNumberType], ea.[EmailAddress], p.[EmailPromotion], a.[AddressLine1], a.[AddressLine2], a.[City], sp.[Name] AS [StateProvinceName], a.[PostalCode], cr.[Name] AS [CountryRegionName], p.[AdditionalContactInfo] FROM [HumanResources].[Employee] AS e INNER JOIN [Person].[Person] AS p ON RTRIM(LTRIM(p.[BusinessEntityID])) = RTRIM(LTRIM(e.[BusinessEntityID])) INNER JOIN [Person].[BusinessEntityAddress] AS bea ON RTRIM(LTRIM(bea.[BusinessEntityID])) = RTRIM(LTRIM(e.[BusinessEntityID])) INNER JOIN [Person].[Address] AS a ON RTRIM(LTRIM(a.[AddressID])) = RTRIM(LTRIM(bea.[AddressID])) INNER JOIN [Person].[StateProvince] AS sp ON RTRIM(LTRIM(sp.[StateProvinceID])) = RTRIM(LTRIM(a.[StateProvinceID])) INNER JOIN [Person].[CountryRegion] AS cr ON RTRIM(LTRIM(cr.[CountryRegionCode])) = RTRIM(LTRIM(sp.[CountryRegionCode])) LEFT OUTER JOIN [Person].[PersonPhone] AS pp ON RTRIM(LTRIM(pp.BusinessEntityID)) = RTRIM(LTRIM(p.[BusinessEntityID])) LEFT OUTER JOIN [Person].[PhoneNumberType] AS pnt ON RTRIM(LTRIM(pp.[PhoneNumberTypeID])) = RTRIM(LTRIM(pnt.[PhoneNumberTypeID])) LEFT OUTER JOIN [Person].[EmailAddress] AS ea ON RTRIM(LTRIM(p.[BusinessEntityID])) = RTRIM(LTRIM(ea.[BusinessEntityID])); GO
Shazam, it finishes using only 63 ms of CPU time. It asks for a 27MB memory grant and estimates 290 rows (quite accurately).
The execution plan contains warnings that “Type conversion in expression … may affect “CardinalityEstimate” in query plan choice”, but wow, it really did a remarkably good job with this!
Using OPTION(QUERYTRACEON 9481) to test Compatibility Level 110
You can go back to the old cardinality estimator with SQL Server 2014 in two ways: You could change the whole database’s compatibility level back to 110 like this:
ALTER DATABASE AdventureWorks2012 SET COMPATIBILITY_LEVEL=110; GO
But that changes it for everything in the whole database. We might just want to see how THIS query would behave using the old cardinality estimator, but still on SQL Server 2014. You can do that by adding OPTION(QUERYTRACEON 9481) to the very end of our gruesome query:
SELECT e.[BusinessEntityID], p.[Title], p.[FirstName], p.[MiddleName], p.[LastName], p.[Suffix], e.[JobTitle], pp.[PhoneNumber], pnt.[Name] AS [PhoneNumberType], ea.[EmailAddress], p.[EmailPromotion], a.[AddressLine1], a.[AddressLine2], a.[City], sp.[Name] AS [StateProvinceName], a.[PostalCode], cr.[Name] AS [CountryRegionName], p.[AdditionalContactInfo] FROM [HumanResources].[Employee] AS e INNER JOIN [Person].[Person] AS p ON RTRIM(LTRIM(p.[BusinessEntityID])) = RTRIM(LTRIM(e.[BusinessEntityID])) INNER JOIN [Person].[BusinessEntityAddress] AS bea ON RTRIM(LTRIM(bea.[BusinessEntityID])) = RTRIM(LTRIM(e.[BusinessEntityID])) INNER JOIN [Person].[Address] AS a ON RTRIM(LTRIM(a.[AddressID])) = RTRIM(LTRIM(bea.[AddressID])) INNER JOIN [Person].[StateProvince] AS sp ON RTRIM(LTRIM(sp.[StateProvinceID])) = RTRIM(LTRIM(a.[StateProvinceID])) INNER JOIN [Person].[CountryRegion] AS cr ON RTRIM(LTRIM(cr.[CountryRegionCode])) = RTRIM(LTRIM(sp.[CountryRegionCode])) LEFT OUTER JOIN [Person].[PersonPhone] AS pp ON RTRIM(LTRIM(pp.BusinessEntityID)) = RTRIM(LTRIM(p.[BusinessEntityID])) LEFT OUTER JOIN [Person].[PhoneNumberType] AS pnt ON RTRIM(LTRIM(pp.[PhoneNumberTypeID])) = RTRIM(LTRIM(pnt.[PhoneNumberTypeID])) LEFT OUTER JOIN [Person].[EmailAddress] AS ea ON RTRIM(LTRIM(p.[BusinessEntityID])) = RTRIM(LTRIM(ea.[BusinessEntityID])) OPTION(QUERYTRACEON 9481);
Retesting the query with the old cardinality estimator… Ouch! The query uses 84,109 ms of CPU time and the execution plan is back to a world of confusion, thinking it’s going to have to handle a kazillion rows:
Hey there new cardinality estimator, I’d like to get to know you better.
I’m not saying the new cardinality estimator will be better at every query, that it won’t have any regressions, or that you should start putting functions around all your joins.
But it’s pretty remarkable when the optimizer takes code that you wrote to be as terrible as possible, and suddenly makes it fast. Sign me up for more of that.
Do you rely on index rebuilds to make queries run faster? Or do you always feel like statistics are “bad” and are the cause of your query problems? You’re not alone– it’s easy to fall into the trap of always blaming fragmentation or statistics. Learn why these two tools aren’t the answer to every problem in these two videos from Kendra Little.
Does Rebuilding Indexes Make Queries Faster in SQL Server?
Learn if rebuilding indexes is a good way to help queries run faster in SQL Server in this 13 minute video.
Not convinced that fragmentation isn’t really your problem? Read Brent’s post, “Stop Worrying about Index Fragmentation“.
Why are Statistics Always the Problem? (Or ARE They?)
Once you’ve moved beyond an addiction to rebuilding indexes, you may find that you become an obsessive Statistics Blamer. Learn why you might mistakenly think your stats are “bad” when something else is really the problem in this 12 minute video.
There’s a couple of new features in SQL Server 2014 that provide options for how you work with temporary objects. Will inline index creation or memory optimized temporary tables forever change the way you code? Let’s take a look!
Inline Index Creation
SQL Server 2014 brings us a TSQL improvement called “inline specification of CLUSTERED and NONCLUSTERED indexes.” This feature works in Standard Edition and applies to many types of table. This improves the functionality of table variables. But does it make table variables as good as temp tables?
First, let’s take a look at the feature, then take it for a test drive.
Creating a table with an inline nonclustered index
Here’s a simple look at the new feature– when I create a table (nothing temporary about it), I can name and define an index on multiple columns. In this case, I’m creating a nonclustered index on the columns j and k:
CREATE TABLE dbo.IndexCreation ( i int identity constraint pkIndexCreation primary key clustered, j char(10) index ixIndexCreation_jk nonclustered (j,k), k varchar(100) ); GO
I also have the option to put all my constraint and index create statements at the end of the table, like this:
CREATE TABLE dbo.IndexCreation ( i int identity, j char(10), k varchar(100), constraint pkIndexCreation primary key clustered (i), index ixIndexCreation_jk nonclustered (j,k) ); GO
What about temp tables?
The same syntax for inline index create listed above works just fine for me on temporary tables in my CTP of SQL Server 2014.
This is a good thing for some people! One of the issues with temporary tables in stored procedures is that creating an index on the temp table can prevent caching of the temporary item. For most people, this is no big deal, but for some frequently run stored procedures, it might make a difference.
Creating the nonclustered indexes on the temp table at creation time (instead of afterward) can improve temp table caching in stored procedures.
Before you rejoice, there’s some fine print. If you change your temp table syntax to use inline index creation and it enables caching, you might run into issues where statistics aren’t updated on your temporary tables when you think they would be. (The short version: Statistics are also cached for temp tables, and Gremlins keep from updating very frequently.) Just test carefully if you’ve got very frequently run stored procedures you’re modifying.
Inline index creation on table variables
The new syntax works on table variables, too! This means that with SQL Server 2014 and higher, you can create non-unique nonclustered indexes on table variables. You can even set the fillfactor option. (Note: I’m not saying you should use a lower fillfactor– I was just surprised that option was available for table variables.)
DECLARE @IndexCreation TABLE ( i int identity primary key clustered with (fillfactor=95), j char(10) index ixIndexCreation_jk nonclustered (j,k) with (fillfactor=95), k varchar(100) ); GO
With the new SQL 2014 syntax, you will also have the option to create a non-unique clustered index on a table variable:
DECLARE @IndexCreation TABLE ( i int, j char(10), k varchar(100), index cxIndexCreation_i clustered (i), index ixIndexCreation_jk nonclustered (j,k) ); GO
So, are table variables as good as temp tables now?
Well, sorry, not generally.
First, there’s some limitations to the “inline index create” feature. You can’t inline every kind of index you might want — you can’t add included columns or filters to indexes created with an inline create statement. Since you can add indexes with those to a temporary table after it’s created (and you CANNOT do that with a table variable), it has an advantage.
But temporary tables still have another advantage.
Statistics help temporary tables
When looking at the new feature, I wondered if these new indexes declared inline might secretly allow some sort of populated statistic on table variables– which hasn’t worked before.
But, unfortunately no. Even using the inline indexes, table variables do NOT get statistics to help the optimizer psychically predict how many rows will come back based on a specific predicate value. That can cause you to get less efficient execution plans. Let’s look at an example.
First up, the temp table:
CREATE TABLE #TestMe ( i INT IDENTITY, City VARCHAR(60), StateProvinceID INT, INDEX ixTestMe_StateProvinceID NONCLUSTERED (StateProvinceID), CONSTRAINT cxTestMe_i PRIMARY KEY CLUSTERED (i) ); INSERT #TestMe (City,StateProvinceID) SELECT City, StateProvinceID FROM AdventureWorks2012.Person.Address; GO --This gets a nested loop plan SELECT City FROM #TestMe WHERE StateProvinceID=1; GO;
For the temporary table, SQL Server uses statistics associated with the nonclustered index to estimate that it will get 25 rows back (which is right on). Based on this it decides to seek to the rows in the nonclustered index, then do a nested loop lookup to fetch the City column from the clustered index. It does 52 logical reads:
Now let’s run the same code, but with a table variable with an inline index:
DECLARE @TestMe TABLE ( i INT IDENTITY PRIMARY KEY CLUSTERED (i), City VARCHAR(60), StateProvinceID INT, INDEX ixTestMe_StateProvinceID NONCLUSTERED (StateProvinceID) ); INSERT @TestMe (City,StateProvinceID) SELECT City, StateProvinceID FROM AdventureWorks2012.Person.Address; --This gets a clustered index scan SELECT City FROM @TestMe WHERE StateProvinceID=1; GO
Oddly enough, it gets a clustered index scan. It estimates that only one row will be found — that’s because for table variables, statistics associated with the nonclustered index still can’t be populated. So it doesn’t know to estimate the 25 rows, and it just guesses one.
With a one row estimate, I thought the optimizer would surely go for the nested loop lookup. Looking up just one row is easy, right? But instead it decided to scan the clustered index. (Note: The nonclustered index is functional– it will use it if I omit the City column from the query. But it does not seem to want to do a key lookup from it in my testing here. Wacky!)
Finding: Inline Index creation is the most awesome for temp tables
I like the new syntax for its added flexibility. I do wish it allowed included columns, but for indexes with just key columns it can make for tidier, more compact code.
This feature doesn’t fix all the flaws of table variables– it’s interesting that I didn’t get a nested loop lookup in my testing, which makes me wonder if the optimizer has as many options with the indexes on table variables.
The ability of temporary tables to have column and index related statistics still gives them a great advantage in most situations. Using the inline index creation script on temporary tables in stored procedure to improve caching is a nice little bonus. Even with the gotcha I linked to above about statistics updates on temp tables, I think this feature makes the case for temporary tables even stronger.
What about Memory Optimized Table Variables?
First off, this feature is more expensive. SQL Server 2014 adds in new “memory optimized” tables, AKA “Project Hekaton”. This feature is only available in Enterprise Edition. I won’t come close to covering the whole feature here– I’ll just scratch the surface of one of its uses: the “Memory Optimized Table Variable”.
The first thing of note is that memory optimized tables DO support statistics– so does that mean that a Memory Optimized Table Variable might have them? Let’s take a look!
First, let’s enable memory optimized objects
To test this feature out, I need to make some changes to my test database:
ALTER DATABASE [IndexTest] ADD FILEGROUP [ImaNewFileGroup] CONTAINS MEMORY_OPTIMIZED_DATA GO ALTER DATABASE [IndexTest] ADD FILE (name='imanewfilegroup_1', filename='C:\MSSQL\Data\imanewfilegroup_1') TO FILEGROUP [ImaNewFileGroup]; GO ALTER DATABASE [IndexTest] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON; GO
Memory_Optimized = ON !
Now I can start creating my memory optimized table variable. First, I must create a table type with the table variable’s definition. I’m not going to test this in a natively compiled stored procedure just yet, so I’m leaving off the identity (it’s not supported in this scenario). I also use some special collations and don’t allow nullability on some columns to get the whole thing to work.
CREATE TYPE TM1 as TABLE ( City VARCHAR(60) COLLATE Latin1_General_100_BIN2, StateProvinceID INT NOT NULL, INDEX ixTestMe_StateProvinceID NONCLUSTERED (StateProvinceID) ) WITH (MEMORY_OPTIMIZED = ON)
All right, now that our table type exists, we can put it to use! Let’s populate it and query it and check out what plan we get:
DECLARE @TestMe TM1; INSERT @TestMe (City,StateProvinceID) SELECT City, StateProvinceID FROM AdventureWorks2012.Person.[Address]; SELECT City FROM @TestMe WHERE StateProvinceID=1; GO
There’s a little surprise in the execution plan:
Estimated rows is still 1– statistics still aren’t working here. But magically we get an index seek on the nonclustered index instead of a scan on the clustered index. This is remarkable because back in the normal world of non-memory-optimized indexes, the City column would not be in that nonclustered index– we only asked for it to contain StateProvinceID!
Memory Optimized Indexes are Always Covering
In the brave new world of Memory Optimized tables, indexes have a motto: Always Be Covering. We’re not in Kansas any more Toto, and there’s no such thing as a Key Lookup or Bookmark Lookup anymore.
This means a couple of things: for these tables, it really doesn’t matter that you don’t have the INCLUDE syntax on inline index creation. Nonclustered indexes will always have all the columns, so why bother with an INCLUDE clause? (I’m guessing that’s why it’s not possible in the new syntax!) Also, memory optimized indexes may be larger than you’d assume because they cover everything.
For 99% of the use cases I see in the wild these days, temporary tables beat table variables easily because their ability to have statistics improves query optimization dramatically. That remains true with the enhancements in SQL Server 2014.
For the 1% of cases where stored procedures using temporary objects are run at high rates of frequency, there are a few new choices that are attractive. They both have gotchas and require testing, but they could both turn out pretty well:
Option 1: Inline Nonclustered Indexes on Temp Tables: This works in Standard Edition, so I think this will be the most widely adopted. Instead of creating a nonclustered index after the temp table exists, you can improve caching of your temp tables by creating nonclustered indexes with the temp table itself. Just make sure you don’t get into trouble with out of date statistics.
Option 2: Memory Optimized Table Variables: For you lucky people with Enterprise Edition, you can test these out. You won’t want to do exactly what I did above — you’ll probably want to use natively compiled stored procedures. You’ll definitely want to test at scale and make sure you can monitor performance of these new structures at load. That may be a little tricky: you don’t get actual execution plan or cost information for natively compiled procedures. Given the fact that the memory optimized table variables didn’t show any statistics in my testing, in complex implementations you could end up with some strange execution plans. (This post hasn’t covered questions like “How much memory are these structures using?” “How much memory do you have left?” “What happens if you run out?”) In short: there’s much to learn here– don’t rush in without making sure you can support it.
You’d love to become a Database Administrator, but how do you get your foot in the door? Kendra Little has hired junior DBAs and helps employers develop interview procedures for database administrators. In this 30 minute video you’ll learn what employers look for, steps you can take to make yourself a great candidate, and how to build the right resume to get your first DBA job.
Looking for the links from the webcast? Scroll down below the video!
- Download our free SQL Server Training Plan DBA eBook
- Join our free SQL Server Training Plan Weekly Email
- Free How to Develop Your DBA Career eBook
- SQL Server User Group Chapters (Professional Association for SQL Server)
- Practice interview questions with our $29 SQL Server DBA Job Interview Question and Answer Training Kit
SQL Server DBAs are slow to adopt Windows Server 2012 and Server 2012 R2. I frequently find teams planning new implementations of SQL Server 2012 on Windows Server 2008 R2.
There seem to be two main reasons for this:
- DBAs want to change a minimal number of factors, and are slow to trust a new version of the OS. Windows 2008R2 feels “safe.”
- Windows Server 2012 was hard to love when it first came out. With no Start menu, Windows 2012 was more baffling than exciting to most production DBAs.
But these reasons don’t hold up– and staying on an old OS isn’t doing you any favors. If you’re planning a SQL Server 2012 (or upcoming 2014) installation, you should also be using Windows Server 2012 or Server 2012 R2.
Windows Server 2008R2 isn’t “Safer” or “Simpler” than Server 2012 R2
Let’s break down that first concern above. The essense of it is that staying on Windows Server 2008R2 seems less risky than using a new OS.
But let’s look at that closely. The most recent service pack for Windows 2008R2 was released in March 2011– that was Service Pack 1. (I bet I’m not the only person who has searched in vain for SP2 repeatedly, thinking I must have just missed it.)
Lots of fixes have been released since Service Pack 1. Here’s a few highlights:
- KB 2566205 – Scalability of the I/O performance decreases in Windows Server 2008 R2 if more than 256 GB of physical memory is used
- KB 2468345 – Computer intermittently performs poorly or stops responding when the Storport driver is used in Windows Server 2008 R2
- KB 2545635 – You experience disk I/O performance issues on a computer that has an Intel Westmere-EX processor with at least 8 sockets and that is running Windows Server 2008 R2
- KB 2553549 – All the TCP/IP ports that are in a TIME_WAIT status are not closed after 497 days from system startup in Windows Vista, in Windows 7, in Windows Server 2008 and in Windows Server 2008 R2
Which patches you need varies by your configuration, and this is just the tip of the iceberg.
When you’re using high availability solutions, the list of patches you need to consider just gets bigger– and the risks get higher. In this post Brent describes a cluster-specific bug that was released after Stack Exchange experienced Availability Group outages due to a clustering issue.
Although Windows 2008R2 has been out for a while, managing an Availability Group or a Windows Failover Cluster on it isn’t simpler– it’s actually more complicated! You’ve got more patches you need to be aware of and manage, and you still need to keep an eye out for new fixes as they’re released.
Failover Clustering has huge new features in quorum
Don’t get me wrong– Windows 2008 R2 has some pretty nice features when it comes to setting up a cluster, particularly compared to older versions of Windows. I love the Cluster Validation Wizard. I find the Failover Cluster Manager snap-in to be pretty intuitive and easy to manage.
But Failover Clusters have grown up significantly– particularly when it comes to your options with making sure that your applications stay online. And having more ways to keep applications online is probably the exact reason that you’re using features involving clustering!
Dynamic Quorum and Vote Assignment – Windows Server 2012
Windows Server 2012 introduced “Dynamic Quorum“. Dynamic Quorum lets the cluster add and remove votes for nodes if they shut down or fail. This makes it more likely for the cluster to maintain a majority if you have multiple sequential failures.
Windows Server 2012 also lets you easily customize quorum configuration and remove votes from some nodes if you’d like to. To remove node votes with Windows Server 2008R2, you’d have to know to install KB 2494036 and then reconfigure quorum.
Bonus: as of Windows Server 2012, the Windows Failover Clustering feature is part of Windows Standard Edition.
Dynamic Witness, Tiebreakers – Windows Server 2012 R2
Windows Server 2012 R2 introduces even more quorum features. The Dynamic Witness feature lets the cluster decide when it will or won’t give a vote to the witness you have configured.
This means that if you have multiple sequential failures, the cluster has more options and is smarter about keeping the right number of votes available. It also means that the guidance about creating a witness is simpler: since the vote can be managed dynamically, you should always create a witness.
Windows 2012 R2 has more features, too– the cluster is smarter about what happens when things start to come back online after you force quorum, and it has options for specifying a tiebreaker if you have multiple failures at the same time.
And it’s a simple thing, but I love it the most: in 2012 R2 you can see what’s happening with dynamic votes for nodes by using the Failover Cluster Manager (no PowerShell commands required). This makes testing different failure scenarios much more straightfoward as you can quickly and easily see how many votes there are, and who’s got them.
Oh, and they fixed that Start menu thing, too.
You can’t easily migrate your Windows 2008R2 Cluster to Windows 2012+
The final reason I wouldn’t plan a new highly available environment on Windows 2008R2 is simply that I don’t want to be stuck there. You might think, “We’ll start out on a known OS and then upgrade soon” — but that’s easier said than done.
If you’re on a Windows 2008R2 cluster and you want to upgrade, you will need to create a new, separate failover cluster using Windows Server 2012, and then migrate to it. That requires separate storage, separate servers, and a careful migration plan. It’s absolutely doable, but it’s not simple, cheap, or easy. (Note that the built-in “Migrate a Cluster” wizard doesn’t support SQL Server installs for Windows 2008R2 -> Windows 2012.)
What about SQL Server 2008? Or 2005?
If you’re doing a new server buildout, you may decide to install SQL Server 2008 R2 on a Windows 2012 / 2012 R2 failover cluster. That’s supported, but to get installation to work you may need to use a slipstream installation and take a few other steps. (I think slipstreaming is great, but budget extra time to get everything tested and working.)
If you’re thinking about running SQL Server 2005 on Windows 2012 or Windows 2012 R2, that’s just not supported by Microsoft.
It’s Time to Embrace Our New OS Overlord
I really like Windows 2008 R2. I’ve used it for years, it’s very familiar, and I feel like I know its quirks. But I also recognize that it’s got a lot of baggage. It’s not really simpler or easier to manage.
The improvements to Windows Failover Clustering features in later versions of the operating system should be very compelling to anyone considering clustering SQL Server or using Availability Groups. It’s not always perfect and it’s certainly complicated, but it’s much better than what you’ve got in Windows 2008R2.
It’s time to start planning your new SQL Server implementations with Windows 2012 and higher.
Ever wanted to prove that your code or index changes had a positive impact? In this 30 minute video, Kendra shows you how to use Extended Events to measure a production workload– and how you can aggregate the results to prove your changes made a difference.
Want to skip straight to the demos? Here’s a quick timeline:
- Demo 1: How I generate a fake workload – 2:45
- Demo 2: Taking an initial benchmark with Extended Events – 4:45
- Demo 3: Aggregating the results of the Extended Events Trace -10:52
- Demo 4: Taking a second benchmark with Extended Events and aggregating the results – 18:05
Want a sample of the type of Extended Events trace run in the demo? Here’s a sample script. This isn’t any rocket science on my part: I generated the script with the Extended Events wizard and then told it to script it out. (Try it yourself!)
--****************** --These scripts were generated from SQL Server 2012 --Management Studio Extended Events Wizard --Customization is primarily just formatting for readability --****************** CREATE EVENT SESSION [Production Perf Sample-Before] ON SERVER ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1) ACTION(sqlserver.client_app_name,sqlserver.database_id, sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id) WHERE ((([package0].[divides_by_uint64]([sqlserver].[session_id],(5))) AND ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)))) AND ([package0].[equal_boolean]([sqlserver].[is_system],(0))) AND ([sqlserver].[query_hash]<>(0)) )), ADD EVENT sqlserver.sql_statement_completed( ACTION(sqlserver.client_app_name,sqlserver.database_id, sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id) WHERE ((([package0].[divides_by_uint64]([sqlserver].[session_id],(5))) AND ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)))) AND ([package0].[equal_boolean]([sqlserver].[is_system],(0))) AND ([sqlserver].[query_hash]<>(0)) )) ADD TARGET package0.event_file (SET filename=N'c:\Xevents\Traces\Production Perf Sample-Before') WITH ( EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, TRACK_CAUSALITY=ON, STARTUP_STATE=OFF ) GO --ALTER EVENT SESSION [Production Perf Sample-Before] ON SERVER STATE=START; --GO --ALTER EVENT SESSION [Production Perf Sample-Before] ON SERVER STATE=STOP; --GO
One of my favorite things about SQL Server is the instrumentation. It’s incredibly good at giving you information about what’s going on in the database engine and query optimizer.
Want to know why your server is slow? Need to know what made it go offline? You have a lot of tools available.
This hasn’t always been the case. One of the toughest things about the old SQL Server 2000 is the lack of instrumentation. The tools that help you improve SQL Server have all been added in the last 10 years.
The cool thing is that the instrumentation gets better and better with each version of SQL Server– new features get new Dynamic Management Views and functions. And sometimes we get a whole new feature to help us get insight into SQL Server.
If you’ve still got SQL Server 2000 instances where performance and availability is important, explain to your management how limited the tools are to diagnose and repair issues. SQL Server’s come an awful long way!
The other day I was running a script I’d written to expand out some tables in the AdventureWorks sample database for some testing. The script was taking a long time, and at first I thought I was just spoiled because I usually run my tests on my laptop, whose storage is backed by SSDs. In this case I was running my test against a SQL Server instance up in AWS.
After a delicious fifteen minute coffee break, I realized something was wrong– it STILL wasn’t done. I grabbed sp_AskBrent® to do a quick check and see if my storage was really THAT slow.
I ran sp_AskBrent® with a 60 second sample, like this:
exec sp_AskBrent @seconds=60, @ExpertMode=1; GO
Here’s what I saw (click for a larger version):
PREEMPTIVE_OS_WRITEFILEGATHER means slow file growths
In a 60 second sample, I’d been waiting for PREEMPTIVE_OS_WRITEFILEGATHER for 36 seconds. Whoops!
The script I was running was making tables larger. The database was out of room, and was growing the data file in small autogrowth increments. Each of these automatic growths was stopping to “zero out” the files, because I hadn’t enabled Instant File Initialization in SQL Server.
How I enabled Instant File Initialization
My test instance is set up a bit uniquely– it uses UNC paths for the SQL Server databases. This is supported in SQL Server 2012, and I was testing it out in a Windows Failover Cluster. (If you use shares for files, you need to be very careful about redundancy and availability of course, but that’s a topic for another post.)
I’d forgotten to enable Instant File Initialization for the SQL Server Service on the server that hosted the file shares! Wherever Windows will actually grow the files, the SQL Server Service account needs the “Perform Volume Maintenance” tasks right.
Using the Local Security Policy to Grant “Perform Volume Maintenance Tasks”
Here’s how to grant the rights:
First, open the local security. I usually do this by running the command secpol.msc, but you can also find this in the “Administrative Tools” menu.
Then navigate to Local Policy -> User Right Assignment -> Perform Volume Maintenance Tasks.
Add the SQL Server Service account. The change will take effect the next time you restart SQL Server.
So, did it improve my performance?
Heck yeah, it did! On the first run I got tired of waiting and killed my expansion script after 20 minutes and just dealt with the rollback. After enabling Instant File Initialization on fileserver that holds the shares where my database files live, the expansion script finished in four minutes and 48 seconds. Shazam!
Temp tables are like real tables, just a little tricker.
When you’re starting out writing TSQL, it’s easy to want to do all your work in a single query. You learn about derived sub-queries, CROSS APPLY statements, and common table expressions. Suddenly, each of your queries is so complex that you hardly know what you’re doing.
But complex TSQL can be quite fragile. Small mis-estimations in parts of the plan can cause things to go very much awry in other regions of the plan.
Temp tables can help performance!
One treatment for slow, complex TSQL is to break it into multiple steps. You create a temp table, populate it, and reference it in future queries. This can lead to improved estimates and reliability, because temporary tables can have column and index related statistics. Those statistics help the optimizer estimate how many rows will be returned at different points along the way.
But this tuning technique isn’t foolproof. It’s easy to forget that statistics can be tricky– just like with real tables. Let’s take a look and prove that statistics issues from “normal” tables also matter on temp tables.
Out of date statistics on temp tables can hurt performance
The first step is to load up a temp table with some rows:
INSERT #temp (TransactionID, TempValue) SELECT TOP 100000 TransactionID, 1 FROM Production.TransactionHistory; GO INSERT #temp (TransactionID, TempValue) SELECT TOP 10000 TransactionID, 2 FROM Production.TransactionHistory; GO
We now have:
- 100K rows where TempValue=1
- 10K rows where TempValue=2
Now let’s say I query the temp table in some way. My where clause is for a specific TempValue. The act of running this query causes a column level statistic to be created on the TempValue column:
SELECT max(th.Quantity) FROM #temp as t JOIN Production.TransactionHistory th on t.TransactionID=th.TransactionID WHERE TempValue=2; GO
We can see the statistic, too. This code is modified from Books Online and uses a DMV available in SQL Server 2012 SP1 and higher:
use tempdb; GO SELECT sp.stats_id, name, last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter FROM sys.stats AS stat CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp WHERE stat.object_id = object_id('#temp'); GO
I continue on, and insert 11K rows for TempValue=3:
INSERT #temp (TransactionID, TempValue) SELECT TOP 11000 TransactionID, 3 FROM Production.TransactionHistory; GO
At this point I have:
- 100K rows where TempValue=1
- 10K rows where TempValue=2
- 11K rows where TempValue=3
Now I run another query which wants to look ONLY at the 11K rows where TempValue=3. SQL Server completely mis-guesses how many rows will be returned– instead of 11K rows, it guesses that I’ll just get a SINGLE row:
SELECT max(th.Quantity) FROM #temp as t JOIN Production.TransactionHistory th on t.TransactionID=th.TransactionID WHERE TempValue=3; GO
Looking at the statistics, we can see why– the statistic that was created when I ran my first SELECT query wasn’t triggered to update:
Let’s look at the histogram
When we look at the histogram for the statistic, it confirms that it doesn’t know about any rows with a TempValue higher than two:
Bad Statistics can lead to bad optimization
If I was joining from this temp table to other large tables, it might cause a very large data skew in my plan– and it could cause big problems. The optimizer might select inefficient joins, not allocate enough memory, and choose not to go parallel if estimates are skewed too low.
Temp tables are a great tool to use, but remember a few key rules:
- Column level statistics will be created for you when you run queries using the temp table
- If you modify the temp table in multiple steps, those statistics may get out of date
In complex procedures, manually creating and updating statistics on columns or indexes can save you time and improve execution plan quality.
Could Trace Flag 2371 Help?
If you find that you have a lot of existing code that has this problem, Trace Flag 2371 may help. This trace flag is available as of SQL Server 2008R2 SP1 and higher, and it changes the default threshold for when statistics automatically update.
The net effect of the trace flag is to cause statistics updates to happen more frequently on large tables. You must turn it on server wide for it to take effect — it doesn’t work for individual sessions.
I’m not a huge fan of enabling any trace flags unless you’re solving a specific problem, and documenting it. This trace flag is not very widely used in my experience, so flipping it on puts you in a small subset of people. If you start hitting wacky issues and need to resolve them, suddenly the trace flag gives you a whole new dimension of complexity. So please don’t think of this trace flag as “preventive medicine” — only use if it you don’t have other good ways to solve the problem.