Webcast "SQL Server par la pratique pour les utilisateurs expérimentés", module 3 Serveur

  • Article

/* Partie Mémoire */

DBCC

memorystatus

SELECT

* FROM sys.dm_os_memory_clerks

/* Utilisation de la mémoire virtuelle */

SELECT

type,

SUM(virtual_memory_reserved_kb) AS TotalVirMemReservedKB,

SUM(virtual_memory_committed_kb) AS TotalVirMemCommittedKB,

SUM(multi_pages_kb) AS TotalMultiPagesKB,

SUM(single_pages_kb) AS TotalSinglePagesKB

FROM

sys.dm_os_memory_clerks

GROUP

BY type

ORDER

BY TotalVirMemCommittedKB DESC, TotalVirMemReservedKB DESC, TotalMultiPagesKB DESC, TotalSinglePagesKB DESC;

/* Utilisation de la mémoire réelle */

SELECT

type,

SUM(multi_pages_kb) AS TotalMultiPagesKB,

SUM(single_pages_kb) AS TotalSinglePagesKB,

SUM(virtual_memory_reserved_kb) AS TotalVirMemReservedKB,

SUM(virtual_memory_committed_kb) AS TotalVirMemCommittedKB

FROM

sys.dm_os_memory_clerks

GROUP

BY type

ORDER

BY TotalMultiPagesKB DESC, TotalSinglePagesKB DESC,TotalVirMemCommittedKB DESC, TotalVirMemReservedKB DESC ;

/* Autres vues sur la mémoire */

select

* from Sys.dm_os_memory_objects

order

BY Type

select

* from sys.dm_os_memory_cache_counters

order

by (single_pages_kb + multi_pages_kb) desc

select

* from sys.dm_os_memory_cache_entries

select

* from sys.dm_os_memory_clerks

order

by type

select

* from sys.dm_os_memory_pools

/* Partie Processus et Threads */

dbcc

sqlperf (threads)

select

* from dbo.sysprocesses