Claude as your Teacher

I've been focusing and studyingon SQL server performance tuning for more than a year now. I was involved in performance tuning task before, but I wanted to be an expert on that area. So I have been following online courses and doing some self-studies and reading books in my spare time.

However, I still feel I lack real world work, which require to be an expert in that field. Best way to get real world experience is find a job on that area. But in my current job I get very little performance tuning experience. I do get lot more SQL development experience. So do use my knowledge on performance tuning when I develop SQL stuff (e.g. stored procedures). 

This led me to search for some real-world experience, without leaving my current job. I did find some information on internet, but didn't find much useful or catered for me (mostly because performance tuning is case by case issue, I think).

Idea came to me suddenly recently, what if I can use AI tool as my real world experience provider. So I started working with Claude to get something developed. I used "Project" feature in Claude chat.

Let us first have a quick look into Claude Project feature.

You can find Project in Claude in left hand side menu:

Claude Projects has 3 components:

• Memory -> This is something claude autogenerate, while you work on the project. But you can edit if you noticed something is not correct.
• Instructions -> Most important part. You can give instructions specific to this project and Claude will follow this everytime it do a task within this project.
• Files -> Any additional knowledge you want to give to claude on the subject.

Your recent chats relate to this project appear under "Recents" section.

So how did I get Claude to give me (or rather teach me) performance tuning exercises?

I gave following prompt/instruction on the project:

I want to challenge my self by resolving various SQL server performance issues. In order to do this I want you to think like a SQL server performance tuning expert and generate me scenarios with performance issues. I want you to generate these challenges in 3 difficult levels -> Easy, Medium and Expert. When I'm ready, I'm going to ask you to generate a scenario and present me in a particular difficult level. For these challenges, you are going to use AdventureWorks sample database provided by Microsoft (which is publicly available) and StackOverflow database which is also available publicly. I want to generate a query or stored procedure or function with a issue and just tell me what users experience when they use it (issues they face). For this you might also need to give some context information also (e.g. parameters particular stored procedure is running, indexes which are already in use) to me. You might also need to give some script to setup the data and other objects to match the scenario in my testing database (e.g. create or drop index scripts). Once you give me the challenge, I will resolve it and present you the solution, for your evaluation. 

Then on project chat area, I gave following prompt:

Please read the instructions given and present me with Easy challenge.

I'm actually really impressed with results. It gave me a scenario where I have to inspect a slow running stored procedure and identify the issue and give solution for it. Not only it gave me a scenario and excersise, it gave me instructions to setup the database in a manner where the issue is occurring.

I'm not going to explain the whole exercises here as it will make this blog very long. But here are the highlights.

In Step 1, it gave me a script to setup the database. Claude used AdventureWorks database. Because default data didn't cater for this exercise, it gave a script to create alternative "SalesOrders" table with about 1.5 million rows in it.

In Step 2, it continued the setup and told me to create an index in a specific way.

In Step 3, it gave me script to create the troublesome stored procedure.

Finally Claude explained the scenario. Here are the actual text:

There's a "Sales by Year" report page in the app that calls dbo.GetOrdersByYear with a single year, e.g. EXEC dbo.GetOrdersByYear @Year = 2022;. Users are complaining that this report is sluggish and keeps getting slower the longer the system's been live — it used to feel snappy when the table was small, now it's a few seconds and climbing. The DBA is confused because there's a perfectly good index sitting on OrderDate that includes the exact columns the query returns, yet it doesn't seem to be helping. CPU also ticks up noticeably whenever the report runs, even though each call only returns one year's worth of rows.

What I want from you:

Run the proc with the actual execution plan on and SET STATISTICS IO, TIME ON, then come back with three things — the root cause (why that index isn't being used the way you'd hope), your fix (the rewritten proc), and the evidence that it worked (before/after logical reads, plan operator change, and duration). Keep the proc's signature the same; the caller shouldn't have to change how it invokes it.  

Not a very bad exercise to start with, isn't it?

I was able to resolve it. When I presented the solution, for my surprise, it showed me some minor things (e.g. data type conversion, which I didn't think of in this situation as it didn't contribute much), I missed.

I'm going to continue to use it. Hoping to blog a about each exercise, and my solution, so you can also learn something from it. If you have any ideas to improve this project, please comments under this blog.