Tools Available for Monitoring and Tuning

• Troubleshoot programming objects. Objects include stored procedures, transactions, triggers, user -defined functions, and views.

IN THE FIELD: OPTIMUM HARDWARE

Computerization in every office has exploded. The average office has three heavily used servers: the email server, the Internet server, and the database server. These services each require specialized computer equipment that must address hard drive space, RAM requirements, and processing speed. Assume for all three servers that you are to use the highest available hardware in all categories. If choices must be made, then more thorough analysis of the complete automated business structure would be required.

There is a complete set of tools for monitoring events and troubleshooting objects within SQL Server. Your choice of tool depends on the type of monitoring and the events to be monitored . To be able to select any of these tools, you must understand where each tool is used and the process for analyzing the data. You must also determine a goal for the optimization. To best troubleshoot any object, you must first have some knowledge of the circumstances surrounding the system and make observations that will form the basis of the study. The following list represents the tools that are used to diagnose SQL Server Implementations:

• Query Analyzer. Used to diagnose T-SQL statements and activity and aid in application performance tuning and optimization tasks .

• SQL Profiler. Provides access to all SQL Server activity and objects. Used for detailed monitoring of selected server-wide events.

• System Monitor. Provides a broad spectrum of server analysis. Used primarily for hardware and operating system activity as well as interactions between the operating system and SQL Server.

• Current activity window. This is what's happening now. View user, process, and lock information based on the present utilization.

• Transact - SQL . Many commands are available that can be used from the Query Analyzer or other T-SQL connections.

• Error logs. This won't necessarily tell you all the "whys," but it is an excellent start to find out the " whats ."

A lot of resources are also at your disposal to help provide answers to your questions and/or perform further research into a problem. These resources are also useful in preparing for the exam or researching specific technologies:

• Web Resources. Too many to mention here, though there is a complete list at the end of the chapter in the "Suggested Readings and Resources" section.

• SQL Server Books OnLine. As always, the first place to look for answers to any questions.

• The Microsoft Public Newsgroups. Find them at news.microsoft.com. Although there are many relevant groups, the microsoft.public.sqlserver.programming and microsoft.public.sqlserver.tools are particularly useful.

You began looking into the Query Analyzer way back in Chapter 1 and have been using the tool throughout the entire book. You were first given only a little bit of detail about a common SQL coding tool, but by now it has become probably your single most effective tool, and you have seen how SQL Server architects have come to depend on it. At the end of this chapter you will discover much more usefulness . The SQL Server Query Analyzer provides for very query-specific analysis. It is an important tool used in fine tuning any T-SQL application. The analyzer is most beneficial when you really want to drill down and focus in on detailed analysis and tuning.

Although the Query Analyzer is an excellent tool for looking into specific queries, the primary monitoring tool covering all aspects of the server applications is the SQL Server Profiler. This represents an important tool because of the vast amount of information it can provide. The SQL Server Profiler enables you to monitor server and database activity. You can capture SQL Profiler data to a table or a file for later analysis, and also replay the events captured on SQL Server. Arguably, if the Profiler is not set up properly, it can provide too much information to be useful. It is important, therefore, to learn the idiosyncrasies of the tool to make it as useful a tool as it can be.

Other tools used on the server for troubleshooting and other forms of analysis are available. To look at the hardware configuration, operating system and other analysis tools are available as part of the NT and Windows 2000 operating system. To monitor hardware and operating system events, you would use the System Monitor tool.

The System Monitor enables you to monitor server performance and activity using predefined objects and counters or user-defined counters to monitor events. System Monitor is referred to as Performance Monitor in Windows NT. Its role is to collect counts rather than data about the events. You can set thresholds on specific counters to generate alerts and notify the individuals who need to address the problem. Often the best place to start the troubleshooting process is to observe the current activity.

The Current Activity window is found in the SQL Server Enterprise Manager and graphically displays information about processes currently running. It also displays blocked processes, locks, user activity, and other useful information for ad hoc views of current activity. Equally helpful are the logs that SQL Server provides for the reporting of errors.

Error Logs contain additional information about events in SQL Server beyond what is available elsewhere. You can use the information in the error log to troubleshoot SQL Server- related problems. The Windows application event log provides an overall picture of events occurring on the Windows NT 4.0 and Windows 2000 system as a whole, as well as events in SQL Server, SQL Server Agent, and Full-Text Search. Several stored procedures have been built in to SQL Server to provide for specific benefits.

It probably goes without saying (but is worth a friendly reminder) that monitoring and troubleshooting have always played an important part in the activities performed on the job. Microsoft as well has always made this an exam focus. It is important to understand what each of the tools can do to aid in monitoring and troubleshooting. Knowing which tool to select based on symptoms of the problems is the focus of this chapter, as well, as you'll go into further depth within each of these areas.

Stored Procedures Used to Diagnose and Optimize

Many useful stored procedures are available for people who are a little more advanced with T-SQL and the design of system-level applications. Often a programmatic solution is desired over one that provides only visual feedback. In these instances the output from the stored procedures can be acted upon in an automated manner. (See the "Job, Operator, Alert Integration" section later in this chapter).

The sp_who stored procedure reports snapshot information about current SQL Server users and processes. This is a T-SQL alternative to viewing user activity in the current activity window in SQL Server Enterprise Manager. Similarly, sp _lock reports snapshot information about locks, including the object ID, index ID, type of lock, and type or resource to which the lock applies. The sp_lock procedure is also a Transact-SQL alternative used to view lock activity in the current activity window in SQL Server Enterprise Manager.

Other procedures also present alternative mechanisms to query for results normally displayed. The sp_spaceused procedure displays an estimate of the current amount of disk space used by a table or database. This is a T-SQL alternative to viewing database usage in the Enterprise Manager.

Still further procedures provide specific information that can be immediately acted upon. The sp_monitor statement displays statistics, including CPU usage, I/O usage, and the amount of time idle since last executed. This information can be used as an advanced mechanism for SQL Server monitoring.

Other information of interest to system developers is the availability of a wide variety of built-in system functions. Everything from the name of the server to the name of the user and beyond can be identified for a particular connection.

Built-in Functions

A number of built-in SQL Server functions are available to find out information about the server. This information can be used as an aid in troubleshooting, determining SQL Server utilization, and/or to provide for optimization and performance tuning.

Statistics about SQL Server activity since the server was started are stored in predefined SQL Server counters. Other information pertaining to the server is also stored in similar variables . Functions are all categorized by the type of information provided. The sets of functions discussed in this unit can be as well found in Books Online (BOL) under these categories:

• Configuration Functions

• Metadata Functions

• System Functions

• System Statistical Functions

Configuration functions are scalar functions that operate on a single value and then return a single value. Scalar functions can be used wherever an expression is valid. Use these functions to return information about current configuration option settings. All configuration functions are nondeterministic and will not always return the same results every time they are called.

Configuration Functions

Configuration functions return information about settings in the current configuration and allow for adjustments to be made where applicable (as with SET options) using the sp_configure stored procedure. The full list of these functions with a short description is as follows :

• @@DATEFIRST . Returns the current value of the SET DATEFIRST parameter, which indicates the specified first day of each week: 1 for Monday, 2 for Tuesday, and so on through 7 for Sunday.

• @@DBTS . Returns the value of the current timestamp data type for the current database.

• @@LANGID . Returns the local language identifier (ID) of the language currently in use.

• @@LANGUAGE . Returns the name of the language currently in use.

• @@LOCK_TIMEOUT . Returns the current lock timeout setting, in milliseconds , for the current session.

• @@MAX_CONNECTIONS . Returns the maximum number of simultaneous user connections allowed on a SQL Server.

• @@MAX_PRECISION . Returns the precision level used by decimal and numeric data types.

• @@NESTLEVEL . Returns the nesting level of the current stored procedure execution.

• @@OPTIONS . Returns information about current SET options.

• @@REMSERVER . Returns the name of the remote SQL Server as it appears in the login record.

• @@SERVERNAME . Returns the name of the local server running SQL Server.

• @@SERVICENAME . Returns the name of the Registry key under which SQL Server is running. @@SERVICENAME returns MSSQLServer if the current instance is the default instance; this function returns the instance name if the current instance is a named instance.

• @@SPID . Returns the server process identifier (ID) of the current user process.

• @@TEXTSIZE . Returns the current value of the TEXTSIZE option of the SET statement, which specifies the maximum length, in bytes, of text or image data that is returned by a query.

• @@VERSION . Returns the date, version, and processor type for the current installation.

As you can see, configuration functions are primarily used to determine the server properties for the current installation. There is little fine-tuning that can be performed using these functions. The SET parameters affect the user connection greatly and are discussed in full later in this chapter. These functions provide information about the server; other functions must be used to get information about the databases and their objects.

Meta Data Functions

Meta data functions are scalar functions that return information about the database and database objects. These functions are also nondeterministic and return results based on the current situation. The full list of these functions with a short description is as follows:

• COL_LENGTH . Returns the defined length (in bytes) of a column.

• COL_NAME . Returns the name of a database column given the corresponding table identification number and column identification number.

• COLUMNPROPERTY . Returns information about a column or procedure parameter.

• DATABASEPROPERTY . Returns the named database property value for the given database and property name.

• DATABASEPROPERTYEX . Returns the current setting of the specified database option or property for the specified database.

• DB_ID . Returns the database identification (ID) number.

• DB_NAME . Returns the database name.

• FILE_ID . Returns the file identification (ID) number for the given logical file name in the current database.

• FILE_NAME . Returns the logical file name for the given file identification (ID) number.

• FILEGROUP_ID . Returns the filegroup identification (ID) number for the given filegroup name.

• FILEGROUP_NAME . Returns the filegroup name for the given filegroup identification (ID) number.

• FILEGROUPPROPERTY . Returns the specified filegroup property value when given a filegroup and property name.

• FILEPROPERTY . Returns the specified file name property value when given a file name and property name.

• fn_listextendedproperty . Returns extended property values of database objects.

• FULLTEXTCATALOGPROPERTY . Returns information about full-text catalog properties.

• FULLTEXTSERVICEPROPERTY . Returns information about full-text service-level properties.

• INDEX_COL . Returns the indexed column name.

• INDEXKEY_PROPERTY . Returns information about the index key.

• INDEXPROPERTY . Returns the named index property value given a table identification number, index name, and property name.

• OBJECT_ID . Returns the object identification number.

• OBJECT_NAME . Returns the object name.

• OBJECTPROPERTY . Returns information about objects in the current database.

• @@PROCID . Returns the stored procedure identifier (ID) of the current procedure.

• SQL_VARIANT_PROPERTY . Returns the base data type and other information about a sql_variant value.

• TYPEPROPERTY . Returns information about a data type.

NOTE

DATABASEPROPERTY and DATABASEPROPERTYEX The function DATABASEPROPERTYEX is used to obtain information about the current setting of database options or the properties of a specified database. The DATABASEPROPERTY function is provided for backward compatibility and should not be used with a 2000 installation.

Meta data functions are usually used programmatically to determine information about the database and objects that are currently in use by the session. In the following example, the col_length function is used to determine the length of the Titles column within the Title table of the Pubs database.

 Select col_length('Titles', 'Title') As 'Length In Bytes' 

In the next example, the OBJECTPROPERTY function is used in conjunction with the object_id to determine the type of object.

 IF OBJECTPROPERTY (object_id('titles'),'ISTABLE') = 1     PRINT 'Titles is a table' ELSE IF OBJECTPROPERTY (object_id('titles'),'ISTABLE') = 0     PRINT 'Titles is not a table' ELSE IF OBJECTPROPERTY (object_id('titles'),'ISTABLE')   IS NULL     PRINT 'ERROR: Titles is not a defined object' 

