My question just got published at DBA Quiz 2011. This question is inspired from a real life incident, which occurred to me a few years ago. That time, I was a DBA myself and then one fine day, everything went south. When we checked the log, all the logs were fine till few minutes before our server started to face the issue. After working for long hours, we fixed the issue. Our CTO had called us to analyze the situation. Instead of blaming anyone, he adorned an extremely positive attitude. He suggested that we all go out and come back with three top best solutions. We all gathered after a week and looked at each other’s solution. We combined that and put them in order for our later reference.
Well, today we are going to do the same task. SQL Server MVP Jacob Sebastian has come up with an SQL Server DBA Quiz where my question has just got published.
A query that was working fine every time suddenly became slow. There has been no change in the workload; no new development changes have been deployed, and there have been no other configuration changes in the system. Everybody runs to the DBA asking for resolution. If you were the DBA, what will be the first thing you would do and why? List the three most important priorities.
There are various awards for the winner, but here is a small effort from me. Irrespective of the whoever wins the SQL Quiz at the end, I will give a copy of my wait stats print book to one person who has attempted to answer the question I have asked. If you are not sure on how to answer this question, here is a quick hint.
Hint: SQL Programming Joes 2 Pros (vol 4) [Amazon] | [Flipkart]- Chapter 13 has few interesting hints.
Please participate in the DBA Quiz Here.
Reference: Pinal Dave (https://blog.sqlauthority.com)
10 Comments. Leave new
Look at the tables involved in the query and see if the structure in terms of indexes have changes.
Check the data that was populated in the table for that particular day, was there something in the data which caused the query to slowdown.
IN case the query has left outer joins, the performance can be slow if there is something in the data that affects the joins.
Ramdas
Hi Pinal,
I can say, the following are the three most important priorities to look at when your SQL Server is perforking slowly.
Blocking, Old Statistics, CPU (Historically Low)
Blocking
Blocking is caused by contention of resources. If records being updated are also being shown to a user before the update finishes, then inaccurate information is being displayed. The most common reason for blocking is the lack of indexing or queries not written to utilize existing indexes. They mostly occur when update or delete statements are performing a table scan, while an incompatible lock like a select statement tries to read the same records.
Old Statistics
If old statistics is your problem, you will likely experience this as a gradual decline of SQL Server slowing down over many days or weeks. Out of date statistics cause inaccurate execution plans. Bad execution plans are many times a silent killer because the system can run severely slow. Turn on “auto update statistics” for the database.
CPU – (Historically Low)
When the CPU on a box that has historically been low becomes pegged, there is a lot of hope because this is usually the easiest fix. It is likely you have a run away query. The first thing you want to do is run sp_who2 (or sp_who3) and look for connections that have a SPID > 50 with abnormally high CPU time. The CPU time shown on sp_who2 is cumulative for the entire connection.
Gopalakrishnan Arthanarisamy
Unisys, Bangalore, India.
Get the query from profiler and check the execution plan.
1.There might be problem with the parameter sniffing. Recompile the query.
2.Create the Indexes if required.
Dear Sir
-first execute query in database to check the response time and total time (to check the problem is with our query or some other problem like network)
-i will check the Indexes and statistics based on table and query is already created or not.
-If indexes and statistics are existing then i will check the % of fragmentation.
if the % of fragmentation existing between 20% – to 40% then i will Reorganize Index and if % of Fragmentation is grater than 40% then i will Rebuilt indexes.
and Rebuilt statistics too.
-if indexes and statistics are not existing then i will create index and statistics to improve the performance.
Data modification operations like Insert, Update and Delete statements executes, then fragmentation may occur and slow down the performance.
Arvind Awasthi
Brightsun Travel
Gurgaon
Hye Pinal Dave,
All was well few moments before all went wrong – Reasons and Resolutions
The reasons are as below.
query plan might have changed due to stale statistics
missing indexes (accidental drop of an index)
passing an uncommon parameter value leading to an inefficient query plan for other parameter values.
blocking caused by lock escalation.
https://techcommunity.microsoft.com/t5/DataCAT/bg-p/DataCAT
Checklist for Analyzing Slow-Running Queries
============================================
There are a number of common reasons for slow-running queries and updates:
Slow network communication.
Inadequate memory in the server computer, or not enough memory available for SQL Server.
Lack of useful statistics
Lack of useful indexes.
Lack of useful indexed views.
Lack of useful data striping.
Lack of useful partitioning.
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms177500(v=sql.105)
Mitesh Modi
Hi,
1 There must be huge data modified/inserted in the table , if so need to defragement index by checking dm index statistics.
2 if not so then there can be other query load on server which may be causing locking on the same resource
3 Need to check/review query plan may be source data affecting query performance.
Sachin Mahtole
1. Begin Transaction but not committed
2. Duplicate Index
3. Lock
The first three things I’d check in order would be:
1. Resources – CPU load and table locks. – make sure the resources necessary are available.
2. Query Execution – Check all the tables involved for index changes/update the indexes. Analyze the query execution plan.
3. Hardware – Rule out disk fault. Corruption in a sector of the drive that contains the table data involved with the query will drag performance down, but only for queries using that table.
Hi pinal,
Please give comment.
All we commented is correct or has different issue linked which no one of us noticed.
Give you views on it. This will be helpful for us.
Sachin
Hi Pinal… please put your final comments on this. This is really a helpful discussion. Awaiting for your final comments