How to find the space occupied by a process (Session or SPID).
-
Hi,
How to find the space occupied by a process (Session or SPID).
The SP_Who2 lists many columns but we need to find the exact space used by session in SQL server 2000.
Please help me out...
Thanks in Advance!
Anbu
-
To the best of my knowledge you cannot see exactly how much space a process uses becuase it there really is no definition of this. A connection as I recall uses about 50k memory, each query then may or may not use tempdb while performing work and then you have the buffer and cache areas which can vary moment to moment based on what query/command is issued.
-
By space, do you mean memory?
Here is a procedure which will email alerts for sessions over a certain size and log others over another size you specify - I use it to find sessions using up large amounts of memory. I schedule this as a job, but you could set your thresholds lower and run it more often to log info to the table, or you could also alter the select on sysprocesses to filter by db.
This code has only been reviewed by me, and is probably a horrible example of code which will destroy your server - it comes unwarrantied and unsupported by me or sqlservercentral.com!!
It also uses mail procedures which you dont' have and will need to adjust if you want mail alerts.
CREATE TABLE [dbo].[memory_hogs] (
[spid] [smallint] NOT NULL ,
[dbid] [smallint] NOT NULL ,
[dbname] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cpu] [int] NOT NULL ,
[physical_io] [bigint] NOT NULL ,
[memusage] [int] NOT NULL ,
[statement] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[program_name] [nchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[date_inserted] [datetime] NULL) ON [PRIMARY]
CREATE PROCEDURE FIND_MEMORY_HOGS
@alert_memory_threshold int, --MEMORY THRESHOLD IN PAGES. PAGE = 8K.
@log_memory_threshold int
as
DECLARE @message varchar(3000)
DECLARE @concat_message varchar(3000)
DECLARE @newmessage varchar(3000)
DECLARE @SMTP_Server as nvarchar(30) , @MailStatus as bit
DECLARE @HOG_COUNT AS INT
DECLARE @LOG_HOG_COUNT AS INT
SET @message = ''
SELECT @LOG_HOG_COUNT = COUNT(spid)
FROM master.dbo.sysprocesses
WHERE memusage > @log_memory_threshold
IF @LOG_HOG_COUNT > 0
BEGIN
-- Output from DBCC INPUTBUFFER in this table.
CREATE TABLE #inputbuffer (eventtype nvarchar(30) NULL,
params int NULL,
eventinfo nvarchar(255) NULL)
--get list of all sysprocesses using memory over our param
CREATE TABLE #memory_hogs (
[spid] [smallint] NOT NULL ,
[dbid] [smallint] NOT NULL ,
[dbname] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cpu] [int] NOT NULL ,
[physical_io] [bigint] NOT NULL ,
[memusage] [int] NOT NULL ,
[statement] [varchar] (2000),
program_name nchar(128)) ON [PRIMARY]
DECLARE @my_spid int
INSERT #memory_hogs
SELECT spid,dbid,db_name(dbid) as dbname,cpu,physical_io,memusage,'',program_name
FROM master.dbo.sysprocesses
WHERE memusage > @log_memory_threshold
DECLARE cur_Memory_Hogs CURSOR FAST_FORWARD
FOR
SELECT spid
FROM master.dbo.sysprocesses
WHERE memusage > @log_memory_threshold
set @message = '<strong><span style=''font-family: arial, "lucida console", sans-serif''>' + @@SERVERNAME + ':These processes are using more than ' + convert(varchar(20),@alert_memory_threshold) +' pages of memory space </span></strong>'
OPEN cur_Memory_Hogs
FETCH NEXT FROM cur_Memory_Hogs into @my_spid
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #inputbuffer
EXEC ('DBCC INPUTBUFFER (' + @my_spid + ') WITH NO_INFOMSGS')
UPDATE #memory_hogs
SET statement = (SELECT eventinfo FROM #inputbuffer)
WHERE spid = @my_spid
PRINT @newmessage + @message
set @message = @message + @newmessage
TRUNCATE TABLE #inputbuffer
FETCH NEXT FROM cur_Memory_Hogs into @my_spid
END
--our goal, insert hoggers into the memory_hogs table
INSERT INTO memory_hogs (spid, dbid, dbname, cpu, physical_io,[memusage], statement, program_name,date_inserted)
SELECT spid, dbid, dbname, cpu, physical_io, [memusage], statement, program_name,getdate() FROM #memory_hogs
CLOSE cur_Memory_Hogs
DEALLOCATE cur_Memory_Hogs
DELETE FROM #memory_hogs
DELETE FROM #inputbuffer
--TEST FOR SYSPROCESS OVER OUR ALERT THRESHOLD
SELECT @HOG_COUNT = COUNT(spid)
FROM master.dbo.sysprocesses
WHERE memusage > @alert_memory_threshold
IF @HOG_COUNT > 0
BEGIN
SET @my_spid = NULL
INSERT #memory_hogs
SELECT spid,dbid,db_name(dbid) as dbname,cpu,physical_io,memusage,'',program_name
FROM master.dbo.sysprocesses
WHERE memusage > @alert_memory_threshold
DECLARE cur_Memory_Hogs CURSOR FAST_FORWARD
FOR
SELECT spid
FROM master.dbo.sysprocesses
WHERE memusage > @alert_memory_threshold
set @message = '<strong><span style=''font-family: arial, "lucida console", sans-serif''>' + @@SERVERNAME + ':These processes are using more than ' + convert(varchar(20),@alert_memory_threshold) +' pages of memory space </span></strong>'
OPEN cur_Memory_Hogs
FETCH NEXT FROM cur_Memory_Hogs into @my_spid
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #inputbuffer
EXEC ('DBCC INPUTBUFFER (' + @my_spid + ') WITH NO_INFOMSGS')
UPDATE #memory_hogs
SET statement = (SELECT eventinfo FROM #inputbuffer)
WHERE spid = @my_spid
SELECT @newmessage =
'<span style=''font-family: arial, "lucida console", sans-serif ; font-size:10px; ''>' +
'<p>' +
'<strong>spid:</strong> ' + coalesce(convert(varchar(50),spid),'') + '<br>' +
'<strong>Memory Usage: </strong>' + coalesce(convert(varchar(50),[memusage]),'') + ' 8k pages / ' + coalesce(convert(varchar(50),convert(decimal(20,2),round([memusage] * 8 * .0009765625 , 2))),'') + ' mb<br>' +
'<strong>Program Name:</strong> ' + coalesce(convert(varchar(50),program_name),'') + '<br>' +
'<strong>DB ID:</strong> ' + coalesce(convert(varchar(50), dbid),'') + '<br>' +
'<strong>DB Name:</strong> ' + coalesce(convert(varchar(50),dbname),'') + '<br>' +
'<strong>CPU:</strong> ' + coalesce(convert(varchar(50),cpu),'') + '<br>' +
'<strong>IO:</strong> ' + coalesce(convert(varchar(50),physical_io),'') + '<br>' +
'<strong>Statement:</strong> ' + coalesce(convert(varchar(2000),statement),'') + '<br>' +
'</p>' +
'</span>'
from #memory_hogs where spid = @my_spid
PRINT @newmessage + @message
set @message = @message + @newmessage
TRUNCATE TABLE #inputbuffer
FETCH NEXT FROM cur_Memory_Hogs into @my_spid
END
--select * from #inputbuffer
--select * from #memory_hogs
SELECT * FROM #memory_hogs
EXECUTE master.dbo.SMTP_Server_Set @pager = 1, @SMTPServer = @SMTP_Server OUTPUT
EXEC @MailStatus = master.dbo.xp_smtp_sendmail
@FROM = 'sqldba@xxx.org',
@FROM_NAME = 'MSSQL01 Job Alert',
@TO = 'sqldba@xxx.org',
@cc = '',
@BCC = '',
@subject = 'Memory Hog in progress',
@message = @message ,
@type = N'text/html',
@server = @SMTP_Server
CLOSE cur_Memory_Hogs
DEALLOCATE cur_Memory_Hogs
DROP TABLE #memory_hogs
DROP TABLE #inputbuffer
--END ALERT BLOCK
END
ELSE
BEGIN
--CLEANUP OBJECTS FROM LOGGING OPERATION
DROP TABLE #memory_hogs
DROP TABLE #inputbuffer
END
--END LOGGING BLOCK
END
GO
-
Thank you all for your feedback!
Sam - Thanks for your Code...
I just want to see the space used by a process or session against TempDB space.
i.e. For example. TempDB space used is 50MB, I need to see the split of each process occupied the TempDB space like as follows
SPID Used Space
1 10 MB
2 5 MB
3 25 MB
5 10 MB
....
Is Sql server maintains any log report like this for TempDB space used by each Process or Session (SPID)?
It is like viewing the TempDB usage by each process...Who is using more space?
Please help me in this?
Thanks in Advance!
- Anbu
-
Anbu,
as far as I know it's not possible to query this in SQL 2000. In 2005 you can use the DMV sys.dm_db_session_space_usage. Something like the following should work:
use tempdb
SELECT session_id,(user_objects_alloc_page_count + internal_objects_alloc_page_count)* 8 as kb_used
FROM sys.dm_db_session_space_usage
Markus
[font="Verdana"]Markus Bohse[/font]
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply