Monday, October 27, 2008

[DBA] What's killing my server _right now_?

I'm trying to find/create some code that will tell me at a glance what's pummelling my servers. You know, the ones where you get phone calls saying "what the F is going on?!". This will tell you what current connections are using the most resources. The obvious improvement would be to do a WAITFOR to wait 5 seconds, then compare before/after.

Here's my first stab at it.

(remember to select the beginning to end - there's code hidden off to the sides of the blog columns - grrr.


SELECT
s.login_name,
[spid] = r.session_id,
[database] = DB_NAME(r.database_id),
r.start_time,
r.[status],
r.command,
r.wait_type, r.wait_time, r.wait_resource, r.cpu_time, r.reads,
r.writes, r.logical_reads, s.HOST_NAME, s.program_name,
s.client_interface_name, s.nt_user_name, s.original_login_name,
r.USER_ID, c.client_net_address, c.client_tcp_port,
/* add other interesting columns here */
[obj] = QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid, t.[dbid]))
+ '.' + QUOTENAME(OBJECT_NAME(t.objectid, t.[dbid])),
t.[text]
FROM
sys.dm_exec_requests AS r
LEFT OUTER JOIN sys.dm_exec_connections c
ON c.session_id = r.session_id
INNER JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id
AND r.connection_id = c.connection_id
CROSS APPLY
sys.dm_exec_sql_text(r.[sql_handle]) AS t
WHERE
r.session_id <> @@SPID
AND r.session_id > 50 --system SPIDs are below 50
-- AND s.[host_name] NOT IN ('baking')
-- AND login_name not in ('the_baking_dba')
-- AND DB_NAME(r.database_id) <> 'pies'
ORDER BY logical_reads DESC, reads DESC --or you could use cpu_time

[Maintenance] Using Tokens for descriptive job failures

This was the labor of love of one of my former coworkers. I've meant to send this into SQL Server Mag for a while, but never got around to a full writeup. Hope you can use this.

What it does: in jobs, there are what are called Tokens. If you have Token Substitution turned on in the Agent settings, then this SP can be called as a job step, and it'll email the full results of the job failure step. In your job you'd create a new step (set to return failure on success/failure, since it's just reporting details of the failure), that would only be called on failure, with the "sample syntax" enclosed below. If all is set up correctly (it requires, among other things, xp_smtp_mail - but who _doesn't_ have that in a 32-bit environment?) you'll get a large email with the results of the failure.

That beats the heck out of having to dig through a job failure message that usually reads "....the step failed"


/******************************************************************
Author: Mark A. Hill
Object: usp_SQLAgentTokenMail
Description: This stored procedure uses SQL Agent tokens which can
only be used inside a scheduled job, it will not work in
Query Analyzer. An operator must be set up to send emails,
pages, net sends. Use the Notifications tab of the job to
enable alerts to be written to the Windows event log.

When setting up the job use the "Sample Syntax" below as
the last step of the job, then set all of the other steps
of the job to use "GO TO [step name]" (which contains the
the usp_SQLAgentTokenMail), the stored procedure will
determine if the job failed or succeded and if an email
should be sent or not. Note: The job log information may
appear different then what you are used to when viewing
job history, the stored procedure is doing the logging
instead of the SQL Agent (But it is probably more info
then SQL Agent normally provides!).

xp_smtp_sendmail also needs to be installed, it can be found
at http://SQLDev.Net You may also want to change the [from],
[from_name] and [server] of the xp_smtp_sendmail code below
to match your companies specific needs.

Sample Syntax:
exec msdb.dbo.usp_SQLAgentTokenMail
@job_id = [JOBID]
, @Date = [STRTDT]
, @Time = [STRTTM]
, @Instance = [INST]
, @stepct = [STEPCT]
, @stepid = [STEPID]

Legal Stuff:
You May use this code in whole or in part as public domain
as long as you don't remove these comments.

System Requirements: This has been tested on SQL Server 2000 SP3a
I would imagine that it would work on SQL 7.0 as well but
I have no reason to test it on there. Oh and you will need
a mail server running SMTP, and don't forget to install
xp_smtp_sendmail and verify that it is working properly.

*******************************************************************/

alter proc [dbo].[usp_SQLAgentTokenMail]
@job_id uniqueidentifier
, @Date int = 0
, @Time varchar(8) = 0
, @Instance varchar(128) = ''
, @stepct varchar(10) = ''
, @stepid varchar(10) = ''
as
set nocount on
Declare @varmessage nvarchar(4000)
, @job_name nvarchar(255)
, @varsubject nvarchar(255)
, @datetime varchar(20)
, @vardate varchar(10)
, @vardatetime varchar(20)
, @runduration varchar(10)
, @command varchar(1000)
, @message varchar(1000)
, @step_name varchar(128)
, @subsystem varchar(128)
, @run_status int
, @database_name varchar(128)
, @new_stepid varchar(10)
, @status varchar(30)
, @send_email int
, @send_page int
, @send_netsend int
, @send_eventlog int

, @notify_netsend_operator_id int
, @notify_page_operator_id int
, @notify_email_operator_id int
, @notify_email_operator_name varchar(128)
, @notify_netsend_operator_name varchar(128)
, @notify_page_operator_name varchar(128)

, @notify_level_eventlog int
, @notify_level_email int
, @notify_level_netsend int
, @notify_level_page int

, @weekday_pager_start_time int
, @weekday_pager_end_time int
, @saturday_pager_start_time int
, @saturday_pager_end_time int
, @sunday_pager_start_time int
, @sunday_pager_end_time int

, @dw int
, @operator_time int
, @page_time_valid int
, @email_address_string varchar(255)
, @netsend_string nvarchar(4000)
, @pager_days int
, @page_day_valid int
, @page_dw INT

, @servername sysname

SET @send_email = 0
SET @send_page = 0
SET @send_netsend = 0
SET @send_eventlog = 0
SET @pager_days = 0
set @page_day_valid = 0

select @job_name = name from msdb.dbo.sysjobs with (nolock) where job_id = @job_id
SELECT @servername = @@SERVERNAME
/******************************************************
Selecting the Job History
******************************************************/
select top 1
@new_stepid = sjh.step_id
, @step_name = isnull(sjs.step_name,'')
, @subsystem = isnull(sjs.subsystem,'')
, @database_name = isnull(sjs.database_name,'')
, @command = isnull(sjs.command,'')
, @message = isnull(sjh.message,'')
, @run_status = isnull(sjh.run_status,'')
from msdb.dbo.sysjobs sj with (nolock)
inner join msdb.dbo.sysjobsteps sjs with (nolock) on sj.job_id = sjs.job_id
inner join msdb.dbo.sysjobhistory sjh with (nolock) on sjs.job_id = sjh.job_id
where sj.job_id = @job_id
order by sjh.instance_id desc


/******************************************************
Checking if date passed is valid
******************************************************/
IF isdate(@date) = 1
BEGIN
set @vardate = convert(varchar(10),convert(datetime,convert(varchar(8),@date)),101)
END

IF len(convert(varchar(10),@time)) = 6
BEGIN
SET @time = substring(@time,1,2) + ':' + substring(@time,3,2) + ':' + substring(@time,5,2)
SET @datetime = (@vardate + ' ' + @time)
END

IF isdate(@datetime) = 1
BEGIN
SET @vardatetime = @datetime
END
ELSE
BEGIN
SET @vardatetime = @vardate
END
select @runduration = convert(varchar(10),datediff(ms,@datetime,getdate()))


/******************************************************
Building the subject and message
******************************************************/

if @run_status = 0
BEGIN
SET @varsubject = ('SQL Server Job System: ' + @job_name + ' Failed.')
SET @status = 'Failed'
END
ELSE
BEGIN
SET @varsubject = ('SQL Server Job System: ' + @job_name + ' Completed.')
SET @status = 'Completed'
END

SET @varmessage = ('
Job Name: ' + @job_name + '
Status: ' + @status + '
Server: ' + @@servername + '
Database: ' + @database_name + '
Instance: ' + @instance + '
Date Time: ' + @vardatetime + '
Run Duration: ' + @runduration + ' (Milliseconds)
Step: ' + @step_name + '
Command Type: ' + @subsystem + '
Command Executed: ' + @command + '
Error Message: ' + @message
)


/******************************************************
Selecting the operator to notify
******************************************************/

