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.