The plan cache holds a lot of secrets about what’s going on inside SQL Server. In the First Responder Kit we shared one of our plan cache scripts to find the top resource consuming queries. That query works well, but over time we’ve added some additional functionality to the query. I figured it was time to share the new query that we’re using to analyze SQL Server performance.
Our existing query looks at individual query stats, but it doesn’t take into account stored procedure or trigger execution stats. During our SQL Critical Care checks, we’ve found it helpful to look at both procedures and triggers to figure out if they were causing problems for overall server health.
What Are We Looking At?
The original query just looked at
sys.dm_exec_query_stats and looked at average and total metrics across CPU, duration, and logical reads.
This was a helpful approach, but over time it’s become apparent that we needed to look at more than just individual statements – what if a single procedure was causing problems that only show up in aggregate?
The Bigger Picture
To get a view of the bigger picture, we added in two more DMVs -
sys.dm_exec_procedure_stats. This gives us a big picture view of what’s going on inside SQL Server – the only thing that would make this better would be a DMV for function execution stats.
To avoid skewing results, data is aggregated by the
query_hash - unfortunately this means the queries won’t work against SQL Server 2005. Not only do we rank queries by CPU, IO, duration, and execution count, but a second level of ranking is provided that ranks queries within their logical grouping – by statement, procedure, and trigger. If you want to see what your most expensive trigger is doing, it’s easy enough to make a quick change to the query.
Check it out:
You can download the script in the usual way – by agreeing to our crazy terms of service, selling your email address to a foreign government, and clicking “download”.
Using the Query
This query is easy enough to start using right way – just download the file and run it. Once you’re familiar with it, scroll to the bottom and you’ll see two separate queries you can run. The first is suitable for pasting into Excel – it has no query plan and the SQL text is shortened to easily paste into a single cell. The second query has everything that you want.
Since data is dumped into a temporary table during analysis, it’s easy to keep re-querying the temporary table as you re-sort data or refine what you’re looking for.
Ever wonder how someone else does it? There’s no right way or wrong way, but in this 20-minute session, you can peer over Brent’s shoulder (virtually) while he takes a few Stack Overflow queries, tries various techniques to make them faster, and shows how he measures the before-and-after results.
For the links and scripts, check out the Watch Brent Tune Queries page.
Check out SQLServerBuilds.blogspot.com, a site that lists cumulative updates and service packs. Here’s a remixed version:
The first service pack seems to come out fairly quickly, but after the first one, it’s a year or more. Makes sense – you find a lot of bugs quickly, right?
Microsoft released at least one service pack per year, but … not last year. 2013 saw no service pack releases, and it’s been 442 days since the last service pack (SQL 2012 SP1) shipped.
Is Microsoft taking too long? Let’s look at each current version missing a service pack:
- SQL 2012 has been waiting 442 days for SP2 – but that’s actually right in the range for normal SP2 releases. SQL Server 2008 went 540 days before SP2.
- SQL 2008R2 has been waiting 545 days for SP3 – but that’s also less time than it took for SQL 2005 to get its SP3, so no cause for panic here yet.
- SQL 2008 has been waiting 839 days for SP4 – here we actually do see some cause for alarm, because no supported SQL Server version has gone that long without a service pack.
When you step back and take the long view, we’re not really in that bad of shape yet – but I can see why people would be disturbed that no service packs have been released in over a year. It might just be a timing coincidence, or it might be something more.
But it does beg the question – what if Microsoft just stopped releasing SQL Server service packs altogether, and the only updates from here on out were hotfixes and cumulative updates? How would that affect your patching strategy? Most shops I know don’t apply cumulative updates that often, preferring to wait for service packs. There’s an impression – correct or not – that service packs are better-tested than CUs.
The latest version of our SQL Server health check stored procedure is out today. Here’s what we’ve added in the last couple of versions – big thanks to the folks who keep making this even better for the community:
Changes in v33 – January 20, 2014:
Bob Klimes fixed a bug that Russell Hart introduced in v32, hahaha. Check 59 was false-alarming on Agent jobs that actually had notifications.
Changes in v32 – January 19, 2014:
- Russell Hart fixed a bug in check 59 (Agent jobs without notifications).
- Added @EmailRecipients and @EmailProfile parameters to send the results via Database Mail. Assumes that database mail is already configured correctly. Only sends the main results table, and it will not work well if you also try to use @CheckProcedureCache. Execution plans will not render in email.
- Fixed a bug in checks 108 and 109 that showed poison waits even if they had 0ms of wait time since restart.
- Removed check 120 which warned about backups not using WITH CHECKSUM. We fell out of love with WITH CHECKSUM – turns out nobody uses it.
- Added check 121 – Poison Wait Detected: Serializable Locking – looking for waits with %LCK%R%. Happens when a query uses a combination of lock hints that make the query serializable.
- Added check 122 – User-Created Statistics In Place. There is nothing wrong with creating your own statistics, but it can cause an IO explosion when statistics are updated.
- Added check 123 – Multiple Agent Jobs Starting Simultaneously. Ran into an issue where dozens of jobs started at the exact same time every hour.
Changes in v31 – December 1, 2013:
- Dick Baker, Ambrosetti Ltd (UK) fixed typos in checks 107-109 that looked for the wrong CheckID when skipping checks, plus improved performance while he was in there.
- Dick also improved check 106 (default trace file) so that it will not error out if the user does not have permissions on sys.traces.
- Christoph Muller-Spengler @cms4j added check 118 looking at the top queries in the plan cache for key lookups.
- Philip Dietrich added check 119 for TDE certificates that have not been backed up recently.
- Ricky Lively added @Help to print inline help. I love his approach to it.
- Added check 120 looking for databases that have not had a full backup using the WITH CHECKSUM option in the last 30 days.
During next week’s Watch Brent Tune Queries webcast, I’m using my favorite demo database: Stack Overflow. The Stack Exchange folks are kind enough to make all of their data available via BitTorrent for Creative Commons usage as long as you properly attribute the source.
There’s two ways you can get started writing queries against Stack’s databases – the easy way and the hard way.
The Easy Way to Query StackOverflow.com
Point your browser over to Data.StackExchange.com and the available database list shows the number of questions and answers, plus the date of the database you’ll be querying:
At the time of this writing, the databases are updated every Monday. If you want even more recent data, you can use the Stack Exchange API, but that’s a story for another day.
Click on the site you’d like to query, and you’ll get a list of queries you can start with, or click Compose Query at the top right. As an example, let’s look at a query that compares the popularity of tags:
Yes, this is a lot like SQL Server Management Studio in the browser. At the top, we’ve got our query, plus space for a couple of parameters. One of the fun parts about Data Explorer is that you can design queries to take parameters to show information for different users, date ranges, tags, etc.
At the bottom, notice the tabs for Results, Messages, and Graph. If your results look graph-friendly, Data Explorer is smart enough to figure that out:
And yes, Data Professional, that last tab does indeed say Execution Plan, and it renders in your browser right down to the ability to hover your mouse over parts of the plan and see more details:
Some system commands (like SET STATISTICS IO ON) are allowed, but you can’t create indexes, and there aren’t many indexes to begin with. You can also shoot yourself in the foot by writing an extraordinarily ugly query, and the system won’t stop you – for example, SELECT * FROM Posts will start running, but then may crash your browser as they start returning data. Jeremiah and I managed to repeatedly kill our Chrome browsers while tuning queries for fun.
I like using this to go poking around for unusual questions or answers. For example, I like to find questions that are viewed a lot, but don’t have any upvoted answers yet. (That’s prime territory for a SQL Server geek like me that wants to find tough questions to solve.)
The Hard Way to Query StackOverflow.COM
First, you’ll need to download a copy of the most recent XML data dump. These files are pretty big – around 15GB total – so there’s no direct download for the entire repository. There’s two ways you can get the September 2013 export:
- Download them from Mega - which lets you pick the specific sites you’d like to download.
- Download them all at once via BitTorrent with the StackExchange torrent link at Archive.org
I strongly recommend working with a smaller site’s data first like DBA.StackExchange. If you decide to work with the monster StackOverflow.com’s data, you’re going to temporarily need:
- ~15GB of space for the download
- ~60GB after the StackOverflow.com exports are expanded with 7zip. They’re XML, so they compress extremely well for download, but holy cow, XML is wordy.
- ~50GB for the SQL Server database (and this will stick around)
Next, you need a tool to load that XML into the database platform of your choosing. For Microsoft SQL Server, I use Jeremiah’s improved version of the old Sky Sanders’ SODDI. Sky stopped updating his version a few years ago, and it’s no longer compatible with the current Stack dumps. Jeremiah’s current download is here, and it works with the January 2014 data dump. (The previous version works on the September 2013 data dump.)
The SODDI user interface expects the XML files to be stored in a very specific folder name: MMYYYY SITEINITIALS, like 092013 SO. SODDI will import multiple sites, and it creates a different schema for each site. I just want to import Stack Overflow by itself, all in its own database, so I like naming my folder “092013 dbo”. That way, it creates the tables in the dbo schema, which is just a little friendlier for demos.
When you run SODDI.exe without parameters, this GUI pops up (assuming that you named your Stack Overflow demo folder 092013 dbo):
Source is the folder where you saved the data dumps. It expects to see subfolders in there for 092013 dbo.
Target is the connection string for your database server. I’m using a local SQL Server (note that I picked SqlClient in the Provider dropdown) with a database named StackOverflow, so my connection string is:
Data Source=(local); Initial Catalog=StackOverflow; Integrated Security=True
If you want to use a remote SQL Server, you’d put its name in there instead of (local). You’ll also need to pre-create the database you want to use.
Click Import, and after a lot of disk churn, you’re rewarded with a StackOverflow database with tables for Badges, Comments, Posts, PostTypes, Users, Votes, and VoteTypes.
The resulting database is about 50GB. SQL Server’s data compression doesn’t work too well here because most of the data is off-row LOBs. Backup compression works well, though, with the resulting backup coming in at around 13GB.
Why Go to All This Work?
When I’m teaching performance tuning of queries and indexes, there’s no substitute for a local copy of the database. I want to show the impact of new indexes, analyze execution plans with SQL Sentry Plan Explorer, and run load tests with HammerDB.
That’s what we do in our SQL Server Performance Troubleshooting class – specifically, in my modules on How to Think Like the Engine, What Queries are Killing My Server, T-SQL Anti-patterns, and My T-SQL Tuning Process. Forget AdventureWorks – it’s so much more fun to use real StackOverflow.com data to discover tag patterns, interesting questions, and helpful users.
In theory, you can add indexes online with SQL Server Enterprise Edition.
In theory, with AlwaysOn Availability Groups, you can add and drop indexes on the primary replica whenever you want.
In theory, you can perform read-only queries on the secondaries whenever you want, and nobody gets blocked.
In practice, these things don’t always add up to the same answer.
I’ve got an AlwaysOn Availability Group demo lab with SQL Server 2014 hosting the AdventureWorks2012 database. I’ll start on my secondary server, and I’ll get all of the suffixes from the Person.Person table:
The query results aren’t important – instead, I’m showing the actual execution plan, which is very important. By default, there’s no index on the Suffix field, so I get a clustered index scan.
Say that for some bizarre reason, this is the type of query my end users constantly run, so I decide to switch over to the primary to add an index on Suffix. (As a reminder, you have to add indexes on the primary, not the secondaries.) After adding the index, I run the query again:
Woohoo! We’ve now got an index scan on my new index. (Yeah, it’s not a seek, but this is a crappy query, and a scan is the best I’m going to get.) So far, so good.
But now let’s mix things up a little – let’s run that same query, but start a transaction with it:
Now I have an open transaction, which really shouldn’t mean anything because you can’t do updates on the secondary. However, switch over to the primary, and drop that newly created index. Back over on the secondary, where we’ve got that open transaction, run the exact same query again:
Our transaction has been knocked out and failed. It’s funny to think of a read-only transaction as “failing”, but if you have a stored procedure that starts things off with BEGIN TRAN and then does a lot of reads, thinking it’s going to get a complete point-in-time picture of the data, that’s not going to work.
I know what you’re thinking: “But Brent, I don’t even use snapshot isolation, because I read Kendra’s post about it, and I know we’re not ready to test our app with it yet.” Thing is, SQL Server uses RCSI behind the scenes on all AlwaysOn Availability Group replicas – and it’s lying to you about whether or not the feature is enabled. Here’s the sys.databases view for my secondary replica:
SQL Server claims that for the AdventureWorks2012 database, both snapshot isolation and read committed snapshot are off – but it’s lying to you. Read Books Online about querying the replicas and note:
Read-only workloads use row versioning to remove blocking contention on the secondary databases. All queries that run against the secondary databases are automatically mapped to snapshot isolation transaction level, even when other transaction isolation levels are explicitly set. Also, all locking hints are ignored. This eliminates reader/writer contention.
They’re not kidding – even if I specify dirty reads, I can run into the same problem. Here, I start a transaction, but I’ve asked for the dirtiest, no-lockin-est reads I can get:
But when I add or drop a related index on the primary, even WITH (NOLOCK) transactions are affected:
This starts to point to the challenge of running complex reporting jobs off the secondary replicas. SQL Server is constantly changing the data underneath your queries, and most of the time, it cooperates beautifully. However, if you have a long-running stored procedure (like minutes or hours long), and you want a point-in-time picture of the underlying data while you generate reports on the replica, you don’t want to play around with transactions. Instead, a database snapshot might be a better fit.
If you’d like to run these demos on your own lab, download the scripts here.
While working on some DMV scripts, I came up with a lazy way to have a user definable sort order in the query that seemed like pure genius. I showed it to the team and they’d never seen anything like it before.
Users like to be in control. They want to define custom columns, sort orders, and basically drag, drop, and pivot chart their way to victory. While I’m not going to show you how to build custom everything, we can look at a custom sort order.
Let’s start with a simple query:
SELECT SalesOrderNumber, OrderDate, DueDate, ShipDate, PurchaseOrderNumber, AccountNumber, SubTotal, TaxAmt, Freight, TotalDue FROM Sales.SalesOrderHeader
Our users want to be able to define a custom sort order on this query. We could solve this in a few ways:
- Writing several stored procedures
- Use dynamic SQL to build an
Writing several stored procedures is tedious and error prone – it’s possible that a bug can be fixed in one of the stored procedures but not the others. This solution also presents additional surface area for developers and DBAs to test and maintain. The one advantage that this approach has is that each stored procedure can be tuned individually. For high performance workloads, this is a distinct advantage. For everything else, it’s a liability.
We could also use dynamic SQL to build an order clause. I wanted to avoid this approach because it seemed hacky. After all, it’s just string concatenation. I also wanted to work in the ability for users to supply a top parameter without having to use the
The First Attempt
My first attempt at rocket science looked like this:
DECLARE @SortOrder VARCHAR(50) = 'OrderDate'; SELECT rn, SalesOrderNumber, OrderDate, DueDate, ShipDate, PurchaseOrderNumber, AccountNumber, SubTotal, TaxAmt, Freight, TotalDue FROM ( SELECT CASE @SortOrder WHEN 'OrderDate' THEN ROW_NUMBER() OVER (ORDER BY OrderDate DESC) WHEN 'DueDate' THEN ROW_NUMBER() OVER (ORDER BY DueDate DESC) WHEN 'ShipDate' THEN ROW_NUMBER() OVER (ORDER BY ShipDate DESC) END AS rn, SalesOrderNumber, OrderDate, DueDate, ShipDate, PurchaseOrderNumber, AccountNumber, SubTotal, TaxAmt, Freight, TotalDue FROM Sales.SalesOrderHeader ) AS x ORDER BY rn ASC;
Why do it this way? There are a few tricks with paging that you can perform using
ROW_NUMBER() that I find to be more readable than using
FETCH in SQL Server 2012. Plus
ROW_NUMBER() tricks don’t require SQL Server 2012.
Unfortunately, when I looked at the execution plan for this query, I discovered that SQL Server was performing three separate sorts – one for each of the case statements. You could generously describe this as “less than optimal”.
Even though it seems like SQL Server should optimize out the
CASE statement, the obvious thing doesn’t happen. SQL Server has to compute the
ROW_NUMBER() for every row in the result set and then evaluate the condition in order to determine which row to return – you can even see this in the first execution plan. The second to last node in the plan is a Compute Scalar that determines which
ROW_NUMBER() to return.
I had to dig in and figure out a better way for users get a custom sort option.
CASE to the
My next attempt moved the custom sort down to the
ORDER BY clause:
DECLARE @SortOrder VARCHAR(50) = 'OrderDate'; SELECT SalesOrderNumber, OrderDate, DueDate, ShipDate, PurchaseOrderNumber, AccountNumber, SubTotal, TaxAmt, Freight, TotalDue FROM Sales.SalesOrderHeader ORDER BY CASE @SortOrder WHEN 'OrderDate' THEN ROW_NUMBER() OVER (ORDER BY OrderDate DESC) WHEN 'DueDate' THEN ROW_NUMBER() OVER (ORDER BY DueDate DESC) WHEN 'ShipDate' THEN ROW_NUMBER() OVER (ORDER BY ShipDate DESC) END ASC
This ended up performing worse than the first attempt (query cost of 8.277 compared to the original query’s cost of 6.1622). The new query adds a fourth sort operator. Not only is the query sorting once for each of the possible dates, it’s then performing an additional sort on the output of the
ROW_NUMBER() operator in the
ORDER BY. This clearly isn’t going to work.
Getting Rid of
It seems like
ROW_NUMBER() really isn’t necessary for our scenario. After all – I only added it as a trick if so I could potentially add paging further down the road. Let’s see what happens if we remove it from the query:
DECLARE @SortOrder VARCHAR(50) = 'OrderDate'; SELECT SalesOrderNumber, OrderDate, DueDate, ShipDate, PurchaseOrderNumber, AccountNumber, SubTotal, TaxAmt, Freight, TotalDue FROM Sales.SalesOrderHeader ORDER BY CASE @SortOrder WHEN 'OrderDate' THEN OrderDate WHEN 'DueDate' THEN DueDate WHEN 'ShipDate' THEN ShipDate END DESC
Right away, it’s easy to see that the query is a lot simpler. Just look at the execution plan:
This new form of the query is a winner: the plan is vastly simpler. Even though there’s a massive sort operation going on, the query is still much cheaper – the over all cost is right around 2 – it’s more than three times cheaper than the first plan that we started with.
There’s one downside to this approach – we’ve lost the ability to page results unless we either add back in the ROW_NUMBER() or else use
Bonus Round: Back to
While using my brother as a rubber duck, he suggested one last permutation – combine the
ORDER BY technique with the
DECLARE @SortOrder VARCHAR(50) = 'OrderDate'; SELECT rn, SalesOrderNumber, OrderDate, DueDate, ShipDate, PurchaseOrderNumber, AccountNumber, SubTotal, TaxAmt, Freight, TotalDue FROM ( SELECT ROW_NUMBER() OVER (ORDER BY CASE @SortOrder WHEN 'OrderDate' THEN OrderDate WHEN 'DueDate' THEN DueDate WHEN 'ShipDate' THEN ShipDate END DESC) AS rn, SalesOrderNumber, OrderDate, DueDate, ShipDate, PurchaseOrderNumber, AccountNumber, SubTotal, TaxAmt, Freight, TotalDue FROM Sales.SalesOrderHeader ) AS x ORDER BY rn ;
This ended up being almost as fast as the
ORDER BY approach; this query’s cost is only 0.00314 higher than the
ORDER BY. I don’t know about you, but I would classify that as “pretty much the same”. The advantage of this approach is that we get to keep the
ROW_NUMBER() for paging purposes, there is only one sort, and the code is still relatively simple for maintenance and development purposes.
Check out the plan!
What Did We Learn?
I learned that trying to be smarter than SQL Server can lead to pretty terrible performance. It’s important to remember that the optimizer reserves the right to re-write. In the first and second case, SQL Server did me a favor by re-writing the query. Our third case is fairly obvious. The fourth example is somewhat surprising – by shifting around the location of the
CASE, we’re able to eliminate multiple sorts and gain the benefit of using
The year: 2005.
What was happening with the Brent Ozar Unlimited crew?
I was working on the help desk for a small company while attending Fox Valley Tech College to earn my associate degree. I think I still wanted to go into programming at that point in time! I’d never been to a user group meeting or posted on a forum. I wasn’t even a runner!
Brent took his first full-time DBA gig at Southern Wine & Spirits. He worked on his first cluster and his first 1 TB+ database. He’d never written about Perfmon or attended a conference.
Kendra worked for a dot-com in Seattle. She spent a lot of time with 32-bit servers, automating installs, restores, and configuration of SQL Server for large environments.
Doug was working on a VB6 app for insurance adjusters. At home he had a smokin’ PC with a 1.0 GHz AMD Athlon and a 19” CRT monitor. He and his wife didn’t have kids yet, and he surprised her with a trip to Venice for Christmas.
Jeremiah was (don’t laugh) a developer. While sitting on the bench, he decided to revise the company’s project management software. He rapidly prototyped a new system using SQL Server instead of Access and the .NET Framework with an ORM instead of ad hoc SQL written in VBScript and classic ASP.
The Technology World
Star Wars Episode III was released. YouTube was launched. The Xbox 360 was released.
And, after long last, Microsoft released SQL Server 2005. It had been a long five years since SQL Server 2000. There were drastic changes between the two editions. DMVs (dynamic management views), CLR, hot-add memory, ranking functions, and the XML data type were introduced. Database Mirroring, a new HA feature, was available as of SP1. SQL Server Management Studio: need I say more? These were big changes!
Jump in the time machine. 9 years later, I’m writing this blog post. It’s January, and a new year is beginning.
I’ve finished college, I’ve moved up from the help desk (way up), I’ve been to – and spoken at – a couple user group meetings just in the last month, and I’ve run a couple marathons.
Brent? He’s done OK. He’s attained Microsoft Certified Master (MCM) status in SQL Server. He’s gone from DBA to software evangelist to consultant. He’s spoken all over the world.
Kendra has also attained MCM status and become a consultant. She’s learned a lot about hardware, query tuning, and when it pays to upgrade your environment instead of sinking countless people-hours into solving a problem.
Doug is the newest member of the Brent Ozar Unlimited crew. He’s spent his 9 years learning everything he knows about SQL Server, and becoming a blogger, presenter, and user group leader.
Jeremiah used his software project to set the course for his career. He learned he had a knack for databases, and he ran with it. He too has become a successful consultant, blogger, and speaker.
Technology? It’s changed a bit, too.
Our iPads are now as powerful as Doug’s computer was back in the day. The Xbox One and PlayStation 4 launched this year. Instead of carrying around a phone, a laptop, a camera, and scanner, we carry one smartphone that does everything.
SQL Server, 9 Years, and You
SQL Server has changed dramatically, too!
SQL Server 2012 has been out for well over a year, and SP1 was released in 2013. This release brought some big changes. Internally, the way memory is handled was changed, improving operations and efficiency. There are improved T-SQL functions, such as more windowing functions. AlwaysOn Availability Groups were introduced as the latest HA/DR technology. With a GUI, Extended Events is ready to take over Profiler’s job. Columnstore indexes were introduced to make data warehouse storage and retrieval more efficient.
What’s next? We’re awaiting word on a release date for SQL Server 2014. This release is going to have the usual improvements, and then some. There’s a new method for cardinality estimation. A new engine is being introduced – In-Memory OLTP. Backups can be done directly to “the cloud” in Windows Azure. Clustered columnstore indexes will be updateable. There’s more – check out my recent webcast!
Let Me Ask You a Question
And let’s focus on your job. Are you still using the same laptop you did in 2005? If you’re on call, are you still using the same phone – or, help us all, the same pager – you had 9 years ago? Has your company’s main business application undergone change since then?
You have a newer laptop. You have a newer phone. The applications have been updated, or changed entirely. Why are you still on SQL Server 2005?
Yes, the process of testing a database upgrade is time-consuming. But it’s well worth it. The changes to the internals and the new features available are nothing but positive. And let’s not forget that in just over two years – Microsoft is currently stating April 12, 2016 – extended support for SQL Server 2005 ends.
Start making plans to upgrade today, and reap the benefits!
The Simple-Talk Tribal Award Winners are out, and we won in two categories – Blog of the Year for the Brent Ozar Team Blog, and me as the Person You’d Most Like to Have a Beer With.
So three things we need to cover here.
First, when we have a beer together, don’t worry about picking the beer. Just grab us a beer. I know I’m all foodie-focused, and yes I do love champagne, but I’m not a snob. I’ll drink everything from dark Guinness to fruity hefeweizens to Corona. If you see me with an empty bottle (what, you think I need a glass?), just bring over another one and let’s talk.
Second, you can get our blog posts via email too. We stopped promoting this a while back when we redesigned the site, but the subscription still exists. One of these days we’ll add it back in somewhere. We’ve still also got an RSS feed if you’re old-school like me. (I’ve been using Feedly as a replacement for Google Reader.)
Last, thank you. Thank you for being here, listening, commenting, and enjoying the journey with us. We still joke around about how this company started as a blog about turtles and pantyhose. We’ve come such a long way over the last twelve years, and I can’t wait to see what the future brings for us all. We’re going to keep right on blogging about how you can improve your database skills, your career, and the community as a whole. That’s what’s important to us, and thanks for making it important to you, too.
Before we pick CPUs or memory, let’s start by looking at SQL Server 2012′s licensing costs:
These are MSRP prices and do not include the extra costs for Software Assurance (maintenance) or any discounts for being a school, non-profit, or those photos you have of Bill Gates.
Physical, Standard Edition – you’ll notice that I went with 2 processors rather than 1. Technically, if you’re dying to save money, you could get away with installing SQL Server on a server with a single CPU, but if you want to save that much money, go virtual and avoid the hardware costs altogether. I could have also gone with 2 dual-core processors, but I only know of one dealer still selling those, and we’re talking about new servers here.
Physical, Enterprise Edition – that’s $6,874 per core, so it adds up fast.
Virtual, Standard Edition – here we’re using just 4 cores, the minimum license size Microsoft allows for a new server. You can build smaller ones (and I do), but as long as you’re licensing with Standard Edition, you’re paying per guest, and the minimum cost is $7,172.
Virtual, Enterprise Edition – if you really need Enterprise features in a virtual machine, you’ll most likely be running multiple SQL Server VMs. In that scenario, you’re best off licensing Enterprise Edition at the host level, and then you can run an unlimited number of SQL Server VMs on that host. (When I say “unlimited”, I’m using it the same way your cell phone company tells you that you have unlimited Internet.)
When I’m designing servers, I start with the licensing discussion because it helps everyone focus on the real cost of the server. Often folks want to nickel-and-dime their way into 16GB of RAM and a pair of SATA drives, but once licensing costs come into play, they realize architecture here is different. Our goal is to absolutely minimize the number of cores involved – ideally deploying virtual machines as often as we can – and then when we need to go physical, we get serious about the hardware, because this stuff ain’t cheap.
Now Let’s Talk Hardware
Once you’ve picked your licensing and physical vs virtual, let’s talk hardware. I’m using a major server vendor, but the exact brand isn’t important – you can get similar pricing from the hardware vendor of your choice, and this post isn’t about making brand decisions.
Notice how the numbers are displayed as total, licensing, and hardware? That’s how you need to present them to management. When a manager looks at those physical server numbers, the hardware is still clearly the cheapest part of this transaction. If they want to drive costs down, they can start by asking why this SQL Server needs to be physical – the real way to save money here is to drop down to the Virtual column.
Again, these are public sticker prices here based off the hardware vendor’s web site, and don’t include the extra costs of Windows, management software, or volume discounts. These prices also don’t include the cost of the drive space for the data and log files. Your choice between shared storage (SANs), local SSD, or local magnetic drives varies widely between shops, so I’m leaving that out. Let’s just focus on the basic hardware at first.
Physical, Standard Edition – this is a 2-CPU rack mount server with the fastest quad-core processors available right now, 96GB of the fastest memory, a pair of magnetic hard drives for Windows, and a pair of vendor-supplied-and-supported solid state drives for TempDB.
“BUT BRENT! YOU TOLD ME THIS WAS THE SMALLEST SERVER YOU’D DEPLOY, AND THAT SOUNDS LIKE AN INCREDIBLE SPEED MACHINE!!!1! WHAT ARE YOU THINKING? ARE YOU TROLLING ME AGAIN LIKE YOU DID WITH THE FRAGMENTATION POST?”
No, this is actually what I recommend to clients. You don’t waste dry cleaning money on your dad jeans, and you don’t run $14k worth of software on $3k worth of hardware. Besides, you want this thing to last for a few years, right? You don’t want to come running back to this machine again and again trying to fix performance problems that could be fixed with a basic injection of memory.
Physical, Enterprise Edition – the exact same 2-CPU box with the same processors, but upgraded to 384GB of memory and four 400GB SSDs for TempDB.
“BRENT ZOMG YOU MUST BE JOKING THAT’S CRAZY FAST, LIKE KEN BLOCK’S FORD FIESTA FAST. NOBODY NEEDS THAT MUCH MEMORY EVER, BILL GATES TOLD ME SO!!!!ONE”
Yes, my incredulous caps-loving friend, because we need to stay focused on the $55k worth of licensing costs, the dozens (hundreds? thousands?) of employees who rely on this server every day, and the capabilities in Enterprise Edition. Right now, the $55k of licensing you bought is being wasted on crappy hardware that’s more like Stason Lee’s Ford Fiesta.
Virtual, Standard Edition – since you’re licensing by the guest, you don’t have to buy an additional host for every new SQL Server you deploy. You can just mix these in with the rest of your virtualization farm and incur incremental costs. It’s certainly not free, but it’s nowhere near as expensive as a dedicated physical box.
Virtual, Enterprise Edition – since we’re going to license this at the host level, we generally only want to run SQL Server virtual machines on this host. Any other guests here are just wasting my very valuable CPU cycles and memory – at $55k for the licensing, I need to keep this focused just on SQL Server. Because of that, I’ve got a host equipped with the same power as my Physical Enterprise Edition spec – fast cores, high memory, and some local solid state to make a vSAN deployment easier for my VMware admins. (Disclaimer: when building out a real VMware host, I’d obviously tweak this – I’d end up with 10Gb Ethernet and/or FC HBAs, for example, but that depends on each shop’s network infrastructure.)
Yes, it’s another shock-and-awe post from Brent.
If you’re building physical boxes with 16GB of memory, and then you find yourself repeatedly going back to those boxes to do performance troubleshooting, the problem isn’t the app or the server or the memory.
The problem is you, and your old-school 16GB memory fixation.
You need to step back and look at the whole picture – licensing, business needs, RPO/RTO – and stop trying to save a few bucks in ways that hurt the server, the business, and your weekends.