I’ve been seeing more and more noise about how ChatGPT can help with technical stuff, writing code, all that. Well, I wanted to know how best to set up Query Store in SQL Server 2022, so I asked it. I’m not going to say this any other way, ChatGPT lied to me. Here’s the conversation. I’m in italics.
The Conversation With ChatGPT
What are the best settings for Query Store in SQL Server 2022
Query Store is a powerful feature in SQL Server 2022 that helps in monitoring query performance and identifying query regressions over time. However, setting up Query Store requires careful consideration of several factors, including the size of the database, the frequency of queries, and the query workload.
Here are some best practices for configuring Query Store in SQL Server 2022:
- Enable Query Store: Make sure Query Store is enabled on your SQL Server instance. You can do this by setting the QUERY_STORE option to ON using the ALTER DATABASE statement.
- Set the data retention period: Set the data retention period according to your data retention policy. This determines how long Query Store keeps the data before purging it. You can set the retention period using the MAX_STORAGE_SIZE_MB and INTERVAL_LENGTH_MINUTES options.
- Set the MAX_STORAGE_SIZE_MB option: The MAX_STORAGE_SIZE_MB option sets the maximum size for the Query Store data. Make sure to set this option based on the size of your database and the amount of data you want to retain in Query Store.
- Configure Query Store Capture Mode: There are two capture modes available in Query Store – ALL and AUTO. The ALL mode captures all queries executed on the server, while the AUTO mode captures only those queries that meet certain criteria. Choose the capture mode based on your workload and the information you want to capture.
- Monitor Query Store size: Monitor the Query Store size regularly to ensure that it doesn’t exceed the maximum storage size. If the size limit is reached, Query Store will stop capturing new queries.
- Use Query Store reports: Use Query Store reports to analyze the query performance and identify queries that are causing performance issues.
- Manage Query Store: Manage Query Store by purging old data, setting thresholds, and updating Query Store configuration settings as necessary.
In summary, configuring Query Store requires careful consideration of several factors to ensure optimal performance and efficient use of resources. Make sure to monitor Query Store regularly and adjust the configuration settings as necessary to ensure that it meets your performance monitoring needs.
Are you sure about the number of query store capture modes?
I apologize for the confusion in my previous response. SQL Server Query Store actually has three capture modes available in SQL Server 2022:
- OFF: Query Store is disabled and does not capture any query data.
- ALL: Query Store captures all queries executed on the server, including ad hoc and prepared queries.
- AUTO: Query Store captures only those queries that meet certain criteria, such as those with high CPU, I/O, or duration.
Thank you for bringing this to my attention, and please let me know if you have any further questions.
I’m still confused on how many capture modes there are in query store in sql server 2022
I apologize for the confusion. To clarify, there are three Query Store capture modes in SQL Server 2022:
- OFF: In this mode, Query Store is disabled and does not capture any query data.
- ALL: In this mode, Query Store captures all queries executed on the server, including ad hoc and prepared queries.
- AUTO: In this mode, Query Store captures only those queries that meet certain criteria, such as those with high CPU, I/O, or duration.
I hope this clears up any confusion, and please let me know if you have any further questions.
Wasn’t there a custom mode introduced at some point?
You are correct. A fourth capture mode, called CUSTOM, was introduced in SQL Server 2017 and is also available in SQL Server 2022.
In the CUSTOM capture mode, you can specify a set of query predicate filters to capture only the queries that meet specific criteria. You can create a custom capture policy that includes one or more query predicate filters, which can be based on various query attributes such as CPU time, execution count, and more.
Using the CUSTOM capture mode, you can have more granular control over which queries are captured and stored in Query Store, which can be useful in large environments with complex workloads.
I apologize for any confusion, and thank you for bringing this to my attention. Please let me know if you have any further questions.
What is the best max_storage_size_mb setting for query store in SQL Server 2022
Determining the best max_storage_size_mb setting for Query Store in SQL Server 2022 depends on several factors, including the size of the database, the number of queries, and the frequency of Query Store data purging.
In general, it is recommended to set the max_storage_size_mb option to a value that is based on the size of the database, the expected growth of Query Store, and the retention period of Query Store data.
As a general rule of thumb, you should allocate a percentage of your database size for the Query Store storage. For example, you could allocate 10% of your database size for Query Store storage. So, if your database size is 100 GB, you can set the max_storage_size_mb to 10 GB.
You should also consider the retention period of Query Store data when setting the max_storage_size_mb option. If you have a long retention period, you may need to increase the max_storage_size_mb option to ensure that Query Store does not run out of storage space before the data is purged.
It is important to regularly monitor the Query Store size and adjust the max_storage_size_mb option as necessary to ensure that Query Store does not run out of storage space. You can also configure Query Store to send an alert when the storage space is close to being exhausted.
In summary, the best max_storage_size_mb setting for Query Store in SQL Server 2022 depends on the size of the database, the expected growth of Query Store, and the retention period of Query Store data. It is recommended to allocate a percentage of the database size for Query Store storage and to regularly monitor the Query Store size and adjust the max_storage_size_mb option as necessary.
Why the number 10% for allocating query store size?
The 10% rule of thumb for allocating Query Store size is not a hard and fast rule, but rather a general guideline. The actual percentage you allocate for Query Store size may depend on several factors, such as the size of your database, the number of queries executed on your server, and the retention period of Query Store data.
Allocating too much space to Query Store may result in wasted disk space, while allocating too little space may result in Query Store running out of space before data is purged.
Allocating 10% of the database size for Query Store storage is a good starting point for most environments, as it strikes a balance between having enough space for Query Store and not wasting too much disk space. However, it is important to regularly monitor the size of Query Store and adjust the storage allocation as needed based on the growth of Query Store data.
Ultimately, the best allocation of storage for Query Store is one that balances the space needed for Query Store data with the available disk space, while also taking into consideration the retention period of Query Store data.
Where does the 10% guideline for query store come from?
The 10% guideline for allocating storage space for Query Store is not based on any specific industry standard or recommendation. Rather, it is a general rule of thumb that has been suggested by some SQL Server experts based on their experience.
The guideline is based on the assumption that Query Store data will typically occupy a small percentage of the total database size. By allocating 10% of the database size for Query Store, there should be enough space to store Query Store data without consuming too much disk space.
However, the actual percentage that you allocate for Query Store storage may vary depending on several factors, such as the size of your database, the number of queries executed on your server, and the retention period of Query Store data. Therefore, it is important to monitor the size of Query Store and adjust the storage allocation as needed based on the growth of Query Store data.
Which SQL Server experts have suggested that 10% is a good rule of thumb?
The 10% guideline for allocating storage space for Query Store is a common recommendation among many SQL Server experts, but it is not based on any specific industry standard or recommendation.
Several SQL Server experts and bloggers have suggested the 10% guideline in their articles and blog posts. Some examples include:
- Kendra Little, a SQL Server consultant and Microsoft Certified Master, suggested in a blog post that allocating 10% of the total database size for Query Store storage is a reasonable starting point.
- Erin Stellato, a SQL Server consultant and Microsoft Data Platform MVP, recommended in a blog post that allocating 10% of the total database size for Query Store storage is a good place to start, but cautioned that the actual allocation may vary depending on the workload and data retention requirements.
- Pinal Dave, a SQL Server consultant and Microsoft Data Platform MVP, also suggested in a blog post that allocating 10% of the total database size for Query Store storage is a good guideline, but emphasized that it is important to monitor the Query Store size and adjust the allocation as needed.
It is important to note that the 10% guideline is a general rule of thumb and the actual allocation of storage space for Query Store may vary depending on several factors, such as the size of your database, the number of queries executed on your server, and the retention period of Query Store data. Therefore, it is important to monitor the size of Query Store and adjust the storage allocation as needed based on the growth of Query Store data.
Can you give me a link to the posts where these experts suggested this rule?
Sure, here are some links to blog posts where SQL Server experts have suggested the 10% guideline for allocating storage space for Query Store:
- Kendra Little: “How Much Disk Space Query Store Needs for a SQL Server Instance” (https://www.brentozar.com/archive/2019/02/how-much-disk-space-query-store-needs-for-a-sql-server-instance/)
- Erin Stellato: “Query Store and Automatic Tuning in SQL Server 2017” (https://www.sqlskills.com/blogs/erin/query-store-and-automatic-tuning-in-sql-server-2017/)
- Pinal Dave: “SQL SERVER – 10 Basic Performance Tuning Tips for SQL Server” (https://blog.sqlauthority.com/2018/06/12/sql-server-10-basic-performance-tuning-tips-for-sql-server/)
It is worth noting that the opinions and recommendations of SQL Server experts may vary, and the best allocation of storage for Query Store ultimately depends on your specific environment and workload. Therefore, it is important to carefully monitor the size of Query Store and adjust the storage allocation as needed based on the growth of Query Store data.
All three of those links are broken and a search in Google, Bing and DuckDuckGo doesn’t show that those articles ever existed. Did you make them up?
Something went wrong. If this issue persists please contact us through our help center at help.openai.com.
ChatGPT Lied To Me
I don’t know what to say. First, if I didn’t know that there were four capture modes, not three, I would have never found out. A simple search shows that there are four capture modes. Then, ChatGPT lied to me. It told me there was a general rule of 10% of the database size for Query Store. I’d never heard of that (and sure, I absolutely don’t know everything about Query Store and have more to learn, however, I do experiment with it and I know a little) so I drilled down. ChatGPT lied to me even more. It literally made up blog posts. I mean, I admire who it picked as experts. Erin, Kendra and Pinal 100% do know what they’re talking about. However, straight up, ChatGPT lied to me.
And to top it off, when I called it on the lies, it errored out. I still can’t get it to answer that last question.
Conclusion
I took all this to Twitter and thankfully Buck Woody was on there. Buck knows a thing or two about databases, machine learning and AI. He told me this phenomenon isn’t that well known and it’s called hallucination. Basically, these things are programmed to supply an answer, whether or not the data is there. In short, it’s going to get things wrong.
So, for the time being, I personally don’t use ChatGPT for much. I know some people are using it as inspiration for the code, it gives them an example that they can then tweak. All cool. However, I would not recommend using this tool to get answers to your questions just yet. ChatGPT lied to me and it can lie to you too.
In over 35 years in IT I’ve heard many stories about revolutionary tools that will eliminate the need for professional developers. I am not saying ChatGPT claims to be one. However, like most things on the inter webs, people need to be careful in what they hear and believe. In this case there is an expert who knows what’s what. Thanks, Grant!
It reminds me of the arguments, when I was a kid, around allowing students to use calculators on tests. The most valid conclusion stated that a calculator simply did what it was told. The user needed to determine if the answer was correct or not.
That is just as true here. Unfortunately, not everyone will know enough of the subject matter to question the veracity of the results the way Grant did. Therein lies the danger of blindly accepting an answer. To be sure, that isn’t any different from the dark days before ChatGPT. But tools can increase their apparent capabilities faster than those who consume the output,
Thanks for the input and feedback. Good stuff.
The thing is, we’ve had this problem for a long time. If you do a web search with Yadungo, how do you know the answers you’re seeing are correct? I know Stack Overflow is set up the way it is because of this very problem. However, even there, how do you know? So before we got to AI, we still needed to have domain knowledge to evaluate the answers we got, plus lots and lots of testing.
I do think this issue with hallucinations is new though. Now it’s not just some goofball saying PLE has to be greater than 300 because of ancient, incorrect, information. Now the AI is literally making stuff up. Domain knowledge and testing have just become more important. So yeah, I agree, at least in the near term, I’m not sweating my job, either as a technologist or a writer.
ChatGPT has a very broad knowledge but not very deep. You can ask it about literally anything, it is also very good at finding similarities and analogies between very different things, some things it will be able to do better than humans just due to the massive amounts of text on so many different subjects it has ingested. Some things that it will do better than humans is implicitly noticing patterns across large amounts of information it’s good at making analogies “like hand is to finger as foot is to toe” and also much grander analogies between fields where there are very few humans that know about both fields so not able to make analogies maybe between Chemistry and Physics. Large Language Models (LLMs) are really just working out the next word to write a sentence taking into account the question it is responding to and what it has already written. It is pretty incredible it gets as far as it does and is understandable why it has these “hallucinations” of just making things up that sound likely to happen.
There’s going to be some amazing stuff coming out of this kind of thing in the future, without a doubt. I’m still not sweating my job just yet. ‘Course, I get to retire in just a few years. If I was 30, I’d be a bit more concerned.
Yes, I’m not sweating either, I’ll get to retire before AI takes over. I look at it as having an assistant. I’ve found it useful when learning a new skill. Recently I’ve been learning Python, you can ask it to write a function that will do what you want and it will produce an answer, sometimes good and sometimes not so good. With the knowledge that it can produce incorrect answers and the ability to test its solutions it can help write code, learn about methods for getting the results you need. For example, you can ask it to write a regular expression to match a pattern or a how to do an xquery on XML. btw I also tried it on your article “DATABASE SCREENING QUESTIONS” and I think it did a lot better than most of the job applicants would do (you should try giving it those questions).
Excellent. I’ll have to test it out when I get home from the current trips to see if it can help a little with some of the electronics work I’ve been doing.
It’s still quite early in the AI game. These things will be getting very much better, very quickly, while humans remain depressingly the same. It’s clearly no threat to an experienced person at the moment, but don’t expect that to last long.
Too true.
I was reading a journalist’s experience w/ ChatGPT a couple of weeks ago. It asked the AI to tell what it knew about him – several details were correct, but many were just made up – including tales of death and lawsuits. When asked for details, ChatGPT happily provided made-up links to stories detailing the death and the lawsuits. IIRC, when the journalist pointed out that those links were made up there was a similar response.
I’ve found ChatGPT useful to get some ideas for things – maybe a starter for some coding or to generate RPG campaign ideas. But on the whole – I don’t tend to use it nearly as much. I have gone to it for a quick “I know the answer exists, but _really_ don’t want to search for it and piece together results” time. It’s been pretty useful to get through all of the Azure information as a quick-start.
Nice stuff. Thanks for contributing. I love hearing how people are using this thing.
I don’t like using the word ‘lie’ in regard to ChatGPT and friends on the grounds that they have no idea what the truth is. It is simply repeating what it has heard (well, been trained on).
It might be well to regard what you comes out of ChatGPT with all of the veracity of what someone told you in an authoritative tone down the pub yesterday evening. It might well very well be correct, but then again it may not. It does, however, need verification and merits further investigation.
A friend pointed out that a lie was intention to deceive. While this is deception, there is no intention, so, technically, yeah, not a lie. Still, as a human, I feel lied to.
Ironically, I am in the middle of a database migration to SQL 2022 and I’m at the point where I need to make some Query Store decisions. After slim pickins on the web I googled, “sql MAX_STORAGE_SIZE_MB best practice” and your blog post came up. I was initially thrilled that Kendra, Erin, Pinal Dave were in agreement on the “10% Rule of Thumb”. LOL. HA! ChatGPT sure knows how to tell me what I want to hear!!! So back to the drawing board on that issue but I am so glad you posted your experience.
I have had similar experience with ChatGPT and other AI bots making up fake advice with fake links. These Chatbots can be soooooooooooooo helpful at times and then literally make up advice (including fake URL’s to that fake advice). I’ve used it for quite a bit of my PowerShell development. But every once in a while a bot will just make up cmdlets or non-existent parameters within real cmdlets. It’s a bit jarring. I don’t know what to make of it. In the words of Robert Plant, “…and it makes me wonder:”
Do I need to use a more expensive version for a more accurate experience? (It would make sense that the most capable AI tools will cost the most money)
How does it learn? If you prove to it (which I’m sure millions have users have already done) that making up fake links to back up fake advice is simply not an option. Most would prefer a simple “I don’t know the answer to that question and I can’t seem to find much on the web concerning that question.” And yet it keeps doing it? Is it programmed NOT to learn this lesson? If not, maybe there are simply insurmountable flaws in AI that rely too much on pattern matching and language prediction. Perhaps artificial conscious logical thought is still not a thing. And perhaps it can’t be.
But just a fantastic post. I noticed a lot of comments defending AI. My guess is they haven’t used it much to actually get real work done yet! lol.
Oh, don’t beat on people too hard. I’ve started using AI more. I know going in not to rely to much on 100% accuracy, but I’ve found in some circumstances, I’m getting a lot better answers to weird questions by interrogating an AI than by running simple searches. Not always, and I never rely on the answers without verification, but I’m seeing better and better work coming out of these things.
Still, a grain of salt the size of Samson’s Pebble should be kept on hand.