Sometimes you see someone with toilet paper sticking to their shoe, their shirt tucked into their underwear, or badly in need of a tissue to get rid of whatever that is hanging out of their nose. It’s only right to find a discreet way to let them know they should do some quick cleanup before they get completely embarrassed.
The same embarrassing “oops” exist for SQL Server. There are some practices and configurations which just make it look like the DBA may not have learned about this “internet” thing yet.
But, truthfully, it’s very simple to accidentally embarrass yourself. Here are the top 10 (well, 11) signs that your skills or your SQL Server are badly in need of an update:
- You have databases that haven’t been backed up recently
- Your last DBCC CheckDB is over 2 weeks old
- You have jobs that shrink files– or autoshrink enabled
- You truncate your transaction log
- You’re running the RTM version of SQL Server
- You use SQL 2000 style commands like DBCC SHOWCONTIG and DBCC REINDEX on later versions of SQL Server
- Priority Boost is enabled
- Power savings is enabled
- App accounts have SA or db_owner permissions
- It’s a 32-bit SQL Server
- Your tempdb has only one data file
Even if you know better, have you checked your servers to make sure that you don’t have toilet paper on your shoe without realizing it? Run our free sp_blitz® script to check for most of these issues, and more.
A lot can change in a year and the world of Hadoop is no exception. Join Jeremiah Peschka in this webinar to learn about new features in Hadoop, changes to existing features, how these changes make your life as a DBA better, and where you can go to get started with Hadoop. This session is for people curious about how Hadoop is changing and how it makes their job better.
Say you run an online store, and on each item’s page, you need to show related items that people purchased. Let’s take an Amazon page for my favorite rechargeable AA batteries:
In a perfect world, we would cache this data in the web/app tier – but back here in the real world, sometimes our developers build stored procedures to fetch this kind of data, and the stored procedure ends up getting called way too often.
To solve it, let’s build a caching layer into our stored procedure.
Here’s the pseudocode of how our stored procedure usually works:
I’m using a really simple stored procedure, but this approach works best when you have a complex stored procedure that does a lot of heavy lifting – calculations, querying big tables, aggregations, etc.
Instead of directly doing all the heavy work, check this out:
I’ve introduced a new table here – Cache.dbo.GetRelatedItems. I created a new database called Cache, and I keep it in simple recovery mode. I can use a totally different backup strategy for this database – perhaps not even backing it up at all.
The Cache.dbo.GetRelatedItems table has exactly the same columns that are normally returned by usp_GetRelatedItems, plus the input field. In this case, our stored procedure has an input field of ItemID, and it returns RelatedItemID and RelatedItemName, so the table would look like the one shown at right.
Rocket surgeon readers will note that I added an ID field to give the table something unique. They will also notice that I didn’t add a DatePopulated or DateCached field – depending on my business needs, I would probably just truncate this table every night. In the case of related items in an e-commerce store, I might only truncate it every week, and I’d want to do it right before a really low load period so that the cache could gradually refresh. This outright-truncation technique is less efficient for refreshing the cache, but it minimizes the locking required by deletes. In a caching setup, I’m worried about concurrency here.
When implementing a solution like this, I usually do a lot of A/B performance testing to find the right clustered index for the table. Typically each caching table has no non-clustered indexes, and has just one clustered index designed to produce the fastest range scans for the number of parameters for the stored proc. (Before somebody posts a comment asking for that strategy, no, I’m not blogging that, because it’d take me a day, and I’m lazy.)
If you choose not to back up the Cache database, your code should not rely on the existence of objects in it. It should start by checking to see if the Cache.dbo.GetRelatedItems table exists, and if not, create it. That way if you fail over to your DR site and the Cache database is empty, your queries won’t fail.
I’ve over-simplified the stored procedure a little, though – something actually has to populate the cache table. There’s two ways I could do it: externally, like a SQL Agent job or an SSIS process, or internally – inside the stored procedure itself. Let’s code that:
We start by checking the cache table for results, and if we don’t find any, we add them.
I’m cringing as I write this because I can hear the screams of performance tuners. Yes, I’m adding additional write load on the SQL Server – keep in mind that I only use this approach when I’m faced with:
- A very work-intensive but read-only stored procedure
- Called very frequently (hundreds or thousands of times per minute)
- Whose results change less than once a day (or where we’re not concerned about real-time accuracy)
- A business that needs immediate speed and can’t wait for developers to implement a caching layer
As soon as I deploy a solution like this and the business pain goes away, I immediately start working with the developers on a better long-term solution. This solution is an emergency band-aid to get the business up and running, but it still incurs load on the SQL Server for writing the caching results, getting locks, and running the stored procedure. This is when I start talking to the developers about caching in the app tier, and here’s my favorite resources on that:
Our mysterious employee #2 – where did he come from? How did he get into SQL Server? What does he want to learn in his first year at work with us? The answers to those questions, and more, will reveal themselves in this webcast recording:
Answer: Yep, sometimes it can.
I used to think that higher degrees of parallelism followed a law of diminishing returns– you could add more threads, but the benefits would taper off. But it’s a bit more complicated than that. Microsoft’s recommendation to be careful when setting maxdop to values over 8 is a warning worth heeding.
Lowering maxdop can cut CPU without sacrificing duration
We tend to think that reducing the number of threads available MUST make a query slower– but that we sometimes need to do it so other queries can run concurrently.
It’s great to support concurrency, but lower DOP doesn’t necessarily mean a longer runtime, even if it does lower CPU consumption. Here’s a simple example of an OLTP query run with two different DOP settings. CPU time and duration were measured using “SET STATISTICS TIME ON”. For both runs all data was in memory (no physical reads or read aheads).
Duration in both cases was around 700 milliseconds. The lower DOP didn’t make execution time longer. It changed the way SQL Server ran the query, but it cut overall CPU usage while keeping the runtime about the same. This was great in this case, because the query in question needed to run frequently on an OLTP system.
Higher maxdop can slow down large queries
In the case of large queries, higher DOP can slow down query execution. The impacts here vary a lot by processor, memory type and amount, and whether or not your SQL Server is virtualized— not to mention based on the execution plan of the query in question.
Here’s a totally different query tested on totally different hardware. In this case the query reads in hundreds of gigabytes of data, but as in the previous example all tests were run against a “warm” cache and were not doing physical reads or read ahead reads. The server used in this test had 8 physical cores per NUMA node.
Changing maxdop changes query plans
When you tune maxdop, it’s worth watching the execution plans for the top queries on your server. I’ve seen changing the amount of threads available for a query make the optimizer change its mind about how to run a query immediately– and been able to reproduce it switching the plan back as soon as I hint a different DOP.
Since impacts can be complicated I recommend changing maxdop rarely and monitoring your plan cache and wait stats for at least a week or two after the change.
Parallelism is a good thing
Don’t get me wrong– I ain’t saying parallelism is bad for SQL Server. Multiple threads can make many queries faster!
One of the gotchas with SQL Server is that the default value of “0” for the “Max Degree of Parallelism” setting can lead to poor performance– because it lets SQL Server use all your processors (unless you’ve got more than 64). Fewer threads can not only reduce CPU usage, but may also be faster.
So check your maxdop settings, and keep reading to learn more about CXPACKET waits.
When you vote for us in Simple Talk’s new Tribal Awards, you’re voting for yourself.
Best Free Script – sp_Blitz® – Sure, I started this all by myself a few years ago, but it’s grown into something huge. Scroll down through the change log and get a load of the dozens of contributors who have helped make this tool amazing. I even hear from consultants who ask me, “Are you sure it’s okay to use this thing in my job?” Of course! We’re all in this together, and we’re all trying to make SQL Server easier for everybody.
Blog of the Year – www.BrentOzar.com – To me, the comments are the most fun thing about a blog. Since the first post 11 years ago, we’ve had 16,650 comments (and no, that doesn’t include the spam). This place is fun because you guys take part, and even when you say crazy stuff, at least I get to use my witty retorts.
Person You’d Most Like to Have a Beer With – Brent Ozar – Let’s be honest – you guys only nominated me because you know that whenever I’m drinking beer, I’m also ordering tater tots. It’s not that you want to have a beer with me – you want to eat my tater tots. You’re just being greedy here.
This artist formerly known as SQL Azure is a cloud service something akin to Microsoft SQL Server.
When it first came out, it had a lot of challenges – a small subset of T-SQL commands and datatypes, inability to take backups, and absurdly small database sizes.
But much like Emma Watson, when your back was turned, WASD matured into a surprisingly capable platform. However, most of us still aren’t using it, preferring to run SQL Server on-premise.
This leads to an interesting question: what would Microsoft have to do to get you into Windows Azure SQL Database?
Time for a thought exercise.
You’ve got a database application that has bursty and unpredictable loads. Out of nowhere, you’ll suddenly get socked with a large amount of SELECT queries. Due to the way the app is written, you can’t cache the query results – the queries keep changing, and the business wants data from within the last couple of minutes.
In the past, you’d have used replication or AlwaysOn Availability Groups to build multiple read-only reporting servers, but that means big SQL Servers that sit around idle most of the time. You have to size them for the bursts in load. You could probably get by with a single 2-socket server most of the time, but when the loads come in, you need an 8-socket server to handle all these reads.
But what if you combined virtualization with SQL Server’s ability to add AG replicas on the fly? Think Amazon’s Auto Scaling, but on-premise. The script logic would go something like this:
- If your current readable replicas are experiencing bottlenecks that could be fixed by adding additional replicas,
- And the load lasts more than X minutes/hours (set by the business’s goals)
- Start up a new virtual machine (ideally a template with SQL Server already installed)
- Add it to the cluster
- Restore the most recent full and transaction log backups to it (very easy if you’re already backing up to a network share, and would require no additional load on the current replicas)
- Join it to the Availability Group
- Add it to the read-only routing list
And presto, you’ve got more power. You can also use the same type of logic to tear down replicas you don’t need.
If you wanted to get even fancier and more proactive, rather than adding an all-new server to the Availability Group, you could have the script shut down one of the existing replicas, add more vCPUs and/or memory, and start it back up again. That way you could add more power without having to deal with backups and restores, but on the downside, this means temporarily taking away power from the existing AG.
You can even do this without affecting end users. Take the replica out of the read-only routing list, wait for the last query to finish, and then start the maintenance work on that replica.
It would only make sense at companies where:
- The app’s config string used the ApplicationIntent=ReadOnly parameter for read-only queries, thereby letting us move those to read-only replicas
- The write loads can still be handled by a single server
- The script could finish in time to handle the added load (for example, this isn’t going to work with 5TB databases on slow storage)
- There’s an incentive to get rid of unneeded replicas (because some companies are just okay running lots of unneeded servers to handle peak loads)
- Licensing is done at the virtual host layer, not at the guest layer (which rules out Amazon EC2 and Azure VMs, neither of which would be cost-effective here)
If I was going to write something like this, I’d do it in PowerShell because I’d want one language that interfaces well with my monitoring software, VMware/Hyper-V, Windows clustering, and SQL Server. Alas, I’m not doing it anytime soon – I’ve gone through this thought exercise with a couple of clients, and in both cases, the answer has been, “Screw it, we’ll do it manually instead.” It sure is a fun thought exercise, though.
It does sound like a lot of work, but the slick part is that once the scripts are built and tested, you can leverage it to auto-scale read load for any of your AG-protected databases.
And, uh, open source it, will you? Kthxbai.
When you identify that parameter sniffing is a problem, you need to test whether implementing ‘recompile’ hints will help plan quality. (Not sure what parameter sniffing is? Learn from this blog post or this 30 minute free video.) You must decide: what hint or command do you use, and where do you put it? What trade-offs are you making when it comes to being able to performance tune your SQL Server in the future?
This post runs through common options you have to nudge (or whack) SQL Server into generating a fresh execution plan. I’ll give some pros and cons for each method and explain what’s useful and what’s worth avoiding.
To keep things relatively simple, I’m just discussing how this applies to stored procedures today–this post doesn’t cover other forms of parameterized (or non parameterized) queries. If you’ve got big questions in those areas, feel free to suggest it for a future post in the comments.
Disclaimer: Recompile hints can kill your performance by lighting your CPUs on fire when used incorrectly. Handle with care!
Useful Dynamic Management Views
When I talk about impact on the execution plan cache, I’ll refer to two DMVs:
- sys.dm_exec_query_stats – This DMV is helpful to see the top statements on your SQL Server, regardless of whether they’re part of a procedure or not. Check out a sample query here.
- sys.dm_exec_procedure_stats- This DMV is only available in SQL Server 2008 and higher. It tracks execution metrics for stored procedures. Check out a sample query in Books Online.
For both of these DMVs, having an execution plan in the cache is linked to being able to see execution metrics: number of total executions, total and average CPU, logical reads, etc. When an execution plan is removed from the cache due to recompilation, memory pressure, restart, or other actions, the related execution metrics are removed as well.
Executing a procedure with a runtime recompile hint
One option that I love for quick and easy testing is the ability to call a stored procedure with a recompile hint at execution time. This is great because you don’t have to change any compiled code. Your hint also just applies to what you’re doing at runtime.
To do this, you just use syntax like this:
EXEC dbo.Proc3 @parameter1=80 WITH RECOMPILE; GO
However, there’s a little bit of a gotcha. If you have nested stored procedures, the recompile hint only applies to code in the outermost procedure. You can still get parameter sniffing on any calls to sub-procedures within the stored procedure itself.
So while I do like this sometimes for testing, if you have any nesting in your stored procedures it may not help you get a truly fresh execution plan.
Using the sp_recompile System Stored Procedure
This sometimes comes in handy if you need to intervene during a production incident if you’ve got a bad plan being used repeatedly by incoming sessions. You can force recompilation of plans by running sp_recompile against an object– most common choices are a table or a stored procedure. The command looks like this (a table in this example):
EXEC sp_recompile 'Person.Address'; GO
When this is run, related plans are not immediately removed from SQL Server’s execution plan cache. Instead, the magic happens the next time queries referencing the recompiled object run. At that point, existing execution statistics in sys.dm_exec_query_stats will be reset for statements in the plan.
Execution counts in sys.dm_exec_procedure_stats will not necessarily be reset at next use if you mark a table used by a stored procedure for recompilation. But if you mark the stored procedure itself for recompilation, it will be reset at the next use. (Don’t bother trying to memorize this, just read the next paragraph.)
There’s big downsides with this one. This command requires high permission — the user running it requires ‘alter’ permissions on the table. It also requires a high level of lock to complete. On busy systems I’ve seen this command take part in some nasty blocking chains. Be careful with it!
While this can occasionally be useful for troubleshooting and testing, don’t make it part of production code.
RECOMPILE hints in stored procedure headers
When you’ve got procedures that you want to generate fresh plans, you start looking at how to use hints within the procedure. The option most folks discover first is to use ‘WITH RECOMPILE’ in the header of the stored procedure, like this:
ALTER PROCEDURE dbo.Proc3 @parameter1 int WITH RECOMPILE AS SELECT DISTINCT City as CityFromProc3 FROM Person.Address WHERE StateProvinceID=@parameter1; EXEC dbo.Proc2 @parameter1; GO
Using RECOMPILE in the stored procedure header is pretty drastic — the procedure won’t cache an execution plan when it runs. This means:
- No execution plans in cache to review
- No execution stats recorded in sys.dm_exec_query_stats
- No execution stats recorded in sys.dm_exec_procedure_stats
Wow, that’s a bummer. If you need to identify the impact this procedure is having on your server, you have to run some sort of trace or extended events session and harvest and interpret the results. That’s not quick, and running traces can impact performance.
Kind of a big negative. For that reason, I don’t like this choice much at all.
RECOMPILE hints on individual statements
This option is a bit more work, but it has a much better payoff over time. With this option you take the RECOMPILE hint and apply it only to statements in the stored procedure where you’d like to implement the recompile, like this:
ALTER PROCEDURE dbo.Proc3 @parameter1 int AS SELECT DISTINCT City as CityFromProc3 FROM Person.Address WHERE StateProvinceID=@parameter1 OPTION (RECOMPILE); EXEC dbo.Proc2 @parameter1; GO
Even if you need to use the hint on all the statements in the procedure rather than in the header, this still has benefits! By putting the recompile hint at the statement level in the proc, you magically get:
- Limited execution plans in cache to review (last execution)
- Limited execution stats recorded in sys.dm_exec_query_stats. You’ll only get stats for the last execution, but the plan_generation_num column will increment, at least giving you insight that something is up.
- Execution stats recorded in sys.dm_exec_procedure_stats
The fact that you do get some information in these DMVs can be super useful over time. Applying recompile hints only to the statements that need them is also just more responsible– it lowers your risk of burning yourself up over time with CPU burn.
DBCC FREEPROCCACHE – the nuclear option
This isn’t strictly a recompile hint– but it certainly does cause recompilation. SQL Server has a command you can use to tell it, “Start anew with fresh execution plans”. It’s usually used like this:
--This is the nuclear option DBCC FREEPROCCACHE GO
This command makes everything start fresh– for every query:
- CPU usage may go up as new plans are compiled
- Execution stats are cleared in sys.dm_exec_query_stats (immediately)
- Execution stats are cleared in sys.dm_exec_procedure_stats (immediately)
This command isn’t evil– there’s a time and a place in troubleshooting when using it can help you get to the root cause of a performance problem quickly. However, since it impacts the whole cache and can also impact performance, you must be very careful using it. I do NOT recommend using this command in any regular jobs, automated processes, or production code.
As of SQL Server 2008, you can use this command a bit more gently– you can remove a specific plan from the cache using the “plan handle” or “sql handle”– but of course you’ve got to figure out what that handle is and why you want to remove it. This could come in useful in some niche situations, but in practice it doesn’t come up much. You can also clear a resource governor pool, but, well you’d have to be using resource governor.
What’s it all mean?
When you’re testing, there’s a place in your toolkit for both ‘EXEC procedure WITH RECOMPILE’ and the sp_recompile procedure. Use them with care and beware of possible repercussions, particularly with sp_recompile.
When you’re implementing hints in code, don’t have RECOMPILE blinders on– you can use ‘OPTIMIZE FOR’ hints sometimes successfully as well (although sometimes you’re optimizing for mediocre). And at times, dynamic sql can also help you out.
But if you do use RECOMPILE hints, please keep your recompiles at the statement level– and not in the headers of your procedures.
Learn More in Our Execution Plan Training
Our How to Read Execution Plans Training explains how to get an execution plan, how to read the operators, and learn solutions to common query problems.
Wanna know where you fit in relation to someone else? Get the answers to these questions.
1. How many SQL Server instances do you have? The pro folks use the word “instances” rather than servers because clusters may have multiple SQL Server instances per node.
2. How many DBAs work with you? One person managing 300 instances is very different than a team of 5 people managing those same servers.
3. What’s the total data size? You don’t have to get fancy and calculate space used – just add up all the files in Windows Explorer. If you want to collect this on a regular basis, use the backup sizes from the msdb tables.
4. How busy is your busiest server? Check the Perfmon counter SQL Server: SQL Statistics – Batch Requests per Second during your peak load times.
Have these answers ready off the top of your head, and you’ll get much better advice from other admins. The answers for a 100GB server doing 100 batch requests per second are very different than the ones for 10TB doing 100,000 batch requests per second.