[Index] Easily find the missing indexes in a query

(run in Grid Mode in SSMS)

SET STATISTICS XML ON
[put your query here]
SET STATISTICS XML OFF 

Once you run that, in the results pane there's a "Microsoft SQL Server 2005 XML Showplan" with XML. Double-click on it - it'll open a new tab in SSMS with the XML broken out. Search for "Missing" - there will be a block entitled "Missing Indexes". It has the INEQUALITY columns, the EQUALITY columns, and the INCLUDEs that it wants.

It may be old news to some of you, but it's one of those things I hadn't played with until recently, and I'm really impressed with.

[Setup] Setting up Database Mail on 2005 servers

Just a little script to set up Database Mail on 2005 boxes. I used to use XP_SMTP_MAIL, since the IMAP mail in SQL Server 2000 was a POS. This is considerably better.

-- Create a Database Mail account

EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'Database_Email',
    @description = 'Mail account for use by all database users.',
    @email_address = 'Database_Email@yourcompanyname.com',
    @replyto_address = 'Database_Email@yourcompanyname.com',
    @display_name = 'Database_Email',
    @mailserver_name = 'mail.yourcompanyname.com' ;

-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'Database_Email',
    @description = 'Profile used for administrative mail.' ;

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'Database_Email',
    @account_name = 'Database_Email',
    @sequence_number =1 ;

-- Grant access to the profile to all users in the msdb database

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'Database_Email',
    @principal_name = 'public',
    @is_default = 1 ;

go
--Enable advanced options
sp_configure 'show advanced options',1
go
RECONFIGURE
go
--Now enable the server to send mail
sp_configure 'Database Mail XPs',1
go
reconfigure
go

--test mail
declare @test varchar(50)
select @test = 'Test Email from ' + @@servername
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Database_Email',
    @recipients = 'you@yourcompanyname.com',
    @subject = @test

[Backups] Alter jobs to change your backup server

Something I had to whip up on short notice, so you can see what code I cribbed from SSMS.

Here's what we do:

1. Find any jobs with "Backup" in the name
   
2. Get the job details via sp_help_jobstep
   
3. Step through each job step
   
   
   
   1. Create a different statement, with the new server's name
      
   2. If the step has BACKUP or SQLMAINT, and the old server's name, execute SP_UPDATE_JOBSTEP with the new code.
      
   
   

create table #tmp_sp_help_jobstep
(
step_id int null,
step_name nvarchar(128) null,
subsystem nvarchar(128) collate Latin1_General_CI_AS null,
command nvarchar(max) null,
flags int null,
cmdexec_success_code int null,
on_success_action tinyint null,
on_success_step_id int null,
on_fail_action tinyint null,
on_fail_step_id int null,
server nvarchar(128) null,
database_name sysname null,
database_user_name sysname null,
retry_attempts int null,
retry_interval int null,
os_run_priority int null,
output_file_name nvarchar(300) null,
last_run_outcome int null,
last_run_duration int null,
last_run_retries int null,
last_run_date int null,
last_run_time int null,
proxy_id int null,
job_id uniqueidentifier null)

declare @job_id UNIQUEIDENTIFIER
DECLARE @minid SMALLINT, @maxid SMALLINT
DECLARE @oldservername sysname, @newservername sysname
DECLARE @jobcode NVARCHAR(MAX)
declare crs cursor local fast_forward
for ( SELECT sv.job_id AS [JobID]
 FROM msdb.dbo.sysjobs_view AS sv
 WHERE NAME LIKE '%backup%' )

SELECT @oldservername = 'ServerA' 
SELECT @newservername = 'ServerB'
open crs
fetch crs into @job_id
while @@fetch_status >= 0
begin
TRUNCATE TABLE #tmp_sp_help_jobstep
insert into #tmp_sp_help_jobstep(step_id, step_name, subsystem, command, flags, cmdexec_success_code, on_success_action, on_success_step_id, on_fail_action, on_fail_step_id, server, database_name, database_user_name, retry_attempts, retry_interval, os_run_priority, output_file_name, last_run_outcome, last_run_duration, last_run_retries, last_run_date, last_run_time, proxy_id)
 exec msdb.dbo.sp_help_jobstep @job_id = @job_id
update #tmp_sp_help_jobstep set job_id = @job_id where job_id is NULL
--change to job step occurs here.
SELECT @minid = NULL, @maxid = NULL
SELECT @minid = MIN(step_id), @maxid = MAX(step_id) FROM #tmp_sp_help_jobstep
WHILE @minid <= @maxid   BEGIN    SELECT @jobcode = REPLACE(command, @oldservername, @newservername)      FROM #tmp_sp_help_jobstep      WHERE step_id = @minid    IF EXISTS (SELECT * FROM #tmp_sp_help_jobstep        WHERE step_id = @minid        AND (command LIKE '%backup%' OR command LIKE '%sqlmaint%')       AND command LIKE '%'+@oldservername+'%') --   EXEC msdb.dbo.sp_update_jobstep @job_id=@job_id, @step_id=@minid,  --    @command= @jobcode     SELECT @jobcode    SET @minid = @minid + 1    END  --end change   fetch crs into @job_id end  close crs deallocate crs  DROP table #tmp_sp_help_jobstep