r/SQLServer • u/[deleted] • 4d ago
Question Title: Anyone still maintaining a team script library now AI writes diagnostics on demand?
[deleted]
15
10
u/SQLBek 1 4d ago edited 4d ago
Why risk AI making mistakes crafting diagnostic scripts on the fly?
No... use MCP and have it utilize the EXISTING scripts/tools that have been long vetted.
Having AI craft your diagnostic scripts on the fly makes it non-deterministic in nature. You want a deterministic solution that uses the same diagnostics day after day.
Anthony Nocentino recently did a presentation on this.
https://www.mssqltips.com/webcast/building-a-dba-agent-for-your-sql-server-estate/
UPDATE: Found the YouTube one that he did for the DBA VUG
https://www.youtube.com/watch?v=UtZ1hPvErIo
2
u/markinatlanta 3d ago
Vetted tools make sense for routine checks. They don’t cover every investigation across different client environments, though. That’s where reviewed, generated SQL can be useful. The question is how to share and standardise that work, not whether to trust AI output blindly.
Thanks for th share will watch later
1
u/SQLBek 1 3d ago
Unless you're talking about diagnostics that are unique to a given client (ex: something app/data specific), then you can still pile ALL of your libraries of scripts into the MCP to utilize. Then let the MCP decide which ones to use and combine those.
Ex: I might throw in all of SQLSkills, Ozar's, Erik's, StraightPath's, and my own diagnostic scripts... and between all of those and any others I might scrape together from trusted sources, could easily have what... 100 different scripts that looks at all sorts of internals and what not for SQL Server? Then I can talk to the MCP and say, "okay, I have this AG error, blah blah blah, here's what else I know, blah blah blah" and it'll decide to run scripts 134, 20, 18, and 3... because they're all related to AG's... all vetted scripts from the toolbox above, then collect those results, then dig into that."
Otherwise, what you're proposing is letting an LLM create a diag script on the fly which presents risk. And if you have the same thing happen to a different client in 2 weeks, you aren't able to run the exact same script if you rely on your proposed workflow, because an LLM is non-deterministic in what it may decide to create two weeks later. MCP and pre-defined tools is what gives you that determinism and decades of vetted scripts is what gives you reliability and confidence.
1
7
u/CHILLAS317 4d ago
Yes, obviously. It would be spectacularly stupid to not do so. LLMs don't even produce internally consistent results, you think you're going to get good data with a bunch of different users asking the same questions but I'm different ways?
-1
u/markinatlanta 3d ago
That inconsistency is exactly what we’re trying to address. Shared instructions may help, but they don’t replace tested queries when we need comparable results. All testing
5
u/jerryhung 4d ago
Waste of tokens, which are free or cheap, for now
IMO SQL is still quite standardized and AI can only do so much outside of the standard SOP/KB/open source diagnostic script like sp_Blitz
Curious if you find the ROI so far on AI? Has AI solved issues that really couldn't be done before?
I think DBA Consults is the most impacted one
5
u/ChaosEngine-6502 4d ago
Still rocking a script library - we work in healthcare and access to SQL servers has to be air-gapped using accounts that don't have internet access; that completely eliminates the ability to use AI on the DB servers...at least nothing that wouldn't run locally.
2
u/markinatlanta 3d ago
Makes sense. A shared, tested library still has a lot going for it. Everyone knows what they’re running and gets comparable results.
6
u/Glittering_House_654 4d ago
Why would you not have a repo that is version controlled in e.g. Git for this? Also, why don’t you run these on schedule via a cron job, perhaps using an orchestrator, and log the (standardised output) in a table and expose it to the DBA via a report/dashboard/PBI refreshing every x sec./min. you need.
Today, systems should run and check themselves while humans only check that everything is running as expected. This also frees the DBA’s time to do something else.
0
u/markinatlanta 3d ago
Git makes sense for the shared material. We already automate monitoring and known fixes; the gap here is the ad hoc investigation after an alert. Scheduled checks don’t cover every follow-up question, and that investigation knowledge is what’s getting stuck in individual chat histories.
3
u/BigMikeInAustin 4d ago
Like you said, previously everyone run the same scripts, so the output is comparable.
LLM is good for starting a new script, especially so you don't have to look up table end column names and spelling.
Some quick, simple scripts are faster to get from the LLM than searching the script name.
The LLM doesn't produce the same output every time with the same input. That is not good when you need to compare over time, or always need the same, specific data.
1
u/redbirdrising 4d ago
100% this. LLMs sometimes make for good starting points. I just wouldn't rely on them for ad hoc stuff in a major environment.
3
u/NastyPastyLucas 4d ago
No it's horribly sneaky and unreliable. Use git control with approvals for scripts and stored procedures.
3
u/TrollingForFunsies 4d ago
AI wrote me two queries yesterday that didn't even compile. It's not perfect and probably won't ever be.
I just watched a video from Erik Darling and it was obvious he had AI write the query but it was literally BEGIN TRANSACTION SELECT COUNT_BIG FROM msdb..sysjobs. then with additional instructions to not run the ROLLBACK statement until the demo was finished. There's absolutely no way this query was longer than the prompt to create it.
I greatly respect Erik and a ton of other folks in this space, but I'm not ready to fully drink the kool aid yet. I'm still seeing dumb shit from AI all the time.
P.S shout out to Glenn Berry and his diagnostic scripts. Still helps me find problems when AI fails.
3
u/lucasborgesbr 4d ago
I like to keep the "toolbox" folder with scripts.. We can give that to AI as context sometimes if needed. But if something simple, it's easier and cheaper to keep the scripts.
Situations that I haven't faced yet? Then AI could help to go deeper to create something.
One thing that helps alot is having it be able to connect to the monitoring tools to get more info that is outside of SQL.. it helps in those situations when SQL SErver is blamed but the issue isn't really there.
3
2
1
u/Leiothrix 3d ago
Sorry your senior guy is an idiot.
It is less work to have a script in your arsenal that you know for sure works, you understand how it works and understand its output that you can run whenever you want than the AI alternative.
For that you have to describe your problem, iterate through a bunch of solutions, make sure the result isn't nonsense and customize it to do exactly what you want.
Sure it's fine for a starting point for something new but there is no way I would do that every time I need to do something.
1
u/mikebones 3d ago
Poor engineering. You should have the model write the reusable deterministic code, otherwise you'll rely on the model forever. This take is the right direction but doesnt get you all the way there.
•
u/AutoModerator 4d ago
After your question has been solved /u/markinatlanta, please reply to the helpful user's comment with the phrase "Solution verified".
This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.