SQL Server Optimizer

• Analyze the query execution plan. Considerations include query processor operations and steps.

The SQL Server Query Optimizer is the database engine component of SQL Server 2000. As the database engine, it oversees all data- related interaction. It is responsible for generating the execution plans for any SQL operation. In diagnosing a query, the Optimizer must decide upon the most efficient means of executing the query and interacting with the database objects.

SQL Server has a cost-based optimizer that can be extremely sensitive to the information provided by statistics. Without accurate and up-to-date statistical information, SQL Server has a great deal of difficulty in determining the best execution plan for a particular query.

Statistics maintained include the following:

• Number of rows in the table.

• Number of pages used by the table.

• Number of modifications made to the keys of the table since the last update of statistics.

• An equi-height histogram on the first column of an index.

• Densities on all column prefixes in an index.

• Average key length for an index.

SQL Server goes through a considerable process when it chooses one execution plan out of several possible methods of executing a given operation. This optimization is one of the most important components of a SQL Server database system. Although some overhead is incurred by the optimizer's analysis process, this overhead is saved in execution.

The optimizer uses a cost-based analysis procedure. Each possible method of execution has an associated cost. This cost is determined in terms of the approximated amount of computing resources used in execution. The query optimizer must analyze the possible plans and choose the one that has the lowest estimated cost.

It is not uncommon for some complex SELECT statements to have thousands of possible plans. Of course in this case, the optimizer does not analyze every possible combination. It uses a complex series of processes to find a plan that has a cost reasonably close to the minimuma minimum that is only theoretical and unlikely to be achieved.

The query optimizer relies on up-to-date statistical information that is maintained within the meta data of a database system. This information is collected and updated based on changes to the index and data structure. Proper maintenance should ensure that the statistical data is maintained and accurately reflects the current database environment.

The primary tool available for the interaction with the query optimizer is the SQL Query Analyzer, which is an all-in-one T-SQL editor, debugging environment, and object viewer.

Query Analyzer

At this juncture in the book, and in your own experience with SQL Server, you should have begun to master using this tool to enter SQL. The object browser and templates, color -coded entry environment, variety of display formats, and powerful toolset all make the tool a mandatory element of an administrator's or programmer's toolset.

Now that you are familiar with the tool, it is time to turn it into one of the most important elements of the application diagnostic and performance-tuning framework. Capable of reaching into individual commands and objects, this tool provides for the finest level of granularity and deepest interaction with the SQL Server architecture.

SQL Query Analyzer can be used to gain access to SQL Server meta data and can tear a query down into its individual objects for analysis. For any given query, you can make a number of view changes to see the execution plan, server trace, and/or client statistics if desired. When these options are on, extra tabs are added to the output pane as illustrated in Figure 12.8.

Figure 12.8. Query Analyzerfull tab display.

The Grids tab displays the output of the statement(s) executed. The Execution Plan tab graphically illustrates the method used to execute the query. The Trace tab displays the processes needed on the server to perform the command. The Statistics tab displays the application, network, and time statistics. The Messages tab is used to display any user feedback from the command(s) executed.

When viewing an execution plan, you have the capability of representing the activity the optimizer needs to perform in a graphic set of icons. To accompany the graphic is a set of statistics that goes along with that portion of the operation.

Query Execution Plans

The Execution Plan options graphically display the data retrieval methods chosen by the query optimizer. The graphical execution plan uses icons to represent the execution of specific statements and queries in SQL Server, rather than the tabular text representation produced by the SET SHOWPLAN_ALL or SET SHOWPLAN_TEXT statements. Analysis of a query plan is a very important process that helps you understand the performance characteristics of a query.

An execution plan is particularly useful in determining the steps a complex query needs to take. See Figure 12.9 for a sample query that displays invoice information from combining data from six separate tables.

Figure 12.9. Complex join operation to print invoice data.

The actual steps needed to solve the query aren't very obvious. It is easier to see the work involved by looking at the plan SQL Server uses to display the data as illustrated in Figure 12.10.

Figure 12.10. Execution Plan for Invoice Query.

The graphical plan is read from right to left and from top to bottom. Each query in the batch that is analyzed is displayed, including the cost of each query as a percentage of the total cost of the batch.

Reading Execution Plans

The graphical execution represents an execution plan with icons, whereas the SET SHOWPLAN_ALL or SET SHOWPLAN_TEXT statements use a tabular representation. Each of these mechanisms enable you to look under the hood of the server to see what is actually occurring. The exam will expect you to be able to look at any of these outputs and decipher what is happening. You will also have to provide recommendations for improvement(s). Being able to see behind the scenes is very useful for understanding the performance characteristics of a query. The showplan icons are illustrated in Figure 12.11.

