Managing Your SQL Servers

  • 2/15/2012

Managing Server Activity

As a database administrator, your job is to be sure that SQL Server runs smoothly. To ensure that SQL Server is running optimally, you can actively monitor the server to do the following:

  • Keep track of user connections and locks.

  • View processes and commands that active users are running.

  • Check the status of locks on processes and objects.

  • See blocked or blocking transactions.

  • Ensure that processes complete successfully, and detect errors if they do not.

When problems arise, you can terminate a process if necessary.

Examining Process Information

Process information provides details about the status of processes, current user connections, and other server activity. You can view process information by completing the following steps:

  1. Start SQL Server Management Studio, and then connect to a server.

  2. Use the Object Explorer view to access an instance of the Database Engine.

  3. Right-click the Database Engine instance and then select Activity Monitor.

In Activity Monitor, shown in Figure 1-9, you should see a graphical overview of the server activity, as well as an activity summary by processes, resource waits, data file I/O, and recent expensive queries. The overview and the summaries are provided in separate panels that you can expand to display or shrink to hide.

Figure 1-9

Figure 1-9 Activity Monitor.

The Overview panel has graphs depicting processor time, waiting tasks, database I/O, and batch requests. By default, the graphs are updated every 10 seconds. You can specify a different refresh interval by right-clicking in the panel, pointing to Refresh Interval, and then selecting an interval, such as 30 seconds.

In the Processes panel, processes are sorted by process ID by default, but you can arrange them by any of the available information categories summarized in Table 1-2. Click a category header to sort processes based on that category. Click the same category header again to perform a reverse sort on the category.

Table 1-2 Process Information Used in Database Administration

Category

Description

Session ID

Provides the session ID of the process on the server.

User Process

Flags the process as being either a user process (flag=1) or a server process (flag=0).

Login

Shows which user is running the process by SQL Server ID, service name, or domain account.

Database

Indicates the database with which the process is associated.

Task State

Shows the status of the process. A running process is active and currently performing work. A runnable process has a connection but currently has no work to perform. A sleeping process is waiting for something, such as user input or a lock. A background process is running in the background, periodically performing tasks. A suspended process has work to perform but has stopped.

Command

Displays the command being executed or the last command executed.

Application

Shows the application or SQL Server component (such as a report server) connecting to the server and running the process.

Wait Time

Indicates the elapsed wait time in milliseconds.

Wait Type

Specifies whether the process is waiting or not waiting.

Wait Resource

Displays the resource that the process is waiting for (if any).

Blocked By

Displays the process ID blocking this process.

Head Blocker

Shows 1 if the session ID is the head blocker in the blocking chain. Otherwise, it shows 0.

Memory Use

Displays the amount of memory the process is using (in kilobytes).

Host Name

Displays the host from which the connection originated.

Workload Group

Displays the name of the Resource Governor workload group for the query.

Tracking Resource Waits and Blocks

When you are diagnosing performance issues, you should look closely at the Wait Time, Wait Type, Wait Resource, and Blocked By values for each process. Most of the process information is gathered from data columns returned by various dynamic management views, including the following:

  • sys.dm_os_tasks. Returns information about each task that is active in the instance of SQL Server.

  • sys.dm_os_waiting_tasks. Returns information about each task that is waiting on some resource.

  • sys.dm_exec_requests. Returns information about each request that is executing within SQL Server.

  • sys.dm_exec_sessions. Returns information about each authentication session within SQL Server.

Although Activity Monitor provides a good overview, you might need to use these dynamic management views to get more detailed information about processes, resource waits, and resource blocks.

The Resource Waits panel provides additional information about resource waits. Each wait category combines the wait time for closely related wait types, such as buffer I/O or network I/O. Keep the following in mind:

  • Wait Time. Shows the accumulated wait time per second. Here, a rate of 3,000 ms indicates that three tasks on average were waiting with this wait category.

  • Recent Wait Time. Shows the wait average of accumulated wait time per second. This combines all the wait times over the last several minutes and averages them for this wait category.

  • Average Waiter Count. Shows the average number of waiting tasks per second for this wait category.

  • Cumulative Wait Time. Shows the total amount of wait time for this wait category since SQL Server was started or the wait statistics were reset.

To get a clearer picture of resource waits and blocks, you can use the sys.dm_tran_locks view. Table 1-3 summarizes the information returned with this view. Actual values are in parentheses, preceded by a general category name.

Table 1-3 Lock-Related Information Used in Database Administration

Category

Type

Description

Process ID

(request_session_id)

The process ID of the related user process within SQL Server.

Object ID

(resource_associated_entity_id)

The ID of the entity with which a resource is associated.

Context

(request_exec_context_id)

The ID of the thread associated with the process ID.

Batch ID

(request_request_id)

The batch ID associated with the process ID.

Type (resource_type)

RID

Row identifier; used to lock a single row within a table.

KEY

A row lock within an index; used to protect key ranges.

PAGE

A lock on a data or index page.

EXTENT

A lock on a contiguous group of eight data or index pages.

TABLE

A lock on an entire table, including all data and indexes.

DATABASE

A lock on an entire database.

METADATA

A lock on descriptive information about the object.

ALLOCATION_UNIT

A lock on allocation unit page count statistics during deferred drop operations.

HOBT

A lock on basic access path structures for heap or index reorganization operations or heap-optimized bulk loads.

Subtype

(resource_subtype)

The lock subtype, frequently used with METADATA locks to identify metadata lock activity.

Description

(resource_description)

Gives optional descriptive information.

Request Mode

(request_mode)

S

Shared; used for read-only operations, such as a SELECT statement.

U

Update; used when reading/locking an updatable resource; prevents some deadlock situations.

X

Exclusive; allows only one session to update the data; used with the modification operations, such as INSERT, DELETE, and UPDATE.

I

Intent; used to establish a lock hierarchy.

Sch-S

Schema stability; used when checking a table’s schema.

Sch-M

Schema modification; used when modifying a table’s schema.

BU

Bulk update; used when bulk copying data into a table and the TABLOCK hint is specified.

RangeS_S

Serializable range scan; used with shared resource locks on shared ranges.

RangeS_U

Serializable update; used for updating resource locks on shared ranges.

RangeI_N

Insert range with a null resource lock; used to test ranges before inserting a new key into an index.

RangeX_X

Exclusive range with an exclusive lock; used when updating a key in a range.

Request Type

(request_type)

The type of object requested.

Request Status

(request_status)

GRANT

The lock was obtained.

WAIT

The lock is blocked by another process.

CNVT

The lock is being converted—that is, it is held in one mode but waiting to acquire a stronger lock mode.

Owner Type

(request_owner_type)

CURSOR

The lock owner is a cursor.

SESSION

The lock owner is a user session.

TRANSACTION

The lock owner is a transaction.

SHARED_TRANSACTION_WORKSPACE

The lock owner is the shared portion of the transaction workspace.

EXCLUSIVE_TRANSACTION_WORKSPACE

The lock owner is the exclusive portion of the transaction workspace.

Owner ID

(request_owner_id)

The owner ID associated with the lock.

Owner GUID

(request_owner_guid)

The globally unique identifier (GUID) of the owner associated with the lock.

Database

(resource_database_id)

The database containing the lock.

Object

(resource_associated_entity_id)

The name of the object being locked.

Troubleshooting Deadlocks and Blocking Connections

Two common problems you might encounter are deadlocks and blocking connections. Deadlocks and blocking connections, as described in the following list, can occur in almost any database environment, especially when many users are making connections to databases:

  • Deadlocks can occur when two users have locks on separate objects and each wants a lock on the other’s object. Each user waits for the other user to release the lock, but this does not happen.

  • Blocking connections occur when one connection holds a lock and a second connection wants a conflicting lock type. This forces the second connection to wait or to block the first.

Both deadlocks and blocking connections can degrade server performance.

Although SQL Server can detect and correct deadlocks, you can help speed up this process by identifying potential problems and taking action when necessary. Clearing blocks is a manual step; you must kill the blocking process.

