First Steps to "slow" database analysis

Question

First Steps to "slow" database analysis

stevec883

SSC-Addicted

Points: 439
More actions
June 18, 2020 at 8:14 pm

#3764256

I want to investigate a possible "slow" running database. This is just a report I received and may not hold any validity. Just the same, I want to start by looking at long running queries and missing indexes (if they exists). I realize that this can be a rabbit hole of a topic, but just looking for some initial stages. I am thinking about installing B. Ozar's scripts for analysis, but I am the new DBA and not sure I want to install anything on production at this point. Are there any built in tools to begin investigating indexes and/or slow queries for a particular database, or are the scripts a better way to start? And also, what should I look for to begin?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

If you don't know where to begin, may I suggest starting with Wait Statistics to figure out what types of bottlenecks your system has:

https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

Now looking at those numbers only gives you an overall view cumulative to whenever SQL Server last started. But once you get a feel for which resources are bottlenecks, then you can start looking at what is happening more detailed.

I'd still recommend the Blitz scripts from Brent Ozar. Here's a tip, I'd put those into your own personal database on the production instance, separate from the system databases and separate from your production database. And check out the videos he has on how to use them:

https://www.brentozar.com/archive/2016/09/learn-use-sp_blitz-sp_blitzcache-sp_blitzfirst-sp_blitzindex-tutorial-videos/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Glenn Berry's suite of diagnostic queries is a great place to start:

https://glennsqlperformance.com/2020/02/04/sql-server-diagnostic-information-queries-for-february-2020/

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

stevec883 SSC-Addicted Points: 439 More actions · Answer 3

How can I track these down to see if these are normal locks or causing performance issues?

I'm no

Jeffrey Williams SSC Guru Points: 90245 More actions · Answer 4

One of the first things I would do is try to isolate where the report of 'slow' database is coming from.

Is it slow in the application, if so - in a particular module of the application?
Can it be reproduced by another user?
Is it on a web farm - if so, is it slow across all web servers?
Is it from only one location - or multiple locations? Might indicate a problem at a site or building.
If not in the application - is it a direct connection to SSMS? If so, can the user provide the queries they are running where they see this slowness?
Is it something else? An interface engine, integration services, reporting services?

Once you have an idea of where the issue is occurring - then you can start looking at the system to see if there is any correlation. For example, if the users report a 'slow' database and the application uses a web farm - but it is only slow from one web server - it would seem to indicate a problem with that web server. If it is from all web servers it might indicate a problem with SQL Server - so check from SSMS and see if you can reproduce the slowness. If nothing shows up in SQL Server - then it might be the load balancer or the network.

Maybe the slowness only occurs at a certain time of day - if so, monitor for other processes running at that time that could cause performance issues.

What I have found is that when performance related issues are reported to the application support team - they often assume it is the database first thing without looking at other possibilities. Unless this is an ongoing issue that has already been isolated as database performance - it often isn't the database causing the problem.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

stevec883 SSC-Addicted Points: 439 More actions · Answer 5

Thanks Jeffrey, I realize the app slowness probably has nothing to do with the database. I'm just trying to gain insight on how to dig into the issues the database is (or isn't) having and how to determine that. I have been a sys admin for a long time and know how to break down the big picture, but don't yet know how to break it down at exclusively the database level.

Evgeny Garaev SSCertifiable Points: 7422 More actions · Answer 6

If it is SQL 2016+ you can enable the QueryStore feature and check the reports it provides.