StackExchange users often ask, “Why is my server slow?” Here’s a few tips to get better, faster answers.

Server information

You may have already included server information in your question, but it may not be sufficient. Run sp_Blitz to output a lot of server information fast.

Click on the XML mumbo jumbo and post the output to your question. Here’s an example of a question with the sp_Blitz markdown included. This is the bare minimum of stuff we need to give you a decent answer – but keep reading, putting in more info in, and we’ll be able to give you a great answer.

What is SQL Server waiting on?

As a performance tuner, I start my analysis with the wait stats. If I’m going to help answer why your server is slow, I need to know what SQL Server is waiting on.

I want to know what the waits are since startup and what the waits are right now. That information gives me a good picture of the server.

You can use sp_BlitzFirst to get that data.

To get the waits since startup, run:

Screenshot the first result set and add it to your question.

If you are clearing out your wait stats, the @SinceStartup data is not going to be good. Please don’t clear out your wait stats! If you are clearing out your wait stats, tell us how many hours ago the last clear was. We always need to know what our sample size is when looking at the waits.

To get the waits right now, run:

Screenshot the third result set (wait stats) and add it to your question. It would be beneficial to also screenshot the other result sets, but for now we need to know the waits.

You can use other values for @Seconds. 30 seconds gives a good picture if the performance issue is happening right now.

If the top wait is ASYNC_NETWORK_IO, you can stop here. You have your answer. It is not a SQL Server problem. It’s almost never a network problem even though the wait stat name makes you think it is. It’s sometimes a bottleneck on the application server, but it’s usually an application bottleneck. Applications that do row-by-row processing while SQL Server is sending the data cause ASYNC_NETWORK_IO waits. SQL Server is sending the data fast, but the application is telling SQL Server to stop while it processes what it has been sent so far. While SQL Server is waiting to be told to send more data, it’s recording waiting time on ASYNC_NETWORK_IO. To fix it, modify the application so that it consumes all of the data from SQL Server and THEN does its row-by-row processing.

If the top wait is PAGEIOLATCH_SH, run sp_BlitzIndex @Mode = 0 as it is indicative of memory pressure which can often be fixed by adding high-value missing indexes. Screenshot the “Indexaphobia: High value missing index with High Impact section”. You can hide your object and database names. We need to see the Details, Usage and Size columns. You can hide your object names in the Details column to protect your company.

For all other waits, I dig into the other result sets from the second sp_BlitzFirst call (waits right now).

Let sp_BlitzFirst help us help you

When you run it with @ExpertMode = 1:

The first result set shows current activity at the very start of that sample. Any long-running queries in there? Any non-NULL wait_info or non-NULL blocking_session_id values? Show us that output.

The second result set gives you informational data as well as high priority data. Was there high CPU utilization? Are there any waits of note in there (though we’ll already have seen those with the 3rd result set that you posted)?

The fourth result set shows the I/O performance during that sample for any database file that had some reads or writes. You may have database files that aren’t listed there, it’s just that there weren’t any reads or writes on them during the collection. Do any of the files that are listed have an average stall of over 30 milliseconds. My target for reads is 15 milliseconds or less, and my target for writes is 5 milliseconds or less. Are any files showing high average stalls? The wait stats should reflect I/O waits if there’s a current I/O bottleneck.

The fifth result set is a dump of the SQL Server Performance Monitor counters. I generally skip over that section as I’ll usually pull that up in Performance Monitor as I want to see the values over time.

The sixth result set? I have no idea. Ask Erik.

And finally the seventh result set. That one shows current activity at the very end of the sample. Anything of note in there? Maybe there wasn’t any blocking when the sample started but maybe there is now.

Current activity via sp_WhoIsActive

I also use sp_WhoIsActive to look at current activity. On servers I’ve supported in the past, I have it logging to a table every 30-60 seconds and retaining the data for a few days so I can go back in time. I’ll query the WhoIsActive table in that case. If you don’t have that in place, run sp_WhoIsActive. If you see any long-running queries or blocking, run sp_WhoIsActive again with @get_plans = 1. This will give you the respective estimated execution plan, if it exists. If there’s a specific query you’d like help with, share the execution plan with us via Paste The Plan.

Sometimes fast, sometimes slow

If users are suddenly complaining that things are slow when it was just fine earlier, you might be experiencing what’s called bad Parameter Sniffing. Parameter Sniffing is a good thing, but it can cause problems.

If bad Parameter Sniffing is occurring on a frequently executed query, it could cause high CPU utilization which would slow everything down.

Grab the plan’s XML and save it. Then try removing the plan from the cache for that specific query. If users are now happy, you found the culprit and can now work towards a more permanent fix. We can help with that if you post the plan that you saved prior to removing it from the plan cache. It would also be helpful to see the plan after things are back to normal so that we can do a comparison. You may also need to post the query if the query is big as it may get truncated in the plan.

Check your question for questions and answers

Be sure to periodically check your question to see if it has been answered but also to see if anyone has any questions that could help get your question answered. Your question will be looked at by a lot of people soon after it has been posted. Refresh the page a few minutes after it is posted and again an hour later. Do this hourly for the first day and then daily until it has been answered. Most questions should get a good answer fairly quickly if the question is clear and if enough data has been provided.