Improving Database Application Performance in VS .NET

Creator UPC-A Supplement 5 in VS .NET Improving Database Application Performance

8

System Monitor uses performance counters to represent the measurement of a specific object or system component. For example, the processor object has a counter that captures the percentage of processor time for a particular instance. Upon execution, System Monitor will begin monitoring three default counters (as shown in Table 8-4) for the current machine.

Table 8-4

Description Object Instance --Represents the rate at which pages are read and Memory written to the hard disk and can indicate system delays.

Physical Disk _Total This is the average number of read and write requests that are queued. If this number consistently exceeds a value of 2, you could have an IO bottleneck. Indicates the amount of processor activity by dis- Processor playing the percentage of busy time. This counter should not exceed 80% per CPU. _Total

To add additional counters, click the Add button (the plus sign on the toolbar) above the graph to open the Add Counters dialog box and select a performance object. Each object

represents a subsystem to be monitored and has one or more counters associated with it. Each counter might be associated with a particular instance, such as the ASP.NET worker process. In some cases, the counters are associated with all instances, which is represented as _Total. In cases where the object is not associated with an instance, the instance list box will be unavailable.

Your Windows user account will need to be a member of the local Administrators group or Performance Log Users group for the server being monitored, or a member of the Domain Admins group for the network.

The next section, Investigate Performance Problems, lists some additional performance counters that can be helpful for investigating specific problems with your server. Exam Tip To understand how System Monitor works, you can download a free tool from MSDN called the Performance Monitor Wizard. This tool will help you select configuration settings and determine which counters are best. The utility is available at http://www.microsoft.com/downloads /details.aspx FamilyID=31fccd98-c3a1-4644-9622-faa046d69214&displaylang=en&Hash=7YTJ6NC.

8

A dynamic management view (DMV) can be included in a SELECT query to return information about the health of your server and databases. New to SQL Server 2005, DMVs expose statistical information as a result set. The information included in each view can be critical to diagnosing problems with your SQL Server.

You will need the SELECT permission for each object, along with the VIEW SERVER STATE or VIEW DATABASE STATE permission to use a DMV in a SELECT query. Which permission is required depends on whether the DMV is server-scoped or database-scoped.

SQL Server 2005 provides dozens of views to represent all areas of SQL Server. Each DMV bears a prefix of dm_, and to reference the view in a SELECT query, you must use the two-part name. For example, the following query can be used to access the dm_exec_connections view:

The previous query will return information about all open connections. This will include a time stamp for the connection time, last read and write operations, and a count of the number of reads and writes. This DMV is just one of several built-in views that can be used to diagnose the health of your SQL Server.

The previous section identified tools that you can use to analyze the performance of your SQL Server. Collecting the necessary information is the easy part. The hard part is identifying where a performance issue might lie. Ideally, you should be able to identify issues before end users suffer the consequences. This will allow you to proactively monitor and tune your SQL Server and ensure that all your database applications perform at their best.