select @notify_email_operator_id = notify_email_operator_id
, @notify_netsend_operator_id = notify_netsend_operator_id
, @notify_page_operator_id = notify_page_operator_id
, @notify_level_eventlog = notify_level_eventlog
, @notify_level_email = notify_level_email
, @notify_level_netsend = notify_level_netsend
, @notify_level_page = notify_level_page
from msdb.dbo.sysjobs with (nolock)
where job_id = @job_id

IF @notify_email_operator_id = null PRINT 'No Operator exists, create an operator using Enterprise Manager!'

select @notify_page_operator_name = pager_address
, @weekday_pager_start_time = weekday_pager_start_time
, @weekday_pager_end_time = weekday_pager_end_time
, @saturday_pager_start_time = saturday_pager_start_time
, @saturday_pager_end_time = saturday_pager_end_time
, @sunday_pager_start_time = sunday_pager_start_time
, @sunday_pager_end_time = sunday_pager_end_time
, @pager_days = pager_days
from msdb.dbo.sysoperators with (nolock)
where id = @notify_page_operator_id
and enabled = 1

select @notify_email_operator_name = email_address
from msdb.dbo.sysoperators with (nolock)
where id = @notify_email_operator_id
and enabled = 1

select @notify_netsend_operator_name = netsend_address
from msdb.dbo.sysoperators with (nolock)
where id = @notify_netsend_operator_id
and enabled = 1

/******************************************************
Checking which alerts actions to perform.
******************************************************/

/*
notify_level_email
0 = Never
1 = When the job succeeds
2 = When the job fails
3 = Whenever the job completes (regardless of the job outcome)

Run_status
Status of the job execution:
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
*/

IF @notify_level_email = 0
BEGIN
SET @send_email = 0
END
ELSE
BEGIN
IF @notify_level_email = 3
BEGIN
SET @send_email = 1
END
ELSE
BEGIN
IF ((@run_status = 0) and (@notify_level_email = 2))
BEGIN
SET @send_email = 1
END
ELSE
BEGIN
IF ((@run_status = 1) and (@notify_level_email = 1))
BEGIN
SET @send_email = 1
END
ELSE
BEGIN
SET @send_email = 0
END
END
END
END

IF @notify_level_page = 0
BEGIN
SET @send_page = 0
END
ELSE
BEGIN
IF @notify_level_page = 3
BEGIN
SET @send_page = 1
END
ELSE
BEGIN
IF ((@run_status = 0) and (@notify_level_page = 2))
BEGIN
SET @send_page = 1
END
ELSE
BEGIN
IF ((@run_status = 1) and (@notify_level_page = 1))
BEGIN
SET @send_page = 1
END
ELSE
BEGIN
SET @send_page = 0
END
END
END
END

IF @notify_level_netsend = 0
BEGIN
SET @send_netsend = 0
END
ELSE
BEGIN
IF @notify_level_netsend = 3
BEGIN
SET @send_netsend = 1
END
ELSE
BEGIN
IF ((@run_status = 0) and (@notify_level_netsend = 2))
BEGIN
SET @send_netsend = 1
END
ELSE
BEGIN
IF ((@run_status = 1) and (@notify_level_netsend = 1))
BEGIN
SET @send_netsend = 1
END
ELSE
BEGIN
SET @send_netsend = 0
END
END
END
END

IF @notify_level_eventlog = 0
BEGIN
SET @send_eventlog = 0
END
ELSE
BEGIN
IF @notify_level_eventlog = 3
BEGIN
SET @send_eventlog = 1
END
ELSE
BEGIN
IF ((@run_status = 0) and (@notify_level_eventlog = 2))
BEGIN
SET @send_eventlog = 1
END
ELSE
BEGIN
IF ((@run_status = 1) and (@notify_level_eventlog = 1))
BEGIN
SET @send_eventlog = 1
END
ELSE
BEGIN
SET @send_eventlog = 0
END
END
END
END


/******************************************************
Checking which Days pages should be sent
******************************************************/

set @dw = datepart(dw,getdate())

if @pager_days & 1 = 1 set @page_dw = 1
IF @page_dw = @dw set @page_day_valid = 1

if @pager_days & 2 = 2 set @page_dw = 2
IF @page_dw = @dw set @page_day_valid = 1