System functions and system statistical functions enable you to perform operations and return information about values, objects, settings, and statistical information about the system. System functions are also available to find out more information pertaining to the server, databases, objects, and current session.

System and System Statistical Functions

Many of the system functions pertain more to the application development side than to the troubleshooting, optimizing, and performance-tuning topics, and those functions are not discussed in this chapter. Those functions that can provide useful information have been provided in the following list. Note that unlike the previous groups of functions discussed, some system functions are deterministic.

• APP_NAME . Returns the application name for the current session, if set by the application.

• COLLATIONPROPERTY . Returns the property of a given collation.

• CURRENT_TIMESTAMP . Returns the current date and time; equivalent to GETDATE() .

• CURRENT_USER . Returns the current user; equivalent to USER_NAME() .

• @@ERROR . Returns the error number for the last T-SQL statement executed.

• fn_helpcollations (Deterministic). Returns a list of all the collations supported.

• fn_servershareddrives . Returns the names of shared drives used by a clustered server.

• fn_virtualfilestats . Returns I/O statistics for database files, including log files.

• GETANSINULL . Returns the default nullability for the database for this session.

• HOST_ID . Returns the workstation identification number.

• HOST_NAME . Returns the workstation name.

• PARSENAME (Deterministic). Returns the specified part of an object name. Parts of an object that can be retrieved are the object name, owner name, database name, and server name.

• PERMISSIONS . Returns a value containing a bitmask that indicates the statement, object, or column permissions for the current user.

NOTE

Existence Test The PARSENAME function does not indicate whether an object exists. It just returns the specified piece of the given object name.

IN THE FIELD: PERMISSIONS

The PERMISSIONS function is particularly useful in testing real-world scenarios. Often the vast number of permission settings within an enterprise makes it difficult to obtain permission information about individual users. Because this function returns a value containing a bitmask that indicates the statement, object, or column permissions for the current user, diagnosis of security situations is simplified.

• SERVERPROPERTY . Returns property information about the server instance.

• SESSIONPROPERTY . Returns the SET options settings of a session. ( SET is discussed in detail later in this chapter)

• SESSION_USER . Enables a system-supplied value for the current session's username to be inserted into a table when no default value is specified. Also enables the username to be used in queries, error messages, and so on.

• STATS_DATE . Returns the date that the statistics for the specified index were last updated.

  IN THE FIELD: STATISTICAL UPDATES

  Use the STATS_DATE function to test statistic update settings for any index. Often non-updated statistics cause a system to slow and become unresponsive over time. Periodic checking and UPDATE STATISTICS execution is warranted on most production systems.

• SYSTEM_USER . Enables a system-supplied value for the current system username to be inserted into a table when no default value is specified.

• USER_NAME . Returns a user database username from a given identification number.

What is probably one of the best function sets has been saved until last. Though all the functions mentioned in this unit are important and have their appropriate usage, the most useful set of functions for optimization purposes would be the system statistical functions.

For example, @@CPU_BUSY contains the amount of time the CPU has been executing SQL Server code. The full list of these functions with a short description is as follows:

• @@CONNECTIONS . Returns the number of connections, or attempted connections, the server last started.

• @@CPU_BUSY . Returns the time in milliseconds (based on the resolution of the system timer) that the CPU has spent working since the server was last started.

• fn_virtualfilestats . Returns I/O statistics for database files, including log files.

• @@IDLE . Returns the time in milliseconds (based on the resolution of the system timer) that the server has been idle since last started.

• @@IO_BUSY . Returns the time in milliseconds (based on the resolution of the system timer) that the server has spent performing input and output operations since it was last started.

• @@PACK_RECEIVED . Returns the number of input packets read from the network by the server since last started.

• @@PACK_SENT . Returns the number of output packets written to the network by the server since last started.

• @@PACKET_ERRORS . Returns the number of network packet errors that have occurred on the server since it was last started.

• @@TIMETICKS . Returns the number of microseconds per tick.

• @@TOTAL_ERRORS . Returns the number of disk read/write errors encountered by the server since last started.

