Collect and store performance monitor data to SQL Server table
?
?
When we need to monitor the performance of the database SQL Server server, some database managers may choose Windows to operate for us 'performance' monitor (start menu à management tool à performance).
If the data collected by the performance monitor can be recorded in the database table of SQL Server, many work may be much more convenient for us.
Open performance monitor
Click Start Menu à Run à Execution (Perfmon)
or
Start menu à Administrative tool à performance
?
You can see the following screen.
?
Define Performance Monitor LOG
???????? If you want to set up the logger's log, first expand the performance log and alarm of the tree structure, three items you can choose (counter log, tracking log, alarm), now let us set Counter log. Right click on this node of the counter log, select New Log Setting from the pop-up menu, fill in the log name and determined. Talk about the following screen:
??? The name I fill in is SQL Server so the current log file name will appear:
C: /perflogs/SQL_SERVER_000001.BLG
??????? Next is to define the item you will be to collect or have to monitor. We will choose several different monitoring projects as this log of this LOG we have established. First, let's choose Processor Time first. Click Add button to select the% Processor Time, click Add, just as you see below.
?????? Below we choose memory monitoring, select Memory in the Performance Object drop-down box, then then the counter section selects the Pages / Sec to count and click Add, as shown below
After the operation, we repeatedly follow the steps above, and add the items we need to monitor to the list. After you add all the items to be monitored, click the Close button, you can use the following graphic reference:
???? As we see on the way, I chose a lot of different monitoring projects. I will give you the following:
// CN-BJ-TIGER / MEMORY / AVAILABLE BYTES
// CN-BJ-TIGER / Process (SQLSERVR) / Page Faults / Sec
// CN-BJ-TIGER / Process (SQLSERVR) / Working Set
// CN-BJ-TIGER / Processor (_total) /% Priviled Time
// CN-BJ-TIGER / Processor (_total) /% Processor Time
// CN-BJ-TIGER / Processor (_total) /% USER TIME
// CN-BJ-TIGER / SQLSERVER: BUFFER Manager / Buffer Cache Hit Ratio
// CN-BJ-TIGER / SQLSERVER: General Statistics / User Connections
// CN-BJ-TIGER / SQLSERVER: MEMEORY Manager / Total Server Memory (KB)
// CN-BJ-TIGER / SQLSERVER: SQL STATISTICS / BATCH REQUEST / Sec
// CN-BJ-TIGER / SYSTEM / CONTEXT SWITCHES / SEC
// CN-BJ-TIGER / System / Processor Queue Length
?
Each project in the above table is divided into three segments, the first segment represents the server name, the second segment indicates what the object to monitor, the third paragraph represents the monitoring item.
After selecting a complete monitoring project, you will continue to do something to complete the definition of logs. We need to consider that we will give the data sampling interval, with the above figure, you can set the spacing time and time unit (initial 15S) below the dialog box. However, in general, 15S does not fit all of the monitoring items, so according to each of the different servers, the specific interval is determined because the monitoring room must consume server resources, and the server will also generate a large amount of data collection. After the above work is finished, start setting the storage path and way of the log file and the time plan of the execution. Click on the Log File tab:
For more easily imports files into SQL Server, you need to select a text file with a comma-divided form to store information. In this case, you can select the text file - CSV? Format to store the log file.
??????? Type again in Run.bat
??????????????????????? isql -e -d pubs -i c: /perform/go.sql
??????? save
?
??????? Then establish the SQL script file as follows --- I created SQL script file name is go.sql
????????????? set ANSI_NULLS ON
SET ANSI_WARNINGS ON
?
SELECT * INTO Perflog
From
Openrowset ('microsoft.jet.oledb.4.0', 'text; HDR = NO; Database = C: / PERFLOGS /', SQL_SERVER_000001 # csv)
?
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
Go
?
Before you change the connection settings of the database server
Will? ANSI WARNING
????? ANSI NULLS
Two options are selected
Good to here, we can find the recorded records from the PERFLOG table in the Pubs database.
