March 28, 2022 at 8:44 pm
Hi All,
(see attached waits) I have a very slow sql server, it seemed to happen over night and queries are still slow. index rebuild/reorg and statistics have been updated. what are my troubleshooting options?
March 29, 2022 at 2:10 pm
More data. "Something is slow and here are a bunch of wait stats" doesn't tell us anything. I'd suggest enabling Query Store on the database in question. It's relatively lightweight in terms of observer overhead. It will capture query metrics for you, along with execution plans. They are aggregated, but they're aggregated hourly so you can do before & after comparisons (big, big part of query tuning). That's going to show you specifically which queries are running slow and when. Then, once you know specifically which query, you can look at the text for that query to see if there are any common code smells you can address. After that, look to the execution plan to see if there are issues there. Adjust the query, data structures and indexes as needed. Test, measure & do it all again.
Another approach, one that involves a lot more data, would be to use Extended Events to capture query metrics. Here you'll get extremely detailed data, again, showing which queries are running slow. You can combine this with wait statistics for the individual queries if you still want to use wait stats as an indicator.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 29, 2022 at 3:34 pm
Hi All,
(see attached waits) I have a very slow sql server, it seemed to happen over night and queries are still slow. index rebuild/reorg and statistics have been updated. what are my troubleshooting options?
Have you clicked on any of the URLs provided by the SQLSkills team that are in that output to figure what's going on?
The other thing to ask is what happened "over night"? For example, did someone run Index Maintenance (most people don't actually know what they're doing there and it causes super blocking and page splits the next day {heavy personal experience with that issue}). Did someone make a change to the server settings such a Threshold for Parallelism or MaxDOP or ??? Did someone deploy some new/changed code that actually has a major suck factor built in that can no longer use an index? Did someone add a column to an important table and cause it to wildly fragment? Did someone do a "shrink"? Did someone add a lot of data somewhere but not enough to update stats and maybe fragmented the hell out of an important table?
Have you checked the hardware to ensure you didn't have a CPU or Memory failure?
As for your graphic... what were the results when you ran that the day before? Ah... understood... you probably forgot to do this type of baselining every day and so you don't have such a thing or you'd have posted it with the one you did. (That's a hint for the future).
And, to be honest, since this is your very first post and, owing to the nature of the post and the total lack of any detail, seems like it could be a prelude to SPAM.
--Jeff Moden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply