Baseline SQL Server with SQL Sentry Performance Advisor

Baselines can be any set of metrics that have been recorded to give you an understanding of what is the norm.  Having a baseline gives you another piece to the puzzle when troubleshooting performance issues.  It is the “base” that is used as a comparison to highlight areas of performance degradation.

We are all aware how important it is to baseline our SQL Servers right?!  We all take regular baselines of our SQL Servers right?!  For some reason I’m guessing there is quite a high percentage of people who were thinking wrong to at least one of the above statements.  Why do I say that? Well in October 2012 Paul Randal carried out a survey on baselines and posted the results here.  I was surprised to see not only that more people did not have baselines than did, but also the reasoning for not having a baseline.  In my opinion there is no excuse for not taking baselines, after all it doesn’t take much time or effort to create a baseline to capture only the most significant SQL Server metrics.  My stance on this is firm but fair; if you’re a DBA and you are not taking baselines then you aren’t doing your job to the best of your abilities!  Still don’t agree?  Erin Stellato did a fantastic article over at SQL Server Central on Capturing Baselines on Production SQL Servers.  With this you don’t even need to write anything, it is a simple copy and paste job with some familiarisation of the routine.  Go on, take a look and give it a go and I’m sure you will see the value when the gremlins get into your environment and you’re frantically trying to find the problem.

Understanding how to measure performance allows you to analyse the impact on your environments the decisions you've made have had, while also assisting you with future decisions.  Too many times I have seen decisions being made incorrectly, myself included, because of a lack of information.  Without a full picture it is impossible to be sure the decision you are about to make is correct.  Basically what I am getting at is the more information about your environments you have at your disposal the better the chance of you making the right decisions.  It is worth mentioning at this point I don’t mean go and collect every single performance counter in perfmon and or all the meta-data information in the DMV’s.  The key is to find the right metrics for your environments.

The main area that most people measure is the performance of the SQL Server itself, including both Windows (perfmon) and SQL Server (DMV’s).  While this information is invaluable to have not many people measure the performance of the databases, specifically the associated code within SQL Server and the applications that abuse it.  In this post I am going to provide one way which I have used over the years to give me a representation of the performance of the statements being run against a server.

Below are just a few things over the years I have used baselines for;

• Give me a representation of “normal” performance
• Troubleshoot performance issues
• Capacity Planning
• Monitoring the Impact of an application release

Recently however I used a baseline for something completely different; to show some of the benefits that SQL Sentry Performance Advisor would give us as a DBA team in our day to day roles.  For those of you not familiar with SQL Sentry, the products they offer, in particular Performance Advisor I suggest you head over to their website here. In summary;

“Performance Advisor for SQL Server provides unparalleled insight, awareness and control over the true source of SQL Server performance issues. Performance Advisor is packed with ground-breaking features that aren’t found in any other performance monitoring software, all designed with the singular goal of simplifying the process of SQL Server performance tuning and optimization”.

Performance Advisor comprises of a Monitor Service which monitors one or more SQL Server Instances a Repository Database which requires SQL Server and a client application for viewing the collected trace data.  

For the purposes of this post I will protect sensitive information by replacing names etc.  The environment I have configured is as below;

ServerA
This Server runs the SQL Sentry Performance Advisor Monitor Service and SQL Sentry repository database.  This server also runs an instance of Reporting Services that a small reporting solution is deployed too.

ServerB
This is a production SQL Server that is monitored by SQL Sentry Performance Advisor.

To configure this environment you will also need a minimum of two servers as above.  Below is a breakdown of the process.

Configure ServerA
Install SQL Sentry Performance Advisor and monitor at least one instance of SQL Server.

Create the baseline routine
While Performance Advisor is “doing its thang” create the routine to aggregate the trace data and produce our baseline.

Take the initial baseline
This is simple leave Performance Advisor to run for 7 days monitoring ServerB, don’t react to or change anything that is not business critical during this time to ensure results are as accurate as possible.

Analyse the initial baseline
The initial baseline will be based on 7 days of trace data, analyse this data based on the baseline routine created.

Tune, Tune, Tune
Spend the next 7 days performance tuning as you see fit based on the picture that Performance Advisor has painted.

Take the second baseline
Once you are happy with the amount of tuning you have done take the second baseline.  My advice here is that you align the initial and second baselines to start and end on the same weekday and time.  I start on Monday at 00:00 and end on Sunday at 23:59:59.

Compare the initial and second baselines
Compare the results of the initial and second baselines.

NOTE – This routine has been developed and deployed to SQL Server 2008 R2 and has not been tested on any other version.  Although every care has been taken to ensure it works with SQL Server 2008, 2008R2 and 2012, I cannot guarantee it.

The routine can be found here to download and implement.

Configure ServerA
As I mentioned earlier this Server will run the Monitor Service and also house the SQL Sentry repository database.  The SQL Sentry Quick Start Guide will detail how to install and configure Performance Advisor.  The trial is for 15 days however this baseline exercise may last for 21-28 days depending on the length of the tuning process.  Alternatively you could reduce the period that the initial and second baselines are captured for so the entire process fits within the 15 days.  The reason I used 7 days is that there are processes that run over the weekend which I wanted to capture, tune and compare.  The SQL Sentry team are very good, they offer some of the best vendor support I have ever received, I would suggest speaking to them if you need to extend the trial beyond the 15 days.  Once Performance Advisor is installed and configured on ServerA to monitor ServerB you are ready to move onto the next step.

There is a ReadMe.txt in the DatabaseScripts folder that details a few requirements, but the scripts are pretty straight forward to follow.

The routine aggregates data and records the SpeedIndex (this is the metric used throughout the routine) for different intervals.  One of the intervals Business Day is defined in the routine as 08:00 to 22:00, this is specific to my current employer and if this does not reflect your business day you will need to update this in 3 of the scripts and save the files before running them.

Update the 2 - CreateDatabaseObjects.sql script;
There are three instances of 08:00 and 22:00 in this script.
Update the 3 - InsertLookupTableData.sql script;
There is one instance of 08:00 and 22:00 in this script.
Update the 4 - CreateJobs.sql script;
There are two instances of 08:00 and 22:00 in this script

There is also a stored procedure used by the Reporting Solution in 2 - CreateDatabaseObjects.sql script that uses REPLACE to remove the trailing domain from the server name.  This will need updating to your domain if you also want to remove the trailing domain for neatness.  There are two instances of .YourDomain.Here in 2 - CreateDatabaseObjects.sql that you will need update accordingly.  For example if your domain was sql365.local then you would replace .YourDomain.Here with .sql365.local

Create the baseline routine
To create the baseline routine you will need to run 4 scripts in the DatabaseScripts folder, in order, which I have listed below;

1 - CreateITInternalDatabase.sql
2 - CreateDatabaseObjects.sql
3 - InsertLookupTableData.sql
4 - CreateJobs.sql

A list of the objects created by the scripts is below;

1 x Database
ITInternal

3 x Schemas
Archive
Kpi
Reports

6 x Tables
Archive.tblSpeedIndexByMinute
Archive.tblSpeedIndexSummary
kpi.tblLookupServerName
kpi.tblLookupSpeedIndexDescription
kpi.tblSpeedIndexByMinute
kpi.tblSpeedIndexSummary

16 x Stored Procedures
Archive.spArchivetblSpeedIndexByMinute
Archive.spArchivetblSpeedIndexSummary
kpi.spGetInfoForInsertSpeedIndexByMinute
kpi.spInsertSpeedIndexByMinute
kpi.spInsertSpeedIndexSummary
kpi.spInsertSpeedIndexSummaryMoreThanOneDay
kpi.spShowPerformanceSpeedIndex
kpi.spShowPerformanceSpeedIndexMoreThan1Day
Reports.spGetAverageSpeedIndexForCurrentMonth
Reports.spGetMaxSpeedIndexSummaryCurrentBusinessDay
Reports.spGetServerName
Reports.spGetSpeedIndexByDay
Reports.spGetSpeedIndexByMinuteCurrentBusinessDay
Reports.spGetSpeedIndexDescription
Reports.spGetSpeedIndexSummary
Reports.spGetSpeedIndexSummaryYesterday

8 x Jobs
(00:30) - Insert Speed Index For Last 24 Hours
(22:30) - Insert Speed Index For Last Business Day
(Every 5 Minutes) - Insert Speed Index For Last 5 Minutes
(Every Hour) - Insert Speed Index For Last Hour
(Every Minute) - Insert Speed Index For The Minute
(Mon 01:00) - Insert Speed Index For Last Week
(Monthly) - ArchiveSpeedIndexByMinuteData
(Monthly) - Insert Speed Index For Last Month

Once the scripts have been run, you can then deploy the Reporting Services Solution.  The solution is in the ReportingServicesSolution folder and comprises of;

2 x Data Sources
ITInternal
SQLSentry

2 x Shared Data sets
ServerName
SpeedIndexDescription

3 x Reports
SpeedIndexByDay
This shows a 30 day representation of the SpeedIndex for a given server.
SpeedIndexCurrentBusinessDay
This shows a one day representation of the SpeedIndex for the current day along with the SpeedIndex for;
Max 5 Min Period
Max 1 Hour Period
Last 24 Hours
SpeedIndexForBusinessDay – Email
This is the same as SpeedIndexCurrentBusinessDay but has an added date parameter, I use this for historical checks and also for subscriptions hence the Email in the title.

Both Data Sources need to be set to use the server that you configured earlier (ServerA);

Double click each Data Source and at the Shared Data Source Properties window (see below) click Edit.

At the Connection properties window (see below) type the server name you have used as ServerA and click OK.

The only other thing to change is to update the Deployment Settings to point to the Report Server instance and path where you want to deploy the reports and of course deploy the solution.

To do this right click DatabaseTeamKPI top level in solution explorer (see below) and select properties;

The TargetServerURL needs to be updated to reflect the server you want to house the reports.  If this is different to ServerA then the servernamehere will need to reflect this.  Once amended, simply right click DatabaseTeamKPI again and select Build, then right click DatabaseTeamKPI one last time and click deploy.

The routine and reports are now setup; it is time to take the initial baseline.

Take the initial baseline
Not too much to do here, the SQL Agent jobs created by the database scripts will aggregate the trace data collected by Performance Advisor and create the baseline.  You can view the data collected by querying the tables or using the reports in the reporting solution, to familiarise yourself with the routine while the initial baseline is being collected so you can hit the ground running with the analysis.

What I suggest here is that you export the SpeedIndexByDay report once a day for use when comparing the two baselines.  This is not an essential as the data the reports use is still record so can be used historically.

Analyse the initial baseline
The SpeedIndex is the figure we are using here, the lower the better, however each environment will be different so you will have to use your own judgement as to what is acceptable in your environment.  I’ve included a summary of the how the SpeedIndex is calculated below;

SpeedIndex = ((QueryHitCount * QueryAverageDuration / 1000000) * 420 / Duration of Aggregation)

Tune, Tune, Tune
The SQLSentryPerformance.sql file contains two queries that both return the SpeedIndex and the most expensive queries run against ServerB.  The first will be run against data between two dates the second will be run against data between two times on the same day.  The second result set that contains the queries will give execution duration and read / write data.  I would take the top 25 – 50 queries and tune them.  Now when I say tune this can vary dramatically from removing an order by or adding an index, to completely refactoring a query.  SQL Sentry Plan Explorer that ships with Performance Advisor is invaluable here to help identify expensive operators in the execution plans.

Take the second baseline
Not too much to do here, the SQL Agent jobs created by the database scripts will aggregate the trace data collected by Performance Advisor and create the baseline.

NOTE – Do not make any changes during this period that are not business critical.  This will help improve the accuracy of the baselines and the impact of the tuning.

What I suggest here is that you export the SpeedIndexByDay report once a day for use when comparing the two baselines.  This is not an essential as the data the reports use is still record so can be used historically.  Another piece of advice that I mentioned earlier is that you align the initial and second baselines to start and end on the same weekday and time.  I start on Monday at 00:00 and end on Sunday at 23:59:59.

At my current employer we were able to achieve a 40% – 50% reduction in the SpeedIndex KPI during one week of tuning. 

Compare the initial and second baselines
Another simple task here, compare the SpeedIndex values of the initial and second baselines.  How in depth you want to go here is entirely up to you.  The lowest level of granularity for the SpeedIndex that the routine captures is 1 minute.

This routine has proved extremely valuable and the impact visible not only to the DBA team but to the IT management team.  So much so that off the back of this and a few other bells and whistles that Performance Advisor provides, we were approved to purchase the required licenses of Performance Advisor to monitor our core SQL Server clusters!

I hope this post gives you an insight into the importance of baselines and the role they play in the day to day monitoring of your SQL Servers.  I also hope that the routine allows you to gain in my eyes “another valuable metric” and a means by which to “Sell” SQL Sentry Performance Advisor to your management team as a truly fantastic tool which gives you so much more than just the data that the routine uses.

As always, Enjoy!

Chris

Dedicated Administrator Connection (DAC)

We all dread the scenario whereby SQL Server is under so much load and has a complete lack of resources that no further connections can be made.  Although extreme and a situation I have never been in there is a saving grace, one last chance before opting for measures that will induce outages, the Dedicated Administrator Connection or DAC.  

First introduced in SQL Server 2005 the DAC is a special connection that is accessible when all other connections fail. The DAC is available via both SQLCMD and SSMS however it is recommended to use SQLCMD as it uses fewer resources than the GUI of SSMS.  The sole purpose of the DAC is for diagnosing problems when no other connection can be made, it is not to be used as a regular connection.  It is also recommended that you connect to the master database when using the DAC and that you do not run any resource intensive queries.

There are some limitations which I will detail below;

1 - Only one connection to the DAC is allowed, if already in use any further connections will be rejected

The below query it will return the SPID for the DAC if it is in use;

SELECT  s.session_id
FROM    sys.tcp_endpoints AS E
        INNER JOIN sys.dm_exec_sessions AS S ON E.endpoint_id = S.endpoint_id
WHERE   E.name = 'Dedicated Admin Connection';

2 - By default the DAC is only available locally, this can be changed by enabling remote admin connections using sp_configure
3 - Only users with membership in the sysadmin fixed server role can connect to the DAC
4 - Some SQL Statements are unavailable using the DAC, for example BACKUP or RESTORE.

Below are some examples of connecting to the DAC using both SQLCMD and SSMS, If you have never connected to the DAC or used SQLCMD for that matter, I recommend testing connectivity to the DAC and becoming familiar with SQLCMD.  This will save precious time in the event of a serious problem, you really don't want to be googling or boling when facing a potential outage.

Example 1: Connect to the DAC using SQLCMD and integrated security

Open command prompt and run;

sqlcmd -S ServerNameHere\InstanceNameHere -d master -A

Example 2: Connect to the DAC using SQLCMD and SQL authentication

Open command prompt and run;

sqlcmd -S ServerNameHere\InstanceNameHere -U UserNameHere -P PasswordHere -d master -A

Example 3: Connect to the DAC using SSMS

Open SSMS, at the connection window prefix the ServerName or ServerName\InstanceName with ADMIN:

ADMIN:SQL365

or

ADMIN:SQL365\INST01

Enjoy!

Chris

Object Qualification

I came across an interesting issue recently with NHibernate, now it is widely known I despise ORM’s, in my experience they do a pretty mediocre job at best and at times can be absolutely horrific.  The issue was that the statements being fired at an instance of SQL Server from an application using NHibernate were not schema qualified.  Now this is not a rant at ORM’s as the issue I will show below is experienced with stored procedures, ad-hoc sql and any T-SQL you execute against SQL Server for that matter.  In fact I will be using a stored procedure in the example ;)

Now for those of you that don’t know SQL Server has to do an awfull lot of work before a statement is actually executed, here I want to show you the performance improvements that can be achieved by schema qualifying your objects.  The below quote is from Microsoft and will set the scene for the rest of the post.

"If user "dbo" owns object dbo.mystoredproc, and another user "Harry" runs this stored procedure with the command "exec mystoredproc," the initial cache lookup by object name fails because the object is not owner-qualified. (It is not yet known whether another stored procedure named Harry.mystoredproc exists, so SQL cannot be sure that the cached plan for dbo.mystoredproc is the right one to execute.) SQL Server then acquires an exclusive compile lock on the procedure and makes preparations to compile the procedure, including resolving the object name to an object ID. Before it compiles the plan, SQL Server uses this object ID to perform a more precise search of the procedure cache and is able to locate a previously compiled plan even without the owner qualification.

 If an existing plan is found, SQL Server reuses the cached plan and does not actually compile the stored procedure. However, the lack of owner-qualification forces SQL to perform a second cache lookup and acquire an exclusive compile lock before determining that the existing cached execution plan can be reused. Acquiring the lock and performing lookups and other work that is needed to get to this point can introduce a delay that is sufficient for the compile locks to lead to blocking. This is especially true if a large number of users who are not the stored procedure's owner simultaneously run it without supplying the owner name. Note that even if you do not see SPIDs waiting on compile locks, lack of owner-qualification can introduce delays in stored procedure execution and unnecessarily high CPU utilization."

Script

To demonstrate this I used the below script, I am running SQL Server 2008 R2 developer edition on my local instance and used the AdventureWorks2008R2 databasewhich is available here.

The script creates a schema called Chris in the AdventureWorks2008R2 database, a user SQL365\Chris is created for the login SQL365\Chris with the default schema of Chris.  Finally a procedure called dbo.spGetSalesOrderHeader is created that returns every record from AdventureWorks2008R2.dbo.SalesOrderHeader.

/*

      -----------------------------------------------------------------

      Object Qualification

      -----------------------------------------------------------------

   

      For more SQL resources, check out SQLServer365.blogspot.com

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

    

      -----------------------------------------------------------------

*/

-- Set Database Context

USE AdventureWorks2008R2;

GO

-- Declare variable

DECLARE @SQL VARCHAR(255)

-- Set variable

SET @SQL = 'CREATE SCHEMA Chris AUTHORIZATION dbo'

-- Create Schema

IF NOT EXISTS ( SELECT  1

                FROM    sys.schemas

                WHERE   name = 'Chris' )

    BEGIN

        EXEC (@SQL)

    END

GO

-- Create user mapped to login with default schema of the above created schema

IF NOT EXISTS ( SELECT  1

                FROM    sys.database_principals

                WHERE   name = 'SQL365\chris' )

    BEGIN

        CREATE USER [SQL365\Chris] FOR LOGIN [SQL365\Chris] WITH DEFAULT_SCHEMA = Chris;

    END

GO

-- Create procedure in dbo schema to be executed by the above user

IF EXISTS ( SELECT  1

            FROM    sys.objects

            WHERE   [object_id] = OBJECT_ID(N'[dbo].[spGetSalesOrderHeader]')

                    AND type IN ( N'P', N'PC' ) )

    DROP PROCEDURE [dbo].[spGetSalesOrderHeader]

GO

CREATE PROCEDURE dbo.spGetSalesOrderHeader

AS

    BEGIN

        SELECT  SalesOrderID ,

                RevisionNumber ,

                OrderDate ,

                DueDate ,

                ShipDate ,

                [Status] ,

                OnlineOrderFlag ,

                SalesOrderNumber ,

                PurchaseOrderNumber ,

                AccountNumber ,

                CustomerID ,

                SalesPersonID ,

                TerritoryID ,

                BillToAddressID ,

                ShipToAddressID ,

                ShipMethodID ,

                CreditCardID ,

                CreditCardApprovalCode ,

                CurrencyRateID ,

                SubTotal ,

                TaxAmt ,

                Freight ,

                TotalDue ,

                Comment ,

                rowguid ,

                ModifiedDate

        FROM    sales.SalesOrderHeader

    END

GO

I use a great tool SQLQueryStress developed by Adam Machanic (B - T) quite frequently when testing the effects of changes under load, it is ingeniously simple to use and I love it.  I used SQLQueryStress to record the results of executing the procedure and without schema qualification and with schema qualification, I used 4 threads (the number of cores in my laptop) and ran a thousand iterations to get a good average.  Results of which are included below;

Non Schema Qualified

Schema Qualified

As you can see the results are pretty damn conclusive every metric measured by SQLQueryStress saw a performance improvement by schema qualifying objects.

Run Time - 27.01% Improvement

ClientSeconds/Iteration (Avg) - 11.99% Improvement

CPU Seconds/Iteration (Avg) - 1.13% Improvement

Actual Seconds/Iteration (Avg) - 12.54% Improvement

There is no excuse for not schema qualifying your objects, performance improvements like this just cannot be ignored.

Enjoy!

Chris

DTA and Hypothetical Indexes

For those of you that don’t know DTA stands for Database Engine Tuning Adviser and is available from the Tools menu in Management Studio.  This tool was first introduced in SQL Server 2005 and has been a much used tool by DBA’s and Developers alike in most of the companies I have worked for.

There are two main problems I have with DTA firstly the default object name prefixes are terrible, no really I mean absolutely awful.  See the table below for examples.

Object type	Default object name prefixes	Example
Indexes	_dta_index_	_dta_index_dta_mv_1_7_1150627142_K2
Statistics	_dta_stat_	_dta_stat_2041058307_2_5
Views	_dta_mv_	_dta_mv_3
Partition functions	_dta_pf_	_dta_pf_1043
Partition schemes	_dta_ps_	_dta_ps_1040

Now there is no right or wrong way to standardise the names of your database objects but indexes for example I go with the below;

Single Index Key Column - IDX_TableName:ColumnName

Multi Index Key Column – IDX_TableName:CompositeX

Some people will agree some won’t, but in my experience this makes life easier for me and the team when maintaining our SQL estate.

The other problem is that while DTA is analysing a workload, it automatically creates the recommended indexes with the meaningless names as mentioned above.  DTA will always clean up the indexes it creates; well actually that is a lie.  If the DTA process exits then the indexes it has created so far will persist!

We can identify these indexes by the value of the is_hypothetical column of the sys.indexes catalog view, this will be = 1.

I have created the below script which will email a list of hypothetical indexes and the script to drop them, simply schedule this as a SQL Server Agent job as you see fit changing the @EmailProfile and @EmailRecipient variables accordingly;

/*

      -----------------------------------------------------------------

      Hypothetical Indexes

      -----------------------------------------------------------------

   

      For more SQL resources, check out SQLServer365.blogspot.com

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

    

      -----------------------------------------------------------------

*/

-- Set database context

USE master;

GO

-- Declare variables

DECLARE @EmailProfile VARCHAR(255)

DECLARE @EmailRecipient VARCHAR(255)

DECLARE @EmailSubject VARCHAR(255)

-- Set variables

SET @EmailProfile = 'DBA'

SET @EmailRecipient = 'Chris@SQLServer365.com'

SET @EmailSubject = 'ALERT - Hypothetical Indexes found on ' + @@SERVERNAME

-- Drop temporary table if exists

IF OBJECT_ID('tempDB.dbo.#HypotheticalIndexDropScript') IS NOT NULL

    DROP TABLE #HypotheticalIndexDropScript;

    

-- Create Temporary Table

CREATE TABLE #HypotheticalIndexDropScript

    (

      DatabaseName VARCHAR(255) ,

      HypotheticalIndexDropScript VARCHAR(4000)

    );

INSERT  INTO #HypotheticalIndexDropScript

        EXEC sp_msforeachdb 'USE [?]; SELECT  DB_NAME(DB_ID()), ''USE '' + ''['' + DB_NAME(DB_ID()) + ''];'' + '' IF  EXISTS (SELECT 1 FROM sys.indexes  AS i WHERE i.[object_id] = '' + ''object_id('' + + '''''''' + ''['' + SCHEMA_NAME(o.[schema_id]) + ''].'' + ''['' +  OBJECT_NAME(i.[object_id]) + '']'' + '''''''' + '')'' + '' AND name = '' + '''''''' + i.NAME + '''''''' + '') ''    

       + '' DROP INDEX '' + ''['' + i.name + '']'' + '' ON '' + ''['' + SCHEMA_NAME(o.[schema_id]) + ''].'' + ''['' + OBJECT_NAME(o.[object_id]) + ''];'' AS HypotheticalIndexDropScript

FROM    sys.indexes i

        INNER JOIN sys.objects o ON o.[object_id] = i.[object_id]

WHERE is_hypothetical = 1'

-- Check for hypothetical indexes

IF EXISTS ( SELECT  1

            FROM    #HypotheticalIndexDropScript )

    BEGIN

        DECLARE @tableHTML NVARCHAR(MAX);

        SET @tableHTML = N'<style type="text/css">'

            + N'.h1 {font-family: Arial, verdana;font-size:16px;border:0px;background-color:white;} '

            + N'.h2 {font-family: Arial, verdana;font-size:12px;border:0px;background-color:white;} '

            + N'body {font-family: Arial, verdana;} '

            + N'table{font-size:12px; border-collapse:collapse;border:1px solid black; padding:3px;} '

            + N'td{background-color:#F1F1F1; border:1px solid black; padding:3px;} '

            + N'th{background-color:#99CCFF; border:1px solid black; padding:3px;}'

            + N'</style>' + N'<table border="1">' + N'<tr>'

            + N'<th>DatabaseName</th>'

            + N'<th>HypotheticalIndexDropScript</th>' + N'</tr>'

            + CAST(( SELECT td = DatabaseName ,

                            '' ,

                            td = HypotheticalIndexDropScript ,

                            ''

                     FROM   #HypotheticalIndexDropScript

                   FOR

                     XML PATH('tr') ,

                         TYPE

                   ) AS NVARCHAR(MAX)) + N'</table>';

          

            -- Email results

        EXEC msdb.dbo.sp_send_dbmail @profile_name = @EmailProfile,

            @recipients = @EmailRecipient, @subject = @EmailSubject,

            @body = @tableHTML, @body_format = 'HTML';

    END

    GO

DTA is not coming out to play as he has been (and will continue to be) a very naughty boy.

Enjoy!

Chris