What's Hot in Your Query Analyzer?
by Bill Wunder
Hopefully, 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
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.
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.
