Performance Tuning in SQL Server: Top 4 Ways to Find Slow Queries - Part4

SQL performance tuning is a never ending battle. I’m not a DBA, but I am a developer who has pretended to be one for 15 years.  I have worked with SQL Server databases with terrabytes of RAM all the way down to Stackify’s massive fleet of little SQL Azure databases. I have seen a little bit of everything over the years.

In this article, I’m going to provide some tips for how developers can find slow SQL queries and do performance tuning in SQL Server.

4 Ways to Find Slow SQL Queries   

4. SQL Azure Query Performance Insights

I am going to assume that SQL Azure’s performance reporting is built on top of Extended Events. Within the Azure Portal you can get access to a wide array of performance reporting and optimization tips that are very helpful.

Note: These reporting capabilities are only available for databases hosted on SQL Azure.

In the screenshot below you can see how SQL Azure makes it easy to use your queries that use the most CPU, Data IO, and Log IO. It is has some great basic reporting built into it.

You can also select an individual query and get more details to help with SQL performance tuning.

Pros: Great basic reporting.

Cons: Only works on Azure. No reporting across multiple databases.

Summary

Next time you need to do some performance tuning with SQL Server, you will have a few options at your disposal to consider. Odds are, you will use more than one of these tools depending on what you are trying to accomplish.

Performance Tuning in SQL Server: Top 4 Ways to Find Slow Queries - Part3

SQL performance tuning is a never ending battle. I’m not a DBA, but I am a developer who has pretended to be one for 15 years.  I have worked with SQL Server databases with terrabytes of RAM all the way down to Stackify’s massive fleet of little SQL Azure databases. I have seen a little bit of everything over the years.

In this article, I’m going to provide some tips for how developers can find slow SQL queries and do performance tuning in SQL Server.

4 Ways to Find Slow SQL Queries  

3. SQL Server Extended Events

The SQL Profiler has been replaced by SQL Server Extended Events. This is sure to anger a lot of people but I can understand why Microsoft is doing it.

Extended Events works via Event Tracing (ETW). This has been the common way for all Microsoft related technologies to expose diagnostic data.

ETW provides much more flexibility. As a developer, I could easily tap into ETW events from SQL Server to collect data for custom uses. That is really cool and really powerful.

MORE: Introducing SQL Server Extended Events

Pros: Easier to enable and leave running. Easier to develop custom solutions with.

Cons: Since it is fairly new, most people may not be aware of it.

  

Performance Tuning in SQL Server: Top 4 Ways to Find Slow Queries - Part2

SQL performance tuning is a never ending battle. I’m not a DBA, but I am a developer who has pretended to be one for 15 years.  I have worked with SQL Server databases with terrabytes of RAM all the way down to Stackify’s massive fleet of little SQL Azure databases. I have seen a little bit of everything over the years.

In this article, I’m going to provide some tips for how developers can find slow SQL queries and do performance tuning in SQL Server.

4 Ways to Find Slow SQL Queries  

2.SQL Server Profiler (DEPRECATED!)

The SQL Server Profiler has been around for a very long time. It is very useful if you are trying to see in real time what SQL queries are being executed against your database.

NOTE: Microsoft has announced that SQL Server Profiler is being deprecated!

SQL Profiler captures very detailed events about your interaction with SQL Server.

·         Login connections, disconnections, and failures

·         SELECT, INSERT, UPDATE, and DELETE statements

·         RPC batch status calls

·         Start and end of stored procedures

·         Start and end of statements within a stored procedure

·         Staart and end of a SQL batch

·         Errors written to the SQL Server error log

·         A lock acquired or released on a database object

·         An opened cursor

·         Security permission checks

SQL SERVER PROFILER

Tutorial: SQL Server Profiler

Pros: Very detailed data available.

Cons: You have to manually turn it on. This forces you to recreate a scenario you are trying to capture. It is eventually going away in favor of Extended Events.

Performance Tuning in SQL Server: Top 4 Ways to Find Slow Queries - Part1

SQL performance tuning is a never ending battle. I’m not a DBA, but I am a developer who has pretended to be one for 15 years.  I have worked with SQL Server databases with terrabytes of RAM all the way down to Stackify’s massive fleet of little SQL Azure databases. I have seen a little bit of everything over the years.

In this article, I’m going to provide some tips for how developers can find slow SQL queries and do performance tuning in SQL Server.

4 Ways to Find Slow SQL Queries  

1. Find Slow Queries With SQL DMVs

One of the great features of SQL Server is all of the dynamic management views (DMVs) that are built into it. There are dozens of them and they can provide a wealth of information about a wide range of topics.

There are several DMVs that provide data about query stats, execution plans, recent queries and much more. These can be used together to provide some amazing insights.

For example, this query below can be used to find the queries that use the most reads, writes, worker time (CPU), etc.

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.TEXT)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1),

qs.execution_count,

qs.total_logical_reads, qs.last_logical_reads,

qs.total_logical_writes, qs.last_logical_writes,

qs.total_worker_time,

qs.last_worker_time,

qs.total_elapsed_time/1000000 total_elapsed_time_in_S,

qs.last_elapsed_time/1000000 last_elapsed_time_in_S,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.total_logical_reads DESC -- logical reads

-- ORDER BY qs.total_logical_writes DESC -- logical writes

-- ORDER BY qs.total_worker_time DESC -- CPU time

SELECT DISTINCT TOP 10 t.TEXT QueryName,

s.execution_count AS ExecutionCount,

s.max_elapsed_time AS nMaxElapsedTime,

ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,

s.creation_time ASnLogCreatedOn,

ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec

FROM sys.dm_exec_query_stats s

CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t

ORDER BY

s.max_elapsed_time DESC

GO

The result of the query will look something like this below. The image below is from a marketing app I made. You can see that one particular query (the top one) takes up all the resources.

By looking at this, I can copy that SQL query and see if there is some way to improve it, add an index, etc.