4 ms·
SQL server has had transactional DDL for as long as I remember. MSSQL also uses per session settings as part of the cache key. This can be confusing if you thi
by SigmundA 7y ago
SQL server has had transactional DDL for as long as I remember.
MSSQL also uses per session settings as part of the cache key. This can be confusing if you think its only based on statement text and wondering why two different client are getting different plans, its because they have different settings.
The planning overhead would completely destroy some applications I have worked on. Some more complex queries execute extremely fast but take some time to plan. Its hard for me to believe planner is so much faster as to be a non issue.
Global caches are key to db performance thats why PG has shared buffers. MSSQL is single process multithreaded though while PG is process per connection so I would imagine that complicates shared data structures somewhat.
- gfody 7y agothe penalty for using a cached plan that is no longer optimal can be devastating, I've often had to use "with recompile" to get around this on SQL Server - I don't think global plan cache is a no-brainer, the scenario you describe is probably better served by a short-lived results cache
- cogman10 7y agoOr worse, I've ran into cases where just enough "bad" parameters flow into the planner to topple the statistics on a previously well behaving stored proc. SQL server does not recover quickly after a cached plan goes wrong. More than once, either adding "WITH RECOMPILE" or manually recompiling a SP has fixed our performance issues. "Everyone has a plan until they get punched in the face" - Mike Tyson
- SigmundA 7y agoI have rarely run into this issue, but they do occur which is why WITH RECOMPILE exists. The majority of the time caching the plans is a benefit and therefore has been the default behavior with a opt out option. With that kind a of plan instability I start looking at query hints though which PG doesn't have either...