[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 

[Tip] SELECT INTO with an IDENTITY column

Really basic, but I always forget the syntax.

select identity(int, 1,1) as ID [...] into [nonexistent table] from [other tables]

[Indexes] Making sure you CAN reorg/rebuild/defrag indexes

In SSMS, prior to SP2, the default when creating indexes was to create them with page locking disabled. Which means you can't defragment or rebuild them.

Here's a script, courtesy of "TheSQLGuru", which will script out the corrections.
http://www.eggheadcafe.com/software/aspnet/29953067/finding-all-the-indexes-o.aspx

set quoted_identifier off
go
SELECT "alter index [" + i.name + "] ON [" + s.name + "].[" + t.name + "]
SET (ALLOW_PAGE_LOCKS = ON)
go"
-- s.name, t.name, i.name
FROM sys.schemas s
JOIN sys.tables t ON
t.schema_id = s.schema_id
JOIN sys.indexes i ON
i.object_id = t.object_id
WHERE
i.index_id > 0
AND INDEXPROPERTY(i.object_id, i.name, 'IsPageLockDisallowed') = 1
AND INDEXPROPERTY(t.object_id, i.name, 'IsStatistics') = 0
AND NOT EXISTS
(
SELECT *
FROM sys.objects keys
WHERE
keys.parent_object_id = i.object_id AND
keys.name = i.name AND
keys.type IN('PK', 'UQ')
)

[Index] DMV to create your missing indexes

This is pure genius.

http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

Uses the DMV to create missing indexes.

(update 2013/05/10 and something, in retrospect, you need to be careful about - don't just add everything it shows.  I was reading that MS actually had this in a CTP, where it would tell you the indexes to add, like Bart's script below.  And people wrote code that would automatically add them, without thinking about what other similar indexes were there, what the impact would be on inserts and updates, etc.  So they caused massive performance problems.  Practical upshot: be careful)

SELECT
mig.index_group_handle, mid.index_handle,
CONVERT (decimal (28,1),
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
) AS improvement_measure,
'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

[Documentation] Now where are my databases again?

Everyone probably already has a version of this, but I saw some horrid code for it the other day. The below is probably 2005 specific (obviously, because of sys.master_files). Master.sys.master_files is very nice - keeps track of all the databases in one spot, rather than needing to query each databases' table.

USE master
go
SELECT sysdatabases.NAME, mf.* FROM sys.master_files mf
INNER JOIN sysdatabases ON mf.database_id = sysdatabases.dbid
ORDER BY physical_name

[Shopping] Results of my pressure cooker expedition

So, I got one. Fagor Duo. In retrospect, the Splendid would work fine, since very few recipes call for 7psi - everyone uses 15. And while some things work wonderfully well, it's not a panacea.

For one, most of the recipes are things you could've done in a crockpot. (Although maybe that's a plus...)

Secondly, while some things work wonderfully, others don't. I have a beef stew (I'll put the recipe up, even though it's not baking) that normally needs to cook for 40 minutes. With the pressure cooker, it cooks for 20... but requires 5 minutes to get up to temp, and 15 to cool down (better results). So the same time, and I have to clean the pressure cooker. Some things work well... but is cooking something in 10 minutes that much better than something in 20?

However, the beef stew _does_ gain the following:
1) Even more tender
2) Flavor better
3) The potatoes get cooked with the meat, so I don't have to deal with boiling potatoes or scrubbing that pot to remove the starch.

[Documentation] List your backups in Wiki format

Something simple I cooked up for documentation. Basically, it looks at the backups made in the past 2 weeks and create a table (in Wiki format). Cut and paste into your wiki page, and done.

BTW - if anyone has code to easily POST this to a web site, I'd be grateful. Ideally I'd set this up to run weekly and show changes in the environment.

Cut and paste the code - Wiki tables require that spacing. Make sure to run in TEXT mode

And yes, I know. The code is crude.

CREATE TABLE #wikilist (id INT IDENTITY, formatting VARCHAR(300))

INSERT INTO #wikilist (formatting)
SELECT distinct 'Server: ' + server_name + '{BR}{| border="1"
! Database !! Type !! Location'
FROM msdb.dbo.backupmediafamily backupmediafamily
inner join msdb.dbo.backupset backupset
on backupset.media_set_id = backupmediafamily.media_set_id
where backup_start_date > getdate()-14

INSERT INTO #wikilist (formatting)
--SELECT * FROM
select distinct '|-
| ' +
database_name + ' || ' +
CASE backupset.[type]
WHEN 'D' then 'Database'
WHEN 'I' THEN 'Differential database'
WHEN 'L' THEN 'TLog'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END + ' || ' +
lower(left(physical_device_name,(len(rtrim(physical_device_name)) - charindex('\',reverse(rtrim(physical_device_name)))))) AS Location
from msdb.dbo.backupmediafamily backupmediafamily
inner join msdb.dbo.backupset backupset
on backupset.media_set_id = backupmediafamily.media_set_id
where backup_start_date > getdate()-14
and left(physical_device_name,(len(rtrim(physical_device_name)) - charindex('\',reverse(rtrim(physical_device_name))))) not like '%:'
GROUP BY '|-
| ' +
database_name + ' || ' +
CASE backupset.[type]
WHEN 'D' then 'Database'
WHEN 'I' THEN 'Differential database'
WHEN 'L' THEN 'TLog'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END + ' || ' +
lower(left(physical_device_name,(len(rtrim(physical_device_name)) - charindex('\',reverse(rtrim(physical_device_name))))))
ORDER BY '|-
| ' +
database_name + ' || ' +
CASE backupset.[type]
WHEN 'D' then 'Database'
WHEN 'I' THEN 'Differential database'
WHEN 'L' THEN 'TLog'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END + ' || ' +
lower(left(physical_device_name,(len(rtrim(physical_device_name)) - charindex('\',reverse(rtrim(physical_device_name))))))

INSERT INTO #wikilist (formatting) VALUES ('|}')
SELECT formatting FROM #wikilist ORDER BY id

[Linked Servers] Trick to making cross-server queries faster

Learned this years ago, and it's one of those nice tricks to keep in your cap.

So, what collation are you running? Do you know? Do you always use the default? Do you use linked servers and need a little more performance?

If you do, then you're in luck. If you have a linked server between two servers running the same collation, enable "collation compatible" and your queries will run faster.

Why? As I remember, if you don't have it enabled, then your query is sent across without the WHERE clause. Once it comes back, it's evaluated, ensuring that collation is properly dealt with. If you have collation compatible = true, then it sends over the whole query, including the WHERE clause. So, fewer results returned, lower I/O on the far-side, and no processing required locally.

One thing, though - make sure you're on the same collation. On 2005, the default is still the same (IIRC), but what it's called has changed.