Getting Help with a Slow Server

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:

sp_BlitzFirst @SinceStartup = 1

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:

Full output of sp_BlitzFirst @ExpertMode = 1 (click to zoom)

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.

Previous Post
PasteThePlan October 2017 Update
Next Post
[Video] Office Hours 2017/09/13 (With Transcriptions)

10 Comments. Leave new

  • Jens Herrmann
    October 7, 2017 12:25 pm

    Hi, investigation our Servers with sp_blitzfirst I found something I can not explain, maybe right, maybe wrong:
    Node: 0 State: ONLINE Online schedulers: 8 Offline schedulers: 0 Processor Group: 0 Memory node: 0 Memory VAS Reserved GB: 215
    Node: 1 State: ONLINE Online schedulers: 8 Offline schedulers: 0 Processor Group: 0 Memory node: 1 Memory VAS Reserved GB: 0
    Can you tel me, if it a missconfiguration or if this is right?
    I thought both Nodes should allocate the same amount of memory.
    I have read many articles about numa nodes, but I don’t found this particular info.
    Best regards, Jens.

    • Jens – go ahead and read the post carefully. This post is about how to get your SQL Server questions answered, and you can start with the very first paragraph – which talks about where to post your server questions. I’d much rather teach you how to fish and get you set up for life.

      • Jens Herrmann
        October 8, 2017 3:05 am

        Thanks Brent, I thought it would be ok, to ask a question beside the main point of the post. I ran EXEC sp_Blitz @OutputType = ‘markdown’, @CheckServerInfo = 1 and I noticed something strange. Sometimes you just have to ask someone. My wife and my doughter did not know either and so I wrote the question into your website. i understood the main focous ouf your Post is how to get into investigating why a server ist slow. Maybe there will be another post about Processors and than I’ll be back, Brent. : )

        • Jens – yeah, we just strongly encourage folks to hit DBA.StackExchange.com for questions so they can learn to get the help they want at any time. Don’t wait around for a relevant blog post – whenever you’ve got a question, that’s what those Q&A sites are for. (And we answer tons of questions over there, too!)

  • Fabien Sabinet
    October 9, 2017 3:07 am

    If I have to diagnostic a slow server I run my tool (SQLProcexp) and I immediately have a realtime view of the active processes and in the osWaitStats tab a realtime view of the active curent wait (important one in red) :

    If you double click on a line you get a description on how that wait happened and what to do.

    And actually reading your article I just improved the explanation for the ASYNC_NETWORK_IO wait ! thanks you Tara 🙂 Tell me if I did get it right :

    ASYNC_NETWORK_IO : Occurs on network writes when the task is blocked behind the network. Often it’s not a network problem in fact, first verify that the client application is getting all the data at once and then processing it, instead of getting and processing the data line by line.

    • Fabien Sabinet
      October 9, 2017 3:09 am

      Ops the picture did not show up… Here :

      https://fabiensabinet.files.wordpress.com/2017/10/currentwait1.png

    • Fabien – I appreciate that you’re really excited about marketing your tool, but I’m noticing a trend that you’re starting to use our site as a promotional site for yours. Let’s hold off on promoting your tool through the comments, okay? We’ll let this comment stand, but from here on out, feel free to contact us directly if you’re interested in some kind of advertising arrangement. Thanks for your understanding!

      • Fabien Sabinet
        October 9, 2017 6:03 am

        Understood Brent.

        Concerning advertising arrangement, your site would be in the first place if I was selling my tool but as it is, and will stay forever free, it’s a little hard for me to think about paying for making it known…

        Anyway thanks for your free help on that until now, it was huge for my little level, and henceforth I will do as you just said.

        • OK, great, thanks for your understanding. As we’ve discussed before, unless you open source it, we don’t have a guarantee that it will be forever free.

          • Fabien Sabinet
            October 9, 2017 7:09 am

            I know that it worth almost nothing in our day, and I think it’s one of the saddest thing in this world we made, but I gave and I give again my word that SQLProcexp will be free forever.

            Even more, I take the closed source as an insurance that it will stay free because if I release it open, some company will not hesitate to take it and after some few modifications sell it (as a lot of free source code ends up sadly).

            Anyway, if one day you try it and like it, I will be delighted to listen what you think about it and how you think I could make it better (as some users did already 🙂 ).

Menu
{"cart_token":"","hash":"","cart_data":""}