Our first two concerns in this series were virtualization licensing and recoverability. It’s not enough to have answers for these questions today, though – we have to think about how long the server will be in place.
The server’s expiration date isn’t dictated by IT. If it was up to us, we’d never run an old, unsupported version of anything – but sometimes the business is quite content doing just that. So how do we plan for this from the start?
We need to get written answers from the business using a simple worksheet. In our Virtualization, Storage, and Hardware video class, students get an Excel spreadsheet to use when interviewing the business users about the project requirements. Here’s one of the tabs:
Start with the first question: how long does this SQL Server need to last? This can be measured in time (like 3 years) or in business growth (like until we quadruple the number of active users).
There are columns for several answers because database servers are rarely one-person projects – people have lots of different opinions, so everybody fills in their answers, and then we come to a group consensus in the last column.
The next several questions ask about how the business, application, and data will change during that time. We’re not looking for exact percentages, but just vague ideas. Are we going to double the number of end users per year? Are we going to start saving files in the database in the next version of our app?
This isn’t just about query performance – remember, we have to be able to do our backups, DBCCs, and restores fast enough to meet the business goals for RPO and RTO. After all, the business isn’t likely to say, “Since we’re growing fast, it’s okay if you lose more data.”
Who Really Answers These Questions?
Every time I teach this class in person, someone raises their hand and asks, “Do business people ever actually have answers for these questions?”
But it opens their eyes to your challenge as a database administrator. How are you supposed to build a perfect server to handle load that the business can’t define?
The less specific the business goals are, the more agile your server design needs to be.
That’s Where Virtualization Shines.
When you’re handed a wildly vague set of business requirements, it’s easy to build a virtual machine sized for today, and then add additional capacity to handle growth of data, users, and app complexity.
But here’s the kicker: you have to actually monitor the growth.
And just buying an off-the-shelf monitoring product ain’t gonna cut it.
Off-the-shelf monitoring products don’t tell you when your number of active users have doubled, when your incoming order rate has quadrupled, or when you’re keeping ten years of sales history instead of two. You have to work with the business to define a list of business metrics that you can monitor via database queries, and then add those metrics to your monitoring software.
For example, when I ran a sales system, I added monitoring queries for the number of orders per day, the number of active salespeople, and the number of orders we kept in the active database. I used my monitoring tool to create line graphs of those numbers every day, and then I sent that report to the stakeholders every month. I could point to the graph and say, “Sales volume has grown by forty percent, and we haven’t archived any data. Without additional investments in hardware, tuning, or staff training, you can expect that performance will degrade accordingly.”
Lines like that open wallets.
But You Gotta Understand Databases, Virtualization, and Business.
This is where I got so frustrated with guidance that says things like, “Set up one data file per virtual core.” If you’ve got a database that’s doubled in size over the past year, and you need to add four additional vCPUs, that guidance means you would have to:
- Add four LUNs on every SQL Server (primary, disaster recovery, reporting, QA, development – because you’re going to refresh those databases from production)
- Add four more identically sized data files (which instantly gives you hot spots on those empty files)
- Rebuild your clustered indexes to even out the load on the old and new data files
- Watch your transaction logs and backup sizes during this time because you’re going to have a huge change rate
But the one-file-per-core never includes guidance like that. They just expect you to “add files” and call it a day, and they ignore the technical and business side effects. Does the business really want to take a huge slowdown impact while all this happens? Especially if they’re in the midst of a growth spurt? Of course not.
Instead, you have to look at the bottleneck that the SQL Server is really facing, and identify the best way to fix that bottleneck with the least impact to the business.
That brings us to the last set of questions on that page of the planning spreadsheet – how will we handle growth when it happens?
If we suddenly acquire our biggest competitor and need to double the data and user load, or if we run a Super Bowl ad, how will we proactively keep the database fast? Are we going to be able to tune code or hire staff – or are we only able to throw hardware at the problem?
Another way to visualize this for management is my Manager’s Guide to Tuning Code – it shows management what your options are, and lets them choose your avenues.
Don’t get me wrong – I love throwing hardware at a problem when it’s the easiest, cheapest, fastest way to solve the pain. But remember that while virtualization makes it easier to throw hardware at a problem quickly, that may not be the easiest/cheapest/fastest solution. And even if you do it, you need to understand the real side effects of tricks like “one data file per volume per core” – because it’s nowhere near easy/cheap/fast in the real world.
You have a sneaking suspicion that your servers aren’t all paid for, and you need to get a rough idea of how SQL Server licensing works. You’ve never bought a box of SQL Server before, and you have no idea where to get started.
Microsoft Certified Master Brent Ozar will break it down into a few simple, easy-to-understand slides and show you the most popular licensing options. He’ll also explain 3 classic licensing mistakes and help you avoid ‘em in this 20-minute video.
To ask licensing questions after watching the video, join our weekly webcast for live Q&A. Not only do we answer your questions, we also give away a prize at 12:25 PM EST – don’t miss it!
Have questions? Feel free to leave a comment so we can discuss it on Tuesday!
What you need to do: log into SQLpass.org and change your password. If you use the same password anywhere else, change it on all of those sites immediately as well.
Why you need to do it: anytime you ever logged into SQLpass.org or SQLsaturday.com, or updated your password, or created an account, your password (along with everything else) went unencrypted between your computer and PASS’s web servers. Anyone sniffing network packets along the way saw your username, email, password, etc in clear text. (Think about what a security gold mine this would have been for someone sniffing WiFi at a SQLSaturday or the PASS Summit.) There’s a nice side benefit for updating your account – you also become eligible to vote in the current PASS elections.
Who you need to thank: the vulnerability was discovered by George Stocker on Friday, and PASS HQ finished the fixes & testing on Saturday. That’s a fantastic turnaround time – kudos to PASS for reacting so fast!
Who you should blame: yourself, for not noticing for years that you were putting login information into a web site that wasn’t using https. What kind of data professional are you, anyway? You’re probably even using that same password on multiple web sites, or heaven forbid, your company email. Get it together and use 1Password or LastPass.
Who you should not blame: the current PASS Board of Directors because this has likely been in place ever since PASS set up their current web site, and the current management inherited this little surprise. (You know how it goes – it’s like your ancient SQL Server 2000 in the corner where everybody knows the SA password.)
What’s still left to do: PASS needs to clearly, concisely communicate the severity of this issue to members as well – I really wish they’d set all passwords in the database to null, and force everybody to go through the password-reset process. The SSL setup still needs some work, as shown in the SSL tests, but considering the whole thing was done in 24 hours, it’s one heck of a good first step. (SQLsaturday.com also fails that test.)
Your first concern is licensing, and once you’ve got that under control, it’s time to talk about backups, restores, and uptime.
The business begins by defining the RPO and RTO requirements for high availability, disaster recovery, and “oops” deletions. Use our High Availability Planning Worksheet and fill in the “Current” column on page 1:
You need to give the business a rough idea of the current state – how much data you’ll lose under each of those scenarios, and how long the SQL Server will be down. Be honest – don’t overstate your capabilities, because that just means you’ll get less budget money to build the system the business wants.
Then let the business users fill in the “Biz Goal” column for each scenario. Of course, by default, everyone wants to pick zero data loss and zero downtime, but that’s where page 2 of the worksheet comes in:
Give both page 1 & 2 of the worksheet to the business users and let them pick the right availability requirements for their budget. Technical managers will want to leap to specific methods (“we have to use VMware replication”) but keep steering the conversation back to the big picture – what does the database need?
The costs aren’t meant to be exact estimates, just rough ballpark numbers of the hardware, software, installation, and ongoing maintenance costs for a couple/few years. To learn more about the technologies in each box, check out our High Availability and Disaster Recovery Resources page.
If your business checks different boxes for each of the three scenarios (HA, DR, and OOPS), then you’ll probably end up with a mix of different SQL Server technologies. For example, the OOPS scenario is usually handled by transaction log backups and a separate standby restore server so that you can pull out just the data you need, but that isn’t going to help you for high availability if you need 1-minute automatic failover with zero data loss.
After the business checks boxes for high availability, disaster recovery, and “oops” deletions, it’s time to pick the right recoverability option. There are virtualization-friendly options in every box, but it’s important to note what isn’t in these boxes.
VMware, Hyper-V, and Xen Alone Aren’t High Availability.
Virtualization admins often think, “If something goes wrong with hardware, the virtual server will just start up on another host.” VMware, Hyper-V, and Xen all do a great job of recovering from these kinds of failures, but those aren’t the only kinds of failures we need to avoid. Note the list of failure types in our High Availability scenario.
Sure, hypervisors protect you fairly well from Windows crashes, RAID controller failures, bad memory chips, or somebody unplugging the wrong box.
But what about patching and OS-drive-full type problems?
I’ve heard admins say, “No problem – before we do any patching, we’ll shut SQL Server down, take a snapshot of the VM, and then start the patch. If anything goes wrong, we’ll just roll back to the snapshot. No data loss, not much downtime.”
Oh, I wish. Let me tell you a story.
One of my clients was preparing a new SQL Server for production. As part of their prep, they needed to apply SQL updates to it, so they started the installation process, and …
A few minutes later the phone calls started pouring in.
Because they were patching the wrong box. They were patching the production SQL Server VM, not the new VM. To make matters worse, they ran into the SSISDB bug, and their production server was down for hours while they figured it out.
How Recoverability Influences Virtualization Design
When you’re designing solutions for HA, DR, and OOPS, read the scenarios described in this simple worksheet. Expect that sooner or later, at a time you can’t predict or control, every one of these is going to happen to you. (Well, maybe not zombies in the data center.) Your technical solution is driven by the business’s requirements for RPO/RTO in each scenario. Understand what virtualization alone can give you, and when you’re going to have to add SQL-Server-level solutions.
Your design then needs to take into account one more challenge: capacity. How many databases will you have, how large will they be, and how fast will the data change? For example, a solution involving log shipping, a 1TB database, a 10% change rate per day due to batch load jobs, and a 1Gb Ethernet pipe probably isn’t going to be a 1-minute-data-loss solution.
It all boils down to one simple question:
can you back up, DBCC, and restore the databases fast enough?
Not only do you have to answer the question for today, but for the life expectancy of the server as well. In my next virtualization post, I’ll explore capacity planning as it relates to RPO/RTO, and then we’ll be able to put the whole picture together of what our VM environment will look like.
Want to see our sessions in Seattle? Add all of our calendar events to our schedule:
Or if you’d rather add individual events:
Every single thing you do with a virtual SQL Server starts with one very important concern: how are you going to license it?
The 3 Common Ways to License Virtual SQL Servers
Developer Edition, by the guest – Developer Edition has all the power of Enterprise Edition, but it’s way cheaper (like in the $50/server range) and you’re not allowed to use it for production purposes. Because the licensing is so cheap, just mix these VMs in with the rest of your guests on a shared pool of hosts. Developer Edition licensing is handled differently than other SQL Server editions, typically bundled with your developers’ MSDN subscriptions.
Enterprise Edition, by the host – When you have 5 or more guests, it usually makes sense to buy a dedicated pair of host servers in an active/passive configuration. For example:
- Dell R720 Primary – 2 sockets, 4 cores each, 768GB memory – ~$25k USD
- Dell R720 Secondary – same hardware config – ~$25k USD
- 8 physical cores of SQL Server Enterprise Edition licensing and Software Assurance – ~$55k USD (you only have to pay licensing on the physical cores regardless of whether you enable hyperthreading)
- Hypervisor, Windows, management software licensing, shared storage – left as an exercise for the reader
You can configure your hypervisor (VMware, Hyper-V, Xen) to use one of the hosts as the primary, and when it fails, automatically boot up the guests on the secondary R720. This way, you only have to license one of the two hosts, and you can run an unlimited* number of SQL Servers on this host. (* – Not really.) Even better, even though they may be small VMs, you can still use Enterprise Edition on these guests and get access to cool EE-only features like online index rebuilds.
As your SQL Server needs grow, you can license the Secondary and balance the VM load across both, and even start to add additional licensed hosts. The key, though, is to avoid running any non-SQL-Server loads here because these are your utmost expensive host cores.
Standard Edition, by the guest – When you have 4 or fewer SQL Server instances, and they don’t need enough hardware horsepower to merit a dedicated host pool, then you’re usually best off licensing each guest with Standard Edition. This way, you can intermingle them on the same hardware that the rest of your virtual guests use.
At around $2k USD per core, it’s roughly 1/4 the price of Enterprise Edition, but you have to keep an eye on the number of virtual cores you’re using. You may read training material that says “just add vCPUs when you have performance pressure,” but this is expensive advice when you’re licensing each guest by the core. As you approach 16 vCPUs of Standard Edition licensing, you need to build a plan to migrate over to the host-based Enterprise Edition licensing approach. 28 vCPUs of Standard costs about the same as 8 vCPUs of Enterprise, but only the latter lets you run an unlimited number of guests, use over 64GB of memory on SQL 2012 and prior, do online index rebuilds, etc.
How Licensing Affects Your Administration
If you need SSRS, SSIS, or SSAS, and you’re licensing hosts with Enterprise Edition, you simply deploy these other services on separate virtual servers. You never make these other services fight with the core SQL Server engine for the same memory. However, if you’re on Standard, you saved money on licensing, but you spend extra performance troubleshooting time. You’ll need to install these services on the same VM as the engine to keep costs down, but you’ll need to understand how to triage performance.
When you design a high availability solution, the edition determines your choices as well. If you’re on Standard, and you need automatic failover for OS & SQL problems, you’ve backed yourself into a corner of either failover clustered instances or synchronous database mirroring. These solutions add complexity that shops without a DBA often can’t afford, so they end up going without OS/SQL protection and sticking with the simpler hypervisor-based failovers. On the other hand, once you’ve decided to license the hosts with Enterprise Edition, you can take advantage of AlwaysOn Availability Groups and asynchronous database mirroring without extra licensing costs – even for your smallest VMs.
Knowing your licensing means faster decision-making on architecture, and that’s why you want to start your virtualization projects here. Licensing is usually the most expensive and most restrictive part of the project – get it right first.
To learn more about licensing SQL Server, check out the 2014 Licensing Guide PDF.
Until now, SQL Server training usually meant registering for a course, showing up, hearing a lecture from a podium (often from someone who’s only done demos on their laptop, not production systems), working through a few workshops, and then going home with a bunch of ideas. It was like trying to ride a rollercoaster by jumping on as it came speeding toward the platform, having a great time, and then jumping off at high speed again. No graceful transitions, just a shocking experience.
We wanted to do better.
We wanted to ease students into the experience, get them more involved with the material ahead of time, connect the dots between the demos and their real environment, and empower them to help each other with their studies. We wanted to mix up the training so that it was less about theory, and more about their real world environment. Finally, as students left the classroom, we wanted to ease them back into the real world in a way that empowered them to succeed.
We couldn’t accomplish all of those goals immediately – doing this kind of practical, realistic, and comprehensive training is really hard work, so we’ve been gradually experimenting with more and more tactics this year. Here’s what we’ve come up with so far.
What It’s Like to Attend Our Training
We start mixing things up as soon as someone registers for one of our courses. For example, our How to Be a Senior DBA students were assigned a series of private pre-requisite training videos as soon as they registered for the course. They get started learning about RPO, RTO, and SQL Server’s options to meet their availability goals. (Behind the scenes, it took us years of work to get our e-commerce setup and training videos to this point, but the integration is definitely paying off now.)
On the first day of the in-person class, we started by discussing a couple of real-life case studies – then turned their own environment into a real case study. Each attendee used worksheets to describe their current production environment’s requirements to the person sitting next to them, and that person acted as a consultant. The team worked together to sketch out the right long-term solution for a company’s needs, and then they switched roles. Each student was able to sit on both sides of the issue – describing a problem, and building a solution for someone else.
In just one hour, this combination of pre-learning plus collaborative exercises accomplished three goals: it reinforced the student’s knowledge, helped them get a second opinion about their systems, and built a relationship with other people in the room. The relationship-building paid off throughout the course as the students carried on lively discussions at every break, over every lunch, and hanging around the flipcharts to talk shop. I wish I could say this was part of our giant master plan, but it was just a happy discovery that we made back in San Diego, and we’ve been using it ever since.
Another discovery this year was that students learned better when we assigned them script homework before the course. For example, before the SQL Server Performance Troubleshooting class, students were assigned scripts to run on their production servers and copy the results into Excel.
In Chicago, one of the students came up to me and said:
“I’m so glad you gave us the wait stats homework before class. I’d run the queries on several of my slow production servers, and I didn’t understand the results at the time, but now it all makes perfect sense. I understand why the data warehouse has PAGEIOLATCH and the web app has CXPACKET, and I know exactly what to do next.”
As we’d been covering wait stats, she’d been comparing her results with what we were discussing, and she was able to make a simple set of steps for her next maintenance window – all while sitting in class. Some of our students even used our lab scripts on their live production servers, fixing issues as we discussed them. (That might be taking the concept a little too far!)
Our Next Round of Training Experiments: Seattle
In our 2-day Seattle class, Make SQL Server Apps Faster, we’re running a couple more experiments.
We’re starting off with a day of lectures on performance tuning. We cover how to think like SQL Server, using wait stats to know why SQL Server is slow, tuning with the plan cache, beating anti-patterns, building the best indexes, and a challenge for attendees.
We weren’t content with that. People keep asking for more hands on sessions like “Watch Brent Tune Queries.” You really want to see our thought processes at work. We’ve done just that. Attendees think through the problem and sketch out their answers. Then they spend the day watching us solve it – they watch me doing query tuning, Kendra doing index tuning, and Jeremiah working through execution plans. At the end of the day, we judge their answers together.
We’re also offering a package deal – some attendees have said they want to watch recordings of the sessions later to reinforce what they’ve learned. (They already get the demo scripts, but sometimes it’s better to watch us doing it.) For an extra $200, the attendees get our 7-hour Developer’s Guide to SQL Server Performance videos that cover about half of what we’re teaching in-person, and we’ve extended similar deals to our 2014 training class attendees too.
For 2015, our new week-long courses represent another new experiment. This year’s attendees kept telling us, “As long as I’m out of the office, I might as well be out for the whole week.” Sounds good! So we’re taking more time to dive deeper into more topics.
We’re excited about how we’re doing SQL Server training differently than anybody else on the market, and we can’t wait to share it with you.
Got a high performance SQL Server 2012 instance? Check out the brand new KB article for recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 used with high-performance workloads.
Make sure you expand the following plus sign – this is where all the good stuff is stored:
After expanding it, you’ll get a huge list of categorized advice for trace flags, cumulative updates, MAXDOP settings, and much more:
I haven’t read through the details on this yet, but I’d note that this advice is focused on high performance workloads. Don’t go enabling trace flags without understanding what they do and their side effects.
I continue to be impressed by the documentation Microsoft is putting out. Books Online keeps getting better and better, and this is the coolest KB article I’ve seen in a long time. Way to go, Microsoft!
You store data in SQL Server, but you don’t have enough work to keep a full-time DBA busy.
In just half an hour, you’ll learn the basics of performance troubleshooting and index tuning from me – a recovering developer myself. I’ll show you the basic care and feeding of a Microsoft SQL Server instance and give you scripts to keep you out of trouble:
In this session that I gave at the 24 Hours of PASS 2014, I cover these steps:
1. Start every new table with a clustered index.
A clustered index tells SQL Server, “Here’s how I want you to sort the data.” Without that definition, SQL Server just sloppily throws your data anywhere that it can find space. While that might be awesome for insert speeds, it’s not so good as a universal data structure when we need to make performance tuning as easy as possible.
Generally, your clustered index needs to be on fields that are:
As you learn more about indexes, it’s tempting to micro-optimize them by saying, “Well, I think my end users are going to query this data on a combination of date and warehouse number.” Don’t over-think that before the users start using the app. Index designs are easy to change later.
There are situations where heaps – tables without clustered indexes – can perform faster. Thing is, they have some serious gotchas as Kendra Little explains in this 30-minute video. Heaps also miss out on the magic of SQL Server’s automatically suggested indexes, and that’s especially important for us in the next step.
2. Check your indexes weekly with sp_BlitzIndex®.
As it runs your beautifully crafted queries, SQL Server is constantly gathering index diagnostics. It keeps track of what indexes would have been the most helpful, how many times they would have been used, and how to create those indexes. You can query those diagnostic tables yourself, but frankly, it’s painful.
That’s where the free sp_BlitzIndex comes in. Download it, install it on your production server, and then run it in your database. It gives a funny sanity check on which indexes you could probably drop, and which ones you should add.
Run it, and then cover your eyes. It’s going to produce all kinds of heinous warnings about the awful mess that the last guy stuck you with. I only want you to focus on two warnings:
Warning: Index Hoarding – these are nonclustered indexes that are in place now, but nobody’s using them. SQL Server has decided these indexes just aren’t the most efficient way to get your data. Disable these rather than dropping them, because that way if they’re needed again, you can just enable them again as explained in that link.
(Now would be a good time to mention that if you’ve been bossing SQL Server around by specifying index hints in your queries, you’re gonna have a bad time. Your queries will fail.)
Warning: Missing Indexes – SQL Server wants a copy of your table stored in a different order to make queries go faster. Careful adding these willy-nilly – aim for 5 or less nonclustered indexes per table, and each index should have 5 or less fields on it. Avoid indexing XML, VARCHAR(MAX), NVARCHAR(MAX), or other big data types because the index will be huuuuge.
If you’re on SQL Server Standard Edition, creating indexes can lock your table while you work, so do the actual adding in development first. Time how long it takes, and depending on your dev hardware’s speed and database size, you’ll have a rough idea of what it will look like in production. Then do the doin’ during a maintenance window or after hours. If you’re lucky enough to use SQL Server Enterprise Edition, you can use the WITH (ONLINE = ON) parameter while creating indexes to have a lower impact on your end users.
And remember that first step where we said to start with a clustered index? If you’re thinking SQL Server will recommend the right clustered index for you, think again.
Repeat this process every Friday. See, SQL Server empties out the contents of this diagnostic data whenever SQL Server restarts, and then it constantly keeps piling the data back up. On Friday, even if your server was restarted over the weekend, you’ve got some good diagnostic data to check because users have been hitting the app all week. (This is why you can’t run it in your dev/staging environment and get the same good recommendations.) Within a month or so, you won’t believe the difference in performance.
3. Build the Suckerboard weekly with sp_BlitzCache®.
As it’s executing queries, SQL Server tracks which queries get run most often, and which ones use the most server resources. It’s like capturing a trace of your server, but even better because SQL Server is already doing this for you. You can query the dynamic management views to pull the data out, but ain’t nobody got time for that.
That’s where the free sp_BlitzCache comes in. Download it, install it on your production SQL Server, and then run it with these parameters:
EXEC sp_BlitzCache @top = 10, @sort_order = ‘duration’
This will give you the top 10 queries that have run the longest in total. For example, if a query runs for 10 seconds, and it’s been called 1,000 times, then it’ll have 10,000 total seconds of duration, so it will rank higher than a query that was only ran once and ran for 500 seconds.
This is the opposite of the leaderboard – this is the Suckerboard. It’s the Hall of Shame, the queries that we’re not too proud of. The good news is that it’s usually really easy to fix these once you know what they are. On the far right of sp_BlitzCache’s output, you can click on the query plan to see the query’s execution plan, and you’ll often find things like missing index warnings or zombies.
Repeat the process with a few other parameters to see the highest CPU users, the ones that read the most data, and the ones that ran most frequently:
EXEC sp_BlitzCache @top = 10, @sort_order = ‘CPU’
EXEC sp_BlitzCache @top = 10, @sort_order = ‘reads’
EXEC sp_BlitzCache @top = 10, @sort_order = ‘execution’
Repeat this every Friday and email the list of queries to your team. This has an amazing effect – people see their queries on the Suckerboard, and they immediately wanna work on those queries to get them off the Hall of Shame. It becomes a competition to keep off the Suckerboard.
After a month or so, you’ll be very comfortable with the list of queries. You’ll know them right away at a glance, and you’ll know why they show up – like what kind of work they’re doing. This makes you extremely well-equipped to deal with the next step.
4. When slowness strikes, use sp_WhoIsActive and sp_AskBrent®.
When someone comes running in screaming that the SQL Server is slow, run these two stored procedures:
sp_WhoIsActive lists all of the queries that are running right now, ordered by longest-running to newest.
sp_AskBrent checks a bunch of common potential issues on your server and warns you if a data file is growing, a transaction is stuck rolling back, a backup is running, and so on.
5. When you need to learn more, here’s what to do.
- Explaining Clustered vs Nonclustered Indexes – Jes Schultz Borland simplifies these in a blog post with examples.
- Filtered Index Limitations – these are indexes with a WHERE clause, thereby taking up less space, but…yeah.
- How to Decide if You Should Use Table Partitioning – this advanced indexing technique has a tempting call as you start to scale. Kendra Little explains it.
- More index resources
Plan cache and execution plan resources:
- How to Use sp_BlitzCache® – Jeremiah explains how to query your plan cache and find the most resource-intensive queries.
- How to Get Your Query’s Execution Plan – a 15-minute video that explains a few different methods.
- Parameter Sniffing – if your query suddenly runs slow out of nowhere without any changes, this might be your problem.
- More execution plan resources
And if you’re coming to PASS, we’re holding a performance tuning class on Monday & Tuesday called Make SQL Server Apps Go Faster. If you learned something in this article & video, then you’ll learn a LOT in the class. Read more about it now.
DBAs reading this: stop freaking out.
I know, I’m simplifying a lot of stuff in here. When you read the sentence, “Index designs are easy to change later,” you instantly pop a vein in your forehead because you remember that time you had to change a clustered index on a 1TB data warehouse.
Take a step back and put yourself in the frame of mind of a developer who’s alone in a dark room with an application, a SQL Server, no DBA, no budget, and no time to get everything perfect. I’m trying to give them the tools to get good-enough performance while spending less than 2 hours a week worrying about their database.
Sure, there’s all kinds of interesting scenarios and tricks you’d like me to explain on this page – but remember, the time they spend reading this page and learning techniques counts against their 2 hours of spare time per week. Focus on the things that will truly make a difference in performance, and ease off the academic “well actually” stuff.
You love Q&A sites like StackOverflow.com and DBA.StackExchange.com, but sometimes it’s hard to find interesting questions that need to be answered. So many people just sit around hitting refresh, knocking out the new incoming questions as soon as they come in. What’s a database person to do?
Use the power of the SQL.
Data.StackExchange.com lets you run real T-SQL queries against a recently restored copy of the StackExchange databases. Here’s my super-secret 3-step process to find questions that I have a shot at answering.
Step 1. Find out how old the restored database is.
Run this query to get a list of site databases and the newest post date in each one:
I take a glance at StackExchange.Dba and StackOverflow to make sure I’m dealing with a relatively recent database restore. These database restores are done weekly, but you know how it goes – sometimes scheduled jobs fail.
Step 2. Find questions everybody’s talking about.
Run this query to find questions with many comments, but no accepted answer. Note that for this one, you do have to pick the site you want to focus on – this doesn’t run across all databases.
If you click on the Post Link, you’re taken to the live question. If the database restore was a few days ago, keep in mind that the live question may have changed or been answered by now.
The other columns help me see at a glance if this is a question I’m interested in. In the above screenshot, the third question, “New database server hardware,” has 2 answers, but none of them have been upvoted, and there hasn’t been any comments since 2014-05-13. It’s old, and the questioner has probably moved on, but you can revive the question by posting a really good answer. You can also take your time, knowing nobody else is really active on it right now. These are great opportunities to post a really in-depth answer for other folks to find as they search the web later.
Which brings me to my next favorite query:
Step 3. Find questions that people keep looking at.
Run this query to find questions with many views, but no accepted answer. This one is filtered by a specific tag because the SQL Server ones don’t usually show up in the top views. Use tags from the DBA.se tag list or the StackOverflow tag list.
Obscure error numbers are usually going to pop up here because there’s not much public information about ‘em. Now’s your chance to write an answer explaining how to troubleshoot that error number, and presto, you’re the next Jon Skeet.