SQL Server Top 10 Queries

MartinCroft — Wed, 04 May 2011 19:29:00 GMT

Day 4 and still rehashing some old post i needed to migrate from another site. In the garden burning wood and posting the latest blog when the bench collapsed around me .

This is a list of the top 10 queries I use on a frequent basis that are relatively simple, either one liners or next to one liners. These are in reverse order from 10 through my favourite 1.

10 Job Information

You want to quickly access job information, you don’t want to have to trawl through joining MSDB.dbo.sysjobs , MSDB.sysjobsteps, just want a quick overview of what jobs are enabled/disabled or what job was updated yesterday? Or when it that job last run or next run

--Name,Enabled, description, owner, modeified date,etc
EXEC MSDB.dbo.sp_help_job
--Or specify Job_id and get step details, schedule times
EXEC MSDB.dbo.sp_help_job ’34DD4F82-423C-46E9-9E9A-BF7786′

9 Quick Search

Trying to work out where a particular column or table is called in a procedure, you can check the dependency’s or another option is to just quicker check syscomments for the text you are after, there are several procedure out on the web that add a high degree of search ability but this is fast and easy

USE NorthWind;
GO
SELECT
      OBJECT_NAME(iD),text
FROM
      syscomments
WHERE
      Text LIKE ‘%sales%’

8 Traces running

I have seen it before your running a trace, the trace hangs is it still running, how can it be you have closed the crashed profiler, always safer to check. or is big brother watching! one way to check! See BOL for the output to this function

SELECT * FROM ::fn_trace_getinfo(default)

7 Am I sysadmin

Maybe not useful for everyone, but I have had uses for this multiple times, especially with a variety of access accounts and SQL2005 /2008 ability to switch environments quickly (right click and change connection). I find it useful to know if the account I am logged in has Sysadmin as a usual theme with SQL there are a various ways of finding this out, here is one I use.

–System Admin 1 yes god like powers 0 no I can’t drop that database

SELECT is_srvrolemember(‘sysadmin’) [Sysadmin]

6 System Uptime

Is the system running like a dog? People start asking when was SQL rebooted, I.T’s magic wand lets reboot the server, when was SQL last started. There are several ways of finding out this information ( can use Top tip 5 as well!) but this is an easy approach. Basically see when SPID 1 logged in, also if you useDATEDIFF you can get SQL to tell you how many days, how many DBA’s can count I even use SELECT 10+20 to work out calculations, far too slow opening up calculator.

–Logintime for SPID 1 
SELECT DATEDIFF(dd,login_time,Getdate())Uptime,Login_time 
FROM master..sysprocesses 
WHERE spid =1

5 Errorlogs

Sometimes useful when evaluating an issue, the SQL error logs can be access from the object explorer, but can be quicker and especially if it’s a long log it open far quicker this way.

–Read Error log takes Integer value for the error log number

exec master..xp_readerrorlog

Useful to find out a variety of information quickly, the log gets recycled when the server reboots, header shows were these logs are actually kept, version of SQL and a variety of message. If database are in recovery good place to look to get idea how long its going to take

4 Statistics

Lifted directly from the pages of the SQL bible, or BOL as it known. Things are running pants trying to work out what has changed, how up to date are the statistics? This will tell you.

–STATS_DATE code from BOL 
SELECT 
‘Index Name’= i.name,‘Statistics Date’=STATS_DATE(i.id,i.indid) 
FROM 
sysobjects o 
JOIN 
sysindexes i ON o.id = i.id

I tend to use order by 2 DESC added onto the end to order by the tables that were last updated.

3 Disk Space

Another one of those problem solving procedures. Used quite frequently on development system, as you really should have no excuses for production systems running out of space, unless it is the log drive and something untoward has occurred. One of the first procedures run when a developer says my database restore won’t work, 90Gb doesn’t fit on 45Gb free space funnily enough!

–List disk information, useful for those users filling logs!

EXEC Master.dbo.xp_fixeddrives

2 Short Cut Keys

Life savers when reviewing production incidents, how often you get “it’s not working” with little or no information, multiple systems that you’re unsure of the exact schema, so it is useful to know short cut keys. These are some of the ones I use daily. By assigning to short cuts you can specify, by highlighting SQL I can quickly pull up lots of useful info

–Get the stored procedure text of system proc sp_who , just highlight sp_who & press Ctrl-F1

USE 
MASTER; 
GO 
sp_helptext 
sp_who 
– Get infomation on tables , Highlight Region & Press ALT +F1 
USE NorthWind 
GO 
Region

1 Quick Blocking

The piece of code that I probably use more than most, so simple but so helpful in times of crisis, which was basically many years ago ripped off from the system stoted procedure EXECsp_blockcnt, which basically just tells you the number of blocked processes on ther server. This querys can be written from memory with no need for fancy solutions on production boxes that your can’t role out code to.

–Blocking processes

SELECT 
* 
FROM 
MASTER.dbo.sysprocesses 
WHERE blocked <> 0

Martin Croft Musings on SQL Server : Traces, Blocking

SQL Server Top 10 Queries

Day 4 and still rehashing some old post i needed to migrate from another site. In the garden burning wood and posting the latest blog when the bench collapsed around me .