Database Administrators Stack Exchange
Database Administrators Stack Exchange

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.

Caching can be more generalized to refer to temporarily storing (caching) data locally in order to reduce access time to data stored far away. It can include CPU caching but for this Database Administrators site, it is primarily associated to disk caching. For databases, caching can occur on many levels, including SQL execution plans, metadata, stored procedures, and actual database data. Include tags for database vendor and release, and any appropriate related tags such as:

See also

176 questions
50
votes
5 answers

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 is there even an option to "optimize for ad hoc…
SomeGuy
  • 2,053
  • 8
  • 35
  • 48
45
votes
6 answers

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?
Adam Matan
  • 12,079
  • 30
  • 82
  • 96
43
votes
3 answers

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 parameters to variables so as to defeat parameter…
RThomas
  • 3,446
  • 6
  • 30
  • 48
24
votes
3 answers

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 are being executed on many of the tables. What I'm…
Craig Sefton
  • 405
  • 1
  • 4
  • 7
19
votes
2 answers

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 but may be cached. An index may be entirely in the…
davetapley
  • 953
  • 4
  • 9
  • 16
17
votes
4 answers

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. I have a SQL performance tool grafana which…
user9516827
  • 1,345
  • 3
  • 19
  • 41
15
votes
1 answer

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. What caches does FREESYSTEMCACHE wipe that…
Sean Long
  • 2,256
  • 5
  • 23
  • 32
12
votes
1 answer

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" indicates: The number of blocks dirtied indicates the…
D-Rock
  • 275
  • 2
  • 7
12
votes
2 answers

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'm talking about is called entiretablecache and…
Tom V
  • 15,752
  • 7
  • 66
  • 87
11
votes
1 answer

Qcache_free_memory not full yet I get a lot 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? query_cache_size=512M query_cache_limit=1M This…
Nifle
  • 1,472
  • 8
  • 17
  • 31
11
votes
1 answer

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 follow, but I want to build a definitive list that is…
Eric Higgins
  • 2,689
  • 1
  • 19
  • 25
10
votes
3 answers

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 improve performance of first time (virgin)…
alpav
  • 255
  • 2
  • 8
10
votes
1 answer

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?
Magier
  • 4,827
  • 8
  • 48
  • 91
9
votes
1 answer

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 of the buffer cache. Page life expectancy hits zero,…
Charleh
  • 231
  • 2
  • 7
9
votes
1 answer

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 belonging to this table are currently on the buffer cache,…
ivanmp
  • 2,323
  • 3
  • 24
  • 36
1
2 3
…
11 12