Using query history, cached plans, and runtime data to investigate performance.
200 associated posts60 primary posts
You may have read that you need to have more than one data file in SQL Server's tempdb. This can happen even if you're using blazing fast storage. If you create a lot of tiny objects in tempdb you may hit a bottleneck on special pages that SQL Server uses internally to allocate all those objects. For certain workloads, adding more tempdb files speeds up SQL Server.
Finding the worst queries in SQL Server is easy, right? You just query one of the dm_exec_* DMVs, bring in the SQL text and the execution plan, and then you spend hours looking at execution plans trying to figure out what’s wrong in the query, much less how to fix it. All that’s changing - we’re introducing sp_BlitzCache®.…
We've all found those databases. They're on your production SQL Server instance, but nobody seems to know if they're being used, or what's using them.
You could run some sort of trace, but you'd probably drag down performance on your SQL Server. What you need is a quicker way to find out if the database is in use.
Is a Login Using the database?
And if so, what query did they run last? My favorite way to see this is with Adam Machanic's sp_whoisactive.
Analyzing a workload can be difficult. There are a number of tools on the market (both free and commercial). These tools universally reduce workload analysis to totals and averages - details and outliers are smeared together. I’m against using just averages to analyze workloads; averages and totals aren’t good enough, especially with the tools we…
It’s easy to find problem queries on your SQL Server after they’ve happened. You can open your favorite monitoring tool or look in the plan cache. Those options are great for finding things after they’ve happened, but what if you want to find problems as they happen?
Server Side Trace
You could set up a SQL Server Server Side Trace. This is an acceptable solution if you’re using SQL Server 2008 or earlier. There are a few problems with this approach.
"Can you tell me a little about this code?" I asked, keeping my best poker face on."I'm really proud of it. It gathers a whole bunch of stuff from accounting, human resources, manufacturing, you name it. I want to be able to track how we're doing over time." He nearly hovered in his chair with…
The plan cache holds a lot of secrets about what’s going on inside SQL Server. In the First Responder Kit we shared one of our plan cache scripts to find the top resource consuming queries. That query works well, but over time we’ve added some additional functionality to the query. I figured it was time to share the new query that we’re using to analyze SQL Server performance.
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?
Problem: your database server’s performance stinks like Limburger cheese.
Solution: find the queries that are making that smell.
Hiding in Plain Sight
Your database server knows exactly which queries are performing the worst. When queries are executed, the execution plans – and associated execution statistics – are stored in memory (with a few exceptions). This information is stored in the plan cache, and you can access it by querying a couple of DMVs.
In the e-discovery business, kCura Relativity is the big gorilla, and I've been working with kCura and their clients since 2011. I think it's one of the most interesting businesses I've ever worked with - up there with StackOverflow - and like Stack, they're really open with their customers. Today marks my second year being…
I came across an interesting case recently where queries were failing in a high transaction SQL Server. We knew that queries were occasionally failing with error severity 20 because SQL Server Agent alerts were periodically firing. However, those built in alerts don't capture exactly what has failed-- they just say "Something failed big. Good luck with that."
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.
No one knows everything, especially about SQL Server. When you encounter a problem, especially for the first time, you may not know how to solve it. I’ve seen some crazy approaches taken to fix problems – like duct tape on a car. Many times, instead of fixing the root cause of a problem, such as a poorly-performing query, a server-level change is made.
I’m curious. I like to know how things work. I have to read the user manual of everything I buy so I know all of its features. I like clicking all the buttons in applications I work with. However, SQL Server Management Studio (SSMS) has so many I haven’t gotten through all of them. At SQL Saturday #118 Wisconsin earlier this year, I was watching a presentation and the presenter clicked “Include Client Statistics”.
When you send your beautifully hand-crafted organic T-SQL statement to SQL Server, the database engine takes a moment to appreciate the poetry of your work. Before it turns your request into results, it has to build an execution plan. SQL Server considers: Which tables it needs to join together What subqueries it needs to execute…
What are the costs when you ask SQL Server to recompile a statement or a stored procedure each time it runs? In this 30 minute talk Kendra Little explains the impact on query execution-- and on your SQL Server instance-- of forcing recompilation. This talk is appropriate for DBAs and Developers who have a working knowledge of execution plans in SQL Server.
What’s slowing down production? It’s easy to find out in 60 seconds by running a simple plan cache query. See, in order to turn your beautifully written query into a set of results, SQL Server needs a plan – an execution plan. It caches these plans in memory along with statistics like the number of…
When your SQL Server is critically injured you need a first responder kit to help you diagnose the problem and apply emergency aid. In this session, Kendra Little introduces you to invaluable tools and techniques for triaging an emergency. If you have one year’s experience with database administration, this half-hour session will set you up to triage like a pro.
So you're lazy and you're company's broke - what to do? Here's my favorite free downloads to help manage SQL Server: Understand execution plans with SQL Sentry Plan Explorer - I dunno about you, but viewing execution plans in SQL Server Management Studio is a pain in my rear. The scrolling sucks, the cost numbers…
SQL Server database administrators need to be able to quickly find out what queries and stored procedures are running slow. Microsoft includes sp_who and sp_who2 in SQL Server 2005 and 2008, but there's a much better tool, and it's completely free.
In this five minute tutorial video, I explain how to use sp_WhoIsActive from Adam Machanic (Blog - @AdamMachanic):
