Tessera

Copilot Code Review and the 4,000 Character Limit Here is a fun way to lose an afternoon: carefully write coding standards into your repository's .github/copilot-instructions.md, request a Copilot code review on a pull request that violates several of them, and watch the review come back with…

Copilot Code Review and the 4,000 Character Limit

Here is a fun way to lose an afternoon: carefully write coding standards into your repository's .github/copilot-instructions.md, request a Copilot code review on a pull request that violates several of them, and watch the review come back with cheerful comments about none of it. The instructions were fine. The file was in the right place. The problem was quantity: my instructions file was many thousands of characters long, and at the time, Copilot code review read only the first 4,000 of them.

sqlserverscience.com

Getting Your AI Agent to Actually Follow Your Instructions There is a special kind of frustration in watching an AI agent cheerfully do the exact thing your instructions file tells it not to do. You wrote the rule. The rule is right there. The agent read it, in some technical sense of the word…

Getting Your AI Agent to Actually Follow Your Instructions

There is a special kind of frustration in watching an AI agent cheerfully do the exact thing your instructions file tells it not to do. You wrote the rule. The rule is right there. The agent read it, in some technical sense of the word "read," and then ran the query anyway. I have been running AI agents against real DBA work for a while now, with a custom instructions file that has grown the way scar tissue grows: every section marks a place where something went wrong once.

sqlserverscience.com

Scalar UDFs vs Inline TVFs: Measuring the Damage Scalar user-defined functions are the most natural thing in the world to write. You have a bit of logic, you wrap it in a function, you call it in your SELECT list, and your query is suddenly clean and readable and forty times slower. That last part…

Scalar UDFs vs Inline TVFs: Measuring the Damage

Scalar user-defined functions are the most natural thing in the world to write. You have a bit of logic, you wrap it in a function, you call it in your SELECT list, and your query is suddenly clean and readable and forty times slower. That last part tends to arrive later, on someone else's on-call shift. This post takes one piece of logic and implements it three ways: as a classic scalar UDF, as the same scalar UDF with SQL Server 2019's automatic inlining allowed to work, and as an inline table-valued function.

sqlserverscience.com

What Reaches Outside Your Database? sys.dm_db_uncontained_entities Every database likes to pretend it is self-contained. Then you restore it onto another instance and discover the stored procedure that queries a sister database, the synonym pointing at a linked server, the login-mapped user that…

What Reaches Outside Your Database? sys.dm_db_uncontained_entities

Every database likes to pretend it is self-contained. Then you restore it onto another instance and discover the stored procedure that queries a sister database, the synonym pointing at a linked server, the login-mapped user that no longer maps to anything, and the job of untangling begins at the worst possible time: after the move. SQL Server can tell you all of this…

sqlserverscience.com

The Guard Clauses Your Index Rebuild Script Needs Writing a loop that rebuilds fragmented indexes takes about ten minutes. Writing one you can safely leave running unattended at 2 AM takes considerably longer, and the difference is entirely in the guard clauses: the checks that decide when not to…

The Guard Clauses Your Index Rebuild Script Needs

Writing a loop that rebuilds fragmented indexes takes about ten minutes. Writing one you can safely leave running unattended at 2 AM takes considerably longer, and the difference is entirely in the guard clauses: the checks that decide when not to touch an index, and which options are even legal for the index in front of you. Years ago I published my index reorg/rebuild script, and it still runs nightly on instances I look after.

sqlserverscience.com

Hedging Like a Witness Under Oath A viral AI-doom video and one hedged research paper explain why overclaiming wins, and why calibrated uncertainty is honesty wearing an anxious face.

Hedging Like a Witness Under Oath

A viral AI-doom video and one hedged research paper explain why overclaiming wins, and why calibrated uncertainty is honesty wearing an anxious face.

sqlserverscience.com

Finding Eager Index Spools in the Plan Cache Somewhere in your plan cache right now, there is probably a query quietly building an index every single time it runs. Not once. Every execution. It builds the index, uses it, and throws it away, like a carpenter who constructs a new ladder each morning…

