If you’ve ever had to troubleshoot SQL Server performance issues, you’ll have come across SQL Server tools, such as Profiler, Extended Events (EE), and Query Store.
Each tool has its strengths, limitations, and use cases. By choosing the right tool for your scenario, you’ll get to the root of the problem more quickly.
SQL Server Tool #1 – SQL Server Profiler
SQL Server Profiler is the most reliable and go-to-tool for many DBAs, it’s been around FOREVER, and helps you trace SQL Server activity in real time.
Pros
Real-time monitoring
Profiler helps you see what’s happening in real time. You can watch queries, logins, errors and other events as they happen, which is useful if you’re trying to reproduce an issue or trace a user session.
Legacy systems
Profiler works with versions going back to SQL Server 2000, so it’s the go-to tool for legacy versions.
Troubleshooting
It’s useful if you’re working in a dev or test environment as you trace a problem quickly.
Cons
High overhead
Running Profiler on a busy production server can slow things down, as it consumes significant CPU and memory.
Deprecated
Microsoft has been trying to get us away from Profiler and on to EE for years.
Limited filtering
The filtering is limited to basic criteria like database name, application name, login name etc, and is not as advanced as EE. This can result in capturing more data that you’d ideally like to, impacting performance.
SQL Server Tool #2 - Extended Events (EE)
EE is Microsoft’s answer to Profiler’s limitations. As a SQL Server tool, it’s more efficient and has increased flexibility.
Pros
Highly customisable
You can easily define events to capture, apply advanced filters, and choose how the data is stored.
Scalable
EE can handle large volumes of data without degrading performance, making it a suitable SQL Server tool for continuous monitoring in production.
Integrations with SSMS
SQL Server Management Studio includes tools to create, manage, and view EE sessions, including live data views and templates.
Efficiency
Many popular events are lightweight (e.g. sql_batch_starting, rpc_completed) so in this regard, EE has minimal impact on system performance, even in high-throughput environments.
Cons
Steep learning curve
EE flexibility comes with the cost of a more complex setup process with a steeper learning curve.
Monitoring certain events can be very CPU/memory intensive. It’s crucial that the DBA understands this to avoid causing performance issues.
Less intuitive UI
The interface isn’t as user-friendly as Profiler, especially for DBAs that are using it for the first time.
Requires newer SQL Server versions
Some advanced EE features are only available in SQL Server 2012 and later.
SQL Server Tool #3 - Query store
Query Store tracks query performance over time, and even allows you to force a specific query plan if one of your queries starts misbehaving.
Pros
Historical performance tracking
Query Store automatically captures query execution plans and runtime statistics over time, allowing you to analyse trends.
Built in reports
SSMS includes visual reports for identifying top resource-consuming queries, regressed plans, and plan changes.
Low overhead
Query Store is designed to run continuously with minimal impact on performance, especially when configured properly.
Cons
Not real time
Data is collected and aggregated periodically, so it’s not suitable for immediate troubleshooting or live monitoring.
Requires SQL Server 2016 or later
Query Store isn’t available in older versions, so it’s limited for use in legacy environments.
Significant storage
If not managed, Query Store can grow large, especially in environments with frequent query changes or high query volume.
Let's recap
Still unsure of which SQL Server tool to use for which scenario?
Each SQL Server tool has its place, you just need to choose the right one for the job. If you’re managing SQL Server performance, all these are essential SQL Server tools that you can use together to catch issues early, fix them fast, and keep things running.
Need help managing your SQL Server databases? Head to our database services, or feel free to contact us below.
