What's Hot in Your Query
Analyzer?
by Bill
WunderHopefully, you have the database developers in your
shop working from Query Analyzer as much as possible rather than
slogging around in Enterprise Manager. The primary advantage to
developing with Query Analyzer over Enterprise Manager is that
with Query Analyzer you create scripts that can be tested and
reused while with Enterprise manager you complete linear
procedural changes to your system that can as easily be
mal-reproduced as duplicated with no way to know which
possibility happened until the disaster has begun. With Query
Analyzer you can foster stability and managed change. With
Enterprise Manager you fly by the seat of your pants.
It's almost deviously clever that the one tool would be named
Enterprise Manager. The name gives you the sense that this is
THE tool for administration and big picture management of your
SQL Servers. The realities of Enterprise Manager are actually
quite distant from that illusion. In my experience Enterprise
Manager crashes much more often than Query Analyzer. I can't
tell you how many times a mouse click in Enterprise Manager has
resulted in an error message pop-up on my workstation. In
practice there are very few features of Enterprise Manager that
compel me to use the tool. In fact, it's usually a simple matter
of egoistic resistance to having to look up syntax that finds me
clicking around in Enterprise Manager if at all.
In case you hadn't noticed, Enterprise Manager can be quite
slow as well. Even when it's behaving as intended.
Query Analyzer on the other hand is somewhat understated in
name. The name gives an indication that the tool takes care of
some small part of the SQL Server subsystems: namely queries. As
you and I know the great majority of management,
troubleshooting, monitoring, and administration necessary in SQL
Server is query based. And who hasn't had the experience of
Query Analyzer getting into the wrong hands and having some
misbegotten query bring a server to its knees? Without a doubt,
Query Analyzer is the workhorse of the SQL Server Client
Utilities suite.
Where the capabilities of Enterprise Manager and Query
Analyzer overlap - with rare exception - Query Analyzer provides
a more elegant interface. You can create DDL in Enterprise
Manager or you can use a script in Query Analyzer. The script
can be based on a template that can add common form to all
scripts in the shop. Who would argue that when scripts look the
same they are easier to maintain? If you edit a stored procedure
in Enterprise Manager you are stuck with an ALTER PROCEDURE
operation and there is difficulty to identify when the change
was made. If you use Query Analyzer you can drop the existing
stored procedure and recreate it thus allowing a date stamp to
reflect that the stored procedure has been changed. And the
changed date is observable even by Enterprise Manager users. On
and on I could go about how using scripts built in Query
Analyzer - and saved in a source control repository - is going
to bring benefits to your shop.
Once you get in the habit of using Query Analyzer it's good
to know that there are a number of shortcuts available that can
make the tool even more efficient and friendly. There are over
50 built-in short cuts that can assist you with editing,
navigation, tool access, and otherwise ease the usability of
Query Analyzer. These shortcuts are well documented in Books
Online. I'm just going to trust here that if you are a short-cut
oriented person you will check them out. (see Book Online
keywords: shortcut keys)
In addition to the tools useful assortment of built-in
hotkeys there are three built-in and 9 customizable query
execution hot keys that you can use to run a query without
having to open a new query window or loose any work. In fact
these query shortcuts can actually exploit what you have in your
query window or even your results window. For example one of the
built-in queries executes sp_help when you hit ALT+F1.
So if you were working on a stored procedure in the query
window and needed to see the structure of a table in the
procedure you could simple select the table, hit ALT+F1
and you could review the table definition as described by
sp_help in your results window. Let's take that example one
step farther. If you defined one of the customizable shortcuts,
such as the one available as ALT+4 to be sp_helptext
and the procedure you were working on called a stored procedure,
you could simply highlight the called procedure's name, hit
ALT+4 and the text of the nested procedure would be
available for your review in the results pane. And if that
called procedure contained an inline function you that you also
wished to review, you could highlight the function name - in the
results pane! - hit ALT+4 and you'd get the text of that
function in the result pane. Pretty powerful and a very quick
research technique!
Of course, in the example you could also have hit the
built-in hotkey F8 to bring up the object browser and
navigated around to see the same things, but not without a few
moments of waiting... waiting... waiting... Often not all that
dissimilar from the long pauses so familiar when using
Enterprise Manager. This is because the object browser gets you
back into the business of having to populate whole collections
of the DMO object model on your workstation just to see one item
in that collection in much the same way Enterprise Manager works
under the covers. I'm not saying don't use the Object Browser
(And I'm not unequivocally saying don't use Enterprise Manager
either for that matter). I think the Object Browser has a useful
place. For example you can drag table names and column lists
into the query window from the object browser to ease the
construction of well built queries - as opposed to those nasty
but easy to type select * from ... constructs. I'm just trying
to point out that often it is faster and therefore helpful to
maintain a train of thought if you use a few well selected query
shortcuts. The trick is to get the right 9 custom queries in
those short cuts to best suit your needs.
You get nine custom query shortcuts. You can add them from
the "Tools" menu by selecting "Customize...". You can add system
stored procedures, T-SQL statements, and even your own stored
procedures. Any T-SQL Statements can't be grossly complex
because the input box for each query will only accept 256
characters. One unfortunate feature is that if you add your own
queries or stored procedures you cannot use bits select from the
query or results panes as parameters like you can with the
built-in sp_who (CTL+1) or you can with most added system
stored procedure such as sp_helptext or sp_who2.
Now after all that explanation I'd like to share with you
what I keep in the customizable shortcuts of my query Analyzer.
I'm so dependent on these shortcuts that I even had to add them
to the Query Analyzer on the Citrix server.
CTL+F1 exec admin.dbo.ActiveSpids
This one lets me get right to the source of a problem when
ever the developer sneaker net provides an indication that there
something wrong on a server in the moment. This works kind of
like "Current Activity" in Enterprise Manager or sp_who2 only
much more direct in revealing what is happening on the server. I
get only active and blocked processes in the result set plus I
get to see the input buffer and currently executing statement
for each spid as well as the locking details of the blocking
chain top pin. You can find out more about this procedure in my
sswug.org article Current Activity from
Query Analyzer and you can download the
script to create the stored procedure ActiveSPIDs from the
sswug.com
Script Library. Note that the script is updated from the
article to include the ::fn_get_sql data made available
in SQL Server 2000 SP3a
use admin
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[activeSPIDs]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ActiveSPIDs]
GO
create proc dbo.ActiveSPIDs
as
/*******************************************************************************************************
* admin.dbo.ActiveSPIDs
* Creator: Bill Wunder
*
* Date: 3-13-2003
*
* Project: utility
*
* Description: show current runnable, blocking and, if exists, blocked spids
*
* Usage:
EXECUTE dbo.ActiveSPIDs
* notes: build on all servers and run as CTL-F1 (Tool|Customize|Custom tab) in Query Analyzer.
*
* Modifications:
* Developer Name Date Brief Description
* ------------------ -------- ------------------------------------------------------------
* bw 7-4-2003 show current activity not cumulative and make multiuser
* bw 09-02-03 add fn_get_sql for blocker
********************************************************************************************************/
declare @blocker int
, @spid int
, @handle binary(20)
, @start int
, @end int
set nocount on
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- use a permanent table so multiple users can watch at the same time
if object_id('dbo.RunningSPIDs','U') is null
begin
create table dbo.RunningSPIDs
(spid smallint
, host varchar(15)
, cpu int
, io bigint
, lastcpu int
, lastio bigint
, start varchar(20)
, app varchar(35)
, recdt datetime
, lastrecdt datetime
, op varchar(20)
, open_tran smallint)
end
delete r
from dbo.RunningSPIDs r
left join master.dbo.sysprocesses p
on p.spid = r.spid
where cast(p.last_batch as varchar(20)) <> r.start
or p.spid is null
or p.status <> 'runnable'
update r
set cpu = p.cpu
, io = cast(p.physical_io as int)
, lastcpu = r.cpu
, lastio = r.io
, recdt = getdate()
, lastrecdt = r.recdt
, open_tran = p.open_tran
from master.dbo.sysprocesses p
left join msdb.dbo.sysjobs j
on substring(p.program_name,charindex('0x', p.program_name) + 18, 16)
= substring(replace(j.job_id, '-',''),17,16)
join dbo.RunningSPIDs r
on p.spid = r.spid
where (p.status='runnable'
or p.spid in (Select blocked from master.dbo.sysprocesses where blocked <> 0))
and p.spid<>@@spid
and r.app = case when p.program_name like 'SQLAgent - TSQL JobStep%'
then 'Job: ' + substring(j.name,1,30)
else substring(p.program_name,1,35)
end
insert dbo.RunningSPIDs
select p.spid
, substring(p.hostname,1,15)
, p.cpu
, cast(p.physical_io as int)
, null
, null
, cast(p.last_batch as varchar(20))
, case when p.program_name like 'SQLAgent - TSQL JobStep%'
then 'Job: ' + substring(j.name,1,30)
else substring(p.program_name,1,35)
end
, getdate()
, null
, substring(replace(replace(replace(p.cmd,char(13),char(32)),char(10)
,char(32)),char(9), char(32)),1,20)
, p.open_tran
from master.dbo.sysprocesses p
left join msdb.dbo.sysjobs j
on substring(p.program_name,charindex('0x', p.program_name) + 18, 16)
= substring(replace(j.job_id, '-',''),17,16)
where (p.status='runnable'
or p.spid in (Select blocked from master.dbo.sysprocesses where blocked <> 0))
and p.spid<>@@spid
and not exists (select 1 from dbo.RunningSPIDs where spid = p.spid)
print '"Runnable" SPIDs ordered by CPU usage'
print ' [cur cpu] and [cur io] in last [cur dur] (current duration) seconds - rerun proc to refresh'
print ' Null [cur dur] means [cur cpu] and [cur io] values are total values since batch started.'
print ' If [cur dur] and batch started not changing check for open tran -->'
print ''
select spid
, isnull(cpu-lastcpu,cpu) [cur cpu]
, isnull(io-lastio,io) [cur io]
, datediff(second,lastrecdt,recdt) as [cur dur]
, start as [batch started]
, host
, op
, app
, open_tran
, cpu [tot cpu]
, io [tot io]
from dbo.RunningSPIDs
order by cpu desc
if exists(select 1 from master.dbo.sysprocesses where blocked <> 0)
begin
print 'Blocking and Blocked SPIDs'
print ''
select p.spid,
p.blocked [Blocker],
p.waittime,
cast(p.lastwaittype as varchar(20)) [lastwaittype],
cast(rtrim(ltrim(p.waitresource)) as varchar(20)) [waitresource],
cast(p.last_batch as varchar(20)) as [last batch],
substring(p.hostname,1,15) as [Host Name],
substring(replace(replace(replace(p.cmd,char(13),char(32)),char(10)
,char(32)),char(9), char(32)),1,20) as [op],
case when p.program_name like 'SQLAgent - TSQL JobStep%'
then 'Job: ' + substring(j.name,1,20)
else substring(p.program_name,1,25)
end as [Application Name]
from master.dbo.sysprocesses p
left join msdb.dbo.sysjobs j
on substring(p.program_name,charindex('0x', p.program_name) + 18, 16)
= substring(replace(j.job_id, '-',''),17,16)
where p.spid<>@@spid
and (p.blocked <> 0
or p.spid in (select blocked
from master.dbo.sysprocesses
where blocked <> 0))
order by blocked, p.last_batch
select @blocker = min(blocked)
from master.dbo.sysprocesses
where blocked > 0
while @blocker is not null
begin
select @handle = sql_handle
, @start = stmt_start
, @end = stmt_end
from master.dbo.sysprocesses
where spid = @blocker
print 'Info on Blocking SPID ' + cast(@blocker as varchar(10))
IF NOT EXISTS (SELECT * FROM ::fn_get_sql(@Handle))
select 'Unknown, handle not found in cache' [Currently Executing Statement]
else
select replace(replace(replace(substring(text,
(@start + 2)/2,CASE @end
WHEN -1 THEN (datalength(text))
ELSE (@end - @start + 2)/2
END)
,char(13),char(32))
,char(10),char(32))
,char(9),char(32)) [Currently Executing Statement]
from ::fn_get_sql(@handle)
print 'Input Buffer:'
dbcc inputbuffer(@blocker)
print 'Output Buffer:'
dbcc outputbuffer(@blocker)
print 'Output Buffer:'
exec sp_lock @blocker
select @blocker = min(blocked)
from master.dbo.sysprocesses
where blocked > @blocker
end
end
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CTL+3 sp_who2
I use sp_who2 almost exclusively over sp_who.
I wish I could simple replace the CTL+2 sp_who query but
alas...
CTL+4 sp_helptext
I can't tell you how useful this is for me.
CTL+5 select char(10) +'###',
s.starttime,p.name+' - '+s.stepname,
s.errordescription
from msdb..sysdtssteplog s
join msdb..sysdtspackagelog p
on s.lineagefull=p.lineagefull
where s.starttime>getdate()-8
and s.stepexecresult<>0
order by s.starttime desc
This one lets me get all the DTS failures that have been
written to msdb in the last week. Note that you cannot put line
feeds into the shortcut input box. I reformatted this query here
to make it more readable. You would want to remove all the line
feeds from the query before you paste it into the shortcut input
box if you use any query. I also normally like to generously use
white space in my queries. This one exceeded the maximum length
allowed for a shortcut before I removed all unnecessary white
space.
CTL+6 select * from admin.dbo.blockingdetails
where cast(recCreatedDt as datetime) > getdate() - 2
This one shows me all the long running blocks - defined as
any block lasting over 30 seconds in my shop - that occurred on
a server in the last 2 days. I use this one a lot when someone
asks, "Was there something going on on the server at such and
such a time." Uncanny how many times I can tell them yes.
The script
the script to create this automated block monitoring and
tracking tool in the admin database is also available in the
sswug.com
Script Library.
use admin
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetBlockingDetails]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetBlockingDetails]
GO
CREATE PROCEDURE dbo.GetBlockingDetails
@email_recipients varchar(100)
as
/*******************************************************************************************************
* admin.dbo.GetBlockingDetails
* Creator: Bill Wunder
* bwunder@yahoo.com
*
* Date: 3-13-2003
*
* Project: utility
*
* Description: Track long running blocks Send notification by email and keep history in a table
*
* Usage:
EXECUTE admin.dbo.GetBlockingDetails
* notes: proc creates tracking table if not already there, run from Agent with once/minute schedule
*
* Modifications:
* Developer Name Date Brief Description
* ------------------ -------- ------------------------------------------------------------
*
********************************************************************************************************/
declare @spid int,
@mySubject varchar(100),
@myMessage varchar(300),
@myQuery varchar(500),
@blocker int,
@blockedlimit int,
@recCreatedDt datetime
create table #inputbuffer(EventType varchar(20), Parameter int, EventInfo varchar(255))
-- Don't use a temp table so it can be queried later
if object_id('admin.dbo.BlockingDetails','U') is null
create table admin.dbo.BlockingDetails (recCreatedDt varchar(20),
spid int,
blockedBy int,
waitResource varchar(50),
lastBatch varchar(20),
programName varchar(36),
loginName varchar(20),
hostName varchar(15),
inputBuffer varchar(255),
additionalInfo varchar(255),
blockEndTime varchar(20),
constraint pk_BlockingDetails__recCreatedDt__BlockedBy__spid primary key (recCreatedDt, blockedBy, spid))
set nocount on
set @recCreatedDt = cast(getdate() as varchar(20))
-- could be more than one block, take a stab at one
select @blocker = min(spid)
from master.dbo.sysprocesses
where spid in (select blocked from master.dbo.sysprocesses)
and blocked = 0
if (@blocker is not null)
begin
waitfor delay '000:01:00' -- disregard blocks that are gone in 60 seconds
-- if the identified block is gone, were done otherwise shoot off an sp_who2
if @blocker in (select blocked from master.dbo.sysprocesses)
begin
insert BlockingDetails (recCreatedDt,
spid,
blockedBy,
waitResource,
lastBatch,
programName,
loginName,
hostName)
select @recCreatedDt,
spid,
blocked,
substring(waitresource,1,50),
cast(last_batch as varchar(20)),
cast(case when (program_name like 'SQLAgent%' and charindex('0x', program_name) > 0)
then substring(program_name,charindex('0x', program_name),35)
else substring(program_name,1,35)
end as varchar(36)),
cast(substring(loginame,1,20) as varchar(20)),
cast(substring(hostname,1,15) as varchar(15))
from master.dbo.sysprocesses
where spid = @blocker
or blocked = @blocker
order by blocked, spid -- will always put blocker first
select @mySubject = @@servername + ' Block Alert: spid '
+ cast(@blocker as varchar(10)) + ' has blocked '
+ cast((@@rowcount - 1) as varchar(10))
+ ' other process(es) for 1 Minute'
select @myMessage = 'See attached for details of the blocking chain'
select @myQuery = 'select *
from admin.dbo.BlockingDetails
where (spid = ' + cast(@blocker as varchar(10)) + '
or blockedBy = ' + cast(@blocker as varchar(10)) + ')
and recCreatedDt = ''' + cast(@recCreatedDt as varchar(20)) + ''''
exec master.dbo.xp_sendmail @recipients = @email_recipients,
@subject = @mySubject,
@message = @myMessage,
@query = @myQuery,
@width = 800
-- keep going if this spid is still blocking
if @blocker in (select blocked from master.dbo.sysprocesses)
begin
insert #inputbuffer exec master.dbo.sp_executesql N'dbcc inputbuffer(@spid)',N'@spid int',@blocker
update admin.dbo.BlockingDetails
set inputbuffer = replace(EventInfo, char(13) + char(10), ' ') -- get rid of line feed
from #inputbuffer
where spid = @blocker
and recCreatedDt = @recCreatedDt
-- add table lock info to the additionalInfo column
end
-- add the inputbuffer of each blocked spid to the details table
select @spid = min(spid)
from admin.dbo.BlockingDetails
where blockedBy = @blocker
and recCreatedDt = @recCreatedDt
and spid > 0
while @spid is not null
begin
if exists (select 1 from master.dbo.sysprocesses where spid = @spid
and blocked = @blocker)
begin
delete #inputbuffer
insert #inputbuffer exec sp_executesql N'dbcc inputbuffer(@spid)',N'@spid int',@spid
update admin.dbo.BlockingDetails
set inputbuffer = replace(EventInfo, char(13) + char(10), ' ') -- get rid of line feed
from #inputbuffer
where spid = @spid
and blockedBy = @blocker
and recCreatedDt = @recCreatedDt
end
else
update admin.dbo.BlockingDetails
set additionalInfo = 'No longer blocked, may have timed out',
blockEndTime = cast(getdate() as varchar(20))
where BlockingDetails.spid = @spid
and blockedBy = @blocker
and recCreatedDt = @recCreatedDt
select @spid = min(spid)
from admin.dbo.BlockingDetails
where blockedBy = @blocker
and recCreatedDt = @recCreatedDt
and spid > @spid
end
end
if exists (select 1 from master.dbo.sysprocesses where blocked = @blocker)
begin
waitfor delay '000:01:00' -- provide additional reporting if block persists
if exists (select 1 from master.dbo.sysprocesses where blocked = @blocker)
begin
set @mySubject = @@servername + ' persistent block status - spid '
+ cast(@blocker as varchar(10)) + ' has been blocking for over two minutes!'
exec master.dbo.xp_sendmail @recipients = @email_recipients,
@message = 'Blocking notification will be suspended until block is ended',
@subject = @mySubject
while exists (select 1 from master.dbo.sysprocesses where blocked = @blocker)
begin
waitfor delay '000:01:00' -- provide additional reporting if block persists
-- watch the original blocked processes
select @spid = min(spid)
from admin.dbo.BlockingDetails
where blockedBy = @blocker
and recCreatedDt = @recCreatedDt
and spid > 0
while @spid is not null
begin
if not exists (select 1 from master.dbo.sysprocesses where spid = @spid
and blocked = @blocker)
update admin.dbo.BlockingDetails
set additionalInfo = 'No longer blocked, may have timed out',
blockEndTime = cast(getdate() as varchar(20))
where BlockingDetails.spid = @spid
and blockedBy = @blocker
and recCreatedDt = @recCreatedDt
select @spid = min(spid)
from admin.dbo.BlockingDetails
where blockedBy = @blocker
and recCreatedDt = @recCreatedDt
and spid > @spid
end
update admin.dbo.BlockingDetails
set blockEndTime = cast(getdate() as varchar(20))
where BlockingDetails.spid = @spid
and blockedBy = @blocker
and recCreatedDt = @recCreatedDt
and blockEndTime is NULL
end
set @mySubject = @@servername + ' Blocking notification reenabled, spid '
+ cast(@blocker as varchar(10)) + ' is no longer blocking.'
exec master.dbo.xp_sendmail @recipients = @email_recipients,
@message = 'Block monitoring will resume in one minute.',
@subject = @mySubject
end
end
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CTL+7 select cast(database_name as varchar(20)) db,
datediff(mi,
backup_start_date,
backup_finish_date) [minutes],
backup_start_date,
backup_size
from msdb.dbo.backupset
where backup_start_date > getdate()-7
and type = 'D'
order by database_name
This one shows me the backup history for the last week and
includes the backup duration. This is another one that I
reformatted for readability here that would need to have the
line feeds removed to be used in a shortcut
CTL+8 master.dbo.xp_readerrorlog
Read the log. And if you type 1, highlight it and hit CTL+8
you get ERRORLOG.1. Very useful and fast!
CTL+9 admin.dbo.CheckJobStatus
This one gives me a dump of all the jobs that failed in the
last week - and that haven't been aged from msdb of course. I
had to make this one a stored procedure because I couldn't fit
the query in the shortcut's input box.
use admin
CREATE PROCEDURE dbo.CheckJobStatus
as
set nocount on
select h.run_date as [Run Date],
h.run_time as [Run Time],
cast(j.name as varchar(30)) as [Job Name],
cast(h.step_name as varchar(30)) as [Step Name] ,
h.message as [Message]
from msdb.dbo.sysjobhistory h
inner join msdb.dbo.sysjobs j
on h.job_id = j.job_id
where message not like ('% succeeded%')
and message not like ('%did not generate any output%')
and message not like ('%SQLMaint%')
and step_name not like ('%(Job outcome)%')
and h.run_date > datepart(yyyy, current_timestamp - 7) * 10000
+ datepart(mm, current_timestamp - 7) * 100
+ datepart(dd, current_timestamp - 7)
order by h.run_date desc
CTL+0 if db_name() = 'admin' use master else use admin
This one is the epitome of lazy Bill. I like to default all
users to tempdb. That way they never have problems connection
when a database is dropped. Then to make things easy for myself
I just have to hit CTL-0 to put myself in the admin database.
The other database I use with some frequency is master so I just
hit CTL+0 again and I'm in master. From there I can toggle from
admin to master to admin... until the cows come home.
So What's Hot in Your Query Analyzer? Send me your QUERY
Analyzer query shortcuts and If I get enough responses, I'll
post them in a follow on article.
Bill
If you are a reader in Boulder Colorado area please join us
at the next monthly meeting of the Boulder SQL Server User
Group. Just drop me an
email for full details. |