Tag: AI

Finding and Killing Blockers with Redgate AI Tech

Redgate has a research arm, called the Foundry, that has been experimenting with AIs and DBA tasks. This post shows how GenAI tech can be helpful to DBAs in finding blocking and removing the offending client.

This is part of a series of experiments with AI systems.

Redgate Runbooks

One of the experiments the Foundry is running is with something we’ve called Runbooks. Here’s the main screen, where I have a welcome and a chat window. This is like what I see in Claude.ai.

I have connected this to two instances in the settings, and given the tool permissions to run queries, but not execute commands. The first server is the Local 2022 Default and the second is the 2910-41433.

A Blocking Problem

I’m going to set up a blocking session with this code. Notice it opens a transaction and then performs an update.

In a second session, I’ll run this code. Notice this select is blocked and I have no results. The bottom shows this as “executing”.

Now I’ll go to the Runbooks and enter a question. In this case, I ask it what is wrong with my 2022 server, as if someone called me and said there was an issue. Imagine the “Select” query owner wondering why things aren’t returning right away.

The Redgate Runbook responds by saying it needs to run something. The first time, it asks me to approve this, which I did. Then it runs it and shows executed.

Below this I get some results of what’s returned. This isn’t different than SP_who2, but if you’ve used that tool, you often get a lot of system stuff. I Could use sp_whoisactive, but again, more results than I want without knowing anything. Here the Runbook as limited results to what I care about.

What’s more, the Runbook then tells me something about what it analyzes. This isn’t perfect, but it’s been better than what a lot of help desk/first line support people have told me.

If I know who 56 is, certainly I can ask them to close their tran. This isn’t perfect, but I can ask the Runbook to do this, as I do at the bottom of the image above.

It again asks me to run something, and when it does, I see the executed note.

If I go back to SSMS, I see the query completed.

I typically might not kill the session without more research. I could have asked for what this is, which I’ll do now for the 57 (blocked) session. What was running here? (since 56 was killed)

Again, I approve this and get results.

To me, that’s pretty cool. Using AI to help me get things done as a lever, rather than a replacement is useful. I could have set the AI checking while I finished another task, or used Slack/Teams to check with others as a troubleshoot. I could certainly let the AI run, but I want approvals. I could copy/paste the code to a tool to run it, but the AI let’s this run separately, while I could be multi-tasking with a phone call to the user, or to the help desk, or anything else. More importantly, if I had this AI working from a mobile phone (jump box, etc.) I could be doing minimal typing and have the tech working for me.

This isn’t a product, and unlikely to be one in its own right, but this is the type of thinking we do at Redgate. Harness AI, in a safe way, that’s useful.

And ingeniously simple.

Tom Hodgson runs the group that worked on this, and he have me a fun, unforgettable interview. You can watch here: https://www.red-gate.com/simple-talk/podcasts/coffee-chat-with-tom-hodgson/

Video Walkthrough

Watch this live

November 5, 2025
Data > Hype

There is a ton of hype now about using GenAI for various tasks, especially for technical workers. There are lots of executives who would like to use AI to reduce their cost of labor, whether that’s getting more out of their existing staff or perhaps even reducing staff. Salesforce famously noted they weren’t hiring software engineers in 2025. I’m not sure they let engineers go, but it seems they did let support people go.

For many technical people, we know the hype of a GenAI agent writing code is just that: hype. The agents can’t do the same job that humans do, at least not for some humans. We still need humans to prompt the AIs, make decisions, and maybe most importantly, stop the agents when they’re off track. I’m not sure anyone other than a trained software engineer can do that well.

I was listening to a podcast recently on software developers using AI, and there was an interesting comment. “Data beats hype every time, ” which is something I hope most data professionals understand. We should experiment with our hypothesis, measure outcomes, and then decide if we continue on with our direction, or if we need to rethink our hypothesis.

Isn’t that how you query tune? You have an idea of what might reduce query time, you make a change, and check the results. Hopefully you don’t just rewrite certain queries using a pattern because this has helped improve performance in the past without testing your choice. Maybe you default to adding a new index (or a new key column/include column) to make a query perform better? I hope you don’t do those last two.

AI technology can be helpful, but there needs to be some thought put into how to roll it out, how to set up and measure experiments, and get feedback on whether it actually produces better code and helps engineers. Or if it’s just hype that isn’t helping.

Ultimately, I think that this is especially true for data professionals, as the training of models on SQL code isn’t as simple or easy as it might be for Python, Java, C#, etc. For example, I find some models are biased more towards one platform (MySQL) than another (SQL Server). Your experiments should include using a few different models and finding out which ones work well and (more importantly) which ones don’t. We also need to learn where models actually produce better-performing code for our platforms.

If you’re skeptical of AI, then conduct some experiments. Try to learn to use the tool to help you, rather than replace you. Look for ways to speed up your development, or have an assistant handle tedious tasks. I have found that when I do that, I get benefits from AI that save a bit of typing.

From the Pragmatic Engineer podcast, the best way to deal with some of the hype on AI is with data, take a structured approach to rolling it out, throw in a lot of AB testing measures with different groups or cohorts, evaluate, and see what works well. One of the things the guest noted was that the most highly regulated and structured groups are having the most success with AI. Because they’re careful about rollout, and they are measuring everything. They’ve been measuring time spent, accuracy of tasks and more. Then they decide where and when to use AI, which might be the best advice you get.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

November 5, 2025

Creating a Striped Backup Set with AI

I needed to test a striped backup, so I decided to ask the AI’s for help.

This is part of a series of experiments with AI systems.

The Problem

A customer wanted to know if we could handle a large striped backup set. In this case, 20 files. I didn’t want to try and do this myself because I was busy at the time, so I thought I could get the AI’s to help me.

I started with this prompt: for a sql server database called “Sandbox” can you generate a backup command that will stripe the backup across 32 files with “sandbox” the current date/time, and a number for the backup file name?

Local Gemma3 LLM

I started with a local model because I had that open. I got a result that looked OK.

Below this I had PowerShell code to help me:

-- Using PowerShell (example):
# $i = 1
# while ($i -le 32)
# {
#   $filename = "D:\Backups\Sandbox\_" + REPLACE(CONVERT(VARCHAR, GETDATE(), 112), ' ', '_') + "_" + $i.ToString("D3").PadLeft(3, '0') + ".bak"
#   # Execute the backup command:
#   Invoke-Command -ComputerName <ServerName> -ScriptBlock {
#       & "backup.exe" -Database "Sandbox" -BackupFile "$($filename)" -BackupType "D" -Init -Format "MEDIANAME = \"Sandbox\""
#   }
#   $i++
# }

This won’t work, proving that there isn’t enough database training in some of these small models. The model does warn me this won’t work as is.

Claude

Since the local model was slow, and didn’t get me what I wanted, I tried Claude. Same prompt, and I got this code, which is nice in that it handles the current date time whenever I run it.

DECLARE @BackupPath NVARCHAR(500) = 'C:\Backups\' -- Change this to your backup directory
DECLARE @DateTime NVARCHAR(20) = CONVERT(NVARCHAR, GETDATE(), 112) + '_' + REPLACE(CONVERT(NVARCHAR, GETDATE(), 108), ':', '')
DECLARE @BackupCommand NVARCHAR(MAX)

SET @BackupCommand = 'BACKUP DATABASE [Sandbox] TO '

-- Generate 32 striped backup files
SET @BackupCommand = @BackupCommand +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_01.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_02.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_03.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_04.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_05.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_06.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_07.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_08.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_09.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_10.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_11.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_12.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_13.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_14.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_15.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_16.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_17.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_18.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_19.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_20.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_21.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_22.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_23.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_24.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_25.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_26.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_27.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_28.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_29.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_30.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_31.bak'', ' +
     'DISK = ''' + @BackupPath + 'sandbox_' + @DateTime + '_32.bak'' ' +
     'WITH COMPRESSION, STATS = 10'

EXEC sp_executesql @BackupCommand

I could easily then test this by adding a SELECT and commenting out the Backup command. When I do that, I see these results.

Note I’ve started adding some line breaks to the results so I can check things.

Prompt AI

I decided to try Prompt AI as well. I started with this code:

BACKUP DATABASE sandbox
TO DISK = ‘sandbox_20251013_x.bak’ WITH INIT

Then I opened Prompt AI and entered this: expand this backup command to create a striped backup of 32 files, replacing the x with a number for each file

You can see this below:

This got me a quick and dirty backup test.

Then we need to correct the syntax.

Now I have a command.

However, this doesn’t work as I can’t use string concatenation inside the backup command. I asked Prompt AI to generate a string for this instead (from this code) and then sp_exeutesql and got this code:

DECLARE @DateString VARCHAR(8) = CONVERT(VARCHAR(8), GETDATE(), 112) -- Format: YYYYMMDD

DECLARE @BackupCommand NVARCHAR(MAX)
-- Build the backup command as a string

SET @BackupCommand = 'BACKUP DATABASE sandbox TO '
-- Add all backup file destinations

SELECT @BackupCommand = @BackupCommand + CASE
                                              WHEN LEN(@BackupCommand) > 25 THEN
                                                  ', '
                                              ELSE
                                                  ''
                                          END + -- Add comma except for first item
     'DISK = ''sandbox_' + @DateString + '_' + CAST(number AS VARCHAR(2)) + '.bak'''

FROM master.dbo.spt_values

WHERE type = 'P'
       AND number
       BETWEEN 1 AND 32
-- Add WITH INIT

SET @BackupCommand = @BackupCommand + ' WITH INIT'
-- Execute the dynamically built command

--EXEC sp_executesql @BackupCommand

SELECT @BackupCommand

That works.

Summary

Both Claude and PromptAI worked, though Claude worked well with a first prompt. I continue to like the Sonnet model and find it really helpful in saving time.

It took me longer to write this post than generate the code in 3 places and test it. In terms of saving time, Claude would have saved me at least probably ten minutes, maybe more, as I’d spend time making decisions. With Claude, I got the code in under a minute and then needed to test it.

Always test your code. Including your AI-generated code.

October 22, 2025

Prompt AI helping with Auditing

I had a conversation with a customer asking this question: how can I tell who called a stored procedure so I can audit the action?

I decided to see if Prompt could help me here.

This is part of a series of experiments with AI systems. This is also part of a series of posts on SQL Prompt. You can see all my posts on SQL Prompt under that tag.

The Setup

I had this code, which is a simple stored procedure. I sketched this out, but I’m looking for a function to add to my code that helps me audit the caller.

I opened the Prompt AI (ALT+Z) and asked the question you see in the image. I also asked to assign it to the variable, but didn’t exactly specify everything.

In the image below, you can see the code that changed. Prompt AI added the System_User function, which is exactly what I wanted.

I accepted this and then asked to change this to the user instead of the login.

Again, the function I wanted was added: CURRENT_USER.

I know what I needed, or I had an idea. However, if I wasn’t sure what function to use or if there was one, this is handy. I’m working inline, rather than going to a browser and hitting Books Online or Googling for the answer. I can keep coding or looking at my code as the AI runs.

If I ask for an explanation, I get one, which helps me judge if this is actually the code I want.

If you have SQL Prompt, get Prompt AI a try by opening it with ALT+Z.

If you haven’t tried SQL Prompt, download the eval and give it a try. I think you’ll find this is one of the best tools to increase your productivity writing SQL.

Video Walkthrough

I show this live in the video below.

October 15, 2025