Lock Escalation, and Why You Shouldn’t Disable It!
Lock escalation affects every SQL Server database. Understanding when and why SQL Server escalates row-level locks to table locks and what you can do about it is essential knowledge for any DBA managing concurrent workloads.
Should I use (max) size for all my nvarchar/varchar columns?
Should you use VARCHAR(MAX) for all your string columns? No. Here’s why MAX costs you indexing, memory grants, and storage efficiency with demos you can run yourself.
Identifying the Physical Location of a Row
Occasionally I’ve needed to determine the physical location of a row stored in SQL Server. The code in this post uses the undocumented feature, %%PHYSLOC%%, which returns a binary representation in hexadecimal of the location…
Deleting a Column: Meta-data-only operation
Microsoft’s SQL Server development team is constantly working to improve performance. One important performance optimization methodology that has been eminent in SQL Server for a long time has been making as many meta-data-only operations as…
suspect_pages
When SQL Server detects corruption in a database, it reports that corruption immediately to the client who requested the data. But did you know SQL Server also stores the details about which pages have experienced…
Msg 824, Level 24, State 2, SQL Server detected a logical consistency-based I/O error: invalid protection option
Data in SQL Server is stored in 8KB pages, both in memory and on-disk. Corruption in those pages are detected using one of several different methods, configurable at the database level. The best option, and…