How many connection ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How many connection ?

Author

Topic

pamyral_279
Posting Yak Master

143 Posts

Posted - 2006-07-26 : 01:57:01

Are there ways to display how many connection to my database at this time ?
Any store procedure,sql command ?
Thank you very much ?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-07-26 : 02:34:42

Try this:

EXEC sp_who

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

pamyral_279
Posting Yak Master

143 Posts

Posted - 2006-07-26 : 04:40:15

My problem :
I build my web application with many users access at the same time.
I want to know how many connection accessing at the this time .
How performance are effect ? and so on .Many more ...?
Thank you .

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-07-26 : 06:38:48

Perfmon is a nice tool. Look in Windows Help for it.

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-26 : 06:46:49

Add this sp to master
http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html

It will show you what is executing and what is blocking and the commands executed.
Check the diskio for things that are doing a lot o work.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pamyral_279
Posting Yak Master

143 Posts

Posted - 2006-07-27 : 05:09:42

Thank all !
Useful for me !
But your link die ! Check again !
Thank you very much

quote:
Originally posted by nr

Add this sp to master
http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html

It will show you what is executing and what is blocking and the commands executed.
Check the diskio for things that are doing a lot o work.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-27 : 05:33:16

If you're talking about mine then it's ok for me.
Try copying it to another browser window.
If it still doesn't work try
www.nigelrivett.net
www.mindsdoor.net
www.nigelrivett.com
www.nigelrivett.co.uk

and search for nrinfo or look under the admin section.
If it still fails here's the code


if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_nrinfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_nrinfo]
GO

Create procedure sp_nrInfo
@status varchar(20) = 'active' ,
@type   varchar(10) = 'info'
as
/*
exec sp_nrSpidByStatus			-- all spids whith status runnable
exec sp_nrSpidByStatus 'sleeping'	-- all spids whith status sleeping
exec sp_nrSpidByStatus 'background'	-- all spids whith status background
exec sp_nrSpidByStatus 'sleeping'	-- all spids whith status sleeping
exec sp_nrSpidByStatus 'all'		-- all spids
exec sp_nrSpidByStatus 'blk'		-- all blocked or blocking spids
exec sp_nrSpidByStatus '74'		-- an individual spid - also gives subthreads
select * from master..sysprocesses where spid = 56
*/
set transaction isolation level read uncommitted
set nocount on
-- Get blocking
declare @blkspid varchar(20)

select top 1 @blkspid = convert(varchar(20),spid) from master..sysprocesses 
where spid in (select blocked from master..sysprocesses)
order by blocked

if @blkspid is not null
begin
	-- this is all that will appear on the intranet
	select 	[blocking spid] = spid ,
		last_batch = last_batch ,
		hostname = left(hostname,14) ,
		program_name = left(program_name,20) ,
		cmd = cmd ,
		physical_io = physical_io
	from master..sysprocesses
	where spid = @blkspid
	
	exec ('dbcc inputbuffer (' + @blkspid + ')')
	
	print  'blocking spid command'
	DECLARE @sql_handle binary(20)
	SELECT	@sql_handle = sql_handle
			FROM master.dbo.sysprocesses
			WHERE	spid = @blkspid
				AND ecid = 0
  			SELECT *
   			FROM ::fn_get_sql(@sql_handle)
	
	print 'sp_who2 result for blocking spid'
	exec ('sp_who2 ' + @blkspid) 
	
	print 'spids that are being blocked'
	select spid, blocked from master..sysprocesses 
	where spid in (select blocked from master..sysprocesses)
	order by blocked
end

declare	@cmd varchar(1000)

declare @buf varchar(1000) ,
	@id int ,
	@spid int ,
	@maxSpid int
	create table #spid (spid int, command varchar(1000) null)
	create table #temp (x varchar(100), y int, s varchar(1000), id int identity (1,1))
	create table #spids (spid int)
	if isnumeric(@status) = 1
	begin
		insert #spids select @status
	end
	else if @status = 'blk'
	begin
		insert	#spids 
		select spid from master..sysprocesses where blocked <> 0
		union
		select blocked from master..sysprocesses where blocked <> 0
	end
	else if @status = 'active'
		insert	#spids 
		select 	distinct spid 
		from 	master..sysprocesses 
		where 	blocked <> 0
		or	upper(cmd)    not in 	(
				                     'AWAITING COMMAND'
				                    ,'MIRROR HANDLER'
				                    ,'LAZY WRITER'
				                    ,'CHECKPOINT SLEEP'
				                    ,'RA MANAGER'
						)
		or lower(status) <> 'sleeping'
	else
	begin
		insert	#spids select spid from master..sysprocesses where (status = @status or @status = 'all') and ecid = 0
	end
	select 	@spid = 0 ,
		@maxSpid = max(spid)
	from	#spids
	
	if @type = 'info'
	begin
		while @spid < @maxSpid
		begin
			select	@spid = min(spid) from #spids where spid > @spid
			
			select @cmd = 'dbcc inputbuffer (' + convert(varchar(10),@spid) + ')'
			
			delete #temp
			
			insert #temp
			exec (@cmd)
			
			select 	@id = 0 ,
				@buf = ''
			select @buf = @buf + replace(replace(s,char(10),'|'),char(13),'|')
	
			from #temp
	
			insert 	#spid
			select	@spid, @buf
		end
	end
	else
	begin
		insert	#spid select spid, '' from #spids
	end
	
	select 	spid 		= convert(varchar(4),#spid.spid) ,
		status 		= min(left(case when s.status = 'Runnable' then 'aRunnable' else 'z' + s.status end ,12)) ,
		threads		= count(*) ,
		loginame 	= min(left(s.loginame, 25)) ,
		hostname	= min(left (s.hostname, 14)) ,
		BlkBy		= max(case when s.blocked <> 0 then convert(varchar(3),s.blocked) else '   ' end) ,
		DBName		= left(db_name(min(dbid)), 10) ,
		command		= min(left(s.cmd, 30)) ,
		CPUTime		= sum(s.cpu) ,
		DiskIO		= sum(s.physical_io) ,
		LastBatch	= max(convert(varchar(23),s.last_batch,121)) ,
		ProgramName	= min(left(s.program_name, 30)) ,
		spid		= convert(varchar(4),#spid.spid) ,
		buffercmd	= max(#spid.command)
	from	#spid ,
		master..sysprocesses s
	where	s.spid = #spid.spid
	group by #spid.spid
	order by convert(int,#spid.spid) desc

	drop table #spid
	drop table #temp

go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pamyral_279
Posting Yak Master

143 Posts

Posted - 2006-07-27 : 23:24:23

I follow your guide !
But still errors when i run store procedure:
Errors are :

Server: Msg 207, Level 16, State 3, Procedure sp_nrInfo, Line 41
Invalid column name 'sql_handle'.
Server: Msg 195, Level 15, State 1, Procedure sp_nrInfo, Line 45
'fn_get_sql' is not a recognized function name.
Server: Msg 208, Level 16, State 11, Line 1
Invalid object name 'sp_nrinfo'.
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'sp_nrinfo'. The stored procedure will still be created.

Review for me ! Thanks again !

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-28 : 05:07:39

Which version of sql server are you using?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-28 : 05:09:38

I suspect you are v7 - try this instead


if exists (select * from sysobjects where id = object_id(N'[dbo].[sp_nrinfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_nrinfo]
GO

Create procedure sp_nrInfo
@status varchar(20) = 'active' ,
@type   varchar(10) = 'info'
as
/*
exec sp_nrSpidByStatus			-- all spids whith status runnable
exec sp_nrSpidByStatus 'sleeping'	-- all spids whith status sleeping
exec sp_nrSpidByStatus 'background'	-- all spids whith status background
exec sp_nrSpidByStatus 'sleeping'	-- all spids whith status sleeping
exec sp_nrSpidByStatus 'all'		-- all spids
exec sp_nrSpidByStatus 'blk'		-- all blocked or blocking spids
exec sp_nrSpidByStatus '74'		-- an individual spid - also gives subthreads
select * from master..sysprocesses where spid = 56
*/
set transaction isolation level read uncommitted
set nocount on
-- Get blocking
declare @blkspid varchar(20)

select top 1 @blkspid = convert(varchar(20),spid) from master..sysprocesses 
where spid in (select blocked from master..sysprocesses)
order by blocked

if @blkspid is not null
begin
	-- this is all that will appear on the intranet
	select 	[blocking spid] = spid ,
		last_batch = last_batch ,
		hostname = left(hostname,14) ,
		program_name = left(program_name,20) ,
		cmd = cmd ,
		physical_io = physical_io
	from master..sysprocesses
	where spid = @blkspid
	
	exec ('dbcc inputbuffer (' + @blkspid + ')')
	
	print 'sp_who2 result for blocking spid'
	exec ('sp_who2 ' + @blkspid) 
	
	print 'spids that are being blocked'
	select spid, blocked from master..sysprocesses 
	where spid in (select blocked from master..sysprocesses)
	order by blocked
end

declare	@cmd varchar(1000)

declare @buf varchar(1000) ,
	@id int ,
	@spid int ,
	@maxSpid int
	create table #spid (spid int, command varchar(1000) null)
	create table #temp (x varchar(100), y int, s varchar(1000), id int identity (1,1))
	create table #spids (spid int)
	if isnumeric(@status) = 1
	begin
		insert #spids select @status
	end
	else if @status = 'blk'
	begin
		insert	#spids 
		select spid from master..sysprocesses where blocked <> 0
		union
		select blocked from master..sysprocesses where blocked <> 0
	end
	else if @status = 'active'
		insert	#spids 
		select 	distinct spid 
		from 	master..sysprocesses 
		where 	blocked <> 0
		or	upper(cmd)    not in 	(
				                     'AWAITING COMMAND'
				                    ,'MIRROR HANDLER'
				                    ,'LAZY WRITER'
				                    ,'CHECKPOINT SLEEP'
				                    ,'RA MANAGER'
						)
		or lower(status) <> 'sleeping'
	else
	begin
		insert	#spids select spid from master..sysprocesses where (status = @status or @status = 'all') and ecid = 0
	end
	select 	@spid = 0 ,
		@maxSpid = max(spid)
	from	#spids
	
	if @type = 'info'
	begin
		while @spid < @maxSpid
		begin
			select	@spid = min(spid) from #spids where spid > @spid
			
			select @cmd = 'dbcc inputbuffer (' + convert(varchar(10),@spid) + ')'
			
			delete #temp
			
			insert #temp
			exec (@cmd)
			
			select 	@id = 0 ,
				@buf = ''
			select @buf = @buf + replace(replace(s,char(10),'|'),char(13),'|')
	
			from #temp
	
			insert 	#spid
			select	@spid, @buf
		end
	end
	else
	begin
		insert	#spid select spid, '' from #spids
	end
	
	select 	spid 		= convert(varchar(4),#spid.spid) ,
		status 		= min(left(case when s.status = 'Runnable' then 'aRunnable' else 'z' + s.status end ,12)) ,
		threads		= count(*) ,
		loginame 	= min(left(s.loginame, 25)) ,
		hostname	= min(left (s.hostname, 14)) ,
		BlkBy		= max(case when s.blocked <> 0 then convert(varchar(3),s.blocked) else '   ' end) ,
		DBName		= left(db_name(min(dbid)), 10) ,
		command		= min(left(s.cmd, 30)) ,
		CPUTime		= sum(s.cpu) ,
		DiskIO		= sum(s.physical_io) ,
		LastBatch	= max(convert(varchar(23),s.last_batch,121)) ,
		ProgramName	= min(left(s.program_name, 30)) ,
		spid		= convert(varchar(4),#spid.spid) ,
		buffercmd	= max(#spid.command)
	from	#spid ,
		master..sysprocesses s
	where	s.spid = #spid.spid
	group by #spid.spid
	order by convert(int,#spid.spid) desc

	drop table #spid
	drop table #temp

go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources