Provide some tips for how developers can find slow SQL queries and do performance tuning in SQL Server.
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,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.
((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
By looking at this, I can copy that SQL query and see if there is some way to improve it, add an index, etc.
- Pros: Always available basic rollup statistics.
- Cons: Doesn’t tell you what is calling the queries. Can’t visualize when the queries are being called over time.
Query Reporting via APM Solutions
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
0 comments: