Optimizing SQL Server Configuration | MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition

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

A number of tools, utilities, and information sources are built in to SQL Server to help you optimize SQL Server operations. These mechanisms can be used to diagnose, maintain, and optimize all operations.

To obtain immediate information about the server, you can observe the current activity using the Enterprise Manager or stored procedures. To observe information from a historical perspective, you can view the SQL Server Logs from the Enterprise Manager.

Current Server Activity

The SQL Server Enterprise Manager provides a facility where an administrator can go to find out pertinent information about what the server is currently doing. Use the Current Activity window to perform monitoring of SQL Server. This enables you to determine, at a glance, the volume and general types of activity on the system related to current blocked and blocking transactions in the system, connected users, the last statement executed, and locks that are currently in effect.

The Current Activity view provides a display of process information, locks broken down by process identification, and locks broken down by object.

Process Information

The process information provides information on all activity currently executing against the system. It also lists current connections that may not be active but are still using resources. Here are descriptions of the process information columns :

Process ID . SQL Server Process Identifier.
Context ID . Execution Context Identifier, used to uniquely identify the sub-threads operating on behalf of the process.
User. Identifier of the user who executed the command.
Database. Database currently being used by the process.
Status. Status of the process.
Open Transactions. Number of open transactions.
Command. Command currently being executed.
Application. Name of the application program being used.
Wait Time. Current wait time in milliseconds . When the process is not waiting, the wait time is zero.
Wait Type. Indicates the name of the last or current wait type.
Wait Resources. Textual representation of a lock resource.
CPU. Cumulative CPU time for the process.
Physical IO. Cumulative disk reads and writes .
Memory Usage. Number of pages in the procedure cache that are currently allocated. A negative number indicates that the process is freeing memory allocated by another process.
Login Time. Time at which a client process logged in to the server.
Last Batch. Last time a client process executed a remote stored procedure call or an EXECUTE statement.
Host. Name of the workstation.
Network Library. Column in which the client's network library is stored. Every client process comes in on a network connection. Network connections have a network library associated with them that enables them to make the connection.
Network Address. Assigned unique identifier for the network interface card on each user's workstation.
Blocked By. Process ID (SPID) of a blocking process.
Blocking. Process ID (SPID) of processes that are blocked.

NOTE

CPU Returning Zero The CPU entry is updated only for processes performed on behalf of T-SQL statements executed when SET STATISTICS TIME ON has been activated in the same session. When zero is returned, SET STATISTICS TIME is OFF . CPU time for each process is accumulated regardless of the statistics setting.

A significant amount of information is available about each of the processes utilizing the SQL Server database system. Each process also controls SQL Server locking behavior.

Lock Information

Locking information provides information on all locks currently being used by SQL Server to protect the object and data integrity of all SQL Server objects. Here are descriptions of the lock information columns:

spid . Server process ID of the current user process.
ecid . Execution context ID. Represents the ID of a given thread associated with a specific spid.
Lock type . Any of a number of available types. Lock types are listed and defined later in this unit.
Lock mode . The level a lock has been given according to the needs of the process. Lock modes are defined later in this unit.
Status . Whether or not a lock was obtained ( GRANT ) or is being blocked ( WAIT ) or converted ( CNVT ).
Owner . The lock owner.
Index . The index associated with the resource. If the index is clustered, you see the table name instead.
Resource . Resource being locked.

There are several available lock types and lockable SQL Server resource:

RID . Row identifier. Used to lock a single row individually within a table. This is identified by a fileid:page:rid combination, where rid is the row identifier on the page.
KEY . Key; a row lock within an index. Used to protect key ranges in serializable transactions. This is represented as a hexadecimal number used internally by SQL Server.
PAG . Data or index page. The page is identified by a fileid:page combination, where fileid is the file id in the sysfiles table, and page is the logical page number within that file.
EXT . Contiguous group of eight data pages or index pages. The first page number in the extent being locked. The page is identified by a fileid:page combination.
TAB . Entire table, including all data and indexes. The ObjId column contains the object ID of the table.
DB . Database; the dbid column contains the database ID of the database.

Several lock modes control how SQL Server sets and recognizes locks, blocking, and user access. Lock modes represent levels of locking from the lowest levelsharedto the highest levelexclusive.

Shared ( S ). Used for operations that do not change or update data.
Update ( U ). Used on resources that can be updated.
Exclusive ( X ). Used for data modification operations.
Intent ( I ). Used to establish a lock hierarchy.
Schema . Used when an operation dependent on the schema of a table is executing. There are two types of schema locks: schema stability ( Sch-S ) and schema modification ( Sch-M ).
Bulk update ( BU ). Used when bulk copying data into a table and the TABLOCK hint is specified.
RangeS_S . Shared range, shared resource lock; serializable range scan.
RangeS_U . Shared range, update resource lock; serializable update scan.
RangeI_N . Insert range, null resource lock. Used to test ranges before inserting a new key into an index.
RangeX_X . Exclusive range, exclusive resource lock. Used when updating a key in a range.

Locks are set at different modes as needed to allow for data updates, index creation, and table redefinition. An example of locking used during an update process is shown in Step by Step 12.3.

STEP BY STEP

12.3 Viewing Lock Behavior

Start the Enterprise Manager and expand your server and the Management folder.
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.
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.
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.
Return to the Enterprise Manager, leaving the Query Analyzer window open, right-click on Current Activity, and select Refresh.
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.
Expand Locks/Process ID and locate the process identification number for the Query Analyzer.
Notice the lock in place for the Northwind database. Also note that the type of lock is DB (database) and the mode the lock is S (shared). This is the lowest locking level that allows other user activity to resume normally.
Return to the Query Analyzer and enter the following code to begin a transaction and hold a data update without committing it:
```
 BEGIN TRANSACTION UPDATE Customers        SET ContactName = 'Maria Anderson'        WHERE CustomerID = 'ALFKI' 
```
Return to the Enterprise Manager, leaving the Query Analyzer window open, right-click on Current Activity, and select Refresh.
Expand Locks/Process ID and locate the process identification number for the Query Analyzer.
Now notice the additional locks held until the update is committed. These locks will prevent any other process from updating the data until the current update is finished.
Return to the query analyzer and execute a COMMIT TRANSACION statement.
Refresh the locking information in the Enterprise Manager to ensure all locks are removed.

Only compatible lock types can be placed on a resource that is already locked. Table 12.1 provides a compatibility matrix for resource lock modes that shows which locks are compatible with other locks obtained on the same resource.

Table 12.1. Lock Computability Matrix

Existing Granted Lock Mode
Requested Lock Mode	IS	S	U	IX	SIX	X
Intent Shared (IS)						`X`
Shared (S)				`X`	`X`	`X`
Update (U)			`X`	`X`	`X`	`X`
Intent Exclusive (IX)		`X`	`X`		`X`	`X`
Shared with Intent Exclusive (SIX)		`X`	`X`	`X`	`X`	`X`
Exclusive (X)	`X`	`X`	`X`	`X`	`X`	`X`

The compatibility matrix indicates whether a specific type of lock can be granted with another already in place.

Some of the same information seen in the Current Activity window can also be retrieved programmatically using stored procedures. Stored procedures play an important part in developing system code to aid in server management.

Stored Procedures

Stored procedures can be used to perform data gathering in much the same manner that the Enterprise Manager uses to gather its information to display to the user. By querying system objects, functions, and counters, the stored procedures produce output that can be used directly, or they can perform other conditional activity through the systematic use of the results. Much system level coding can be developed by leveraging the procedures that already exist.

Hundreds of available stored procedures are installed with SQL Server, as well as a few dozen extended stored procedures. Not all these procedures are used in data gathering, troubleshooting, and optimization, but many provide information that assists in these activities.

Stored procedures are stored in the Master and user databases, whereas extended stored procedures are found in only the Master database. Additional stored procedures can be created, and additional extended stored procedures can be installed. A selection of the more commonly used procedures is covered in this section. For more information on these and other procedures, refer to stored procedures coverage in SQL Server Books Online.

sp_who

This procedure reports current information about users and processes, including the executing statement and whether the statement is blocked. This is a T-SQL alternative to viewing user activity in the Enterprise Manager.

The resultset provided by this procedure provides user and process information, including the process identification and status information, as well as an ID for any blocking process. Also provided from the command is the database in use and the T-SQL command that is executing.

sp_lock

This procedure reports information about locks and the resources affected by locking. This is a T-SQL alternative to viewing lock activity in the Enterprise Manager.

It provides a resultset similar to the one displayed in the Enterprise Manager, listing all relevant locks, their status, mode, and resources being affected.

sp_monitor

