August 23, 2021 at 4:54 pm
I have received a priority 1 status after running sp_BlitzCache on one of my instances and I think it points to a large issue with my execution plans (there are other alerts about this as well; forced serialization, compilation timeout, plan warnings). Does anyone have an idea as to where to start remediating? Here is the finding message; (my server was rebooted on 8/18).
You have 15092 total plans in your cache, with 100.00% plans created in the past 24 hours, 100.00% created in the past 4 hours, and 81.00% created in the past 1 hour.
August 24, 2021 at 4:36 pm
That is just telling you that you need more memory to store query plans OR you need fewer unique query plans running against the instance. With the most of your plans existing for less than an hour in your plan cache, it sounds like more memory would be beneficial but it depends on the workload. If your instance is getting a lot of ad-hoc queries, turning on "Optimize for Ad-Hoc workload" will help and you won't need to add more memory. Basically, if a query plan is going to only be used once, you don't want it in the plan cache as that is just wasted. BUT if you have a lot of repeated queries, you want them in the plan cache so SQL doesn't need to build a new plan with each query. Also, if you have a lot of end users querying the database directly, you may want to look at turning that option on and likely reducing the number of users who are running TSQL queries directly against the database and have them use stored procedures when possible.
The reason I say it depends is I have no idea how much memory your system has or how much SQL is allowed to use. If you have 2 TB of RAM on that system and SQL is allowed to use 1.8 TB of it, adding more RAM isn't the right solution. But if you have 4 GB of RAM on the system and that SQL instance is allowed to use 1 GB (or has the default of 2 TB), you should look at reconfiguring SQL to use a more appropriate value.
If you don't have a lot of ad-hoc queries, then turning optimize for ad-hoc workloads isn't going to be the way to go. And even if you DO have a lot of ad-hoc queries, it may be better to build up stored procedures for the majority of the workloads if possible.
I would look at investigating the other alerts too though. Compilation timeout is not good as it means you have some very complex queries running against the database. Plan warnings are usually good to investigate as they can sometimes be quick and easy fixes that drastically improve performance.
The above is all just my opinion on what you should do. As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 24, 2021 at 10:00 pm
Thanks Brian. The server is set to optimize for ad hoc queries. Developers writing their own queries seems to be on ongoing reality. Is there any other way to keep the re-compilations down? Probably not... not sure I can stop the developers in my shop.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply