November 20, 2009 at 6:39 am
Tim, it was definitely of interest to me, as although I've heard of a couple of these, I've not seen listed exactly what they are/what they do, and as with anything else, wouldn't know to Google it (Bing it?) unless I knew what I was looking for.
Thanks for the article!
Jon
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 20, 2009 at 6:42 am
Good job Timothy. I've heard of most of these, but a nice reminder of what they do and how they work. Easy to forget them if you don't work with them regularly.
I'm sure lots of people have never heard of these commands
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 20, 2009 at 6:45 am
I made a few changes to the formatting, so the article should read better now.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 20, 2009 at 7:39 am
mmendoza (5/9/2008)
Steve Eckhart (5/9/2008)
Several of the examples were unreadable, displaying only a horizontal scroll bar. What might have been the example code was in a window which appeared to be 1 or 2 pixels tall.I was able to work around this glitch by printing the article to PDF. The code listings are printed inline with the article.
I agree with Steve that this UI bug should be fixed.
This is what helped me to fix the problem also!
November 20, 2009 at 8:39 am
The following is a much more flexible and much more useful version of sp_who/2 that I have been using for a number of years.
/* Author: Richard Ding
** Creation Date: 10/10/2004
** Version: 1.0.0
*/
CREATE procedure [dbo].[sp_who1]
@spidpool varchar(500) = null,
@status sysname = null,
@loginame sysname = null,
@command sysname = null,
@dbname sysname = null,
@hostname sysname = null,
@waittime int = null,
@lastbatch datetime = null,
@program sysname = null,
@opentran int = null,
@blocked bit = null
as
set nocount on
declare -- search argument in the where clause
@select varchar(2000),
@SARG_spid varchar(20),
@SARG_status varchar(40),
@SARG_loginame varchar(150),
@SARG_command varchar(40),
@SARG_dbname varchar(150),
@SARG_hostname varchar(150),
@SARG_waittime varchar(80),
@SARG_lastbatch varchar(50),
@SARG_program varchar(150),
@SARG_opentran varchar(20),
@SARG_blocked varchar(30),
@SARG_all varchar(8000),
@order_by_clause varchar(100),
@header varchar(500),
@where varchar(10),
@total_users int,
@total_runnables int,
-- define maximum column length for dynamic adjustment
@max_spid varchar(5),
@max_status varchar(5),
@max_loginame varchar(5),
@max_dbname varchar(5),
@max_command varchar(5),
@max_hostname varchar(5),
@max_memusage varchar(5),
@max_physical_io varchar(5),
@max_program_name varchar(5),
@max_cpu varchar(5),
@max_opentran varchar(5),
@max_blocked varchar(5),
@max_waittime varchar(5),
@max_lastwaittype varchar(5),
@max_waitresource varchar(5),
-- this piece of T-SQL checks the validity of input spids pool
@SARG_spidpool varchar(300),
@single_spid varchar(20),
@comma_position tinyint,
@error varchar(100),
@spidpoollength int
set @spidpool = ltrim(rtrim(replace(@spidpool, ' ', '')))
set @spidpoollength = len(@spidpool)
set @SARG_spidpool = @spidpool
if @spidpool is not null -- user provided one or more spids
begin
if patindex('%[^0-9 ,]%', @spidpool) = 0 -- clean pool, only numeric, space and comma allowed
begin
while @spidpoollength > 0
begin
set @comma_position = charindex(',', @spidpool)
if @comma_position = 0-- at the last spid
set @comma_position = @spidpoollength+1
set @single_spid = substring(@spidpool, 1, @comma_position-1)
if convert(int, @single_spid) not between 0 and 32767
begin
set @error = 'spid ' + @single_spid + ' out of range. Valid spids are integers between 0
and 32767'
raiserror (@error, 16, 1)
return (1)
end
if charindex(',', @spidpool) = 0
set @spidpoollength = 0
else
begin
set @spidpool = substring(@spidpool, @comma_position+1, len(@spidpool)-
@comma_position)
set @spidpoollength = len(@spidpool)
end
end
end
else
begin
raiserror ('invalid character(s) in spid pool. Only numeric, space and comma allowed.',
16, 1)
return (1)
end
end
-- Make sure login name is existing
if (@loginame is not null)
begin
if not exists (select 1 from master.dbo.syslogins with (nolock) where name = @loginame)
begin
raiserror(15007, -1, -1, @loginame)
return (1)
end
end
-- check if database is existing
if (@dbname is not null)
begin
if not exists (select name from master.dbo.sysdatabases with (nolock) where name =
@dbname)
begin
raiserror (15010, -1, -1, @dbname)
return (1)
end
end
if object_id('tempdb..##TmpSysprocesses') is null
begin
create table ##TmpSysprocesses -- hold critical info and minimize performance hit on sys
( spid smallint,
status nchar(30),
loginame nchar(128),
dbname nchar(128),
command nchar(16),
hostname nchar(128),
memusg int,
phys_io int,
login_time datetime,
last_batch datetime,
program nchar(128),
cpu int,
blkBy smallint,
open_tran smallint,
waittype binary(2),
waittime int,
lastwaittype nchar(32),
waitresource nchar(512) )
create clustered index clust on ##TmpSysprocesses (spid)
-- create nonclustered index nclust on ##TmpSysprocesses (status, loginame, dbname,
-- command, hostname, last_batch, waittime, open_tran)
end
else
truncate table ##TmpSysprocesses
insert into ##TmpSysprocesses
select spid, status, loginame, db_name(dbid), cmd, hostname, memusage, physical_io,
login_time,
last_batch, program_name, cpu, blocked, open_tran, waittype, waittime, lastwaittype,
waitresource
from master.dbo.sysprocesses with (nolock)
select
@max_spid = max(len(ltrim(str(spid)))),
@max_status = ltrim(str(max(len(status)))),
@max_loginame = ltrim(str(max(len(loginame)))),
@max_dbname = ltrim(str(max(len(dbname)))),
@max_command = ltrim(str(max(len(command)))),
@max_hostname = ltrim(str(max(len(hostname)))),
@max_memusage = max(len(ltrim(str(memusg)))),
@max_physical_io = max(len(ltrim(str(phys_io)))),
@max_program_name = ltrim(str(max(len(program)))),
@max_cpu = max(len(ltrim(str(cpu)))),
@max_opentran = max(len(ltrim(str(open_tran)))),
@max_blocked = max(len(ltrim(str(blkBy)))),
@max_waittime = max(len(ltrim(str(waittime)))),
@max_lastwaittype = ltrim(str(max(len(lastwaittype)))),
@max_waitresource = ltrim(str(max(len(waitresource)))) from ##TmpSysprocesses
select @total_users = count(spid) from ##TmpSysprocesses
select @total_runnables = count(spid) from ##TmpSysprocesses where status = 'runnable'
set @header = '*** sp_who1 at ' + substring(convert(varchar(30), getdate(), 9), 1, 20) + space(1)
+
substring(convert(varchar(30), getdate(), 9), 25, 2) + space(5) + 'Server: ' +
upper(@@servername) +
space (5) + 'Total of spids: ' + ltrim(str(@total_users)) + space(5) +
'Total of runnables: ' + ltrim(str(@total_runnables)) + ' ***'+ char(10)
-- When blocking occurs
declare @block_flag bit
set @block_flag = 0
if (@blocked is not null)
begin
if exists (select 1 from ##TmpSysprocesses where blkBy > 0) -- there is blocking
begin
set @block_flag = 1
if object_id ('tempdb..##TmpBlockingSpid') is null
create table ##TmpBlockingSpid (spid int)
else
truncate table ##TmpBlockingSpid
insert into ##TmpBlockingSpid -- save head spid of blocking chain
select distinct spid from ##TmpSysprocesses
where blkBy = 0 and spid in (select blkBy from ##TmpSysprocesses)
print @header
print ''
print 'Head(s) of blocking chain is(are):' + char(13) + char(9)
select distinct spid from ##TmpBlockingSpid order by spid asc
print ''
end
end
select
@SARG_spidpool = case when @spidpool is null then ''
else ' and spid in (' + @SARG_spidpool + ')' end,
@SARG_status = case when @status is null then ''
else ' and status = ''' + @status + '''' end,
@SARG_loginame = case when @loginame is null then ''
else ' and loginame = ''' + @loginame + '''' end,
@SARG_command = case when @command is null then ''
else ' and command = ''' + @command + '''' end,
@SARG_dbname = case when @dbname is null then ''
else ' and dbname = ''' + @dbname + '''' end,
@SARG_hostname = case when @hostname is null then ''
else ' and hostname = ''' + @hostname + '''' end,
@SARG_waittime = case when @waittime is null then ''
else ' and waittype > 0x0000 and waittime > ' + ltrim(str(@waittime)) + '' end,
@SARG_lastbatch = case when @lastbatch is null then ''
else ' and last_batch >= ''' + convert(varchar(30), @lastbatch) + '''' end,
@SARG_program = case when @program is null then ''
else ' and program = ''' + @program + '''' end,
@SARG_opentran = case when @opentran is null then ''
else ' and open_tran >= ' + ltrim(str(@opentran)) + '' end,
@SARG_blocked = case when @blocked is null then ''
when @blocked = 0 then ' and blkBy = 0'
else ' and blkBy > 0' end
select @SARG_all = @SARG_spidpool + @SARG_status + @SARG_loginame +
@SARG_command +
@SARG_dbname + @SARG_hostname + @SARG_waittime + @SARG_lastbatch +
@SARG_program + @SARG_opentran + @SARG_blocked
if len(@SARG_all) = 0
set @where = ''
else
set @where = ' where '
select @SARG_all = substring(@SARG_all, 6, len(@SARG_all))
set @select = 'select
left(spid, ' + @max_spid + ') as ''SPID'',
left(status, ' + @max_status + ') AS ''status'',
left(loginame, ' + @max_loginame + ') AS ''loginame'',
left(dbname, ' + @max_dbname + ') AS ''dbname'',
left(command, ' + @max_command + ') as ''command'',
left(hostname, ' + @max_hostname + ') as ''hostname'',
left(memusg, ' + @max_memusage + ') as ''memusg'',
left(phys_io, ' + @max_physical_io + ') as ''phys_io'',
left(substring(convert(varchar(25), login_time, 101), 1, 10) +
'' '' + convert(varchar(25), login_time, 8), 20) as ''login_time'',
left(substring(convert(varchar(25), last_batch, 101), 1, 10) +
'' '' + convert(varchar(25), last_batch, 8), 20) as ''last_batch'',
left(spid, ' + @max_spid + ') as ''SPID'',
left(program, ' + @max_program_name + ') as ''program'',
left(cpu, ' + @max_cpu + ') as ''cpu'',
left(open_tran, ' + @max_opentran + ') as ''opentran'',
left(blkBy, ' + @max_blocked + ') as ''blkBy'',
left(waittime, ' + @max_waittime + ') as ''waittime'',
left(lastwaittype, ' + @max_lastwaittype + ') as ''lastwaittype'',
left(waitresource, ' + @max_waitresource + ') as ''waitresource'',
left(spid, ' + @max_spid + ') as ''SPID'' from ##TmpSysprocesses '
set @order_by_clause = ' order by dbname asc, loginame asc, status asc, command asc'
print ''
if @block_flag <> 1
print @header
exec (@select + @where + @SARG_all + @order_by_clause)
return (0)
November 20, 2009 at 10:09 am
Steve Jones - Editor (11/20/2009)
I made a few changes to the formatting, so the article should read better now.
Thank you. The changes are not very noticeable in FireFox to me, but it looks much better in IE now.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
November 20, 2009 at 10:28 am
JStiney, that is an excellent procedure. Thank you.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
November 20, 2009 at 11:50 am
November 20, 2009 at 12:44 pm
Boo Birds are out for the posters bashing the author's article being to simplistic. We have to remember as SQL keeps stealing marketshare there are thousands of new DBA's every year who this is very valuable for. Just think of a the new DBA reading for the first time that MS can just yank away code without warning. That was a lesson for me 10 years ago. An article like this would have been a life saver.
I rated this a petaflop worth of stars for its tone and importance.
November 20, 2009 at 4:31 pm
The point has been made that many DBAs know these procs and have used them for years.
Great, so why doesn't Microsoft include them in BOL?
They are stable and have survived a number of SQL Server editions and are generically useful.
XP_DirTree and XP_FixedDrives are quite useful as well!
November 20, 2009 at 5:54 pm
There extended so there married to languages that may not want to continue along into future releases.
November 23, 2009 at 4:34 am
JStiney (11/20/2009)
The following is a much more flexible and much more useful version of sp_who/2 that I have been using for a number of years./* Author: Richard Ding
** Creation Date: 10/10/2004
** Version: 1.0.0
*/
if object_id('tempdb..##TmpSysprocesses') is null
begin
create table ##TmpSysprocesses -- hold critical info and minimize performance hit on sys
( spid smallint,status nchar(30),loginame nchar(128),dbname nchar(128),command nchar(16),hostname nchar(128),memusg int,
phys_io int,login_time datetime,last_batch datetime,program nchar(128),cpu int,blkBy smallint,open_tran smallint,waittype binary(2),
waittime int,lastwaittype nchar(32),waitresource nchar(512) )
create clustered index clust on ##TmpSysprocesses (spid)
-- create nonclustered index nclust on ##TmpSysprocesses (status, loginame, dbname,
-- command, hostname, last_batch, waittime, open_tran)
end
else
truncate table ##TmpSysprocesses
Maybe we need to reboot our servers more often, but I had to set ##TmpSysprocesses.waittime to a bigint to get this code to run as there were several entries over 13,000,000,000
--
Scott
November 23, 2009 at 9:00 pm
November 24, 2009 at 9:51 am
einman33 (11/20/2009)
There extended so there married to languages that may not want to continue along into future releases.
Some extended procedures are documented and those are very likely to remain supported for a very long time.
The Undocumented stored procedures are distinct not supported; however, many of them are highly useful and have remained available for several successive releases now.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
November 28, 2009 at 1:12 pm
Great job Tim, very useful stuff!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 31 through 45 (of 51 total)
You must be logged in to reply to this topic. Login to reply