Setting up DB Mail on SQL 2005

The following code will set up all the necessary parts for SQL 2005 DBMail. Note that you will need to also enable Database Mail in Surface Area Configuration for Features. Also the variables need to be set to suit the installation.

USE msdb

GO

DECLARE @ProfileName VARCHAR(255)
DECLARE @AccountName VARCHAR(255)
DECLARE @SMTPAddress VARCHAR(255)
DECLARE @EmailAddress VARCHAR(128)
DECLARE @DisplayUser VARCHAR(128)

SET @ProfileName = 'MyMailProfile';
SET @AccountName = 'MyMailAccount';
SET @SMTPAddress = 'my.smtp.server.address';
SET @EmailAddress = 'myemail@myorg.com';
SET @DisplayUser = 'My Real Name';

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @AccountName,
@email_address = @EmailAddress,
@display_name = @DisplayUser,
@mailserver_name = @SMTPAddress

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @ProfileName

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @ProfileName,
@account_name = @AccountName,
@sequence_number = 1 ;

To send an email :

EXEC msdb.dbo.sp_send_dbmail
@recipients =N'someone@someaddress.com',
@body = 'Test Email Body',
@subject = 'Test Email Subject',
@profile_name = 'MyMailProfile'

Search Cache Plans Stored Procedure

Excellent article in SQL Server Central which inspects cached plans looking for poor performance. Full credit to Ian Stirk (Ian_Stirk@yahoo.com) for a great, easy to use and very useful routine.

Code is as follows :


CREATE PROC [dbo].[dba_SearchCachedPlans]
@StringToSearchFor VARCHAR(255)
AS
/*----------------------------------------------------------------------
Purpose: Inspects cached plans for a given string.
------------------------------------------------------------------------

Parameters: @StringToSearchFor - string to search for e.g. '%missingindexes%'.

Revision History:
03/06/2008 Ian_Stirk@yahoo.com Initial version

Example Usage:
1. exec dbo.dba_SearchCachedPlans '%missingindexes%'
2. exec dbo.dba_SearchCachedPlans '%columnswithnostatistics%'
3. exec dbo.dba_SearchCachedPlans '%tablescan%'
4. exec dbo.dba_SearchCachedPlans '%CREATE PROC%MessageWrite%'

-----------------------------------------------------------------------*/
BEGIN
-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
st.text AS [SQL]
, cp.cacheobjtype
, cp.objtype
, DB_NAME(st.dbid)AS [DatabaseName]
, cp.usecounts AS [Plan usage]
, qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE CAST(qp.query_plan AS NVARCHAR(MAX))LIKE @StringToSearchFor
ORDER BY cp.usecounts DESC
END