if @pager_days & 4 = 4 set @page_dw = 3
IF @page_dw = @dw set @page_day_valid = 1

if @pager_days & 8 = 8 set @page_dw = 4
IF @page_dw = @dw set @page_day_valid = 1

if @pager_days & 16 = 16 set @page_dw = 5
IF @page_dw = @dw set @page_day_valid = 1

if @pager_days & 32 = 32 set @page_dw = 6
IF @page_dw = @dw set @page_day_valid = 1

if @pager_days & 64 = 64 set @page_dw = 7
IF @page_dw = @dw set @page_day_valid = 1


/******************************************************
Checking if operator is on pager duty.
******************************************************/

set @operator_time = (select convert(int,replace(convert(varchar(10),getdate(),108),':','')))

set @page_time_valid = 0

IF @dw between 2 and 6 -- Weekdays
BEGIN
IF @operator_time BETWEEN @weekday_pager_start_time and @weekday_pager_end_time
BEGIN
set @page_time_valid = 1
END
end

IF @dw = 1 -- Sunday
BEGIN
IF @operator_time between @sunday_pager_start_time and @sunday_pager_end_time
BEGIN
set @page_time_valid = 1
END
end

IF @dw = 7 -- Saturday
BEGIN
IF @operator_time between @saturday_pager_start_time and @saturday_pager_end_time
BEGIN
set @page_time_valid = 1
END
end

/******************************************************
Checking which email addresses to use.
******************************************************/

IF ((@page_time_valid = 1) and (@page_day_valid = 1) and (@send_page = 1))
BEGIN
IF @send_email = 1
BEGIN
SET @email_address_string = (@notify_page_operator_name + ', ' + @notify_email_operator_name)
END
ELSE
BEGIN
SET @email_address_string = @notify_page_operator_name
END
END
ELSE
BEGIN
IF @send_email = 1
BEGIN
SET @email_address_string = @notify_email_operator_name
END
END


/******************************************************
Sending the email to the operator.
******************************************************/

IF ((@send_email = 1) or (@send_page = 1))
BEGIN
exec master.dbo.xp_smtp_sendmail
@from = N'sqlservice@yourcompanyname.com'
, @FROM_NAME = @servername
, @to = @email_address_string
, @subject = @varsubject
, @message = @varmessage
, @server = N'mail'
END

/******************************************************
Sending the netsend to the operator
******************************************************/

