Have you ever sat staring at a spinning wheel in your application, knowing deep down that a SQL Server query is currently eating your server alive? We’ve all been there. It’s that sinking feeling when the CPU is pegged at 100%, users are starting to call the help desk, and you need answers right now. Finding the “slow” query is often the hardest part of performance tuning, but once you identify the culprit, the path to a solution becomes much clearer.
Since I started working with SQL Server back in 1990, I’ve seen just about every type of performance bottleneck imaginable. At Stedman Solutions, our team: with well over 100 years of combined experience: spends a huge portion of our time helping clients hunt down these performance killers. Whether it’s a missing index, a poorly written JOIN, or a massive table scan, the first step is always the same: visibility.
Today, let’s explore five proven ways to find those long-running queries so you can stop guessing and start fixing.
1. Using sys.dm_exec_requests for Real-Time Monitoring
When the server is screaming and you need to know exactly what is happening at this very microsecond, sys.dm_exec_requests is your best friend. Think of this Dynamic Management View (DMV) as the “police scanner” for your SQL Server. It shows you every request that is currently executing.
Unlike other tools that look at historical data, this view gives you the raw, immediate truth. You can see the session_id, the start_time, and most importantly, the total_elapsed_time. If a query has been running for 10 minutes, it will show up here with a high elapsed time value.
We frequently use a query that joins sys.dm_exec_requests with sys.dm_exec_sql_text to pull the actual T-SQL code being run. Why does this matter? Because knowing that Session 54 is slow doesn’t help you unless you can see that Session 54 is running a massive report against the Sales table.
If you are interested in diving deeper into how to analyze these results and understand execution plans, you should check out our SQL Performance Tuning Class. Using the link here will even give you an 11% discount to get started.
2. Query Store for Historical Analysis
While real-time monitoring is great for “fires,” what happens if the server was slow at 2:00 AM while you were sleeping? That’s where the Query Store comes in. Introduced in SQL Server 2016, the Query Store is essentially the “flight data recorder” for your database. It captures a history of queries, execution plans, and runtime statistics.
The magic of Query Store is that it survives a server restart. Traditional DMVs are cleared when the SQL Service restarts or the server reboots, but Query Store data is persisted on disk within your database. This allows us to look back over the last week or month to see which queries are trending toward longer runtimes.
One of the most powerful features here is the “Regressed Queries” report. If a query used to run in 2 seconds but suddenly started taking 20 seconds yesterday, Query Store will flag it. Usually, this is due to a “plan regression,” where SQL Server chose a less efficient way to get the data. With Query Store, you can often fix this with a single click by forcing the older, faster execution plan.
3. Extended Events for Specific Thresholds
For a long time, DBAs relied on SQL Server Profiler to catch slow queries. However, Profiler is deprecated and, frankly, it’s quite heavy on server resources. These days, we use Extended Events (XEvents). Extended Events are a lightweight performance monitoring system that allows you to collect the data you need without dragging down the server’s performance.
The best way to use XEvents for finding long-running queries is to set a threshold. For example, you can create a session that only captures queries that take longer than five seconds to execute (the duration filter). This way, you aren’t overwhelmed by thousands of sub-second queries that are running perfectly fine. You only get a log of the “heavy hitters.”
We often set these up for clients to run in the background. It provides a clean, filtered list of everything that crossed the “unacceptable” line. If you’re struggling to set these up or need a more comprehensive look at your environment, our SQL Performance Tuning services can help you build a customized monitoring strategy.
4. Database Health Monitor for Visual Diagnostics
Sometimes, looking at rows of data in a grid isn’t enough to see the big picture. You need to see the health of your server at a glance. That is exactly why I created the Database Health Monitor. It’s a tool designed to give you visual feedback on what is happening inside your SQL Server.
One of the key features of Database Health Monitor is the “Real-Time Workload” and “Long Running Queries” reporting. It takes the complex data from various DMVs and presents it in a way that is easy to digest. You can see which databases are under the most pressure and which specific queries are causing the most pain.
The best part? It’s a tool built by DBAs, for DBAs. We use it internally every single day to manage our clients’ environments. You can actually download Database Health Monitor for free and start using it on your own servers today. It’s a great way to jumpstart your performance tuning journey without having to write dozens of custom scripts from scratch.
5. Plan Cache Analysis for Heavy Hitters
Finally, we have Plan Cache analysis. Every time you run a query, SQL Server (usually) stores the execution plan in memory so it doesn’t have to figure out how to run it again the next time. Along with those plans, SQL Server keeps track of how many times the query has run and how much total time it has spent executing.
By querying sys.dm_exec_query_stats, we can find queries that might not be the “longest” in a single run, but are the “most expensive” over time. For example, a query that takes 10 seconds and runs once a day isn’t nearly as big of a problem as a query that takes 1 second but runs 100,000 times an hour.
Analyzing the plan cache allows us to find these “death by a thousand cuts” scenarios. We look for high aggregate CPU time or high logical reads. If a query is responsible for 40% of your total server IO, that is the first one you should optimize, regardless of how “fast” it feels to a single user.
Putting It All Together
Performance tuning isn’t a one-time event; it’s an ongoing process of monitoring, identifying, and refining. By combining real-time tools like sys.dm_exec_requests with historical data from the Query Store and visual aids like Database Health Monitor, you gain a 360-degree view of your SQL Server’s health.
I’ve seen servers that were minutes away from a complete crash be totally transformed just by identifying the top two or three long-running queries and adding the correct indexes. It doesn’t always take a massive hardware upgrade to fix a slow server: often, it just takes a bit of detective work.
At Stedman Solutions, we specialize in this kind of detective work. If you’re tired of fighting with slow performance or if your team is stretched too thin to keep up with database maintenance, we offer Managed Services where we take the burden off your shoulders. We monitor your servers 24/7, find the slow queries before they become outages, and keep everything running smoothly.
If you’re not sure where to start, or if you just want a second pair of eyes on a particularly stubborn performance issue, I’d love to chat with you. We offer a free 30-minute consultation where we can discuss your specific challenges and see how we can help.
Don’t let long-running queries bring your business to a halt. Use the tools available to you, keep learning, and remember that we are always here to help you get the most out of your SQL Server environment. Happy tuning!