Finding Eager Index Spools in the Plan Cache

Somewhere in your plan cache right now, there is probably a query quietly building an index every single time it runs. Not once. Every execution. It builds the index, uses it, and throws it away, like a carpenter who constructs a new ladder each morning and burns it every night. That is an eager index spool, and the frustrating part is that SQL Server never files a missing index request for it.

sqlserverscience.com

What ROW and PAGE Compression Do to the Record Data compression is one of the few storage features you can turn on with a single clause and measure the same afternoon. It changes the physical format of the record on the page, not the meaning of your data, so no query has to change.[1] This post…

What ROW and PAGE Compression Do to the Record

Data compression is one of the few storage features you can turn on with a single clause and measure the same afternoon. It changes the physical format of the record on the page, not the meaning of your data, so no query has to change.[1] This post takes one table, stores identical rows in it three ways, uncompressed, row-compressed, and page-compressed, and reads the records off the page to see exactly what shrank and how.

sqlserverscience.com

A Tour of SQL Server Page Types: Data, GAM, SGAM, PFS, IAM Most of this storage series has been spent inside data pages, pulling rows apart byte by byte. But a data file is not only data pages. Scattered through it, at fixed and predictable spots, are pages whose entire job is to track other…

A Tour of SQL Server Page Types: Data, GAM, SGAM, PFS, IAM

Most of this storage series has been spent inside data pages, pulling rows apart byte by byte. But a data file is not only data pages. Scattered through it, at fixed and predictable spots, are pages whose entire job is to track other pages: which extents are allocated, which have free space, how full each page is, and which pages belong to which table.

sqlserverscience.com

Forwarded Records: What an UPDATE Can Do to a Heap A heap, a table with no clustered index, has a failure mode that clustered tables do not: an UPDATE that makes a row larger can force it to physically move, and SQL Server leaves a pointer behind at the old location so nonclustered indexes and the…

Forwarded Records: What an UPDATE Can Do to a Heap

A heap, a table with no clustered index, has a failure mode that clustered tables do not: an UPDATE that makes a row larger can force it to physically move, and SQL Server leaves a pointer behind at the old location so nonclustered indexes and the RID can still find it. These are forwarded records, and they are one of the better reasons to think twice before leaving a busy table as a heap.

sqlserverscience.com

Row-Overflow and LOB: When a Row Leaves the Page A data page is 8 KB, and a single row is capped at 8,060 bytes of in-row data.[1] So what happens when you declare two varchar(5000) columns, or store a 20,000-character varchar(max), in a row that plainly cannot hold it? SQL Server does not refuse…

Row-Overflow and LOB: When a Row Leaves the Page

A data page is 8 KB, and a single row is capped at 8,060 bytes of in-row data.[1] So what happens when you declare two varchar(5000) columns, or store a 20,000-character varchar(max), in a row that plainly cannot hold it? SQL Server does not refuse the row; it moves part of it off the page and leaves a pointer behind.

sqlserverscience.com

How SQL Server Packs BIT Columns Into a Single Byte A common worry about bit columns is that a table full of yes/no flags will waste a byte on every flag. It will not. SQL Server packs bit columns together, up to eight of them sharing a single byte, and the catalog view that reports each bit as "1…

How SQL Server Packs BIT Columns Into a Single Byte

A common worry about bit columns is that a table full of yes/no flags will waste a byte on every flag. It will not. SQL Server packs bit columns together, up to eight of them sharing a single byte, and the catalog view that reports each bit as "1 byte" hides that fact rather than revealing it. This is a short, satisfying one to see on the page: we will store ten…

sqlserverscience.com

The NULL Bitmap: How SQL Server Records Nullability Every SQL Server data row carries a NULL bitmap, even a table with no nullable columns. Find it on the page, watch the bits flip as columns go NULL, and see what it actually costs.

The NULL Bitmap: How SQL Server Records Nullability

Every SQL Server data row carries a NULL bitmap, even a table with no nullable columns. Find it on the page, watch the bits flip as columns go NULL, and see what it actually costs.

sqlserverscience.com

How SQL Server Stores MONEY and SMALLMONEY, and the Rounding Trap money and smallmoney are just integers scaled by 10000. The real page bytes, why the ranges are what they are, and the four-decimal truncation trap that bites calculations.

How SQL Server Stores MONEY and SMALLMONEY, and the Rounding Trap

money and smallmoney are just integers scaled by 10000. The real page bytes, why the ranges are what they are, and the four-decimal truncation trap that bites calculations.

sqlserverscience.com

How SQL Server Stores DECIMAL and NUMERIC, Down to the Bytes decimal and numeric store a sign byte plus a little-endian integer scaled by the column scale. Why precision buys storage in tiers, and why an oversized decimal is not free.

How SQL Server Stores DECIMAL and NUMERIC, Down to the Bytes

decimal and numeric store a sign byte plus a little-endian integer scaled by the column scale. Why precision buys storage in tiers, and why an oversized decimal is not free.

sqlserverscience.com

How SQL Server Stores a GUID, and Why Random Ones Fragment A uniqueidentifier is 16 bytes with a peculiar sort order. Why random GUIDs fragment as a clustering key, and how NEWSEQUENTIALID and design fix it.

How SQL Server Stores a GUID, and Why Random Ones Fragment

A uniqueidentifier is 16 bytes with a peculiar sort order. Why random GUIDs fragment as a clustering key, and how NEWSEQUENTIALID and design fix it.

sqlserverscience.com

How SQL Server Stores Strings: varchar, nvarchar, and What UTF-8 Changed The same word can take 3, 5, 6, or 9 bytes depending on the type and collation. A byte-level look at varchar, nvarchar, and UTF-8 collations.

How SQL Server Stores Strings: varchar, nvarchar, and What UTF-8 Changed

The same word can take 3, 5, 6, or 9 bytes depending on the type and collation. A byte-level look at varchar, nvarchar, and UTF-8 collations.

sqlserverscience.com

SQL Server HA and DR Options: A Field Guide Log shipping, replication, mirroring, FCIs, and Availability Groups compared: what each protects, sync vs async, failover, editions, and versions.

SQL Server HA and DR Options: A Field Guide

Log shipping, replication, mirroring, FCIs, and Availability Groups compared: what each protects, sync vs async, failover, editions, and versions.

sqlserverscience.com

Can You Actually Downgrade to Standard Edition? Ask the Database First Since SQL Server 2016 SP1, sys.dm_db_persisted_sku_features over-reports edition features. How to read it before downgrading to Standard.

Can You Actually Downgrade to Standard Edition? Ask the Database First

Since SQL Server 2016 SP1, sys.dm_db_persisted_sku_features over-reports edition features. How to read it before downgrading to Standard.

sqlserverscience.com

Catching Slow File I/O with a Filtered Extended Events Session file_read_completed and file_write_completed fire on every I/O. Filter on duration to capture only the slow ones, then shred to name the file.

Catching Slow File I/O with a Filtered Extended Events Session

file_read_completed and file_write_completed fire on every I/O. Filter on duration to capture only the slow ones, then shred to name the file.

sqlserverscience.com

Mining the system_health Session You Already Have The system_health session runs by default and writes .xel files. Read the event file, shred the XML, and mine the diagnostics on disk.

Mining the system_health Session You Already Have

The system_health session runs by default and writes .xel files. Read the event file, shred the XML, and mine the diagnostics on disk.

sqlserverscience.com

A Lightweight Error Audit with Extended Events Capture error_reported in Extended Events, filter the backup and informational noise at the session, and triage real errors by severity.

A Lightweight Error Audit with Extended Events

Capture error_reported in Extended Events, filter the backup and informational noise at the session, and triage real errors by severity.

sqlserverscience.com