| Downloads |
|---|
| 48437.zip |
Editor's note: Windows systems administrators are increasingly being asked to assume multiple roles in their companies. Specifically, many Windows systems administrators are charged with setting up and maintaining not only the Windows servers and workstations in their networks but also SQL Server machines. To help save time and effort, we'll show you how to automate tasks through T-SQL code and scripts.
There are times when the workload on a specific Microsoft SQL Server machine exhausts all the server machine resources. In times like that, you must quickly identify the SQL Server sessions that consume the most resources of the machine. That can be a tedious job: You must connect to SQL Server Enterprise Manager; go to the Current Activity window located under the Management folder; click Process Info; sort the processes table by physical I/O, memory usage, or CPU in descending order; write down the session IDs; and call all the users who are connected to SQL Server with those session IDs to decide which consuming process to terminate to free up the resource.
I wrote a useful T-SQL stored procedure called getMaxResourceConsumeSessions that can quickly identify the most resource-consuming sessions and output information about them to a table in a specified HTML file. The stored procedure uses the DBCC SQLPERF (THREADS) statement to obtain the physical I/O, memory usage, and CPU values. This T-SQL statement isn't documented in SQL Server Books Online (BOL) but is mentioned in other resources, such as Microsoft Developer Network (MSDN). The stored procedure then populates a table with the results. As Figure 1 shows, this table contains five columns: factor, SessionID, ThreadID, userLoginName, and computed_value. The factor column specifies the type of value (i.e., physical I/O, memory usage, or CPU). The SessionID and ThreadID columns identify the session number and thread number, respectively. The userLoginName column identifies the user who initiated the session. The computed_value column specifies the maximal resource-consuming value for the factor in that row.
Instead of using DBCC SQLPERF (THREADS), some administrators might be tempted to use the sp_who system stored procedure. Although you can use sp_who to obtain the session IDs (labeled as spid in sp_who), thread IDs (ecid), and login names (loginname) for current processes, its output doesn't include physical I/O, memory usage, or CPU statistics. So, you can't use it here.
How the Stored Procedure Works
Listing 1 shows getMaxResourceConsumeSessions. This stored procedure starts by creating the #ListOfWorkThreads temporary table and the ##MaximalResourceConsumeSessions global temporary table. The #ListOfWorkThreads table will hold the temporary result of the DBCC SQLPERF (THREADS) statement. The ## MaximalResourceConsumeSessions table will contain the maximal resource-consuming value for each factor (i.e., I/O, CPU, and memory usage).
Next, getMaxResourceConsumeSessions uses the EXEC statement to dynamically execute the DBCC SQLPERF (THREADS) statement, outputting the results to #ListOfWorkThreads. As callout A shows, the stored procedure then uses the SELECT MAX statement to obtain from #ListOfWorkThreads the maximal value for each factor. The stored procedure uses the UNION ALL operator to combine the results into ##MaximalResourceConsumeSessions. Finally, a call to the sp_makewebtask system stored procedure generates the HTML file containing the table.
How to Use the Stored Procedure
I wrote and tested getMaxResourceConsumeSessions on SQL Server 2000 Standard Edition Service Pack 1 (SP1) installed on a Windows XP machine, but it should also work on all other versions of SQL Server 2000. There shouldn't be a problem executing it on SQL Server 2005. You should compile the code on the master database. No code customizations are needed.
You can execute the getMaxResourceConsumeSessions stored procedure by using the osql utility from a batch file or from a T-SQL job that has a line of code such as
exec
getMaxResourceConsumeSessions
'c:\temp\maxResourceSessions.htm'
(Although this command appears on several lines here, you would enter it all on one line.) Listing 2 shows an example of how to invoke and call getMaxResourceConsumeSessions from a T-SQL script.
The Results
When you run the getMaxResourceConsumeSessions stored procedure, you'll get the results not only in the HTML file but also in SQL Query Analyzer. In Query Analyzer, the results will appear in the Grids tab, as Figure 2 shows. In the Messages tab, you'll also receive a message, such as DBCC execution completed. If DBCC printed error messages, contact your system administrator. The HTML file c:\temp\maxResourceSessions.htm containing the maximal resource-consuming session has been generated.
No matter whether you view the getMaxResourceConsumeSessions results in Query Analyzer or the HTML file, you'll immediately know which process to terminate to free up the resource. Then all you'll need to do is terminate the process and let the user know that you'll be doing so.
Eli Leiba ([email protected]) is a senior application DBA at Israel Electric Company, a teacher at Microsoft CTEC, and a senior database consultant for 2Cher IT Professionals Co. With 14 years of experience, he is certified in Oracle and SQL Server database administration and implementation and has a B.S. in Computer Science.
