none
How to check Database memory , CPU , I/O usage, Processes and Waits RRS feed

  • Question

  • Hi All,

    I have production server where 15-20 databases resides. I have to create Index maintenance plan for one db but before that I need to check few things like db CPU usage, Memory usage, processes , I/O usage and all that parameters.

    How to check CPU, Memory parameters for single database? Any script ?

    When I create index job will this impact other databases reside in same server?

    Any way to see how much CPU, Memory, Processes allocation to each databases in a server?

    Thanks,

    Meena K

    Thursday, June 22, 2017 2:05 AM

All replies

  • Thursday, June 22, 2017 3:54 AM
    Moderator
  • Hello,

    Addtional, in SSMS do a right-mouse click on the server node => Reports => Standard Reports => ..., here you can get several informations on SQL Server instance level.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, June 22, 2017 5:44 AM
  • Hi

    You need to identify the optimal window frame to run the index maintenance plan when your server resource level are least used Database processes, CPU, Memory and Disk I/O. you can identify this by running perfmon or any other third party tool.

    When you create Index maintenance plan for one Database, ideally it should not impact other database but it may impact other database if your server level resource are getting utilized by index maintenance.

    Best Regards,

    Harsh

    Thursday, June 22, 2017 5:52 AM
  • Hi Meena,

    To check the database CPU usage, you can try following script:

    ;WITH CPU_Per_Db AS
    (SELECT 
    dmpa.DatabaseID
    , DB_Name(dmpa.DatabaseID) AS [Database]
    , SUM(dmqs.total_worker_time) AS CPUTimeAsMS
    FROM sys.dm_exec_query_stats dmqs 
    CROSS APPLY 
    (SELECT 
    CONVERT(INT, value) AS [DatabaseID] 
    FROM sys.dm_exec_plan_attributes(dmqs.plan_handle)
    WHERE attribute = N'dbid') dmpa
    GROUP BY dmpa.DatabaseID)
     
    SELECT 
    [Database] 
    ,[CPUTimeAsMS] 
    ,CAST([CPUTimeAsMS] * 1.0 / SUM([CPUTimeAsMS]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUTimeAs%]
    FROM CPU_Per_Db
    ORDER BY [CPUTimeAs%] DESC;
    

    Then for the Memory usage or Process allocation, please refer to following articles:

    Determine SQL Server memory use by database and object 

    T-SQL Query Script to monitor Memory Usage of a SQL Server Instance 

    Examining SQL Server processes 

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 22, 2017 5:55 AM
  • How to check CPU, Memory parameters for single database? Any script ?

    WITH DB_CPU_Stats
    AS
    (SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
     FROM sys.dm_exec_query_stats AS qs
     CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 
                  FROM sys.dm_exec_plan_attributes(qs.plan_handle)
                  WHERE attribute = N'dbid') AS F_DB
     GROUP BY DatabaseID)
    SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
           DatabaseName, [CPU_Time_Ms], 
           CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
    FROM DB_CPU_Stats
    WHERE DatabaseID > 4 -- system databases
    AND DatabaseID <> 32767 -- ResourceDB
    ORDER BY row_num OPTION (RECOMPILE);

    -- Good basic information about OS memory amounts and state  
    SELECT total_physical_memory_kb, available_physical_memory_kb, 
           total_page_file_kb, available_page_file_kb, 
           system_memory_state_desc
    FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);

    -- You want to see "Available physical memory is high"
    -- This indicates that you are not under external memory pressure


    -- SQL Server Process Address space info  
    --(shows whether locked pages is enabled, among other things)
    SELECT physical_memory_in_use_kb,locked_page_allocations_kb, 
           page_fault_count, memory_utilization_percentage, 
           available_commit_limit_kb, process_physical_memory_low, 
           process_virtual_memory_low
    FROM sys.dm_os_process_memory WITH (NOLOCK) OPTION (RECOMPILE);

    -- You want to see 0 for process_physical_memory_low
    -- You want to see 0 for process_virtual_memory_low
    -- This indicates that you are not under internal memory pressure

    <<<When I create index job will this impact other databases reside in same server?

    Use Ola's great script  and you wont have any problems :-)

    https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    >>>>Any way to see how much CPU, Memory, Processes allocation to each databases in a server?

    Regarding CPU see above script

     --Which DBs are consuming most Buffer Cache

    SELECT db_name(database_id) as dbname,
           count(page_id)  as pages,
       convert(decimal(20,2),count(page_id)*8192.0/1048576) as Mb
    from sys.dm_os_buffer_descriptors
    group by database_id
    order by convert(decimal(20,2),count(page_id)*8192.0/1048576) desc

     --Which type of pages are consuming most Buffer Cache

    SELECT page_type,
           count(page_id)  as pages,
       convert(decimal(20,2),count(page_id)*8192.0/1048576) as Mb
    from sys.dm_os_buffer_descriptors
    group by page_type
    order by convert(decimal(20,2),count(page_id)*8192.0/1048576) desc

    WITH DBIO AS
    (
     SELECT
       DB_NAME(IVFS.database_id) AS db,
       CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
       SUM(IVFS.num_of_bytes_read +IVFS.num_of_bytes_written) AS io,
       SUM(IVFS.io_stall) AS io_stall
     FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
       JOIN sys.master_files AS MF
         ON IVFS.database_id = MF.database_id
         AND IVFS.file_id = MF.file_id
     GROUP BY DB_NAME(IVFS.database_id), MF.type
    )
    SELECT db, file_type,
      CAST(1. *io/ (1024 *1024) AS DECIMAL(12, 2))AS io_mb,
      CAST(io_stall /1000. AS DECIMAL(12,2))AS io_stall_s,
      CAST(100.*io_stall / SUM(io_stall)OVER()
           AS DECIMAL(10,2))AS io_stall_pct,
      ROW_NUMBER()OVER(ORDER BY io_stall DESC) AS rn
    FROM DBIO
    ORDER BY io_stall DESC;


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, June 22, 2017 6:46 AM
    Answerer