Listing 1: The getMaxResourceConsumeSessions Stored Procedure

Create procedure getMaxResourceConsumeSessions 
  (@HTMLfileName varchar(100))
AS
  BEGIN
    Declare @TSQLcmd varchar(50)
      set nocount on
        Create table #ListOfWorkThreads 
          (SessionID                          smallint,
          ThreadID                            smallint,
          sessionStatus                     varchar(128),
          userLoginName                  varchar(128),
          physical_I_O                        int,
          session_CPU                      int,
          session_Memory_Usage    int)

        Create table ##MaximalResourceConsumeSessions 
          (factor                     varchar(10),
          SessionID              smallint,
          ThreadID               smallint,
          userLoginName     varchar(128),
          computed_value    int)

-- BEGIN COMMENT
        -- Populate the #ListOfWorkThreads table.
-- END COMMENT
        set @TSQLcmd = 'DBCC SQLPERF (THREADS)'
        INSERT into #ListOfWorkThreads exec (@TSQLcmd)

-- BEGIN CALLOUT A
-- BEGIN COMMENT
        -- Populate the ##MaximalResourceConsumeSessions table.
-- END COMMENT
        INSERT ##MaximalResourceConsumeSessions
          SELECT 'I/O', 
                        SessionID    ,
                        ThreadID               ,
                        userLoginName  ,
                        physical_I_O
            FROM #ListOfWorkThreads
            WHERE physical_I_O = (SELECT MAX(physical_I_O)
            FROM #ListOfWorkThreads )
            UNION ALL
          SELECT 'Memory',
                         SessionID    ,
                         ThreadID               ,
                         userLoginName, 
                         session_Memory_Usage
            FROM #ListOfWorkThreads
            WHERE session_Memory_Usage = (SELECT MAX(session_Memory_Usage)
            FROM #ListOfWorkThreads)
            UNION ALL
          SELECT 'CPU',
                         SessionID    ,
                         ThreadID               ,
                         userLoginName,
                         session_CPU
            FROM #ListOfWorkThreads
            WHERE session_CPU = (SELECT MAX(session_CPU) 
            FROM #ListOfWorkThreads )
-- END CALLOUT A

-- BEGIN COMMENT
        -- Show the results. Generate the HTML file and print a message.
        -- Execute the Web task.
 -- END COMMENT
       exec sp_makewebtask @outputfile = @HTMLfileName, 
          @query = N'SELECT * from ##MaximalResourceConsumeSessions ',
          @bold = 1, 
          @webpagetitle = 'Maximal Resource-Consuming Session IDs',
          @resultstitle = 'Maximal Resource-Consuming Session IDs' 

            select * from ##MaximalResourceConsumeSessions 

-- BEGIN COMMENT
            -- Print another message.
-- END COMMENT
            print 'The HTML file ' + @HTMLfileName  + 
            ' containing the maximal resource-consuming session has been generated.'

-- BEGIN COMMENT
            -- Remove the temporary tables.
-- END COMMENT
            drop table #ListOfWorkThreads
            drop table ##MaximalResourceConsumeSessions 
            set nocount off
END
go