DBA,  Performance

Perfmon Data collection – Integrate SSRS with Perfmon – Dashboard performance of database graph using SSRS Collect perfMon and automate it load to SQL database and generate reports

Advertisements

DBA wants to collect metrics and analysis them and present them to management to get something done by using the data and it is all about data and metric nowadays. How to collect them and show as a presentation.

Tools needed: SSRS, Perfmon.msc, Relog.Exe, database engine, power shell & CMD.

Configure SSRS

Installing SSRS

If you have not installed SSRS, install the service first and configure it.

Go to the software folder and double click the setup – click installation in the left side –New SQL server standalone or add features on existing installation – Next and next – select “Add features to an existing instance of SQL server

SSRS_Installation

Next – Install only – Install.

Configure SSRS

Once, installation completed. Open SSRS from configuration manager. Change the service account and apply –web services URL apply – database creation and apply – Report manager URL and apply.

Setup PerfMon and collect data

This can be done by perfmon windows utility.

Verify SQL performance logging counters are already installed on — run — perfmon.msc

Ensure there are a few gigabytes free on the E: drive

Save a notepad as server.lst that should contain target servers list

save a notepad as performance.lst that should contain all important counters

Open command prompt — CD /d C:\Users\muthu\Desktop\SQLDBA\Perfmon — Change this location

Run createlogs.cmd to create counters specified in performance.lst file

Open perfmon.msc and verify counters are green and working good

 

Sample performance.lst notepad should contain following counters

\Network Interface()\Packets Received/sec
\Network Interface(
)\Packets Sent/sec
\Network Interface()\Bytes Received/sec
\Network Interface(
)\Bytes Sent/sec
\Network Interface()\Bytes Total/sec
\Processor(_Total)\% Processor Time
\Processor(_Total)\% User Time
\Processor(_Total)\% Privileged Time
\Processor(_Total)\% Interrupt Time
\Processor(_Total)\% DPC Time
\LogicalDisk(
)*
\Memory\Pages/sec
\Memory\Committed Bytes
\Memory\Pool Nonpaged Bytes
\Memory\Pool Paged Bytes
\Memory\Pages Input/sec
\Memory\Pages Output/sec
\Memory\Available MBytes
\Memory\%Committed Bytes in Use
\Memory\Available Bytes
\Memory\Page Faults/sec
\Paging File(_Total)\% Usage
\PhysicalDisk(*)*
\PhysicalDisk(_Total)\Avg. Disk Queue Length
\PhysicalDisk(_Total)\Avg. Disk sec/Read
\PhysicalDisk(_Total)\Avg. Disk sec/Write
\PhysicalDisk(_Total)\Disk Read Bytes/sec
\PhysicalDisk(_Total)\Disk Reads/sec
\PhysicalDisk(_Total)\Disk Write Bytes/sec
\PhysicalDisk(_Total)\Disk Writes/sec
\PhysicalDisk(_Total)\% Idle Time
\PhysicalDisk(_Total)\% Disk Time
\Process:(*)\IO Data Bytes/Sec
\System\Processes
\System\Threads
\System\System Up Time
\System\Processor Queue Length
\SQLAgent:Alerts*
\SQLAgent:Statistics*
\SQLServer:Access Methods*
\SQLServer:Backup Device*
\SQLServer:Buffer Manager*
\SQLServer:Database Mirroring*
\SQLServer:Exec Statistics(*)*
\SQLServer:General Statistics*
\SQLServer:Latches*
\SQLServer:Locks(*)*
\SQLServer:Memory Manager*
\SQLServer:SQL Statistics*
\SQLServer:Transactions*
\SQLServer:Wait Statistics(*)*
\SQLServer:Cursor Manager by Type(*)*
\SQLServer:Databases(*)*
\SQLServer:SQL Errors(*)*
\Process(_Total)\Private Bytes
\Process(_Total)\Working Set

 

createlogs.cmd — Save this as a CMD file — Given by my friend.


