Ultimate free performance monitoring tool for SQL Server

Ok... "Maybe" I had overcharged the post title but this script creates a free monitoring platform by using windows performance monitor and saves all the information in only 3 SQL Server tables. After that the information can be analyzed in Excel.

:: ⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃
::
::	[OBJECT NAME]: SQLxCtrl_perfinst.cmd
::	[Version]: 1.3
::
::	[DESCRIPTION]: Creates a performance monitoring tool for a SQL Server with only free windows tools
::	[CODE BY]: Pedro Ferreira (pdrsntsfrr.com)
::
::	[NOTES]: ---
::
::	[INPUT PARAMETERS]: ---
::
::	[OUTPUT PARAMETERS]: ---
::
::
::	[MODIFICATION HISTORY]:
::		Version	  Date         Author                 Comment
::		───────   ──────────   ────────────────────   ───────────────────────────────────────────────────────────
::		1.0       10-09-2014   Pedro Ferreira         initial version
::		1.1       01-05-2015   Pedro Ferreira         created variables
::		1.2       10-01-2016   Pedro Ferreira         auto-create file with performance counters
::		1.3       11-02-2016   Pedro Ferreira         delete file with list of perf counters
::
:: ⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃⁃


:: Some examples of performance counters
::\Processor(_Total)\% Processor Time
::\Processor(_Total)\% Privileged Time
::\Process(sqlservr)\% Processor Time
::\System\Context Switches/sec
::\System\Processor Queue Length
::\Memory\Available MBytes
::\Memory\Free System Page Table Entries
::\Memory\Pages Input/sec
::\Memory\Pages/sec
::\Paging File(_Total)\% Usage
::\Paging File(_Total)\% Usage Peak
::\PhysicalDisk(_Total)\Avg. Disk Read Queue Length
::\PhysicalDisk(_Total)\Avg. Disk sec/Read
::\PhysicalDisk(_Total)\Avg. Disk sec/Transfer
::\PhysicalDisk(_Total)\Avg. Disk sec/Write
::\PhysicalDisk(_Total)\Avg. Disk Write Queue Length


:: cleaning up screen
cls

:: > > Setting up variables
:: SQL Server instance that stores all the data
set sqlserverinstance=SERVER\INSTANCE
:: Authentication information to server
set domainuser=DOMAIN\USER
set domainpass=PASSWORD
:: Name of SQL Server Instance ( SQLServer for default instance | MSSQL$NameOfSqlInstance for named instance)
set nameofsqlserverinstance1=SQLServer
:: Filepath of performance counters list
set perfcounterslist=C:\SQLxCtrl_counterslist.txt
:: Counter interval (in seconds)
set counterinterval=15
:: < <

echo off

:: > > Load some performance counters to a txt file. 
::Overwrite any file that already exists 
break> %perfcounterslist%

::Append counters to the created file
@echo \Processor(_Total)\% Processor Time>> %perfcounterslist%
@echo \Processor(_Total)\% Privileged Time>> %perfcounterslist%
::
@echo \System\Context Switches/sec>> %perfcounterslist%
@echo \System\Processor Queue Length>> %perfcounterslist%
@echo \System\Processes>> %perfcounterslist%
::
@echo \Memory\Available MBytes>> %perfcounterslist%
@echo \Memory\Free System Page Table Entries>> %perfcounterslist%
@echo \Memory\Pages Input/sec>> %perfcounterslist%
@echo \Memory\Pages/sec>> %perfcounterslist%

::SO/PageFile LogicalDisk
@echo \LogicalDisk(C:)\Free Megabytes>> %perfcounterslist%
@echo \LogicalDisk(C:)\Avg. Disk sec/Read>> %perfcounterslist%
@echo \LogicalDisk(C:)\Avg. Disk sec/Transfer>> %perfcounterslist%
@echo \LogicalDisk(C:)\Avg. Disk sec/Write>> %perfcounterslist%
@echo \LogicalDisk(C:)\Avg. Disk Read Queue Length>> %perfcounterslist%
@echo \LogicalDisk(C:)\Avg. Disk Write Queue Length>> %perfcounterslist%


::Log files LogicalDisk
::@echo \LogicalDisk(L:)\Free Megabytes>> %perfcounterslist%
::@echo \LogicalDisk(L:)\Avg. Disk sec/Read>> %perfcounterslist%
::@echo \LogicalDisk(L:)\Avg. Disk sec/Transfer>> %perfcounterslist%
::@echo \LogicalDisk(L:)\Avg. Disk sec/Write>> %perfcounterslist%
::@echo \LogicalDisk(L:)\Avg. Disk Read Queue Length>> %perfcounterslist%
::@echo \LogicalDisk(L:)\Avg. Disk Write Queue Length>> %perfcounterslist%

::SQL Data files LogicalDisk
::@echo \LogicalDisk(S:)\Free Megabytes>> %perfcounterslist%
::@echo \LogicalDisk(S:)\Avg. Disk sec/Read>> %perfcounterslist%
::@echo \LogicalDisk(S:)\Avg. Disk sec/Transfer>> %perfcounterslist%
::@echo \LogicalDisk(S:)\Avg. Disk sec/Write>> %perfcounterslist%
::@echo \LogicalDisk(S:)\Avg. Disk Read Queue Length>> %perfcounterslist%
::@echo \LogicalDisk(S:)\Avg. Disk Write Queue Length>> %perfcounterslist%

