#database performance

Introduction As a SQL Server DBA, one of the biggest challenges I’ve faced is dealing with rapidly growing history tables. I’ve seen them balloon from a few gigabytes to terabytes in size, causing all sorts of performance headaches. But at the same time, the application teams are often hesitant to archive old data due to potential requests from other teams who may need it. In this article, I’ll…

Learn when to use multiple transaction log files per SQL Server database and the pros and cons.

Are you wondering whether it’s a good idea to have more than one transaction log file per database in SQL Server? In this article, we’ll explore the pros and cons of using multiple log files and explain when it can make sense. We’ll also show you how to remove extra log files if needed. By the end, you’ll have a clear understanding of SQL Server transaction log best practices. What are…

For a high activity table, is it worth updating stats at a higher percentage just for auto-update to resample at a lower rate within an hour?

Introduction As a SQL Server DBA, I’ve often wondered about the impact of Query Store size on my databases. It’s a common concern, especially when storage isn’t an issue. In this article, we’ll dive into the world of Query Store and explore whether its size should be a cause for concern. Join me on this journey as we uncover the truth behind Query Store size and learn how to manage it…

Introduction Have you noticed your SQL Server database running more slowly lately? One potential cause could be fragmented or inefficient indexes. In this article, we’ll explore when it makes sense to do a full rebuild of all your indexes to improve database performance. You’ll learn how to identify index fragmentation, the pros and cons of rebuilding indexes, and best practices to follow. Let’s…

In the world of database management, efficiency and smooth operation are paramount. Particularly with SQL Server, one challenge that often arises is row lock contention. This issue can severely impact the performance of your database, leading to slower response times and, in severe cases, deadlocks. However, with the right strategies and understanding, overcoming this hurdle is entirely within…

Determining which applications are writing to a given table in SQL Server can be approached in several ways, depending on your access level, the tools you have at your disposal, and whether you need to monitor this activity in real-time or over a period. Here are some methods to consider: 1. SQL Server Profiler / Extended Events SQL Server Profiler and Extended Events are tools for monitoring…

Imagine you’re a detective in the world of technology. Your mission? To clear the name of an often-misunderstood suspect: the database. It’s a scenario we’ve seen time and again—the app starts dragging its feet, and fingers are pointed at the database, claiming it’s the culprit behind the sluggish performance. But you know better. You understand that without hard evidence, assumptions are just…