@echo off
echo **************************************************************************
echo * CREATELOGS.CMD
echo *
echo * This command configures a perfmon collection log on the target server(s)
echo * specified in server.lst.
echo * The configured counters are taken from performance.lst unless
echo * a filename is provided on the command line.
echo *
echo * Note: You must have administrator rights on the target servers.
echo **************************************************************************
SET COUNTERFILE=performance.lst
if not “%1” == “” set COUNTERFILE=%1
if “%2” == “” set LISTFILE=server.lst
if not “%2” == “” set LISTFILE=%2

if not exist “%COUNTERFILE%” goto ERRBadFile
for /F %%i in (%LISTFILE%) do call :CreateLog %%i %COUNTERFILE%
goto End

:ERRBadFile
echo ERROR: Performance counter file could not be found!
goto End

:CreateLog
rem If no server specified then quit
if “%1” == “” echo ERROR: No target server specified
if “%1” == “” goto End
if “%2” == “” echo ERROR: No performance counters specified
if “%2” == “” goto End

rem Attach to target server (must have admininistrator credentials)
rem Try E: first and if that doesn’t work then try C:
net use b: /d 2>nul
set DESTDRV=e
net use b: \%1\e$ /p:no
if not exist b:*.* set DESTDRV=c
if not exist b:*.* net use b: \%1\c$ /p:no
if not exist b:*.* goto ERRConnect

rem Create a directory off of the root of the target drive to hold the perf logs.
rem This records samples on 60 second intervals.
md b:\logs\perflogs 2>nul
@echo on
logman stop %1_test -s %1 2>&1
@sleepr 5
logman delete %1_test -s %1 1>nul 2>&1
@sleepr 5
logman create counter %1_test -s %1 -o %DESTDRV%:\logs\perflogs\%1_test -v mmddhhmm -f bin -cf %2 -si 00:05:00 -cnf 24:00:00
@sleepr 5
logman start %1_test -s %1
@echo off
net use b: /d 2>nul
goto End

:ERRConnect
echo Error connecting to %1
pause
goto End

:End


Once you have data collected and you can convert to csv using relog or power-shell.

Convert to csv

relog E:\logs\Load\logfile.blg -f csv -o E:\logs\Load\logfile.csv

If you have lot of files, you can use xp_cmdshell and view the location of all files and copy, arrange it in excel and notepad to load on single shot.

Load to SQL database

We can do this by using windows utility relog.exe and it will create tables and load a data from CSV.

We need an ODBC user DSN to connect the database to loading a data using relog.exe.

You can create this in the SQL server –> administrative tools –Data source– user DSN –Add–SQL server–Finish and fill the details and test connection.

Once that done, we can import the csv perfmon data to SQL databases.

— Load to Database
relog “E:\logs\Load\logfile.csv” -f SQL -o SQL:DBA_Test!DBA_Perfmon

DBA_Test is a DSN and DBA_Perfmon is database name.

Blg_Convert_Load_to_DB

The specified log file type has not been installed on this computer csv windows 7 relog, if you get this use Perfmon Logs with Relog.exe to convert it.

If we have many files and we can load one by one or merge as a single csv and can load by script. Better to load one by one.

or load one by one

From SQL xp_cmdshell ‘dir F:\PerfMon_Data\P41\06_Nov*.csv* /b’
Order it in notepad excel and make the CMD script
Create one batch filename.cmd
Run the filename.cmd
Ex: CD /d F:\PerfMon_Data\P41\06_Nov
Run/open filename.cmd

Once the data loaded check you have three tables created – CounterData, CounterDetails,DisplayToID and it holds the data.

Create tables and SPs

Create 2 databases – [DBA_REPORT_CODE], [DBA_Perfmon] and 2 SPs on DBA_REPORT_CODE – [USP_RS_PERFMON], [USP_RS_PERFMON_INSTANCENAME]

USP_RS_PERFMON — Perfmon data report display

USE [DBA_REPORT_CODE]
GO

