I’m teaching our SQL Server Performance Tuning class out in Portland this week, so we’re running all performance posts this week. You can learn more about my upcoming classes here.
When a query is sometimes fast and sometimes slow, for the same input parameters, and you swear nothing else in the environment is changing, that’s often a case of parameter sniffing.
After SQL Server starts up and you run a stored procedure, SQL Server builds an execution plan for that proc based on the first set of parameters that are passed in.
Say we’ve got a stored procedure that runs a report based on country sales data:
- EXEC rpt_Sales @Country = ‘China’ — SQL Server builds an execution plan optimized for big countries with big amounts of sales, and it runs in about 750 milliseconds.
- EXEC rpt_Sales @Country = ‘Monaco’ — it reuses China’s cached execution plan for big data. It’s not great for small countries, but who cares – it’s a small amount of data, so it still runs in 500 milliseconds.
Now we restart the SQL Server, and someone queries for Monaco first:
- EXEC rpt_Sales @Country = ‘Monaco’ — SQL Server builds a plan optimized for tiny countries with tiny amounts of data, and it runs in just 50 milliseconds – way better than when Monaco reused China’s plan. Yay!
- EXEC rpt_Sales @Country = ‘China’ — it reuses Monaco’s cached plan for tiny data. It takes 30 seconds, and suddenly our server starts falling over because several people are running queries at a time.
SQL Server sniffs the first set of parameters that get used. Even though nothing in our environment has changed, suddenly the SQL Server runs much more slowly, and query times for China are horrific.
How to Fix Parameter Sniffing Temporarily
Parameter sniffing fixes are based on your career progression with databases, and they go like this:
1. Reboot the server! – Junior folks panic and freak out, and just restart the server. Sure enough, that erases all cached execution plans. As soon as the box comes back up, they run rpt_Sales for China because that’s the one that was having problems. Because it’s called first, it gets a great plan for big data – and the junior admin believes they’ve fixed the problem.
2. Restart the SQL Server instance – Eventually, as these folks’ careers progress, they realize they can’t go rebooting Windows all the time, so they try this instead. It has the same effect.
3. Run DBCC FREEPROCCACHE – This command erases all execution plans from cache, but doesn’t clear out many of SQL Server’s other caches and statistics. It’s a little bit more lightweight than restarting the instance, and can be done online.
4. Rebuild indexes – Doing this has an interesting side effect: when SQL Server reads an entire index to rebuild it, it gives you double the bang for the buck, and also updates your index’s statistics at the same time. This fixes the parameter sniffing issue because when SQL Server sees updated stats on an object used by an incoming query, it’ll build a new execution plan for that query.
5. Update statistics – As folks learn the above juicy tidbit, they realize they could get by with just updating stats. That’s a much lighter-weight operation than rebuilding indexes, so they switch over to just updating stats. However, I didn’t say it was lightweight.
6. Run sp_recompile for one table or proc – This system stored procedure accepts a table or a stored procedure name as a parameter, and marks all related execution plans as needing a recompile the next time they run. This doesn’t change the stats – but if you run it for China first this time, you may just get a more accurate query plan using the same stats.
7. Run DBCC FREEPROCCACHE for a single query – This one’s my favorite! Run sp_BlitzCache @results = ‘expert’ and scroll all the way to the right hand side. You’ll see plan handles and sql handles for your most resource-intensive queries. Find the one experiencing the parameter sniffing issue, and save its execution plan to disk first. (You’ll want this later.) Then pass its sql or plan handle into DBCC FREEPROCCACHE. Presto, just one plan gets evicted from the cache. It’s like a targeted missile strike rather than a big nuclear bomb.
How to Prepare to Fix Parameter Sniffing Permanently
Assuming you saved the execution plan like we just discussed, you’ve got a huge head start! That plan includes:
- A set of calling parameters for the stored proc
- An example of what the execution plan looks like when it’s slow
Now you need:
- At least one other set of calling parameters that produce a different execution plan (like our Monaco vs China example)
- An example of what the execution plan looks like when it’s speedy for each set of parameters
To get that info, check out How to Start Troubleshooting a Slow Stored Procedure.
Once you have all that, you’re ready to build a single version of the stored procedure that is consistently fast across all calling parameters. Start digging into Erland Sommarskog’s epic post, Slow in the Application, Fast in SSMS. Even though that title may not describe your exact problem, trust me, it’s relevant.
I’m teaching our SQL Server Performance Tuning class out in Portland this week, so we’re running all performance posts this week. You can learn more about my upcoming classes here.
When you need to find out why a stored procedure is running slow, here’s the information to start gathering:
Check to see if the plan is in the cache. Run sp_BlitzCache® and use several different @sort_order parameters – try cpu, reads, duration, executions. If you find it in your top 10 plans, you can view the execution plan (it’s in the far right columns of sp_BlitzCache®’s results), right-click on a select statement in the plan, and click Properties. You can see the optimized parameters in the details. Save this plan – it might be a good example of the query’s plan, or a bad one.
Collect a set of parameters that work. Sometimes the reporting end user will know what parameters to use to run the stored procedure, but that’s not always the case. You can also check the comments at the start of the stored proc – great developers include a sample set for calling or testing. Once you get past those easy methods, you may have to resort to tougher stuff:
- If it runs frequently, run sp_BlitzFirst @ExpertMode = 1 while it’s running, and save the execution plan to a file. Look in the XML for its compiled parameter values.
- Run a trace or use Extended Events to capture the start of execution. This can be pretty dicey and have a performance impact.
- Worst case, you may have to reverse-engineer the code in order to find working params.
Find out if those parameters are fast, slow, or vary. Ideally, you want to collect a set of calling parameters that are slow, another set that are fast, and possibly a set that vary from time to time.
Find out if the stored proc does any writes. Look for inserts, updates, and deletes in the query. There’s nothing wrong with tuning those – you just have to do it in development, obviously. Not only that, but you also have to watch out for varying performance.
If it does writes, does it do the same amount of writes every time? For example, if it’s consistently updating the same rows every time it runs, it’ll probably take the same amount of time. However, if it’s adding more rows, or deleting rows, it may not – subsequent runs with the same parameters might not have work to do, or might have a lot of work to do. You may have to restore the database from backup each time you test it.
Does the stored proc run differently in development? Is it slow in production, and fast in development? Or is it consistently slow in both locations? If you measure the query with SET STATISTICS IO, TIME ON in both servers, does it produce the same number of reads and CPU time in both environments? If not, what are the differences between those environments? You may have to change the dev environment’s configuration in order to reproduce the issue, or you may have to resort to tuning in production.
Erik says: Relying on SQL to cache, and keep cached, relevant information about stored procedure calls can feel futile. I’d much rather be logging this stuff in a way that I have better control of. Whether it’s logged in the application, or the application logs to a table, it’s a much more reliable way to track stored procedure calls, parameters passed in, and how long they ran for. You can even track which users are running queries, and bring people running horrible ones in for training. This can take place a classroom or a dark alley.
Exciting New Doodads
When SP3 for 2012 dropped, we were all super excited by the new query tuning-centric features that were at our disposal. Now all we had to do was get people to install SP3! Great features like this make patching an easier sell. Now with SP2 for 2014 out, a lot of those great features finally made it to 2014. Let’s talk about what they mean for you, and what they mean for us.
For you, dear reader
If you’re rocking the second-newest version of SQL Server, you can get all sorts of cool new insights into what your queries are doing when you’re not watching, as well as when you are. There are some great additions to execution plans baked in here, too. Some, like the TempDB spills, are in the actual execution plan only, but don’t let that get you down. This stuff was all included in 2016, so don’t feel like you’re going to miss out if you set your upgrade sights on the brand new. Note to self: someday this blog post is going to sound hopelessly outdated.
Seriously, if you build applications that hit SQL Server, get them tested on these newer versions. I’m not just trying to line Microsoft’s licensing pockets here, I’m trying to help you out. Many of these things are awesome for tuning performance problems. They will make your life easier. You’ll look like a hero when you can quickly diagnose why something is slow on a client’s kajillion dollar server that they don’t have any monitoring for and their DBA is a SAN admin who restarts SQL every morning because it’s using too much memory.
As consultants who build tools to make your SQL Server life easier, they’re great opportunities for us to not only see how these new features work, but also to figure out ways to let you know how to read, interpret, and troubleshoot problems with new information. We’ve been hard at work updating sp_BlitzCache and sp_BlitzFirst to keep you a sane and functioning SQL user.
Having this information standard across the three most recent versions makes presenting and analyzing it much easier. So what did we do?
If you run this with @ExpertMode = 1, you get a snapshot of what’s currently running at the beginning and end of the window you’re measuring. So, if you use @ExpertMode = 1, @Seconds = 30, you’ll see what was running on both sides of that 30 second window.
Pretty sweet. If you’re on a version that supports it, you’ll also get these columns in your results.
If your query isn’t running for the first time, or coming back from a recompile, these columns can have some useful information in them about all the many resources they’re using. You may be able to spot variations in here that can explain why things are slow ‘sometimes’. You may also be able to spot large memory grants on rather small queries, and/or rather large queries that for some reason aren’t going parallel, etc. Good stuff! Thanks, Microsoft.
Everyone’s favorite tool (poll in company chat) for spelunking your plan crevasse got some new columns and some new sort modes specifically for memory grants. Again, only if you’re on a supported version. You’ll see columns for Min and Max Memory Grants, Min and Max Used, and Average Memory Grant. There’s a new warning for when queries use <= 10% of their memory grant, which is configurable.
EXEC dbo.sp_BlitzCache @SortOrder = 'memory grant' EXEC dbo.sp_BlitzCache @SortOrder = 'average memory grant'
Sorting by memory grant will sort by the maximum grant requested column, and average memory grant will sort by the largest average, determined by max grant / executions.
If you’re living in the present
Give these new versions a shot, and if you’re giddy about GitHub, drop us a line with any issues, or code contributions.
Thanks for reading!
Microsoft has been quietly making some amazing improvements for performance tuners in SQL Server 2012, 2014, and 2016. This week, we’re going to introduce you to just how awesome they are. (They being the improvements, not Microsoft. You already knew they were awesome.)
Using the freely available StackOverflow database, let’s start with a simple query – SELECT * FROM Users:
When I hover my mouse over the SELECT in the execution plan, I get a little popup with things like estimated subtree cost, estimated number of rows, and more.
Now let’s add an ORDER BY:
When I hover over the SELECT, there’s a new line in town: Memory Grant. SQL Server needs RAM to sort the list of users before delivering them back to us. It didn’t need memory for the prior query because it was just dumping the data out as fast as it could read it.
You can see more details about the memory grant if you right-click on the SELECT and click Properties:
When SQL Server builds your query’s execution plan, it has to guess:
- Desired Memory – how much this plan wants in a perfect scenario, in kilobytes. Cached as part of the cached execution plan, and will remain consistent for all executions of this plan.
- Granted Memory – how much this execution of the query actually got. This can vary between execution to execution depending on the server’s available workspace memory at the time. Yes, dear reader, this is one of the reasons why sometimes your query is slow, and sometimes it’s fast. This value is not cached as part of the plan.
- Grant Wait Time – how long this execution of the query had to wait in order to get its memory. This relates to the Perfmon counter Memory Grants Pending. Not cached, since it varies from execution to execution.
- Max Used Memory – how much this execution used, and also not cached obviously. This is where things start to get interesting.
SQL Server’s query grant memory estimates are really important.
If it estimates too little memory, then as your query starts to bring back more and more data and it runs out, then you’ll be spilling to TempDB. That’s why we freak out when user-defined functions only estimate 1-100 rows will come back, or when table variables only estimate 1 row will come back.
As we join those objects to other things in our query, SQL Server may wildly underestimate the amount of work it has to do for the rest of the tables. You can see this in action in my Watch Brent Tune Queries video from SQLRally Nordic in Copenhagen.
On the other hand, when SQL Server estimates too much memory is required, we have a different problem:
- Your query may wait a long time just to start because that estimated memory isn’t available
- SQL Server may clear out a lot of otherwise-useful memory just to run your query
- Other people may have to wait for memory because a ton got allocated to your query
You can see this one in action in Kendra’s AdventureWorks query that estimates bazillions of rows. It estimates 10.5 terabytes of data will be handled, when in actuality it’s only 292MB.
But we’ve been flying blind – until now.
Until now, it was really hard to troubleshoot these issues. While the plan cache does show us how much memory a query would want, it didn’t show us how much was actually used.
We could try running the query to get the actual plan, but even that didn’t always get us the right answer. If we were suffering from parameter sniffing problems, one set of parameters might work fine while the other would produce catastrophic underestimates or overestimates.
We could try using Profiler or Extended Events to catch spills or huge grants as they happened, but…let’s be honest, you’re not doing that. You’re the kind of person who barely finishes reading a blog post, let alone starts firing up a tool before problems happen.
This comes up during Office Hours once a week
Which pretty much gives it a 100% hit rate. I’m writing this mostly to have something to reference when people ask.
If you restore a database on a SQL Server 2014 or greater instance, you have choices to make. You can either set the compatibility level to 120 (or 130, if 2016), and your queries will start using the new CE. If you leave it at a compatibility level below 120, it will continue to use the old one. You can change this at the query level by hinting with Trace Flags.
9481 will force the old CE in >= 120 database
2312 will force the new CE in a < 120 database
This gives you the ability to test queries, ALL OF YOUR QUERIES, with the new optimizer to check for regressions.
What’s the difference?
The new CE no longer assumes data independence. That means if you were searching a city and state combination, it would make no fuss about the relationship between the two. That doesn’t quite make sense, does it? The new CE aims to fix that, but it’s not always right either.
For more detailed write-ups on the inner workings, here are some links:
- MSDN’s coverage of the old & new CEs
- Joe Sack’s awesome white paper
- CSS blog post on the new CE, Part 1
- CSS blog post on the new CE, Part 2
Microsoft has also made guarantees that you’ll no longer face regressions when upgrading, because you won’t be using the newest CE unless your database compatibility level is set to the latest version. If you’re on 2016, you get some extra power over this with the Query Data Store.
Thanks for reading!
Brent says: this is one of the features we’ll be demonstrating during the Performance Overhead of Various Features session at Dell DBA Days 2016. Just by flipping one switch at the database level, you can get different query plans – so it’s important to know whether things are getting better or crazy better. (Okay, or also worse. But let’s think positive.)
Next week at Dell DBA Days, one of our sessions is The Unbearable Lightness of BEGIN. We’ll be talking about what happens when you start a transaction, how SQL Server manages lock escalation, and how indexes can help avert blocking issues.
For you to really get it, you’re going to want to already be familiar with the first two modules of my online class, How to Think Like the SQL Server Engine.
But I know how many of you subscribe to the blog, and how (relatively) few of you have seen this course.
So what am I gonna do? Well, I’ll give you the first couple of lessons this week for free so you can do your homework before Dell DBA Days. Print out this 3-page PDF to follow along, get yourself a warm cup of coffee, and enjoy:
UPDATE – the videos have expired.
I travel a lot, and I’ve seen a lot of things, but even I was surprised this week when I sat down in a Cracker Barrel Restaurant that had the oddest soundtrack. It turns out there’s an entire sub-genre of country music that specializes in database songs.
While I was sitting there eating my Uncle Herschel’s Favorite Breakfast (with country ham and hash brown casserole, of course), I heard tunes like:
- Your Cheatin’ Transaction
- The Day I Lost My Tables
- Papa Warned Me About Triggers
- He Left Me with MongoDB
- God Bless the Transaction Log
- The Night of the Cold, Dead SAN
- Save Me from Books Online
- She Thinks That She’s an Admin
- Distributed Transaction Blues
- Workin’ 99.999 to 5
- Why Won’t You Commit?
- The SQL Server 2000 I Hide
- The Best Little Data Warehouse in Texas
Amazing. I had no idea these were even a thing. I’m sure we’ll hear more next week in Round Rock. I might even sing a few for you – register to watch and find out. Leave your suggestions in the comments, and we’ll pick our favorite one Monday, announce it on the first Dell DBA Days webcast, and give the winner a free Everything Bundle.
Erik says: My favorites were “Mamas Don’t Let Your Babies Grow Up To Be Developers”, “Ring Buffer Of Fire”, and “Patching After Midnight”.
One of my favorite questions is, “How can I generate workloads to run against SQL Server for testing?”
Step 1: get the StackOverflow.com database. This 100GB database has a relatively simple schema, just a few tables, real-world data distributions, and enough rows that you can generate seriously slow queries.
Step 2: get the top user queries. Over at Data.StackExchange.com, users write their own queries to learn things about Stack’s data. I’ve taken a dozen highly-voted queries and turned them into stored procedures, all of which take a single integer parameter. This way I can call ’em with a single random number and get constantly varying queries.
Step 3: call them randomly with SQLQueryStress. The last stored proc in the above script is usp_RandomQ, which uses a random number generator to pick one of the stored procs and run it. I use a recompile hint on usp_RandomQ because I don’t want his metrics sticking around in the plan cache – the real workload is the various stored procs that he calls. Just set up SQLQueryStress to repeatedly call usp_RandomQ from, say, a dozen threads, and presto, your SQL Server will fall over.
This is the exact technique we’ll be using in the session Performance Overhead of Various Features, where I’ll be working with Transparent Data Encryption, the new Cardinality Estimator, Buffer Pool Extensions, the number of TempDB data files, and more. Register now to watch it free, and if your lab server is as beefy as ours, you’ll be able to follow along and see how your numbers compare.
Platform-as-a-Service users (Azure SQL DB, Amazon RDS) often ask me:
- How can I move my data into the cloud? Can I just take a backup on-premises, and restore up in the cloud?
- How can I use the cloud as inexpensive disaster recovery?
- Once I go to PaaS, why can’t I just get a backup file with my data?
- How can I refresh an on-premises dev server from cloud production?
- How can I do cross-provider disaster recovery inexpensively, like have a primary in AWS and a secondary in Azure?
- Why am I locked into just one cloud provider once I go PaaS?
Until now, the single biggest problem has been that both Azure SQL DB and Amazon RDS SQL Server don’t give you access to backup files. If you wanted to get your data out, you were hassling with things like import/export wizards, BCP, or sync apps.
This is a really, really, really big deal, something Azure SQL DB doesn’t support (and I dearly wish it did). I get even more excited reading this because now Microsoft has to do it in order to remain competitive, and that’ll make Azure SQL DB a much more attractive product for traditional DBAs.
Here’s the use cases that it supports:
“I’m on-premises, and I want to use the cloud as DR.” Just keep taking your full backups as normal, but use a tool like Cloudberry Drive to automatically sync them to Amazon S3. When disaster strikes (or preferably, when you want to test and document this process long before disaster strikes), spin up an Amazon RDS SQL Server instance and restore your backups. Presto, you’re back in business. (I’m glossing over all the parts about setting up web and app servers, but that’s a developer/devops/sysadmin problem, right?)
“I have big databases, and I want to experiment with the cloud, but can’t I upload fast.” Ship your USB hard drive to Amazon with your backups, they’ll copy ’em into S3, and then you can spin up RDS instances. Got more data? Check out Amazon Snowball.
“I’m using the cloud, and I want cross-provider DR.” Run your primary SQL Server in Amazon RDS, schedule regular backups to Amazon S3, and then use a cross-provider file sync tool or roll your own service to push those backup files from Amazon S3 over to Azure or Google Drive. When disaster strikes at Amazon (or if you just want to bail out of Amazon and switch cloud providers), just restore that backup somewhere else. Same thing if you want to refresh a local dev or reporting server, too.
“I’m using the cloud, but I might outgrow Platform-as-a-Service.” PaaS makes management dramatically easier, but both Amazon and Azure set limits on how large your databases can get. Putting your database in Amazon RDS or Azure SQL DB is basically a bet that your data will grow more slowly than their database size limits. If you bet wrong – which is a great thing because your data skyrocketed, usually indicating that you’re in the money – you have an easy transition into IaaS (self-managed SQL Server in the cloud) rather than the painful hell of dealing with data exports.
This right here changes every SQL Server cloud presentation that I give. It’s really that big.
This week, Brent, Richie, Erik, and Tara discuss new Microsoft certs for data science, index rebuilds, replication, patching SQL server, and what to say to a manager that says he needs an active/active HA solution but has no idea what that means.
If you prefer to listen to the audio:
Office Hours Webcast – 2016-07-20
How should I manage a thousand database schemas?
Brent Ozar: Joe O’Conner asks, he says, “Any suggestions on tools or methodology for standardizing a schema to accommodate a thousand plus instances that may all be slightly different?” Joe, let me ask a follow-up question too if you can return back and ask, do you want them to be different or do you want them to be the same? That will probably influence the answer that we give you there.
Why is replication showing high CPU use?
Brent Ozar: Let’s see here, next up. James Kelly says, “In SQL Server 2005…” I’ve got to stop using last names because somebody is going to ask a question they’re ashamed of—like James Kelly—who has SQL Server 2005. Poor guy, god bless him. It’s not a bad database platform, it’s not horrible. It’s better than 2000 was. He says sp_replmonitorrefreshjob causes high CPU when suspended mode on the distribution database. What should I look into in order to reduce CPU? Tara, I’m even sorry I read that but you’re the only one here that would be remotely qualified.
Tara Kizer: Yeah.
Brent Ozar: You know anything about that? I don’t know anything about that.
Tara Kizer: I haven’t encountered that and I used replication for, I don’t know, ten years or so, what is meant by suspended mode on the distribution database? Is that when the jobs are not working?
Brent Ozar: I think he’s doing sp_WhoIsActive. I bet he’s doing sp_WhoIsActive.
Tara Kizer: Oh, but suspended doesn’t necessarily mean anything. “Causes high CPU…” Are you sure it’s that refresh job that’s causing the high CPU? If it is, I would start taking a look at the replication monitor and looking for what kind of latency you have in the queues. I would imagine you’ve got a large backlog and maybe that’s why it’s taking it a while to pull up the data.
Brent Ozar: That makes sense. So my webcam probably just froze. I all of a sudden got pouring down rain here, you could hear it in the building, just all of a sudden it pours down and immediately my internet dies. I’m like, oh, it’s going to be like that. It’s going to be like that.
Richie Rump: What kind of backwater [inaudible 00:01:41] are you in?
Tara Kizer: Yeah, I was going to say. Richie is like, it rains all the time.
Brent Ozar: Dennis says that MS certs are great at uncovering what areas you don’t know. Yeah, but also what areas you don’t need to know.
Tara Kizer: Or what areas you do know.
What do you think about the new Microsoft degree for data science?
Brent Ozar: Greg asks an interesting follow-up question. “What do you guys think about the new Microsoft certs for data science?” Did you guys see that new degree?
Erik Darling: Yeah.
Brent Ozar: Richie clearly saw it. Erik saw it.
Tara Kizer: I only heard about it, I haven’t seen it.
Brent Ozar: I didn’t look at it, I just saw the headlines and went, “Hmm, what?” So Richie, you made such a face. There must be a good reason you made that face, or a bad reason.
Richie Rump: Calling it a degree is weird to me. It’s just strange. I don’t understand it. I spent five years getting my four-year degree, so am I going to spend six years trying to get my seven-year MS degree? I don’t understand. I don’t understand it.
Erik Darling: Is it really that long term? I thought it was like hours.
Brent Ozar: Well, but it’s also the kind of thing you’re going to do spare time. It’s not like, I hope to god you’re not going to take out a loan and go do that kind of thing. Go spend $1,000 on exams or something.
Erik Darling: It was like $500 or whatever, or something. I don’t know, I didn’t read all the fine print.
Brent Ozar: There’s going to be like no training on it, third party for a while, because it’s all brand new. And that platform changes so fast. I’m like, I don’t get that. So I’m not keen on that.
Richie Rump: I mean basic stuff it doesn’t, but I mean is that what they’re going to teach? They’re going to teach me the basics of data science independent of their platform? Or are they going to say, “This is how we do it at Microsoft?”
Erik Darling: … golden hammer from Microsoft.
Brent Ozar: There was a statistics course in it, or like one statistics test. But then the rest was heavily Microsoft. Yeah, exactly. I was like… especially if, you want to know more than one toolset if you’re going to do the data science thing. In the database community, you can know just Microsoft and you can make a wonderful living for yourself. It’s fantastic. Data science is not—you don’t get to learn just one tool, you’ve got to learn a lot of tools.
Yadda Yadda Yadda
Brent Ozar: James says he’s waiting for his boss to get a credit card and register for the Senior SQL DBA class next week. Cool, very cool. We’re doing an online class there for four days.
Brent Ozar: Nate Johnson says, “SQL Server Management Studio 2016 says it won’t work with 2005 instances but it’s working okay for me. Have you guys used SSMS 2016 with 2005?” I will answer that one. No, we were doing La Cucaracha the day that 2005 was out of support. Erik was doing a countdown for that date. He could not wait. So we haven’t used it at all. Usually what that means with Microsoft is just that they don’t have it in their test matrix anymore. It usually works, there’s edge cases where it won’t but usually you’re okay.
Brent Ozar: Thomas says, “I think if you’re new, the certs are good because they show that you’ve been at least exposed to areas of SQL Servers. In my day job, I don’t do any analysis services, reporting services, or integration services so the certs will give me some exposure to them in an organized way.” You can die of exposure, you know that. That’s dangerous.
Erik Darling: Naked and afraid.
Brent Ozar: Naked and afraid, in indexes.
Richie Rump: Unsubscribe.
What should I do about an index rebuild that takes 90 minutes?
Brent Ozar: Michael Tilly asks a question that is really inconsistent, multiple parts, and a novella. So if you guys want to read into it, we’re going to read through it. Michael says he has a question about index rebuilds. He works at a large university, actually Brent’s alma mater—I don’t think you’re allowed to say that unless you graduate—and University of Houston for three semesters, so yeah. “I have a large students table…” 35,000 students a year, if I remember correctly. “That has many, many millions of rows of active and inactive students. I have a maintenance plan that does a nightly index rebuild on the…” Holy cow. “On the…” Yeah, already we’re shaking our heads. “It seems to take an excessive amount of time, 90 minutes. Right now we’re rebuilding it online. I’m thinking that maybe I should turn it offline. What should I do?”
Erik Darling: I think you should turn that maintenance plan off.
Tara Kizer: Off, yeah, why are you doing this?
Erik Darling: Because my whole thing with what you’re saying is like you probably get a whole bunch of students once a year.
Brent Ozar: What are you trying to say?
Erik Darling: I’m trying to say that you enroll students once a year.
Brent Ozar: Some of them graduate.
Erik Darling: Yeah, but you enroll students once a year. So like you add in a new crop of students and at that one point, your table might be fragmented. Then after that, you might have some dropouts, but you just change them to inactive. You don’t like delete them from the table. I can’t imagine the students table is getting heavily fragmented aside from enrollment and graduation. Or like even then, graduation is probably just switching to inactive. So I just can’t imagine a way for fragmentation to even accrue that much on a daily basis.
Brent Ozar: I think if they had an index on estimated completion or estimated graduation date, my record would have moved around a lot during the course of me being there. But I think for the rest of the people, not so much.
Richie Rump: So what can he do, Erik?
Brent Ozar: Oh, yeah, good point.
Erik Darling: You would have to go back. I wrote a blog post recently, no surprises there.
Brent Ozar: What?
Erik Darling: Peanut gallery, all of yous—it was about when you want statistics instead of an index but it touched a little bit on why index fragmentation isn’t really that important and up-to-date statistics are far more important. So what I would do in your shoes is I would stop rebuilding the index every night. I would stop rebuilding indexes pretty much in general and then just update the statistics daily and see how you fare. You’ll probably just end up in the same place now where performance is what it is because the statistics got updated, it didn’t reflect all of the fact that you were rebuilding the index every night and wasting hours and hours of your life and precious disk and CPU time.
Tara Kizer: And for the update statistics, because you have several million rows of data in that table, you could probably just get away with like a 20 percent, 30 percent sampling. You probably don’t need to do a full scan on that table.
How do I resync AGs when replicas have different drive letters?
Brent Ozar: Nathan says he’s got an availability group and they very rarely have to resync their dbs. They’ve added new databases and “we also have to start syncing those. But we’re not able to do a full sync because the drive letters don’t line up anymore. For example, I have some drive letters that exist on the primary but not on the secondaries. Is there a way to automate this process without doing backups and restores and joining them manually?”
Tara Kizer: You have to do a backup and a restore and say with move in order to get the different drive letters. So, no. There isn’t a different way.
Brent Ozar: Yeah, that blows.
Erik Darling: Rename your drives.
Tara Kizer: Yeah, rename your drives. Script this out so that it’s automated at least, you’re not having to do it manually.
Erik Darling: Yeah, there are actually a bunch of questions on dba.stackexchange.com about how to automate adding databases to availability groups. There are some, not like fully fleshed out scripts, but there were some pretty good outlines of scripts on there about how to get started but you are going to have to make certain considerations for the fact that you have different drive letters and that stuff when you’re writing your script.
Brent Ozar: I want to say there’s a trick with the SUBST command too. Like there’s this command line S-U-B-S-T that can let you make fake drive letters to point to other drive letters. Totally don’t quote me on that but just like google it to see. I’m sure it’s not supported but just as a fun trick.
Why isn’t Brent giving money to the University of Houston?
Brent Ozar: Michael from my fake alma mater comes back and says, “As it turns out, we do have constant fragmentation because we keep all our student history.” Yeah, I know because you keep asking me for donations which seems pretty stupid because I dropped out. Why would I give you money if I didn’t finish? He says, “As we constantly update the home address and phone number and other info, so lots of updates.” Listen, you’ve got to unsubscribe me. I’ve asked several times. That’s not true. Although you did bulldoze the dorm that I was in and I was a little pissed off about that one.
How do I change account owners on an endpoint?
Brent Ozar: Let’s see here, Thomas says, “We have a former DBA who granted the rights on the service accounts to an endpoint.” I think he used his own personal service accounts on an endpoint, like mirroring or availability groups. “I can’t drop his login without changing that. Is there a way to change which service accounts have permissions on an endpoint without dropping and recreating the connection? I tried ALTER AUTHORIZATION and that didn’t work.”
Tara Kizer: What’s the endpoint being used for? I mean can’t you just rebuild whatever that is? Is it mirroring? Is it an availability group? What is it?
Brent Ozar: Yeah, it should be a brief outage, right? Just recreate it.
Tara Kizer: Yeah, exactly, get a maintenance window for this.
Brent Ozar: Yeah, I like that. And you want to be patching anyway. I’ll also take the crazy thought of, “Why change it?” Leave it under his account. As long as you have the password.
Tara Kizer: Or maybe rename it so that it’s like a service account now and not someone’s—you know, because the [inaudible 00:10:26] will remain the same. So it will appear as a regular account at that point.
Brent Ozar: The service account formerly known as…
Tara Kizer: Yes.
More about synchronizing a thousand schema differences
Brent Ozar: Joe comes back with the thousand schema thing. Joe says, “Are there any suggestions or tools or methodology for standardizing to a single schema that will accommodate slight differences in a thousand plus instances without losing any data?” Wow.
Tara Kizer: It’s a thousand plus SQL Server instances and trying to get them all into one database with different schemas? I’m confused what the question is.
Brent Ozar: You know what I bet he’s doing, Erik. I bet he’s doing what your favorite ISV does. Yes, I bet, so we blog about this kCura Relativity, it’s a piece of software out there and customers are allowed to change tables but your deployment scripts still have to work. How do their deployment scripts work, like when they drop a new version of their app?
Erik Darling: The last time I did it, the deployment scripts were in a numbered folder and called by a C# program—by some program. Don’t quote me on the C#. But it’s tough because you have to specifically change only your indexes. The last thing didn’t go so well because there was some custom objects that they didn’t account for that I had to like go back and manually change. So I don’t know that I would recommend that process as an overall process. But, you know, they do alter tables directly from scripts and everything.
Brent Ozar: Run them in order based on their version upgrades. They also have dozens of developers to manage that kind of thing.
Erik Darling: Yep.
Brent Ozar: Lee asks, “I’m curious, why does the model database, the system database model, why does it have recovery set to full?”
Tara Kizer: Well that’s just because it’s the default for new databases so when you create a new database and you don’t provide all of the settings, it uses model as the template and it’s just the default that Microsoft has set. I really wish that they would switch it to simple. If they’re going to have everything be default to full, then how about you default to setting up transaction log backups too?
Brent Ozar: Oh, yes. Yeah, I like that. We should have a connect item for that.
Tara Kizer: Closed. Won’t fix.
Erik Darling: Immediately.
Why does my log file grow even in simple recovery model?
Brent Ozar: Lakshmi says, “I have a database in a simple recovery model and we make a lot of changes on say Friday nights. The transaction log grows to like 20 gigs, why is it that my log file is growing even in simple recovery model?” Did I say simple recovery mode at the beginning? That’s Tara’s hot point.
Tara Kizer: It’s one of mine, yeah. Everyone says “recovery mode.”
Brent Ozar: Yeah, simple recovery model.
Tara Kizer: You’re logging of your transactions doesn’t change based upon your recovery model. It’s still the same amount of logging. So if you have 20 gigabytes of data changed, or at least logging, it’s going to be the same with simple or full. It’s what happens at the end of the transaction is what’s different between the two recovery models. With simple, that is cleared from the transaction log after it completes, either rolls back or commits. With full, it stays in there until something backs up the transaction log. So the size doesn’t change based upon recovery model.
Brent Ozar: Some yoyo doesn’t BEGIN TRAN, your log is going to be growing for a while.
Does antivirus slow down SQL Server?
Brent Ozar: Matthew says, “Have you guys ever experienced noticeably degraded performance as a result of having antivirus installed on a SQL Server?”
Tara Kizer: Yes. Definitely. That’s why you need exclusions in place. At one point we had something like 46 exclusions on the database servers. I think it went even higher. I forget what the number was, 60s or 70s I think. But there’s a list of exclusions that you need to have in place on a SQL Server. It’s SQL Server executable, your MDF files, LDF, NDF, all these… There’s just tons of them and I believe that this is documented somewhere, what your exclusions should be. I don’t know where it is though.
Brent Ozar: There’s a KB article. If you Google/Bing whatever “SQL Server antivirus.” Then you site: support.Microsoft.com. If you site: in Google and restrict it to a single site you’ll get the answers just from the Microsoft Knowledge Base.
Where can I read about lifecycle management?
Brent Ozar: Graham says, “Other than Microsoft’s content,” so I guess we know how he feels about Microsoft’s content, “are there any good end of life or lifecycle management references for SQL Server versions?” Did you guys ever see any good database documentation around when you should upgrade or how to handle upgrades other than Microsoft’s?
Erik Darling: Most of the ones that I’ve seen reference a lot of Microsoft’s stuff, like using the Best Practice Advisor and the Upgrade Advisor and using those as sort of jump off points for what you should do from then on out. Microsoft does, at least I think, a fairly decent job for the Upgrade Advisor stuff, like giving you the scripted out things that you need to do your migration with.
Brent Ozar: Tara, with all the SQL Servers that you guys managed, how did you do processes for upgrades? Did you like write out, “Here’s exactly what we’re going to do” and which order, or was it like different every time you guys did a SQL Server?
Tara Kizer: When I was at QualComm and I was the primary DBA for a specific division it was really based upon what projects were in the pipeline and if we could get an upgrade placed into that project. If they were doing a major release, maybe we could get that database upgraded. I’m sure to this day they still have SQL Server 2005 instances out there. I’ve been gone from them for about three years but I’m positive just because how many there were when I left. There weren’t any 2000 but tons and tons of 2005. Some of them probably just get stuck there. Not all companies care about not being supported by Microsoft. So what if you don’t have any security hotfixes. If you have a secure environment, you may not care about that type of stuff. Companies really care about getting releases out, making their customers happy. Do they really care about the SQL Server version behind the scenes? The DBA team cares, you know, what do we have to support.
Brent Ozar: That’s why we still at the company we still run Windows 95 here at Brent Ozar Unlimited. It works really well.
Richie Rump: Well I’m on 3.11, that Workgroup edition.
Brent Ozar: I’ve been trying to get him to upgrade that Trumpet TCP/IP stack is just not…
Richie Rump: It’s an oldie but a goodie, man.
Brent Ozar: Oldie but a goodie, that Solitaire, no Minesweeper like 3.11.
Richie Rump: Hot dog theme, that’s what it is.
Brent Ozar: Oh my god, the hot dog theme is correct. So for those of you who ever get out in the community if you ever get the chance to Erland Sommarskog speak, Erland Sommarskog is a presenter in the SQL Server community and he usually uses the hot dog theme on his desktop. It’s just awesome. Comic Sans everywhere, pastel colors.
Tara Kizer: That would drive me crazy.
How often should I patch my SQL Server?
Brent Ozar: Adam asks, “What do you guys recommend for patching? What is a good balance of staying up to date with SQL Server patches while not just being on the hamster wheel continuously and doing constant updates? How often do you recommend patching SQL Servers?”
Tara Kizer: So based upon these large corporations that I’ve worked with, most of them want to do monthly patches. As the person who has to do the patches, I don’t want to do monthly patches because it is painful when you have to do 100 servers, 700 servers. We had a lot of servers that had to be patched. It wouldn’t just be one person having to do all this patching, it would have to be a team of people. It was disruptive. There was always some issue on some server that patches would not work, maybe failovers weren’t working that day. There was always some issue. It was just painful. It took way too much time. I wanted to do quarterly but large enterprises want to ensure that their environments are secure. Now, that goes against what I just said about SQL Server 2005 and not having security hotfixes—and these are the same corporations I’m talking about. But yeah, as one of the people on the patching team, I did not want to do it monthly. I would much prefer quarterly. Oracle doesn’t do monthly. Or, I should say, Oracle teams don’t do it monthly. I don’t mean just one company. It’s very common for them to do quarterly or twice a year. Why is it so important on Microsoft? I mean I realize it’s because of all the bugs there are as far as security goes.
Erik Darling: The really good thing about quarterly patches too is that it gives you some time to apply them to a development environment and let them burn it and make sure that they don’t do anything too funky. Because you know a lot of times a patch will get released and within the first 24 hours a lot of people will be raising their hands like, “It broke something.” So it gives you time to test things out as well. Quarterly is a good span of time to run across something awful.
Tara Kizer: Yeah, one of the companies I worked for, the patches came out on Tuesdays, “Patch Tuesdays” was what everyone called it for Microsoft patches. Then by Thursday we had to have installed, in production. I mean that was very aggressive. We ended up switching it to the following Tuesday, but still, it’s still very aggressive.
Erik Darling: So was that the “see you next Tuesday?”
Tara Kizer: Yeah. I mean, yeah, they’ve been installed in a test environment and maybe there’s some automated testing that has occurred on those, but that’s not enough time to burn those patches in.
Brent Ozar: That would suck.
Erik Darling: That’s horrible.
Should I use Desired State Configuration for SQL Server?
Brent Ozar: Graham says he’s trying to move to DSC, Desired State Configuration for SQL Server upgrades and migrations. I haven’t seen anybody successfully use DSC for SQL Server. Stack was trying to use it and failed. They had to give up on it and they have really bright PowerShell guys over there. I don’t think it’s quite there yet for SQL Server. It makes total sense for Windows, just not quite there yet for SQL Server.
Tara Kizer: I’ve never even heard of that.
Brent Ozar: The idea is you programmatically declare what you want the server to end up as and then Windows takes care of patching, installing the right features and getting it to that point for you. It’s not quite there.
Tara Kizer: Isn’t that what System Center Configuration Manager does? I mean it just knows what you’re missing.
Brent Ozar: Yeah, except you’re also supposed to be able to declare in code the whole state you want to get to, server names, what IIS tools you have installed, SQL Server features, where you turn things on and off. Yeah, it’s pretty tough.
Thanks for putting the Everything Bundle on sale
Brent Ozar: Lakshmi says, “Thank you, I just made my manager purchase the Everything Bundle from your training. Hope I can learn much more from there.” Well what are you doing on our free webcast? Go over to the good stuff. Here we’re just answering questions. These people don’t know what they’re doing. Go learn the good things. No, but thank you for picking that up. That’s awesome. We have a good time building that stuff.
Why does the Database Tuning Advisor only tell me to drop indexes?
Brent Ozar: Anker says, “Hi, I’m using the database tuning advisor in SQL Server 2014 for recommendations but it’s only giving me drop index recommendations.” [Laughter]… listen, I was trying to keep a straight face while asking this question. “Does the DTA ever provide any index suggestions too?”
Tara Kizer: Why are you using DTA?
Brent Ozar: So why should he not use the DTA?
Erik Darling: DTA is craaaaazy.
Brent Ozar: How else are you going to get those?
Erik Darling: We have a tool called sp_BlitzIndex which is very helpful for finding missing index requests. There are some differences between it and DTA. Missing index requests are fueled by a process in the query optimization process called index matching. The stuff in the DMVs that gets logged is per query. The stuff that DTA logs is per workload. So you might have something in your settings that’s not giving you missing index requests for your workload that you can do both and you can click a bunch of stuff where you want suggestions, even for index views I found out recently. So that’s the difference between what they each do. But if it is only generating drop index recommendations and you have missing index recommendations enabled, I would really like to see what comes out of sp_BlitzIndex for you because that’s a wild time.
Richie Rump: Yeah, I have a blogpost that I was working on and it introduces a script that prevents the DTA indexes from being created.
Brent Ozar: So the blogpost, I don’t think it’s gone live yet.
Richie Rump: No, I was working on it. I haven’t loaded it yet. Still writing it. But the script is—I wrote the script. The script is great.
Nice video on the Dell DBA Days blog post
Brent Ozar: Greg Smith says, “Great video on the Dell DBA Days blogpost today.” We’re excited about that. We’ll all be down in Round Rock sweating through our shirts in August. We’re going to try and stay in the air conditioning.
Richie Rump: What else is new, Brent?
Brent Ozar: What else is new.
What would you say to a manager who wants…
Erik Darling: There’s a really good question up top, if you don’t mind.
Brent Ozar: Oh yeah, who asked?
Erik Darling: It’s a Scott Kelly question. “What would you say to a manager that says they need an active/active HA solution but has no idea what that means?”
Erik Darling: I think the most diplomatic way to handle that is to ask them to sketch out what that would look like and provide a budget for it. Then go from there because—just don’t walk into your manager and say, “You don’t know what that means.” Ask them to spend some time sketching it out, you know, a Visio diagram or just a whiteboard of what it would look like and how much he thinks it would cost. That’s where I would go from there if I was in your shoes.
Tara Kizer: This is a topic that I had to deal with a few times at one of the companies. Every few years management would be like, “We want to do active/active.” So every single time, we would go to the vendors and say, “What can we do to make this happen?” Here comes the price tag and we’d send it to management and they’d say, “Oh no, never mind.” It is very expensive. We’re talking two million dollars probably.
Erik Darling: In hardware, not just licensing.
Tara Kizer: Yeah, exactly. That’s several years ago last time I looked at it.
Brent Ozar: And Graham, if you get our download pack, if you go to BrentOzar.com and click First Aid up at the top, we have a download pack. We have a high availability and disaster recovery worksheet in there that helps you sketch out how much data you’re willing to lose, how long you’re allowed to be down for. One of the options is a zero/zero failover solution. It’s a million bucks and up. So just be honest and talk through it with the manager. The other thing I’d ask the manager is, “What’s driving that? What is that’s making you want that?” It may just be that they want cost savings or whatever and I’d say, “All right, so can you introduce me to someone else you’ve worked with at one of your past places who can help us design that?”
“Well, I’ve never worked with anything…”
“Oh, me neither. Sounds like it’s a good time for me to go to training.”
Are a lot of companies running SQL Server on Windows Core?
Brent Ozar: Let’s see. I had another question in here. Tom asks, “From what your team is seeing, have many companies started using Core installations yet? Windows Core.” Have you any of you guys seen SQL Server on Windows Core yet?
Erik Darling: Nada.
Tara Kizer: I started playing with it as far as what we would have to do to install everything and what does a database team need to learn. But this was back on 2008 Core and it wasn’t very good.
Richie Rump: Yeah.
Tara Kizer: Yeah, so we abandoned it and I’m sure that company is now using Windows Core because we really wanted to get to that to do Microsoft patching because there’s a lot less to patch on Windows Core than there is on the full versions.
Richie Rump: Yeah, same thing here. Where I was at a company and they were testing it. The IT folks really wanted to go Core and the DBAs were like, “Whoa, wait a second. I don’t know how to handle some of this stuff.’” They tested it and they were like, “Maybe not.” So, they abandoned it.
Tara Kizer: Was it Windows 2008 Core where it first was released?
Brent Ozar: Yeah, I think so.
Tara Kizer: Yeah. It was terrible. We were researching how to set like lock pages in memory via code. All of these things that you have to do on the database server when you need to set up a SQL Server. Once it’s there, you probably have some PowerShell stuff. But back then, it was just painful. I mean we figured it all out, but we decided we just didn’t want to support it yet so we were waiting for Windows 2012 Core.
Brent Ozar: If I remember right on 2008 Core you also had to enable TCP/IP through the SQL Server configuration manager. And even that [inaudible 00:25:39] goes through the GUI so.
Tara Kizer: Yeah.
Do we have to pay for the First Responder Kit?
Brent Ozar: Anker says, “Hi, I love using your scripts from the First Responder Kit. I want to use them in production to get performance recommendations but my manager stopped me in doing so after reading the license agreement of yours. Do we have to buy it from you?” No, it’s totally free. In fact, if you go to firstresponderkit.org it has the open source license on there. When you go to firstrepsonderkit.org go click on the GitHub link at the top right. It has a license that’s very normal for open source products. You are free to use it in any capacity that you want to use it as long as you keep that copyright in there. If you try to for example bundle it into a paid product and you don’t include that node of where it came from, then it gets a little sketchy but you can use it for—if you want to take over the world with SQL Server using Service Broker and linked servers, whatever you want to use it for, go knock yourself out.
How do I grant access to stored procedures for low-privilege users?
Brent Ozar: Nate says—this will be the last one that we take because he says, as soon as I see a question that says, “I don’t know if you have time left,” that means I probably should have read it before I do it. It says, “When I was a young boy…” He says, no, “If I had a stored proc that needs to be executed with a low level user but requires higher permissions and I don’t want to get the low level user…” He wants to give people access to run stored procedures but they need to be peons. What does he need to do? If you go to BrentOzar.com/AskBrent, that sounds like it’s an advice column. It’s not, it’s about a stored procedure but it has a little instruction on there on how you use certificates in order to grant permissions to users to run stuff. Really simple. Sounds horrifying but it’s super simple. That certificate word sounds awful.
Erik Darling: We’re probably going to change that URL at some point now, huh?
Brent Ozar: At some point I need to move all the rest of the documentation over there because people are going to go, “What do you mean Ask Brent? That doesn’t make any sense.”
Erik Darling: Yeah. Which one?
Brent Ozar: You changed your name on here too now.
Erik Darling: I did, magic.
Brent Ozar: Oh my goodness. Wow. We look professional. Well thanks everybody for hanging out with us. We’ll see you guys next week at Office Hours. Bye, everybody.
Erik Darling: Bye-bye.