Process information can tell you when deadlocks or blocking connections occur. Examine these process information columns: Wait Time, Wait Type, Resource, Blocking, and Blocked By. When you have a deadlock or blocking situation, take a closer look at the locks on the objects that are causing problems. Refer to the Tracking Resource Waits and Blocks section earlier in this chapter for details. You also might want to stop the offending processes, and you can do this by following the steps described in the Killing Server Processes section later in this chapter.

You can also use the sys.dm_tran_locks view to obtain information about active locks. Each row in the results returned by this view represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. The following example returns a list of locks in the Customer database:

T-SQL

USE customer;
GO
SELECT * FROM sys.dm_tran_locks

PowerShell

Invoke-Sqlcmd -Query "USE customer; SELECT * FROM sys.dm_tran_locks"
-ServerInstance "DbServer25"

In the result set, the results are organized in two main groups. Columns that begin with resource_ describe the resource on which the lock request is being made. Columns that begin with request_ describe the lock request itself. (Table 1-3 lists the correlation between the columns in the results and the categories listed in Activity Monitor.) While Activity Monitor returns the actual database name, the resource_database_id column returns the database_id as set in the sys.databases view. In SQL Server, database IDs are set on a per-server basis. You can determine the database name for a particular database ID on a particular server by using the following statement:

SELECT name, database_id FROM sys.databases

In the results returned by sys.dm_tran_locks, request_session_id tracks process IDs. Process IDs tracked internally by SQL Server do not correspond to process IDs tracked by the operating system. You can determine the association between the SQL Server process IDs and Windows thread IDs by using the following query:

SELECT ServerTasks.session_id, ServerThreads.os_thread_id
    FROM sys.dm_os_tasks AS ServerTasks
    INNER JOIN sys.dm_os_threads AS ServerThreads
        ON ServerTasks.worker_address = ServerThreads.worker_address
    WHERE ServerTasks.session_id IS NOT NULL
    ORDER BY ServerTasks.session_id;
GO

While you are connected to the database that contains the locking object, you get more information about the locking object and blocking information. Use the following query, where <resource_associated_entity_id> is the value in the related column, to get information about the locking object:

SELECT object_name(object_id), *
    FROM sys.partitions
    WHERE hobt_id=<resource_associated_entity_id>

Use the following query to get blocking information:

SELECT
       tr1.resource_type,
       tr1.resource_subtype,
       tr1.resource_database_id,
       tr1.resource_associated_entity_id,
       tr1.request_mode,
       tr1.request_type,
       tr1.request_status,
       tr1.request_session_id,
       tr1.request_owner_type,
       tr2.blocking_session_id
   FROM sys.dm_tran_locks as tr1
   INNER JOIN sys.dm_os_waiting_tasks as tr2
       ON tr1.lock_owner_address = tr2.resource_address;

Tracking Command Execution in SQL Server

Sometimes you want to track the commands that users are executing. You can do this by using Activity Monitor as follows:

  1. In SQL Server Management Studio, use the Object Explorer view to access an instance of the Database Engine.

  2. Right-click the Database Engine instance and then select Activity Monitor.

  3. Expand the Processes panel by clicking Options. The entries in the Session ID, User Process, and Login columns can help you track user sessions and the processes they are using.

  4. Right-click a process and then select Details to display the Session Details dialog box, as shown in Figure 1-10. This dialog box shows the last command batch executed by the user.

    Figure 1-10

    Figure 1-10 The Session Details dialog box displays the user’s most recent command batch.

  5. To track current commands being executed by the user, click Refresh periodically.

  6. To end the process, click Kill Process. Then, when prompted, choose Yes.

Killing Server Processes

Sometimes you might need to stop processes that are blocking connections or using too much CPU time. To do this, complete the following steps:

  1. In SQL Server Management Studio, use the Object Explorer view to access an instance of the Database Engine.

  2. Right-click the Database Engine instance and then select Activity Monitor.

  3. Expand the Processes panel by clicking Options.

  4. Right-click the process you want to stop, and then choose Kill Process. When prompted to confirm, click Yes.