SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[11.5.355 Std] - Fetching schema objects is too frequent
Goto page Previous  1, 2, 3
 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[11.5.355 Std] - Fetching schema objects is too frequent
Author Message
BrendonOSullivan



Joined: 02 Mar 2021
Posts: 24
Country: New Zealand

Post Reply with quote
Hi Sysop,
you are 100% correct, replacing :SCHEMA_ID with $SCHEMA_ID$ immediately makes the queries for cache refresh run in batch mode instead of as remote procedure calls with sp_executesql.

This screenshot was captured with the sql profiler and shows the change in "Event Class".


Once I started using the updated query with $SCHEMA_ID$ then the initial runs were slow for each database. For each 2nd and all subsequent cache-refreshes the runs are always fast, even after restarting Microsoft SQL Server Management Studio multiple times.


The screenshot shows that I tested with 4 databases. The initial cache refreshes take between 27 and 52 seconds but afterwards it never takes longer than 2.5 seconds, sometimes just 1s!

Interestingly the long, initial runs show 2 writes to the SQL server which shouldn't be necessary since this query modifies no data. Perhaps the profiler has caught the act of storing the execution plan in one of the system databases. In all of the fast runs of ~2 seconds there are never any writes, always 0.

I experimented with reverting to :SCHEMA_ID and this immediately returned to the RPC call. Interestingly it is also fast now and I can't make it run slowly, no matter how many times I restart the Management Studio! So there may be some permanent improvement to even the RPC calls after running in batch mode. It is hard to say though since sometimes SQL Server takes a few hours or days to return to its bad habits.

In any case your suggestion to use $SCHEMA_ID$ appears to be working as intended and I will watch and observe how it continues to perform in the next few days.

Thanks for this suggestion!
Thu Mar 18, 2021 10:54 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Excellent! Thank you so much for the provided details, and happy that we have managed to find a satisfactory solution.

Just in case, SQL Server 2016 introduced paramers sniffing feature that enables better query optimization for queries with parameters. Your expected upgrade to SQL Server 2017 would make this change in the settings obsolete. I would recommend reverting to the original queries after the server upgrade in order to avoid potential compatibility issues with future SQL Assistant upgrades.
Fri Mar 19, 2021 12:26 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post Reply with quote
It's good to know about the difference between :x and $x$ when using ADO connection. What you experienced (long first run, short subsequent ones) is exactly what you should expect for every query. The definition of long is varying, of course.

Hopefully, I'm wrong, but I don't think that would be the root cause, because if the parameter sniffing caused the problem, the bad performance would have returned as soon as you restored the original query. Besides, ps will only cause issues when using the cached plan with parameters other than it was sniffed with, so it would still perform well when called for the same database/schema. I also recollect parameter sniffing for stored procedures to be much older than 2016. The query Brendon caught with the Profiler
Code:

exec sp_executesql N'whateverwhatever'',N'@b0 int,@b1 int,@b2 int',@b0=1,@b1=1,@b2=1

is what could use a parameter sniffing for eg. the sp_executesql and you have no way to perfectly avert that because even if you include RECOMPILE in the modified DB Query "Objects + Typed Synonyms", that will also be wrapped into the sp_executesql. Aside from that, the negative side-effects of the parameter sniffing should have gone away when using RECOMPILE.

Regardless, the execution time of >= 25 seconds for that query is still awful. It would be interesting to see the exec plan and query times if there were indexes on the system tables that would support that query (don't even try that on a production server, please). The exec plan Brendon posted has two clustered index scans and only one seek with a pretty bad guess for estimated vs actual rows, probably because of outdated statistics.

As for when the SQL Server will be back to its bad habits, that depends on the load and the Page Life Expectancy metric. If that value is low, and you're not doing a cache refresh before it expires, you shall expect the next refresh to be slow because the pages containing the requested data might no longer reside in the buffer pool and have to be reloaded from disk.

PLE (accompanied with other interesting metrics) can be checked in the Performance Dashboard / Key Metrics / Buffer Cache Hit Ratio / Page Life Expectancy. The Dashboard tab says it should be above 300. I recall some debates in articles I've read years ago that question this recommendation saying it was once true and is outdated now, but cannot remember what should be viewed as a good value.

If you cannot access the Performance Dashboard, you can get it executing this query:
Code:

SELECT
    [object_name]
    ,[counter_name]
    ,[cntr_value]
FROM
    sys.dm_os_performance_counters
WHERE 1 = 1   
    -- AND [object_name] LIKE '%Manager%'
    AND    [counter_name] = 'Page life expectancy'



For comparison, we have a server having it as low as 38 and that server does experience memory pressure. We also have other servers having that value well above 400000. Those servers have plenty of memory for their needs.

By the way, SysOp, if the version using $SCHEMA_ID$ is seemingly superior to the one using :SCHEMA_ID, why not use that one as a default for the DB Query?
Fri Mar 19, 2021 6:42 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Goto page Previous  1, 2, 3
Page 3 of 3

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.