Figure 12.11. Graphical showplan icons.

Many potential processes are represented in Figure 12.11. Some of these processes are discussed in full in this chapter because they pertain to material you are likely to find on the exam. For information on other processes, you should refer to SQL Server Books Online.

Each of the icons specifies a logical and physical operation. An execution plan will have any number of icons dependent upon the number of operations needed to perform the query. A single queryeven a simple onewill have a number of icons, each used to represent a part of the query or statement.

Each node is related to a parent node. All nodes with the same parent are drawn in the same column. Rules with arrowheads connect each node to its parent. Recursive operations are shown with an iteration symbol, and operators are shown as symbols related to a specific parent. When the query contains multiple statements, multiple query execution plans are drawn.

Execution Plan Caching and Sharing

The memory used by SQL Server is divided among a number of processes. A pool of this memory is divided between the storage of execution plans and allocation of data buffers. The actual division between the two varies significantly depending on the system use. The portion of this memory pool that is used to store execution plans is referred to as the procedure cache . The data buffers are similarly called the data cache . In any given execution plan there are two main components: the plan itself and the context on which the query was executed.

The majority of the plan is a read-only structure that can be used by any number of applications, connections, and users. No actual user context is stored within the plan itself. The execution context is a data structure stored for each user currently running the query associated with the plan. This area stores any data specific to a single use, such as parameter values.

When a query is executed, the server first looks through the procedure cache to verify that a plan already exists. If present, the plan is reused. This reuse of execution plans saves on the overhead associated with recompiling the query. If no plan exists, the server generates a new one. SQL Server 2000 has integrated logic that enables a plan to be shared between connections without requiring that an application prepare the statement.

EXAM TIP

Bookmark Lookup Operation One of the execution plan processes that you may run into on the exam is the Bookmark Lookup operation. This operation performs a data find operation using a bookmark (row ID or clustering key), and this value is used to look up the corresponding row in the table or clustered index.

In rare instances you may be able to apply index and other optimizer hints to change the way a query is performed. The optimizer in most cases produces the best plan, and attempts at bettering the plan don't usually provide any gains.

Optimizer Hints

A "hint" is a method of providing information in the SQL syntax that tells the query optimizer to use a specific plan rather than the one it may normally choose. Index, join, locking, query, table, and views all can be supplied with hint information to be passed to the query analyzer. Table 12.2 lists the options available for join, query, and table hints:

Table 12.2. SQL Server Query Hints

Hint Type	Option	Description
Join	LOOP HASH MERGE REMOTE	Provides joining strategy option.
Query	{HASH ORDER} GROUP	Selects hashing or ordering to be used in a COMPUTE GROUP BY and COMPUTE aggregations functions.
Query	{MERGE HASH CONCAT}UNION	Provides strategy for method used to perform UNION operations.
Query	FAST integer	Optimizes for retrieval of a specific number of rows.
Query	FORCE ORDER	Mandates the joins in the order the tables appear in the query.
Query	ROBUST PLAN	Creates a plan that accommodates maximum potential row size in bytes.
Table	FASTFIRSTROW	Has the same effect as the FAST 1 query hint.
Table	INDEX =	Instructs SQL Server to use the specified index rather than select its own index.
Table	HOLDLOCK SERIALIZABLE REPEATABLEREAD READCOMMITTED READUNCOMMITTED NOLOCK	Specifies the data isolation level used for a table locking and lock bypass purposes.
Table	ROWLOCK PAGLOCK TABLOCK TABLOCKX NOLOCK	Specifies locking granularity.
Table	READPAST	Skips locked rows altogether.
Table	UPDLOCK	Takes update locks rather than shared locks. Cannot be used with NOLOCK or XLOCK .
Table	XLOCK	Takes an exclusive lock held until the end of the transaction. Cannot be used with NOLOCK or UPDLOCK .

Hints can provide additional control over the individual operations performed by the optimizer in the act of retrieving data with a query. Though not necessarily used to improve performance, hints are used for changing the action that would normally be taken. Hints can provide additional functionality that enables system-level applications to be designed that may need access to data beyond the capabilities of a standard data read. In most cases, the use of hints is not recommended because the optimizer usually selects the best mechanism to retrieve the data from a given query.

SET Options

