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.
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.