Hot answers tagged

36

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 ...


29

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 ...


24

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 ...


15

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 ...


14

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 ...


12

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 ...


12

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 ...


9

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 ...


9

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/.


8

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" StatementOptmLevel="FULL" ...


7

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 ...


7

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. ...


7

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 ...


6

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 ...


6

Nothing that MySQL does will lead to stale data -- not the Query cache, not SQL_CACHE, not the key_buffer, not the buffer_pool. The problem must be outside MySQL.


6

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 ...


5

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 ...


5

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) ...


5

"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 ...


5

If your data can be pre-calculated and is static intra-day then why not? After all, isn't this what a data warehouse does? As for having up to 50 million rows, this is peanuts.


5

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 ...


5

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 ...


5

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. and The same behavior will occur ...


4

If by cache you mean shared_buffers, no, it won't be removed from there due to being changed. shared_buffers is the primary place where it does get changed. From there it will eventually get pushed out to the kernel, and then from there to disk.


4

Would clearing the database memory cache on a daily/weekly schedule be of use? The straight answer is No. This would be a very bad idea, I suggest you don't do this. So my thinking is that by clearing the memory at the end of the day, the memory use in the next day will only be relevant of what needed? Would this be suitable case of ...


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

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.


4

PostgreSQL doesn't have a result cache. So the only caching that happens is in shared_buffers and the kernel buffer cache - both of which must inhertently be filled in the process of generating the query results, so it must happen before (or while) results are sent. WAL is written as a transaction executes, and WAL archives get replicated as they're ...


4

MySQL Documentation has SQL_NO_CACHE option: Two query cache-related options may be specified in SELECT statements: SQL_CACHE The query result is cached if it is cacheable and the value of the query_cache_type system variable is ON or DEMAND. SQL_NO_CACHE The server does not use the query cache. It neither checks the query cache ...



Only top voted, non community-wiki answers of a minimum length are eligible