The first tab on the Task Manager is the Applications tab. The Applications view provides a list of tasks in the left column and the status of these applications in the right column. The status information enables you to determine whether an application like SQL Server Management Studio or SQL Server Configuration Manager is running and allows you to terminate an application that is not responding, in trouble, or causing problems for your server. To stop such an application, highlight the particular application and click End Task at the bottom of the Task Manager. You can also switch to another application if you have several applications running. To do so, highlight the program and click Switch To at the bottom of the Task Manager. Finally, you can create a dump file that can be used when a point-in-time snapshot of every process running is needed for advanced troubleshooting. To create a dump file, right-click on an application and select Create Dump File.

The second Task Manager tab is the Processes tab. It provides a list of running processes on the server. It also measures the performance in simple data format. This information includes CPU percent used, memory allocated to each process, and username used in initiating a process, which includes system, local, and network services.

If the initial analysis of the process on your server, such as SQL Server Integration Package or SQL Server instance, takes up too much CPU percentage or uses too many memory resources, thereby hindering server performance, you can sort the processes by clicking the CPU or Memory (Private Working Set) column header. The processes are then sorted in order of usage. This way, you can tell which one is slowing down performance on your server. You can terminate a process by selecting the process and clicking the End Process button.

Many other performance or process measures can be removed or added to the Processes view. They include, but are not limited to, process identifier (PID), CPU time, session ID, and page faults. To add these measures, select View, Select Columns to open the Select Column property page. Here, you can add process counters to the process list or remove them from the list.

The newest addition to the family of Task Manager tabs is the Services tab. When this tab is selected, you can quickly assess and troubleshoot a specific Windows or SQL Server service by viewing whether it has stopped or is still running. The Services tab also offers additional key details including the service name, service description, and service group. In addition, it is also possible to launch the Services snap-in if you need to make changes to a specific service. For example, if you know a given service should be running and you don’t see it running in the Process tab (common services include SQL Server Browser, SQL Server, or SQL Server Agent), you can just go to the Services tab and attempt to start the service from there. It’s very rudimentary, but in keeping with what Task Manager is typically used for, it does offer a quick overview of system status and preliminary problem resolution.

The Performance tab allows you to view the CPU and pagefile usage in graphical form. This information is especially useful when you need a quick view of a performance bottleneck. The Performance tab makes it possible to graph a percentage of processor time in Kernel mode. To show this, select View, Show Kernel Times. The kernel time is represented by the red line in the graph. The kernel time is the measure of time that applications are using operating system services. The other processor time is known as User mode. User mode processor time is spent in threads that are spawned by applications on the system. If your server has multiple CPU processors installed, you can view multiple CPU graphs at a time by selecting View, CPU History. Also on the Performance tab you will find a button labeled Resource Monitor. You can invoke Resource Monitor for additional analysis of the system. Resource Monitor is covered in the section “Using the Windows Server 2008 Performance and Reliability Monitoring Tools.”

The Networking tab provides a measurement of the network traffic for the connections on the local server in graphical form. This utility is a great way to monitor database mirroring traffic between two SQL Server systems. For multiple network connections—whether they are dial-up, LAN connections, a WAN connection, a VPN connection, or the like—the Networking tab displays a graphical comparison of the traffic for each connection. It provides a quick overview of the adapter, network utilization, link speed, and state of your connection.

To show a visible line on the graph for network traffic on any interface, the view automatically scales to magnify the view of traffic versus available bandwidth. The graph scales from 0% to 100% if the Auto Scale option is not enabled. The greater the percentage shown on the graph, the less is the magnified view of the current traffic. To auto-scale and capture network traffic, select Options, Auto Scale.

It is possible to break down traffic on the graph into Bytes Sent, Received, and Total Bytes by selecting View, Network Adapter History and checking the selections you’d like graphed. This can be useful if you determine the overall throughput is high and you need to quickly determine if inbound or outbound traffic is an issue. In this situation, the default setting is displayed in Total Bytes.