The SET command is used to alter the current session handling of specific information. Many different facilities are affected by the SET command. Each of the options provides a setting for the current session that changes that aspect of the interaction with the server.

SET options are grouped into many categories; each category has several statements that affect the format of the display to the user, the data that is actually displayed, and/or the interaction with the server. The categories covered by set operations are as follows :

• Date and Time. Controls the format of the date and/or configures what is to be treated as the first day of the week.

• Locking. Controls the way the session reacts to a deadlock and/or the amount of wait time before a lock is released.

• Query Execution. Handles the treatment of errors in mathematical operations, the format, amount, and type of data returned to the client, and/or whether a query is to be parsed, compiled, or executed.

• Statistics. A group of debugging settings that determines whether to give the user execution plan information and statistics.

• Transactions. Controls the way transactions are treated.

• SQL-92 Settings. Determines whether settings are to be set to SQL-92 industry standard values.

• Miscellaneous. A grouping of functions that don't fit into any one of the previously mentioned categories, yet can enable you to alter the interaction with the server and current database.

Some SET options take effect at parse-time, whereas others affect the execute-time environment. Parse-time options take effect as the options are encountered in text, without recognizing the control of flow of the procedure. Execute-time options take effect during the execution of the code in which they are specified. Execute options that have been set at the time of a failure in a procedure remain set. The QUOTED_IDENTIFIER , PARSEONLY , OFFSETS , and FIPS_FLAGGER options are parse-time options. All other SET options are execute-time options.

Options set in a script apply until they are reset or until session termination. Options that are set in a stored procedure or trigger apply until reset inside that stored procedure or trigger, or until control returns to the code that called the procedure. Unless explicitly reset, option values from all higher-level code apply within any procedure. Unless reset, options set for a connection apply after a connection is made to a different database. Some values are stored within the stored procedure, and used during the execution of that stored procedure regardless of other higher-level settings. For monitoring, troubleshooting, and optimizing a server you will find the STATISTICS and SHOWPLAN options to be the most useful.

SET STATISTICS Evaluation

If any performance issue is related to a query, a database's performance can be determined by using the SET SHOWPLAN , STATISTICS IO , STATISTICS TIME , and STATISTICS PROFILE statement options. SHOWPLAN describes the method chosen by the optimizer to retrieve the data. STATISTICS IO reports information about the number of scans , logical reads (pages accessed in cache), and physical reads (number of times the disk was accessed) for each table. STATISTICS TIME displays the amount of time (in milliseconds ) required to parse, compile, and execute a query. STATISTICS PROFILE displays a resultset after each executed query that represents a profile of the execution of the query.

Proactive/Automated Optimization

It is the role of the SQL Server Agent service running on the server to control a lot of mechanisms within an instance of SQL Server. The SQL Server Agent is responsible for running jobs and tasks scheduled to occur at specific times and/or intervals. The agent also helps detect conditions for which administrators have defined actions to be taken. The agent also runs replication tasks, and in general is responsible for handling repetitive tasks and exception handling conditions defined through the other SQL Server components.

The statistical maintenance functionality introduced in SQL Server 7 and expanded upon in SQL Server 2000 may generate unwanted overhead on a production system if it initiates statistical updates during heavy production periods or starts a high number of UPDATE STATISTICS processes. It may be worthwhile scheduling this type of maintenance to occur during idle time to prevent this behavior.

Using the SQL Server Agent to automate tasks involves scheduling periodic activities on the server. Jobs are defined for tasks that are to be scheduled. Jobs can also be defined to run on demand or during idle time on the server. Notification of completion, errors, or other defined conditions is performed through the definition of operators and the assignment of conditions under which the operator is to be contacted. The definition of alerts identifies circumstances that the agent will watch for and act upon when they occur.

Jobs are objects consisting of one or more steps to be performed. Each step can be an operating system activity or executable, a T-SQL operation, active-x script, or replication activity. Jobs are extremely flexible and powerful operations that can perform any activity from maintenance to reporting, backups , and/or application processes.

Alerts are actions to be taken when specific events occur, such as an error on the server, an application process call, or any other definable event. A definable event is any process that places information into the Windows 2000 application event log. Specific errors, errors of certain severity, or performance criteria can all have a corresponding alert defined. The alert can be defined to take such actions as sending an email notification, paging an operator through a paging service, or running a job to respond to the situation.

Operators are individuals or groups of individuals, who can be identified by a network account, email identifier, or computer identification, who can address problems with the database and other servers. An operator can be sent a message resulting from an alert, a job step, job completion, or other SQL Server processes. Messages can be sent through email, a pager, or a net send network command.

