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.

When is a “Full Scan” not a Full Scan?

The performance counter “Full scans/sec” sounds pretty scary. If you see spikes in this counter, you’re likely to think that your queries are madly scanning all the data in your tables– there are no stylish, well performant seeks, just drunken queries spewing IO everywhere.

If you look up the counter in SQL Server Books Online, it seems to confirm that. Full Scans/sec is defined as the “Number of unrestricted full scans per second. These can be either base-table or full-index scans.” It’s not just a FULL scan, it’s “unrestricted.” That’s sounds really bad, right?

Not necessarily.

Let’s take a look. You can warm up your test SQL Server instance and restore your favorite sample database to play along with the sample queries below using the AdventureWorks2012 database and perfmon.

Test 1: A full scan

Here’s our first test query. This query technically does an index scan, but it’s a very efficient partial scan– it uses just 3 logical reads to hop to the end of the index and grab the last value.

500 of these finish in less than one second. Here’s what the plan looks like. (If you look deep into the properties of the scan operator, you’ll find that it says that it’s a special “Backwards” scan for speed.)

In perfmon, I can see that this query did the following:

• 500 Full Scans/sec (SQL Server:Access Methods)
• 1,908 Page lookups/sec (SQL Server: Buffer Manager – this is logical reads)
• 0 Page reads/sec (SQL Server: Buffer Manager — this is physical reads and the data was already in memory)

Hmm, so what we’ve seen here is that we can make the full scans/sec counter spike with a scan that isn’t really “unrestricted” or “full” in the way that we might think.

Full scan does NOT really mean that it read the entire index! (I’m not the first to discover this, of course. Michael J Swart gives a nice example of a TOP query doing something similar in this post on different types of scans.)

Test 2: a “worse” full scan

Let’s make our test query a bit nastier. This new version of the query is not such a great citizen — it reads 3,820 pages on each run (as opposed to 3 reads from the query before):

This is also a scan but it does more IO. 500 of these take five seconds or so– much longer. Here’s the plan:

When we run this, we see that:

• Full scans/sec only spikes up to a max value of 97 this time– it’s much LOWER, even though we hit the SQL Server with a nastier query. We just ran this from a single SQL Server session, so it didn’t spike up as much and took longer– but it certainly isn’t better than the first query.
• Page lookups/sec spikes up to a max value of 362,161. Each run of the query is doing more logical IO– and the fact that this query does more work shows up much more clearly here.
• Page reads/sec just has a slight bump and at one point 8 reads/sec are registered. The data was already in memory, so this wasn’t really impacted at all.

The “worse” query didn’t spike the full scans/sec counter, but it did more IO. If we were designing monitoring around the full scans/sec counter then we’d probably just throw an alert on the incident that actually had less impact.

Pulling it all together

Monitoring is a tricky business. Performance counters tell you a lot about SQL Server– but if you monitor on individual thresholds on many performance counters, you’ll end up with false alarms and more questions than answers. The counters shown in this post can be useful for baselining, trending, and interpreting in a larger context of information, but by themselves they don’t tell you a full story.

If you’re getting frustrated with perf counters and want to learn what’s really dragging down your SQL Server, check out our free webcasts, our free scripts, or our performance tuning training videos.

Update – October 2015 – You can download a Torrent of a database (rather than a data dump) now.

Most of my demos involve 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 Hard Way to Query StackOverflow

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 the Stack Overflow Data Dump Importer.

The SODDI user interface expects two things:

1. The XML files have 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.
2. All of the XML files for the site have to be present in that folder: Badges, Comments, PostHistory, PostLinks, Posts, Tags, Users, and Votes.

When you run SODDI.exe without parameters, this GUI pops up (assuming that you named your Stack Overflow demo folder 092013 SO):

Source is the folder where you saved the data dumps. It expects to see subfolders in there for 092013 SO.

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.

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:

Data Explorer is really easy to use – check out the one-page documentation. It’s even open source, so to learn more about how it works, visit StackExchange.DataExplorer on Github.

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.)

Here’s a SQL Server Management Studio trick that comes in super handy when you’ve got long pieces of code. I almost never see anyone use this, and I think the reason is that few people know about it.

