Ali Shaik's Database Diary

IO Usage at the Database Level We can build out further on this information and present the data in a more usable format and provide exactly what we need vs. what the developer of the DMV/DMF may have foreseen it used for by writing our own query with it incorporated into it. WITH IO_Per_DB AS (SELECT DB_NAME(database_id) AS Db , CONVERT(DECIMAL(12,2), SUM(num_of_bytes_read + num_of_bytes_written) / 1024 / 1024) AS TotalMb FROM sys.dm_io_virtual_file_stats(NULL, NULL) dmivfs GROUP BY database_id) SELECT Db ,TotalMb ,CAST(TotalMb / SUM(TotalMb) OVER() * 100 AS DECIMAL(5,2)) AS [I/O] FROM IO_Per_DB ORDER BY [I/O] DESC; The above query now displays total MBPs and percentage of IO across entire SQL Server per database. If we need to few this in a more detailed perspective , i.e. from the data file view vs. the database, ensuring we identify prospective high IO areas that could become a bottleneck, we could use the following query: WITH IO_Per_DB_Per_File AS (SELECT DB_NAME(dmivfs.database_...

  Crucial for Performance and Responsiveness: Monitoring latches in real-time is essential for maintaining optimal performance and responsiveness in a SQL Server database system. Role of Latches: Latches, being lightweight synchronization primitives, play a pivotal role in safeguarding in-memory structures from concurrent access. Impact of Excessive Latch Contention: Excessive latch contention can lead to queuing and delays, significantly impacting the throughput and responsiveness of the system. Swift Detection of Bottlenecks: Vigilant observation of latch behaviors in real-time allows database administrators to swiftly detect potential bottlenecks. Preventing Severe Performance Issues: Addressing issues before they escalate into severe performance degradation is crucial for maintaining system health. Insights into Dynamic Workloads: Real-time monitoring not only identifies issues promptly but also provides valuable insights into dynamic workloads and interactions within the serve...

Introduction: In SQL Server, computed columns are a powerful feature that allows you to create virtual columns with values calculated on-the-fly based on other columns' data. While computed columns can simplify data retrieval and improve data consistency, they can also pose challenges when it comes to replication. This blog post explores the issues associated with computed columns in SQL Server replication and presents solutions to tackle them. Understanding Computed Columns: A computed column is essentially a virtual column within a table. It does not physically store its data in the table unless explicitly marked as "PERSISTED." Instead, a computed column derives its value based on an expression that can use data from other columns in the same table. Challenges in Replication: Replication in SQL Server involves copying data from one database (the publisher) to one or more destination databases (subscribers). However, when dealing with computed columns, replication can e...

Introduction: Replication is a powerful data distribution mechanism, but it's essential to understand how it interacts with your transaction log. While merge and snapshot replication have minimal impact on transaction logs, transactional replication can significantly affect their size. In this blog post, we'll explore how replication influences transaction logs and offer strategies to manage them effectively. Merge Replication and Snapshot Replication: Merge and snapshot replication don't directly impact the size of your transaction log. These replication methods focus on data synchronization and delivery without significant log-related implications. Transactional Replication: Transactional replication, on the other hand, can affect transaction log size. When a database includes one or more transactional publications, the log isn't truncated until all transactions relevant to these publications are delivered to the distribution database. This can result in transaction l...

Introduction: Snapshot generation in replication involves locking mechanisms that vary based on the replication method used. Let's unveil the types of locks encountered during snapshot generation and their implications in different replication scenarios. Snapshot Generation Locks: Snapshot Replication 📸: Snapshot replication employs exclusive locks that encompass the entire snapshot generation process. These locks ensure data consistency during the snapshot creation but can temporarily suspend other concurrent operations on the same data. It's like safeguarding a room while a thorough inspection is conducted. Transactional Replication 💼: In transactional replication, locks are momentarily acquired at the outset of snapshot generation and are swiftly released. This approach minimizes disruption to regular database activities, allowing them to continue almost immediately. Think of it as a brief checkpoint on a busy road. Merge Replication 🔄: Merge replication operates uniquely...

Replication is a crucial component in maintaining data consistency across distributed systems. However, there are times when data fails to reach its intended subscribers. In this blog post, we'll explore some common reasons why data may not be delivered to subscribers in a replication setup. Filtered Tables with No Changes: If your replication involves filtered tables, data will only be sent to subscribers if there are changes that match the filter criteria. Ensure that changes meeting the filter conditions are indeed present. Non-Functioning Agents: Replication relies on agents to transfer data between the Publisher and Subscribers. If one or more agents are not running or are encountering errors, data delivery will be disrupted. Check agent status and logs for any issues. Trigger-Based Deletions or ROLLBACK Statements: Triggers can be a double-edged sword in replication. Data deleted by a trigger or a ROLLBACK statement within a trigger can prevent data from being delivered as ex...

In the realm of SQL Server, database management is a complex art. One crucial aspect of this management is controlling when data is written from memory to disk through a process called checkpointing. While SQL Server offers automatic checkpoints, a more precise tool in your arsenal is the indirect checkpoint. Indirect Checkpoint in SQL Server Purpose: The primary aim of indirect checkpoints is to alleviate the I/O spikes often associated with traditional automatic checkpoints. By opting for indirect checkpoints, you gain better control and predictability over the checkpoint process. Database Configuration: Enabling indirect checkpoints for a specific database involves setting the TARGET_RECOVERY_TIME option at the database level. This setting defines the desired time interval (in seconds) between checkpoint events for that specific database. Predictable Checkpoints: Indirect checkpoints empower you to specify the frequency of checkpoints for each database. This level of granularity hel...