TempDB occupe plus d'espace en RAM que sur disque ?

Version imprimable

Bonjour,

Depuis quelques jours, le serveur qui sert aux développements et aux tests de l'application vendue par mon entreprise connaît quelques pressions mémoires que nous n'avons pas encore eu le temps de diagnostiquer proprement.

L'erreur la plus souvent obtenue est :

Citation:

There is insufficient system memory in resource pool 'default' to run this query.

Mais il arrive parfois aussi que les clients applicatifs ne parviennent pas à se connecter au serveur :

Citation:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

Enfin, il arrive parfois que nous ne puissions pas prendre le contrôle du serveur à l'aide d'une connexion bureau à distance.

Le serveur est un Dell PE6850 (dépasse la période de garantie du constructeur) avec Windows Server 2003 32 bits (à jour), et 64GB de RAM, avec SQL Server 2008 Enterprise.

Je viens de jeter un œil aux tailles occupées par les bases de données dans le cache à l'aide de la requête suivante :
Code:

1 2 3 4 5 6 SELECT D.name AS database_name , CAST(COUNT_BIG(*) * 8192.0 / 1024 / 1024 AS decimal(10,2)) AS cached_size_MB FROM sys.dm_os_buffer_descriptors AS OSBD INNER JOIN sys.databases AS D ON OSBD.database_id = D.database_id GROUP BY D.name WITH ROLLUP ORDER BY cached_size_MB DESC
qui me retourne 11GB pour TempDB.

A l'aide de la requête suivante :
Code:

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 USE TempDB GO ;WITH CTE AS ( SELECT name AS logicial_name , physical_name AS physical_name , CASE is_percent_growth WHEN 0 THEN CAST(growth * 8 / 1024 AS VARCHAR(8)) + ' MB' ELSE CAST(growth AS VARCHAR(3)) + ' %' END AS growth , CAST((size * 8) / CAST(1024 AS numeric(14, 2)) AS numeric(14, 2)) AS file_size_MB , CAST((FILEPROPERTY(name, 'SpaceUsed') * 8) / CAST(1024 AS numeric(14, 2)) AS numeric(14, 2)) AS occupied_space_MB FROM sys.database_files ) SELECT logicial_name , physical_name , file_size_MB , growth , occupied_space_MB , file_size_MB - occupied_space_MB AS free_space_MB , CAST((CAST(occupied_space_MB AS numeric(14, 2)) / file_size_MB) * 100 AS numeric(14, 2)) AS [%occupe] , CAST((CAST(file_size_MB - occupied_space_MB AS numeric(14, 2)) / file_size_MB) * 100 AS numeric(14, 2)) AS [%libre] FROM CTE
J'obtiens :

Citation:

tempdev Z:\MSSQL\data\tempdb.mdf 17028.00 100 MB 4.31 17023.69 0.03 99.97
templog Y:\MSSQL\data\templog.ldf 1200.00 100 MB 406.56 793.44 33.88 66.12
tempdev1 V:\MSSQL\Data\tempdb1.ndf 16928.00 100 MB 3.38 16924.62 0.02 99.98
tempdev2 W:\MSSQL\Data\tempdb2.ndf 17028.00 100 MB 11.75 17016.25 0.07 99.93
tempdev3 Y:\MSSQL\Data\tempdb3.ndf 13928.94 100 MB 25.75 13903.19 0.18 99.82

Je subodore que cette requête soit incorrecte, mais je trouve le résultat assez bizarre ...

Qu'en pensez-vous ?

@++ ;)

Lut,

D'un côté l'occupation au niveau fichier et de l'autre côté RAM .. admettons.

Que te donne la requête suivante :

Code:

1
2
3
4
5
SELECT 
 version_store_reserved_page_count * 8. / 1024 AS version_store_reserved_MB,
 user_object_reserved_page_count * 8 / 1024 AS user_object_reserved_MB,
 internal_object_reserved_page_count * 8 / 1024 AS internal_object_reserved_MB