Job, Operator, Alert Integration

Jobs, alerts, and operators can be defined as a single step because the interface tools in the Enterprise Manager are linked together, and each portion of the definition allows for new entries.

An organized approach to automation, however, is recommended. Possibly the first step is to define the important individuals in the environment who could respond to potential problems. As you create the operators, you should as well accompany the operator definitions with short descriptions of the operator's purpose and function as it relates to the database system. Step by Step 12.5 shows you how to define a sample operator.

STEP BY STEP

12.5 Operator Definition

1. Start the Enterprise Manager and expand your server, the Management folder, the SQL Server Agent, and select the Operators icon.

2. Right-click Operators and select the New Operator option.

3. Enter SalesPeople for the name of the operator. (This operator will actually contain a group of individuals. Operators can be single users, groups of users, or a computer name .)

4. If your SQL Server is configured to use mail, you can add an email address. Any email address can be used for an operator. It is best to use a group alias for email messaging purposes.

5. Use your own computer name as the net send address. Use the test buttons on the right side of the interface to test any of the contact mechanisms specified.

6. Notice the listing of information on the Notifications tab. When you create an operator, you can at the same time indicate the alerts that this operator will respond to. For this example, leave this blank for now. Select OK to create the operator.

Any of the operators defined can be selected to receive notification by a number of SQL Server operations. Email and other message sending capabilities have been built into most of the features in SQL Server 2000. For this example, the operators are going to receive notification of a new customer. After they have been contacted, they can potentially contact the customer and perform an initial sales call.

As well as operator notification, you may want to execute tasks based on an alert condition. Step by Step 12.6 sets up a job that will initialize demographic information storage for each new customer that comes into the system.

STEP BY STEP

12.6 Initialize Demographics

1. Start the Enterprise Manager and expand your server and then the databases folder. Select the Tables icon and in the right pane select the CustomerDemographics table.

2. Right-click on the table, select Open Table, and select Return All Rows. This should open up a new window revealing no current records in the table.

3. Enter Unknown as the CustomerTypeID and No Demographics as the CustomerDesc.

4. Close the window to save the record and return to the Enterprise Manager.

This newly created record will act as a default for all new customers entering the system. Step by Step 12.7 now configures a job to be executed for each new customer that will initialize the new customer's demographic information.

STEP BY STEP

12.7 Job Description

1. Start the Enterprise Manager and expand your server, the Management folder, and the SQL Server Agent. Select the Jobs icon.

2. Right-click Jobs and select the New Job option.

3. Provide the name InitializeCustomer for the job and a description of Start demographic data recording for the new customer.

4. Select the Steps tab and then click the New button to define a new step for the job.

5. The job's single action is to insert a new record into the CustomerCustomerDemo table with a CustomerTypeID of Unknown. Use the following T-SQL command:

    Declare @CustID nChar(5) Select @CustID = CustomerID from Customers INSERT Northwind.dbo.CustomerCustomerDemo   VALUES(@CustID, 'Unknown') 

   You do not apply a schedule in this example because the intent for this job description is to have it run when a new customer is entered into the system. When a new customer is entered, a chain reaction starts where the operator is notified and the job is executed.

6. On the Notifications tab you would usually select Failure of Steps and/or Jobs to contact an administrator. For the purpose of this exercise, you can use the salesperson as the setup for that ID and that will signal the local computer. Select the SalesPerson operator as a net send operator and select when the job completes from the list of available options.

For the job or operator to serve any purpose, you must configure an alert (as shown in Step by Step 12.8) to recognize the condition you are looking for. In this case the condition is a new customer being entered into the system.

STEP BY STEP

12.8 Alert Definition

1. Start the Enterprise Manager and expand your server, the Management folder, and the SQL Server Agent. Select the Alerts icon.

2. Right-click Alerts and select New Alert.

3. Enter the name NewCustomer for the alert and select the Northwind database from the database selection drop-down list box.

4. You need to define an event number to be used. SQL Server sees all event alert identifiers as error numbers , though you can define elements other than errors. Select error number and then select the ellipses to allow for your own definition.

5. On the Messages tab, select the New button, which should bring up the next available user-defined error number dialog.

6. Leave the severity on informational and select the Always Write to Windows event Log check box. For an alert to respond to any event, it must have been written to the Windows application event log first.

7. Add the following message text to the text box using the %s markers as a way of providing parameters to the message. Parameter information will be passed into the event when it is fired :

    New customer in the system Customer ID: %s                            Contact ID: %s                            Phone No.: %s 

   (To insert a new line within the text box hold the Ctrl key while pressing Enter.)

