This is how effective SQL query monitoring can prevent blockages

In a complex SQL Server ecosystem, performance issues can sometimes resemble detective work, especially when tracking down the elusive culprits of system crashes. This time we will explore how inefficient queries (and poorly conducted query monitoring) can stop operations. We will also identify the “how” and “why” behind the stop and show you how effective monitoring of SQL queries can prevent blockages.

From analyzing load trends to analyzing crash histories and refining execution plans, we make sure that no stone — or rather, no block of data — remains unturned in our quest to keep SQL Server moving fast like a sports car, not like a jumbo mule.

Load trends and CPU usage

Load Trends Screen DBPLUS PERFORMANCE MONITOR is a place that offers something for anyone who wants to get to the bottom of SQL queries. This is a thorough look at the performance statistics for SQL Server instance queries. Grouping statistics by hours from April 4 to 11, we initially focus on the CPU usage of the queries that run. The reason for the drama that our client witnessed was significant blocking incidents:

• On April 5, the processor decided to change its humor, causing confusion with significant blocking incidents.

• The records of April 7 and 11 are equally interesting cases. The second of those days witnessed resources locked in for a staggering 1,272 seconds.

Block history and session analysis

Delving into the heart of the matter and monitoring SQL queries, we visited the 'block history' tab. It serves as our time machine, offering insight into skirmishes with blockades. Here, each type of blockage from individual days gets its moment under the microscope. A vivid interactive chart shows the ups and downs of database drama. It shows us the number of blocking and waiting sessions along with their duration.

A simple click on the chart reveals the culprits of each of the blockages. Separates initiators (blocking sessions) from victims (waiting sessions).

Interestingly, our investigation revealed a notoriousp perpetrator. The same session, with different numbers, was caught red-handed, consistently executing the query that triggered the lockdowns.

Query statistics and execution plans

Visiting the SQL details screen, we can follow the antics of the query from April 11 to 20, discovering that it is a performance chameleon. On April 11, it was decided to try the execution plan, which, to put it mildly, was in no hurry. His execution, each time continued. This slow pace was a pretty good show compared to his later performances.

On brighter days, our query chose a faster execution plan. It flew through the data at a rate five times faster. This change of pace not only shows that the query can change its bars. It also highlights the dramatic impact of choosing the right plan.

Optimize performance with strategic indexing

The comparison of the two execution plans highlighted the way forward: adding an index to the Product ID column. This fix proved to be a hit, increasing query speed and removing those pesky blocking issues. This was a classic case of strategic indexing flexing muscles to increase SQL Server performance.

This episode highlights the importance of vigilant monitoring and timely fixes — hallmarks of DB Plus Performance Monitor. Such tools are essential to detect and fix performance bottlenecks, ensuring that SQL Server runs like a well-oiled machine.

https://www.youtube.com/watch?v=Ioo0RoEPttE&t=1s