::TempDB files LogicalDisk
::@echo \LogicalDisk(T:)\Free Megabytes>> %perfcounterslist%
::@echo \LogicalDisk(T:)\Avg. Disk sec/Read>> %perfcounterslist%
::@echo \LogicalDisk(T:)\Avg. Disk sec/Transfer>> %perfcounterslist%
::@echo \LogicalDisk(T:)\Avg. Disk sec/Write>> %perfcounterslist%
::@echo \LogicalDisk(T:)\Avg. Disk Read Queue Length>> %perfcounterslist%
::@echo \LogicalDisk(T:)\Avg. Disk Write Queue Length>> %perfcounterslist%


@echo \%nameofsqlserverinstance1%:Access Methods\Full Scans/sec>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:Access Methods\Page Splits/sec>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:Access Methods\Workfiles Created/sec>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:Access Methods\Worktables Created/sec>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:Buffer Manager\Buffer cache hit ratio>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:Buffer Manager\Checkpoint pages/sec>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:Buffer Manager\Free pages>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:Buffer Manager\Lazy writes/sec>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:Buffer Manager\Page life expectancy>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:Buffer Manager\Page reads/sec>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:Buffer Manager\Page writes/sec>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:Buffer Manager\Stolen pages>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:General Statistics\Logins/sec>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:General Statistics\Logouts/sec>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:General Statistics\User Connections>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:Latches\Average Latch Wait Time (ms)>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:Locks(_Total)\Average Wait Time (ms)>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:Locks(_Total)\Lock Requests/sec>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:Locks(_Total)\Number of Deadlocks/sec>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:Memory Manager\Target Server Memory (KB)>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:Memory Manager\Total Server Memory (KB)>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:SQL Statistics\Batch Requests/sec>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:SQL Statistics\SQL Compilations/sec>> %perfcounterslist%
@echo \%nameofsqlserverinstance1%:SQL Statistics\SQL Re-Compilations/sec>> %perfc%ounterslist%
:: < <


::Login to SQL Server and created the tables
sqlcmd -S %sqlserverinstance%

/*
USE master;
ALTER DATABASE [SQLxCtrl] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [SQLxCtrl];
GO
*/
CREATE DATABASE [SQLxCtrl];
GO
ALTER DATABASE SQLxCtrl SET AUTO_SHRINK OFF;
GO
ALTER DATABASE SQLxCtrl SET AUTO_CREATE_STATISTICS ON;
GO
ALTER DATABASE SQLxCtrl SET AUTO_UPDATE_STATISTICS ON;
GO
ALTER DATABASE SQLxCtrl SET AUTO_UPDATE_STATISTICS_ASYNC OFF;
GO
CREATE TABLE [SQLxCtrl].[dbo].[CounterData](
 [GUID] [uniqueidentifier] NOT NULL,
 [CounterID] [int] NOT NULL,
 [RecordIndex] [int] NOT NULL,
 [CounterDateTime] [char](24) NOT NULL,
 [CounterValue] [float] NOT NULL,
 [FirstValueA] [int] NULL,
 [FirstValueB] [int] NULL,
 [SecondValueA] [int] NULL,
 [SecondValueB] [int] NULL,
 [MultiCount] [int] NULL,
CONSTRAINT PK_GUID_CounterID_RecordIndex PRIMARY KEY CLUSTERED 
(
 [GUID] ASC,
 [CounterID] ASC,
 [RecordIndex] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
GO
CREATE TABLE [SQLxCtrl].[dbo].[CounterDetails](
 [CounterID] [int] IDENTITY(1,1) NOT NULL,
 [MachineName] [varchar](1024) NOT NULL,
 [ObjectName] [varchar](1024) NOT NULL,
 [CounterName] [varchar](1024) NOT NULL,
 [CounterType] [int] NOT NULL,
 [DefaultScale] [int] NOT NULL,
 [InstanceName] [varchar](1024) NULL,
 [InstanceIndex] [int] NULL,
 [ParentName] [varchar](1024) NULL,
 [ParentObjectID] [int] NULL,
 [TimeBaseA] [int] NULL,
 [TimeBaseB] [int] NULL,
CONSTRAINT PK_CounterDetails PRIMARY KEY CLUSTERED 
(
 [CounterID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
GO
CREATE TABLE [SQLxCtrl].[dbo].[DisplayToID](
 [GUID] [uniqueidentifier] NOT NULL,
 [RunID] [int] NULL,
 [DisplayString] [varchar](900) NOT NULL,
 [LogStartTime] [char](24) NULL,
 [LogStopTime] [char](24) NULL,
 [NumberOfRecords] [int] NULL,
 [MinutesToUTC] [int] NULL,
 [TimeZoneName] [char](32) NULL,
CONSTRAINT PK_DisplayToID PRIMARY KEY CLUSTERED 
(
 [GUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT UK_DisplayString UNIQUE NONCLUSTERED 
(
 [DisplayString] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
GO
EXIT

:: Create a SYSDSN ODBC connection
ODBCCONF CONFIGSYSDSN "SQL Server" "DSN=SQLxCtrl_DSN|Description=SQLxCtrl DSN by PdrSntsFrrr|SERVER=%sqlserverinstance%|Trusted_Connection=1|Database=SQLxCtrl"


:: > > CREATE A DATA COLLECTOR SET (DCS)
:: Stoping the DCS
logman stop SQLxCtrl_DCS

:: Delete the DCS
logman delete SQLxCtrl_DCS

:: Create a counter with DSN connection, with a specified interval, from a created list of performance counters
logman create counter SQLxCtrl_DCS -f sql -o SQLxCtrl_DSN -si %counterinterval% -cf %perfcounterslist% -u %domainuser% %domainpass%

:: Start the DCS
logman start SQLxCtrl_DCS
:: < <


::Delelte the file with the list of performance counters
del %perfcounterslist%

I hope it helps!