• @@TOTAL_READ . Returns the number of non-cache disk reads by the server since last started.

• @@TOTAL_WRITE . Returns the number of disk writes by the server since last started.

NOTE

@@CPU_BUSY This setting enables the database system team to see whether the current configuration is responding well to system stress or would benefit from the use of additional processor consideration. See also @@IDLE for related information.

NOTE

@@IO_BUSY This setting enables the database system team to see whether the current file and disk storage configuration is responding well to system stress or would benefit from the use of additional disk storage consideration.

As you can see from the wide array of functions SQL Server provides, a lot of useful diagnostic information can be used to optimize throughput while providing maximum performance and user response time. One of the primary database administration tools to begin on this arduous journey is DBCC. You can use DBCC to diagnose and act upon some of the most frequent inadequacies of a server configuration.

Database Console Command (DBCC)

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands. DBCC statements enable you to check performance statistics and the logical and physical consistency of a database system. Many DBCC statements can fix detected problems.

DBCC options provide techniques you can use to obtain server status, perform data and index validation, and provide maintenance services. Other miscellaneous options can also affect how an application responds. There are four major groupings of DBCC operations, which have each been addressed separately in the sections that follow.

Status DBCC Operations

Some DBCC operations provide useful information about the processes that have been performed most recently on the server. This type of information can be quite useful in pinpointing the source of SQL activities. Each of the options, presented in the following list, provides a small piece of a very large puzzle, but collectively they can provide a useful picture of the current server activity.

• DBCC INPUTBUFFER . Provides the last statement sent from a client to the server.

• DBCC OPENTRAN . Provides transaction information for the oldest active transaction, distributed transaction, and non-distributed replicated transaction.

• DBCC OUTPUTBUFFER . Returns the current output buffer in hexadecimal and ASCII format for the specified system process ID.

• DBCC PROCCACHE . Displays information about the procedure cache.

• DBCC SHOWCONTIG . Displays fragmentation information for the data and indexes.

  NOTE

  DBCC OPENTRAN Results are displayed only if there is an active transaction or if the database contains current replication information.

  
  

  IN THE FIELD: DBCC OPERATIONS

  One of the most frequently used DBCC status operations is the SHOWCONTIG . Because it can display information specific to data and index fragmentation, it is useful in determining when to carry out maintenance operations.

• DBCC SHOW_STATISTICS . Displays the current distribution statistics for the specified target on the specified table.

• DBCC SQLPERF . Provides statistics about the use of transaction log space in all databases.

• DBCC TRACESTATUS . Displays the status of trace flags.

• DBCC USEROPTIONS . Returns the SET options active (set) for the current connection. SET options are addressed in their entirety later in this chapter.

All these options can provide valuable data to help you determine how performance can be improved. After status information has been generated, the next task in information retrieval is obtaining validation data that can also give you insight into a server.

Validation DBCC Operations

The validation options represent tools available that can reveal database storage problems and also provide the mechanisms to modify and fine-tune the environment.

• DBCC CHECKALLOC . Checks the consistency of disk space.

• DBCC CHECKCATALOG . Checks for consistency in system tables.

• DBCC CHECKCONSTRAINTS . Checks the integrity of a constraint(s).

• DBCC CHECKDB . Checks the allocation and structural integrity of all the objects in the database.

• DBCC CHECKFILEGROUP . Checks the allocation and structural integrity of tables in a filegroup.

• DBCC CHECKIDENT . Checks the current identity value for a table and, if needed, corrects the value.

• DBCC CHECKTABLE . Checks the integrity of the data, index, text, ntext, and image pages.

• DBCC NEWALLOC . Checks the allocation of data and index pages. Equivalent to CHECKALLOC and used for backward compatibility only.

NOTE

DBCC CHECKDB and DBCC CHECKALLOC Use of CHECKALLOC is unnecessary if CHECKDB is used first. CHECKDB contains a superset of options that includes all the functionality provided by CHECKALLOC .

NOTE

Version Improvement Execution of DBCC CHECKDB can be performed with users connected to the database in the 2000 version, which improves upon the locking mechanisms used in SQL Server 7.0 and earlier versions.

Some of these functions are very CPU- and disk- intensive . Caution should be exercised around the time of day a DBCC CHECKDB operation is performed. Other functions as well can impact the server and temporarily increase system overhead.

Maintenance DBCC Operations

Regular maintenance is needed in all database environments. Data and index pages will become fragmented . Data may become corrupt, file sizes may need to be adjusted, and regular maintenance will help you optimize the server environment.

• DBCC DBREINDEX . Rebuilds one or more indexes.

• DBCC DBREPAIR . Drops a damaged database.

• DBCC INDEXDEFRAG . Defragments clustered and secondary indexes of the specified table or view.

• DBCC SHRINKDATABASE . Shrinks the size of the data files in the specified database.

• DBCC SHRINKFILE . Shrinks a specified data file or log file.

• DBCC UPDATEUSAGE . Reports and corrects inaccuracies in the sysindexes table, which may result in incorrect space use reported by sp_spaceused .

NOTE

DBCC DBDEPAIR Compatibility DBCC DBREPAIR is included for backward compatibility only. It is recommended that DROP DATABASE be used to drop damaged databases. DBCC DBREPAIR may not be supported in a future version of SQL Server.

A group of DBCC options do not directly fit into any one of the aforementioned categories. Listed as miscellaneous options, these DBCC operations can provide assistance, help free and better use resources, and provide some tracking mechanisms.

Miscellaneous Operations

• DBCC dllname (FREE) . Unloads the specified extended stored procedure DLL from memory.

• DBCC HELP . Returns syntax information for the specified DBCC statement.

• DBCC PINTABLE . Marks a table to be pinned, and does not flush the pages for the table from memory.

• DBCC ROWLOCK . Used for Microsoft SQL Server 6.5, enabling Insert Row Locking operations on tables.

• DBCC TRACEOFF . Disables trace flag(s). Trace flags are discussed in the next section.

• DBCC TRACEON . Enables trace flag(s). Trace flags are discussed completely in the next section.

• DBCC UNPINTABLE . Marks a table as unpinned. Table pages in the buffer cache can be flushed.

NOTE

Row Lock Compatibility Row-level locking is enabled by default in SQL Server version 2000. The locking strategy is row locking with possible promotion to page or table locking. DBCC ROWLOCK is included for backward compatibility. In a future version of SQL Server, DBCC ROWLOCK may not be supported.

Although DBCC represents one of the premier Microsoft tools available to a SQL Server administrator, there are other alternatives for troubleshooting.

Alternative Mechanisms

A variety of tools from Microsoft, third parties, and industry standards are available for use in troubleshooting, optimization, and reporting of SQL Server information. The next two sections examine some more traditional troubleshooting tools historically used through many versions of SQL Server.

Trace Flags

Trace flags display information about a specific activity within the server and are used to diagnose problems or performance issues. They are particularly useful in deadlock analysis. Trace flags temporarily set specific server characteristics or switch off a particular behavior. Trace flags are often used to diagnose and debug stored procedures and analyze complex system elements.

NOTE

Future Compatibility Trace flag usage has been a longstanding debugging tool that in the past has proven useful. However, with other graphic tools now available that are easier to use and decipher, the use of trace flags is decreasing . Microsoft has stated that behaviors available with these flags may not be supported in future releases of SQL Server.

Four common trace flags are used for troubleshooting different elements of SQL Server.

• 260 . Determines DLL version information.

• 1204 . Determines the command affected by a deadlock and the type of locks that are participating.

• 2528 . Disables/enables parallel checking of objects during DBCC operations.

• 3205 . Disables/enables tape drive compression support.

To determine dynamic link library version information, see the support for GetXPVersion() in SQL Server Books OnLine, but other utilities are available without the use of a flag. Parallel DBCC checking should not usually be disabled, and tape dumps and backups should usually be compressed. Also, the locking information provided can be obtained by viewing the information available through the Enterprise Manager, as shown in Step by Step 12.1.

