SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How many connection ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pamyral_279
Posting Yak Master

134 Posts

Posted - 07/26/2006 :  01:57:01  Show Profile  Reply with Quote
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

Norway
3271 Posts

Posted - 07/26/2006 :  02:34:42  Show Profile  Reply with Quote
Try this:

EXEC sp_who

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

pamyral_279
Posting Yak Master

134 Posts

Posted - 07/26/2006 :  04:40:15  Show Profile  Reply with Quote
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 .
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 07/26/2006 :  06:38:48  Show Profile  Reply with Quote
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)
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 07/26/2006 :  06:46:49  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

pamyral_279
Posting Yak Master

134 Posts

Posted - 07/27/2006 :  05:09:42  Show Profile  Reply with Quote
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.

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 07/27/2006 :  05:33:16  Show Profile  Visit nr's Homepage  Reply with Quote
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.

Edited by - nr on 07/27/2006 05:34:48
Go to Top of Page

pamyral_279
Posting Yak Master

134 Posts

Posted - 07/27/2006 :  23:24:23  Show Profile  Reply with Quote
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 !

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 07/28/2006 :  05:07:39  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 07/28/2006 :  05:09:38  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000