This month’s T-SQL topic is being hosted by Pinal Dave. Thanks Pinal!
He asks the question, “How AI has helped you in your role as a SQL Server professional?”
Forward Looking Statement
I have NOT fully tested what I am about to share but I think it is important based on my preliminary tests and wanted to use this post as a way to get the word out about it.
Teeing Things Up
First off, analyzing query plans can take time. One of the best tools to assist is with the free tool known as Plan Explorer. There are many courses and presentations about using it. I know many of us SQL Server professionals are starting to learn PostgreSQL but I am not aware of a similar tool that can be used like Plan Explorer. Maybe there is?
Looking at the raw XML in a .sqlplan is not fun, at least not for me. It does contain a lot of properties and values that are important for query tuning.
Security, Security, Security
Due to AI company policies, you might not be able to share .sqlplan files or upload them to any websites for better analysis. So even trying to use tools like ChatGPT might not even be an option.
Enter Stage Left: NVIDIA ChatRTX
What if there was a way to analyze .sqlplan files using a local chatbot and NOT require a connection to the internet? Hm…
ChatRTX is now in beta!!! It is a single package (quite large) that allows you to run a Large Language Model locally. Do some searching on YouTube on ChatRTX for some videos and installation guidance.
Personally I was able to successfully install it on both my Win11 gaming laptop and my Win10 desktop rig. I might do a series of blog posts to get into more details. There are restrictions! It took more than a few hours to get everything installed- do the video drivers first! This only works on NVIDIA RTX cards, sorry AMD fans.
My laptop was only able to install the Mistral 7B but my desktop was able to install it and the Llama-2 13B model (I have an insane graphics card, an RTX3090).
For my first test, I pointed it at a folder of PDFs and let it essentially index them. This took time! And then using the ChatRTX interface I could begin prompt engineering against my own files! How cool is that?
Show Me The Money
A classic now, Show Me The Money. (NSFW)
Since .sqlplan files are nothing more than raw XML, all I had to do was to change the file extension to .txt in order for the software to work, and told it to re-scan the folder path.
It was a simple query from the World Wide Importers database. I then asked ChatRTX, “what are the wait stats in 09Apr24.txt” and it came back with:
Based on the given context information, the wait statistics in 09Apr24.txt are:
- Wait type: “A” (ASYNC_NETWORK_IO)
- Wait time: 153
- Wait count: 31
- Elapsed time: 169
Note that these wait statistics are based on the information provided in the given context information and may not reflect the actual wait statistics of the system.
Reference files:
09Apr24.txt
How about that! Imagine being able to ask highly specific performance tuning questions across many files in a secure fashion. I’m just beginning to explore the possibilities.
If you would like to learn more, check out the MEAP book from Chrissy LeMaire, Generative AI for the IT Pro (thanks Kevin Hill for the heads-up). Thanks for reading!
Todd Kleinhans 