IF @send_netsend = 1
BEGIN
SET @netsend_string = ('net send ' + char(39) + @notify_netsend_operator_name + '
' + replace(@varsubject,char(39),'') + '
' + replace(@varmessage,char(39),'') + char(39))

exec master.dbo.xp_cmdshell @netsend_string
END

/******************************************************
Passing error messages to Windows eventlog
******************************************************/
IF @send_eventlog = 1
BEGIN
RAISERROR (@varmessage, 16, 1) with log
END

/******************************************************
Passing error messages to SQL Agent
So the job will fail and show an red "x"
******************************************************/
IF @run_status != 0
BEGIN
RAISERROR (@varmessage, 16, 1)
END

/******************************************************
Passing error messages to job output log
******************************************************/
select @varsubject
select @varmessage
Select ('email sent to: ' + @email_address_string)

/*
SELECT @send_email as 'Send Email'
SELECT @send_page as 'Send Page'
SELECT @send_netsend as 'Send Netsend'
SELECT @send_eventlog as 'Write to Event Log'
SELECT @pager_days as 'Pager Days'
SELECT @dw as 'Day of Week'
SELECT @page_dw as 'Day of Week to Page'
SELECT @page_day_valid as 'Page Day Valid'
*/

Monday, October 20, 2008

[Replication] Replication status

Rough first attempt. Doesn't include everything you might need, but gives you a quick view as to how replication is doing.

The Replication Monitor has a nasty habit of not showing you broken replication - it's usually trying to reapply the command rather than actually being in a failed status. I've watched the replication monitor show everything as fine, but when you go look at a subscription you see that it's between failed retry attempts to reapply a row. If your network is slow you can see the subscriptions flash the error icon then go back to showing everything as being okay.

I'll come back to this and see about adding more information to it. Thanks to Hilary Cotter for pointing out the source table (MSDistribution_Status) in an article - but any mistakes in the code are mine.


use distribution
go
SELECT SUM(UndelivCmdsInDistDB),
MSdistribution_agents.NAME,
MSdistribution_agents.publication,
subscriber_id,
subscriber_db FROM MSDistribution_Status
INNER JOIN MSdistribution_agents
ON MSDistribution_Status.agent_id = MSdistribution_agents.id
WHERE UndelivCmdsInDistDB > 0 --show only those that are backed up
AND subscriber_id > 0 --negative subscriber IDs are for those that
--always have a snapshot ready
GROUP BY MSdistribution_agents.NAME, MSdistribution_agents.publication,
subscriber_id, subscriber_db
ORDER BY MSdistribution_agents.NAME, MSdistribution_agents.publication,
subscriber_id, subscriber_db

Friday, October 10, 2008

[Index] Statistics age

This is pretty handy. Lets you know if your stats are out of date.

Found at http://furrukhbaig.wordpress.com/2007/08/17/index-statistics-age/

SELECT
'Index Name' = ind.name,
'Statistics Date' = STATS_DATE(ind.object_id, ind.index_id)
FROM
SYS.INDEXES ind
WHERE
OBJECT_NAME(ind.object_id) = ''

Wednesday, October 1, 2008

[Tools] Some handy commands for Litespeed

Some notes of mine on using Quest Software's Litespeed Backup. Really handy software, but with some quirks.

To backup without having it compressed, when using NCS (Native Command Substitution)
/* ads_translator_deactivate */
BACKUP DATABASE dynamics TO DISK ='e:\dynamics_native.bak'

(the code in the comments is actually read by the parser and turns off the NCS)

To see if Litespeed is installed on a machine
use master
go
exec xp_sqllitespeed_version
go


Basic Restore(needs to be on one line - split here for readability)
exec master.dbo.xp_restore_database
@database = 'datbase',
@filename = '\\path\datbase.bak',
@filenumber = 1,
@with = 'RECOVERY',
@with = 'NOUNLOAD',
@with = 'STATS = 10'


To restore a database with a new name
--First, get a list of all logical files within the backup--
xp_restore_filelistonly @filename= '\\path\datbase.BAK'

--Now do a restore with MOVE. @database is the new db name
exec master..xp_restore_database @database='datbase2'
, @filename= '\\path\datbase.BAK'
, @with = 'MOVE "datbase_Data" TO "e:\SQL\datbase2.MDF"'
, @with = 'MOVE "datbase_Log" TO "e:\SQL\datbase2.LDF"'


Extractor
The extractor.exe application will take a compressed backup file and decompress a standard MS SQL backup file. Useful if you don't have Litespeed on your other server, or if you need to send a file to someone who doesn't have it.

To call it:
extractor.exe -Fc:\temp\Northwind.bak -Ec:\temp\NorthwindNative.bak

where -F is the original compressed file and -E is the name for the tape files that will be generated (typically 7 files per backup). These can be restored via SSMS by adding each of the .bak[0-7] files to a restore.

Object-level Restores
OR.exe is the application used to do object-level restores from Litespeed. It uses BCP to pull the data out of one table and to place it in the next. It can only do it on a FULL backup - not a Filegroup, Diff, or TLOG. Irritating, that - maybe they've fixed that in Version 5.

Sample command to restore the "route" table from a backup on ServerA to ServerB. This needs to be on one line, I've split it up to illustrate the parameters.
"C:\Program Files (x86)\Imceda\LiteSpeed\SQL Server\Engine\or.exe"
-F\\path\datbase.BAK
-Odbo.route
-R1
-EServerB
-Sexisting_database
-Tdbo.TBD_test_restore

  • -F: database backup filename
  • -O: table name - must include schema
  • -R: connection type - 1 is trusted
  • -E: target server
  • -S: target database
  • -T: target table name - must include schema.


It can be done within SQL Server as well.
exec master..xp_objectrecovery
@status_filename='{178FA185-ABC7-4183-910A-4DDE775BB614}',
@FileName='E:\datbase.BAK',
@ObjectName='dbo.tbd_test_restore',
@DestinationServer='qa_database',
@DestinationDatabase='new_copy_of_datbase',
@DestinationTable='TBD_newtable_temp',
@TempDirectory='E:\temp\'