MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
Authors: Moore T.
Published year: 2003
SQL Server Optimizer
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:
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.
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.
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.
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
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.
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 :
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.
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.
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.
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.
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.
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.
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.
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
Authors: Moore T.
Published year: 2003