Performance Counters To CSV

Yes getting performance counters to a CSV is pretty simple but I wanted to make it even easier.This is a simple script (modified version of my previous performance data script) which allows you to run counters from sql, and dump the CSV in a specified location. Pretty simple setup...

After the variables are declared you will find the portion to set some variables:

1. @Directory: Set this to the Folder you want the CSV and Counter Text to be saved to. The logic in the script will automatically create this directory if it does not exist and add an additional layer in this path (folder) with the name of the server. Example: If you set to 'C:\' The directory created will be 'C:\Servername' Also, do not add the file name here, this is just the directory

2.@CounterFile: This is just the name of the text file that will be created that includes the selected counters. Example: 'Counters.txt'

3. @CSV: Set this to the name you want for the CSV that will get created in the directory.

Example: 'PerfData.csv'

4. @SI: This is the Sampling intervals for the performance counters. If you set to 5, it will run the counter for 5 seconds

5. @SC: This is the number of samples to collect. If you set to 5, it will collect the sample 5 times. So if @SI is set to 5 and @SC is set to 5, it will run each counter for 5 seconds 5 times taking a total of 25 seconds worth of performance counter data

NOTE: Also below these variables you set are the counters them self. Comment out the ones you do not want to run.

Here is the Code...

SET NOCOUNT ON

DECLARE

@cmd VARCHAR(500)

, @cmdshell VARCHAR(200)

, @SI VARCHAR(5)

, @SC VARCHAR(5)

, @CSV VARCHAR(100)

, @CounterFile VARCHAR(100)

, @Server VARCHAR(50)

, @Directory VARCHAR(100)

, @Directory2 VARCHAR(100)

, @CSV2 VARCHAR(100)

, @CounterFile2 VARCHAR(100)

SET @Server = (SELECT @@SERVERNAME)

--------------------------------------------------------------------------------------------------------

SET @Directory = 'D:\PerfMon' --Set Directory Location Where Files Will Be Saved.

SET @CounterFile = 'counters.txt' --Set File name for text file which contains counters.

SET @CSV = 'PerfType1.csv' --Set File Name For Counter CSV File

SET @SI = '2' --Set the sampling interval in seconds

SET @SC = '30' --Set the number of samples to collect

---------------------------------------------------------------------------------------------------------

SET @Directory2 = @Directory + '\' + @Server

SET @CounterFile2 = @Directory2 + '\' + @CounterFile

SET @CSV2 = @Directory2 + '\' + @CSV

SET @cmd = 'mkdir ' + @Directory2

EXEC master..xp_cmdshell @cmd

CREATE TABLE #Output (txt varchar(1000))

--Deletes Existing File

SET @cmd = 'del ' + @CounterFile

INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--System Counters

SET @cmd = 'echo \Memory\Pages/sec >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

SET @cmd = 'echo \Memory\Available Bytes >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

SET @cmd = 'echo \Processor(_Total)\% Processor Time >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \Processor(_Total)\% Privileged >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \Processor(_Total)\% User Time >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \Process(LSASS)\% Processor Time >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \Process(w3wp)\% Processor Time >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \Process(w3wp)\Working Set >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \Process(w3wp)\Private Bytes >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \Process(w3wp)\Page Faults/sec >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \Process(w3wp)\Working Set >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \Process(w3wp)\Working Set Peak >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \Process(w3wp)\Virtual Bytes >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \Process(w3wp)\Virtual Bytes Peak >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \Process(w3wp)\Private Bytes >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \Process(w3wp)\Page File Bytes >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \Process(w3wp)\Page File Bytes Peak >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \Process(OWSTIMER)\% Processor Time >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \ASP.NET\Request Execution Time >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \ASP.NET\Request Wait Time >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \ASP.NET\Requests Queued >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \ASP.NET\Requests Rejected >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \ASP.NET\Worker Process Restarts >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \ASP.NET\Application Restarts >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \System\Context Switches/sec >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

----Back End (SQL Server) Performance Counters

--SET @cmd = 'echo \Processor(_Total)\% Processor Time >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \Memory\Pages/sec >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \System\Context Switches/sec >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \Process(sqlservr)\% Processor Time >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \Process(sqlservr)\Working Set >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \Process(sqlservr)\Private Bytes >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \SQLServer:General Statistics\User Connections >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \SQLServer:Databases\Transactions/sec >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \SQLServer:Locks(_Total)\Number of Deadlocks/sec >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \SQLServer:Locks(_Total)\Lock Waits/sec >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \SQLServer:Locks(_Total)\Lock Wait Time (ms) >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \SQLServer:SQL Statistics\Batch Requests/sec >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \PhysicalDisk(_Total)\Current Disk Queue Length >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \PhysicalDisk(_Total)\Disk Read Bytes/sec >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

--SET @cmd = 'echo \PhysicalDisk(_Total)\Disk Write Bytes/sec >>' + @CounterFile2 INSERT INTO #Output EXEC master..xp_cmdshell @cmd

SET @cmdshell = 'TYPEPERF -f CSV -cf ' +@CounterFile2+ ' -si ' +@SI+ ' -sc ' +@SC+ ' -o ' +@CSV2

PRINT @CMDSHELL

EXEC master..xp_cmdshell @cmdshell

DROP TABLE #Output