SQL Compilations Rate

General
1

I am currently testing SQLWatch on a test server that is running nothing else and the SQL Compilations Rate is consistently above 80%. When I look at the plan cache its all the queries performing the checks getting new plans. What do I need to do lower this percentage?

2

Hi,

Thanks. I noticed that too whilst building a new version. Interestingly the new version has very little overhead and is now able to find all sorts of problems with the old version. Anyway, back to the problem - I haven’t gotten to looking at this problem yet. I’m guessing it’s because of dynamically generated SQL with lots of parameters. You try adding (OPTION KEEP PLAN) but I can’t guarantee this will fix anything. Alternatively you could just turn off checks.

3

I think its the way the check statements are running under the sp_executesql. To test I updated line 166 in the [dbo].[usp_sqlwatch_internal_process_checks] sproc adding paraments for sql_intance and Last_check_date instead of using the replacement. I mass replaced the check_query with those two params as well. The plans that only used those two parameters acquired one plan.

exec sp_executesql @check_query, N'@Last_Check_Date VARCHAR(23),@sql_instance VARCHAR(32), @output decimal(28,5) OUTPUT', @Last_Check_Date = @previous_check_date, @sql_instance = @sql_instance, @output = @check_value output;

Now i’m noticing the queries that check the databases are getting multiple plans.

4

Yep that would have likely been the primary root cause.
You’re welcome to submit a PR with your changes.

Thanks!

5

I’m still keeping an eye on it. My SQL Compilations Rate still hasn’t dropped below 80%. I don’t know if that is because I had use baseline flag = 1

6

Embarrassingly, I can’t remember how the baselines work. I think the idea was that one could define periods to be treated as baselines, and then alerts would use those for comparison and alert on deviation. But can’t quite recall what the baseline flag = 1 does

7

I’m interested to see what you find out as well. I ended up adding the option keep plan to the action queries and doesn’t seem to be making a difference.

8

It appears to be when sp_whoisactive is scheduled to run.