STEP BY STEP

12.1 Viewing SQL Server Debugging Information Using the Enterprise Manager

1. Start the Enterprise Manager and expand your server, and then the Management folder.

2. Expand the Current Activity Store to view the current processes in use on the system and any locks currently in place because of server activity.

3. Click on Process Info and size the columns in the right pane so that you can simultaneously view Process ID, User, Database, and Status. Make a note of the highest process identification number. (A lot of other information is available for these processes, and you may want to scroll right to see the types of process information available.)

4. Start an instance of the Query Analyzer from the Windows Start menu and provide connection information if needed. Select the Northwind database from the drop-down menu at the top of the screen.

5. Return to the Enterprise Manager, leaving the Query Analyzer window open .

6. Right-click on Current Activity and select Refresh.

7. Click on Process Info and notice the new processes added and the databases in use for the instance of the Query Analyzer you have started.

8. Expand Locks/Process ID and locate the process identification number for the Query Analyzer.

9. Notice the lock in place for Northwind database. Also note that the type of lock is DB (database) and the mode of the lock is S (shared). This is the lowest locking level allowed.

SQL Server uses a number of different lock types and modes. You'll learn more about locking mechanisms later in this chapter, including full details of the implications of each of these mechanisms.

With TCP/IP protocol mechanisms, another form of troubleshooting often implemented is to use the inherent protocol suite properties. TCP/IP is a detailed set of protocols that enables a large amount of information to be passed back and forth within a variety of packet types. SNMP is one protocol within this suite that has a lot of troubleshooting capability.

Simple Network Management Protocol (SNMP)

SNMP is an application protocol that is part of the larger TCP/IP protocol suite. SNMP provides for network management services that report and diagnose operating system and protocol information. Using SNMP, you can monitor an instance of any server that has been configured to report its information. SQL Server can be monitored running on Windows NT 4.0, Windows 98, and UNIX, as well as other platforms.

SNMP requires that a service report information to an agent who can then send data to monitoring computers. Using different tools, an administrator can view and report on a variety of information from the operating system, SQL Server software, and database objects. SNMP uses management information databases (MIB) for each service that it diagnoses. This database provides the necessary hierarchy to enable an agent to query the server for the desired data.

With the Microsoft SQL Server Management Information Base (MSSQL-MIB), you can use SNMP applications to monitor SQL Server. You can monitor performance information, access databases, and view server and database configuration parameters. Monitoring tools are available from a variety of third-party vendors . Systems Management Server, NT, and Windows 2000 all have built-in tools that can be optionally installed to perform SNMP queries.

Optimizing Performance Using Views

To maximize performance in large applications, the design of partitioned views should be examined. Also, placing indexes on views will provide for faster searching and retrieval of data. In itself, a view generally provides for better performance because less data is involved. Always consider the use of views in situations where tables have an extremely large number of columns, application design is left to the user, and tables contain sensitive information.

REVIEW BREAK: SQL Server Tool Use

You have no doubt noticed that a great deal of functionality is available in SQL Server to provide information that can then be beneficially used. Having an enterprise-level database server such as SQL Server necessitates a wide knowledge base to get the best performance and efficiency from the product.

Repeated use of the tools will help you develop the skills necessary to troubleshoot the SQL Server environment and make recommendations on areas of improvement. Whether it be general maintenance, problem solving, or optimization, SQL Server administrators will spend a great deal of their time using these tools.

Over the remainder of the chapter, you will gain some valuable experience using the utilities available in SQL Server. In preparation for the exam, ensure that you have used each tool to the point where you can define its usage and use the tool as a problem-solving mechanism. You will find many exam questions surrounding the use of these tools in a number of the exams you take on your way to becoming an MCSE, MCSD, and/or MCDBA.

Your knowledge of the tools and their uses will be tested by giving you many sample outputs and scenarios that were in place when the output was taken. It will be your responsibility to absorb all the facts and recommend a solution to the problem based on the set of choices given in the answers.