Hot answers tagged

22 votes

Memory Usage by SQL Server

'I am trying to determine if memory pressure is an issue to some of my problem.' very useful script: https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/SQLServer_Memory_Information.sql ...
Igor's user avatar
  • 720
17 votes
Accepted

Memory Usage by SQL Server

Can someone direct to a good/proper explanation. I would start by saying Task Manager is not a correct place to gauge SQL Server memory consumption, it will not tell you correct value when SQL Server ...
Shanky's user avatar
  • 19k
16 votes

Does restarting the SQL Services (on the machine) clear the server cache (for things like query plans and statistics)?

The behavior is no different between restarting the service alone or restarting the service due to rebooting the underlying operating system. Which information is wiped on such a restart? Query plans?...
Aaron Bertrand's user avatar
13 votes

Even after Disabling Read-Ahead and clearing buffer , why there is Logical Read?

A logical read is counted when a single page is retrieved from buffer cache during query execution. This is counted regardless of whether a physical or read-ahead was used to cache the page, or if the ...
Dan Guzman's user avatar
  • 28.1k
13 votes
Accepted

Does restarting the SQL Services (on the machine) clear the server cache (for things like query plans and statistics)?

Yes it does. Since SQL Server is in charge of maintaining those caches, when you shut down SQL server's services, it releases the memory back to the operating system.
Laughing Vergil's user avatar
10 votes
Accepted

How SQL Server caches data in memory

