SQL Server 2012 Note Sharing-32: Performance counter Monitoring SQL

Demand

Requires that Windows-brought Performance Monitor monitor the following objects of SQL and save the results for later analysis.

Performance object: SQL Server databases, counter: Log file (s) size and datafile (s) size

Performance object: SQL Server:buffer Manager, Counter: page reads/sec

Performance object: SQL server:memory Manager, Counter: Total Server memory (KB)

==================================================================

First, in the Start-management tool, find "Performance Monitor".

650) this.width=650; "title=" "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0px; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" "src="/HTTP// Img1.51cto.com/attachment/201406/21/639838_1403312861wamr.png "height=" 484 "/>

In the Performance Monitor interface, right-click to select Add Counters.

650) this.width=650; "title=" style= "border-top:0px;border-right:0px;background-image:none;border-bottom:0px; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" src= "http:// Img1.51cto.com/attachment/201406/21/639838_140331286613zt.png "height=" 448 "/>

First, add a SQL Server database counter.

650) this.width=650; "title=" style= "border-top:0px;border-right:0px;background-image:none;border-bottom:0px; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" src= "http:// Img1.51cto.com/attachment/201406/21/639838_1403312870e6on.png "height=" 443 "/>

When all is added, complete. Click OK.

650) this.width=650; "title=" "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0px; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" src= "http:// Img1.51cto.com/attachment/201406/21/639838_1403312875xzno.png "height=" 438 "/>

Then we create a new collection of data collectors.

650) this.width=650; "title=" style= "border-top:0px;border-right:0px;background-image:none;border-bottom:0px; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" src= "http:// Img1.51cto.com/attachment/201406/21/639838_1403312876agpt.png "height=" "/>"

Enter a name for the data collector.

650) this.width=650; "title=" to "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0px; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" src= "http:// Img1.51cto.com/attachment/201406/21/639838_1403312877uurr.png "height=" 475 "/>

Enter the location where the data is saved.

650) this.width=650; "title=" border-top:0px;border-right:0px;background-image:none;border-bottom:0px "style="; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" "src=" http:// Img1.51cto.com/attachment/201406/21/639838_1403312879yfcy.png "height=" 476 "/>

Once created, we right-click on the custom Data Collector's properties and can see the counters we added earlier.

650) this.width=650; "title=" "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0px; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px; "border=" 0 "alt=" "src=" http:// Img1.51cto.com/attachment/201406/21/639838_1403312883tyat.png "height=" 405 "/>

Then we right-click the SQL Performance Data collector and select Start.

650) this.width=650; "title=" border-top:0px;border-right:0px;background-image:none;border-bottom:0px "style="; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" src= "http:// Img1.51cto.com/attachment/201406/21/639838_1403312885ws02.png "height=" 481 "/>

After collecting a period of time, select Stop,.

650) this.width=650; "title=" style= "border-top:0px;border-right:0px;background-image:none;border-bottom:0px; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" src= "http:// Img1.51cto.com/attachment/201406/21/639838_1403312889nxwd.png "height=" 321 "/>

Then we open the report and can see the collected System Monitor logs.

650) this.width=650, "title=", "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0px; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" "src="/HTTP/ Img1.51cto.com/attachment/201406/21/639838_1403312891qxlv.png "height=" 334 "/>

You can see the SQL performance situation that was collected during this period.

650) this.width=650; "title=" one "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0px; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" one "src=" http:// Img1.51cto.com/attachment/201406/21/639838_140331289616l4.png "height=" 449 "/>

You can also adjust the way performance reports are displayed, as needed.

650) this.width=650; "title=" "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0px; padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" "src=" http:// Img1.51cto.com/attachment/201406/21/639838_1403312901v9sw.png "height=" 463 "/>

=================================================================

This article from "Zeng Hung Xin Technical column" blog, declined to reprint!