/****** Object:  StoredProcedure [dbo].[USP_RS_PERFMON]    Script Date: 21-02-2019 18:05:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- EXEC USP_RS_PERFMON '\\KW3L1P41',@OBJECTNAME='PhysicalDisk',@COUNTERNAME='Avg. Disk sec/Transfer',@UPLOAD_DATE='2016-01-05 00:20:00.000'

create PROC [dbo].[USP_RS_PERFMON]
(@SERVERNAME VARCHAR(100),@OBJECTNAME VARCHAR (100), @COUNTERNAME VARCHAR(100), --@INSTANCENAME VARCHAR(100)=NULL,
@UPLOAD_DATE VARCHAR(100))
AS
/*
Contact:     muthukkumaran kaliyamoorhty
Description: Generate report of perfmon data
changelog:
date         coder                          description
2018-15-june     muthukkumaran kaliyamoorhty     initial
*/

BEGIN
SELECT DBA_PERFMON..COUNTERDETAILS.MACHINENAME,
       DBA_PERFMON..COUNTERDETAILS.OBJECTNAME,
       DBA_PERFMON..COUNTERDETAILS.COUNTERNAME,
       DBA_PERFMON..COUNTERDETAILS.INSTANCENAME,
       DATEDIFF(MINUTE, ST.STARTTIME, CAST(LEFT(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME, LEN(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME) - 8) AS DATETIME)) AS INTERVAL,
       AVG(DBA_PERFMON..COUNTERDATA.COUNTERVALUE) AS COUNTERVALUE
     ,CAST(LEFT(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME, LEN(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME) - 8)AS DATETIME) AS LOAD_TIME
FROM   DBA_PERFMON..COUNTERDATA
INNER JOIN DBA_PERFMON..COUNTERDETAILS ON DBA_PERFMON..COUNTERDATA.COUNTERID = DBA_PERFMON..COUNTERDETAILS.COUNTERID,
(
SELECT DBA_PERFMON..COUNTERDETAILS.MACHINENAME,
MIN(CAST(LEFT(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME, LEN(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME) - 8)AS DATETIME))AS STARTTIME
FROM   DBA_PERFMON..COUNTERDATA
INNER JOIN DBA_PERFMON..COUNTERDETAILS ON DBA_PERFMON..COUNTERDATA.COUNTERID = DBA_PERFMON..COUNTERDETAILS.COUNTERID
GROUP  BY MACHINENAME
) AS ST

-------================================
WHERE  ST.MACHINENAME = DBA_PERFMON..COUNTERDETAILS.MACHINENAME

-------================================ OBJECTNAME
/*
AND OBJECTNAME = 'LOGICALDISK'
AND COUNTERNAME ='AVG. DISK SEC/TRANSFER'
AND INSTANCENAME='H:'
AND COUNTERDATETIME>='2017-11-05 00:20:00.000'
*/
AND DBA_PERFMON..COUNTERDETAILS.MACHINENAME= @SERVERNAME
AND OBJECTNAME = @OBJECTNAME
AND COUNTERNAME = @COUNTERNAME
--AND INSTANCENAME=@INSTANCENAME
AND DBA_PERFMON..COUNTERDATA.COUNTERDATETIME>=@UPLOAD_DATE

GROUP  BY DBA_PERFMON..COUNTERDETAILS.MACHINENAME,
          DBA_PERFMON..COUNTERDETAILS.OBJECTNAME,
          DBA_PERFMON..COUNTERDETAILS.COUNTERNAME,
          DBA_PERFMON..COUNTERDETAILS.COUNTERTYPE,
          DBA_PERFMON..COUNTERDETAILS.INSTANCENAME,
          DATEDIFF(MINUTE, ST.STARTTIME, CAST(LEFT(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME, LEN(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME) - 8) AS DATETIME))
      ,DBA_PERFMON..COUNTERDATA.COUNTERVALUE 
 ,CAST(LEFT(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME, LEN(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME) - 8)AS DATETIME)

HAVING DBA_PERFMON..COUNTERDATA.COUNTERVALUE>0.01
--HAVING DBA_PERFMON..COUNTERDATA.COUNTERVALUE/1024/1024<50
ORDER BY CAST(LEFT(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME, LEN(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME) - 8)AS DATETIME) DESC

END
GO


USP_RS_PERFMON_INSTANCENAME – With instance name of perfmon data display

USE [DBA_REPORT_CODE]
GO

