SJperformanceset.htm:
<HTML>
<HEAD>
<META NAME=”GENERATOR” Content=”Microsoft System Monitor”>
<META HTTP-EQUIV=”Content-Type” content=”text/html; charset=iso-8859-1″>
</HEAD><BODY>
<OBJECT ID=”DISystemMonitor1″ WIDTH=”100%” HEIGHT=”100%” CLASSID=”CLSID:C4D2D8E0-D1DD-11CE-940F-008029004347″>
<PARAM NAME=”_Version” VALUE=”196611″>
<PARAM NAME=”LogName” VALUE=”SJperformanceset”>
<PARAM NAME=”Comment” VALUE=””>
<PARAM NAME=”LogType” VALUE=”0″>
<PARAM NAME=”CurrentState” VALUE=”0″>
<PARAM NAME=”LogFileMaxSize” VALUE=”-1″>
<PARAM NAME=”LogFileBaseName” VALUE=”SJperformanceset”>
<PARAM NAME=”LogFileSerialNumber” VALUE=”1″>
<PARAM NAME=”LogFileFolder” VALUE=”C:PerfLogs”>
<PARAM NAME=”LogFileAutoFormat” VALUE=”6″>
<PARAM NAME=”LogFileType” VALUE=”0″>
<PARAM NAME=”StartMode” VALUE=”0″>
<PARAM NAME=”StopMode” VALUE=”0″>
<PARAM NAME=”RestartMode” VALUE=”0″>
<PARAM NAME=”LogFileName” VALUE=”C:PerfLogsSJperformanceset_02281337.csv”>
<PARAM NAME=”EOFCommandFile” VALUE=””>
<PARAM NAME=”Counter00001.Path” VALUE=”MemoryAvailable MBytes”>
<PARAM NAME=”Counter00002.Path” VALUE=”MemoryPages/sec”>
<PARAM NAME=”Counter00003.Path” VALUE=”PhysicalDisk(0 C:)% Idle Time”>
<PARAM NAME=”Counter00004.Path” VALUE=”PhysicalDisk(0 C:)Avg. Disk Queue Length”>
<PARAM NAME=”Counter00005.Path” VALUE=”Processor(_Total)% Processor Time”>
<PARAM NAME=”Counter00006.Path” VALUE=”SQLServer:Access MethodsFull Scans/sec”>
<PARAM NAME=”Counter00007.Path” VALUE=”SQLServer:Buffer ManagerBuffer cache hit ratio”>
<PARAM NAME=”Counter00008.Path” VALUE=”SQLServer:Databases(pubs)Transactions/Sec”>
<PARAM NAME=”Counter00009.Path” VALUE=”SQLServer:General StatisticsUser Connections”>
<PARAM NAME=”Counter00010.Path” VALUE=”SQLServer:Locks(_Total)Average Wait Time (ms)”>
<PARAM NAME=”Counter00011.Path” VALUE=”SystemProcessor Queue Length”>
<PARAM NAME=”CounterCount” VALUE=”11″>
<PARAM NAME=”UpdateInterval” VALUE=”60″>
<PARAM NAME=”SampleIntervalUnitType” VALUE=”2″>
<PARAM NAME=”SampleIntervalValue” VALUE=”1″>
</OBJECT>
</BODY>
</HTML>
SJperformanceset Table Script:
CREATE TABLE [SJperformanceset] (
[CounterDateTime] [datetime] NOT NULL,
[Available MBytes] [numeric](18, 2) NULL,
[Page Reads/sec] [numeric](18, 2) NULL,
[Percent Idle Time] [numeric](18, 2) NULL,
[Avg Disk Queue Length] [numeric](18, 2) NULL,
[Processor Time] [numeric](18, 2) NULL,
[Full Scans/sec] [numeric](18, 2) NULL,
[Cache Hit Ratio] [numeric](18, 2) NULL,
[Transactions/Sec] [numeric](18, 2) NULL,
[User Connections] [numeric](18, 2) NULL,
[Average Wait Time] [numeric](18, 2) NULL,
[Processor Queue Length] [numeric](18, 2) NULL
)
GO
ALTER TABLE [SJperformanceset] WITH NOCHECK ADD
CONSTRAINT [PK_SJperformanceset] PRIMARY KEY CLUSTERED
(
[CounterDateTime]
)
GO
Insert Monitoring Data Script:
INSERT INTO [SJperformanceset] (
[CounterDateTime]
,[Available MBytes]
,[Page Reads/sec]
,[Percent Idle Time]
,[Avg Disk Queue Length]
,[Processor Time]
,[Full Scans/sec]
,[Cache Hit Ratio]
,[Transactions/Sec]
,[User Connections]
,[Average Wait Time]
,[Processor Queue Length]
)
SELECT
[(PDH-CSV 4#0) (Pacific Standard Time)(480)]
,cast([MemoryAvailable MBytes] as float)
,cast([MemoryPages/sec] as float)
,cast([PhysicalDisk(0 C:)% Idle Time] as float)
,cast([PhysicalDisk(0 C:)Avg# Disk Queue Length] as float)
,cast([Processor(_Total)% Processor Time] as float)
,cast([SQLServer:Access MethodsFull Scans/sec] as float)
,cast([SQLServer:Buffer ManagerBuffer cache hit ratio] as float)
,cast([SQLServer:Databases(pubs)Transactions/Sec] as float)
,cast([SQLServer:General StatisticsUser Connections] as float)
,cast([SQLServer:Locks(_Total)Average Wait Time (ms)] as float)
,cast([SystemProcessor Queue Length] as float)
FROM OpenDataSource( ‘Microsoft.Jet.OLEDB.4.0’,
— csv file on a local drive
‘Data Source=C:PerfLogs;Extended properties=Text’)…SJperformanceset_02272122#csv
If you need to consistently monitor the database server’s performance and analyze the log data, you can run both the file copying process and the data importing process using the scheduler.