Comienzo En Sql En Adventure Works

Monitoring, Optimizing, and Troubleshooting Performance
With an increase in the size of a database or the number of users, the performance of the server might be affected. An important task of a database administrator is to monitor the performance of the database server in terms of the time taken in responding to user queries or generating reports. At times, if a transaction leaves the server ina nonresponsive state, an administrator needs to troubleshoot the server. This chapter discusses the ways of monitoring and optimizing the performance of the database server by using various tools provided by SQL Server 2005. Further, it explains how to troubleshoot a nonresponsive server.

Objectives
In this chapter, you will learn to: Monitor and optimize performance Troubleshoot performanceChapter 5

Monitoring and Optimizing Performance
One of the important activities of an administrator includes monitoring the performance of the server. The performance of a database server is measured by the time and resources consumed in responding to queries of the users. An administrator should regularly monitor the performance of the database server and implement appropriate solutionsto optimize it. SQL Server provides various tools such as SQL Server Profiler, Log File Viewer, Dynamic Management Views (DMVs), and Database Tuning Advisor to monitor and optimize performance. In addition, you can also monitor the performance using the System Monitor tool provided by the Windows operating system.

Monitoring Performance by Using SQL Server Profiler
While monitoringperformance, an administrator needs to check for the events occurring on the server. These events include the execution of T-SQL statements or a change in the database properties. SQL Server Profiler provides a graphical user interface to monitor the life cycle of the transactions being executed on a particular instance of the database engine. You can track the login details of users, or the execution ofindividual T-SQL statements or transactions. This tool is used by business application developers to identify the T-SQL statements or stored procedures that slow down the performance of the server. To enable server monitoring, SQL Server Profiler tracks the events that occur in the database engine. An event is an action generated within an instance of the SQL Server, such as execution of T-SQLstatements, start or end of stored procedures, error returned by the database engine. An event can also include locks acquired or released by the database objects. SQL Server Profiler uses a trace to store the details of events you need to monitor. While monitoring the performance, you need to create a trace and specify the type of events you need to track.

Creating, Configuring, and Starting aTrace
While creating a trace, you can specify the events and the values for the events that you need to monitor. For example, you might need to monitor only those events for which the CPU time is greater than or equal to100 milliseconds. Similarly, you can also monitor the events only for selected logins. To specify the values for which you want to monitor the events, you can apply filters.
NIITMonitoring, Optimizing, and Troubleshooting Performance 5.3

If you need to monitor the same set of events regularly, you can store the trace settings in a template. SQL Server Profiler supports the following types of templates: Default Template Custom Template Default Template The default template is the template shipped with SQL Sever Profiler. Under the default template, the SQL Server capturestypical values from a trace, such as start time, end time, and log on name. Custom Template In addition to the default template, SQL Server Profiler allows the database administrators to create their own templates. You can select the columns and values to be included in the trace. Consider an example. Robert, a database developer at AdventureWorks, Inc. has notified the database administrator...