You can also add more column headings by selecting View, Select Columns. Various network measures can be added or removed; they include Bytes Throughput, Bytes Sent/Interval, Unicast Sent and Received, and so on.

The final tab on the Task Manager is the Users tab, which displays a list of the users who can access the server, session status, and names. The following five columns are available on the Users tab:

As mentioned earlier, not only can DMVs assist you with performance turning and monitoring, but they can also provided detailed information when you need to monitor a SQL Server system. For example, the sys.dm_os_sys_info view can be used to determine the number of logical CPUs in a system, hyperthread ratio between the logical and physical processors, and the amount of physical memory available in the system. Here is the Transact-SQL code that illustrates this example, including the results.

Another useful DMV that is applicable at the database scope level is sys.dm_tran_locks. It allows a DBA to obtain information on currently active Lock Manager resources. Locks that have been granted or waiting are displayed.

Here are examples of index-related DMVs that gather information pertaining to index usage information within a database. The sys.dm_db_index_usage_stats view is a great DMV to validate which indexes are not being heavily utilized and which indexes are causing maintenance overhead. The sys.dm_db_missing_index_details DMV returns information about missing indexes. Additional parameters identify the database and the table where the missing index resides.

To set up auditing, a DBA first creates a SQL Server Audit object and then specifies the location to which the audited events will be written. Audits can be saved to the Windows Security Log, the Windows Application Log, or to a file on the network infrastructure. The audit destination is also referred to as a target. Creating a SQL Server Audit object can be performed by using SSMS or Transact-SQL. Within a SQL Server instance, it is possible to generate more than one SQL Server Audit object; however, each Server Audit object would have its own audit destination.

In mission-critical environments where security and auditing are a major concern or requirement, it is a best practice to write auditing data to the Windows Security Log. The Windows Application Log is inferior compared to the Windows Security Log as it requests lower permissions and any authenticated user can read or write to and from the log. In addition, files on the network are vulnerable to anyone who has access to the file system. Therefore, the Windows Security Log is the best choice as it offers the highest level of security, making audit data less subject to tampering.

A Server Audit Specification object is associated with a SQL Server Audit object. It defines the server event that will be audited based on a predefined set of SQL Server Audit action types. Examples of Server Audit Action Groups include: Server Permission Change Group, Database Ownership Change Group, and Failed Login Group. There is a plethora of Server Audit action types that a DBA can choose from for auditing a SQL Server instance. Review the topic “Server-Level Audit Action Groups” in SQL Server 2008 Books Online for a full listing of Server-Level Audit Action Groups.

There is a one-to-one mapping between server audit specifications and server audits. Multiple server audit specifications can be created; however, they cannot be bound to the same server audit. Each specification requires its own server audit.

A Database Audit Specification object is also associated with a server audit. It is similar to the Server Audit Specification object. In this case, however, the Database Audit Specification object defines the database event that will be audited based on a predefined set of Database-Level Audit Action Groups. Examples of Database-Level Audit Action Groups include DATABASE_ROLE_MEMBER_CHANGE_GROUP, DATABASE_OPERATION_GROUP, and SCHEMA_OBJECT_CHANGE_GROUP. As with server audit specifications, there are many predefined Database-Level Audit Action Groups that are applicable to a database. In addition, database-level audits can be linked to a specific database action; for example, an event can be raised whenever a SELECT, UPDATE, INSERT, DELETE, EXECUTE, or RECEIVE statement is issued. Review the topic “Database-Level Audit Action Groups and Database-Level Audit Actions” in SQL Server 2008 Books Online for a full listing of Database-Level Audit Action Groups and respective actions.

It should be noted that there is a one-to-one mapping between database audit specifications and server audits. Multiple database audit specifications can be created; however, they cannot be bound to the same server audit. Each specification requires its own server audit.

The first step in this four-phase process is to create the Audit object. This example creates an audit called TOR-SQL01-Audit, which stores the audit logs to a file residing in a SQLServerAudit folder located on the L: drive. In addition, a queue delay of 1000 is used and maximum and minimum rollover settings are set to unlimited.

