tag:blogger.com,1999:blog-47430673968005398072024-02-08T05:38:53.676-05:00Mark on Databases, etc.I focus on SQL Server and Azure SQL Database. As is true for most database developers and administrators, I have created a vast library of scripts that help me in my work. I am sharing some of my best and most universally useful ones here.Mark Freeman (@m60freeman)http://www.blogger.com/profile/11475488917130342878noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-4743067396800539807.post-62485789348705349462022-03-11T21:46:00.000-05:002022-03-11T21:46:22.974-05:00Find timeouts using Query Store<p> Kendra Little has a great <a href="https://www.littlekendra.com/2022/03/11/find-execution-timeouts-query-store/" target="_blank">blog post</a> that explains a lot about timeouts, how to create one on purpose, and various ways to find them. She includes a simple query to get the essential information about each timeout from Query Store. <br /><br />However, I often find myself in the "too much is never enough" mode, and have been using a more complex script that provides significantly more detail. Kendra's post inspired me to Optimize my script, clean it up a bit, and post it here.</p>
<style type="text/css">
.SQLCode {
font-size: 13px;
font-weight: bold;
font-family: monospace;;
white-space: pre;
-o-tab-size: 4;
-moz-tab-size: 4;
-webkit-tab-size: 4;
}
.SQLComment {
color: #00AA00;
}
.SQLString {
color: #AA0000;
}
.SQLFunction {
color: #AA00AA;
}
.SQLKeyword {
color: #0000AA;
}
.SQLOperator {
color: #777777;
}
.SQLErrorHighlight {
background-color: #FFFF00;
}
</style>
<pre class="SQLCode"><span class="SQLComment">/* Find timeouts using Query Store.sql
Purpose: This will get data from Query Store about statement timeouts. Two results sets are provided,
one with statement text and plans, and one without. The latter is for easy sharing (pasting
into Excel, etc.).
Author: Mark Freeman (@m60freeman)
Last Edited: 2022-03-11
Instructions: Provide a value for @QueryHash or set it to NULL to revieve data for all statements, then
execute.
Compatibility: SQL Server 2016+, Azure SQL Database
License: This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0
Unported License.
http://creativecommons.org/licenses/by-nc-sa/3.0/
References:
https://blogs.technet.microsoft.com/dataplatform/2017/03/02/query-store-how-it-works-how-to-use-the-new-query-store-dmvs
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-wait-stats-transact-sql
https://sqlperformance.com/2017/11/waits-2/wait-statistics-query-store
https://davegugg.wordpress.com/2015/01/29/creating-a-comma-separated-list-with-for-xml-path-and-stuff
*/</span>
<span class="SQLKeyword">SET</span> <span class="SQLKeyword">TRANSACTION</span> <span class="SQLKeyword">ISOLATION</span> <span class="SQLKeyword">LEVEL</span> <span class="SQLKeyword">READ</span> <span class="SQLKeyword">UNCOMMITTED</span><span class="SQLOperator">;</span>
<span class="SQLKeyword">DECLARE</span> @QueryHash <span class="SQLKeyword">UNIQUEIDENTIFIER</span> <span class="SQLComment">--= 0xE99B152E01DBEF83
</span><span class="SQLOperator">;</span>
<span class="SQLKeyword">CREATE</span> <span class="SQLKeyword">TABLE</span> #Result (
ExecutionType <span class="SQLKeyword">NVARCHAR</span>(60) <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
FirstExecutionTime <span class="SQLKeyword">DATETIMEOFFSET</span>(7) <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
LastExecutionTime <span class="SQLKeyword">DATETIMEOFFSET</span>(7) <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
LastQueryExecutionTime <span class="SQLKeyword">DATETIMEOFFSET</span>(7) <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
IntervalStart <span class="SQLKeyword">DATETIMEOFFSET</span>(7) <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
IntervalEnd <span class="SQLKeyword">DATETIMEOFFSET</span>(7) <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
MaxDurationSecs <span class="SQLKeyword">DECIMAL</span>(18<span class="SQLOperator">,</span> 4) <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
AvgDurationSecs <span class="SQLKeyword">DECIMAL</span>(18<span class="SQLOperator">,</span> 4) <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
ExecutionCount <span class="SQLKeyword">BIGINT</span> <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
ObjectName <span class="SQLKeyword">NVARCHAR</span>(128) <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
QueryHash <span class="SQLKeyword">BINARY</span>(8) <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
QueryId <span class="SQLKeyword">BIGINT</span> <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
StatementHandle <span class="SQLKeyword">VARBINARY</span>(44) <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
StatementType <span class="SQLKeyword">VARCHAR</span>(6) <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
StmtText <span class="SQLKeyword">NVARCHAR</span>(<span class="SQLFunction">MAX</span>) <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
QueryPlan <span class="SQLKeyword">XML</span> <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
QueryPlanHash <span class="SQLKeyword">BINARY</span>(8) <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
QueryParameterization <span class="SQLKeyword">NVARCHAR</span>(60) <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
IsTrivial <span class="SQLKeyword">BIT</span> <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
IsParallel <span class="SQLKeyword">BIT</span> <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
IsForced <span class="SQLKeyword">BIT</span> <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
MaxLogicalReads <span class="SQLKeyword">BIGINT</span> <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
AvgLogicalReads <span class="SQLKeyword">INT</span> <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
MaxLogicalWrites <span class="SQLKeyword">BIGINT</span> <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
AvgLogicalWrites <span class="SQLKeyword">INT</span> <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
Waits <span class="SQLKeyword">NVARCHAR</span>(<span class="SQLFunction">MAX</span>) <span class="SQLOperator">NOT</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">,</span>
<span class="SQLKeyword">PRIMARY</span> <span class="SQLKeyword">KEY</span> ( IntervalEnd <span class="SQLKeyword">DESC</span><span class="SQLOperator">,</span>
LastQueryExecutionTime <span class="SQLKeyword">DESC</span><span class="SQLOperator">,</span>
MaxLogicalReads <span class="SQLKeyword">DESC</span><span class="SQLOperator">,</span>
StatementHandle<span class="SQLOperator">,</span>
QueryPlanHash)
)<span class="SQLOperator">;</span>
<span class="SQLKeyword">WITH</span> <span class="SQLFunction">Query</span> <span class="SQLKeyword">AS</span> (
<span class="SQLKeyword">SELECT</span> LastQueryExecutionTime <span class="SQLOperator">=</span> q<span class="SQLOperator">.</span>last_execution_time
<span class="SQLOperator">,</span>QueryParameterization <span class="SQLOperator">=</span> q<span class="SQLOperator">.</span>query_parameterization_type_desc
<span class="SQLOperator">,</span>QueryPlan <span class="SQLOperator">=</span> <span class="SQLFunction">CAST</span>(p<span class="SQLOperator">.</span>query_plan <span class="SQLKeyword">AS</span> <span class="SQLKeyword">XML</span>)
<span class="SQLOperator">,</span>IsTrivial <span class="SQLOperator">=</span> p<span class="SQLOperator">.</span>is_trivial_plan
<span class="SQLOperator">,</span>IsParallel <span class="SQLOperator">=</span> p<span class="SQLOperator">.</span>is_parallel_plan
<span class="SQLOperator">,</span>IsForced <span class="SQLOperator">=</span> p<span class="SQLOperator">.</span>is_forced_plan
<span class="SQLOperator">,</span>ObjectName <span class="SQLOperator">=</span> <span class="SQLFunction">OBJECT_NAME</span>(q<span class="SQLOperator">.</span>[object_id])
<span class="SQLOperator">,</span>QueryHash <span class="SQLOperator">=</span> q<span class="SQLOperator">.</span>query_hash
<span class="SQLOperator">,</span>QueryId <span class="SQLOperator">=</span> q<span class="SQLOperator">.</span>query_id
<span class="SQLOperator">,</span>StatementHandle <span class="SQLOperator">=</span> t<span class="SQLOperator">.</span>statement_sql_handle
<span class="SQLOperator">,</span>StmtText <span class="SQLOperator">=</span> t<span class="SQLOperator">.</span>query_sql_text
<span class="SQLOperator">,</span>QueryPlanHash <span class="SQLOperator">=</span> p<span class="SQLOperator">.</span>query_plan_hash
<span class="SQLOperator">,</span>QueryPlanId <span class="SQLOperator">=</span> p<span class="SQLOperator">.</span>plan_id
<span class="SQLKeyword">FROM</span> sys<span class="SQLOperator">.</span>query_store_query_text t
<span class="SQLKeyword">JOIN</span> sys<span class="SQLOperator">.</span>query_store_query q <span class="SQLKeyword">ON</span> t<span class="SQLOperator">.</span>query_text_id <span class="SQLOperator">=</span> q<span class="SQLOperator">.</span>query_text_id
<span class="SQLKeyword">JOIN</span> sys<span class="SQLOperator">.</span>query_store_plan p <span class="SQLKeyword">ON</span> q<span class="SQLOperator">.</span>query_id <span class="SQLOperator">=</span> p<span class="SQLOperator">.</span>query_id
<span class="SQLKeyword">WHERE</span> t<span class="SQLOperator">.</span>query_sql_text <span class="SQLOperator">NOT</span> <span class="SQLOperator">LIKE</span> <span class="SQLString">'ALTER INDEX %'</span>
<span class="SQLOperator">AND</span> t<span class="SQLOperator">.</span>query_sql_text <span class="SQLOperator">NOT</span> <span class="SQLOperator">LIKE</span> <span class="SQLString">'SELECT StatMan%'</span>
<span class="SQLOperator">AND</span> t<span class="SQLOperator">.</span>query_sql_text <span class="SQLOperator">NOT</span> <span class="SQLOperator">LIKE</span> <span class="SQLString">'%encrypted%'</span>
<span class="SQLOperator">AND</span> t<span class="SQLOperator">.</span>query_sql_text <span class="SQLOperator">NOT</span> <span class="SQLOperator">LIKE</span> <span class="SQLString">'(@plan_handle%'</span>
<span class="SQLOperator">AND</span> t<span class="SQLOperator">.</span>query_sql_text <span class="SQLOperator">NOT</span> <span class="SQLOperator">LIKE</span> <span class="SQLString">'WITH tempdbObjects%'</span>
<span class="SQLOperator">AND</span> t<span class="SQLOperator">.</span>query_sql_text <span class="SQLOperator">NOT</span> <span class="SQLOperator">LIKE</span> <span class="SQLString">'SELECT SUM(reserved_page_count)%'</span>
<span class="SQLOperator">AND</span> t<span class="SQLOperator">.</span>query_sql_text <span class="SQLOperator">NOT</span> <span class="SQLOperator">LIKE</span> <span class="SQLString">'SELECT secondary_lag_seconds%'</span>
<span class="SQLOperator">AND</span> q<span class="SQLOperator">.</span>query_hash <span class="SQLOperator">=</span> <span class="SQLFunction">ISNULL</span>(@QueryHash<span class="SQLOperator">,</span> q<span class="SQLOperator">.</span>query_hash)
)
<span class="SQLKeyword">INSERT</span> <span class="SQLKeyword">INTO</span> #Result <span class="SQLKeyword">WITH</span> (<span class="SQLKeyword">TABLOCK</span>)
<span class="SQLKeyword">SELECT</span> ExecutionType <span class="SQLOperator">=</span> rs<span class="SQLOperator">.</span>execution_type_desc
<span class="SQLOperator">,</span>FirstExecutionTime <span class="SQLOperator">=</span> rs<span class="SQLOperator">.</span>first_execution_time
<span class="SQLOperator">,</span>LastExecutionTime <span class="SQLOperator">=</span> rs<span class="SQLOperator">.</span>last_execution_time
<span class="SQLOperator">,</span>q<span class="SQLOperator">.</span>LastQueryExecutionTime
<span class="SQLOperator">,</span>IntervalStart <span class="SQLOperator">=</span> i<span class="SQLOperator">.</span>start_time
<span class="SQLOperator">,</span>IntervalEnd <span class="SQLOperator">=</span> i<span class="SQLOperator">.</span>end_time
<span class="SQLOperator">,</span>MaxDurationSecs <span class="SQLOperator">=</span> <span class="SQLFunction">CAST</span>(<span class="SQLFunction">ROUND</span>(rs<span class="SQLOperator">.</span>max_duration <span class="SQLOperator">/</span> 1000000.0<span class="SQLOperator">,</span> 4) <span class="SQLKeyword">AS</span> <span class="SQLKeyword">DECIMAL</span>(18<span class="SQLOperator">,</span> 4))
<span class="SQLOperator">,</span>AvgDurationSecs <span class="SQLOperator">=</span> <span class="SQLFunction">CAST</span>(<span class="SQLFunction">ROUND</span>(rs<span class="SQLOperator">.</span>avg_duration <span class="SQLOperator">/</span> 1000000.0<span class="SQLOperator">,</span> 4) <span class="SQLKeyword">AS</span> <span class="SQLKeyword">DECIMAL</span>(18<span class="SQLOperator">,</span> 4))
<span class="SQLOperator">,</span>ExecutionCount <span class="SQLOperator">=</span> rs<span class="SQLOperator">.</span>count_executions
<span class="SQLOperator">,</span>q<span class="SQLOperator">.</span>ObjectName
<span class="SQLOperator">,</span>q<span class="SQLOperator">.</span>QueryHash
<span class="SQLOperator">,</span>q<span class="SQLOperator">.</span>QueryId
<span class="SQLOperator">,</span>q<span class="SQLOperator">.</span>StatementHandle
<span class="SQLOperator">,</span>StatementType <span class="SQLOperator">=</span> <span class="SQLKeyword">CASE</span>
<span class="SQLKeyword">WHEN</span> <span class="SQLFunction">CHARINDEX</span>(<span class="SQLString">N'MERGE '</span><span class="SQLOperator">,</span> q<span class="SQLOperator">.</span>StmtText) <span class="SQLOperator">></span> 0 <span class="SQLKeyword">THEN</span> <span class="SQLString">'MERGE'</span>
<span class="SQLKeyword">WHEN</span> <span class="SQLFunction">CHARINDEX</span>(<span class="SQLString">N'INSERT '</span><span class="SQLOperator">,</span> q<span class="SQLOperator">.</span>StmtText) <span class="SQLOperator">></span> 0 <span class="SQLKeyword">THEN</span> <span class="SQLString">'INSERT'</span>
<span class="SQLKeyword">WHEN</span> <span class="SQLFunction">CHARINDEX</span>(<span class="SQLString">N'UPDATE '</span><span class="SQLOperator">,</span> q<span class="SQLOperator">.</span>StmtText) <span class="SQLOperator">></span> 0 <span class="SQLKeyword">THEN</span> <span class="SQLString">'UPDATE'</span>
<span class="SQLKeyword">WHEN</span> <span class="SQLFunction">CHARINDEX</span>(<span class="SQLString">N'DELETE '</span><span class="SQLOperator">,</span> q<span class="SQLOperator">.</span>StmtText) <span class="SQLOperator">></span> 0 <span class="SQLKeyword">THEN</span> <span class="SQLString">'DELETE'</span>
<span class="SQLKeyword">WHEN</span> <span class="SQLFunction">CHARINDEX</span>(<span class="SQLString">N'SELECT '</span><span class="SQLOperator">,</span> q<span class="SQLOperator">.</span>StmtText) <span class="SQLOperator">></span> 0 <span class="SQLKeyword">THEN</span> <span class="SQLString">'SELECT'</span>
<span class="SQLKeyword">END</span>
<span class="SQLOperator">,</span>q<span class="SQLOperator">.</span>StmtText
<span class="SQLOperator">,</span>q<span class="SQLOperator">.</span>QueryPlan
<span class="SQLOperator">,</span>q<span class="SQLOperator">.</span>QueryPlanHash
<span class="SQLOperator">,</span>q<span class="SQLOperator">.</span>QueryParameterization
<span class="SQLOperator">,</span>q<span class="SQLOperator">.</span>IsTrivial
<span class="SQLOperator">,</span>q<span class="SQLOperator">.</span>IsParallel
<span class="SQLOperator">,</span>q<span class="SQLOperator">.</span>IsForced
<span class="SQLOperator">,</span>MaxLogicalReads <span class="SQLOperator">=</span> rs<span class="SQLOperator">.</span>max_logical_io_reads
<span class="SQLOperator">,</span>AvgLogicalReads <span class="SQLOperator">=</span> <span class="SQLFunction">CAST</span>(<span class="SQLFunction">ROUND</span>(rs<span class="SQLOperator">.</span>avg_logical_io_reads<span class="SQLOperator">,</span> 0) <span class="SQLKeyword">AS</span> <span class="SQLKeyword">INT</span>)
<span class="SQLOperator">,</span>MaxLogicalWrites <span class="SQLOperator">=</span> rs<span class="SQLOperator">.</span>max_logical_io_writes
<span class="SQLOperator">,</span>AvgLogicalWrites <span class="SQLOperator">=</span> <span class="SQLFunction">CAST</span>(<span class="SQLFunction">ROUND</span>(rs<span class="SQLOperator">.</span>avg_logical_io_writes<span class="SQLOperator">,</span> 0) <span class="SQLKeyword">AS</span> <span class="SQLKeyword">INT</span>)
<span class="SQLOperator">,</span>w<span class="SQLOperator">.</span>Waits
<span class="SQLKeyword">FROM</span> <span class="SQLFunction">Query</span> q
<span class="SQLKeyword">JOIN</span> sys<span class="SQLOperator">.</span>query_store_runtime_stats rs <span class="SQLKeyword">ON</span> q<span class="SQLOperator">.</span>QueryPlanId <span class="SQLOperator">=</span> rs<span class="SQLOperator">.</span>plan_id
<span class="SQLKeyword">JOIN</span> sys<span class="SQLOperator">.</span>query_store_runtime_stats_interval i <span class="SQLKeyword">ON</span> rs<span class="SQLOperator">.</span>runtime_stats_interval_id <span class="SQLOperator">=</span> i<span class="SQLOperator">.</span>runtime_stats_interval_id
<span class="SQLKeyword">OUTER</span> <span class="SQLKeyword">APPLY</span> (
<span class="SQLKeyword">SELECT</span> Waits <span class="SQLOperator">=</span> <span class="SQLFunction">STUFF</span>( ( <span class="SQLKeyword">SELECT</span> <span class="SQLString">', '</span> <span class="SQLOperator">+</span> <span class="SQLFunction">CONVERT</span>(<span class="SQLKeyword">VARCHAR</span><span class="SQLOperator">,</span> wait_category_desc) <span class="SQLOperator">+</span> <span class="SQLString">': '</span>
<span class="SQLOperator">+</span> <span class="SQLFunction">CONVERT</span>(<span class="SQLKeyword">VARCHAR</span><span class="SQLOperator">,</span> total_query_wait_time_ms) <span class="SQLOperator">+</span> <span class="SQLString">'ms'</span>
<span class="SQLKeyword">FROM</span> ( <span class="SQLKeyword">SELECT</span> ws<span class="SQLOperator">.</span>wait_category_desc<span class="SQLOperator">,</span> ws<span class="SQLOperator">.</span>total_query_wait_time_ms
<span class="SQLKeyword">FROM</span> sys<span class="SQLOperator">.</span>query_store_wait_stats ws
<span class="SQLKeyword">WHERE</span> ws<span class="SQLOperator">.</span>runtime_stats_interval_id <span class="SQLOperator">=</span> rs<span class="SQLOperator">.</span>runtime_stats_interval_id
<span class="SQLOperator">AND</span> ws<span class="SQLOperator">.</span>plan_id <span class="SQLOperator">=</span> rs<span class="SQLOperator">.</span>plan_id
<span class="SQLOperator">AND</span> rs<span class="SQLOperator">.</span>execution_type <span class="SQLOperator">=</span> ws<span class="SQLOperator">.</span>execution_type
) WaitValues
<span class="SQLKeyword">ORDER</span> <span class="SQLKeyword">BY</span> total_query_wait_time_ms <span class="SQLKeyword">DESC</span>
<span class="SQLKeyword">FOR</span> <span class="SQLKeyword">XML</span> PATH(<span class="SQLString">''</span>)
)<span class="SQLOperator">,</span> 1<span class="SQLOperator">,</span> 2<span class="SQLOperator">,</span> <span class="SQLString">''</span>
)
) w
<span class="SQLKeyword">WHERE</span> rs<span class="SQLOperator">.</span>execution_type <span class="SQLOperator">=</span> 3
<span class="SQLComment">-- AND rs.first_execution_time >= '2020-12-14 00:00:00' --DATEADD(HOUR, -12, GETDATE())
</span><span class="SQLComment">-- AND rs.last_execution_time <= '2020-12-21 23:23:23' --DATEADD(HOUR, -12, GETDATE())
</span><span class="SQLKeyword">OPTION</span> (<span class="SQLKeyword">MAXDOP</span> 1)<span class="SQLOperator">;</span> <span class="SQLComment">/* To minimize the load when run in production */</span>
<span class="SQLKeyword">SELECT</span> <span class="SQLOperator">*</span>
<span class="SQLKeyword">FROM</span> #Result
<span class="SQLKeyword">ORDER</span> <span class="SQLKeyword">BY</span> IntervalEnd <span class="SQLKeyword">DESC</span><span class="SQLOperator">,</span> LastQueryExecutionTime <span class="SQLKeyword">DESC</span><span class="SQLOperator">,</span> MaxLogicalReads <span class="SQLKeyword">DESC</span><span class="SQLOperator">,</span> StatementHandle<span class="SQLOperator">,</span> QueryPlanHash<span class="SQLOperator">;</span>
<span class="SQLComment">/* No statement text or plans, for easy sharing */</span>
<span class="SQLKeyword">SELECT</span> ExecutionType
<span class="SQLOperator">,</span>ObjectName
<span class="SQLOperator">,</span>QueryHash
<span class="SQLOperator">,</span>QueryPlanHash
<span class="SQLOperator">,</span>QueryId
<span class="SQLOperator">,</span>FirstExecutionTime
<span class="SQLOperator">,</span>LastExecutionTime
<span class="SQLOperator">,</span>LastQueryExecutionTime
<span class="SQLOperator">,</span>IntervalStart
<span class="SQLOperator">,</span>IntervalEnd
<span class="SQLOperator">,</span>MaxDurationSecs
<span class="SQLOperator">,</span>AvgDurationSecs
<span class="SQLOperator">,</span>ExecutionCount
<span class="SQLOperator">,</span>MaxLogicalReads
<span class="SQLOperator">,</span>AvgLogicalReads
<span class="SQLOperator">,</span>MaxLogicalWrites
<span class="SQLOperator">,</span>AvgLogicalWrites
<span class="SQLOperator">,</span>Waits
<span class="SQLKeyword">FROM</span> #Result
<span class="SQLKeyword">ORDER</span> <span class="SQLKeyword">BY</span> IntervalEnd <span class="SQLKeyword">DESC</span><span class="SQLOperator">,</span> LastQueryExecutionTime <span class="SQLKeyword">DESC</span><span class="SQLOperator">,</span> MaxLogicalReads <span class="SQLKeyword">DESC</span><span class="SQLOperator">,</span> StatementHandle<span class="SQLOperator">,</span> QueryPlanHash<span class="SQLOperator">;</span>
<span class="SQLKeyword">DROP</span> <span class="SQLKeyword">TABLE</span> #Result<span class="SQLOperator">;</span>
</pre>
Mark Freeman (@m60freeman)http://www.blogger.com/profile/11475488917130342878noreply@blogger.com0tag:blogger.com,1999:blog-4743067396800539807.post-91208111421802364062011-11-20T21:13:00.001-05:002014-01-13T14:17:56.730-05:00Configure Database MailWhen it comes to monitoring servers and software, I like to have them notify me of issues rather than my having to periodically ask them how they are doing. The foundation of that is being able to have them send me email.<br />
<br />
SQL Server has a feature called Database Mail that works very well for this purpose, but it can be a bit difficult to configure correctly, especially if you try to do it manually. I will show several ways to make use of Database Mail in future posts, but in this post I will show how to get it configured correctly. The necessary steps are:<br />
<br />
<ol>
<li>Enable Database Mail and SQL Agent: This sets the features so they can be used, as they are generally disabled by default.</li>
<li>Setup Database Mail</li>
<ol>
<li>Create a Database Mail profile: A profile is a collection of mail accounts, and it must exist before we can add an account to it.</li>
<li>Create a Database Mail account: This is the account we are going to use for sending email; it contains all the information about the SMTP server and the address to be used as the sender.</li>
<li>Add the account to the profile: The account cannot be used unless it is a member of a profile.</li>
<li>Grant the DBMailUsers role access to the profile: This provides the security right that is needed and also makes the profile the default.</li>
</ol>
<li>Send a test message: At this point, Database Mail should be ready to send a message and this will confirm that it works properly. Depending on your mail client, you may need to refresh your InBox view or give it a command to fetch new mail to avoid waiting for the standard interval for checking if new mail has arrived.</li>
<li>Configure SQL Agent to use the profile and operator: Without this step, SQL Agent cannot use Database Mail. Unfortunately, it requires that the SQL Agent service be restarted. I didn't add the restart to the script as it is likely that security would prevent it from working in many environments. You can use SSMS (right-click on SQL Agent in Object Explorer and select Restart), SQL Server Configuration Manager, or the Windows Services Control Panel applet to restart the service manually.</li>
</ol>
The script below is what I use to accomplish all of this except the service restart. Just substitute your own values in the block of SET statements near the top and execute it. Each of these values is described in the script.<br />
<style type="text/css">
.SQLCode {
font-size: 13px;
font-weight: bold;
font-family: monospace;;
white-space: pre;
-o-tab-size: 4;
-moz-tab-size: 4;
-webkit-tab-size: 4;
}
.SQLComment {
color: #00AA00;
}
.SQLString {
color: #AA0000;
}
.SQLFunction {
color: #AA00AA;
}
.SQLKeyword {
color: #0000AA;
}
.SQLOperator {
color: #777777;
}
</style><br />
<pre class="SQLCode"><span class="SQLComment">/* Configure Database Mail.sql
Purpose: Configures Database Mail and sends a test message
Author: Mark Freeman
Last Edited: 2011-11-20
Instructions: Update the values in the block of SET statements as appropriate,
then execute.
References: Adapted from:
http://www.kannade.com/tech/database-mail-and-sql-agent-mail-setup-using-script-t30.html and
http://www.mssqltips.com/sqlservertip/1736/sql-server-database-mail-and-sql-agent-mail-setup-by-using-a-script/
Compatibility: SQL Server 2005 and newer
License: This work is licensed under a Creative Commons
Attribution-NonCommercial-ShareAlike 3.0 Unported License.
http://creativecommons.org/licenses/by-nc-sa/3.0/
*/</span>
<span class="SQLKeyword">SET</span> <span class="SQLKeyword">NOCOUNT</span> <span class="SQLKeyword">ON</span>
<span class="SQLKeyword">USE</span> [master]
<span class="SQLKeyword">DECLARE</span> @ProfileName <span class="SQLKeyword">SYSNAME</span><span class="SQLOperator">,</span>
@SmtpServer <span class="SQLKeyword">SYSNAME</span><span class="SQLOperator">,</span>
@OperatorName <span class="SQLKeyword">SYSNAME</span><span class="SQLOperator">,</span>
@ToEmail <span class="SQLKeyword">SYSNAME</span><span class="SQLOperator">,</span>
@RecipientDomainName <span class="SQLKeyword">SYSNAME</span><span class="SQLOperator">,</span>
@SenderDomainName <span class="SQLKeyword">SYSNAME</span><span class="SQLOperator">,</span>
@ReplyToEmail <span class="SQLKeyword">SYSNAME</span><span class="SQLOperator">,</span>
@SmtpUserName <span class="SQLKeyword">NVARCHAR</span>(128)<span class="SQLOperator">,</span>
@SmtpPassword <span class="SQLKeyword">NVARCHAR</span>(128)<span class="SQLOperator">,</span>
@SmtpPort <span class="SQLKeyword">INT</span><span class="SQLOperator">,</span>
@SmtpUseDefaultCredentials <span class="SQLKeyword">BIT</span><span class="SQLOperator">,</span>
@SmtpEnableSsl <span class="SQLKeyword">BIT</span><span class="SQLOperator">;</span>
<span class="SQLComment">-- Please update the values in the section below as needed:
</span><span class="SQLKeyword">SET</span> @ProfileName <span class="SQLOperator">=</span> <span class="SQLString">'MyProfile'</span><span class="SQLOperator">;</span> <span class="SQLComment">/* This could be your company name,
domain name, or something
generic such as
'Database Mail Profile' */</span>
<span class="SQLKeyword">SET</span> @SmtpServer <span class="SQLOperator">=</span> <span class="SQLString">'smtp.mydomain.com'</span><span class="SQLOperator">;</span> <span class="SQLComment">/* Usually something like
'smtp.mydomain.com' */</span>
<span class="SQLKeyword">SET</span> @SmtpPort <span class="SQLOperator">=</span> 25<span class="SQLOperator">;</span> <span class="SQLComment">/* This is the standard port,
but yours may differ */</span>
<span class="SQLKeyword">SET</span> @RecipientDomainName <span class="SQLOperator">=</span> <span class="SQLString">'mydomain.com'</span><span class="SQLOperator">;</span> <span class="SQLComment">/* This may differ from the domain
of the sender if, for example,
you arehaving servers at other
companies send you emails */</span>
<span class="SQLKeyword">SET</span> @SenderDomainName <span class="SQLOperator">=</span> @RecipientDomainName<span class="SQLOperator">;</span> <span class="SQLComment">/* This is usually the domain of the
SMTP server */</span>
<span class="SQLKeyword">SET</span> @SmtpUserName <span class="SQLOperator">=</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">;</span> <span class="SQLComment">/* Only needed if your SMTP server
requires authentication */</span>
<span class="SQLKeyword">SET</span> @SmtpPassword <span class="SQLOperator">=</span> <span class="SQLKeyword">NULL</span><span class="SQLOperator">;</span> <span class="SQLComment">/* Only needed if your SMTP server
requires authentication */</span>
<span class="SQLKeyword">SET</span> @SmtpUseDefaultCredentials <span class="SQLOperator">=</span> 0 <span class="SQLComment">/* Change to 1 to use the
credentials of the SQL Server
Database Engine and ignore the
user name and password
settings */</span>
<span class="SQLKeyword">SET</span> @SmtpEnableSsl <span class="SQLOperator">=</span> 0<span class="SQLOperator">;</span> <span class="SQLComment">/* Change to 1 if your SMTP server
uses SSL encryption */</span>
<span class="SQLKeyword">SET</span> @ToEmail <span class="SQLOperator">=</span> <span class="SQLString">'dba'</span> <span class="SQLOperator">+</span> @RecipientDomainName<span class="SQLOperator">;</span>
<span class="SQLKeyword">SET</span> @ReplyToEmail <span class="SQLOperator">=</span> <span class="SQLString">'sqlserver'</span> <span class="SQLOperator">+</span> @SenderDomainName<span class="SQLOperator">;</span>
<span class="SQLComment">-- Please update the values in the section above as needed
</span>
<span class="SQLComment">-- Enable Database Mail and SQL Agent
</span><span class="SQLKeyword">EXEC</span> sp_configure <span class="SQLString">'show advanced options'</span><span class="SQLOperator">,</span> 1<span class="SQLOperator">;</span>
<span class="SQLKeyword">RECONFIGURE</span> <span class="SQLKeyword">WITH</span> OVERRIDE<span class="SQLOperator">;</span>
<span class="SQLKeyword">EXEC</span> sp_configure <span class="SQLString">'Database Mail XPs'</span><span class="SQLOperator">,</span> 1<span class="SQLOperator">;</span>
<span class="SQLKeyword">EXEC</span> sp_configure <span class="SQLString">'Agent XPs'</span><span class="SQLOperator">,</span>1<span class="SQLOperator">;</span>
<span class="SQLKeyword">EXEC</span> sp_configure <span class="SQLString">'show advanced options'</span><span class="SQLOperator">,</span> 0<span class="SQLOperator">;</span>
<span class="SQLKeyword">RECONFIGURE</span> <span class="SQLKeyword">WITH</span> OVERRIDE<span class="SQLOperator">;</span>
<span class="SQLComment">-- Setup Database Mail
</span><span class="SQLKeyword">IF</span> <span class="SQLOperator">EXISTS</span>(<span class="SQLKeyword">SELECT</span> <span class="SQLOperator">*</span> <span class="SQLKeyword">FROM</span> msdb<span class="SQLOperator">.</span>dbo<span class="SQLOperator">.</span>sysmail_profile)
<span class="SQLKeyword">PRINT</span> <span class="SQLString">'Database Mail already configured'</span><span class="SQLOperator">;</span>
<span class="SQLKeyword">ELSE</span> <span class="SQLKeyword">BEGIN</span>
<span class="SQLKeyword">DECLARE</span> @FromEmail <span class="SQLKeyword">SYSNAME</span><span class="SQLOperator">,</span>
@FromName <span class="SQLKeyword">SYSNAME</span><span class="SQLOperator">;</span>
<span class="SQLKeyword">SELECT</span> @FromEmail <span class="SQLOperator">=</span> <span class="SQLFunction">REPLACE</span>(<span class="SQLFunction">@@SERVERNAME</span><span class="SQLOperator">,</span><span class="SQLString">'\'</span><span class="SQLOperator">,</span><span class="SQLString">'_'</span>) <span class="SQLOperator">+</span> @SenderDomainName<span class="SQLOperator">,</span>
@FromName <span class="SQLOperator">=</span> <span class="SQLString">'SQL Server '</span> <span class="SQLOperator">+</span> <span class="SQLFunction">@@SERVICENAME</span><span class="SQLOperator">;</span>
<span class="SQLComment">-- Create a Database Mail profile
</span> <span class="SQLKeyword">EXEC</span> msdb<span class="SQLOperator">.</span>dbo<span class="SQLOperator">.</span>sysmail_add_profile_sp
@profile_name <span class="SQLOperator">=</span> @ProfileName
<span class="SQLOperator">,</span>@description <span class="SQLOperator">=</span> <span class="SQLString">'Mail profile setup for email from this SQL Server'</span><span class="SQLOperator">;</span>
<span class="SQLComment">-- Create a Database Mail account, assuming Windows Authentication
</span> <span class="SQLKeyword">EXEC</span> msdb<span class="SQLOperator">.</span>dbo<span class="SQLOperator">.</span>sysmail_add_account_sp
@account_name <span class="SQLOperator">=</span> @ToEmail
<span class="SQLOperator">,</span>@description <span class="SQLOperator">=</span> <span class="SQLString">'Mail account for use by database processes'</span>
<span class="SQLOperator">,</span>@email_address <span class="SQLOperator">=</span> @FromEmail
<span class="SQLOperator">,</span>@replyto_address <span class="SQLOperator">=</span> @ReplyToEmail
<span class="SQLOperator">,</span>@display_name <span class="SQLOperator">=</span> @FromName
<span class="SQLOperator">,</span>@mailserver_name <span class="SQLOperator">=</span> @SmtpServer
<span class="SQLOperator">,</span>@mailserver_type <span class="SQLOperator">=</span> <span class="SQLString">'SMTP'</span>
<span class="SQLOperator">,</span>@port <span class="SQLOperator">=</span> @SmtpPort
<span class="SQLOperator">,</span>@username <span class="SQLOperator">=</span> @SmtpUserName
<span class="SQLOperator">,</span>@password <span class="SQLOperator">=</span> @SmtpPassword
<span class="SQLOperator">,</span>@use_default_credentials <span class="SQLOperator">=</span> @SmtpUseDefaultCredentials
<span class="SQLOperator">,</span>@enable_ssl <span class="SQLOperator">=</span> @SmtpEnableSsl<span class="SQLOperator">;</span>
<span class="SQLComment">-- Add the account to the profile
</span> <span class="SQLKeyword">EXEC</span> msdb<span class="SQLOperator">.</span>dbo<span class="SQLOperator">.</span>sysmail_add_profileaccount_sp
@profile_name <span class="SQLOperator">=</span> @ProfileName
<span class="SQLOperator">,</span>@Account_name <span class="SQLOperator">=</span> @ToEmail
<span class="SQLOperator">,</span>@sequence_number <span class="SQLOperator">=</span> 1<span class="SQLOperator">;</span>
<span class="SQLComment">-- Grant access to the profile to the DBMailUsers role
</span> <span class="SQLKeyword">EXEC</span> msdb<span class="SQLOperator">.</span>dbo<span class="SQLOperator">.</span>sysmail_add_principalprofile_sp
@profile_name <span class="SQLOperator">=</span> @ProfileName
<span class="SQLOperator">,</span>@is_default <span class="SQLOperator">=</span> 1<span class="SQLOperator">;</span>
<span class="SQLKeyword">END</span>
<span class="SQLComment">-- Send a test message
</span><span class="SQLKeyword">DECLARE</span> @MessageText <span class="SQLKeyword">VARCHAR</span>(100)<span class="SQLOperator">;</span>
<span class="SQLKeyword">SET</span> @MessageText <span class="SQLOperator">=</span> <span class="SQLString">'Test from SQL Server '</span> <span class="SQLOperator">+</span> <span class="SQLFunction">@@SERVICENAME</span><span class="SQLOperator">;</span>
<span class="SQLKeyword">EXEC</span> msdb<span class="SQLOperator">.</span><span class="SQLOperator">.</span>sp_send_dbmail
@profile_name <span class="SQLOperator">=</span> @ProfileName
<span class="SQLOperator">,</span>@recipients <span class="SQLOperator">=</span> @ToEmail
<span class="SQLOperator">,</span>@subject <span class="SQLOperator">=</span> @MessageText
<span class="SQLOperator">,</span>@body <span class="SQLOperator">=</span> @MessageText<span class="SQLOperator">;</span>
<span class="SQLComment">-- Create operator
</span><span class="SQLKeyword">IF</span> <span class="SQLOperator">NOT</span> <span class="SQLOperator">EXISTS</span> (<span class="SQLKeyword">SELECT</span> 1 <span class="SQLKeyword">FROM</span> msdb<span class="SQLOperator">.</span>dbo<span class="SQLOperator">.</span>sysoperators <span class="SQLKeyword">WHERE</span> <span class="SQLKeyword">name</span> <span class="SQLOperator">=</span> @ToEmail)
<span class="SQLKeyword">EXEC</span> msdb<span class="SQLOperator">.</span>dbo<span class="SQLOperator">.</span>sp_add_operator
@name <span class="SQLOperator">=</span> @ToEmail
<span class="SQLOperator">,</span>@enabled <span class="SQLOperator">=</span> 1
<span class="SQLOperator">,</span>@email_address <span class="SQLOperator">=</span> @ToEmail<span class="SQLOperator">;</span>
<span class="SQLKeyword">EXEC</span> master<span class="SQLOperator">.</span>dbo<span class="SQLOperator">.</span>sp_MSsetalertinfo @failsafeoperator <span class="SQLOperator">=</span> @ToEmail<span class="SQLOperator">;</span>
<span class="SQLComment">-- Configure SQL Agent to use the profile and operator
</span><span class="SQLKeyword">USE</span> [msdb]<span class="SQLOperator">;</span>
<span class="SQLKeyword">EXEC</span> msdb<span class="SQLOperator">.</span>dbo<span class="SQLOperator">.</span>sp_set_sqlagent_properties @email_save_in_sent_folder<span class="SQLOperator">=</span>1<span class="SQLOperator">;</span>
<span class="SQLKeyword">EXEC</span> master<span class="SQLOperator">.</span>dbo<span class="SQLOperator">.</span>xp_instance_regwrite
<span class="SQLString">N'HKEY_LOCAL_MACHINE'</span>
<span class="SQLOperator">,</span><span class="SQLString">N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'</span>
<span class="SQLOperator">,</span><span class="SQLString">N'UseDatabaseMail'</span>
<span class="SQLOperator">,</span><span class="SQLString">N'REG_DWORD'</span>
<span class="SQLOperator">,</span>1<span class="SQLOperator">;</span>
<span class="SQLKeyword">EXEC</span> master<span class="SQLOperator">.</span>dbo<span class="SQLOperator">.</span>xp_instance_regwrite
<span class="SQLString">N'HKEY_LOCAL_MACHINE'</span>
<span class="SQLOperator">,</span><span class="SQLString">N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'</span>
<span class="SQLOperator">,</span><span class="SQLString">N'DatabaseMailProfile'</span>
<span class="SQLOperator">,</span><span class="SQLString">N'REG_SZ'</span>
<span class="SQLOperator">,</span>@ProfileName<span class="SQLOperator">;</span>
<span class="SQLKeyword">PRINT</span> <span class="SQLString">'**** You must manually restart the Service: SQL Server Agent ('</span>
<span class="SQLOperator">+</span> <span class="SQLFunction">@@SERVICENAME</span> <span class="SQLOperator">+</span> <span class="SQLString">')'</span><span class="SQLOperator">;</span>
</pre>
Mark Freeman (@m60freeman)http://www.blogger.com/profile/11475488917130342878noreply@blogger.com0tag:blogger.com,1999:blog-4743067396800539807.post-25920207617887444822011-09-02T12:50:00.003-04:002011-11-23T01:03:55.732-05:00Finding Foreign Keys without IndexesI have seen a number of blog posts on this topic that go into detail as to why it is important to have such indexes and sometimes provide helpful queries. Examples would include:<br />
<a href="http://www.brentozar.com/archive/2011/09/indexing-for-deletes/">http://www.brentozar.com/archive/2011/09/indexing-for-deletes/</a><br />
<a href="http://michaeljswart.com/2009/12/finding-your-missing-keys/">http://michaeljswart.com/2009/12/finding-your-missing-keys/</a><br />
<br />
However, I wanted to take it a step further. The following query not only finds the Foreign Keys without an associated Index, it also provides the command needed to create each index in both XML and text formats.<br />
<br />
If you click on the XML result, SSMS will open a new window containing the command already nicely formatted for you to copy into your source control system of preference, or into a new SSMS window so you can execute it. Of course, the query as shown uses my preferred naming and formatting standards for the indexes, but it should be easy enough to modify it to use your own.<br />
<br />
<code style="font-size: 12px;"><span style="color: green;">/* What indexes are needed for my foreign keys.sql<br />
<br />
Purpose: Find foreign keys without an index and the command needed to create the index. <br />
Note that if these aren't showing up in the missing_index* views you might not<br />
think that you need them, but not having them will cause some deletes from<br />
referenced tables to perform full table scans. For example, without the<br />
appropriate foreign key index, deleting a customer would result in a full scan<br />
of the invoice table to ensure that the customer PK does not exist in any<br />
invoice record.<br />
<br />
Author: Mark Freeman (@m60freeman)<br />
<br />
Changes: 2011-09-01 Created<br />
2011-11-20 Eliminated matches to index where the referenced column is not the<br />
first column in the index, added comment and brackets in Potential<br />
DDL, and added handling of objects with the same name in multiple<br />
schemas<br />
*/</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">SELECT </span><span style="color: black;">rc.Constraint_Schema </span><span style="color: blue;">AS </span><span style="color: black;">Base_Schema<br />
</span><span style="color: grey;">,</span><span style="color: black;">ccu.Table_Name </span><span style="color: blue;">AS </span><span style="color: black;">Base_Table<br />
</span><span style="color: grey;">,</span><span style="color: black;">ccu.Column_Name </span><span style="color: blue;">AS </span><span style="color: black;">Base_Column<br />
</span><span style="color: grey;">,</span><span style="color: black;">ccu.Constraint_Name </span><span style="color: blue;">AS </span><span style="color: black;">Foreign_Key<br />
</span><span style="color: grey;">,</span><span style="color: black;">ccu2.Constraint_Schema </span><span style="color: blue;">AS </span><span style="color: black;">Referenced_Schema<br />
</span><span style="color: grey;">,</span><span style="color: black;">ccu2.Table_Name </span><span style="color: blue;">AS </span><span style="color: black;">Referenced_Table<br />
</span><span style="color: grey;">,</span><span style="color: black;">ccu2.Column_Name </span><span style="color: blue;">AS </span><span style="color: black;">Referenced_Column<br />
</span><span style="color: grey;">,</span><span style="color: red;">'IX_' </span><span style="color: grey;">+ </span><span style="color: black;">ccu.Table_Name </span><span style="color: grey;">+ </span><span style="color: red;">'_' </span><span style="color: grey;">+ </span><span style="color: black;">ccu.Column_Name </span><span style="color: blue;">AS </span><span style="color: black;">Potential_Index_Name<br />
</span><span style="color: grey;">,</span><span style="color: magenta;">CAST</span><span style="color: grey;">(</span><span style="color: red;">'<?query --' </span><span style="color: grey;">+ </span><span style="color: blue;">CHAR</span><span style="color: grey;">(</span><span style="color: black;">13</span><span style="color: grey;">) + <br />
</span><span style="color: red;">'CREATE INDEX [IX_' </span><span style="color: grey;">+<br />
</span><span style="color: black;">ccu.Table_Name </span><span style="color: grey;">+ </span><span style="color: red;">'_' </span><span style="color: grey;">+ </span><span style="color: black;">ccu.Column_Name </span><span style="color: grey;">+ <br />
</span><span style="color: red;">']' </span><span style="color: grey;">+ </span><span style="color: blue;">CHAR</span><span style="color: grey;">(</span><span style="color: black;">13</span><span style="color: grey;">) + <br />
</span><span style="color: blue;">CHAR</span><span style="color: grey;">(</span><span style="color: black;">9</span><span style="color: grey;">) + </span><span style="color: red;">'-- Needed to support ' </span><span style="color: grey;">+ <br />
</span><span style="color: black;">ccu.Constraint_Name </span><span style="color: grey;">+ </span><span style="color: blue;">CHAR</span><span style="color: grey;">(</span><span style="color: black;">13</span><span style="color: grey;">) + <br />
</span><span style="color: blue;">CHAR</span><span style="color: grey;">(</span><span style="color: black;">9</span><span style="color: grey;">) + </span><span style="color: red;">'ON [' </span><span style="color: grey;">+ </span><span style="color: black;">rc.Constraint_Schema </span><span style="color: grey;">+ <br />
</span><span style="color: red;">'].[' </span><span style="color: grey;">+ </span><span style="color: black;">ccu.Table_Name </span><span style="color: grey;">+ </span><span style="color: red;">']' </span><span style="color: grey;">+ </span><span style="color: blue;">CHAR</span><span style="color: grey;">(</span><span style="color: black;">13</span><span style="color: grey;">) + <br />
</span><span style="color: blue;">CHAR</span><span style="color: grey;">(</span><span style="color: black;">9</span><span style="color: grey;">) + </span><span style="color: red;">'(' </span><span style="color: grey;">+ </span><span style="color: blue;">CHAR</span><span style="color: grey;">(</span><span style="color: black;">13</span><span style="color: grey;">) + <br />
</span><span style="color: blue;">CHAR</span><span style="color: grey;">(</span><span style="color: black;">9</span><span style="color: grey;">) + </span><span style="color: blue;">CHAR</span><span style="color: grey;">(</span><span style="color: black;">9</span><span style="color: grey;">) + </span><span style="color: red;">'[' </span><span style="color: grey;">+ </span><span style="color: black;">ccu.Column_Name </span><span style="color: grey;">+ <br />
</span><span style="color: red;">']' </span><span style="color: grey;">+ </span><span style="color: blue;">CHAR</span><span style="color: grey;">(</span><span style="color: black;">13</span><span style="color: grey;">) + </span><span style="color: blue;">CHAR</span><span style="color: grey;">(</span><span style="color: black;">9</span><span style="color: grey;">) + </span><span style="color: red;">');'<br />
</span><span style="color: grey;">+ </span><span style="color: blue;">CHAR</span><span style="color: grey;">(</span><span style="color: black;">13</span><span style="color: grey;">) + </span><span style="color: red;">' --?>' </span><span style="color: blue;">AS XML</span><span style="color: grey;">) </span><span style="color: blue;">AS </span><span style="color: black;">Potential_DDL_XML<br />
</span><span style="color: grey;">,</span><span style="color: red;">'CREATE INDEX [IX_' </span><span style="color: grey;">+ </span><span style="color: black;">ccu.Table_Name </span><span style="color: grey;">+ </span><span style="color: red;">'_' </span><span style="color: grey;">+ <br />
</span><span style="color: black;">ccu.Column_Name </span><span style="color: grey;">+ </span><span style="color: red;">']' </span><span style="color: grey;">+ </span><span style="color: red;">' ON [' </span><span style="color: grey;">+ <br />
</span><span style="color: black;">rc.Constraint_Schema </span><span style="color: grey;">+ </span><span style="color: red;">'].[' </span><span style="color: grey;">+ <br />
</span><span style="color: black;">ccu.Table_Name </span><span style="color: grey;">+ </span><span style="color: red;">']' </span><span style="color: grey;">+ </span><span style="color: red;">'([' </span><span style="color: grey;">+ <br />
</span><span style="color: black;">ccu.Column_Name </span><span style="color: grey;">+ <br />
</span><span style="color: red;">']); -- Needed to support '<br />
</span><span style="color: grey;">+ </span><span style="color: black;">ccu.Constraint_Name </span><span style="color: blue;">AS </span><span style="color: black;">Potential_DDL</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">FROM </span><span style="color: black;">information_schema.referential_constraints rc<br />
</span><span style="color: blue;">JOIN </span><span style="color: black;">information_schema.constraint_column_usage ccu<br />
</span><span style="color: blue;">ON </span><span style="color: black;">rc.CONSTRAINT_NAME </span><span style="color: blue;">= </span><span style="color: black;">ccu.CONSTRAINT_NAME<br />
</span><span style="color: grey;">AND </span><span style="color: black;">rc.CONSTRAINT_SCHEMA </span><span style="color: blue;">= </span><span style="color: black;">ccu.CONSTRAINT_SCHEMA<br />
</span><span style="color: blue;">JOIN </span><span style="color: black;">information_schema.constraint_column_usage ccu2<br />
</span><span style="color: blue;">ON </span><span style="color: black;">rc.UNIQUE_CONSTRAINT_NAME </span><span style="color: blue;">= </span><span style="color: black;">ccu2.CONSTRAINT_NAME<br />
</span><span style="color: grey;">AND </span><span style="color: black;">rc.CONSTRAINT_SCHEMA </span><span style="color: blue;">= </span><span style="color: black;">ccu2.CONSTRAINT_SCHEMA</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">WHERE </span><span style="color: grey;">NOT EXISTS (<br />
</span><span style="color: blue;">SELECT </span><span style="color: black;">1<br />
</span><span style="color: blue;">FROM </span><span style="color: black;">sys.indexes i <br />
</span><span style="color: blue;">JOIN </span><span style="color: black;">sys.index_columns ic<br />
</span><span style="color: blue;">ON </span><span style="color: black;">ic.[object_id] </span><span style="color: blue;">= </span><span style="color: black;">i.[object_id]<br />
</span><span style="color: grey;">AND </span><span style="color: black;">ic.index_Id </span><span style="color: blue;">= </span><span style="color: black;">i.index_Id<br />
</span><span style="color: blue;">JOIN </span><span style="color: black;">sys.columns c<br />
</span><span style="color: blue;">ON </span><span style="color: black;">c.[object_id] </span><span style="color: blue;">= </span><span style="color: black;">ic.[object_id]<br />
</span><span style="color: grey;">AND </span><span style="color: black;">c.column_id </span><span style="color: blue;">= </span><span style="color: black;">ic.column_id<br />
</span><span style="color: blue;">JOIN </span><span style="color: black;">sys.objects o<br />
</span><span style="color: blue;">ON </span><span style="color: black;">o.[object_id] </span><span style="color: blue;">= </span><span style="color: black;">c.[object_id]<br />
</span><span style="color: blue;">JOIN </span><span style="color: black;">sys.schemas s<br />
</span><span style="color: blue;">ON </span><span style="color: black;">s.[schema_id] </span><span style="color: blue;">= </span><span style="color: black;">o.[schema_id]<br />
</span><span style="color: blue;">WHERE </span><span style="color: black;">c.name </span><span style="color: blue;">= </span><span style="color: black;">ccu.COLUMN_NAME<br />
</span><span style="color: grey;">AND </span><span style="color: magenta;">OBJECT_NAME</span><span style="color: grey;">(</span><span style="color: black;">c.[object_id]</span><span style="color: grey;">) </span><span style="color: blue;">= </span><span style="color: black;">ccu.TABLE_NAME<br />
</span><span style="color: grey;">AND </span><span style="color: black;">s.name </span><span style="color: blue;">= </span><span style="color: black;">ccu.TABLE_SCHEMA<br />
</span><span style="color: grey;">AND </span><span style="color: black;">ic.key_ordinal </span><span style="color: blue;">= </span><span style="color: black;">1<br />
</span><span style="color: grey;">)<br />
AND </span><span style="color: black;">ccu.Table_Name </span><span style="color: grey;">NOT LIKE </span><span style="color: red;">'aspnet_%'</span></code><br />
<code style="font-size: 12px;"><span style="color: blue;">ORDER BY </span><span style="color: black;">ccu2.Constraint_Schema</span><span style="color: grey;">,<br />
</span><span style="color: black;">ccu2.Table_Name</span><span style="color: grey;">,<br />
</span><span style="color: black;">rc.Constraint_Schema</span><span style="color: grey;">,<br />
</span><span style="color: black;">ccu.Table_Name</span><span style="color: grey;">,<br />
</span><span style="color: black;">ccu.Column_Name</span><span style="color: grey;">;</span></code>Mark Freeman (@m60freeman)http://www.blogger.com/profile/11475488917130342878noreply@blogger.com4