This procedure displays statistics, including CPU usage, I/O usage, and the amount of time idle since the stored procedure was last executed.

A variety of information is produced in the resultset for this command. The time of execution of sp_monitor , the number of seconds that SQL Server has been working, the number of seconds that SQL Server has been performing input and output operations, and the number of seconds that SQL Server has been idle provide information that can be useful in determining whether processing resources are adequate.

The number of input packets read by SQL Server, output packets written by SQL Server, and errors encountered by SQL Server while reading and writing packets can help you determine SQL Server's network utilization.

The number of reads by SQL Server, number of writes, and the number of errors encountered by SQL Server while reading and writing can help you determine file storage interactions.

Knowing the number of logins or attempted logins to SQL Server can be useful in security information gathering or as an aid in determining SQL Server utilization.

sp_spaceused

This 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. With sp_spaceused you can easily see the number of rows, disk space reserved, and disk space used by a table in the current database, or display the disk space reserved and used by the entire database.

xp_sqlmaint

This is an extended stored procedure that calls the SQL maintenance utility. Passed during the call to this utility is a string containing switches that indicate the tasks to be performed and other related details. The sqlmaint utility performs a set of maintenance operations on one or more databases, in a similar fashion as each operation's corresponding T-SQL functions.

The sqlmaint utility performs maintenance operations on one or more databases. Use sqlmaint to run DBCC checks, back up a database and its transaction log, update statistics, and rebuild indexes. The utility performs database maintenance activities and generates a report. The report can be sent to a designated text file, HTML file, or email account.

sp_configure

This stored procedure is used to display and/or alter global configuration settings for the server. When executed with no parameters, the stored procedure returns a resultset showing the current configuration options.

After you execute the procedure to change a configuration option, you should use the RECONFIGURE WITH OVERRIDE statement for the change to take immediate effect.

sp_dboption

This procedure is used to display and/or alter database options. It is supported for backward compatibility, and you should therefore use ALTER DATABASE to set database options.

sp_help

This procedure reports information about an object listed in the sysobjects table. In general, you can tack any object name to the end of help to receive help and information about a particular type of object, such as sp_helpalert , sp_helpfilegroup , sp_helpserver , and sp_helpuser . Over 100 stored procedures have the sp_help prefix.

SQL Profiler Stored Procedures and Functions

Use T-SQL stored procedures to gather SQL Profiler statistics. The SQL Profiler uses stored procedures to create traces and send the trace output to the appropriate location. These procedures can be used from within an application to create traces manually. This enables you to write custom applications specific to the needs of your business.

In SQL Server version 7.0, these procedures were all prefixed with xp_trace . The features of many of these procedures have been combined, and in the 2000 release there are fewer, more streamlined procedures, as summarized in the following list:

fn_trace_geteventinfo . Returns information about the events traced.
fn_trace_getfilterinfo . Returns information about the filters applied to a trace.
fn_trace_getinfo . Returns information about a trace.
sp_trace_create . Creates a trace definition. The new trace is created in a stopped state.
sp_trace_generateevent . Creates a user-defined event.
sp_trace_setevent . Adds or removes an event or event column. May be executed only on an existing stopped trace. SQL Server returns an error if this stored procedure is executed on a trace that does not exist or is not stopped.
sp_trace_setfilter . Applies a filter to a trace. May only be executed on an existing stopped trace.
sp_trace_setstatus . Modifies the current state of the specified trace. Stops, starts, or deletes the trace definition.

Using these stored procedures, you can configure and execute traces and forward trace events from one or more servers to a file.

REVIEW BREAK: Finding the Facts

Now you have examined everything in a SQL Server installation, using all but the two most diverse and granular tools, Query Analyzer and Profiler. Gathering information from the operating system tools and the server activity procedures and displays plays an important part in the initial fact-finding that must be performed to alter any of the properties of the server.

The processes discussed thus far are often used in an implementation as a starting point toward:

Development of system documentation
Establishing record keeping procedures
Troubleshooting system problems
Optimizing the database server
Tracking data trends

As you begin any of these processes, begin with the hardware and operating system and then proceed into the application server. As you get further into data gathering and analysis, you should look into each database and the interactions between the data and user applications. To view SQL Server tasks in detail, after the initial data gathering processes, use the SQL Server Profiler to develop a more complete picture. When more granularity is desired and you want to look into the user applications, the functionality provided by the Query Analyzer can be used to obtain the most detailed and granular data.