Hot answers tagged cache
18
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!
12
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 ...
10
You may be interessted in one of the mailing lists topics, it's answerd by Tom Lane (core dev):
[..] But my opinion is that people who
think they are smarter than an LRU
caching algorithm are typically
mistaken. If the table is all that
heavily used, it will stay in memory
just fine. If it's not sufficiently
heavily used to stay in memory
...
9
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 ...
7
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 ...
5
You should just disable the query cache with
[mysqld]
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 ...
5
Your question can be basically rephrased as 'How does the query memory grant work?'. A good read on the subject is Understanding SQL server memory grant. Before a query is launched into execution it may require a memory grant for sorts and hashes and other memory hungry operations. This memory grant is an estimate. Based on current system state (number of ...
5
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.
...
5
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 ...
4
MySQL current 5.1 and 5.5 versions do not cache subqueries. Only whole queries. Subqueries are not processed as a separate item and the execution planned created is for the whole query.
MariaDB (a MySQL fork), version 5.3 has an optimization feature that does exactly that: Subquery cache.
If I am not wrong a similar feature will be incorporated in MySQL ...
4
Your caching layer sits between Model and View Controller. You should not hit database for needless requests. These requests include in my opinion.
Almost all lookup tables. You read city and state list already. Why go to database again.
Facts used in almost every page. if you show User's detail every page. Hit to Database once and cache it.
Slow queries ...
4
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) ...
4
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 ...
3
I suggest a different tack altogether. Instead of naming 18,000 parameters why not make use of table-valued parameters? I'm making some leaps here about what exactly you're using all these parameters for (since you so handily anonymized them for us :-)), but if you create these types:
CREATE TYPE dbo.VarcharParameters AS TABLE
(
ParamName SYSNAME,
...
3
Considering how many parameters of the same type you have, the best workaround that I can think of would be to just use a temporary table to pass in all of those nullable varchar(100) parameters.
The following demonstrates how this would be done with the example that you gave:
alter PROCEDURE [dbo].ObviouslyAnonymizedProcedure (
@SchemaId int = Null
...
3
If I understand your question correctly, your examples are just storing the vcount of visitors (visitors_stored) from today.
This means
You'll want a script to reset the visitors_today each day at midnight.
You'll want a trigger on visitors_stored to update the visitors_today table.
Why would you do it? If you regularly wanted to know how many visitors ...
3
with bd as (
select count(*) as pages_in_memory, bd.allocation_unit_id
from sys.dm_os_buffer_descriptors bd
where bd.database_id = db_id()
group by bd.allocation_unit_id)
select p.object_id,
p.index_id,
p.partition_number,
bd.pages_in_memory,
au.total_pages as pages_on_disk,
au.type_desc
from bd
join sys.allocation_units ...
3
Make sure that you have at least 2 unused API slots ([mysqld] sections) in your configi.ini file (and perform a rolling restart of your Cluster after adding them). These slots will then be used by memcached.
Note that you can choose to run mysqld processes on the host as well and have them part of the cluster (can access the same data through SQL and NoSQL) ...
3
When you say "same params and all" then you may find different credentials were used or the same compiled plan wasn't re-used based on timing.
I suspect here that the first web call and the SSMS call used different plans. The DBCCs then cleared the plan, so the second web call worked as expected
There are criteria that determine plan re-use such as using ...
3
cached results are invalidated and regenerated when transactions occur
against the underlying data
I am fairly confident the same would be true for DDL changes but did you mean DML? The long and short is that Oracle is not going to allow an inconsistent result.
3
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 ...
3
MyISAM does not cache data at all. It caches only indexes ( See my post What are the main differences between InnoDB and MyISAM? )
You could just set key_buffer_size to something absurbly small, like 8 (the minimum allowed). You may as well disable the query cache while you're at it (Setting query_cache_size to 0).
Just add these lines to /etc/my.cnf
...
3
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 ...
2
You could do SELECT SQL_NO_CACHE. This is used by mysqldump to always dump fresh data to a text file. This may also help flush the innodb buffer pool if the table being SELECTed is InnoDB.
If you cannot maniuplate the SELECT statements, then set query_cache_type to 0. That will make all SELECTs behave like SELECT SQL_NO_CACHE. You can do this without ...
2
You're probably using one of these generic PHP cache accelerators:
http://en.wikipedia.org/wiki/List_of_PHP_accelerators
I find apc and eaccelerator most used in the wild.
There are other PHP caches also available with each framework or app being used, e.g., Wordpress using wp-cache or w3 total cache, smarty templates using smarty cache, etc..
You say ...
2
There are some restrictions with database links. You can't execute DDL remotely for example.
Queries run against remote tables are not processed in exactly the same way as regular tables:
The local Oracle Database server breaks the distributed query into a corresponding number of remote queries, which it then sends to the remote nodes for execution. ...
2
First, I'd step back and ask what measurements you plan to collect during the test. If you're counting logical reads by query, for example, then you don't need to free the cache. I'm a big fan of using logical reads because it's independent of whether the data is cached or on disk - and in production, it's hard to guess whether a query's data will be ...
2
This question currently reads like a solution looking for a problem. You've decided that a RAM disk is the solution and you want someone to validate that choice. Sorry, not going to happen.
If you have measured and observed a spill to tempdb, it will almost certainly be due to a sort or hash operation and an insufficient query memory grant. Depending on the ...
2
What you haven't mentioned is what kind of queries are run against the database and if there are right indexes to speed up the performance of your queries.
You also need to make sure if there are any other applications running on the same box. Even though the box has 32 GB of RAM, have you set any max memory setting on the database server to put any ...
2
The only thing that is definitively cached for MyISAM is an index.
The key_buffer_size variables sets up how large the MyISAM Key Cache will be.
There are two suggestions I can offer
SUGGESTION #1 : Use a Dedicated Key Cache for the Table
Did you know you could create a keycache dedicated to one or more MyISAM tables? Suppose you have a table called ...
Only top voted, non community-wiki answers of a minimum length are eligible