When I’m working on a stored procedure, sometimes I want to add in a new variable or a temp table. I declare ’em all at the top of the procedure, but I’m working much farther down in the proc than that.

I don’t have to lose my place. I can split the window using a cool, but hard to see feature known as the “splitter bar”.

Drag the splitter bar down. I now have two synchronized views into my procedure.

Voila, I can add my temp table in the right area without losing my place.

This feature isn’t specific to SQL Server Management Studio– it exists in lots of other products. (Including Word!) But the feature is hard to spot and most folks just never seem to learn about it. (I had no idea it was there myself until Jeremiah showed it to me a while back, and now I’m addicted to it.)

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.

The Situation

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:

Possible Solutions

Our users want to be able to define a custom sort order on this query. We could solve this in a few ways:

1. Writing several stored procedures
2. Use dynamic SQL to build an ORDER BY

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 TOP operator.

The First Attempt

My first attempt at rocket science looked like this:

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 OFFSET and 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.

Moving the CASE to the ORDER BY

My next attempt moved the custom sort down to the ORDER BY clause:

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 ROW_NUMBER()

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:

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 FETCH and OFFSET.

Bonus Round: Back to ROW_NUMBER

While using my brother as a rubber duck, he suggested one last permutation – combine the ORDER BY technique with the ROW_NUMBER() technique:

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 theCASE, we’re able to eliminate multiple sorts and gain the benefit of using ROW_NUMBER().

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!

Moving On

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!

SQL Server has amazing instrumentation. Dynamic management views and functions give you great insight into what’s happening in your SQL Server and what’s slowing it down. Interpreting these views and functions takes a lot of time, but with practice and skill you can use them to become great at performance tuning.

But nothing’s perfect. Some of these DMVs and DMFs have bugs or column names that can be misleading. If you take them at face value, you can end up with egg all over your face.

sys.dm_os_sys_info – hyperthread_ratio column

This DMV is great for quickly checking the last startup time of your SQL instance and finding out if it’s virtualized. But don’t trust sys.dm_os_sys_info to tell you whether or not hyperthreading is enabled on your processors.

The “hyperthread_ratio” column is simply an indication that you have multiple cores per processor and does NOT tell you whether or not they are using hyperthreading.

sys.dm_index_usage_stats – the whole concept of “usage”

This DMV is trying to tell you the truth, but almost nobody understands what it means. This DMV tells you how many times a query used an index in its execution plan– but it doesn’t tell you exactly how many times the index was accessed. Only the number of operators referencing it when the plan was run.

A simple example: I run a query that does a nested loop lookup to retrieve a value from the index PK_Address_AddressID. The query runs once, and in that one run it executes the lookup 30 times. This is counted as one “use” of PK_Address_AddressID, even though 30 lookups were executed.

If a single index appears more than once in an execution plan (due to a subquery, union, etc), then it’ll be counted once for each operator in the plan.

TLDR; index_usage_stats does NOT show you the number of “reads” or “writes” an index is really serving up.

sys.dm_exec_trigger_stats – execution_count and last_execution_time columns

Which triggers on your server are running the most and using the most resources? The sys.dm_exec_trigger_stats DMV seems like it’s perfect to answer that question, but beware.

There’s a bug where execution_count increments due to completely unrelated actions that wasn’t fixed until SQL Server 2012. (I’ve seen the info from this DMV be wonky on 2008R2, and I’ve validated I can’t reproduce this on SQL Server 2014, but I haven’t personally tested on 2012.)

Side note: isn’t it cool that Microsoft makes the Connect bug data public? I’m not sure that I ever would have figured out what contributes to inflating the execution counts on my own!

sys.sysindexes – Rowmodctr

It can sometimes be useful to estimate how many changes have occurred since statistics were last updated on a column or index. This gives you a quick way to guestimate if those stats are maybe not-so-fresh.

Good news on this one– the column isn’t perfectly true, but Books Online has a great rundown of its issues. It lets you know that SQL Server doesn’t really use this counter for its own purposes, and that it’s only roughly accurate at best.

