Collects and stores performance Monitor data to SQL Server tables

Source: Internet
Author: User
Tags define log sql time interval server memory
server| Monitoring | data | performance
Collects and stores performance Monitor data to SQL Server tables

?

?

When we need to monitor the performance of the database SQL Server server, some database administrators may choose to operate the ' performance ' monitor provided by Windows (Start menu à admin tool à performance).

If the data collected by the performance Monitor can be logged to a database table in SQL Server, a lot of work may be much more convenient for us.
Turn on Performance Monitor
Click Start menu à run à execution (perfmon)

Or

Start menu à admin tools à performance



?

You'll see the picture below.



?
Define Performance Monitor Log
??????? ? If you want to set the monitor log, first expand the tree structure of the performance log and alarm, there will be three items you can choose (counter log, tracking log, alarm), now we set up the counter log. Right click on this node of the counter log, choose New Log Setting from the pop-up menu, fill in the Log name and confirm. Just to talk about the following picture:





??? The name I filled out is SQL Server so the current log file name appears:

C:\Perflogs QL_SERVER_000001.BLG

??????? The next step is to define the projects that you will be collecting or monitoring. We will select several different monitoring items as the record of this log we built. First we choose Processor Time. Click on the Add button, select%processor time and click Add, just as we see below this one.





????? ? Below we select the memory monitoring, in the Performance object Drop-down box select Memory, and then the following counter section select Pages/sec Count and click Add, the following figure





??????? We then follow the steps above to add the items we need to monitor to the list. After you have added all the items that you want to monitor, click the Close button and you can use the following figure for your reference:





???? As we saw on the way above, I chose many different monitoring items. I'll give you the following list below:

\\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) \%priviledged time

\\CN-BJ-TIGER\Processor (_total) \%processor time

\\CN-BJ-TIGER\Processor (_total) \%user time

\\CN-BJ-TIGER qlserver:buffer Manager\buffer Cache Hit ratio

\\CN-BJ-TIGER qlserver:general Statistics\user Connections

\\CN-BJ-TIGER qlserver:memeory manager\total Server Memory (KB)

\\CN-BJ-TIGER Qlserver:sql Statistics\batch request/sec

\\CN-BJ-TIGER Ystem\context switches/sec

\\CN-BJ-TIGER ystem\processor Queue Length



?

Each item in the above table is divided into three paragraphs, the first paragraph represents the server name, the second paragraph indicates what the object is to monitor, and the third represents the monitored item.

After selecting the full part of the monitoring project, you will continue to do something to complete the definition of log. We need to think about the sampling interval of our data, using the above diagram example, to set the sampling interval and time unit (initially 15s) below the dialog box. However, in general, 15s will not be suitable for all monitoring projects, so depending on the configuration of each server to determine the specific time interval, because the monitoring room must consume server resources, and the server will generate a large number of data collection work.

Once the above work is done, start setting the log file path and the way it is stored and the time plan to execute it. Click on the Log File tab:





In order to easily import files into SQL Server, you need to select a comma-separated text file to store the information. In this case, you can select a text file –csv? Format to store the log file.





??????? Then type in Run.bat

?????????????????????? ISQL-E-D pubs-i c:\perform\go.sql

??????? Save

?

??????? Then build the SQL script file as follows---the SQL script file name I created 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 for the database server





Will? ANSI Warning

???? ? ANSI nulls

Two options selected

So here we can find the monitored records from the Perflog table in the pubs database.


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.