Int32 ought to be enough for any table's identity column

-- Most developers at some point

We've all done it, creating a new table in SQL Server and giving it a nice auto-incrementing integer as the primary key. There's no way that table will ever reach 2,147,483,647 rows, right? Now, for most tables that's likely true, but the last thing you want is to be surprised when suddenly you can no longer insert into your table due to using up that full Int32 space.

In our system there are some tables we knew would be reaching those limits in the relatively near future, and as part of preparing for the migration I wanted to get some good observability on how close we were to the limits and how it was trending. Again, the last thing we wanted was to be surprised. Naturally for me this meant wanting to get this into Datadog so it can easily be visualized and alerted on.

Defining the Agent Job

One way to implement this would have been to create a normal Datadog agent check and run the query that way. This time around I wanted to see how easily I could do it via scheduled SQL Server Agent jobs and report the metric down to the local Datadog agent via UDP directly. You can define agent jobs in PowerShell, so it ended up being very straightforward.

First I defined a couple variables to declare which database and table to look at:

$table = 'MyTable'
$database = 'MyDatabase'

For now this is just limited to one table, but sets the stage for being able to layer in more down the line, perhaps simply querying everything.

Next, I used IDENT_CURRENT to grab the current identity value for that table, and calculate the percentage used (note that this is assuming an integer column type here):

$pctUsed = (Invoke-Sqlcmd `
             -Query "SELECT (IDENT_CURRENT('$database.dbo.$table') / 2147483647) * 100 
                     AS PctUsed" `
             -ServerInstance "sql-server-host" `
           ).PctUsed

With that I construct a metric, tagged with the database and table, and send that to the local Datadog agent:

$message = "olo.database.identity_space_used:$pctUsed|g|#table:$table,database:$database"
$messageBytes = [Text.Encoding]::ASCII.GetBytes($message)

$socket = New-Object System.Net.Sockets.UDPClient 
$socket.Send($messageBytes, $messageBytes.Length, "127.0.0.1", 8125) 
$socket.Close() 

And that's it! I can set this agent job on a schedule and get regularly reported metrics flowing into Datadog to track the column saturation where I need it. Since this is PowerShell I could also have just pulled in the Datadog client library as well, but in this case it was nicer to keep it dependency-free and construct the message manually, since it's straightforward enough.