/****** Object:  StoredProcedure [dbo].[USP_RS_PERFMON_INSTANCENAME]    Script Date: 21-02-2019 18:07:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- EXEC USP_RS_PERFMON_INSTANCENAME '\\KW3L1P41','LOGICALDISK','AVG. DISK SEC/TRANSFER','H:','2016-11-21 07:15:00.000'

create PROC [dbo].[USP_RS_PERFMON_INSTANCENAME]
(@SERVERNAME VARCHAR(100),@OBJECTNAME VARCHAR (100), @COUNTERNAME VARCHAR(100), @INSTANCENAME VARCHAR(100)=NULL,
@UPLOAD_DATE VARCHAR(100)=NULL)
AS
/*
Contact:     muthukkumaran kaliyamoorhty
Description: Generate report of perfmon data
changelog:
date         coder                          description
2018-15-june     muthukkumaran kaliyamoorhty     initial
*/

BEGIN
SELECT DBA_PERFMON..COUNTERDETAILS.MACHINENAME,
       DBA_PERFMON..COUNTERDETAILS.OBJECTNAME,
       DBA_PERFMON..COUNTERDETAILS.COUNTERNAME,
       DBA_PERFMON..COUNTERDETAILS.INSTANCENAME,
       DATEDIFF(MINUTE, ST.STARTTIME, CAST(LEFT(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME, LEN(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME) - 8) AS DATETIME)) AS INTERVAL,
       AVG(DBA_PERFMON..COUNTERDATA.COUNTERVALUE) AS COUNTERVALUE
     ,CAST(LEFT(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME, LEN(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME) - 8)AS DATETIME) AS LOAD_TIME
FROM   DBA_PERFMON..COUNTERDATA
INNER JOIN DBA_PERFMON..COUNTERDETAILS ON DBA_PERFMON..COUNTERDATA.COUNTERID = DBA_PERFMON..COUNTERDETAILS.COUNTERID,
(
SELECT DBA_PERFMON..COUNTERDETAILS.MACHINENAME,
MIN(CAST(LEFT(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME, LEN(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME) - 8)AS DATETIME))AS STARTTIME
FROM   DBA_PERFMON..COUNTERDATA
INNER JOIN DBA_PERFMON..COUNTERDETAILS ON DBA_PERFMON..COUNTERDATA.COUNTERID = DBA_PERFMON..COUNTERDETAILS.COUNTERID
GROUP  BY MACHINENAME
) AS ST

-------================================
WHERE  ST.MACHINENAME = DBA_PERFMON..COUNTERDETAILS.MACHINENAME

-------================================ OBJECTNAME
AND DBA_PERFMON..COUNTERDETAILS.MACHINENAME= @SERVERNAME
AND OBJECTNAME = @OBJECTNAME
AND COUNTERNAME = @COUNTERNAME
AND INSTANCENAME=@INSTANCENAME
AND DBA_PERFMON..COUNTERDATA.COUNTERDATETIME>=@UPLOAD_DATE

GROUP  BY DBA_PERFMON..COUNTERDETAILS.MACHINENAME,
          DBA_PERFMON..COUNTERDETAILS.OBJECTNAME,
          DBA_PERFMON..COUNTERDETAILS.COUNTERNAME,
          DBA_PERFMON..COUNTERDETAILS.COUNTERTYPE,
          DBA_PERFMON..COUNTERDETAILS.INSTANCENAME,
          DATEDIFF(MINUTE, ST.STARTTIME, CAST(LEFT(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME, LEN(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME) - 8) AS DATETIME))
      ,DBA_PERFMON..COUNTERDATA.COUNTERVALUE 
 ,CAST(LEFT(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME, LEN(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME) - 8)AS DATETIME)

--HAVING DBA_PERFMON..COUNTERDATA.COUNTERVALUE>0.1
--HAVING DBA_PERFMON..COUNTERDATA.COUNTERVALUE/1024/1024<50
ORDER BY CAST(LEFT(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME, LEN(DBA_PERFMON..COUNTERDATA.COUNTERDATETIME) - 8)AS DATETIME) DESC

END
GO


 

