Analyzing SQL Server Data Caching

1
Analyzing SQL Server Data Caching

• Tom PullenOxfam SQL Server DBA
• tpullen_at_oxfam.org.ukMonday 10.1.2005

2
SQL Server Data Caching

• This session is based on my article which can be
  found here
• http//www.sql-server-performance.com/tp_analyzing
  _sql_server_2000_data_caching.asp
• This session will look at how you can track the
  tables and indexes that SQL Server is
• keeping in RAM.
• This session is aimed at SQL Server DBAs or
  people with DBA-type responsibility, or
• indeed programmers who are tuning queries.
• Oracle, DB2 etc. users can have a brief nap.

3
dbcc memusage

• - undocumented, unsupported command
• - documented issue with SQL Server 7.0
• http//support.microsoft.com/default.aspx?scidkb
  en-us196629
• - seems to be reliable, and has very limited
  impact in SQL Server 2000
• - reports on the Top 20 largest objects in cache
  only
• - output requires some interpretation

4
Sample memusage output
5
Automating collection of stats 1

• Scheduled task captures output of memusage into a
  table. First create table to hold memusage
• data.
• CREATE TABLE dbo.MemusageRecord (dbid
  int NOT NULL ,objectid int NOT NULL
  ,indexid int NOT NULL ,Buffers int NOT
  NULL ,Dirty int NOT NULL ,InsertDate
  datetime NOT NULL ) ON PRIMARYGO
• CREATE CLUSTERED INDEX mur001 ON
  dbo.memusagerecord(InsertDate) ON
  PRIMARYGO
• ALTER TABLE dbo.memusagerecord ADD
  CONSTRAINT DF_getdate_insertdate DEFAULT
  (getdate()) FOR InsertDateGO

6
Automating collection of stats 2

• Second create the SP to execute dbcc memusage and
  append the results to the table.
• CREATE PROCEDURE prMemusageRecord
• AS
• CREATE TABLE memusagerecord(dbid int,objectid
  int,indexid int,Buffers int,Dirty int,)
• INSERT INTO memusagerecord EXEC('DBCC MEMUSAGE')
• INSERT INTO MemusageRecord
• (dbid, objectid, indexid, Buffers,
  Dirty) SELECT dbid, objectid, indexid, Buffers,
  Dirty FROM memusagerecord
• DROP TABLE memusagerecord
• GO

7
Automating collection of stats 3

• Add the execution of this SP as a scheduled task
  (e.g. every minute) in SQL Server Agent.
• Then .. Analyze the results (for example)
• CREATE PROCEDURE prCheckRecentCache _at_dbname
  SYSNAME
• AS
• DECLARE _at_sql VARCHAR(8000)
• SELECT _at_sql
• 'SELECT so.name as ''Object'',
• CASE m.indexid
• WHEN 0 THEN ''Data''
• WHEN 1 THEN ''Clustered Index''
• ELSE ''Nonclustered Index ID ''
  CONVERT(VARCHAR, m.indexid)
• END AS ''Cache Data Type'',
• CONVERT(DECIMAL(9,2),8m.Buffers/1024.00) AS ''MB
  Cached'', m.InsertDate,
• db_name(dbid) AS ''Database''
• FROM pubs..MemusageRecord m
• JOIN ' _at_dbname '..sysobjects so
• ON m.objectid so.id
• WHERE m.InsertDate(SELECT MAX(InsertDate) FROM
  pubs..MemusageRecord)
• AND m.dbiddb_id('''_at_dbname''')

8
Automating collection of stats 4

• Sample output of prCheckRecentCache

9
Ad-hoc analysis 1

• Uses 2 worktables which are re-populated on each
  run.
• CREATE TABLE dbo.Cache_Recent_Details (
• Object sysname NOT NULL ,
• Cache Data Type varchar (50) COLLATE
  Latin1_General_CI_AS NOT NULL ,
• MB cached int NOT NULL ,
• InsertDate datetime NOT NULL ,
• Database sysname NOT NULL
• ) ON PRIMARY
• CREATE TABLE dbo.tmp_memusagerecord (
• dbid int NOT NULL ,
• objectid int NOT NULL ,
• indexid int NOT NULL ,
• Buffers int NOT NULL ,
• Dirty int NOT NULL ,
• InsertDate datetime NOT NULL
• ) ON PRIMARY

10
Ad-hoc analysis 2

• There are 3 stored procedures (two are called by
  the other, one once and the other in turn for
  each database
• on the server) 1st core stored procedure-
• CREATE proc pr_oneoff_memusagerecord
• as
• create table tmp_memusagerecord
• (
• dbid int,
• objectid int,
• indexid int,
• Buffers int,
• Dirty int,
• )
• insert into tmp_memusagerecord exec('dbcc
  memusage')
• insert into tmp_memusagerecord
• (dbid, objectid, indexid, Buffers, Dirty)

11
Ad-hoc analysis 3

• 2nd core stored procedure-
• CREATE PROCEDURE prCheckRecentCache _at_dbname
  sysname
• AS
• DECLARE _at_sql VARCHAR(8000)
• SELECT _at_sql
• 'SELECT so.name as ''Object'',
• CASE m.indexid
• WHEN 0 THEN ''Data''
• WHEN 1 THEN ''Clustered Index''
• ELSE ''Nonclustered Index ID ''
  CONVERT(VARCHAR, m.indexid)
• END AS ''Cache Data Type'',
• CONVERT(DECIMAL(9,2),8m.Buffers/1024.00) AS ''MB
  Cached'', m.InsertDate, db_name(dbid) AS
  ''Database''
• FROM
• DBADATA..memusagerecord m
• JOIN ' _at_dbname '..sysobjects so
• ON m.objectid so.id
• WHERE m.InsertDate(SELECT MAX(InsertDate) FROM
  DBADATA..memusagerecord)
• AND m.dbiddb_id('''_at_dbname''')

12
Ad-hoc analysis 4

• Shell stored procedure-
• CREATE proc analyse_data_cache
• as
• set nocount on
• delete from dbo.Cache_Recent_Details
• delete from dbo.tmp_memusagerecord
• exec pr_oneoff_memusagerecord
• insert into Cache_Recent_Details exec
  prCheckRecentCache DBADATA
• insert into Cache_Recent_Details exec
  prCheckRecentCache master
• insert into Cache_Recent_Details exec
  prCheckRecentCache model
• insert into Cache_Recent_Details exec
  prCheckRecentCache msdb
• insert into Cache_Recent_Details exec
  prCheckRecentCache tempdb
• select Database,Object,MB cached,Cache
  Data Type,InsertDate from Cache_Recent_Details
• order by MB cached desc
• set nocount off

13
Ad-hoc analysis 5

• Sample output

14
What does it all mean?

• You can use the data you have captured to
  determine-
• how much data cache each database has in total
• which objects, and which of their indexes are
  being cached
• how data cache is changing with time and query
  load
• how the make up of data in the cache correlates
  with query execution times
• the rate at which objects age out of cache and
  which ones stay in RAM all the time
• the impact of big queries on cache and vice versa

15
That's it!
Any questions?