Tag Info

Hot answers tagged

25

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


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!


17

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


10

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


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

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


7

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


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

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

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

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

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

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

"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

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.


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

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

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

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


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

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

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

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

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

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

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



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