Test Data CREATE TABLE dbo.Table1( A INT IDENTITY(1,1) PRIMARY KEY NOT NULL ,B varchar(255),C int,D int,E int); INSERT INTO dbo.Table1 WITH(TABLOCK) (B,C,D,E) SELECT TOP(...
Randi Vertongen's user avatar
10 votes
Accepted

Does PostgreSQL always sequentially scan pages in the same order?

If multiple processes are trying to seq scan the same table, it will try to line them all up so that all read the same pages at about the same time, to maximize cache hits. It does this by having the ...
jjanes's user avatar
  • 39.5k
10 votes
Accepted

AWS Aurora PostgreSQL Serverless: How do you pre-warm the shared buffer after scaling?

My answer is not specific to AWS Aurora PostgreSQL Serverless, but for Postgres in general. Simple alternative In your related comment, you hinted that you only need rows from the last 24 hours. So ...
Erwin Brandstetter's user avatar
9 votes
Accepted

Where does an execution plan come from?

SQL Server 2012 has an indicator in the plan itself, RetrievedFromCache, which can be either "true" or "false". This appears to be the property you are asking about. This is a sample (the last line ...
Hannah Vernon's user avatar
  • 70k
7 votes

Even after Disabling Read-Ahead and clearing buffer , why there is Logical Read?

There will always be logical reads. SQL Server never returns data to you directly from disk. If the page you need is already in the buffer pool, there is a logical read. If it’s not, it will be moved ...
Aaron Bertrand's user avatar
7 votes
Accepted

Find uncompressed size of all tables in a database

I need the actual size the uncompressed data will have. ... I would prefer to have size as correct as possible. While the desire for this information is certainly understandable, getting this ...
Solomon Rutzky's user avatar
7 votes

Pre Caching Index on a large table in PostgrSQL

You could use the additional module pg_prewarm. Has to be installed once per database. See: PostgreSQL: Force data into memory It can "prewarm" tables as well as indexes. To do it for your index: ...
Erwin Brandstetter's user avatar
7 votes
Accepted

Non-deterministic performance of query on select, from 1s to 60s on table with 1 billion rows

Explanation The all-important difference between the two query plans is the added read=xyz bit in multiple places of the slow version. Slow: Buffers: shared hit=116296 read=42298 Fast: Buffers: ...
Erwin Brandstetter's user avatar
6 votes

Procedure Cache Dropping

One reason could be somebody changing options or running sp_configure. That would be logged in your error log. Please read this article: Using Sp_configure To Change a Value Will Issue DBCC ...
6 votes

Find uncompressed size of all tables in a database

From your question it seems as if you have a maximum cache size S and you don't want to load tables into the cache that exceed that size. If that's true then you don't need to know the exact size of ...
Joe Obbish's user avatar
  • 32.1k
5 votes

How to load entire database into memory?

I want to load my entire database into memory, but how can I do this? Before figuring out "how" to do something, it is often best to be clear on "why" that something should be done. So, why exactly ...
Solomon Rutzky's user avatar
4 votes

Performance considerations for Caching Aggregate counts

What performance considerations should tip the scales in favor of a particular strategy? Essentially, as any form of cache, these strategies allow to increase performance of reads at the expense of ...
Vladimir Baranov's user avatar
4 votes
Accepted

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

The non-persisted computed column values are not cached in memory. Here's a brief-ish demo that confirms this. First I'll create a database for our testing: CREATE DATABASE CC_BufferPool_Test; GO ...
Josh Darnell's user avatar
  • 29.2k
4 votes

SQL dumping all pages from buffer cache every few minutes

Although you seem to have solved the issue yourself, here is a summary of the relevant information surrounding the solution. Server Memory Server Configuration Options Microsoft write in their ...
John K. N.'s user avatar
  • 17.6k
4 votes
Accepted

SQL Server database slow performance

Next time, this query will collect the top 25 cached stored procedures by query time (I think I got this from Paul White, but that may not be accurate): -- Top Cached SPs By Avg Elapsed Time SELECT ...
DBADon's user avatar
  • 325
4 votes

AWS Aurora PostgreSQL Serverless: How do you pre-warm the shared buffer after scaling?

I think you're out of luck on #1. The release notes for PG 11 note the addition of that feature. And I'm not familiar with Aurora, don't have any idea about #2. For #3, it all depends on what the &...
AdamKG's user avatar
  • 932
4 votes
Accepted

How to know how much memory is used by each DB user in the cache buffer?

You have an interesting question. However, as per my understanding of Oracle and the various caches that exist in the SGA and PGA, there are no real means to determine the usage of each individual ...
John K. N.'s user avatar
  • 17.6k
3 votes
Accepted

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

Warmup If you have a secondary index on the table, SELECT COUNT(*)... will use that. So the first "warming" fetched that index. The second "warming" fetched the data; note that the hump is higher, ...
Rick James's user avatar
  • 77.7k
3 votes

Is our MySQL poor performance related to excessive daily query cache prunes?

I would yes for one reason: InnoDB and the Query Cache are not good neighbors. Why ? On Jun 07, 2014, I answered the question Why query_cache_type is disabled by default start from MySQL 5.6?. In ...
RolandoMySQLDBA's user avatar
3 votes
Accepted

What can data cache contain besides pages from physical disk?

There are couple of things here so let me explain Logical reads: A logical read occurs every time the Database Engine requests a page from the buffer cache. If the page is not currently in the buffer ...
Shanky's user avatar
  • 19k
3 votes
Accepted

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

With that in mind, how does Enterprise Edition decide how much memory it can use for the data cache? SQL Server memory( Buffer Pool) is designed to adjust its requirement according to available ...
Shanky's user avatar
  • 19k
3 votes

How SQL Server caches data in memory

even if we need just 100 rows ? or just 100 rows (their data pages) are read from disk to memory ? This could be due to Logical Processing order of SQL query as the TOP clause would be considered ...
Shekar Kola's user avatar
  • 2,407
3 votes
Accepted

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

No. You can set Minimum & Maximum server memory to control the range of total memory utilization by the Instance, but there is not fine grained control for the constituent parts that make up "...
AMtwo's user avatar
  • 16.1k
3 votes
Accepted

Why the second query is so much faster than the first one?

TL/DR: Cold cache. If only the first execution is slow it's typically cold cache. And yours is just such a case. As you can see under IO & Buffers in your image (already made you wonder), the fast ...
Erwin Brandstetter's user avatar
2 votes

Is the overhead of frequent query cache invalidation ever worth it?

I can think of a perfect case for it, and we have tested thoroughly and run it in production...I call it the "fast lane" clustering strategy: If you do read-write splitting with a proxy like MaxScale,...
Christopher McGowan's user avatar

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