Execution plan changes in SQL server

04/07/2024

Execution Plan Changes in SQL Server: Reasons and Solutions

Executions plan—complex maps charted by the SQL Server optimizer, dictate the most efficient route for data retrieval. However, the plans are not static. They morph and adapt, influenced by a host of factors ranging from data volume changes to system upgrades. Each of the execution plan changes in SQL Server has the potential to sway performance dramatically.

Understanding the reasons behind these shifts, and more importantly, how to effectively manage them translates to maintaining optimal performance in SQL Server environments.

Overcoming Execution Plan Changes

20/06/2024

From Analysis to Action: Overcoming Execution Plan Changes

Execution plans choose the way in which SQL queries are executed by the database engine. As such, they are a difference between a smoothly running database and one that stumbles under load. Sometimes, an execution plan changes, for various reasons—often silently and without warning—and the far-reaching fallout can be huge.

The most immediate impact seen as the execution plan changes is a decline in query performance. A query that once ran smoothly in milliseconds might, due to an altered plan, start consuming seconds. This increase in query time can cascade into longer load times for applications, frustrated end-users, and severe financial repercussions in high-stakes environments such as financial trading platforms or real-time data services.

The Most Important Database Performance Metrics

06/06/2024

The Most Important Database Performance Metrics

If no one sees the database issues, do they really happen?  – Contrary to the popular philosophical dilemma, this question can be answered quite easily – Absolutely yes. Unseen or unmonitored problems in database systems do occur and can indeed cause significant disruptions. It doesn’t matter if they’re not immediately apparent. Without monitoring of the database performance metrics and proactive management, these underlying issues can compound, leading to decreased performance, potential data loss, and even system failures that by their nature demand urgent and often extensive interventions to rectify.

The cost of ignoring database performance metrics is just too great.

Locks by Application Issues

23/05/2024

The Story of Locks by Application Issues

On the 20th of April, query load times usually ranged from 12 to 15 seconds per snapshot, but there was a notable spike exceeding 2,000 seconds during certain periods. This anomaly suggested a deeper issue, prompting a detailed investigation. By examining the correlation between locks and application issues, executions, disk reads, and buffer writes, a significant overlap was found between increased query durations and intensified locking issues. Accessing the lock history tab provided detailed data on each lock event, including the time, duration, and specific database elements affected.

speed up a database read query

02/05/2024

How can you speed up database read queries

Before getting into the technicalities of speeding up your database read queries, the first step is always to have a clear understanding of what exactly you need. This might sound straightforward, but inefficient queries often begin with uncertainty about the data requirements. Knowing exactly what information is necessary for your application or analysis not only helps in crafting precise queries but also prevents the database from retrieving extraneous information, which can significantly slow down performance.