8. Press OK to close the window and save the custom message. Press OK to close the Manage SQL Server Messages dialog box.

   Note the error number that was given to you. Later you will need this number. 50001 would be used if this is the first user-defined alert in the system

9. On the Response tab, select the Execute Job check box and then select the previously created job. Select the Net Send check box of the SalesPeople operator and click OK to close and save the alert definition.

A new customer will be entered into the Customers table. This process must fire the alert provided in the preceding steps and pass the CustomerID , ContactName , and Phone to the alert so that this information can be placed into a message to the salespeople.

The whole chain reaction starts with an INSERT trigger on the Customers table. Because INSERT triggers are automatically fired by SQL Server when new data is entered, it is the perfect mechanism for this type of operation. The trigger must fire off an event to be recognized by SQL Server as the one being monitored by the alert definition. Step by Step 12.9 shows how to trigger the alert.

STEP BY STEP

12.9 Triggering the Alert

1. Start the Enterprise Manager and expand your server and the databases folder. Select the Tables icon and in the right pane select the Customers table.

2. Right-click on the table, select All Tasks, and select Manage Triggers. This should open up a new window that enables you to define the trigger.

3. Create the INPUT trigger using the following code:

    CREATE TRIGGER NewCustomerEntry ON [dbo].[Customers] FOR INSERT AS DECLARE @CustNo nchar(5) DECLARE @Contact nvarchar(30) DECLARE @PhoneNo nvarchar(24) SELECT @CustNo = CustomerID, @PhoneNo = Phone,                @Contact = ContactName FROM INSERTED RAISERROR(50001, 1, 1, @CustNo, @Contact, @PhoneNo) 

   50001 is based on the error number that was created earlier. If your error number was not 50001, then the code would need to be altered accordingly .

4. Right-click on the Customers table, select Open Table, and then select Return All Rows.

5. Scroll down to the bottom of the table and enter the information for a new customer.

A lot of processes can be performed through automation, and this represents an area where development and process skills are important. In tuning and recognizing other performance factors, SQL Server Agent alerts can be set up based on performance criteria. The ability to monitor the server for this type of information can be a huge administrative benefit.

Performance Counter Alerts

The SQL Server Agent can monitor performance conditions in a similar fashion to how the System Monitor is used. A System Monitor counter can be used as the basis for specifying a performance condition to monitor. The condition causes an alert to fire if the performance threshold is reached. You can define the area of SQL Server performance to be monitored by defining the object, counter, instance, and threshold condition just as you do with the system/performance monitor. The difference is that the monitoring can be performed on an ongoing basis without any additional overhead.

The counter selected represents the specific property to be monitored. Because the performance data is collected on a periodic basis, there is often a delay between the threshold being hit and the performance alert firing.

Performance alerts can be used to activate maintenance jobs, such as backing up and clearing a log file when it is close to becoming full. You may also want individuals to be contacted in the event of potential problems such as an inordinate number of deadlocks per second. It is easier to allow for proactive management and solve potential problems before they can affect the end user.

Database Maintenance Plan Wizard

The Database Maintenance Plan Wizard is a good starting point for any server maintenance. The wizard can be used to help set up core maintenance tasks. At least a minimum amount of maintenance is necessary on any server. The wizard ensures that your database performs well, is backed up on a scheduled basis, and measures are taken to check for inconsistencies.

After you answer a few elementary questions, the wizard puts in place the necessary jobs to ensure that at least a minimum of upkeep operations are performed on the database system. Until you have mastered the remainder of the procedures developed in this chapter, this should remain the basis for your maintenance plan development.

REVIEW BREAK: The Profiler and Analyzer

By far the most substantial tools available to tune and maintain SQL Server are the combination of the SQL Server Profiler, SQL Query Analyzer, and automated maintenance through the use of jobs, alerts, and operators.

The SQL Profiler gives you a detailed report of activities captured from the server. With this amount of information available, troubleshooting server problems and diagnosing resource utilization becomes a lot easier.

The SQL Query Analyzer enables you to diagnose and fine-tune individual commands and processes. When you want to optimize a single application, procedure, and/or database, the analyzer gives you the most granularity available for viewing SQL Server statistical data.

The SQL Server Agent, operators, jobs, and alerts provide for a mechanism whereby maintenance operations can be scheduled to occur on a regular basis. This facility can be in itself an application architecture to build around.