Now that the audit has been created, the next step is to enable the audit. This can be done by executing the following Transact-SQL syntax.

The following Transact-SQL syntax illustrates how to create a server audit specification to capture failed logins based on our example.

The final phase in the process is to view the audit log for any irregularities or suspicious activity. Ironically, this is one of the most critical steps, which is too often overlooked. Before viewing the log, let’s first generate some failed logon attempts on the server. This can be done by selecting File, Connect Object Explorer in SQL Server Management Studio. In the Connect to Server dialog box, ensure that the Server Type is set to the Database Engine, and enter the name of the SQL Server instance that is hosting the audits. Change the authentication to SQL Server Authentication. Enter the SA as the Login, type the incorrect password, and then click Connect. You should receive an Error 18456 message indicating that you cannot connect to the SQL Server because the login failed for user SA. Repeat the login attempt two more times so that a few more audit events are generated.

Follow the steps to review the audit log we generated in Phase 1.

1.   In Object Explorer, expand a SQL Server instance, the Security folder, and then the Audits folder.

2.   Right-click the audit, TOR-SQL01-Audit, and specify View Audit Logs.

3.   The SQL Server Log File Viewer will be invoked as shown in Figure 17.4. Take note of the failed login attempts that were captured based on our audit specification. If you click on an event where the Action ID states LOGIN FAILED, you will be able to review addition details such as time, audit collection name, user account, and so on.

FIGURE 17.4 Viewing the Failed Login Group audit log.

4.   Click Close when you have finished reviewing the log.

Compared to other locations, the Windows Server 2008 Security log is the best place to record audit object access, as it is the least likely to be tampered with. When using the Windows Server 2008 Security log as a Audit target, two tasks must be initiated as prerequisites. First the Audit Object Access setting must be configured on the Windows Server 2008 system in order to allow audit events to be recorded. Second, the account used to generate audit events to the Security log, which is the SQL Server Agent, must be granted the Generate Security Audits permissions.

The following example illustrates how to conduct the first step by enabling Audit Object Access on a SQL Server 2008 system running Windows Server 2008.

The next step is to open up the local security policy on the Windows Server 2008 system that will be hosting the SQL Server Audit object and grant the account associated with the SQL Server Agent permission to record data to the Security Log. This can be accomplished by conducting the following steps:

A new function can be leveraged to view security events associated with an audit file residing on a SQL Server system. Here is the default syntax:

The DBA will need to specify the directory and path to the actual filename that needs to be viewed. Here is an example based on the audit file Payrole_Security_Audit, which was created in the steps earlier in the chapter.

Using SQL Server Management Studio, right-click on an audit, click Server Audit Specification and/or Database Audit Specification, and select Delete. It would be best to first delete the audit specification and then the audit associated with the audit specification. The drop can also be achieved with Transact-SQL. Here are the three basic syntaxes.

It is possible to manage the properties associated with a data collection set by right-clicking a data collection set in Object Explorer and then selecting Properties. Examples of built-in system data collection sets in SQL Server 2008 include: Disk Usage Collection Set, Query Activity Collection Set, and Server Activity Collection Set. Each of these collection sets can be managed.

The Data Collection Set Properties dialog box has a total of three pages. The settings in many sections on these pages are customizable. Become familiar with these pages by reading the next section.

The Data Collection Set General Page

The General page is broken down into the following sections as depicted in Figure 17.13.

FIGURE 17.13 Viewing the General page associated with the properties of a data collection set.

   Name— This text box is self-explanatory; it displays the name of the data collection set.

   Data Collection and Upload— The two options available in this section include Non-Cached and Cached mode. These options dictate how data is collected and uploaded to the management data warehouse. The Non-Cached mode is the default option involved in collecting performance data based on a scheduled interval. Alternatively, non-cached data is collected and uploaded on demand. The second option, known as Cached mode, collects performance data at collection frequencies and then uploads the data based on a separate schedule.

   Collection Items— The next section located on the general page is Collection Items. A DBA can review the collected items including names, category type, and collection frequency in seconds. For example, performance data associated with Disk Files or Log Files disk usage can be collected based on the Generic T-SQL Collector type every 60 seconds.

Note

The Collection frequency time interval can be modified only if the Data Collection and Upload setting is configured as Cached.

   Input Parameters— This section presents the input parameters used for the collection set based on Transact-SQL.

   Specify the Account to Run the Collection Set— This setting indicates the account used to run the collection set. The SQL Server Agent account is the default account; however, if proxy accounts are configured and available, there is the option to select an account from the drop-down list.

   Specify How Long to Retain Data in the Management Warehouse— The final setting indicates how long performance data that is based on a collection should be retained in the management data warehouse. Of course, DBAs can change the frequency by specifying a new value or selecting the option to retain data indefinitely.

When viewing collection set logs, a DBA has two choices: either review logs associated with all the collection sets, or drill down on a specific collection set and view just one log file. Follow these steps to review the logs associated with all of the collection sets.

If there is a need to capture performance data from one or more SQL Server instances, the best practice is to use one MDW within the SQL Server infrastructure. Again, ensure that the MDW can support the workload based on the number of instances recording performance data to the MDW.

Follow these steps to implement a data collection on a SQL Server instance that is not hosting the MDW. For this example, our MDW is residing on TOR-SQL01Instance01 and a data collection will be configured on TOR-SQL01Instance02 to record performance data to the MDW residing on TOR-SQL01Instance01.

1.   Launch SQL Server Management Studio, and then connect to a SQL Server Database Engine instance for which you plan on setting up a data collection (TOR-SQL01Instance02).

2.   In Object Explorer, expand a SQL Server instance (TOR-SQL01Instance02), expand the Management folder, and then select the Data Collection node. This SQL Server instance should not be hosting the MDW database.

3.   Right-click the Data Collection node and then select Configure Management Data Warehouse.

4.   Click Next in the Welcome to the Configure Management Data Warehouse Wizard.

5.   On the Select Configuration Task page, select the option Set Up Data Collection, and then click Next.

6.   On the subsequent page, specify a SQL Server instance that is already hosting the MDW, such as TOR-SQL01Instance01. Next, specify the database name of the MDW and the Cache directory and then click Next, as shown in Figure 17.14.

FIGURE 17.14 Configure a data collection to use a MDW on another SQL Server instance.

7.   Verify the configuration on the Complete the Wizard page, and then click Finish.

8.   The final page will indicate the status of the installation. Verify that all actions were successful, and then click Close.

9.   Repeat steps 1 through 8 on all SQL Server instances for which you want to capture performance monitoring data.

Each built-in data collection set includes intuitive reports that should be leveraged for analyzing trends and diagnostics. To generate a report, conduct the following steps:

The mail delivery architecture in SQL Server 2008 is very similar to Database Mail in SQL Server 2005; However, it is significantly different compared to SQL Server 2000 and previous versions. Although the legacy SQL Mail functionality is still available for backward compatibility, its use is not encouraged. In addition, it is not supported on 64-bit editions of SQL Server 2008.

Database Mail in SQL Server 2008 continues to offer mail functionality without the installation of a MAPI client such as Outlook on the server just to send email. Email continues to be sent using standard Simple Mail Transfer Protocol (SMTP). This also means that one or more available SMTP servers in the organization can be used to relay mail, which could include an existing Exchange Server 2007 running either the Hub Transport or Edge role.

To use the new Database Mail feature, the user must be part of the DatabaseMailUserRole role in the MSDB database. This role allows the execution of the sp_send_dbmail stored procedure.

Implementing Database Mail

After installing and configuring the SMTP server, follow these steps to configure Database Mail for a SQL Server instance:

1.   In Object Explorer, expand a SQL Server instance and then the Management folder. Database Mail should be listed.

2.   Right-click Database Mail and select Configure Database Mail.

3.   On the Welcome page, click Next.

4.   Select the Set Up Database Mail option and click Next.

5.   If prompted, click Yes to enable Database Mail.

6.   Type Email Notification in the Profile Name field.

Note

If the Database Mail feature is not available, a message will be displayed. Click Yes to enable the Database Mail feature.

The next step is to establish a Database Mail account, which is simply a list of SMTP servers used to send the email.

Multiple Database Mail accounts can be used. When email is sent, each mail account is tried in order until one of them is successful. When the email is successfully sent through an account, that account is used for subsequent email delivery until it becomes unavailable.

Each account can be configured with a different authentication, depending on the requirements of the environment. Follow these steps to add the Database Mail account:

1.   Still on the New Profile page, click the Add button to open the New Database Mail Account page.

2.   Type Local SMTP in the Account Name field.

3.   Type an email address such as [email protected] for the Email Address field.

4.   Type a display name such as Email Notification in the Display Name field.

5.   The Server Name field must be populated with the name of your SMTP server. For this example, type TOR-HB01.companyabc.com in the Server Name field.

6.   Select the type of SMTP authentication to use, such as Windows Authentication using Database Engine Service Credentials, Basic Authentication, or Anonymous Authentication. The setting selected will be based on the relay security associated with the SMTP server.

7.   Click OK and then click Next.

Figure 17.15 shows how the New Database Mail Account page should look. You can add additional accounts using the same procedure.

FIGURE 17.15 New Database Mail account.

On the Manage Profile Security page, you can configure the profile as public or private. Public profiles can be used by any user in the DatabaseMailUserRole role, whereas private profiles can be used only by specific database users. The profile can also be configured as the default profile.

To continue using the wizard, enable the Email Notification Profile by checking the Public check box and then click Next.

On the Configure System Parameters page, you can configure the setting that controls how Database Mail operates. For example, to configure the system to retry delivery if an error is experienced, set the Account Retry Attempts and Account Retry Delay (Seconds) options.

To continue the wizard, accept the default values and click Next. Click Finish to complete the wizard and execute the defined configuration.

Tip

From a security perspective, it is a best practice to use a dedicated SMTP server within the infrastructure and not the SQL Server system. Adding the IIS and SMTP roles on a SQL Server system increases the surface area of attack. On systems that are running Exchange Server 2007, relaying can be achieved on an Exchange Server system running the Hub Transport or Edge Transport role.

An operator is a user or a group that receives notifications. Notifications can include email, pagers, and net send. The schedule of the operator can also be configured; for example, an operator can be defined to receive notification during business hours, and a different operator can be defined to use notifications during nonbusiness hours or on the weekend.

From within SSMS, you can define new operators. To add a new operator to the SQL Server instance, follow these steps:

You can use the Notification section of the New Operator page to enable notifications for existing alerts on the server.

Alerts can be defined for a wide range of SQL Server events. You can receive alerts on the following types of events:

Follow these steps to generate an alert when the used log file space falls below 100MB in the AdventureWorks2008 database:

You can also define a response to an alert. Responses can include executing a job or notifying an operator. The following steps demonstrate how to add an operator to the previously created alert:

You can use the Options page of the new alert to specify whether the error text is included in the different types of alerts.

The first area of interest in the Reliability and Performance Monitor snap-in is the Resource Overview screen, also known as the Resource Monitor. It is displayed in the home page central details pane after the Performance and Reliability Monitoring tool is invoked and looks similar to the new Activity Monitor in SQL Server 2008. Alternatively, you can review the Resource Overview screen by selecting Reliability and Performance in the navigation tree. Resource Monitor can also be launched from within the Performance tab in the Windows Task Manager.

The Resource Monitor Overview screen presents a holistic real-time graphical illustration of a SQL Server system’s CPU usage, disk usage, network usage, and memory usage, as displayed in Figure 17.16.

FIGURE 17.16 Viewing the Resource Overview home page in Reliability and Performance Monitor.

You can view additional process-level detail to better understand your system’s current resource usage by expanding subsections beneath the graphs. For example, when expanded, the CPU subsection includes CPU consumption by application such as SQL Server, and the Disk subsection displays disk activity based on read and write operations. For example, you can view disk activity associated with SQLServer.exe. In addition, the Network subsection exhibits bytes being sent and received based on an application. This comes in handy when measuring Network Utilization monitoring SQL Server database mirroring between two systems. Finally, the Memory subsection reveals information about the memory use of SQL Server.

The Resource Monitor Overview screen is the first level of defense when there is a need to get a quick overview of a SQL Server system’s resources. If quick diagnosis of an issue cannot be achieved, an administrator should leverage the additional tools within the Reliability and Performance Monitor. These are covered in the upcoming sections.

Windows Server 2008 comes with two tools for performance monitoring. The first tool is called Performance Monitor and the second tool is known as Reliability Monitor. These tools together provide performance analysis and information that can be used for bottleneck, performance, and troubleshooting analysis of a SQL Server system.

First, defining some terms used in performance monitoring will help clarify the function of Performance Monitor and how it ties in to software and system functionality. The three components noted in the Performance Monitor, Data Collector Sets, and Reports are as follows:

The Performance Monitor provides an interface that allows for the analysis of system data, research performance, and bottlenecks. The System Monitor displays performance counter output in line graphs, histogram (bar chart), and report format.

The histogram and line graphs can be used to view multiple counters at the same time. However, each data point displays only a single value that is independent of its object. The report view is better for displaying multiple values.

Launching the Performance Monitor is accomplished by selecting Performance Monitor from the Monitoring Tools folder in the Reliability and Performance MMC snap-in. You can also open it from a command line by typing perfmon.msc. When a new Performance console is started, it loads a blank system monitor graph into the console with % Processor Time as the only counter defined.

Before counters can be displayed, they have to be added. The counters can be added simply by using the menu bar. The Counter button on the button bar includes Add, Delete, and Highlight. You can use the Add Counter button to display new counters. On the other hand, use the Delete Counter button to remove unwanted counters from the display. The Highlight button is helpful for highlighting a particular counter of interest; a counter can be highlighted with either a white or black color around the counter.

The following step-by-step procedures depict how to add counters to the Performance Monitor:

1.   In the navigation tree of Event Viewer, first expand Reliability and Performance, Monitoring Tools, and then Performance Monitoring.

2.   Either click the Add icon in the menu bar or right-click anywhere on the graph and select Add Counters.

Note

Typical baseline counters consist of Memory - Pages / Sec, Physical Disk - Avg. Disk Queue Length and Processor - % Processor time.

3.   The Add Counters dialog box is invoked as shown in Figure 17.17. In the Available Counters section, select the desired counters and click Add.

FIGURE 17.17 Adding counters to Performance Monitor.

4.   Review the selected counters in the Added Counters section and then click OK.

In the Performance Monitor display, update displays by clicking the Clear Display button. Clicking the Freeze Display button or pressing Ctrl+F freezes displays, which suspends data collection. Data collection can be resumed by pressing Ctrl+F or clicking the Freeze Display button again. Click the Update Data button to display an updated data analysis.

It is also possible to export and import a display by using the Cut and Paste buttons. For example, a display can be saved to the Clipboard and then imported into another instance of the Performance Monitor. This is commonly used to view or analyze system information on a different system such as information from a production server.

The Properties page of Performance Monitor has five additional tabs of configuration: General, Source, Data, Graph, and Appearance. Generally, the Properties page provides access to settings that control the graph grid, color, style of display data, and so on. Data can be saved from the monitor in different ways. The easiest way to retain the display features is to save the control as an HTML file.

The Performance Monitor enables you to also save log files in comma-separated (CSV) or tab-separated (TSV) format, which you can then analyze by using third-party tools such as Seagate Crystal Reports. Alternatively, a comma-separated or tab-separated file can be imported into a spreadsheet or database application such as Microsoft Excel or Access.

The Reliability Monitor is a brand new tool first introduced with the release of Windows Vista and now reintroduced with Windows Server 2008. This enhanced system management tool is the second monitoring tool available with Microsoft’s Reliability and Performance Monitor MMC snap-in. Use this tool when you need help troubleshooting the root cause associated with reduced reliability of a SQL Server system running on Windows Server 2008. Reliability Monitor provides event details through system stability charts and reports that help diagnose items that may be negatively impacting the reliability of a system.

