Managing Your SQL Servers

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 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

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.

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);

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

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 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.