How to Find the Worst Performing Queries in SQL Server 2008

How to find the worst performing queries in SQL Server 2008?

top 10 worst queries based on...:

SELECT TOP 10
    total_worker_time/execution_count AS Avg_CPU_Time
        ,execution_count
        ,total_elapsed_time/execution_count as AVG_Run_Time
        ,(SELECT
              SUBSTRING(text,statement_start_offset/2,(CASE
                                                           WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), text)) * 2 
                                                           ELSE statement_end_offset 
                                                       END -statement_start_offset)/2
                       ) FROM sys.dm_exec_sql_text(sql_handle)
         ) AS query_text 
FROM sys.dm_exec_query_stats 

--pick your criteria

ORDER BY Avg_CPU_Time DESC
--ORDER BY AVG_Run_Time DESC
--ORDER BY execution_count DESC

Microsoft SQL Server Management Studio 2008 Find and Replace only searches through open queries

You mentioned you are are connecting to a database on a separate server. Do you have access to the .ssmssln solution file?

Otherwise, you can use this query to search stored procedure contents

c.f.: Search Text in Stored Procedure in SQL Server

SELECT name, definition
FROM sys.sql_modules m 
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition Like '%search string%';

MS SQL DMVs for UDF performance stats - how to find top 10 worst UDFs

Try this.

• changing sys.procedures to sys.sql_modules
• add further join to sys.objects
• filter by sys.objects.type IN ('IF', 'TF', 'FN')

The code above restricts to stored procs only

Note: there is no "sys.functions"

Fixing a slow running SQL query

This is one of my interview questions that I've given for years. Keep in mind that I do not use it as a yes/no, I use it to gauge how deep their SQL Server knowledge goes and whether they're server or code focused.

Your answer went towards how to find which query is running slow, and possibly examine server resource reasons as to why it's suddenly running slow. Based on your answer, I would start to label you as an operational DBA type. These are exactly the steps that an operational DBA performs when they get the call that the server is suddenly running slow. That's fine if that's what I'm interviewing for and that's what you're looking for. I might dig further into what your steps would be to resolve the issue once you find deadlocks for example, but I wouldn't expect people to be able to go very deep. If it's not a deadlock or blocking, better answers here would be to capture the execution plan and see if there are stale stats. It's also possible that parameter sniffing is going on, so a stored proc may need to be "recompiled". Those are the typical problems I see the DBA's running into. I don't interview for DBA's often so maybe other people have deeper questions here.

If the interview is for a developer job however, then I would expect the answer more to make an assumption that we've already located which query is running slowly, and that it's reproducible. I'll even go ahead and state as much if needed. The things that a developer has control over are different than what the operational DBA has control over, so I would expect the developer to start looking at the code.

People will often recommend looking at the execution plan at this point, and therefore recommend it as a good answer. I'll explain a little later why I don't necessarily agree that this is the best first step. If the interviewee does happen to mention the execution plan at this point however, my followup questions would be to ask what they're looking for on the execution plan. The most common answer would be to look for table scans instead of seeks, possibly showing signs of a missing index. The answers that show me more experience working with execution plans have to do with looking for steps with the highest percentage of the whole and/or looking for thick lines.

I find a lot of query tuning efforts go astray when starting with the execution plans and solutions get hacky because the people tuning the queries don't know what they want the execution plan to look like, just that they don't like the one they have. They'll then try to focus on the seemingly worst performing step, adding indexes, query hints, etc, when it may turn out that because of some other step, the entire execution plan is flipped upside down, and they're tuning the wrong piece. If, for example, you have three tables joined together on foreign keys, and the third table is missing an index, SQL Server may decide that the next best plan is to walk the tables in the opposite direction because primary key indexes exist there. The side effect may be that it looks like the first table is the one with the problem when really it's the third table.

The way I go about tuning a query, and therefore what I prefer to hear as an answer, is to look at the code and get a feel for what the code is trying to do and how I would expect the joins to flow. I start breaking up the query into pieces starting with the first table. Keep in mind that I'm using the term "first" here loosely, to represent the table that I want SQL Server to start in. That is not necessarily the first table listed. It is however typically the smallest table, especially with the "where" applied. I will then slowly add in the additional tables one by one to see if I can find where the query turns south. It's typically a missing index, no sargability, too low of cardinality, or stale statistics. If you as the interviewee use those exact terms in context, you're going to ace this question no matter who is interviewing you.

Also, once you have an expectation of how you want the joins to flow, now is a good time to compare your expectations with the actual execution plan. This is how you can tell if a plan has flipped on you.

If I was answering the question, or tuning an actual query, I would also add that I like to get row counts on the tables and to look at the selectivity of all columns in the joins and "where" clauses. I also like to actually look at the data. Sometimes problems just aren't obvious from the code but become obvious when you see some of the data.

Very Slow Performance on SUM SQL Server 2008

It's not a good idea to join with 5-million rows table. If you take a look at the execution plan, I bet you will find that join between Flows and Likes_Dislikes is hashjoin - which is the worst possible case.

First step in optimizing this query is to detect which particular join adds time to execution. Supposedly, there is a part of this query which executes in acceptable time (e.g. 1-2 seconds). Everything else is the problem. And problems are solved by denormalizing tables. Instead of joining with likes/dislikes table, add likes/dislikes score column to Flow table. Whenever you insert to likes/dislikes, immediately update Flow record. By doing that, you will not need the heavy join in this query.

Keep in mind that normalization is a well defined theory, but that practice often goes against it. Striking the right balance between normalized tables and redundancy makes the great software.

Check Procedures Performance

The following article provides instruction on how to write a T-SQL query and also provides a stored procedure for identifying the poorest performing SQL Server queries.

http://www.databasejournal.com/features/mssql/article.php/3802936/Finding-the-Worst-Performing-T-SQL-Statements-on-an-Instance.htm

This solution uses the Dynamic Management Views(DMV's) which are available only in SQL Server 2005 onwards.

I hope this answers your query.

Join statement performance in SQL

Quick answer: No, there is no difference in performance.

This is a rather commonly asked question and is explained well here: INNER JOIN ON vs WHERE clause

The query optimization engine in SQL server will automatically convert both of these queries into the same approach for retrieving results.

The biggest difference in performance for your query would be the order of your joins and how much data was filtered with each new join. The most selective join statements (which filter out the most results) should come first (as much as possible depending on the tables you're joining).

How to find slowest queries

In SQL 2005 you can use management views to find slow running queries. A good script i found a while ago on SQL server performance will help get you started; it lists data with the slowest performing first.

SELECT  creation_time 
        ,last_execution_time
        ,total_physical_reads
        ,total_logical_reads 
        ,total_logical_writes
        , execution_count
        , total_worker_time
        , total_elapsed_time
        , total_elapsed_time / execution_count avg_elapsed_time
        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
         ((CASE statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;

---

Related Topics