SQL Server Performance

How to monitor the memory usage through DBCC Perf

Discussion in 'SQL Server 2005 General DBA Questions' started by y.koteswarrao, Jul 14, 2010.

  1. y.koteswarrao New Member

    Hi,
    Is it possible to monitor the memory usage through DBCC Perf command..........
    Koteswarrao
    y.koteswarrao, Jul 14, 2010
    #2

  2. satya Moderator

    I believe there is no direct statement called DBCC Perf, DBCC SQLPERF - http://msdn.microsoft.com/en-us/library/ms189768(v=SQL.100).aspx is used to provide transaction log usage.
    Also there are various DBCC statements http://msdn.microsoft.com/en-us/library/ms188796(v=SQL.100).aspx that can help you to accomplish various tasks.
    Coming to memory monitoring DBCC MEMORYSTATUS http://support.microsoft.com/kb/271624 is the keyword to use, Also see other blog posts related to such monitoring methods:
    http://sqlserver-qa.net/blogs/perftune/archive/2009/09/07/5651.aspx
    http://sqlserver-qa.net/blogs/perftune/archive/tags/cpu/memory/default.aspx specific to memory related tags.
    Hope this helps.
    satya, Jul 15, 2010
    #3

  3. ashish287 New Member

    hope this query is helpful to you....
    can run it in master or specific database to get the information
    SELECT TOP 100
    [Object_Name] = object_name(st.objectid),
    creation_time,
    last_execution_time,
    total_cpu_time = total_worker_time / 1000,
    avg_cpu_time = (total_worker_time / execution_count) / 1000,
    min_cpu_time = min_worker_time / 1000,
    max_cpu_time = max_worker_time / 1000,
    last_cpu_time = last_worker_time / 1000,
    total_time_elapsed = total_elapsed_time / 1000 ,
    avg_time_elapsed = (total_elapsed_time / execution_count) / 1000,
    min_time_elapsed = min_elapsed_time / 1000,
    max_time_elapsed = max_elapsed_time / 1000,
    avg_physical_reads = total_physical_reads / execution_count,
    avg_logical_reads = total_logical_reads / execution_count,
    execution_count,
    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
    (
    (
    CASE statement_end_offset
    WHEN -1 THEN DATALENGTH(st.text)
    ELSE qs.statement_end_offset
    END
    - qs.statement_start_offset
    ) /2
    ) + 1
    ) as statement_text
    FROM
    sys.dm_exec_query_stats qs
    CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) st
    WHERE
    Object_Name(st.objectid) IS NOT NULL
    AND st.dbid = DB_ID()
    ORDER BY
    db_name(st.dbid),
    total_worker_time / execution_count DESC
    ashish287, Jul 16, 2010
    #4

  4. ashish287 New Member

    Forget to mention, if this query is helpful to you then dont give credit to me...
    I found it on one of the dba forum on sqlservercentral in past and using it whenever required.
    ashish287, Jul 16, 2010
    #5

Share This Page