You can check the report by using the SP- EXEC USP_RS_PERFMON ‘\win-pc’,@OBJECTNAME=’PhysicalDisk’,@COUNTERNAME=’Avg. Disk sec/Transfer’,@UPLOAD_DATE=’2016-01-05 00:20:00.000′. (OR) you can directly query the tables by adjusting the SP codes.

Build reports

Use business intelligence studio and design a report and deploy it or import the RDL files.

This should be separate post, since it has lot of images to create reports. (Will write later)

Create a shared data source and data set. Create two reports one for without instance name and one is with instance name of perfmon counters.

Build_Report_business intelligence

View graph from SSRS

Create three folders called perfmon_counters and data source and data set. Upload all in the right folders and link each by using manages option.

The report server cannot process the report. The reference for the shared dataset ‘DataSet1’ is not valid. (rsInvalidDataSetReference)

This means the data set and data source is not linked with the reports.

main_page_1

Pass the parameters in the report and view the values in a graph.

report1

reportCPU

Perfmon baseline / Benchmark values.

Object Counter Rule of Thumb
Paging File % Usage < 70%
Processor % Processor Time <= 80%
Processor % Privilege Time < 40% of % Processor Time
Process(sqlservr) Process(msmdsrv) % Processor Time < 80%
System Processor Queue Length < 2
System Context Switches/sec < 1500
Physical Disk Avg ms/read < 8
Physical Disk Avg ms/write <1
Physical Disk Avg. Disk sec/Read <12 sec
Physical Disk Avg. Disk sec/Write < 2 sec
Logical/Physical Avg. Disk sec/Transfer 0.005-0.010 sec
Avg. Disk sec/Read 0.005-0.010 sec
Avg. Disk sec/Write 0.005-0.010 sec
Memory Available Mbytes =>300
Memory Page faults/sec
Memory Pages/sec <20
SQL Server: Memory Manager Memory Grants Pending ~0
SQL Server: Memory Manager Total Server Memory (KB)
SQL Server: Memory Manager Target Server Memory (KB)
SQL Server: Buffer Manager Free List Stalls/sec < 2
SQL Server: Buffer Manager Free Pages/sec
SQL Server: Buffer Manager Free Memory (KB)
SQL Server: Buffer Manager Stolen Server Memory (KB)
SQL Server: Buffer Manager Stolen Pages/sec Proportionate to Batch Requests /sec
SQL Server: Buffer Manager Lazy writes/sec <20 (Should be zero)
SQL Server: Buffer Manager Page life expectancy >300
SQL Server: Buffer Manager Page reads/sec <90
SQL Server: Buffer Manager Page writes/sec
SQL Server: Databases Log Flush Waits/sec ~0
SQL Server: General Statistics User Connections Proportionate to Batch Requests /sec
SQL Server: SQL Statistics Batch Requests/Sec Proportionate to workload
SQL Server: SQL Statistics SQL Server: SQL Statistics < 10% of Batch Requests/Sec
SQL Server: SQL Statistics SQL Re-Compilations/sec < 10% of SQL Compil-ations/sec
SQL Server:Access Methods Forwarded Records/sec < 10 per 100 Batch Requests/Sec
SQL Server:Access Methods Full Scans/sec Index Searches/sec)/(Full Scans/sec) > 1000
SQL Server:Access Methods Index Searches/sec Index Searches/sec)/(Full Scans/sec) > 1000

I cannot add all the images and steps here, since it will be long post, if anyone needed info feel free to contact me.

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

