Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»»

Undocumented Extended and Stored Procedures Expand / Collapse
Author
Message
Posted Friday, November 20, 2009 6:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:02 AM
Points: 2,668, Visits: 19,229
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
How to post performance problems
Tally Table:What it is and how it replaces a loop

"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."
Post #822333
Posted Friday, November 20, 2009 6:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 9:09 AM
Points: 33,169, Visits: 15,301
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: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #822334
Posted Friday, November 20, 2009 6:45 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 9:09 AM
Points: 33,169, Visits: 15,301
I made a few changes to the formatting, so the article should read better now.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #822340
Posted Friday, November 20, 2009 7:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 20, 2009 7:37 AM
Points: 1, Visits: 0
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!


James
Post #822402
Posted Friday, November 20, 2009 8:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 2, 2012 7:05 AM
Points: 75, Visits: 446
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)








Post #822465
Posted Friday, November 20, 2009 10:09 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
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/
Post #822531
Posted Friday, November 20, 2009 10:28 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
JStiney, that is an excellent procedure. Thank you.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #822553
Posted Friday, November 20, 2009 11:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 20, 2009 11:48 AM
Points: 1, Visits: 0
someone tried to argue this point with me. I am glad that I could use this to prove him wrong.

Susan
Post #822596
Posted Friday, November 20, 2009 12:44 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 8:14 AM
Points: 567, Visits: 512
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.
Post #822649
Posted Friday, November 20, 2009 4:31 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 18, 2014 1:30 AM
Points: 2,898, Visits: 1,795
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!


LinkedIn Profile
Newbie on www.simple-talk.com
Post #822781
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse