The tag has no wiki summary.

learn more… | top users | synonyms

21
votes
3answers
6k 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 ...
17
votes
5answers
6k 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 ...
11
votes
5answers
7k 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?
10
votes
1answer
613 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 ...
9
votes
2answers
2k 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 ...
8
votes
2answers
1k 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 ...
8
votes
3answers
4k 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 ...
6
votes
1answer
454 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 ...
5
votes
1answer
835 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 ...
5
votes
2answers
307 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 ...
4
votes
1answer
2k 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? ...
4
votes
1answer
399 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
1answer
236 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 ...
4
votes
2answers
172 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 ...
4
votes
2answers
344 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 ...
4
votes
1answer
176 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 < ...
3
votes
5answers
3k 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 ...
3
votes
1answer
3k 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. ...
3
votes
2answers
520 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 | ...
3
votes
2answers
4k 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. ...
3
votes
1answer
105 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 ...
2
votes
2answers
246 views

Stored procedure compilation blocking due to many parameters to sp_executesql

I am seeing blocking due to locks on compiling a particular stored procedure (as described in KB 263889). Basically, several processes are waiting with LCK_M_X on the same resource "TAB: ...
2
votes
2answers
2k 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 ...
2
votes
1answer
163 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 ...
2
votes
1answer
619 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 ...
2
votes
1answer
63 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 ...
2
votes
1answer
39 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 ...
2
votes
0answers
70 views

Stored Procedure slow after weekend

When I come to work on monday some stored procedures take several minutes to execute and STATISTICS IO shows a lot of PHYSICAL READS and READ AHEADS going on. However, after running the procedures ...
2
votes
0answers
220 views

Multithreading caching in SQL CLR

Is there any mechanism available to a SQLCLR assembly for caching that will still work when the assembly is registered as "safe"? The cache needs to be thread-safe, so it can be shared by multiple ...
1
vote
1answer
821 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 ...
1
vote
1answer
3k 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 ...
1
vote
1answer
124 views

Where do caching servers conceptually fit in my architecture?

Given the simple well-known architecture below: Where (conceptually) does the caching server(s) sit?
1
vote
3answers
232 views

getting error while starting memecached service with ndb engine

i am installed memcached library by following the steps in the internet. now there is a binary file created in bin folder of mysql now i exicuting the command memcached i am getting error like this ...
1
vote
2answers
155 views

Functioning of Oracle's Server result Cache

I am reading the Oracle concepts doc and have a doubt [about general cache mechanism ] : PL/SQL function code can include a request to cache its results. Upon invocation of this function, the ...
1
vote
1answer
73 views

PostgreSQL: cache, disk, replication. What order?

I am thinking of using two servers in Master/Slave replication I want to add following condition: RAM size = dataset size * x, where x > 2. After that, I plan to force PGSQL to keep all data in RAM ...
1
vote
1answer
300 views

Does MySQL's MyISAM use cache before insert?

I want to test two different Disks, one of them is HDD and another is SSD I use exactly same information and app for both servers. They are exactly same, just one of them is SSD and another is HARD. ...
1
vote
1answer
85 views

how to use mysql temporal cache tables

I took this example from a book where visitors_today is the cache table from visitor_stored. The idea is to store all the visitors per day (visitors_stored) using a cache table. Create table ...
1
vote
1answer
68 views

Different plans or buffers for different connections?

We had an issue in our dev environment where a procedure call timed out from the web server after 30 seconds. I traced the query and ran it manually (same params and all) from SSMS and it executed in ...
1
vote
1answer
32 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 ...
1
vote
1answer
63 views

Battery backed write cache and Postgres

I will be getting a SAN with a battery backed write cache for a new Postgres data warehouse and am not sure what settings to apply to make proper use of it. If the cache is enabled in the SAN is that ...
1
vote
1answer
111 views

Query_cache doesn't work with join

I have a simple join query, but for some reason query_cache won't cache it! SELECT id, news, approve, FIXED, DATE, allow_main FROM post LEFT JOIN post_plus ON post.id = post_plus.news_id WHERE ...
1
vote
1answer
562 views

MySQL: To use MYISAM or INNODB engine? (plot twist enclosed)

We use INNODB for all of our tables, but I have one special table, a 100+ GB reference table (only read-queries against it). We are loading changes to this table in a nightly batch job using LOAD ...
1
vote
2answers
2k views

Cache all mysql table

Maybe it sound's like i'm an idiot... But i'm developing app with huge db on RoR. Also i see that if i do some query and after that do it one more it is doing much faster, becouse it has been ...
1
vote
2answers
146 views

Oracle 11.2 using too few memory

it is about one year that I have changed my server from Oracle 10g to 11.2g (and also changed the hardware and Windows from 2003R2 to 2012). The problem is that with more than doubled cpu power, ...
1
vote
1answer
69 views

If MySQL isn't caching, then what is?

I ran a few commands to see whether MySQL is caching queries/result sets. It looks like this is not the case. mysql> show variables like 'query_cache_size'; -------------- show variables like ...
1
vote
1answer
125 views

Command line switch to disable Coherence?

We have a Java app that happens to have the coherence jar in the path. My application doesn't need it, so I'd like to pass some type of -D variable to the JVM to tell it to ignore coherence, or ...
1
vote
1answer
362 views

MySQL: How do I limit or prevent caching for specific tables?

I'm currently working on a database schema for tables to hold user information on a website with integrated forums. Right now my idea is to have all the data in two separate tables: one table with ...
1
vote
1answer
30 views

Is it normal that I have to FLUSH QUERY CACHE once every 6 hours to maintain fast queries?

I am running MySQL 5.5.37. I have a cronjob set to run every 6 hours to flush the query cache. I realized that if I don't do that, then the cache becomes fragmented and average query response time ...
1
vote
0answers
39 views

Flush procedure cache or drop and create?

Okay I had a very strange issue today on SQL Server 2005. We had a stored procedure update that added new parameters to part of it. One database out of ~150 or more seem to not work properly. Using ...
1
vote
0answers
45 views

mysql vs mongodb cache/load balancing

I have a system where users can 'like' content. There will likely be many hundreds of these likes going on at once. I'd like it to be AJAX driven so you get an immediate response. At the moment I ...