Connaître la taille du cache allouée aux bases de données et à leurs tables avec SQL Server 2005 et suivants

Publié le par

Voici quelques requêtes qui montrent la taille du cache de données allouée aux tables (et vues indexées) :

– pour toute l’instance SQL Server,
Рpar base de donn̩es
Рpar table (ou vue index̩e) dans une base de donn̩es en particulier

=> Au niveau de l’instance SQL Server

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-------------------------------
-- Nicolas Souquet - 04/06/2012
-------------------------------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
 
;WITH
  CTE AS  
  (
    SELECT  COUNT(*) / 128 AS used_MB
      , SUM (CAST (free_space_in_bytes AS float)) / (1024 * 1024) AS empty_MB
    FROM  sys.dm_os_buffer_descriptors
  )
SELECT  used_MB
  , CAST(empty_MB AS bigint) AS empty_MB
  , CAST((empty_MB / used_MB) * 100 AS decimal(5,2)) AS ratio
FROM  CTE
GO

Cette requête donne l’espace alloué au cache de données pour toutes les bases de données d’une instance SQL Server.
Elle montre également l’espace vide dans les pages, qui quand elle est élevée, est due soit à une fragmentation des index élevée, soit à un modèle de données incorrect qui conduit à ne pouvoir stocker que peu de lignes par page de données.
En effet le taille d’une page de données est de 8192 octets, desquels 8060 octets sont dédiés aux données utilisateur.
Si donc la taille d’une ligne est de 4031 octets (cas extrême) ou plus, on ne peut stocker qu’une seule ligne de données par page, et on gaspille 4029 octets.

=> Décomposition par base de données

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-------------------------------
-- Nicolas Souquet - 04/06/2012
-------------------------------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
 
;WITH
  CTE AS
  (
    SELECT  CASE
        WHEN database_id = 32767 THEN 'RESOURCE DATABASE'
        ELSE DB_NAME(database_id)  
      END AS database_name
      , COUNT (*) * 8 / 1024 AS reserved_MB
      , SUM(CAST(free_space_in_bytes AS bigint)) / (1024 * 1024) AS empty_MB
    FROM  sys.dm_os_buffer_descriptors
    GROUP  BY database_id
  )
SELECT  database_name
  , reserved_MB
  , empty_MB
  , CAST(100.0 * empty_MB / CASE reserved_MB WHEN 0 THEN 1 ELSE reserved_MB END AS decimal(5,2))[empty_%]
FROM  CTE
--WHERE  database_name = 'maBD'
ORDER  BY empty_MB DESC

Même chose ici, mais pour chaque objet de base de données.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-------------------------------
-- Nicolas Souquet - 04/06/2012
-------------------------------
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
 
;WITH
  CTE AS
  (
    SELECT    BD.allocation_unit_id
        , AU.type_desc AS allocation_type
        , P.object_id
        , P.index_id
        , COUNT_BIG(*) AS page_count
        , COUNT_BIG(*) * 8192 AS allocated_space_in_bytes
        , SUM(CAST(BD.free_space_in_bytes AS bigint)) AS free_space_in_bytes
    FROM    sys.dm_os_buffer_descriptors AS BD
    INNER JOIN  sys.allocation_units AS AU
          ON AU.allocation_unit_id = BD.allocation_unit_id
    INNER JOIN  sys.partitions AS P
          ON AU.container_id = P.partition_id
    WHERE    BD.database_id = DB_ID()
    GROUP BY  BD.allocation_unit_id
        , AU.type_desc
        , P.object_id
        , P.index_id
    HAVING    COUNT_BIG(*) > 8
  )
SELECT    S.name + '.' + O.name AS table_or_indexed_view_name
    , I.name AS index_name
    , C.allocation_type
    , C.page_count
    , C.allocated_space_in_bytes
    , C.allocated_space_in_bytes - C.free_space_in_bytes AS used_space_in_bytes
    , C.free_space_in_bytes
    , CAST((CAST(C.free_space_in_bytes AS decimal(20,2)) / C.allocated_space_in_bytes) * 100 AS decimal(5,2)) AS [free_space_in_%]
FROM    CTE AS C
INNER JOIN  sys.indexes AS I
      ON I.index_id = C.index_id
      AND I.object_id = C.object_id
INNER JOIN  sys.objects AS O
      ON C.object_id = O.object_id
INNER JOIN  sys.schemas AS S
      ON S.schema_id = O.schema_id
INNER JOIN  sys.database_principals AS DP
      ON S.principal_id = DP.principal_id
WHERE    DP.type = 'S' -- schema
AND    DP.default_schema_name IS NOT NULL
ORDER BY  C.page_count DESC

Cette requête nécessite de changer le contexte de base de données à la base de données que l’on souhaite étudier.
En effet, elle retourne la liste des tables (et vues indexées) qui sont présentes dans le cache de données, ainsi que la taille qu’elles y occupent, avec les index.

En exécutant cette dernière requête à intervalles de temps réguliers à l’aide d’un job de l’Agent SQL Server, et en enregistrant le résultat dans une table, on peut étudier la taille du cache allouée aux objets, de façon à faire une projection sur la quantité de RAM nécessaire au bon fonctionnement de la base de données.

Laisser un commentaire