And then it lets you know that SQL Server doesn’t expose column modification counters, so this maybe-pretty-close-guestimate counter is still perhaps better than nothing.

sys.dm_os_performance_counters

I’m a huge fan of SQL Server’s performance counters, wheter you’re querying them via sys.dm_os_performance_counters or looking at them with perfmon.exe.

But there’s a whole host of misleading and just plain outdated counters that will lead you astray. Learn which perf counters to beware in Jeremiah’s post on perfmon.

SQL Server’s Instrumentation is Great

And honestly, so is SQL Server’s documentation. The challenge is that there’s a massive amount to document– and Books Online can’t cover all the nuances of everything. Keep using those DMVs– just keep an eye out for the gotchas.

Answer: Yep, sometimes it can.

I used to think that higher degrees of parallelism followed a law of diminishing returns– you could add more threads, but the benefits would taper off. But it’s a bit more complicated than that. Microsoft’s recommendation to be careful when setting maxdop to values over 8 is a warning worth heeding.

Lowering maxdop can cut CPU without sacrificing duration

We tend to think that reducing the number of threads available MUST make a query slower– but that we sometimes need to do it so other queries can run concurrently.

It’s great to support concurrency, but lower DOP doesn’t necessarily mean a longer runtime, even if it does lower CPU consumption. Here’s a simple example of an OLTP query run with two different DOP settings. CPU time and duration were measured using “SET STATISTICS TIME ON”. For both runs all data was in memory (no physical reads or read aheads).

Duration in both cases was around 700 milliseconds. The lower DOP didn’t make execution time longer. It changed the way SQL Server ran the query, but it cut overall CPU usage while keeping the runtime about the same. This was great in this case, because the query in question needed to run frequently on an OLTP system.

Higher maxdop can slow down large queries

In the case of large queries, higher DOP can slow down query execution. The impacts here vary a lot by processor, memory type and amount, and whether or not your SQL Server is virtualized— not to mention based on the execution plan of the query in question.

Here’s a totally different query tested on totally different hardware. In this case the query reads in hundreds of gigabytes of data, but as in the previous example all tests were run against a “warm” cache and were not doing physical reads or read ahead reads. The server used in this test had 8 physical cores per NUMA node.

Changing maxdop changes query plans

When you tune maxdop, it’s worth watching the execution plans for the top queries on your server. I’ve seen changing the amount of threads available for a query make the optimizer change its mind about how to run a query immediately– and been able to reproduce it switching the plan back as soon as I hint a different DOP.

Since impacts can be complicated I recommend changing maxdop rarely and monitoring your plan cache and wait stats for at least a week or two after the change.

Parallelism is a good thing

Don’t get me wrong– I ain’t saying parallelism is bad for SQL Server. Multiple threads can make many queries faster!

One of the gotchas with SQL Server is that the default value of “0” for the “Max Degree of Parallelism” setting can lead to poor performance– because it lets SQL Server use all your processors (unless you’ve got more than 64). Fewer threads can not only reduce CPU usage, but may also be faster.

So check your maxdop settings, and keep reading to learn more about CXPACKET waits.

When you vote for us in Simple Talk’s new Tribal Awards, you’re voting for yourself.

Best Free Script – sp_Blitz® – Sure, I started this all by myself a few years ago, but it’s grown into something huge. Scroll down through the change log and get a load of the dozens of contributors who have helped make this tool amazing. I even hear from consultants who ask me, “Are you sure it’s okay to use this thing in my job?” Of course! We’re all in this together, and we’re all trying to make SQL Server easier for everybody.

Blog of the Year – www.BrentOzar.com – To me, the comments are the most fun thing about a blog. Since the first post 11 years ago, we’ve had 16,650 comments (and no, that doesn’t include the spam). This place is fun because you guys take part, and even when you say crazy stuff, at least I get to use my witty retorts.

Person You’d Most Like to Have a Beer With – Brent Ozar – Let’s be honest – you guys only nominated me because you know that whenever I’m drinking beer, I’m also ordering tater tots. It’s not that you want to have a beer with me – you want to eat my tater tots. You’re just being greedy here.

So go vote, and we all win.