Understanding DBA Cache Read

[Former Member]

Dear team,

I need help for following.

As per the understanding; whenever any job/query is executed; most of the databases brings the data from physical files and put it into Database Cache.

Can you please let us know whether the data segment as well as index are copied when data is read from datafiles?

Also How come database engines knows whether requested data by any process (end user query to DB) is available in Cache. (this question may sound senseless but I do have doubt about it)

Regards,

Arthur

[former_member184473 · Accepted solution]

Hello Arthur,

You can check what is inside the cache using sys.dm_os_buffer_descriptors dmv.

Check more information in the following website:
Buffer Management

Execution Plan Caching and Reuse

SQL Server knows which statements are already cached because of the sql id (SQL handle):

SQL handle: 0x02000000A14F0E173067D0DDB4C9D6ED7EDACA4CBA842B910000000000000000000000000000000000000000
=========== INPUT ===============================
SELECT TABNAME, BLOCKNR, FIELDSLG, FIELDS
FROM "DDNTF"
WHERE TABNAME = @P1
ORDER BY TABNAME, BLOCKNR
/* R3:NTAB:0 T:DDNTF */

You can check the SQL cache of your SAP system under transaction DBACockpit > Performance > SQL Statements.

Regards,

Eduardo Rezende

[Former Member · Accepted solution]

Thanks Sire

If I may extend this question to another; here is the one.

When We assign Minimum and maximum memory to SQL instance; Why is that in some cases I see that sqlserver.exe shows Maximum memory in "Private memory" utilization tab in task manager and in some cases it is really less ?

i.e. If i Assign 30 GB max. memory to sql instance for 2 independent systems; One shows that it is using 30 GB in task manager and another shows it is using only 200-300 MB.

[former_member184473 · Accepted solution]

Hello Arthur,

Maybe your SQL Server does not have locked pages priviledge.

For more information check note 1134345.

Are you using VM? If yes, the rammap tool could help.

For more information check the following websites:

Virtualization – Overcommitting memory and how to detect it within the VM - Running SAP Application...

Introduction to the new Sysinternals tool: RAMMap - Ask the Performance Team - Site Home - TechNet ...

Regards,

Eduardo