FROM sys.dm_db_file_space_usage

Salut,

Citation:

D'un côté l'occupation au niveau fichier et de l'autre côté RAM .. admettons.

Qu'est-ce à dire ?

Sympa la DMV :)
J'ai agrémenté un peu ta requête :
Code:

1 2 3 4 5 6 SELECT DF.name AS logical_file_name , FSU.version_store_reserved_page_count * 8. / 1024 AS version_store_reserved_MB , FSU.user_object_reserved_page_count * 8 / 1024 AS user_object_reserved_MB , FSU.internal_object_reserved_page_count * 8 / 1024 AS internal_object_reserved_MB FROM sys.dm_db_file_space_usage AS FSU INNER JOIN sys.database_files AS DF ON DF.file_id = FSU.file_id
Qui me retourne donc :

Citation:

tempdev 0.125000 0 1
tempdev1 0.125000 0 1
tempdev2 0.062500 0 0
tempdev3 0.125000 0 1

Ce qui est assez bas. Qu'en penses-tu ?

@++ ;)

Regarde les objets de la tempdb qui occupe ton buffer :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT count(*)AS cached_pages_count 
    ,name ,index_id 
FROM sys.dm_os_buffer_descriptors AS bd 
    INNER JOIN 
    (
        SELECT object_name(object_id) AS name 
            ,index_id ,allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.hobt_id 
                    AND (au.type = 1 OR au.type = 3)
        UNION ALL
        SELECT object_name(object_id) AS name   
            ,index_id, allocation_unit_id
        FROM sys.allocation_units AS au
            INNER JOIN sys.partitions AS p 
                ON au.container_id = p.partition_id 
                    AND au.type = 2
    ) AS obj 
        ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id 
ORDER BY cached_pages_count DESC;

J'en ai une similaire à ta requête :

Code:

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
USE TempDB
GO
 
WITH
    CTE AS
    (
            SELECT        object_id
                    , p.index_id
                    , allocation_unit_id
            FROM        sys.allocation_units AS au
            INNER JOIN    sys.partitions AS p 
                        ON au.container_id = p.hobt_id 
                        AND (au.type = 1 OR au.type = 3)
        UNION ALL
            SELECT        object_id AS name
                    , p.index_id
                    , au.allocation_unit_id
            FROM        sys.allocation_units AS au
            INNER JOIN    sys.partitions AS p 
                        ON au.container_id = p.partition_id 
                        AND au.type = 2
    )
SELECT        o.name AS table_name
        , i.name AS index_name
        , COUNT(*) AS cached_pages_count
        , COUNT_BIG(*) * CAST(8192.0 AS decimal(19, 6)) / 1024 / 1024 AS cache_MB
FROM        sys.dm_os_buffer_descriptors AS bd 
INNER JOIN    CTE AS c
            ON bd.allocation_unit_id = c.allocation_unit_id
INNER JOIN    sys.all_objects AS o
            ON o.object_id = c.object_id
LEFT JOIN    sys.indexes AS i
            ON i.object_id = c.object_id
            AND i.index_id = c.index_id
WHERE        o.name NOT LIKE 'sys%'
--AND        bd.database_id = DB_ID()
GROUP BY    o.name, i.name
ORDER BY    COUNT(*) DESC

qui me retourne entre 600MB et 3GB pour une table ...
Y'a un développeur pas cool là :)

@++ ;)

Regardes si des processus verouillent des objets de Tempdb avec le code suivant :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
USE Tempdb
 
SELECT  request_session_id ,
        DB_NAME(resource_database_id) AS [Database] ,
        resource_type ,
        resource_subtype ,
        request_type ,
        request_mode ,
        resource_description ,
        request_mode ,
        request_owner_type
FROM    sys.dm_tran_locks
WHERE   request_session_id > 50
        AND resource_database_id = DB_ID()
        AND request_session_id <> @@SPID
ORDER BY request_session_id ;