- Managing what requests are made
- Managing the efficiency of each request
- Managing the hardware capacity available to satisfy requests
And that’s it.
In the grand scheme of things, those are the only 3 knobs you get.
Oh sure, from the outside, performance tuning looks more complicated than that, but at the end of the day, it always comes back to those 3 knobs and knowing which ones you can turn.
Managing what requests are made and when starts with understanding your workload: analyzing the queries that are running, where they’re coming from, what the data is being used for, and why the request is being made. The easiest free way to start with that is sp_BlitzCache, and one of my favorite ways to start the discussion is by using @SortOrder = ‘executions’ to show the most frequent query requests. Time after time, folks just flat out don’t know what the app is actually requesting from the SQL Server – and when we start asking why, the tuning process often progresses to … just not making those requests, like caching static configuration data in the app tier.
Managing the efficiency of each request starts with measuring what the queries are doing, and how the database server is configured to respond to those requests. Tuning efficiency can mean changing indexes, isolation levels, SQL Server settings, or even tuning the query itself.
Managing the hardware capacity available starts with measuring how overloaded the server is. I like using wait time per core per second to figure out if there’s smoke coming out of the server, or if it’s sitting around bored.
As a consultant, I like giving clients a couple/few different options that fall into different categories. For example, if they’re querying the database for static configuration data thousands of times per second, they could either cache it in the app tier, or continue to invest more in hardware capacity to sustain those requests.
When you compare caching to cha-ching, suddenly folks get it. They’d rather go to the registered cache than the cash register. For a good primer on caching in .NET, check out Nick Craver’s post on how Stack Overflow does caching.
11 Comments. Leave new
One thing I’ve had to check for the servers I’ve looked at is the number of executions, but also the time for those executions. It’s great to know that a certain query only runs 12x/hour, but when you look deeper and see that the query runs for almost 5 minutes each time, that could be an issue. 🙂
Greatly appreciate these tools – they have definitely helped narrow down major issues quickly and efficiently.
Peter – awesome, you’re welcome!
Because of you Brent the DBA’s world wide actually love their job!
Dready – awww, thanks!
Do you know what knob means?
Ajith – there are a couple of different definitions. The one I’m using is a common one – a dial for adjustment – but another common one is the slang term for male private parts, which might be used to describe someone who leaves a blog post comment that says, “Do you know what knob means?”
Was that the response you were looking for, or something else?
SELECT ‘ha’
GO 10000
perfect reply.
top kek
It’s a bit like saying “What does it take to launch a rocket into space? – That’s easy, just press the red button”.
Easy for you to say, Brent 🙂
Nicely explain Brent !! Thanks