2 Comments

  • kishore kumar

    Hi Muthu,
    Thanks for scripts and steps. I have configured and uploaded to database, i could not configure ssrs,can you help me or send documents if any. I have shared my email in other comments.

    • Muthukkumaran Kaliyamoorthy

      Hi Kishore,
      I will share you. If you could not configure ssrs, you can run the t-sql with filters and can view or copy it excel – power pivot etc.

      SELECT counterdetails.machinename,
      counterdetails.objectname,
      counterdetails.countername,
      –counterdetails.countertype,
      counterdetails.instancename,
      Datediff(minute, st.starttime, Cast(LEFT(counterdata.counterdatetime, Len(counterdata.counterdatetime) – 8) AS DATETIME)) AS interval,
      Avg(counterdata.countervalue) AS countervalue
      –Avg(counterdata.countervalue) /1024/1024 AS counterValues_GB
      –counterdata.countervalue AS counterValues
      ,Cast(LEFT(counterdata.counterdatetime, Len(counterdata.counterdatetime) – 8)AS DATETIME) as Load_Time
      FROM counterdata
      INNER JOIN counterdetails ON counterdata.counterid = counterdetails.counterid,
      (
      SELECT counterdetails.machinename,
      Min(Cast(LEFT(counterdata.counterdatetime, Len(counterdata.counterdatetime) – 8)AS DATETIME))AS startTime
      FROM counterdata
      INNER JOIN counterdetails ON counterdata.counterid = counterdetails.counterid
      GROUP BY machinename
      ) AS st

      WHERE st.machinename = counterdetails.machinename

      AND objectname in (‘Paging File’,’Processor’,’Process(sqlservr) Process(msmdsrv)’
      ,’System’,’PhysicalDisk’,’Memory’,’SQLServer:Memory Manager’,’SQLServer:Buffer Manager’
      ,’SQLServer:Databases’,’SQLServer:General Statistics’,’SQLServer:SQL Statistics’
      ,’SQLServer:Access Methods’)

      –AND CounterName in (‘Processor Queue Length’,’% Usage’,’% Processor Time’,’% Privilege Time’,’Context Switches/sec’) — CPU
      –AND CounterName in (‘Avg ms/read’,’Avg ms/write’,’Avg. Disk sec/Read’,’Avg. Disk sec/Write’) — Disk

      –AND CounterName in( ‘Target Server Memory (KB)’,’Total Server Memory (KB)’) — general filter

      –/*
      AND CounterName in (‘Available Mbytes’,’Memory Grants Pending’,’Total Server Memory (KB)’,’Target Server Memory (KB)’
      ,’Free Memory (KB)’,’Stolen Server Memory (KB)’,’Stolen Pages/sec’,’Lazy writes/sec’
      ,’Page life expectancy’,’Lazy writes/sec’,’Page reads/sec’,’Page writes/sec’,’Buffer cache hit ratio’
      ,’Free Pages/sec’ , ‘Free List Stalls/sec’,’Pages/sec’
      )– RAM
      /
      /

      AND CounterName in (‘Log Flush Waits/sec’,’User Connections’,’Batch Requests/Sec’,’SQL Server: SQL Statistics’,’SQL Re-Compilations/sec’
      ,’Forwarded Records/sec’,’Full Scans/sec’,’Index Searches/sec’
      ) — Transaction
      */
      and Cast(LEFT(counterdata.counterdatetime, Len(counterdata.counterdatetime) – 8)AS DATETIME) between ‘2017-08-07’ and ‘2017-08-09’
      GROUP BY counterdetails.machinename,
      counterdetails.objectname,
      counterdetails.countername,
      counterdetails.countertype,
      counterdetails.instancename,
      Datediff(minute, st.starttime, Cast(LEFT(counterdata.counterdatetime, Len(counterdata.counterdatetime) – 8) AS DATETIME))
      ,counterdata.countervalue
      ,Cast(LEFT(counterdata.counterdatetime, Len(counterdata.counterdatetime) – 8)AS DATETIME)

      –having counterdata.countervalue>0
      –having counterdata.countervalue/1024/1024<50
      order by Cast(LEFT(counterdata.counterdatetime, Len(counterdata.counterdatetime) – 8)AS DATETIME) desc

      –Avg(counterdata.countervalue)/1024/1024

      — get the counter name for object/ counter
      /*
      — object
      select CounterName ,objectname from counterdetails where objectname like ‘%Memory%’
      group by CounterName,objectname

      — counter
      select CounterName ,objectname from counterdetails where countername like ‘%memory%’
      group by CounterName,objectname

      select CounterName ,objectname from counterdetails
      group by CounterName,objectname

      Target Server Memory (KB)
      Thread-safe memory objects waits
      Total Server Memory (KB)
      */

Leave a Reply

%d bloggers like this: