The age of AI may be here, but there are still some things an LLM does better than others. There have been different levels of automation or autonomics added to the systems I work with my whole career. With LLMs, as with previous “autonomous” features, it can do some amazing things, but a trained expert can still do better. The same is true with performance recommendations.
If you haven’t tried Cortex Code yet, see my last article on the Cortex Code CLI, including how to get a free trial. Nothing shows the power and faults of AI like trying to use it to do something you are already an expert at.
With the current rate of change, this post may be obsolete rather fast. Maybe it will be one I look back at one day and wonder at the state of things at the time it was written.
AI Suggestions for Performance
As a performance expert, I find that asking Cortex Code for performance suggestions is fraught with issues at this point. I gave it one query and asked for performance recommendations, even using some nifty skills with it, and it gave me 6 suggestions. It promised that some of them would give 60-80% performance improvements. Two of the suggestions were impossible in Snowflake, though they were things that might be possible for other platforms. We don’t need to ANALYZE or collect statistics on Snowflake, unless using Snowflake Postgres – and ANALYZE was one of the recommendations. None of the suggestions were really detrimental. A few might have had an impact of a few percentage points, but none of them would get the 60-80% gains that the LLM was suggesting they would.
Careful prompting helps here. Simply asking how to make a query faster is not enough. Ask for solutions that it can verify will work on Snowflake. Challenge the answers provided in multiple ways. Ask why and what sources were used for conclusions. Ask for observations more than conclusions. All of these things help with the quality of answers.
Another area I find somewhat concerning is how confidently suggestions for changing clustering keys are provided. Don’t get me wrong, at least 40% of the performance questions I deal with at work have to do with clustering in some way. However, the LLMs frequently miss some of the nuance. I’ve seen suggestions that make clustering keys three columns wide on a table with 87 micro-partitions. This is unlikely to be cost-effective. For 87 micro-partitions it is questionable whether even an XS warehouse would benefit much from clustering, and probably then only on a function on a single column. I’ve also seen recommendations to re-cluster a 4-million micro-partition table without asking critical questions about what other queries the table serves or how data arrives in the table. Both of those questions are critical to get efficient clustering that actually makes a difference across a workload. Not to mention the fact that re-clustering a many-TB table can often be done much more cheaply than by the ALTER TABLE ... statements provided.
Be sure to validate any suggestions that Cortex Code, or any LLM, gives you around performance before spending time or money implementing them. I get much better responses to very targeted questions using real data like QUERY_HISTORY, the output of GET_QUERY_OPERATOR_STATS, or the query profile. I find it most useful when I combine my performance knowledge with really targeted questions.
An Example of Performance Recommendations
I took my current free trial account, where the recent activity is mostly the SQL used when writing Snowflake Generation 2 Warehouses. I used a simple prompt:
> look at my query history over the past 14 days and suggest queries that I might want to spend time tuningThe results I received weren’t terrible:
I commented myself in that previous blog article not to use MOD in the real world, and I agree that it would be great if I could eliminate the order by random. But notice, nowhere does it suggest that using Gen2 warehouses led to better performance than Gen1 warehouses for that workload, which is a huge difference. That would have been my top observation. The whole point of the previous article was to show that for those workloads just going from Gen1 to Gen2 could provide up to a 60% increase in performance for those queries.
AI Taught me Something Here
In the second recommendation above, Cortex Code called out something I truly was not aware of. I spend a lot of time using internal tools and internal data to look at and work with queries. For this blog, I don’t use any of that – I use only what external customers can see on Snowflake.
The thing I learned from the second recommendation was not what Cortex Code told me, but the numbers it used to tell me. The statements in that item both update only one row, and that is verified by the numbers available in query history:
Note the variation there in the number of rows produced, though! That immediately caught my eye. As When doing updates from a Gen2 warehouse, Snowflake uses a merge-on-read methodology instead of our Gen1 approach of using a copy-on-read methodology. That means that the update on a Gen1 warehouse must re-write an entire full micro-partition instead of just a little delete file and an undersized micro-partition with the new row. This is the difference we see here – the Gen1 warehouse writes the entire micro-partition with 1,146,880 rows.
This was surprising to me, because I did not realize Snowflake externalized this number. This column is actually deprecated, and may be removed at any time. I almost hesitate to point this out, as I am not sure Snowflake meant to expose this number. While it’s there, it’s a very interesting way to compare updates on Gen1 vs. Gen2. You can get an idea that something like this is occurring by looking at the bytes written (a column that is not deprecated) – notice the number for the Gen1 query is so much higher than the bytes needed for just one row.
The conclusion that Cortex Code drew from these numbers was wholly incorrect, though. It interpreted these numbers to mean that one statement was actually updating many more rows and that there might be a data issue, which was incorrect. Both statements only update one row.
AI for Performance Recommendations
On the one hand, I want to be the crusty DBA, avoiding AI. On the other hand, it is so much better than I am at quickly creating meaningful visualization (with a bit of back and forth), and it comes up with conclusions that teach me things, even when they are at least a little bit wrong. AI may not be able to replace a performance expert, but it might make me look at things I hadn’t considered before.
I continue to use Cortex Code every day as a part of my regular workflow with Snowflake. I also challenge it, ask questions, and verify before using numbers to take action.
One of my colleagues loves to say that a little bit wrong is the worst kind of wrong. LLMs have told me things that are not only useless, but actively harmful if followed. Because of this, I verify what an LLM tells me in at least one way, and sometimes more, before relying on it.
