Practical Solution to SQL Server Performance Monitoring

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.

Continues…

Pages: 1 2 3 4 5




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |