Before getting into the methods to look at performance counters, we'll discuss which performance counters are most useful to you. When you see the list of available performance counters, you're likely to be overwhelmed. Table 13-1 describes the most commonly used and useful performance counters, what they measure, and what represents a problematic measurement. Performance counters are grouped together by what are referred to as objects. Objects may have a particular application called an instance. Under this are the actual counters. To present the information, the Object(Instance):Counter format is usually used.

These basic counters will show you the amount of time that the various system processes are spending working on your system. With the queue length of the processor and the disk, you can see whether some processes are waiting on other processes to complete. Knowing that a process is waiting for resources is one of the best indications you'll get that there is a performance problem. You can also look at the amount of information being sent over your network interface card (NIC) as a general measure of problems on your network. Just these few simple counters can show you how the server is performing.

To use these counters, you need a general idea of what constitutes a potential problem. For example, %Processor Time is problematic when a sustained load is 75 percent or greater. But you will see occasional spikes of 100 percent. Spikes of this nature are a problem only when you also begin to see the Processor Queue Length value grow. Understanding that the Average Disk Queue Length value is growing will alert you to potential problems with I/O, but it will also let you know that your system is beginning to scale and that you may need to consider more, or different, disks and disk configurations.

Several counters will show you the performance and behavior of SQL Server itself. These are available in the same places as the system counters, but as you'll see in Table 13-2, they are formatted slightly differently. You'll see these as SQL Server:Object(Instance):Counter.

The first counter listed in Table 13-2, Full Scans/sec, lets you know how many full scans, a complete read of an index or a table row by row, the system is experiencing. Large numbers here indicate poorly written queries or missing indexes. The second counter, User Connections, simply shows the number of user connections in the system. This is useful when combined with other measures to see how the server is behaving. Lock Wait Time is an indication that a lot of activity is occurring on the server and processes are holding locks that are necessary to manipulate data. This may suggest that transactions are running slowly. Finally, the counter Batch Requests/sec indicates just how much load the server is operating under by showing the number of requests in the system.

The counters displayed in Tables 13-1 and 13-2 are a very small subset of the total counters available, but these will give you a general indication of the health of your server. You would need to look at a number of other counters to get an accurate measure of a system's health. The counters mentioned here are the ones that are most likely indicative of a problem on the system. This is taken from the idea that anything that is causing queuing, in other words, waits in the CPU or I/O, is a problem that needs to be identified and dealt with. Within SQL Server, growing numbers of scans or lock waits can also indicate deteriorating performance. So, although these counters won't provide an overall health for the system, they do act like a check on the pulse of the system, which is an early indicator of other problems. There are multiple ways to access these counters on your systems.

A tool that comes installed with all versions of the Windows operating system is the Performance Monitor, although in Windows Server 2008 it's referred to as the Reliability and Performance Monitor suite. This tool provides a graphical interface for accessing the performance counters introduced in the preceding section. The easiest way to access the Performance Monitor tool, often referred to as Perfmon because of the name of the executable file, is to click the Start menu on your server and click the Run icon. Type in perfmon, and then click the OK button. This will open a window that looks like Figure 13-1.

Figure 13.1. Reliability and Performance Monitor suite

A number of tasks are possible with the Perfmon tool, including viewing performance monitor counters, creating logs of counters, and scheduling the capture of counters to files that can then be viewed through the Perfmon tool or imported into databases for more sophisticated data manipulation. I'll simply show how to view a few of the counters introduced in the previous section.

You first have to access the Performance Monitor tool. Click the icon on the left side of the screen labeled Performance Monitor. This will display a screen similar to Figure 13-2.

Figure 13.2. Initial Performance Monitor window

To add counters to the Performance Monitor window, click the plus icon near the center of the toolbar at the top of the window. This will open the Add Counters window, as shown in Figure 13-3.

Figure 13.3. The Add Counters window in Perfmon

To select counters for a particular computer, you'll need to supply the name of the computer in the "Select counters from computer" combo box, or you can simply let it select from the local computer, as displayed in Figure 13-3. To supply the name, you can either type it or select from a list of computers on your network. Once that's done, you'll need to select one of the performance objects. As shown in Figure 13-2, the % Processor Time object has already been selected for you. To select additional counters, scroll within the "Available counters" window. For this example, select the object General Statistics, which will have your server's instance name in front of it so that it would read ServerInstance:General Statistics if you used the example name from Chapter 2. Scroll down until you find the User Connections counter, and then click it. Click the Add button to add this counter to the "Added counters" list on the right. When you're done adding counters, click the OK button.

Now the Perfmon window will show activity from the two counters selected. The window shows a set period of time, and you can see the variations in data across the period, as shown in Figure 13-4.

Figure 13.4. Perfmon displaying performance counters and activity

Looking at the screen displayed in Figure 13-4, you can see how the performance counters change over time. The data is collected and aggregated so that you can see important information such as the Last, Average, Maximum, and Minimum values. The duration, the amount of time on display, is also shown. You can see the list of counters that is currently on display. You can even highlight a counter by selecting it in the list and then clicking the lightbulb icon in the toolbar at the top of the screen.

With the Perfmon tool, you can further manipulate the display to show different types of graphs or raw data and change the properties of the counters displayed to adjust the color they're displayed in or the scale on which they display. You can also choose to have the Perfmon tool output to a log. There are other ways to get at performance counters, and one of them is within SQL Server using T-SQL.

Introduced in SQL Server 2005, DMVs are mechanisms for looking into the underlying structures and processes of the SQL Server 2008 system and, to a lesser degree, into the operating system. Of particular interest for looking at performance counters is the DMV sys.dm_os_performance_counters. This shows all the SQL Server performance counters within a query. It does not show the operating system performance counters. The performance counters for the operating system are not as easily queried as are those for SQL Server. Querying sys.dm_os_performance_counters is as simple as querying a table:

SELECT * FROM sys.dm_os_performance_counters;

This query will return all the performance counters at this instance in time. To see a specific counter or instance of a counter, you just need to add a WHERE clause to the query so that you return only the counter you're interested in, like this:

SELECT  *
FROM    sys.dm_os_performance_counters AS dopc
WHERE dopc.counter_name = 'Batch Requests/sec'

This will return a data set similar to that shown in Figure 13-5.

Figure 13.5. Results from query against sys.dm_os_performance_counters

The column, cntr_value, shows the value for the counter being selected. If there were no other operations on the server and you were to run the query again, in this instance the counter would go up by 1 to become 133 because even the query against the DMV counts as a batch request. Other values for other counters may go up or down or even remain the same, depending on what each of the counters and instances is recording. You can use this data in any way you like, just like a regular T-SQL query, including storing it into a table for later access. The main strength of the sys.dm_os_performance_counters DMV is that you can access the data in T-SQL and use the data it displays with the T-SQL tools that you're used to using.

Performance counters are not the only way to tell what is occurring within SQL Server. Another method of looking at performance data is the plethora of other DMVs. Detailing all the possible details for information that you could collect through queries against DMVs is beyond the scope of the book. The DMVs within SQL Server can be roughly grouped as either server DMVs or database DMVs. There are 17 different divisions of DMVs. We won't list them all, but we will list the groups directly used to access performance data about the system or the database:

The ability to query all this information in real time or to run queries that gather the data into permanent tables for later analysis makes these DMVs a very important tool for monitoring the performance of the system. They're also useful for later tuning that performance because you can use them to measure changes in behavior. But real-time access to this data is not always a good idea, and it doesn't let you establish a baseline for performance. To do this, another way to collect performance data is needed. This is the Data Collector.

Introduced with SQL Server 2008, the Data Collector is a means of gathering performance metrics, including performance counters, from multiple SQL Server 2008 systems and collecting all the data in one place, namely, the management data warehouse. The Data Collector will gather performance data, including performance counters, procedure execution time, and other information. Because it exposes a full application program interface (API), you can customize it to collect any other kind of information that you want. For our purposes, we'll focus on the three default collections, Disk Usage, Query Activity, and Server Activity.

The Data Collector is a great way to look at information over a long period of time so that you can provide information for tuning purposes. For example, you would want to start collecting data on a new application right away. This initial set of data is known as a baseline. It gives you something to compare when someone asks you whether the system is running slowly or whether the databases are growing quickly. You'll also have the ability to collect performance data before and after you make a change to the system. So if you need to know whether adding a new index, changing a query, or installing a hotfix changed the performance in the system, you'll have data collected that allows you to compare behavior before and after the change you introduced to the system. All of this makes the Data Collector a vital tool in your performance-monitoring and tuning efforts.

To begin using the Data Collector, you need to first establish security, a login, that will be used on all your servers for collecting data. You can approach this in two basic ways. First, you can have a single login across all your servers that have sa privileges, which allows the login to do anything. Second, you can use the built-in data collector roles that are stored in the msdb system database. Detailing all the variations for setting up security for the Data Collector is beyond the scope of this book. For details, refer to "Data Collector Security" in Books Online.

Once the security is set, you'll need to establish a server as the host to the management data warehouse, where the performance data gathered through the Data Collector will be stored. When you have the server ready, open SQL Server Management Studio (SSMS), and connect to the server. Scroll down the folders available in the Object Explorer window to the Management folder. Expand this. It should look something like Figure 13-6, although if you haven't configured it, you may see a small red arrow like the one visible on the Resource Governor icon.

Figure 13.6. The Data Collector tool inside the Management folder

Once you have navigated to the Data Collector icon, as shown in Figure 13-6, you can begin to establish the management data warehouse. Right-click the Data Collector icon, and select Configure Management Data Warehouse from the context menu. This will open the welcome screen to the Configure Management Data Warehouse Wizard. Click the Next button to get past that screen, as shown in Figure 13-7.

Figure 13.7. Management Data Warehouse Wizard's "Select configuration task" page

The default is to create or upgrade a management data warehouse, which is exactly what needs to happen. The other option is to set up data collection on the server that you run this on. This is how you would set up the Data Collector, and there will be more on that later in this section. Click the Next button. This will open the next page of the wizard, as shown in Figure 13-8.

Figure 13.8. Management Data Warehouse Wizard's Configure Management Data Warehouse Storage page

This is a very simple process. Either you select an existing database to act as the management data warehouse or you click the New button and create one when the standard Create Database window opens. Which you do depends on your system. We strongly advise against placing the data collected for the management data warehouse into one of your online transactional systems. You could place it into an existing reporting or management system. If you choose to select a new database, another window, called Map Logins and Users, will open for setting up security. Adjust this as needed for the security within your system and finish the wizard. That's the entire process. Behind the scenes, more occurred than you can immediately see. Inside the database you selected or created, several tables were created that are used by the process to manage the collection and store the data. Stored procedures, views, user-defined functions, and other objects were also added to the database. All these objects are used to gather and present the data as it comes in from the various servers where you've enabled the Data Collector.

To configure the servers that will send their information to the management data warehouse, connect to those servers through SSMS. Navigate to the Management folder so that you can see the Data Collector just like in Figure 13-6. Right-click and select Configure Management Data Warehouse from the context menu. This will again open the wizard's welcome screen. Click Next. This will open the Select Configuration Task page like in Figure 13-7. Click the radio button "Set up data collection," and click Next. This will open the Configure Management Data Warehouse Storage page, as shown in Figure 13-9.

Figure 13.9. Configure Management Data Warehouse Storage page

From the server where you want to collect data, you must define the server where the management data warehouse is stored. Select the server by clicking the ellipsis button, which will enable the "Database name" drop-down. Make sure you select the database that you created previously. Finally, you need to define a directory for data to be cached while it waits for the process to pick it up for storage. Choose an appropriate location on your system. The default is to place it on the system drive (usually C:). Depending on your environment, this is probably a poor choice. Instead, a storage collection that you can manage that won't affect other processes is a more appropriate location. The Data Collector is now configured and running.

The Data Collector is now running on the server where you designated it. It's collecting the data and storing it in the cache directory you defined. To start to view this data, you need to first get it from the cache directory to the management data warehouse. All the collection jobs are set up and running, but they're not transmitting the data collected, and this transmission must be started. Initially you can do this manually. Later you may want to set up scheduled jobs through the SQL Agent to gather the Data Collector data. To get the data manually, right-click any of the defined data collection sets, and choose Collect and Upload Now from the context menu. This will take the data from the disk cache and load it into the management data warehouse. It's now ready for viewing. You should perform this step if you're following along in order to have data visible in the next steps.

The data is available in standard tables, so you could access it directly through SQL queries if you wanted. However, the Data Collector comes installed with a few standardized reports. If you right-click the Data Collector icon in the Object Explorer window, you can select Reports from the context menu and then select Management Data Warehouse from there. With the default install, three reports are available:

Server Activity History

If you select the first item in the list, Server Activity History, you'll see a window with a number of charts. All this information represents basic server-level data, showing information such as the amount of CPU or memory used, what processes are waiting when running inside SQL Server, and the aggregated number of activities over time. This is all very useful information for performance monitoring and tuning. There are so many charts that we can't show them all in a single figure. The top half of the report will look like Figure 13-10.

Figure 13.10. Server Activity History report, top

At the top you can see which server the data is being displayed for. Below that is the time period for which the data is being displayed, and you can modify or scroll through the various time periods using the controls provided. As you change the time period, the other graphs on the chart will change. Immediately below the controls, the time range currently selected is displayed. In the case of Figure 13-10, it's showing a time range between "4/24/2009 2:00AM" and "4/24/2009 6:00AM."

Next are a series of graphs showing different information about the system. Each graph, where appropriate, shows information about the operating system and SQL Server, color-coded as green and blue, respectively. These graphs are nice, but if you need detailed information, you need to click one of the lines. Figure 13-11 shows the detail screen for the % CPU for the system.

Figure 13.11. % CPU Time per processor details report

You can see that once more you have the ability to change the time frame for the report. It also displays a nice graph, even breaking down the behavior by CPU and, finally, showing aggregate numbers for % CPU over the time period. Each of the separate graphs in the main window will show different detail screens with more information specific to that performance metric. To navigate back to the main report, you click the Navigate Backward button located near the top of the window.

Back on the main window of the Server Activity History report, the bottom half of the report looks like Figure 13-12.

Figure 13.12. Server Activity History report, bottom

Once again, these are incredibly useful reports all by themselves, showing the SQL Server waits broken down by wait type and showing SQL Server activity broken down by process type. With either of these reports, it is possible, as before, to select one of the lines or bars of data and get a drill-down menu showing more information.

Note

Initially, immediately after starting the Data Collector, your graphs might be quite empty. As more data accumulates, the graphs will fill in. You can also use the Timeline tool to zoom in on smaller time slices to get a fuller picture.

Disk Usage Summary

When you open the Disk Usage Summary report, you see a single graph. The report lists the databases over a period when the Data Collector was gathered. This allows you to see how the sizes of the databases are changing over time. Figure 13-13 shows the Disk Usage Summary report.

Figure 13.13. Disk Usage Summary report

As you can see, the information is laid out between the data files, shown as the database sizes, and the transaction log, shown as the log sizes. Each one shows a starting size and the current size, which allows you to see a trend. Running this report once a week or once a month will quickly let you know which databases or logs you need to keep an eye on for explosive growth.

You can click the database name to open a new report, the Disk Usage for Database report, as shown in Figure 13-14.

Figure 13.14. Disk Usage for Database report

The Disk Usage for Database report details how much of the space allocated to the database is being used and how much is free. It also shows whether the space used is taken up by indexes or data.

Query Statistics History

The most common source of performance problems in SQL Server is poorly written code. Gathering information on the performance metrics of queries is a vital part of performance monitoring and tuning. Opening the Query Statistics History report will display a window similar to Figure 13-15.

Figure 13.15. Query Statistics History report

At the top of the report is the (by now familiar) time control so that you can determine the time period you're interested in viewing. The graph shows queries ordered by different criteria, such as CPU, Duration, Total I/O, Physical Reads, and Logical Writes. Clicking any one of these will change the display to show the top ten queries for that particular metric. In Figure 13-15, Duration has been selected, so the queries are ordered by the total duration of the query for the time period selected. This ability makes this tool incredibly useful because each of these metrics represents a possible bottleneck in your system. So, you may be seeing a number of waits on your system on the CPU in the Server Activity History report. If you then open the Query Statistics History report and sort by CPU, you can see the top ten worse offenders and begin to tune the queries (query tuning is covered in a later section called "Tuning Queries").

Clicking one of the queries will open the Query Details report, as shown in Figure 13-16. At the top of the report is the text of the query. Depending on what was run inside the query, this can be quite long. Figure 13-16 shows only the bottom half of the report.

Figure 13.16. Query Details report, bottom

The Query Details report shows a lot of information about the query selected. Starting at the top of Figure 13-16, you can see the Query Execution Statistics area. This includes various information such as the average CPU per execution, the average executions per minute, or the total number of executions for the time period. All this information provides you with details to enable you to understand the load that this particular query places on the system. The graph in the middle of Figure 13-16 shows data about the different execution plans that have been created for this query. Selecting each of the different rankings will reorder the execution plans just as it did the queries in the Query Statistics History report. Execution plans are the way that SQL Server figures out how to perform the actions you've requested in the query. They're covered in more detail in the section "Understanding Execution Plans." Being able to look at the information in the Query Details report for a query and compare it to previous entries will be a powerful tool when you begin to tune queries.

There are two types of execution plans in SQL Server, estimated and actual. Queries that manipulate data, also known as Data Manipulation Language (DML) queries, are the only ones that generate execution plans. The execution plans generated come in two basic types, estimated and actual. When a query is submitted to SQL Server, it goes through a process known as query optimization. The query optimization process uses the statistics about the data and the indexes inside the databases in SQL Server to figure out the best method for accessing the data that was defined by the query. It makes these estimates based on the cost to the system in terms of the length of time that the query will run. The cost-based estimate that comes out of the optimization process is the estimated execution plan. The query and the estimated execution plan are passed to the data access engine within SQL Server. The data access engine will, most of the time, use the estimated execution plan to gather the data. Sometimes it will find conditions that cause it to create a different plan. Either way, the plan that is used to access the data becomes the actual execution plan.

Each plan is useful in its own way. The best reason to use an estimated plan is because it doesn't actually execute the query involved. This means that if you have a very large query or a query that is running for very excessive amounts of time, rather than waiting for the query to complete its execution and an actual plan to be generated, you can immediately generate an estimated plan. The main reason to use actual plans is because it represents what was done to execute the query. When the data access engine changes an estimated plan, you will see the changed execution plan, not the estimated plan, when you look at the actual execution plan.

There are a number of possible ways to generate both estimated and actual execution plans. There are also a number of different formats that the plans can be generated in. These include the following:

The easiest and most frequently used method for generating a graphical execution plan is through the query window in SQL Server Management Studio. Open Management Studio, connect to your server, and right-click a database. From the context menu, select New Query. A new query window will open. For this example, we're using Microsoft's test database, AdventureWorks2008. Type a query into the window that selects from a table or executes a stored procedure. Here's the query we're using (salesquery.sql in the download):

SELECT  p.[Name]
       ,soh.OrderDate
       ,soh.AccountNumber
       ,sod.OrderQty
       ,sod.UnitPrice
FROM    Sales.SalesOrderHeader AS soh
        JOIN Sales.SalesOrderDetail AS sod
        ON soh.SalesOrderID = sod.SalesOrderID
        JOIN Production.Product AS p
        ON sod.ProductID = p.ProductID
WHERE   p.[Name] LIKE 'LL%'
        AND soh.OrderDate BETWEEN '1/1/2004' AND '1/6/2004'

You can run this query and get results. To see the estimated execution plan, click the appropriate icon on the SQL Editor toolbar. It's the leftmost icon on the toolbar, as shown, along with its tooltip, in Figure 13-17.

Figure 13.17. SQL Editor toolbar with the Display Estimated Execution Plan icon and tooltip

This will immediately open a new tab in the results pane of the Query Editor window. On this tab will be displayed the estimated execution plan. Figure 13-18 shows the estimated execution plan.

Figure 13.18. Estimated execution plan

The first thing to note is that the query was not executed. Instead, the query was passed to the optimizer inside SQL Server, and the output of the optimizer, this execution plan, was returned. There's a lot of information to understand on this execution plan. At the top of Figure 13-18, you see the text "Query 1: Query cost (relative to the batch): 100%." When there is more than one statement inside a query, meaning two SELECT statements or a SELECT statement and an INSERT statement, and so on, each of the individual statements within the query batch will show their individual estimated cost to the entire batch. In this case, there's only one query in the batch, so it takes 100 percent of the cost. Just below that, the text of the query is listed. Next, printed in green, is Missing Index information. This may not be visible depending on the query. In some instances, the optimizer can recognize that an index may improve performance. When it does, it will return that information with the execution plan. Immediately below this is the graphical execution plan. A graphical plan consists of icons representing operations, or operators, within the query and arrows connecting these operations. The arrows present the flow of data from one operator to the next.

There is a lot more to be seen within the execution plan, but instead of exploring the estimated plan in detail, we'll drill down on the actual execution plan. To enable the actual execution plan, refer to Figure 13-17. The icon in the center of the figure is how you enable the display of actual execution plans. When you click it, nothing will happen, but it's a switch. If you notice, it will stay "clicked." Now execute the query. When the query completes, the result set and/or the Messages tab will be displayed as it normally would. In addition, the Execution Plan tab is visible. Click that, and you will see something similar to Figure 13-19.

Figure 13.19. Actual execution plan, including operator order

The numbers displayed to the right of each of the operators were added and will be explained a little later. You'll see that this actual execution plan looks more or less absolutely identical to the estimated execution plan shown in Figure 13-18. In lots of instances, the statistics on the indexes and data within the database are good enough that the estimated plan will be the same as the actual execution plan. There are, however, large differences not immediately visible, but we'll get to those later.

When you read a graphical execution plan, you read it from the top, right, and then down and to the left. But you have to take into account that some operations are being fed from other operators. We've shown the sequence that this particular execution plan is following through the numbers to the right of the operators. The first operator in sequence is the Clustered Index Scan operator at the top of the execution plan. This particular operator represents the reads necessary from the clustered index, detailed on the graphical plan, SalesOrderheader.PK_SaleOrderHeaderId. You can see a number below that, Cost: 51%. That number represents the optimizer's estimates of how much this operator will cost, compared to all the other operations in the execution plan. But it's not an actual number; it represents the number of seconds that the optimizer estimates this operation will take. These estimates are based on the statistics that the optimizer deals with and the data returned by preceding operations. When this varies, and it does frequently, these estimated costs will be wrong. However, they don't change as the execution plan changes. The output from the Clustered Index Scan is represented by the thin little arrow pointing to operator 5. That arrow represents the rows of data coming out of the Clustered Index Scan operator. The size of the arrow is emblematic of the number of rows being moved. Because the next operation, Hash Match, relies on two feeds of data, you must resolve the feed before resolving the Hash Match operator. That's why you then move back over to the right to find operation 2, Index Seek. The output from 2, Index Seek, feeds into 4, the Nested Loop operator. Since the Nested Loop operator has two feeds, you again must find the source of the other feed, which is 3, the other Index Seek operator. Operations 2 and 3 combine in operation 4, and then output from operation 4 combines with that of operation 1 inside operation 5. The final output goes to operation 6, the SELECT statement.

It can sound daunting and possibly even confusing to explain how the data flows from one operator to the next, but the arrows representing the rows of data should help show the order. There are more than 100 different operations and operators, so we won't detail them here. In this instance, the operators that are taking multiple feeds represent the JOIN operations within the query that combines the data from multiple tables. A lot more information is available within the graphical execution plan. If you hover over an operator with the mouse pointer, you'll get a tooltip displaying details about the operator. Figure 13-20 shows the tooltip for Nested Loops (Inner Join).

Figure 13.20. Nested Loops tooltip

The tooltip gives you a lot more information about the operator. Each of the operator tooltips is laid out in roughly the same way, although the details will vary. You can see a description at the top window that names the operator and succinctly describes what it does and how it works. Next is a listing of measurements about the operation. These measurements are where you can begin drilling down on the operators to understand what the individual operator is doing and how well it's doing it. You can see some of the differences between the estimated and actual execution plans here. Near the top of Figure 13-20 you can see the measurement Actual Number of Rows and a value of 8625. Just below halfway down is the Estimated Number of Rows measurement and a value of 26270.1. This means that although there are an estimated 26270.1 rows being returned, the actual number of rows is about a third of that at 8625. At the bottom of the tooltip are details about the operator, either the output, the input, or the objects upon which the operator is working. In this case, it was the output of the operator and the references used to do the loop join. When you move the mouse again, the tooltip closes.

You can also get a tooltip about the rows of information. Again, hover the arrow instead of the operator. Figure 13-21 shows an example of the data flow tooltip.

Figure 13.21. Data flow tooltip

The data flow tooltip just shows the information you see. The actual plan shows the actual number of rows in addition to the estimated rows available in the estimated execution plan. It's useful to see how much data is being moved through the query.

There are even more details about the operators available. Right-click one of the operators, and select Properties from the context menu. This will open a properties view similar to that shown in Figure 13-22.

Figure 13.22. Execution plan operator properties

A lot of the information available on the tooltip is repeated here on the properties. The Properties window has even more information available. You can open the pieces of data that have a plus sign next to them to get more information. All this is available to you so you can understand what's happening within the query. It's a little bit of work to get to the information from the graphical execution plan. If you want to deal with nothing but raw data, you need to look at the XML execution plan.

There are a few ways to generate an XML execution plan, but since even the graphical execution plans we've been working with have XML behind the scenes, it's possible to simply use a graphical plan to generate XML. Right-click inside the execution plan, and select Show Execution Plan XML. This will open a new window. Figure 13-23 shows a partial representation of an XML execution plan.

Figure 13.23. The XML execution plan

All the information available through the graphical part of the plan and from the properties of the plan is available within the XML. Unfortunately, it's somewhat difficult to read XML. Primarily you'll use the XML plans as a means to transmit the execution plan to co-workers or support personnel.

SQL Server Profiler is a mechanism for gathering and viewing detailed information about the queries being executed on your system. It provides a means to gather this information in an automated fashion so that you can use it to identify long-running or frequently called procedures. You can also gather other types of real-time information such as users logging into or out of the system, error conditions such as a deadlocks, locking information, and transactions. But the primary use is in gathering information about stored procedures as they are executed and doing this over a period of time.

You can gather the data necessary to work with the Profiler in one of two ways, using T-SQL statements or directly through the Profiler. The process that gathers the information, regardless of how it's defined, is called a trace. The safest way is to use T-SQL in what is traditionally referred to as a server-side trace. But setting up the T-SQL can be difficult until you get used to it. Fortunately, you can use the Profiler to define the events you would like to collect and then use it to generate the T-SQL for your server-side trace. If you are collecting data on a nonproduction system and you don't mind that the Profiler GUI can add load to your system, you can use the GUI to collect the data directly. However you choose to collect the data, you can use Profiler to browse that data.

A trace consists of two basic types of information: events and columns. Events represent the different actions occurring on the SQL Server machine such as a procedure call being initiated or that same procedure call completing. You can gather data for a number of events, but primarily we'll focus on collecting information about queries. The data being collected is broken down into columns. You can define different columns of data for different events, and some events have only certain columns of data available, regardless of what's defined.

When you first open SQL Server Profiler, accessed through the Performance Tools folder under your SQL Server 2008 folder in Programs, you'll need to connect to a server. You can connect to a production server, but do not run the trace being defined through the Profiler GUI against that server. Once you are connected to a server, the Trace Properties window opens and should look something like Figure 13-24.

Figure 13.24. Trace Properties window on the General tab

You're presented immediately with a number of possible choices. You can provide a name for the trace, which is a good idea since it's possible to run more than one trace. Give your trace a description so that it can be discerned from other traces and so you'll know why it's running if you come across it while looking through SQL Server. You can build the trace definition all by yourself, or you can take advantage of the templates available. You can even create your own templates. Each of the templates defines different events and columns for the trace. For example purposes, just leave the Standard template in place.

The next option for the trace is very important. If you change nothing, this trace will output to the GUI window when it is run. You can also output the trace to a file or to a table. Although you will want to put the data into a table at some point, it's a bad idea to have the trace output directly to a table. Depending on the volume of data, you can overwhelm a server. The safest way to run a trace is to output to a file. On the General tab, you can define a stop time for your trace. Defining a stop time is a very good idea because, depending on the events you've picked and the load on your server, running a trace can have some small effect on the load of the machine. For now, just leave everything with the defaults.

To define events and columns, you need to click the Events Selection tab. This changes the Trace Properties window so that it looks more like what's visible in Figure 13-25.

Figure 13.25. Trace Properties window with Events Selection tab displayed

The Events Selection tab is where you can define the events and columns that make up a trace. Selecting a given event requires selecting one of the check boxes in the rows on the extreme left side of the screen. Then running across the screens are the various columns. The Standard template selects a good list of columns that provide useful information. The TextData column will show the procedure or query and any parameter values used in that particular call. This is one of the most useful columns. When a query is identified as being long-running, you'll want to have the parameter values that were used in that call. This is where you'll get that information. Other useful columns include the performance metrics of CPU, Reads, Writes, and especially Duration. The other columns can be useful in other ways. If you want to select other events than those currently displayed, select the "Show all events" check box. To display columns not selected, select the "Show all columns" check box. You can also apply column filters and group data.

To get this ready to export to T-SQL, you will need to run it. Click the Run button. Just as soon as you do, the trace will begin to run, but you can stop it by clicking the Stop Selected Trace icon visible on the toolbar. With the trace defined but stopped, now is the time to create a T-SQL script for creating the server-side trace. Select the File

/****************************************************/
/* Created by: SQL Server 2008 Profiler             */
/* Date: 04/29/2009  03:20:05 PM         */
/****************************************************/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:MyFolderMyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere',
@maxfilesize, NULL
if (@rc != 0) goto error
...

There will be quite a lot more to the script than this. To run this script, follow the commented instructions, and replace the string InsertFileNameHere with a valid path and file name. Once this is completed, you can run it. It will start a server-side trace that collects the events and columns you defined, writes them out to the path and file provided, and stops tracing at the time you defined.

Working with Trace Data

Once the data is captured into a trace file, you have multiple options for working with. The first is very simple. Open SQL Server Profiler, and select File

To get the data into a table, you can use one of two methods; save it to a table from the GUI, or use T-SQL and the fn_trace_gettable function to do the same thing. From the GUI, simply click File

SELECT * INTO NewTable
FROM ::fn_trace_gettable('\path	racfile.trc', DEFAULT);

Once the data is in the table, you can begin running queries against it. Now you can aggregate on multiple values at the same time, create Reporting Services reports that can be transmitted to interested users of the system, or do just about anything else that you could do with SQL Server and the data it contains.

Working with the trace data from tables is the best approach. There are a couple of small caveats worth noting. When you use either of the methods outlined, the TextData column is created as the data type NText. You will want to convert that to NVARCHAR(MAX) in order to be able to perform certain types of queries. Also, since each trace event captures the parameters along with the stored procedure or query calls, you'll need a method for cleaning out the parameters from the queries. The Microsoft Developer Network provides just such a mechanism at http://msdn.microsoft.com/en-us/library/aa175800(SQL.80).aspx. This was written originally for SQL Server 2000, but it will work just fine with SQL Server 2008.

Using Trace to Capture Execution Plans

One of the possible events that you can select for your trace through the Profiler interface is a way to get the execution plan for queries as they run on the server. Using the Profiler GUI, display all the events by selecting the Show All Events check box. Scroll around on the screen until you see a group of events labeled Performance. Expand that group. There are a number of useful events for capturing performance-related information. The most interesting in this case is the event labeled Showplan XML. Select this event, and be sure that you have the TextData column selected. That column is where the XML will be returned. Running the trace, the output will look like Figure 13-26.

Figure 13.26. Trace Event Showplan XML

As was explained earlier, all the graphical execution plans are just XML execution plans under the covers. When you select the Showplan XML event, the TextData column displays as an execution plan. This is a fully functional graphical execution plan.

Warning

Showplan XML, and all the execution plan events, is a very expensive operation and should be used very judiciously when running a trace against a production system.

One of the principal methods that SQL Server uses to maintain and control queries is indexes and the statistics on those indexes. Taking direct control over these indexes yourself can take a lot of time and effort and require education and discovery. SQL Server has a tool that will help you create indexes, the Database Engine Tuning Advisor (DTA). The DTA can be run a number of different ways to help you. You can capture a trace data set and send it to the DTA for analysis. You can pass a query from the Query Editor inside Management Studio straight into the DTA for analysis.

To see it in action, we'll run the DTA against the query used previously (salesquery.sql in the download):

SELECT  p.[Name]
       ,soh.OrderDate
       ,soh.AccountNumber
       ,sod.OrderQty
       ,sod.UnitPrice
FROM    Sales.SalesOrderHeader AS soh
        JOIN Sales.SalesOrderDetail AS sod
        ON soh.SalesOrderID = sod.SalesOrderID
        JOIN Production.Product AS p
        ON sod.ProductID = p.ProductID
WHERE   p.[Name] LIKE 'LL%'
        AND soh.OrderDate BETWEEN '1/1/2004' AND '1/6/2004'

In the query window, right-click the query text, and select Analyze Query in Database Engine Tuning Advisor from the context menu. This will open the DTA in a window that looks like Figure 13-27.

Figure 13.27. Database Engine Tuning Advisor's General tab

This is a simple example. With more complicated examples that include data from other databases that are being run based on the workload supplied by a trace, you would have more databases selected. In this case, it's a single query against a single database. From here you can move to the Tuning Options tab to adjust the possible changes being proposed by the DTA. For this example, we'll let the default values work. On the DTA toolbar, click the Start Analysis button, and a new tab showing the progress of the analysis will open. Once the analysis completes, another new tab showing recommendations from the DTA will open. Figure 13-28 shows the recommendations for the query.

Figure 13.28. Database Engine Tuning Advisor Recommendations tab

For the query supplied, the DTA ran through the information in the query and the information in the tables, and it arrived at three indexes that it thinks will achieve a 52 percent increase in speed. You should take any and all recommendations from the DTA and test them prior to implementing them in production. It is not always right.

Index and data compression were introduced in SQL Server 2008. They are available only in the Enterprise and Developer editions. Data in SQL Server is stored on a construct referred to as a page. When you read data off the disk, you have to read a whole page. Frequently, especially when running queries that return large sets of data, you have to read a number of pages. The process of reading data from disk, as well as writing it there, is one of the most expensive that SQL Server does. Compression forces more data onto a page. This means that when the data is retrieved, fewer reads against the disk are necessary with more data returned. This can radically increase performance. Compression does not come without a cost, however. The process of compressing and uncompressing the data must be taken up the CPU. On systems that are already under significant stress in and around the CPU, introducing compression could be a disaster. The good news is that CPU speed keeps increasing, and it's one of the easiest ways to increase performance. No application or code changes are required to deal with compressed data or indexes.

You can compress a table or indexes for the table, separately or together. It's worth noting that you can't count on the compression on a table or a clustered index to automatically get transmitted to the nonclustered indexes for that table. They must be created with compression enabled individually. Compression can be implemented through compression of the row or through compression of the page. Page compression uses row compression, and it compresses the mechanisms that describe the storage of the page. Actually, implementing either of these compressions is simply a matter of definition when you create the table. The following script (createcompressedtable.sql in the download) shows how it works:

CREATE TABLE dbo.MyTable
(Col1 INT NOT NULL
,Col2 NVARCHAR(50) NULL)
WITH (DATA_COMPRESSION = PAGE);

This will create the table dbo.MyTable with page-level compression. To create the table with row-level compression instead, just substitute ROW for PAGE in the syntax. To create an index with compression, the following syntax will work (createcompressedindex.sql in the download):

CREATE NONCLUSTERED INDEX ix_MyTable1
    ON dbo.MyTable (Col2)
WITH ( DATA_COMPRESSION = ROW ) ;

Using fewer resources for storage can and will result in performance improvements, but you will need to monitor the CPU on systems using compression.