The tool uses a System Stability Index to rate the stability of a system each day over its lifetime by means of an index scorecard that identifies any reduction in reliability. An index rating of 1 represents a system in its least stable stage, whereas an index rating of 10 indicates a system in its most stable stage. Each day’s index rating is displayed in a System Stability Chart graph, as illustrated in Figure 17.18. This graph typically helps administrators to identify dates when stability issues with the Windows Server 2008 system occurred. Additional itemized system stability information can be found in an accompanying System Stability Report section of the Reliability Monitor screen. The additional stability information further assists by identifying the root cause of the reliability issues. This information is grouped into the following categories: Software Installs and Uninstalls, Application Failures, Hardware Failures, Windows Failures, and Miscellaneous Failures.

FIGURE 17.18 The Reliability Monitor.

Reliability Monitor is an essential tool for identifying problems with Windows Server 2008. With Reliability Monitoring, an administrator can quickly identify changes in a system that caused a negative trend with system reliability. As such, this tool can also help administrators anticipate other problems, which all ultimately leads to solving issues efficiently and effectively.

The Data Collector Set is a vital new feature available as a subfolder within the Performance and Reliability snap-in. The purpose of a data collector set is to review or log system performance data. This is achievable through a single component that encompasses organized multiple data collection points. This information can then be analyzed to diagnose problems, correct system performance issues, or create baselines.

Performance counters, event trace data, and system configuration information are all data collector elements that can be captured and contained in a data collector set. Data collector sets can be based on a predefined template, created from a data collector set that already exists, created manually or with a wizard, or can be user defined. Data collector sets can be exported and used for multiple systems easing the administrative load involving the configuration of new systems producing more effective monitoring. Wizards facilitate the creation of data collector sets and enable an administrator to quickly create collections based on server roles or the type of information that is required.

The final folder in the Reliability and Performance console is Reports. The Reports folder provides diagnostic reports to support administrators in troubleshooting and diagnosing system performance problems including reliability. Reports are viewed in the central details pane of the Reliability and Performance Monitor snap-in.

The reports are based on data collector sets that were previously defined by users or preconfigured and included with Windows Server 2008 Reliability and Performance Monitoring. The report console’s features and functionality are very similar to those seen using the reports introduced with Server Performance Advisor in Windows Server 2003.

The Reporting folder is broken into two main subfolders: User Defined Reports and System Reports. The default System Reports subfolder typically includes reports relating to LAN diagnostics, system diagnostics, and system performance. Additional system reports are automatically generated depending on the server role installed on the Windows Server 2008 system. For example, an Active Directory diagnostics system report is automatically included in the console when the Active Directory Domain Services server role is installed on the Windows Server 2008 system.

The SQL Server Profiler tool captures SQL Server 2008 events as they are generated on a SQL Server. The captured information, referred to as a workload, can be reviewed in the UI or saved to a trace file. The workload can be used to analyze performance or can be replayed to conduct N+1 testing. The SQL Server Profiler tool is invaluable for getting detailed insight into the internal workings of applications and databases from a real-world and real-time perspective.

For additional information on using the SQL Server Profiler, see Chapter 19.

The Database Engine Tuning Advisor automates the process of selecting an optimized set of indexes, indexed views, statistics, and partitions and even provides the code to implement the recommendations it makes. The Database Engine Tuning Advisor can work with a specific query or can use a real-world workload as gathered by the SQL Server Profiler tool. The advantage of the latter approach is that the workload is generated based on actual usage, and the tuning recommendations reflect that.

The Database Engine Tuning Advisor is customizable and allows you to select the level of recommendation that the tool recommends. This feature allows you to maintain the existing database design and make appropriate fine-tuning recommendations for just indexes. Or you can make the existing design flexible and then have the tool recommend far-reaching changes to the structure such as partitioning.

For additional information on using the Database Engine Tuning Advisor, see Chapter 19.