Questions tagged [cache]
A cache is a mechanism for temporarily storing (caching) data in memory in order to reduce access time to data stored on disk drives.
173
questions
50
votes
5
answers
35k
views
Why would I NOT use the SQL Server option "optimize for ad hoc workloads"?
I've been reading some great articles regarding SQL Server plan caching by Kimberly Tripp such as this one:
http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/
Why ...
- 1,973
43
votes
6
answers
56k
views
PostgreSQL: Force data into memory
Is there a systematic way to force PostgreSQL to load a specific table into memory, or at least read it from disk so that it will be cached by the system?
- 11.5k
43
votes
3
answers
18k
views
Parameter Sniffing vs VARIABLES vs Recompile vs OPTIMIZE FOR UNKNOWN
So we had a long running proc causing problems this morning (30 sec + run time). We decided to check to see if parameter sniffing was to blame. So, we rewrote the proc and set the incoming ...
- 3,396
24
votes
3
answers
11k
views
Is the overhead of frequent query cache invalidation ever worth it?
I'm currently working on a MySQL database where we are seeing a large number of invalidations from the query cache, primarily because of the high number of INSERT, DELETE and UPDATE statements that ...
- 395
19
votes
2
answers
13k
views
PostgreSQL Index Caching
I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions:
PostgreSQL indexes, like rows, live on disk ...
- 873
15
votes
4
answers
99k
views
Memory Usage by SQL Server
How do i check memory usage by my SQL server in production box.
I am using SQL Server 2016.When ever i check task manager,it shows above 90%.
I don't think that is the real memory usage by sql server.
...
- 1,295
14
votes
1
answer
31k
views
Difference between FreeProcCache and FreeSystemCache
Currently I run DBCC FREEPROCCACHE when I want to wipe information out of the buffer pool between running SQL queries. However, I was reviewing this Technet article referencing DBCC FREESYSTEMCACHE. ...
- 2,226
12
votes
2
answers
2k
views
Find uncompressed size of all tables in a database
In Dynamics AX there is a caching mechanism where tables can be configured to be loaded into memory and cached. This cache is limited to a certain amount of KB to prevent memory issues. The setting I'...
- 15.6k
11
votes
1
answer
13k
views
Qcache_free_memory not full yet I get alot of Qcache_lowmem_prunes
I just started dabbling with the query cache for our CMS.
Can anyone tell me (or at least give a good guess) why I get a lot of Qcache_lowmem_prunes when more than half of Qcache_free_memory is free?
...
- 1,452
11
votes
1
answer
1k
views
Definitive list of steps for SQL Server baseline testing?
Before running a performance test / baseline for an app that uses SQL Server, I want to be able to set the instance to a "clean" state, without restarting the instance. There are steps I tend to ...
- 2,679
10
votes
1
answer
3k
views
Why does a SELECT statement dirty cache buffers in Postgres?
I am running EXPLAIN (ANALYZE, BUFFERS) SELECT ... in my Postgres 9.3 server. I end up seeing something like Buffers: shared hit=166416 dirtied=2 in the output.
From the documentation, "dirtied" ...
- 255
10
votes
3
answers
6k
views
How to boost performance of virgin queries in MS SQL Server?
I have ASP.NET web site that does it's own independent caching of data and data does not change for long periods of time, so it does not need to query SQL Server second time with same query. I need to ...
- 255
10
votes
1
answer
616
views
Where does an execution plan come from?
Is there a way I can determine if a plan was just generated for a particular query, or was found in the plan cache?
- 4,757
9
votes
1
answer
2k
views
How much memory is a table taking up?
Is there a way to find out how much memory a table is taking up in SQL Server (2005 and greater)?
For example, suppose I have a table with 30 million records. I'd like to know how many pages ...
- 2,323
9
votes
1
answer
834
views
SQL dumping all pages from buffer cache every few minutes
I have a single SQL2012 SP4 node running several databases.
The server has 20GB of memory available, 14GB allocated to SQL (nothing else running on the box).
Every few minutes SQL dumps the whole ...
- 231
8
votes
3
answers
7k
views
Does restarting the SQL Services (on the machine) clear the server cache (for things like query plans and statistics)?
Does the server cache get wiped (similarly to when you restart the SQL instance / machine) when you only restart the SQL Services themselves?
- 35.1k
8
votes
3
answers
2k
views
Even after Disabling Read-Ahead and clearing buffer , why there is Logical Read?
I have to find the total time taken for query to execute in SQL-SERVER 2016 when all the data pages should be in Disk only.
To accomplish that , I cleared all buffers in the SQL-SERVER using the ...
- 93
8
votes
2
answers
41k
views
query_cache_type = 0 vs query_cache_type = 1
Recently, I moved from standard MySQL to Percona, and used the Percona Wizard to generate my.cnf.
However, I can see that, by default, the generated settings for my.cnf use query_cache_type = 0. (...
- 267
8
votes
1
answer
133
views
Does MySQL/InnoDB use table caches when dealing with foreign tables?
TL;DR: Does a table referred to via schema.table get held in table cache?
I have a large number of client databases that our application connects to based on an http request. These are used for every ...
- 251
7
votes
2
answers
3k
views
How SQL Server caches data in memory
I have couple of questions on how caching of data works
Imagine we have below situation:
Server restarted or we just ran DBCC DROPCLEANBUFFERS
We have a Table1 that is 50 GB and has columns A, B, C, ...
- 5,472
7
votes
2
answers
4k
views
How to calculate cache misses for PostgreSQL
I'm administering a server where a tool using a PostgreSQL runs. The tool takes care of most PostgreSQL configurations by itself, but I'm observing some performance problems. I could confirm at OS ...
- 171
6
votes
2
answers
2k
views
AWS Aurora PostgreSQL Serverless: How do you pre-warm the shared buffer after scaling?
I'm using AWS Aurora PostgreSQL Serverless with autoscaling.
It appears as though scaling clears the shared buffer, so right when we want to crank out the performance, we are forced to face-plant with ...
- 163
6
votes
1
answer
756
views
Procedure Cache Dropping
We've experienced an issue with SQL Server dropping proc cache out of the blue.
I'm talking 4GB down to 0 in 2 minutes. This has occurred approx once per day in the last two weeks.
It does happens ...
- 194
6
votes
1
answer
5k
views
How bad is query plan pollution (too many equivalent plans in cache)?
Question currently affects C#/.NET, DB access based on ADO.NET and SQL Server 2008 R2, but I think it applies to other databases as well.
I noticed some old modules of a system have non-optimal SQL ...
- 500
6
votes
2
answers
4k
views
Sudden performance decrease in SQL Server
I've got a problem with a SQL Server 2008 R2 instance. Last week the performance suddenly broke down. All inserts are very very slow. It takes 4-5 times longer than before. On the hardware we found no ...
- 61
6
votes
2
answers
393
views
mysql performance / cache configuration enigma [closed]
I have two mysql 5.1 instances (say A, B) hosting the same database schema. If I run (with mysql workbench) the same query on both instances I don't understand why I get very different response times ...
- 161
5
votes
5
answers
10k
views
Why is MySQL caching data on our dynamic PHP site?
We are having an annoyance with our PHP/MySQL site in that it seems to cache data from the database and our PHP pages are showing out of date records. In fact, there are pages that link to URLs that ...
- 53
5
votes
1
answer
266
views
Non-deterministic performance of query on select, from 1s to 60s on table with 1 billion rows
I'm trying to investigate why the performance of this query is so non-deterministic. It can take anywhere from 1 seconds, to 60 seconds and above. The nature of the query is to select a "time ...
- 53
5
votes
1
answer
2k
views
Pre Caching Index on a large table in PostgrSQL
I have a table with about 10mln rows in it with a primary key and an index defined on it:
create table test.test_table(
date_info date not null,
string_data varchar(64) not null,
data ...
- 181
5
votes
4
answers
3k
views
Is our MySQL poor performance related to excessive daily query cache prunes?
We are experiencing a high number of query cache prunes per day, currently 80851746. MySQL really struggles even under a reasonable load of say 50+ concurrent connections.
The host is a Physical ...
- 51
5
votes
1
answer
2k
views
Performance considerations for Caching Aggregate counts
We have an InventoryActivity table that holds transactional changes to item quantity:
CREATE TABLE dbo.InventoryActivity(
InventoryActivity_uid int IDENTITY(1,1) NOT NULL PRIMARY KEY,
...
- 159
5
votes
1
answer
2k
views
How can I identify queries that are producing cache misses in Postgres?
I have a Postgres database with a total data size of 115GB. The server has ~60GB of memory. The index cache hit rate is holding at 99%+ but the table cache hit rate has fallen to ~97%.
I am trying ...
- 255
5
votes
1
answer
1k
views
To minimize Cache misses in PostgreSQL?
You can calculate cache misses as described here.
However, I am interested in how to minimize the phenomenon in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, ...
4
votes
1
answer
704
views
Does PostgreSQL always sequentially scan pages in the same order?
Yet another question from my discovery of the great new EXPLAIN options in PostgreSQL. This one focuses on the BUFFERS option.
Here is EXPLAIN:
EXPLAIN (ANALYZE, BUFFERS) SELECT event_time FROM ...
- 189
4
votes
1
answer
1k
views
Reindexing and its effects on SQL cache
Currently looking into reindexing our sql database, and cannot find any information on whether or not the procedure cache would be effected. Any help or information would be great. We are using SQL ...
4
votes
1
answer
758
views
Buffer usage for index with no reads
Using SQL Server 2008 R2, the main transactional table in a vendor database is massive compared to all others and has about 14 indexes. Some of these indexes don't get used in our environment, but we ...
- 143
4
votes
2
answers
372
views
MySQL: Is it safe to have a table with multiple millions of rows?
I have a very complex search query to write that will touch many tables, have conditions and joins. For efficiency reasons, I am strongly considering creating a "cache table" where I would maybe once ...
- 303
4
votes
1
answer
2k
views
user permission cache in session or check from database?
On each page of my websites 2 or 3 permissions have to be checked and I use role-permission model. Which one is better? to check permissions of each user always through database directly or once ...
- 61
4
votes
2
answers
5k
views
Does Oracle cache queries when using a database link
Some vendor told us that we should not use database link between our database and theirs as the queries executed over a database link would not be cached, nor optimized.
I can't find any information ...
- 143
4
votes
1
answer
3k
views
Can I partially invalidate the MySQL table cache?
I want to set a fairly large table to MyISAM mode to keep it cached proactively. The table file is approximately 3GB.
After several hours, MySQL invalidates the entire table from cache, while < ...
- 41
4
votes
2
answers
19k
views
Is it possible to delete cache in Mongo?
My case is that I run several queries in Mongo and measure the response time. I know that Mongo uses Memory Mapped files and cache some data in RAM.
If I create an index and run a query the execution ...
- 175
3
votes
1
answer
19k
views
What is wrong with table_cache hit rate?
In my.cnf I have:
table_cache = 524288
open_files_limit = 65535
Both are at max allowed value for mysql config. Both are less than max open file limit:
# cat /proc/sys/fs/file-max
...
- 215
3
votes
2
answers
2k
views
Can I tell MySQL what to cache?
Pretty straightforward question:
Let's say I have a specific query that I know is regularly used, can I tell MySQL to always cache it "automatically" ... i.e. once the cached query results are ...
- 363
3
votes
2
answers
1k
views
MySQL cache is not getting hit by subqueries
I noticed something weird on MySQL query_cache behavior and I would like to know if this is a normal behavior.
Let's say I have an item table
ID | Item
-----------
1 | Item_1
2 | Item_2
3 | ...
- 2,248
3
votes
1
answer
10k
views
Lowering MySQL Memory Usage without Restart
We are using Percona Server 5.5.35-rel33.0-611.precise with InnoDB in our production server. Recently Mysql memory went high and it did't drop from there. We are afraid whether it would result in OOM ...
- 31
3
votes
1
answer
1k
views
MySQL: can a table get stuck in the table cache?
I've asked this question on the ServerFault stackexchange, someone directed me here.
I'm a PHP developer and am responsible for a set of PHP scripts that run on a shared host running FreeBSD, that ...
- 153
3
votes
1
answer
4k
views
Mysql: Tell InnoDB to keep a specific table or database out of pool/cache?
I have some tables that are used primarily to keep historical data, or that are only used once a day.
I do not want any operations on these tables to disrupt the more important tables present in ...
- 95
3
votes
1
answer
116
views
SQL Server Cache population on server / service startup
We have upgraded our SQL Server instance with buckets of RAM, so it can now much more effectively cache . The issue is that it takes time (days) to populate the cache. As far as I understand this is ...
- 297
3
votes
1
answer
358
views
Postgres caching and bytea
I have a DB which, among other things, stores images (as bytea, if that's interesting). It also stores users' metadata, activity logs, etc., but images currently take 500MB out of the total 600MB data....
- 131
3
votes
0
answers
579
views
Should Azure host write-caching be enabled when using MongoDB's WiredTiger storage engine
I have several virtual machines on Azure running a mongoDB replicaSet with the WiredTiger storage engine. I'm not sure about the proper host caching option to set on the disks that contain the MongoDB ...
- 31