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.

Filter by
Sorted by
Tagged with
-1
votes
0answers
42 views

SQL server query is very slow [closed]

My server have 2 instance: 1 version 2012 and 1 version 2017. Sometimes, instance sql 2012 (or sql 2017) execute query slowly. Cause: cache have problem but i don't know what is it. I use "select * ...
1
vote
1answer
22 views

Caching derived dataset automatically in SQL Server

I have a view that combines multiple tables via UNION ALL, and handles some basic errors with the data. Due to the way that the errors are handled, the view is a bit slower than I would hope. Is there ...
0
votes
1answer
23 views

Is it possible to index or cache non-immutable function (based on date)

I have a function in my Postgres database where the calculation is based on how old a record is - so given the same parameters, it always outputs a different value. However, I'd like to index or ...
0
votes
1answer
11 views

SSRS Report returning old data

I have created and tested a report in Visual Studio and it returns data from a SQL server database. Once uploaded to SSRS, the report returns old data. What would I need to check as it appears the ...
1
vote
2answers
32 views

How to clear SQL Agent Job Scheduler Cache

I have to submit tickets to alter job scheduling, so rather than a walk through of the GUI I try to update msdb..sysschedules via tsql script. I always see the alterations immediately via the GUI. ...
0
votes
0answers
30 views

Firebird begin backup and forced writes off, timestamp update delayed

We have a Firebird database that's almost 200 Gbyte in sice, and for performance we have forced writes off. Please don't debate the risks of forced writes, we are aware of them. This is Firebird 3.0.4 ...
2
votes
1answer
49 views

Can we set the fixed size for both plan cache and data cache?

As far as I know, SQL Server automatically manages the size for the components of buffer pool. I wonder if it is possible to set pre-defined size for both plan cache and data cache?
0
votes
0answers
44 views

DB2 iseries query without cache

Is it possibile to perform a query without using cached data? I need to test many similar queries and I need to compare process time. To make the measures comparable, I need to run each query without ...
6
votes
2answers
2k 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, ...
1
vote
0answers
83 views

Why did mongoDB reads from disk into cache suddenly increase?

This is a mongoDB replica set with 1 primary, 1 secondary and 1 arbiter. Suddenly the reads from disk into cache and cache page evictions increased 2x-3x and stay elevated since then (all metrics ...
2
votes
2answers
363 views

Does Log File contain both committed and uncommitted transactions?

My question is related to SQL Server architecture. Mainly log file. In the picture below it says that when transaction is committed, log record in LOG CACHE will be moved to Log file. Does it mean ...
0
votes
1answer
22 views

Amount of used cache / free cache

I need to find out how much cache is used, and if there is free cache available. Background: Queries on a production server take a lot of time when the server is under heavy load. I suspect that ...
0
votes
1answer
400 views

Result Cache disabled in Oracle 12c Enterprise - BYPASS status

I have two database instances, development and production that are fairly identical. On production, result cache is working fine, in development it is not. The value for the database parameter ...
0
votes
1answer
141 views

Prepare statements plan invalidation in postgresql

Postgresql caches plans for prepared statements and their subsequent executes. see: https://www.postgresql.org/docs/current/sql-prepare.html https://stackoverflow.com/questions/7142335/how-does-...
2
votes
1answer
67 views

PostgreSQL. Unstable execution time hugely exceeding planning time

PG version: 10.9 Schema: create table core_bundle ( id serial not null constraint core_bundle_pkey primary key, dt_add timestamp with time zone not null, dt_upd timestamp with time zone ...
-4
votes
1answer
354 views

SQL Server database slow performance [closed]

I have a SQL Server database in production, and typically the performance of the application connected the database is satisfying. For some days many clients were complaining about slow performance of ...
0
votes
1answer
139 views

How to cache inserted data with ProxySQL to avoid delays

we are using MariaDB 10.2 with Galera plugin. Cluster consists of 4 nodes - 3 are busy service complex queries and 1 is more for a backup. Recently we came across ProxySQL and this sounds very ...
8
votes
3answers
2k 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?
1
vote
2answers
184 views

Are Cataloge and relation cache in PostgreSQL per connection or global to the server?

I am trying to figure out if Postgres' relation and catalog cache are per connection or per server? I did find that plan caches are per connection. It would make sense for the cache to exist for as ...
9
votes
1answer
302 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 ...
0
votes
1answer
25 views

Redis has “commands” that mutate the various data types it can store. Why? Redis is primarily a cache

If you look at something like https://redis.io/commands#list, you will see that types that Redis stores and supports are fully mutable, atomically, within the data store's memory. This does not make ...
0
votes
1answer
29 views

Caching results of query for part of table that almost never change

In our database we have a table with a very large number of rows. Let's call it Table1. The greatest difficulty for us is a COUNT query to this table with some conditions (see example below): Query1 ...
1
vote
1answer
291 views

How does SQL Server determine how much memory to allocate to Data cache

SQL Server Standard Edition has a limit to how much memory it can use for the data cache. With that in mind, how does Enterprise Edition decide how much memory it can use for the data cache? Lets ...
0
votes
1answer
413 views

How does MySQL evict pages from the buffer pool?

I am trying to understand the internals of MySQL. I do know that MySQL's buffer pool management has an old/young page list as shows here. And I do know about the flushing of old dirty pages, such as ...
0
votes
0answers
154 views

no performance improvement with pgpool query result caching

I'm trying to add query result caching to a PostGIS database that's frequently hit with the same (expensive) spatial queries. Looks like pgpool-II is currently the only well-developed solution. ...
0
votes
1answer
63 views

Using a caching tool for a remote database

When the DB is on a remote server, is there any tool to cache frequent queries on the main server in order to reduce database connections? If there are any available, what features should I consider ...
1
vote
1answer
157 views

Is any caching mechamism used for non-persisted computed columns?

Does SQL Server cache the result of a non-persisted computed column so it can be reused without incurring cost of recalculation? Additional Context I've always given the advice that if using computed ...
0
votes
1answer
414 views

query_cache_type on mysql galera cluster

I was wondering could I turn on the query_cache_type for mysql galera cluster of two nodes. The scenario is : 1. Proxysql infront of two master-master galera(mysql) nodes 2. Inserts are coming ...
2
votes
0answers
325 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 ...
1
vote
1answer
354 views

Reduce query execution time in frequently updated tables

The current query takes approx 6-7 seconds. Data is been fetched from 7 tables. Tables are fully normalized, frequently updated but are mostly read approximately 100 times a day. The same query gets ...
2
votes
1answer
225 views

What can data cache contain besides pages from physical disk?

While setting statistics io option ON, I see this result physical_reads are 0 and read-ahead reads are 276 which means that 276 pages of that table which are on the disk drive were in data cache, but ...
0
votes
1answer
159 views

mysql - 35GB binlog files are cached in OS cache

I have a mysql database with 5.6. Binary logs are enabled and it has 5 slaves. GTID mode enabled. Maximum binlog file size is 1GB. sync_binlog = 0 When I check the free -gh total ...
0
votes
0answers
65 views

Does PostgreSQL do the caching like applications do? [duplicate]

Applications usually cache data on an implementation of hash data structures to speed up the query, typically Memcached or Redis. Does relational databases also do the same things internally? For ...
2
votes
1answer
63 views

Caching queries that last longer then then X seconds

Let's assume that I want work with query cache enabled. For example let's set it to 128mb value but my working enviorment is very random and dynamic, so cache is full in 1 minute after setting it. ...
11
votes
4answers
40k 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. ...
7
votes
3answers
1k 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 ...
2
votes
2answers
7k 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 ...
0
votes
0answers
294 views

Service broker stuck infinitely looping using outdated activation stored procedure

I was debugging and stuck a bunch of log statements into my activation procedure including a statement that logs the value of @@ROWCOUNT that is used in this manner: INSERT INTO ###Debug(SomeMessage, ...
0
votes
1answer
247 views

Am I clearing the query cache correctly?

I'm trying to optimize a query but need to make sure the query cache is cleared properly for obvious reasons, however it doesn't seem to be working. I ran a query the first time and it took about 3.5 ...
-1
votes
1answer
46 views

SQL Server 2012 cache system? [closed]

I have an application that uses SQL Server 2012. Even if I change a value directly in the database (not via an application), it takes time until the application sees those changes, even if the ...
1
vote
1answer
2k views

Reasonable values for thread_cache_size in relation with Connections [closed]

I want to know reasonable values for thread_cache_size in relation with the connections that you have. Which is the good proportion? And which proportions do you handle in relation with your clients ...
1
vote
2answers
4k views

How to set cache on windows 10 pro 64bit machine

When running Mongod I get the following WARNING: The file system cache of this machine is configured to be greater than 40% of the total memory. This can lead to increased memory pressure and poor ...
12
votes
2answers
1k 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'...
2
votes
0answers
708 views

MongoDB + WiredTiger cache usage stuck on 95%

I'm running a mongo 3.4.7 cluster (8 CPU, 120GB ram, primary + 2 secondaries) with ±130 open connections, each one of them to a separate DB with ±50 collections. After a few minutes of running, cache ...
1
vote
2answers
2k views

mysql performance tuning innodb_buffer_pool_size and warming the cache (select * vs select count(*))

I have a couple of questions about tuning a MySQL (5.5) database. It seems the clients database server has never had any performance tuning done. Upon reading around, it seems the one thing is to set ...
0
votes
0answers
98 views

Postgres and OS Cache Management

I would like to know about how the OS cache works for postgres table and index files. Let's say I have a table with 10 years of data, and commonly used data only in the last 1 year. This data is ...
1
vote
0answers
19 views

What kind of object is being checked against the cache when a new query arrives? [duplicate]

After reading several resources, it's not clear to me what kind of object is being checked against SQL Server Plan Cache when a new query arrives. I see 3 possible options: The pure query text. The ...
0
votes
1answer
200 views

How to calculate total memory used by one table?

I have innodb_buffer_pool_size=100GB; show table status where name='test_tb'\G gives Data_length: 4686086144 ...
5
votes
4answers
2k 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 ...
1
vote
1answer
396 views

Is it good sign? If SQL Server Buffer and Procedure cache hit ratio always showing 100%

If 100% Buffer and Procedure cache hit ratio is considered not good in general? I check Buffer Cache hit ratio with below query and it showing 100% (100.0000000000000) SELECT (a.cntr_value * 1.0 / ...