The SQL Server development team work on the principle of least surprise - so SQL Server generally has new features disabled in the interests of maintaining behaviour as previous versions.
Yes, optimize for adhoc workloads is great at reducing plan cache bloat - but always test it first!
[Edit: Kalen Delaney tells an interesting anecdote that she asked one ...
The query is
SELECT SUM(Amount) AS SummaryTotal
FROM PDetail WITH(NOLOCK)
WHERE ClientID = @merchid
AND PostedDate BETWEEN @datebegin AND @dateend
The table contains 103,129,000 rows.
The fast plan looks up by ClientId with a residual predicate on the date but needs to do 96 lookups to retrieve the Amount. The <ParameterList> section in ...
Postgres 9.4 finally added an extension to preload data from relations into the OS or database buffer cache (at your choice):
This allows reaching full operating performance more quickly.
Run once in your database (detailed instructions here):
CREATE EXTENSION pg_prewarm;
Then it's simple to preload any given relation. Basic example:
So, my question is this... how can parameter sniffing be to blame when
we get the same slow query on an empty plan cache... there shouldn't
be any parameters to sniff?
When SQL Server compiles a query containing parameter values, it sniffs the specific values of those parameters for cardinality (row count) estimation. In your case, the particular values ...
What caches does FREESYSTEMCACHE wipe that FREEPROCCACHE doesn't?
There are many system caches available in SQL Server. I am referring to SQL 2008R2 (as I tested on it).
Below query will return all the caches available :
--- ONLY for Educational purpose. Don't attempt to run on PROD !!
select 'DBCC freesystemcache ('+''''+name+''''+')' from sys....
The query cache is a very nice feature, but don't be tempted to pay too much attention to it and don't be tempted to make it too large. Understanding some of its internals will probably help in that regard.
The query cache starts out as one big contiguous chunk of available memory. Then "blocks" are carved out of this big block:
each cached query takes a ...
Below is a little code that will help you decide if "switching optimize for ad hoc workloads ON/OFF" will be beneficial or not. We normally check this as a part of our health check for in-house and client servers.
It is the safest option to enable and is described well by Brad here and by Glenn Berry here.
--- for 2008 and up .. Optimize ad-hoc for ...
Playing a bit with pg_buffercache, I could get answers to some of your questions.
This is quite obvious, but the results for (5) also show that answer is YES
I am yet to set up a good example for this, for now it is more yes than no :) (See my edit below, the answer is NO.)
Since the planner is who decides whether to use an index or not, we can say YES, it ...
'I am trying to determine if memory pressure is an issue to some of my problem.'
very useful script:
you see verbose memory utilization:
SQL Server 2017 Diagnostic ...
You should just disable the query cache with
query_cache_size = 0
and then restart mysql. Why would I suggest that ???
The Query Cache will always butt heads with InnoDB. It would be nice if InnoDB's MVCC would let queries be served from the query cache if modifications do not affect repeatable reads for other transactions. Unfortunately, InnoDB ...
One thing, here, is that you should be using this form, instead:
mysql> show global status like '%open%';
Some of these counters are global and some of them are session, so not using the GLOBAL keyword gives you a split set of numbers (especially the Opened_table* values).
The problem with tuning scripts is they can't possibly take into account all of ...
The behavior is no different between restarting the service alone or restarting the service due to rebooting the underlying operating system. Which information is wiped on such a restart?
Query plans? Yes.
Table/index data? Yes.
(Query plans, which use statistics, will have to be recompiled, but statistics won't have to get re-created ...
A logical read is counted when a single page is retrieved from buffer cache during query execution. This is counted regardless of whether a physical or read-ahead was used to cache the page, or if the page already existed in the buffer cache. Consequentially, logical reads is a measure of how many times pages were actually touched in memory during query ...
Can someone direct to a good/proper explanation.
I would start by saying Task Manager is not a correct place to gauge SQL Server memory consumption, it will not tell you correct value when SQL Server service account has Locked Pages in Memory(LPIM) privilege. This is because normally task manager tracks Process Private bytes which is pageable memory and ...
This has a simple reason.
In PostgreSQL a row has to go through a visibility check. On the first read, the system checks if a row can be seen by everybody. If it is, it will be "frozen". This is where the writes come from. Similarly, VACUUM also sets bits.
There is a detailed explanation: http://www.cybertec.at/speeding-up-things-with-hint-bits/.
Index pages are fetched when a query decides they will be useful to cut down on the amount of table data needed to answer a query. Only the blocks of the index navigated to accomplish that are read in. Yes, they go into the same shared_buffers pool where table data is stored. Both are also backed by the operating system cache as a second layer of caching.
SQL Server 2012 has an indicator in the plan itself, RetrievedFromCache, which can be either "true" or "false".
This appears to be the property you are asking about.
This is a sample (the last line shows the property):
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1"
CREATE TABLE dbo.Table1( A INT IDENTITY(1,1) PRIMARY KEY NOT NULL
,B varchar(255),C int,D int,E int);
INSERT INTO dbo.Table1 WITH(TABLOCK)
SELECT TOP(1000000) 'Some Value ' + CAST((ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) % 400) as varchar(255))-- 400 different values
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
When you turn the "Optimize for Ad Hoc Workloads" option on, you will cause ad-hoc queries that are run the 2nd time to be just as slow as the 1st, because you will be Compiling an Execution Plan and pulling the same Data (without it cached) those first 2 times.
This may not be a big deal, but you'll notice it when testing queries.
So what happens now, ...
Think of a production server that serves only 5 different queries, but several thousand of those per second. You are the Microsoft SQL Server development team. You are going to fiddle with plan caching. Do you turn this behavior on by default, when you know that some of your largest and most critical clients (e.g., Microsoft's internal SAP implementation) ...
I need the actual size the uncompressed data will have.
I would prefer to have size as correct as possible.
While the desire for this information is certainly understandable, getting this information, especially in the context of "correct as possible" is trickier than everyone is expecting due to faulty assumptions. Whether doing the uncompressed ...
There will always be logical reads. SQL Server never returns data to you directly from disk. If the page you need is already in the buffer pool, there is a logical read. If it’s not, it will be moved into the buffer pool, and then there is also a logical read. You will always get the read coming from cache.
Also, I don’t understand the point of your test. ...
Execution Plan Caching and Reuse lists some of the factors that trigger recompilation:
Recompiling Execution Plans
Certain changes in a database can cause an
execution plan to be either inefficient or invalid, based on the new
state of the database. SQL Server detects the changes that invalidate
an execution plan and marks the plan as not valid....
"How bad is it?" depends on the degree to which you are suffering now or could suffer with increased workload in the future.
One major point of suffering with plan cache pollution could be too many single use plans bloating your plan cache leading to inefficient cache usage.
Another point of suffering could be high compilations/second - so in an ...
One reason could be somebody changing options or running sp_configure. That would be logged in your error log.
Please read this article: Using Sp_configure To Change a Value Will Issue DBCC FREEPROCCACHE
using sp_configure to change a configuration value causes SQL Server
to issue a DBCC FREEPROCCACHE statement.
The same behavior will occur if ...
From your question it seems as if you have a maximum cache size S and you don't want to load tables into the cache that exceed that size. If that's true then you don't need to know the exact size of each table. You just need to know if a table is bigger or smaller than the maximum cache size S. That is a significantly easier problem depending on your tables' ...
For a Wordpress blog it should be fine to set query_cache_type = 1. See, the major problems with the query cache are:
It invalidates very easily (any update on some table invalidates all queries related to said table)
It has a single mutex on which any incoming or outgoing query must go through.
The query cache was fine in the days where machines had one ...
Sql Server's Buffer Pool is a wonderful thing. It's smart enough to handle all sorts of situations in a fairly intelligent way. Here are a couple examples showing how at first glance the buffer pool behavior seems strange, but is actually fairly clever.
Consider a 400 GB clustered index on a server with 64 GB of Memory available for the buffer pool. If a ...
MySQL Documentation has SQL_NO_CACHE option:
Two query cache-related options may be specified in SELECT statements:
The query result is cached if it is cacheable and the value of the
query_cache_type system variable is ON or DEMAND.
The server does not use the query cache. It neither checks the query
cache to ...