Archive Data by Date

Tables with a large amount of data can become unwieldy. As a result, it’s not uncommon to archive data by date into an archive table that contains older rows we care about, but don’t need…

Memory Consumption by Object

SQL Server caches object data in memory in the buffer pool. Understanding memory consumption by object can be crucial for performance. For instance, you may have a large logging table consuming 90% of the buffer…

dm_db_database_page_allocations

sys.dm_db_database_page_allocations is an undocumented SQL Server T-SQL Dynamic Management Function. This DMF provides details about allocated pages, allocation units, and allocation extents. The function definition in SQL Server 2016 is:

The function definition tells…

Using Dynamic SQL inside a Stored Procedure

Preventing direct access to database tables for users is a widely considered “best practice” for many DBAs. The pattern typically looks like User -> Stored Procedure -> Tables. Since the stored procedure owner has access…

Configure a SQL Server Agent Operator

SQL Server Agent operators are used by SQL Server Agent to send notifications about events as they happen. Typically, this would include notifications that a SQL Server Agent Job failed during execution. You might also…

How to Corrupt a Database

Microsoft goes to great lengths to ensure data integrity remains its paramount concern with SQL Server data. After all, ACID compliance doesn’t mean much if the underlying data is corrupt. Having said that, there are…