Tag Info

Hot answers tagged

21

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!


19

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


14

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


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

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


6

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

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


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


4

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


4

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


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


4

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


4

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


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

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

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


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

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

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


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


3

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



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