Parameter sniffing, parameterization, recompilation, and cached-plan reuse.
62 associated posts60 primary posts
This week, Erik and Richie discuss performance tuning, relativity, Always On Availability Groups, parameter sniffing, technical debt, unit testing, deadlocks, testing storage performance, and more...
Here’s the video on YouTube:
I've been using the Stack Overflow public database for my performance tuning demos for a few years now, and I just keep finding more reasons to love it. It's got a few simple tables with a lot of data. The main ones are Users, Posts (questions & answers), Comments, Votes, and Badges, ranging from 1GB…
Facemaking
You know when you think you know something, and the obviousness of it makes you all the more confident that you know it?
That's usually the first sign that there's a giant gotcha waiting for you.
This week, Brent, Tara, Richie, and Erik discuss SQL Server 2017 CTP 2, quorum configuration for a 4-node availability group, database sharding, premature optimization, parameter sniffing, tracking query plans within cursors, and what's coming up in the new version of Paste The Plan.
Here’s the video on YouTube:
This week, Erik, Tara, and Richie discuss altered tables and update statements, parameter sniffing issues, their issues with Azure, a resource for column store indexes, AlwaysON availability groups, killing sleeping connections, and fill factor value for indexes.
Here’s the video on YouTube:
This week, Erik and Richie discuss truncating log files, reindexing, using sp_rename, query tuning, columnstore indexes, debugging parameter sniffing issues, query processing internals, and troubleshooting query performance issues.
Here’s the video on YouTube:
You've probably heard about parameter sniffing
But there's an even more insidious menace out there: Parameter Snorting.
It goes beyond ordinary parameter sniffing, where SQL at least tried to come up with a good plan for something once upon a compile. In these cases, it just plain gives up and throws a garbage number at you. You've seen it happen countless times with Table Variables, Local Variables, non-SARGable queries, catch-all queries, and many more poorly thunked query patterns.
On this first Office Hours call of 2017, Brent, Erik, Tara, and Richie discuss multi-server jobs, how to find motivated Junior DBAs, working with SAP databases, partitioning, AlwaysOn Availability Groups, tracking SQL users, SQL monitoring tools, data capture, source archiving, and much more!
Here’s the video on YouTube:
During our pre-con in Seattle
A really sharp lady brought up using filtered statistics, and for a good reason. She has some big tables, and with just 200 histogram steps, you can miss out on a lot of information about data distribution when you have millions or billions of rows in a table. There's simply not enough room to describe it all accurately, even with a full scan of the stats.
Say you've got an application that has tons of OPTIMIZE FOR UNKNOWN hints in the T-SQL, and you're getting bad query plans.
We're going to use the same StackOverflow query (and the same index on Reputation) that I demoed in the post Why Is This Query Sometimes Fast and Sometimes Slow? This technique produces a query that will produce two different execution plans depending on the Reputation parameter.
You swear you didn't change anything, but all of a sudden the SQL Server is going doggone slow. What happened? Parameter sniffing might be the problem, and to explain it, let's see how it works. I'm going to use the StackOverflow database - particularly, the Users table that I demo in How to Think Like the…
This week, Brent, Richie, Tara, and Erik discuss enhancing queries, database corruption, availability groups, parameter sniffing, sending mail via SQL server, whether SQL 2016 is solid or not, other versions of SQL, physical servers vs virtual servers, and much more!
Here’s the video on YouTube:
This week, Brent, Richie, Erik, and Tara discuss parameter sniffing, database backups, referential integrity in databases, clustering, creating and using indexes, in-place upgrades, bench marking tools, and more.
Here’s the video on YouTube:
This week, Brent, Richie, and Tara discuss corruption, resetting query stats, fixing parameter sniffing issues, how to suggest features for our scripts, and why Brent is sporting an Oracle sweatshirt.
Here’s the video on YouTube:
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.
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…
Sometimes you end up in a good plan / bad plan situation: an important query runs just fine most of the time. The query is parameterized, a good execution plan gets re-used, everything is cool. But sometimes, a "bad plan" gets compiled and starts to be reused. This is "bad" parameter sniffing. "Bad plans" can come in a few…
There's an important rule for tuning stored procedures that's easy to forget: when you're testing queries from procedures in SQL Server Management Studio, execute it as a stored procedure, a temporary stored procedure, or using literal values.
Don't re-write the statemet you're tuning as an individual TSQL statement using local variables!
Where it goes wrong
Stored procedures usually have multiple queries in them. When you're tuning, you usually pick out the most problematic statement, maybe from the query cache), and tune that.
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 50 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?
Imagine you work for a zoo, and you need to ship an animal to another zoo. If you are told one will be a mouse and one will be an elephant, you will need to handle them differently. Elephants get moved by truck, while mice can be put in a box and driven or flown.
