tag:blogger.com,1999:blog-46161498921943681112024-03-17T19:59:03.250-07:00My Humble SQL TipsI'm keeping this blog as a repository for SQL tips and tricks I have learned over my time working with SQL Server.Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.comBlogger70125tag:blogger.com,1999:blog-4616149892194368111.post-48426027048226143712018-03-15T08:41:00.004-07:002022-08-05T09:33:51.900-07:00Querying the Execution Plan for Subtree Cost - Take 2<div class="separator" style="clear: both; text-align: center;">
</div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiW0q0RJq54e9YSnQ8PKIA5deXgbhLkqq51ZF1c5L_dauWvrxDDQ434rgbyMiZiXH06iDlMrtn_Cls6LoR6OJu5fjuoC1cdyi7G9XhDfhh7v0f-23Yrb9rBR2J896b4I9ibk-ge4C2kmeVj/s1600/blog_exec_plan.jpg" imageanchor="1" style="clear: right; display: inline !important; float: right; margin-bottom: 1em; margin-left: 1em; text-align: center;"><img border="0" data-original-height="332" data-original-width="612" height="173" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiW0q0RJq54e9YSnQ8PKIA5deXgbhLkqq51ZF1c5L_dauWvrxDDQ434rgbyMiZiXH06iDlMrtn_Cls6LoR6OJu5fjuoC1cdyi7G9XhDfhh7v0f-23Yrb9rBR2J896b4I9ibk-ge4C2kmeVj/s320/blog_exec_plan.jpg" width="320" /></a>A few weeks ago I shared a query for probing the execution plan for the <a href="http://myhumblesqltips.blogspot.co.uk/2018/01/querying-execution-plan-xml-for-subtree.html">Estimated Subtree Cost value</a>.<br />
<br />
That query was rather simple but it got the job done. In that post I also explained how, although there are several ways of finding missing index suggestions, they were not always very helpful. After posting I was thinking that it would be helpful to combine the two, or at least display the results so that it is easy to determine which execution plans include missing index warnings.<br />
<br />
<a name='more'></a><br />
I found the following query in a post by Jonathan Kehayias on <a href="https://www.sqlskills.com/blogs/jonathan/digging-into-the-sql-plan-cache-finding-missing-indexes/">sqlskills.com</a>. It is a good way of not only finding execution plans with missing index warnings, but displaying the index recommendations within.<br />
<br />
<span style="font-size: xx-small;"><pre class="brush: sql">
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT query_plan,
n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text,
n.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT') AS impact,
DB_ID(REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'),'[',''),']','')) AS database_id,
OBJECT_ID(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' +
n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' +
n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')) AS OBJECT_ID,
n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' +
n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' +
n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')
AS statement,
( SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'EQUALITY'
FOR XML PATH('')
) AS equality_columns,
( SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INEQUALITY'
FOR XML PATH('')
) AS inequality_columns,
( SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM n.nodes('//ColumnGroup') AS t(cg)
CROSS APPLY cg.nodes('Column') AS r(c)
WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INCLUDE'
FOR XML PATH('')
) AS include_columns
INTO #MissingIndexInfo
FROM
(
SELECT query_plan
FROM (
SELECT DISTINCT plan_handle
FROM sys.dm_exec_query_stats WITH(NOLOCK)
) AS qs
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp
WHERE tp.query_plan.exist('//MissingIndex')=1
) AS tab (query_plan)
CROSS APPLY query_plan.nodes('//StmtSimple') AS q(n)
WHERE n.exist('QueryPlan/MissingIndexes') = 1;
-- Trim trailing comma from lists
UPDATE #MissingIndexInfo
SET equality_columns = LEFT(equality_columns,LEN(equality_columns)-1),
inequality_columns = LEFT(inequality_columns,LEN(inequality_columns)-1),
include_columns = LEFT(include_columns,LEN(include_columns)-1);
SELECT *
FROM #MissingIndexInfo;
DROP TABLE #MissingIndexInfo;
</pre></span>
Obviously, I wanted to combine the above with my desire to find execution plans with an Estimated Subtree Cost of greater than x. So, I came up with the stored procedure below. I decided to make the query a stored procedure. As I was testing it I noticed that this query was actually becoming one of the most resource intensive queries. I obviously wanted to exclude it and the easiest way was to create it as a stored procedure and then delete any rows with this stored procedure in the ObjectName column of the temp table. Regular readers of my blog (are there any?) may remember that my team maintains a DBA database. In my current role we call it SYSDBA. It holds a number of stored procedures, functions and tables that are used for maintenance tasks and other regularly used queries that the DBA team needs. This stored proc will now enter our arsenal.<br />
<br />
Let me restate that my XML query skills are virtually non-existent. The query below was cobbled together with a lot of trial and error. I welcome any tips and feedback on making it tidier and more efficient. <b>This query will take a long time to run on large and/or busy databases.</b><br />
<br />
Finally, let me describe how to run the stored procedure and the result set returned.
<br />
The stored procedure has only 1 parameter: @Cost which is an integer. This is the lower threshold for the EstimatedSubtreeCost value. The stored procedure will find all execution plans that contain an EstimatedTotalSubtreeCost value of greater than @Cost.<br />
<br />
The stored procedure's result set is as follows:<br />
<br />
<table border="1">
<colgroup><col width="180"></col>
<col width="400"></col>
</colgroup><tbody>
<tr>
<th>Column</th>
<th>Description</th>
</tr>
<tr>
<td>query_plan</td>
<td>The full XML execution plan.</td>
</tr>
<tr>
<td>plan_handle</td>
<td>The identity of the execution plan.</td>
</tr>
<tr>
<td>cost</td>
<td>The Estimated Subtree Cost of at least one section of the plan.</td>
</tr>
<tr>
<td>DBName</td>
<td>The database name from where this query is run. It may not be the database where that the query affects. For example, if the stored proc is stored on DB1 and a statement within it access DB2, DB1 will be displayed.</td>
</tr>
<tr>
<td>objectid</td>
<td>If the query is a stored procedure an id will be displayed otherwise it will be null.</td>
</tr>
<tr>
<td>param_list</td>
<td>Declare statements for all the parameters listed within the execution plan.</td>
</tr>
<tr>
<td>sql_text</td>
<td>The sql statement or stored procedure within the execution plan.</td>
</tr>
<tr>
<td>Index_impact</td>
<td>Impact of any missing index. The value will be NULL if no index is suggested.</td>
</tr>
<tr>
<td>Index_database_id</td>
<td>The database id where for the missing index. The value will be NULL if no index is suggested.</td>
</tr>
<tr>
<td>Index_object_id</td>
<td>The object id (Table) for any missing index. The value will be NULL if no index is suggested.</td>
</tr>
<tr>
<td>Index_statement</td>
<td>The SQL statement that for the missing index. The value will be NULL if no index is suggested. The value will most probably be the same as the sql_text column value.</td>
</tr>
<tr>
<td>Index_equality_columns</td>
<td>The list of equality columns for any missing index. The value will be NULL if no index is suggested.</td>
</tr>
<tr>
<td>Index_inequality_columns</td>
<td>The list of inequality columns of any missing index. The value will be NULL if no index is suggested or if there are no inequality columns.</td>
</tr>
<tr>
<td>Index_include_columns</td>
<td>The list of included columns of any missing index. The value will be NULL if no index is suggested or if there are no included columns.</td>
</tr>
</tbody></table>
<br />
<br />
Finally, here is the stored procedure:
<br />
<span style="font-size: xx-small;"><pre class="brush: sql">
USE [SYSDBA];
GO
IF EXISTS
(
SELECT 1
FROM [sys].[procedures]
WHERE [name] = 'FindExpensiveExecutionPlans'
)
DROP PROC [dbo].[FindExpensiveExecutionPlans];
GO
/***********************************************************************************
Created by: Thomas Mucha
Created: 15/03/2018
Modified by:
Modified:
Description: Returns the XML execution plan for any plan that has a estimated subtree cost
higher than the set @Cost parameter value. The default is 100
***********************************************************************************/
CREATE PROC [dbo].[FindExpensiveExecutionPlans]
(@Cost INT = 100)
AS
BEGIN
SET NOCOUNT ON;
WITH XMLNAMESPACES
(
DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT [tab].[query_plan],
[tab].[plan_handle],
DB_NAME([dbid]) AS [DBName],
OBJECT_NAME([objectid]) AS [ObjectName],
[objectid],
[n].[value]('(//StmtSimple/@StatementText)[1]', 'VARCHAR(4000)') AS [sql_text],
[n].[value]('(//MissingIndexGroup/@Index__impact)[1]', 'FLOAT') AS [Index_impact],
DB_ID(REPLACE(REPLACE([n].[value]('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'), '[', ''), ']', '')) AS [Index_database_id],
(
SELECT DISTINCT
'DECLARE ' + ISNULL([c].[value]('(@Column)[1]', 'VARCHAR(128)'), '') + ' ['
+ ISNULL([c].[value]('(@ParameterDataType)[1]', 'VARCHAR(128)'), '') + ']' + ' = '
+ ISNULL([c].[value]('(@ParameterCompiledValue)[1]', 'VARCHAR(MAX)'), '') + '; '
FROM [n].nodes('//ParameterList') AS [t]([cg])
CROSS APPLY [cg].nodes('ColumnReference') AS [r]([c])
FOR XML PATH('')
) AS [param_list],
OBJECT_ID([n].[value]('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.'
+ [n].[value]('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.'
+ [n].[value]('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')
) AS [Index_OBJECT_ID],
[n].[value]('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.'
+ [n].[value]('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.'
+ [n].[value]('(//MissingIndex/@Table)[1]', 'VARCHAR(128)') AS [Index_statement],
(
SELECT DISTINCT
[c].[value]('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM [n].nodes('//ColumnGroup') AS [t]([cg])
CROSS APPLY [cg].nodes('Column') AS [r]([c])
WHERE [cg].[value]('(@Usage)[1]', 'VARCHAR(128)') = 'EQUALITY'
FOR XML PATH('')
) AS [Index_equality_columns],
(
SELECT DISTINCT
[c].[value]('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM [n].nodes('//ColumnGroup') AS [t]([cg])
CROSS APPLY [cg].nodes('Column') AS [r]([c])
WHERE [cg].[value]('(@Usage)[1]', 'VARCHAR(128)') = 'INEQUALITY'
FOR XML PATH('')
) AS [Index_inequality_columns],
(
SELECT DISTINCT
[c].[value]('(@Name)[1]', 'VARCHAR(128)') + ', '
FROM [n].nodes('//ColumnGroup') AS [t]([cg])
CROSS APPLY [cg].nodes('Column') AS [r]([c])
WHERE [cg].[value]('(@Usage)[1]', 'VARCHAR(128)') = 'INCLUDE'
FOR XML PATH('')
) AS [Index_include_columns],
[n].[value]('(@EstimatedTotalSubtreeCost)[1]', 'FLOAT') AS [Cost]
INTO [#MissingIndexInfo]
FROM
(
SELECT [query_plan],
[plan_handle],
[dbid],
[objectid]
FROM
(
SELECT DISTINCT
[plan_handle]
FROM [sys].[dm_exec_query_stats] WITH (NOLOCK)
) AS [qs]
OUTER APPLY [sys].dm_exec_query_plan([qs].[plan_handle]) [tp]
WHERE [tp].[query_plan].[exist]('//RelOp[
@EstimatedTotalSubtreeCost > sql:variable("@Cost")
]') = 1
) AS [tab]([query_plan], [plan_handle], [dbid], [objectid])
CROSS APPLY [query_plan].nodes('//RelOp') AS [q]([n])
WHERE [n].[value]('(@EstimatedTotalSubtreeCost)[1]', 'FLOAT') > @Cost;
CREATE CLUSTERED INDEX [IX_MissingIndexInfo_planhandle]
ON [#MissingIndexInfo] ([plan_handle]);
DELETE FROM [#MissingIndexInfo]
WHERE [ObjectName] = 'FindExpensiveExecutionPlans';
-- Trim trailing comma from lists
UPDATE [#MissingIndexInfo]
SET [Index_equality_columns] = LEFT([Index_equality_columns], LEN([Index_equality_columns]) - 1),
[Index_inequality_columns] = LEFT([Index_inequality_columns], LEN([Index_inequality_columns]) - 1),
[Index_include_columns] = LEFT([Index_include_columns], LEN([Index_include_columns]) - 1);
SELECT CONVERT(XML, [b].[query_plan]) AS [query_plan],
[a].[plan_handle],
[cost],
[DBName],
[objectid],
[param_list],
[sql_text],
[Index_impact],
[Index_database_id],
[Index_OBJECT_ID],
[Index_statement],
[Index_equality_columns],
[Index_inequality_columns],
[Index_include_columns]
FROM
(
SELECT [DBName],
[objectid],
[plan_handle],
[sql_text],
[Index_impact],
[param_list],
[Index_database_id],
[Index_OBJECT_ID],
[Index_statement],
[Index_equality_columns],
[Index_inequality_columns],
[Index_include_columns],
MAX([Cost]) AS [cost]
FROM [#MissingIndexInfo]
GROUP BY [DBName],
[objectid],
[plan_handle],
[sql_text],
[Index_impact],
[param_list],
[Index_database_id],
[Index_OBJECT_ID],
[Index_statement],
[Index_equality_columns],
[Index_inequality_columns],
[Index_include_columns]
) AS [a]
JOIN
(
SELECT DISTINCT
CONVERT(VARCHAR(MAX), [query_plan]) AS [query_plan],
[plan_handle]
FROM [#MissingIndexInfo]
) AS [b]
ON [a].[plan_handle] = [b].[plan_handle]
ORDER BY [cost] DESC;
DROP TABLE [#MissingIndexInfo];
END;
</pre></span>
<br />
You can download it from my <a href="https://drive.google.com/file/d/1QXPkixYFRWl4s3NDzQH9gA8KP7Weh8cF/view?usp=sharing">Google Drive</a> as well.Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com1tag:blogger.com,1999:blog-4616149892194368111.post-15612117049214293412018-01-19T01:11:00.002-08:002018-12-07T06:55:31.727-08:00Querying the Execution Plan XML for Subtree CostThere are quite a lot of great tools and queries available to the DBA that aid in troubleshooting and performance tuning. The list is incredibly long, but I mainly use RedGate's SQL Monitor (which has come on in the last 5 years in leaps and bounds), <a href="https://www.sqlskills.com/blogs/glenn/category/dmv-queries/" target="_blank">Glenn Berry's DMV queries</a>, <a href="http://whoisactive.com/" target="_blank">Adam Machanic's sp_whoisactive</a> and <a href="https://www.brentozar.com/first-aid/" target="_blank">Brent Ozar Unlimited's sp_Blitz suite</a> (predominantly sp_Blitz and sp_BlitzIndex). In writing this, I see that Brent Ozar has expanded his offering, so it looks like I have a weekend project ahead of me!<br />
<br />
<a name='more'></a><br />
The one thing I had trouble finding was a way to query execution plans based on an Estimated Subtree Cost value. There is a missing index DMV and Glenn Berry has a good query that finds execution plans with a missing index warning.<br />
<br />
The trouble with this is that these warnings and missing index suggestions:<br />
<ol>
<li>Aren't always raised when a query needs an index; </li>
<li>They are a bit of a blunt instrument, i.e. other similar indexes tend not to be considered. For example, if there is another similar query that is just missing an included column, SQL Server will suggest a brand new index; </li>
<li>Most relevant to this blog post, SQL Server will claim an index is missing for a query that has an extremely low Estimated Subtree Cost. While it may be technically true that an index may help, the performance gain for the query will be negligible and the index maintenance overhead may outweigh that gain anyway.</li>
</ol>
<br />
RedGate SQL Monitor now allows you to find expensive queries for a particular time period and you can now display and download individual execution plans. While this is very helpful for identifying certain problem areas, it is very slow if you have a longer list of queries to sift through.<br />
<br />
I was looking for a way to query the XML of the execution plans held in sys.dm_exec_query_plan DMV (this one is really a DMF). Part of my problem has been that my XML querying skills are very poor. It's due to equal measures of laziness and lack of understanding. But I finally put together the proper search string and the Internet revealed to me a question and answer about querying execution plans: <a href="https://stackoverflow.com/questions/17572261/how-to-filter-xml-execution-plan-data-in-a-where-clause-using-tsql" target="_blank">How to filter XML execution plan data in a WHERE clause using TSQL</a> on Stack Overflow.<br />
<br />
<span style="font-size: xx-small;"><pre class="brush: sql">
declare @IndexName nvarchar(100) = '[MyIndex]';
with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select object_name(qp.objectid),
cp.usecounts,
qp.query_plan
from sys.dm_exec_cached_plans as cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) as qp
where cp.objtype = 'Proc' and
qp.query_plan.exist('//RelOp[
@PhysicalOp = "Index Seek" and
IndexScan/Object/@Index = sql:variable("@IndexName")
]') = 1;
</pre></span>
<br />
The above code needed to be modified a little to achieve what I needed. Basically, the parameter needed to be changed to an INT, but I also changed its name for clarity's sake as well as adding a few columns to provide some additional information. Here's my version:
<br />
<br />
<span style="font-size: xx-small;"><pre class="brush: sql">
--Find execution plans with high Estimated subtree cost
declare @Cost int
set @Cost = 100;
with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select top 10 qp.query_plan, cp.usecounts,objectid, object_name(objectid) as objectname, db_name([dbid]) as DBName
from sys.dm_exec_cached_plans as cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) as qp
where /*cp.objtype = 'Proc'
--and dbid = db_id()
and*/ qp.query_plan.exist('//RelOp[
@EstimatedTotalSubtreeCost > sql:variable("@Cost")
]') = 1
ORDER BY cp.usecounts desc
</pre></span>
Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com0tag:blogger.com,1999:blog-4616149892194368111.post-9284576521539162062018-01-02T03:25:00.000-08:002018-12-07T06:57:13.498-08:00SQL 2016 Database Scoped Configuration: MAXDOPSQL Server 2016 Microsoft expanded the range and flexibility of <a href="https://www.mssqltips.com/sqlservertip/4245/sql-server-2016-database-scoped-configuration-options/" target="_blank">database level configuration settings</a>. There are some new features, as well as features which that can be set at more than just the server or database scope. Now, among other things, we are able to:
<br />
<ul>
<li>set certain options both at the instance and the database level (<a href="https://www.mssqltips.com/sqlservertip/4266/sql-server-2016-database-scoped-configurations-for-maxdop-procedure-cache-and-cross-database-queries/" target="_blank">MAXDOP</a>)</li>
<li>set instance level trace flag settings at the database level (<a href="https://docs.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server" target="_blank">LEGACY_CARDINALITY_ESTIMATION</a>)</li>
<li>the Trace Flag 1117 is now set at the <a href="https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-file-and-filegroup-options" target="_blank">FILEGROUP LEVEL</a></li>
<li>the brand new database level <a href="https://www.sqlshack.com/sql-server-2016-database-scoped-configuration/" target="_blank">CLEAR PROCEDURE_CACHE</a></li>
</ul>
<div>
A summary of the new options can be found on the <a href="https://social.technet.microsoft.com/wiki/contents/articles/34718.new-sql-server-2016-scoped-configuration.aspx" target="_blank">Technet blog</a>.<br />
<br />
In this post I am going to focus on the MAXDOP feature. Having read a few articles and posts about this feature it wasn't clear to me exactly how this feature worked. Especially in regard to the relationship between the instance level setting and the database level setting.</div>
<div>
<br />
<a name='more'></a><br />
The short answer is:</div>
The query optimiser will use the instance level setting when the database setting is 0, otherwise the database setting overrides the instance setting. I will demonstrate this below.<br />
<br />
I'm using a VM with 4 vCPUs. Below I set up 4 different scenarios to illustrate how the various settings affect CPU usage.<br />
<br />
<b>Scenario 1:
</b><br />
SQL Server Instance MAXDOP set to max and database scoped MAXDOP set to 0 (or inactive).<br />
<span style="font-size: xx-small;"><pre class="brush: sql">
/******SET SERVER MAXDOP TO MAX******/
USE [master]
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max degree of parallelism', N'0' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
/******SET DATABASE MAXDOP TO INACTIVE (SERVER SCOPE)******/
USE [Test_DB]
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
--Parallel query
select *
from [dbo].[Big_Wide_Table_Archive] as a
join [dbo].[Big_Wide_Table] as b on a.ID = b.ID
]]>
</pre></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDciPr-lwT2QK9Emchqg4KHYew8OEFmz9BwFUFZEQGdSa2hV_D8sEXnnG8g8KYjCuEKuPvyQ_fy_n-jSOUR07YhCDOkOcSuuFwKTEASEBfr8O2Ffkl-miOpw6sBMCNOpmr6Sou57PGmp0z/s1600/MAXDOP+Scenario+1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="260" data-original-width="417" height="199" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDciPr-lwT2QK9Emchqg4KHYew8OEFmz9BwFUFZEQGdSa2hV_D8sEXnnG8g8KYjCuEKuPvyQ_fy_n-jSOUR07YhCDOkOcSuuFwKTEASEBfr8O2Ffkl-miOpw6sBMCNOpmr6Sou57PGmp0z/s320/MAXDOP+Scenario+1.JPG" width="320" /></a></div>
<br />
<b>Scenario 2:
</b><br />
SQL Server Instance MAXDOP set to max and database scoped MAXDOP set to 2.<br />
<span style="font-size: xx-small;"><pre class="brush: sql">
/******SET SERVER MAXDOP TO MAX******/
USE [master]
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max degree of parallelism', N'0' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
/******SET DATABASE MAXDOP TO 2******/
USE [Test_DB]
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2;
--Parallel query
select *
from [dbo].[Big_Wide_Table_Archive] as a
join [dbo].[Big_Wide_Table] as b on a.ID = b.ID
</pre></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikpkovTM5HIPKqylx7XKjZgqNvjCvMieMpkGqHw4G8UlyD80uDDGOSoYleHflM_GYSueR06M9atDXm2zx4x6CigAu2lZFQI5ZgE-LZogKxBvaFkVL2FTMpQ3pUBMTXhVjnDNRVrZNt-Fl2/s1600/MAXDOP+Scenario+2.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="259" data-original-width="410" height="202" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikpkovTM5HIPKqylx7XKjZgqNvjCvMieMpkGqHw4G8UlyD80uDDGOSoYleHflM_GYSueR06M9atDXm2zx4x6CigAu2lZFQI5ZgE-LZogKxBvaFkVL2FTMpQ3pUBMTXhVjnDNRVrZNt-Fl2/s320/MAXDOP+Scenario+2.JPG" width="320" /></a></div>
<br />
<b>Scenario 3:
</b><br />
SQL Server Instance MAXDOP set to 1 and database scoped MAXDOP set to 0 (or inactive).
<span style="font-size: xx-small;"><pre class="brush: sql">
/******SET SERVER MAXDOP TO 1******/
USE [master]
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max degree of parallelism', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
/******SET DATABASE MAXDOP TO INACTIVE (SERVER SCOPE)******/
USE [Test_DB]
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
--Parallel query
select *
from [dbo].[Big_Wide_Table_Archive] as a
join [dbo].[Big_Wide_Table] as b on a.ID = b.ID
</pre></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAuAwC9_w__DKpfb1oGQIaD8E8DTAltGoprl8R9l07ycE0BrXbS50AyGZC-yfhodMUs2KYq6JpjOj_gpuSSm3dFIQ4FE8b2gGhxr3ZUFZXHRh8cZc9-q8PVOAbjRsFTUqmmAcCKRkgrnSd/s1600/MAXDOP+Scenario+3.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="267" data-original-width="423" height="201" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAuAwC9_w__DKpfb1oGQIaD8E8DTAltGoprl8R9l07ycE0BrXbS50AyGZC-yfhodMUs2KYq6JpjOj_gpuSSm3dFIQ4FE8b2gGhxr3ZUFZXHRh8cZc9-q8PVOAbjRsFTUqmmAcCKRkgrnSd/s320/MAXDOP+Scenario+3.JPG" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<b>Scenario 4:
</b><br />
SQL Server Instance MAXDOP set to 1 and database scoped MAXDOP set to 4.
<span style="font-size: xx-small;"><pre class="brush: sql" >
/******SET SERVER MAXDOP TO 1******/
USE [master]
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max degree of parallelism', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
/******SET DATABASE MAXDOP TO 4******/
USE [Test_DB]
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;
--Parallel query
select *
from [dbo].[Big_Wide_Table_Archive] as a
join [dbo].[Big_Wide_Table] as b on a.ID = b.ID
</pre></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhbyoEbYRVLWUuoW0mMHtjoT8t08Ud06ZwzZ8wK5T5i_jH-Rk9LJq3ho579qUm95u23x80TkRV246-ldY_bKeJ9xXUNdST5i2X3P8zDqGDlLGqWHmlbOYrf1Uz2-4Vb6GGKtpBaCPYxB-98/s1600/MAXDOP+Scenario+4.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="256" data-original-width="413" height="198" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhbyoEbYRVLWUuoW0mMHtjoT8t08Ud06ZwzZ8wK5T5i_jH-Rk9LJq3ho579qUm95u23x80TkRV246-ldY_bKeJ9xXUNdST5i2X3P8zDqGDlLGqWHmlbOYrf1Uz2-4Vb6GGKtpBaCPYxB-98/s320/MAXDOP+Scenario+4.JPG" width="320" /></a></div>
<br />
I hope the above has shed some light on how this feature works. I look forward to using this feature. I am a fan of aggregating databases as much as is practical and this will certainly help as performance tuning can be that much more granular.Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com2tag:blogger.com,1999:blog-4616149892194368111.post-39473357789658237752017-09-18T08:43:00.001-07:002018-12-07T06:59:13.186-08:00Master Data Services Model CloningYes! My company uses Master Data Services or MDS. Yes! It's a pain. And yes! I needed to learn how to use and administer it. For anyone that hasn't had this well meant tool forced upon on them, it feels ... (if I'm being kind) ... a little unfinished.<br />
<br />
Based on my experience MDS administration requires an awkward mix of command line tools, the MDS web interface, and even some sql scripts and stored procs executed directly on the database. There are a few strange quirks, like the fact that individual users or groups can be set as administrators, but there is a single super-user. This super-user can only be an individual and not an AD group (although I'll be happy to be proved wrong on this!). Users in the System Administration role can deploy models, but still need to be given permissions to view and edit those models by the super-user (this has <a href="https://docs.microsoft.com/en-us/sql/master-data-services/what-s-new-in-master-data-services-mds#improved-security" target="_blank">changed in SQL 2016</a> - multiple super-users are supported as well as AD groups are now supported as super-users).<br />
<a name='more'></a><br />
The following article explains a certain quirk regarding deploying models across environments in order to allow for ongoing development: <a href="http://www.sqlchick.com/entries/2015/3/16/how-to-deploy-master-data-services-models-between-environments" target="_blank">How to Deploy Master Data Services Models Between Environments</a>.<br />
<br />
The development team at my company actively maintains and develops their BI solution with MDS as a core component. I have therefore created a PowerShell script to aid in deploying and synchronising models across MDS environments. My goal was to create a script that did not require any MDS knowledge. However, it will help to have a basic understanding of how MDS works and is deployed. The script performs and supports the following:<br />
<ul>
<li>Multiple models can be migrated. </li>
<li>Multiple AD Groups and/or users can be given update permissions to the models.</li>
<li>Creates model packages (pkg files) from source MDS service and saves them to the local MDS directory specifying the -includedata switch. </li>
<li>Copies the model packages to a central directory (or back up directory)</li>
<li>Cleans up the various directories (backup, local MDS on source and destination servers)</li>
<li>Copies the packages from the central directory (or back up directory) to the destination server</li>
<li>Deploys clones of the models to the destination server</li>
<li>Assigns Update permissions to the defined groups and/or users </li>
</ul>
<div>
There are some restrictions / limitations:<br />
<ul>
<li>If migrating multiple models they all need to use the same version. There is a parameter for the -version switch of the <i>MDSModelDeploy createpackage </i>command but the same value will be used for each model. If you need to deploy multiple models with different versions the script will need to be run multiple times.</li>
<li>The MDS Service name parameter value is also the same for all models being migrated. </li>
<li>The AD Groups and users will already need to exist in MDS. If they are not there, the permissions part of the script will fail. I aim to add a check for the existence of the users and groups. </li>
<li>I have tested this using PowerShell version 5. I have no reason to believe it wouldn't work in version 4, but I have not tested that.</li>
<li>I have only tested this script against SQL Server 2012 and 2014. </li>
</ul>
The basic prerequisites are:</div>
<ul>
<li>The script is designed to run remotely so the machine the script is run on must have access to all servers and directories.</li>
<li>The credentials under which the script is run must have all the necessary permissions on the remote servers and directories. I have tested the script as a system administrator on all servers and with full control on the file share directory used for backups.</li>
<li>The credentials under which the script is run must have at least db_owner permissions on the MDS database. I have tested this as a sys_admin on the database servers. </li>
<li>The credentials under which the script is run must have permission to access the System Administration functional area in the target Master Data Services environment.</li>
</ul>
<br />
A downloadable version of the script is on my <a href="https://drive.google.com/open?id=0B-znT8ogfZ3LUGd3WjJCenZtU3c" target="_blank">Google Drive</a>.<span style="font-size: xx-small;"><pre class="brush: powershell" >
<########################################################################################################################################################################
Created by: Thomas Mucha
Created: 2017-09-14
Description: This script allows you to clone models with data from one MDS server to another. The CLONE funtion preserves the Models' MUID values. Cloning models allows
for future model updates.
See http://www.sqlchick.com/entries/2015/3/16/how-to-deploy-master-data-services-models-between-environments for a good explanation of MDS deployment options
In order to run this script
########################################################################################################################################################################>
cls
<#############################################>
<###### Modify the following parameters ######>
[string[]]$Models = ('Model1','Model2','Model3','Model4','Model5') ;
$SourceServer = "SourceServerInstance" ;
$DestinationServer = "DestinationServerInstance" ;
$MDSDatabaseName = "MDS_Database_Name" ;
$PKGBackupPath = "\\BackupDirectory\$SourceServer\MDS1"; #This directory must be accessible by both Source and Destination servers
[int]$BackupPKGCleanupPeriod = -30 ; #File age of backup PKG files in days. Any files accessed before (Get-Date).AddDays($BackupPathCleanupPeriod)} will be deleted
[int]$SourcePKGCleanupPeriod = -30 ; #File age of locally saved PKG files in days. Any files accessed before (Get-Date).AddDays($BackupPathCleanupPeriod)} will be deleted
[int]$DestinationPKGCleanupPeriod = -30 ; #File age of locally saved PKG files in days. Any files accessed before (Get-Date).AddDays($BackupPathCleanupPeriod)} will be deleted
$MDSServiceName = "MDS1"
$MDSModelVersion = "VERSION_1"
$DeleteModelsOnDestinationServer = "No" #"Yes" to delete any existing models from the destination server with the same name as those on the source server
#"No" Prints warning that models with the same name exist on both the source and the destination servers. Script exits.
[string[]]$GroupList = ("DomainName\ADGroup1","DomainName\ADGroup2"); # Groups and Users must already exist in MDS in order for the permissions to be set.
[string[]]$UserList = ($null); #Leave NULL if no users are to be added. Only use the user list when necessary, the permissions should be handled by groups
<######## Modify the above parameters ########>
<#############################################>
[string]$date = Get-Date -format s;
$date = $date -replace "-", "";
$date = "_$date" -replace ":", "";
##Delete Models from Destination server
If ($DeleteModelsOnDestinationServer -eq "No")
{
[String[]]$ExistingModels = $null
foreach ($Model in $Models)
{
$ExistingModels = $ExistingModels + (Invoke-Sqlcmd -Query "select name from mdm.tblmodel where name = '$Model';" -ServerInstance $DestinationServer -Database $MDSDatabaseName -QueryTimeout 0).name;
}
If ($ExistingModels.Length -gt 1)
{
Write-Warning "Model(s) $ExistingModels exist on $DestinationServer. Migration has been halted!"
Break
}
}
If ($DeleteModelsOnDestinationServer -eq "Yes")
{
Write-Verbose "Deleting any existing models on $DestinationServer" -Verbose
foreach ($Model in $Models)
{
Invoke-Sqlcmd -Query "declare @muid uniqueidentifier; select @muid = MUID from mdm.tblmodel where name = '$Model';
if @muid is not null
begin
exec mdm.udpModelDeleteByMUID @Model_MUID= @muid
end;" -ServerInstance $DestinationServer -Database $MDSDatabaseName -QueryTimeout 0;
Write-Verbose "$Model has been found and deleted on $DestinationServer" -Verbose
};
};
Write-Verbose "Determining MDS version and directory on destination server: $DestinationServer" -Verbose
$RemoteDestMDSPath = (Get-ChildItem -Recurse -Directory "\\$DestinationServer\c$\Program Files" | where { $_.FullName -like "*Master Data Services*Configuration" }).FullName ;
Write-Verbose "Determining MDS version and directory on source server: $SourceServer" -Verbose
$RemoteSourceMDSPath = (Get-ChildItem -Recurse -Directory "\\$SourceServer\c$\Program Files" | where { $_.FullName -like "*Master Data Services*Configuration" }).FullName ;
$LocalSourceMDSPath = $RemoteSourceMDSPath.Replace("\\$SourceServer\c$", "c:");
$LocalDestinationMDSPath = $RemoteDestMDSPath.Replace("\\$DestinationServer\c$", "c:");
##Cleanup any old Model pkg files - any "pkg" files older than 30 days will be deleted
Write-Verbose "Testing for and creating backup directory" -Verbose
if (-Not (Test-Path -Path $PKGBackupPath))
{ New-Item $PKGBackupPath -Type directory };
Write-Verbose "Cleaning up PKG files from backup directory" -Verbose
Get-ChildItem $PKGBackupPath | where {$_.Extension -eq '.pkg' -and $_.LastWriteTime -lt (Get-Date).AddDays($BackupPKGCleanupPeriod)} | Remove-Item ;
Write-Verbose "Cleaning up PKG files from $RemoteSourceMDSPath directory" -Verbose
Get-ChildItem $RemoteSourceMDSPath | where {$_.Extension -eq '.pkg' -and $_.LastWriteTime -lt (Get-Date).AddDays($SourcePKGCleanupPeriod)} | Remove-Item ;
Write-Verbose "Cleaning up PKG files from $RemoteDestMDSPath directory" -Verbose
Get-ChildItem $RemoteDestMDSPath | where {$_.Extension -eq '.pkg' -and $_.LastWriteTime -lt (Get-Date).AddDays($DestinationPKGCleanupPeriod)} | Remove-Item
##Create packages from source models
Write-Verbose "Creating Model Packages on source server: $SourceServer" -Verbose
foreach ($Model in $Models)
{
$CMD = "MDSModelDeploy createpackage -model $Model -version $MDSModelVersion -service $MDSServiceName -package $Model$date -includedata"
invoke-command -ComputerName $SourceServer -ArgumentList $CMD,$LocalSourceMDSPath -ScriptBlock {
param ($CMD,$LocalSourceMDSPath)
Set-Location $LocalSourceMDSPath
cmd /c $CMD
};
};
##Move pkg files to destination server via the Backup directory
Write-Verbose "Copying Model Packages to $PKGBackupPath" -Verbose
Copy-Item "$RemoteSourceMDSPath\*$Date.pkg" "$PKGBackupPath";
Write-Verbose "Copying Model Packages to $RemoteDestMDSPath" -Verbose
Copy-Item "$PKGBackupPath\*$Date.pkg" "$RemoteDestMDSPath";
##Deploy clones of the packages to the destination server
Write-Verbose "Deploying Model Packages as clones on destination server: $DestinationServer" -Verbose
foreach ($Model in $Models)
{
$filename = (Get-ChildItem $RemoteDestMDSPath | where {$_.Name -Like "$Model$Date.pkg"}).Name
$CMD = "MDSModelDeploy deployclone -package ""$filename"" -service $MDSServiceName"
invoke-command -ComputerName $DestinationServer -ArgumentList $CMD,$LocalDestinationMDSPath -ScriptBlock {
param ($CMD,$LocalDestinationMDSPath)
$LocalDestinationMDSPath
Set-Location $LocalDestinationMDSPath
cmd /c $CMD
};
};
##Add permissions to newly deployed models
Write-Verbose "Setting update permissions for groups and/or users" -Verbose
foreach ($Model in $Models)
{
foreach ($Group in $GroupList)
{
Invoke-Sqlcmd -ServerInstance $DestinationServer -Database MDS_MasterData -Query "declare @groupid int;
declare @groupmuid uniqueidentifier;
declare @groupname nvarchar(100);
declare @modelid uniqueidentifier;
declare @modelname nvarchar(100);
select @groupid = ID, @groupmuid = MUID, @groupname = [Name]
from mdm.tblUserGroup
where [Name] = '$Group'
select @modelid = MUID, @modelname = [Name]
from mdm.tblModel
where [Name] = '$Model'
declare @userperms table (RoleAccess_ID int, RoleAccess_MUID uniqueidentifier, Privelege_ID int, Privelege_Name nvarchar(100), ObjectType_ID int, ObjectType_NAME nvarchar(100), Securable_ID int, Securable_MUID uniqueidentifier, Securable_Name nvarchar(100), Model_ID int, Model_MUID uniqueidentifier, Model_Name nvarchar(100), SourceUserGroup_ID int, SourceUserGroup_MUID uniqueidentifier, SourceUserGroup_Name nvarchar(100), IsModelAdministrator int)
insert into @userperms
exec mdm.udpSecurityPrivilegesSummaryGet @SystemUser_ID=1,@Principal_ID=@groupid,@PrincipalType_ID=1,@IncludeGroupAssignments=1,@Model_ID=NULL
if not exists (select 1 from @userperms where ObjectType_ID = 1 and Securable_Name = @modelname and Privelege_ID = 2)
if not exists (select 1 from @userperms where ObjectType_ID = 1 and Securable_Name = @modelname)
begin
declare @p14 bigint
declare @p15 uniqueidentifier
exec mdm.udpSecurityPrivilegesSaveByMUID @SystemUser_ID=1
,@Principal_MUID=@groupmuid
,@PrincipalType_ID=2
,@Principal_Name=@groupname
,@RoleAccess_MUID='00000000-0000-0000-0000-000000000000'
,@Object_ID=1
,@Privilege_ID=2
,@Model_MUID=@modelid
,@Model_Name=@modelname,@Securable_MUID=@modelid,@Securable_Name=@modelname,@Status_ID=0,@Return_ID=@p14 output,@Return_MUID=@p15 output
end"
}
}
if ($UserList.Length > 0)
{
foreach ($Model in $Models)
{
foreach ($User in $UserList)
{
Invoke-Sqlcmd -ServerInstance $DestinationServer -Database MDS_MasterData -Query "declare @userid int;
declare @usermuid uniqueidentifier;
declare @username nvarchar(100);
declare @modelid uniqueidentifier;
declare @modelname nvarchar(100);
select @userid = ID, @usermuid = MUID, @username = UserName
from mdm.tbluser
where UserName = '$User'
select @modelid = MUID, @modelname = [Name]
from mdm.tblModel
where [Name] = '$Model'
declare @userperms table (RoleAccess_ID int, RoleAccess_MUID uniqueidentifier, Privelege_ID int, Privelege_Name nvarchar(100), ObjectType_ID int, ObjectType_NAME nvarchar(100), Securable_ID int, Securable_MUID uniqueidentifier, Securable_Name nvarchar(100), Model_ID int, Model_MUID uniqueidentifier, Model_Name nvarchar(100), SourceUserGroup_ID int, SourceUserGroup_MUID uniqueidentifier, SourceUserGroup_Name nvarchar(100), IsModelAdministrator int)
insert into @userperms
exec mdm.udpSecurityPrivilegesSummaryGet @SystemUser_ID=1,@Principal_ID=@userid,@PrincipalType_ID=1,@IncludeGroupAssignments=1,@Model_ID=NULL
if not exists (select 1 from @userperms where ObjectType_ID = 1 and Securable_Name = @modelname and Privelege_ID = 2)
if not exists (select 1 from @userperms where ObjectType_ID = 1 and Securable_Name = @modelname)
begin
declare @p14 bigint
declare @p15 uniqueidentifier
exec mdm.udpSecurityPrivilegesSaveByMUID @SystemUser_ID=1
,@Principal_MUID=@usermuid
,@PrincipalType_ID=1
,@Principal_Name=@username
,@RoleAccess_MUID='00000000-0000-0000-0000-000000000000'
,@Object_ID=1
,@Privilege_ID=2
,@Model_MUID=@modelid
,@Model_Name=@modelname,@Securable_MUID=@modelid,@Securable_Name=@modelname,@Status_ID=0,@Return_ID=@p14 output,@Return_MUID=@p15 output
end"
}
}
}
</pre></span>Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com1tag:blogger.com,1999:blog-4616149892194368111.post-27544985725344053202017-08-04T04:16:00.000-07:002018-12-07T07:00:21.010-08:00Check for SQL Server ConnectivityMy company is revamping its domain structure and tightening up network security. I am one of the guinea pigs and therefore have been put into the new domain and OU groups. Obviously, we are experiencing some teething problems. As a result I can't access things, namely SQL Server instances that I used to be able to connect to from my work station.<br />
<br />
I needed to provide the network team a list of the instances I could no longer access and I certainly wasn't going to try to connect to each one manually. We have a very large estate and it would have taken me ages.<br />
<br />
<a name='more'></a>As is the case more and more lately, I turned to PowerShell. The script below does the following:<br />
<ol>
<li>Connects to our asset management database, Landesk, to get a list of the VM names that host SQL Server instances. </li>
<li>Loops through and attempts to execute a simple query on each</li>
<li>Captures when there is a "network-related or instance specific" error</li>
<ul>
<li>Alternative 1: Print only the server name that cannot be accessed</li>
<li>Alternative 2: Also print the successful server names</li>
</ul>
</ol>
<div>
My life is made easy in that very few SQL Servers in the estate use named instances. But I am also working under the assumption that if I can't access the default instance I won't be able to access any named instances on the same host VM.<br />
<br />
An important part of this PowerShell script is to set the ErrorActionPreference global parameter to SilentlyContinue. That way you will get a clean list of server names and the script won't abort on an error.<br />
<br />
I've added a filter to the If statement, so that it will only report when a "network specific" error is returned. This could easily be changed to any other error string you need to catch.<br />
<br />
I have also created a parameter to capture the results of the query. I do not want to see the results of successful query executions. It would only make the server list difficult to read.<br />
<br />
Finally, the only difference to the two variations I described above is the Else statement. If all you want is a list of inaccessible servers, remove the Else statement.<br />
<br /></div>
<span style="font-size: xx-small;">
<pre class="brush: powershell" type="syntaxhighlighter">
cls
$ErrorActionPreference = "SilentlyContinue"
<############################################################
For the list of servers either:
Use Landesk or other CMDB by modifying the where clause in
the below query
or
Modify the list of the $Servers parameter. Enter either 1 or
more server names
Comment out the one you don't need
############################################################>
<#### LANDESK QUERY ####>
#$Servers = (Invoke-Sqlcmd -ServerInstance LanDeskSQLServer01 -Database Landesk -Query "select DeviceName DeviceName from dbo.Computer where DeviceName like '%sql%'" -ErrorVariable ProcessError).DeviceName
<#### LANDESK QUERY ####>
<#### MANUAL SERVER LIST ####>
[string[]]$Servers = ("SQLServer01","SQLServer02")
<#### MANUAL SERVER LIST ####>
foreach ($Server in $Servers)
{
$TestConnectivity = Invoke-Sqlcmd -ServerInstance $Server -Database master -Query "select @@version as version" -ErrorVariable ProcessError
If ($ProcessError -like "*A network-related*")
{
Write-Warning -Message $Server
}
Else
{
Write-Verbose $Server -Verbose
}
}
$ErrorActionPreference = "Stop"
</pre>
</span>Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com0tag:blogger.com,1999:blog-4616149892194368111.post-13553031627811930822017-07-28T08:01:00.000-07:002018-12-07T07:03:57.116-08:00Cleanup SQL Server LoginsCleaning up after someone leaves. We all do it right? Our companies all have water tight policies and procedures to handle that, or maybe they have policies but not procedures. Or maybe they just kind of don't.<br />
<br />
Well, if your company is anything like most of the places I've worked, it's somewhere in the middle. And from a DBA perspective, especially on non-production environments, your list of logins and users becomes a bit long and messy after a while.<br />
<br />
<a name='more'></a><br />
I've put together the PowerShell script below to get a list of Active Directory accounts as SQL logins and then checks for their existence in Active Directory. The script then generates SQL to drop users and logins. The SQL is not executed automatically in order to allow you to confirm whether those users and logins really can be dropped.<br />
<br />
The script can check multiple SQL instances at once. Either by interrogating an asset database. My company uses Landesk. Alternatively, a list of instance names can be entered manually.<br />
<br />
In order for the script to run successfully, the user must have permissions and access to all the SQL Server instances listed and read permissions on the Active Directory server. You'll also need to have the Powershell Active Directory module and SQLPS installed on the client machine. To install the Active Directory module see:<br />
<br />
<ul>
<li>Windows 10: <a href="https://gallery.technet.microsoft.com/Install-the-Active-fd32e541" target="_blank">Install the Active Directory PowerShell Module on Windows 10</a></li>
<li>Windows 7: <a href="https://blogs.msdn.microsoft.com/rkramesh/2012/01/17/how-to-add-active-directory-module-in-powershell-in-windows-7/" target="_blank">How to add Active Directory module in PowerShell in Windows 7</a></li>
</ul>
<div>
<b>UPDATE</b>: Based on some feedback I've altered the script to use the Windows login's SID instead of name. This allows for any name changes. I've also added a Domain parameter so that if you have users from other trusted domains the script will only find the users from your preferred domain. By default the script uses the $env:USERDOMAIN environment variable. But the parameter can be changed as needed.</div>
<span style="font-size: xx-small;">
<pre class="brush: powershell" >
cls
$ADServer = "ADServer01"
$Domain = $env:USERDOMAIN
$opt = (Get-Host).PrivateData
$opt.WarningForegroundColor = "DarkRed"
$opt.WarningBackgroundColor = "Yellow"
$opt.VerboseForegroundColor = "White"
$opt.VerboseBackgroundColor = "Green"
<#### LANDESK QUERY ####>
#$Servers = (Invoke-Sqlcmd -ServerInstance LandeskServer -Database Landesk -Query "SELECT DeviceName from computer where DeviceName like '%sql%D' and DomainName like 'DomainName%'").DeviceName
<#### LANDESK QUERY ####>
<#### MANUAL SERVER LIST ####>
[string[]]$Servers = ("SQLServer01","SQLServer02")
<#### MANUAL SERVER LIST ####>
foreach ($Server in $Servers)
{
$FailedUsers = ""
$DropUserQuery = ""
Try
{
$users = (Invoke-Sqlcmd -ServerInstance $Server -Database master -QueryTimeout 0 -ErrorAction SilentlyContinue -Query "
DECLARE @users table (Name sysname)
declare @command varchar(4000)
set @command = 'USE [?];
insert into @users
select substring(name,charindex(''\'',name,1)+1,500) as Name
from sysusers
where isntuser = 1
and name like ''$Domain%''
and name not like ''NT %'''
insert into @users
select substring(name,charindex('\',name,1)+1,500) as Name
from syslogins
where isntuser = 1
and name like '$Domain%'
and name not like 'NT %'
select distinct Name from @users").Name
foreach ($user in $users)
{
Try
{
$query = "select sid as SID from syslogins where name = '$Domain\$user' and isntuser = 1"
$SID = Invoke-Sqlcmd -ServerInstance $Server -Database master -Query $query | foreach {new-object security.principal.securityidentifier($_.SID,0)};
$results = Get-ADUser -Server $ADServer -Filter {SID -eq $SID} #| select SAMAccountName
Write-Verbose $results.SamAccountName
}
Catch
{
$FailedUsers = "$FailedUsers`n$user"
$query = "declare @command varchar(4000)
set @command = 'USE [?]
if exists (select 1 from sys.sysusers where isntname = 1 and name = ''$Domain\$user'')
select ''USE [?];' + CHAR(10) + 'DROP USER ['' + name + '']' + CHAR(10) + '''
from sys.sysusers
where isntname = 1
and name like ''%$user'''
exec sp_msforeachdb @command"
$DropUserQuery = "$DropUserQuery`n" + (Invoke-Sqlcmd -ServerInstance $Server -Database master -Query $query).Column1
$query = "USE [master];`n select 'DROP LOGIN [' + name + ']' + CHAR(10) + ''
from syslogins
where name like '%$user'"
$DropUserQuery = "$DropUserQuery`n" + (Invoke-Sqlcmd -ServerInstance $Server -Database master -Query $query).Column1
}
}
if ($FailedUsers.length -gt 0)
{
Write-Warning "On server $server : The following $Domain users do not exist in Active Directory: $FailedUsers"
Write-Host "On server $server : Run the following SQL to remove the users as required `n $DropUserQuery"
}
else
{
Write-Verbose "On server $server : All $Domain users were found in Active Directory`n`n" -Verbose
}
}
Catch
{
Write-Warning "Cannot connect to $Server`n`n"
}
}
</pre>
</span>
Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com3tag:blogger.com,1999:blog-4616149892194368111.post-40008573730767075732017-05-05T09:38:00.001-07:002018-03-19T05:30:17.784-07:00Compress your database tablesSQL Server has offered data compression since SQL 2008. It is a very good feature but, as with almost everything SQL Server, it needs to be deployed judiciously. Data compression has been written about and reviewed by many SQL experts who are much more qualified and knowledgeable than me, so I won't go into it too much.<br />
<br />
First, a quick summary of what the feature does as well as its benefits and effects:<br />
As the feature's name suggests, when it is enabled on a table and/or its index(es), SQL server compresses (or shrinks) the data before it is written to the database. There are two types: ROW and PAGE.<br />
<a name='more'></a><ul>
<li>Benefits</li>
<ul>
<li>Disk space savings</li>
<ul>
<li>Higher space saving for PAGE compression</li>
<li>Lower space saving for ROW compression</li>
</ul>
<li>Memory usage efficiency gains</li>
<li>Logical and physical read performance: more data fits in fewer pages</li>
</ul>
</ul>
<ul>
<li>Considerations</li>
<ul>
<li>Increased CPU cost</li>
<ul>
<li>Higher CPU cost for PAGE compression</li>
<li>Lower CPU cost for ROW compression</li>
</ul>
</ul>
</ul>
<ul><ul><ul></ul>
</ul>
</ul>
<br />
<div>
Personally, I feel (and so does <a href="https://www.brentozar.com/archive/2009/08/sql-server-data-compression-its-a-party/" target="_blank">Brent Ozar</a>) that the benefits outweigh the costs. Most of the SQL Server instances I have managed were much more disk bound than CPU bound. So if your server is running at an average of 20%-30% CPU and your I/O wait stats are high, it's a no-brainer in my mind.</div>
<div>
<br /></div>
<div>
Additional resources:</div>
<div>
<a href="https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression" target="_blank">MSDN Docs - Data Compression</a> </div>
<div>
<a href="http://sqlmag.com/database-performance-tuning/practical-data-compression-sql-server" target="_blank">Practical Data Compression in SQL Server</a></div>
<div>
<a href="https://technet.microsoft.com/en-us/library/dd894051(v=sql.100).aspx%C2%A0" target="_blank">Data Compression: Strategy, Capacity Planning and Best Practices</a></div>
<div>
<br /></div>
<div>
Now that we know about what data compression is, let's talk about how to implement it. The second link above describes how to compress tables and indexes. But what if you'd like to evaluate and compress your whole database. Well, I stumbled across a stored procedure by C. Meuleman ([dbo].[_Compress_Database]), which I downloaded from <a href="http://tinyurl.com/pf6ol77">http://tinyurl.com/pf6ol77</a>. Glenn Berry has one as well, but it doesn't do quite what I want: <a href="https://www.sqlskills.com/blogs/glenn/estimating-data-compression-savings-in-sql-server-2012/" target="_blank">Estimating Data Compression Savings in SQL Server 2012</a></div>
<div>
<br /></div>
<div>
It is a very nice script which loops through all the tables and indexes in your database and, based on the read/write thresholds you set (or just use the defaults) gives recommendations for which compression type to use for each object. I've used this stored proc many times. It is a real time saver and a good way of quickly getting an idea of what tables will get the best storage benefit based on their usage profile. It prints out the ALTER TABLE/ALTER INDEX scripts and there is even a parameter to state whether you want to immediately run the compression.</div>
<div>
<br /></div>
<div>
The one thing I didn't like about the stored proc was that it needed to be created in the database that was being evaluated. It also created tables in that database. I prefer to leave application databases alone. Even if you are carrying out the evaluation on a non-production server (which would be the preferred method) you are adding objects to a database that may have ongoing development being carried out against it. And you then need to be careful about cleaning up after yourself and so on. </div>
<div>
<br /></div>
<div>
My database team has its own DBA databases on both production and non-production database servers. We prefer to put any tools like this stored procedure on this database. That way, if we create a mess, it's not affecting anyone else.</div>
<div>
<br /></div>
<div>
With that in mind, I altered the above mentioned stored proc so that you can create and run it on a maintenance database and specify which database you want to evaluate. You can download the script from <a href="https://drive.google.com/open?id=0B-znT8ogfZ3LUnU5dFVxODJ0S1U" target="_blank">Google Drive</a>.</div>
<div>
<br /></div>
<div>
I hope you find this useful.</div>
Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com0tag:blogger.com,1999:blog-4616149892194368111.post-18650070491559816622016-09-02T04:29:00.001-07:002017-07-28T08:22:23.033-07:00Query To Find SQL Audit DetailsI've been deploying a SQL Server auditing solution for our new SQL 2014 estate. I know, we're a few years behind. But if you saw some of the legacy systems I work with you'd understand that migrating to SQL 2014 is quite a coup!<br />
<br />
Anyway, I found a really great audit solution that I have used as a base on <a href="http://colleenmorrow.com/2012/07/31/sql-audit-201-creating-an-audit-solution/" target="_blank">Colleen Morrow's</a> blog. I will detail what I have added to her solution in a later post. But, for now, I would like to share a query that displays some basic information about existing audits, both at the server and database level.
<br />
<br />
I put this query together for the purposes of documentation. Our 2014 estate is getting large fast. And with so many installations, we need to keep track of what is installed and configured on all our servers.<br />
<br />
<a name='more'></a><br />
The script below UNIONs two queries:<br />
<ul>
<li>One for the Server audit specifications</li>
<li>One for the database audit specifications</li>
</ul>
<div>
The database audit specification is a dynamic SQL statement due to the fact that it needs to interrogate the system tables of individual databases for the audit details. I have put in explicit COLLATE hints to cater for servers where databases are not all in line with the server's collation.</div>
<br />
<span style="font-size: xx-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
-- Find Audit Details
DECLARE @SQL varchar(max) = '';
DECLARE @auditspecs table (DBName sysname, AuditID int, AuditName sysname, Audit_type_desc varchar(10), On_Failure_Desc varchar(20), Audit_Enabled int, Audit_Spec_Name varchar(100), Audit_Spec_Enabled int, Audit_Actions varchar(max));
SELECT @SQL = @SQL + 'SELECT ''' + name + ''' COLLATE Latin1_General_CI_AS as AuditName , sa.audit_id ,sa.name COLLATE Latin1_General_CI_AS, sa.type_desc COLLATE Latin1_General_CI_AS, sa.on_failure_desc COLLATE Latin1_General_CI_AS,sa.is_state_enabled as Audit_Enabled, sas.name COLLATE Latin1_General_CI_AS as Audit_Spec_Name, sas.is_state_enabled as Audit_Spec_Enabled
,SUBSTRING(( SELECT '', '' + CAST(dasd.audit_action_name AS varchar(50))
FROM ' + name + '.sys.database_audit_specification_details AS dasd
WHERE sas.database_specification_id = dasd.database_specification_id
FOR
XML PATH('''')
) , 2, 65536) COLLATE Latin1_General_CI_AS AS Audit_Actions
FROM ' + name + '.sys.server_audits as sa
JOIN ' + name + '.sys.database_audit_specifications as sas on sa.audit_guid = sas.audit_guid; '
FROM sys.databases
WHERE name <> 'tempdb'
AND state = 0
AND is_read_only = 0;;
--print @SQL;
INSERT INTO @auditspecs
exec (@SQL);
SELECT ' Server Audit' COLLATE Latin1_General_CI_AS as DBName, sa.audit_id,sa.name COLLATE Latin1_General_CI_AS as Audit_Name,sa.type_desc COLLATE Latin1_General_CI_AS as Audit_type, sa.on_failure_desc COLLATE Latin1_General_CI_AS as ON_Fail_Desc, sa.is_state_enabled as Audit_Enabled,sas.name COLLATE Latin1_General_CI_AS as Audit_Spec_Name, sas.is_state_enabled as Audit_Spec_Enabled
,SUBSTRING(( SELECT ', ' + CAST(sasd.audit_action_name AS varchar(50))
FROM sys.server_audit_specification_details AS sasd
WHERE sas.server_specification_id = sasd.server_specification_id
FOR
XML PATH('')
), 2, 65536) COLLATE Latin1_General_CI_AS AS Audit_Actions
FROM sys.server_audits as sa
JOIN sys.server_audit_specifications as sas on sa.audit_guid = sas.audit_guid
UNION
SELECT * FROM @auditspecs
ORDER BY 1;
]]>
</script>
</span>
<br />
Here's a sample result set:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsDcYLDuaxZpF7voQoaJBRCD1wH9HY7Zgz-TMjtuYQ4EyWE9xnTdNpkE93mTVSDtq3trh5O2gsBDbhlaNkZ1f4dBIGNufcXGTduh_xq2C4pt7nvEyQoW29QjE177K9pvQmy7cpmjBzkxGt/s1600/AuditSpecSample.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="85" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsDcYLDuaxZpF7voQoaJBRCD1wH9HY7Zgz-TMjtuYQ4EyWE9xnTdNpkE93mTVSDtq3trh5O2gsBDbhlaNkZ1f4dBIGNufcXGTduh_xq2C4pt7nvEyQoW29QjE177K9pvQmy7cpmjBzkxGt/s400/AuditSpecSample.JPG" width="400" /></a></div>
<br />
<br />
<br />Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com2tag:blogger.com,1999:blog-4616149892194368111.post-9969431971429010732016-02-01T04:46:00.002-08:002016-02-11T03:39:11.969-08:00Restarting a service with PowerShellWe use Redgate's SQL Monitor to keep an eye on our mission critical servers. I like the product. It is reasonably user-friendly, reliable and it helps my team monitor our servers efficiently. I've used other products before and I think SQL Monitor compares favourably. No, I'm not paid by RedGate!!! <br />
<br />
With that in mind, my team has been struggling a bit with an ongoing issue: every Monday morning one of the SQL Monitor processes was running amok and maxing out CPU! This was strange, as the product is generally pretty stable. After some investigation, we realised that the security team had started performing regular vulnerability scans over the weekends. Part of this test involved intentional failed login attempts. SQL Monitor can't seem to handle this, and the web server, specifically the "xsp4" process goes haywire and trashes the CPU.<br />
<br />
<a name='more'></a>
Our small issue (according to management) didn't warrant a reworking of the new security procedures - fair enough, I guess. So, it was decided that we should restart the service that controls the xsp4 process, MonitorWebServiceNetwork, every Monday morning. And the best way to do this would be using a PowerShell script executed through Windows' Task Scheduler. I'm new to Powershell, so I had to scour the web to help me come up with the solution.<br />
<br />
I knew that I didn't want to restart the service unnecessarily, so I needed a script that established if CPU usage for the particular process was high or not. I found this great post: <a href="http://www.adminarsenal.com/admin-arsenal-blog/powershell-get-cpu-usage-for-a-process-using-get-counter/">Powershell: Get CPU Usage for a Process Using Get-Counter</a> by Kris Powell. In it he shares the script below that outputs the CPU usage of a process (or processes as he uses a wildcard for the process name).<br />
<br />
<span style="font-size: x-small;">
<script class="brush: ps" type="syntaxhighlighter">
<![CDATA[
# Option A: This is if you just have the name of the process; partial name OK
$ProcessName = "cpu"
# Option B: This is for if you just have the PID; it will get the name for you
#$ProcessPID = "6860"
#$ProcessName = (Get-Process -Id $ProcessPID).Name
$CpuCores = (Get-WMIObject Win32_ComputerSystem).NumberOfLogicalProcessors
$Samples = (Get-Counter "\Process($Processname*)\% Processor Time").CounterSamples
$Samples | Select `
InstanceName,
@{Name="CPU %";Expression={[Decimal]::Round(($_.CookedValue / $CpuCores), 2)}}
]]></script>
</span>
<br />
This script is great. The only problem for me is that it returns the CPU usage value as a string and I need a decimal value in order to compare it to my threshold value (50% CPU usage). Incidentally, I also didn't need to return the InstanceName. after much digging around, I modified the script resulting in the following:<br />
<br />
<span style="font-size: x-small;">
<script class="brush: ps" type="syntaxhighlighter">
<![CDATA[
[string]$ProcessName = "xsp4"
$CpuCores = (Get-WMIObject Win32_ComputerSystem).NumberOfLogicalProcessors;
[Decimal]$cpu = (Get-Counter "\Process($Processname)\% Processor Time").CounterSamples | Select -ExpandProperty CookedValue ;
$Usage = $cpu/$CpuCores;
$Usage;
]]></script>
</span>
<br />
Since I didn't need to print any of the counter values I completely removed the $Samples select statement. Instead I replaced the $Samples parameter with one called $cpu (just to make it more readable) and selected just the CookedValue property using the -ExpandProperty select parameter. This still returns a string value, so it is here that I convert it by defining the parameter as a decimal. Finally, I calculate the actual CPU usage by dividing the CookedValue by the number of CPU cores. I store this value in another parameter, $Usage. Kris Powell explains how he gets all this info in his post, so I won't repeat it. <br />
<br />
Once I have this value I can use it in an if statement and restart the service if it surpasses my threshold:
<br />
<br />
<span style="font-size: x-small;">
<script class="brush: ps" type="syntaxhighlighter">
<![CDATA[
[string]$ProcessName = "xsp4",
[string]$ServiceName = "MonitorWebServiceNetwork"
$CpuCores = (Get-WMIObject Win32_ComputerSystem).NumberOfLogicalProcessors;
[Decimal]$cpu = (Get-Counter "\Process($Processname)\% Processor Time").CounterSamples | Select -ExpandProperty CookedValue ;
$Usage = $cpu/$CpuCores;
If ($Usage -gt 50)
{
Restart-Service $ServiceName ;
Exit;
}
Else
{
Exit;
}
]]></script>
</span>
<br />
At this point I've added one more parameter, $ServiceName. This is because the process that is maxing out CPU is controlled by a service and in my case, the names are different. So if "xsp4" is using too much CPU, I need to restart the "MonitorWebServiceNetwork" service. And to make it fully flexible I've added one last parameter, $threshold. The final script allows the parameters to be passed as arguments so you can run it as a script file:
<br />
<br />
<span style="font-size: x-small;">
<script class="brush: ps" type="syntaxhighlighter">
<![CDATA[
param
(
[string]$ProcessName = "xsp4",
[string]$ServiceName = "MonitorWebServiceNetwork"
[int] $threshold = 50
)
$CpuCores = (Get-WMIObject Win32_ComputerSystem).NumberOfLogicalProcessors;
[Decimal]$cpu = (Get-Counter "\Process($Processname)\% Processor Time").CounterSamples | Select -ExpandProperty CookedValue ;
$Usage = $cpu/$CpuCores;
If ($Usage -gt $threshold)
{
Restart-Service $ServiceName ;
Exit;
}
Else
{
Exit;
}
]]></script>
</span>
<br />
The last hurdle I ran into was being able to execute this script from the task scheduler. This was resolved by the following blog post: <a href="http://blogs.technet.com/b/heyscriptingguy/archive/2011/01/12/schedule-powershell-scripts-that-require-input-values.aspx">Schedule PowerShell Scripts that Require Input Values</a> by Ed Wilson, the Microsoft Scripting Guy. There's also a good explanation of scheduling PowerShell scripts here: <a href="https://dmitrysotnikov.wordpress.com/2011/02/03/how-to-schedule-a-powershell-script/">How to Schedule a PowerShell Script</a> by Dmitry Sotnikov. The Action in my scheduled task is defined as follows:<br />
<br />
Action: Start a program<br />
Program/script: PowerShell.exe<br />
Add arguments (optional): -Command "& 'D:\Red Gate\CPU_Usage_Restart_Service.ps1' -ProcessName 'xsp4' -ServiceName 'MonitorWebServiceNetwork' -threshold 50"<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEirZmKKG7HOYPKH6SDBoeIwnOUZTLgt4IBwD3Ob26IRNZ-Igud-ialU3jHyv3hEjFIK2bc5ZP7WJbq4F6R-mlqyNw0gKo_mDhWAzHRFHjZLsTsx8Mxa1GBG644RXDIZdhNa_VAyhWNgrb9M/s1600/Capture.JPG" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEirZmKKG7HOYPKH6SDBoeIwnOUZTLgt4IBwD3Ob26IRNZ-Igud-ialU3jHyv3hEjFIK2bc5ZP7WJbq4F6R-mlqyNw0gKo_mDhWAzHRFHjZLsTsx8Mxa1GBG644RXDIZdhNa_VAyhWNgrb9M/s320/Capture.JPG" /></a><br />
<br />
Finally, I needed to schedule the task. Unfortunately, the times when the vulnerability scan would trigger the problem were variable. The vulnerability scan is run against a large estate of servers and there was no guarantee as to how long it would take each time. In the end I decided to schedule the task to run every 10 minutes over a several hour period on Monday mornings.<br />
<br />
As always, I hope you've found this post helpful.
<br />
<br />Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com118tag:blogger.com,1999:blog-4616149892194368111.post-21017658284222962522015-12-07T03:17:00.000-08:002016-02-11T03:38:27.959-08:00Databases With No Recent BackupsI've written the script below to easily identify databases that have not had backups taken within a certain amount of time. This could be useful if you need to build your own monitoring. However, even if you use a third party monitoring tool,as I do, it is helpful to run this query on the server for which I've received an alert.<br />
<br />
Many of the servers I manage have a large number of databases. The alerts I receive for overdue backups are database specific. But, in general, if one database is overdue, chances are something has happened on the server. So this query can give me a quick overview.<br />
<br />
<a name='more'></a>
At the top of the query there are three (3) variables:<br />
<br />
<ul>
<li>DECLARE @LogAge INT --Hours</li>
<li>DECLARE @DiffAge INT --Days</li>
<li>DECLARE @FullAge INT --Days</li>
</ul>
<div>
Set these according to what you consider makes a database backup to be out of date. For example, if you take daily full backups you'll want to set @FullAge to -2 (or even -1). The query will then find any database whose latest backup was taken more than two days from the time of running the query. </div>
<div>
<br /></div>
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
USE [msdb]
DECLARE @LogAge INT --Hours
DECLARE @DiffAge INT --Days
DECLARE @FullAge INT --Days
/***********************************************************************************************
--Set the age over which backups are old
***********************************************************************************************/
SET @LogAge = -30 --Hours
SET @DiffAge = -2 --Days
SET @FullAge = -6 --Days
/***********************************************************************************************
--Find Backup History
***********************************************************************************************/
;WITH Backups AS
(
SELECT MAX(media_set_id) AS Max_Media_Set_Id
,MAX(backup_start_date) as [LastBackupDate],database_name AS DatabaseName
,CASE WHEN [type] = 'L' THEN 'Log' WHEN [type] = 'I' THEN 'Diff' ELSE 'FULL' END AS [BackupType]
,DATABASEPROPERTYEX(database_name, 'Recovery') AS RecoveryModel
FROM [dbo].[backupset] as bs
WHERE database_name <> 'tempdb'
GROUP BY database_name,[type]
HAVING (MAX(CASE WHEN [type] = 'L' THEN backup_start_date ELSE NULL END) < DATEADD(HH,@LogAge,GETDATE())
OR MAX(CASE WHEN [type] = 'I' THEN backup_start_date ELSE NULL END) < DATEADD(D,@DiffAge,GETDATE())
OR MAX(CASE WHEN [type] = 'D' THEN backup_start_date ELSE NULL END) < DATEADD(D,@FullAge,GETDATE())
)
),
Files AS (SELECT database_name AS DatabaseName,bmf.physical_device_name,bmf.logical_device_name,bmf.device_type
FROM [dbo].[backupset] as bs
JOIN dbo.backupmediafamily as bmf ON bs.Media_Set_Id = bmf.media_set_id
JOIN Backups AS b ON b.DatabaseName = bs.database_name AND b.Max_Media_Set_Id = bs.media_set_id)
SELECT d.name
,CASE WHEN b.LastBackupDate IS NULL THEN 'Recent Backup taken' ELSE 'NO RECENT BACKUPS!!!' END
--,b.Max_Media_Set_Id
,b.LastBackupDate
,b.BackupType
,DATABASEPROPERTYEX(d.name, 'Recovery') AS RecoveryModel
,f.device_type
,f.logical_device_name
,f.physical_device_name
FROM sys.databases as d
LEFT OUTER JOIN Backups as b on d.name = b.DatabaseName
LEFT OUTER JOIN Files as f ON f.DatabaseName = d.name
WHERE d.name <> 'tempdb'
ORDER BY d.name,[LastBackupDate],BackupType
]]></script>
</span>
<br />
<div>
As always, I hope you find this helpful.</div>
Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com1tag:blogger.com,1999:blog-4616149892194368111.post-30534879906763710512015-11-25T06:26:00.001-08:002017-07-28T08:23:08.575-07:00Script to Alter File GrowthI recently inherited a large estate of SQL Servers. Over the years, before I started, it grew organically and was managed by an insufficient number of accidental DBAs. As a result many SQL server instances were built and left with their default settings.<br />
<br />
One such setting is file growth, which, tends to be set either at a growth rate of 10% or 1MB. Given the number of databases that can be on a single server I wrote a little script to aid me in making the changes. <br />
<br />
I realise that every server and database has different requirements. I am using this script to get my databases and servers to a basic level of "best practice" and avoid any performance issues that may arise.<br />
<br />
<a name='more'></a><br />
The script below sets the database file growth as follows:
<br />
<ul>
<li>Finds databases with files set to grow by either a percentage or 1MB or less</li>
<li>Calculates the file growth rate as follows:
<ul>
<li>100MB for files < 1GB</li>
<li>1024MB for files > 10GB</li>
<li>File growth value set to 10% of the file's size when file is between 1GB and 10GB (rounded to the nearest 100)</li>
</ul>
</li>
</ul>
Run the script then copy and paste the results into another SSMS window. It is best to review the resulting scripts to be sure they will do what you expect them to.<br />
<br />
As with all my scripts, this comes with no guarantees. Any scripts should be tested before put into a production environment.
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
USE [master]
GO
DECLARE @sql VARCHAR(MAX)
SET @sql= ''
;WITH cte AS (SELECT (size * 8 / 1024.0) as sizeMB,database_id,db_name(database_id) as DBName ,file_id,CASE WHEN is_percent_growth = 0 THEN (growth * 8 / 1024.0) ELSE growth END as growth
FROM sys.master_files
--GROUP BY database_id,db_name(database_id),file_id
)
SELECT @SQL = @SQL + 'ALTER DATABASE [' + db_name(mf.database_id) + ']
MODIFY FILE ( NAME = N''' + mf.name + ''',
FILEGROWTH = ' + CASE WHEN cte.sizeMB < 1024 THEN '100' --Growth interval 100MB when DB file less than 1GB
WHEN cte.sizeMB > 10240 THEN '1024' --Growth interval 1GB when DB file greater than 10GB
ELSE CONVERT(VARCHAR(10),ROUND(CONVERT(INT,sizeMB *.1),-2)) END + 'MB );' --Growth interval 10% of db file size when file between 1GB and 10GB
+ CHAR(10)
from sys.master_files as mf
join cte on mf.database_id = cte.database_id and mf.file_id = cte.file_id
WHERE is_percent_growth = 1 OR (is_percent_growth = 0 AND cte.growth < CASE WHEN cte.sizeMB < 1024 THEN 100
WHEN cte.sizeMB > 10240 THEN 1024 END)
ORDER BY db_name(mf.database_id)
PRINT @sql
]]></script>
</span>Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com0tag:blogger.com,1999:blog-4616149892194368111.post-12135906278929080122015-09-11T04:06:00.000-07:002015-09-11T04:26:56.587-07:00Finding When A SQL Agent Job RanA quick post for today since I haven't added anything in quite a while.<br />
<br />
Recently, I was investigating a deadlock that was occurring, like clockwork, on a particular server. A trace revealed that it was being executed by the SQL Server Agent account. The host machine that executed a query involved in the deadlock is our SSIS host. Therefore, there are loads of jobs with a dizzying array of schedules to weed through.<br />
<br />
<a name='more'></a>So I needed a query that can tell me what job(s) were running at a particular point in time. The key table is the msdb.dbo.sysjobhistory table, which, as it says on the tin, keeps a record of job executions. There is one main hurdle when it comes to querying this table: the run_date, run_time and run_duration columns are all stored as INT (<a href="https://msdn.microsoft.com/en-gb/library/ms174997.aspx">https://msdn.microsoft.com/en-gb/library/ms174997.aspx</a>). The run_duration is the most difficult to deal with since it is a variable length value: i.e. a 5 second duration is stored as 5 and a 2 hour duration is stored as 20000!<br />
<br />
Helpfully, Microsoft provides a function - msdb.dbo.agent_datetime(run_date, run_time) - to calculate the run_time of a job (curiously, I can't find any official MSDN documentation on this function). So converting the two columns run_date and run_time is easy. More difficult is formatting the run_duration into a time value.<br />
<br />
There is an additional problem involved. The sysjobhistory table doesn't record the date and time that jobs finish. This needs to be calculated by taking the start time (run_date and run_time) and adding the run_duration to it.<br />
<br />
I've created the query below as follows:<br />
<br />
<ul>
<li>@RunTime parameter is the point in time when you need to find running jobs</li>
<li>The CTE converts the run_duration INT value to a VARCHAR</li>
<li>It filters on step_id 0 (this is job outcome which shows the total job duration and success or failure of the job)</li>
<li>Finally I search for jobs starting <= @RunTime and ending <= @RunTime </li>
</ul>
<br />
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
USE [msdb]
GO
DECLARE @RunTime DATETIME = '2015-09-11 03:35:00'
;WITH cte AS(SELECT RIGHT('000000' + CONVERT(VARCHAR(6),run_duration),6 ) AS FormattedTime,job_id,step_id,instance_id FROM msdb..sysjobhistory
WHERE run_duration >=0
AND step_id = 0)
SELECT jh.instance_id,j.name
, dbo.agent_datetime(jh.run_date,jh.run_time) as Start_time
,DATEADD(SECOND, DATEDIFF(SECOND, 0, CONVERT(TIME,STUFF(STUFF(cte.FormattedTime,3,0,':'),6,0,':'))), dbo.agent_datetime(jh.run_date,jh.run_time)) AS End_Time
,CONVERT(TIME,STUFF(STUFF(cte.FormattedTime,3,0,':'),6,0,':')) as Duration
FROM sysjobs as j
JOIN sysjobhistory as jh on j.job_id = jh.job_id
JOIN cte on cte.job_id = jh.job_id and cte.step_id = jh.step_id and jh.instance_id = cte.instance_id
where dbo.agent_datetime(jh.run_date,jh.run_time) <= @RunTime /*Start time*/
AND DATEADD(SECOND, DATEDIFF(SECOND, 0, CONVERT(TIME,STUFF(STUFF(cte.FormattedTime,3,0,':'),6,0,':'))), dbo.agent_datetime(jh.run_date,jh.run_time)) >= @RunTime /*End time*/
AND jh.step_id = 0
ORDER BY jh.instance_id
]]></script>
</span>
<br />
There is an additional dependency, the SQL Agent job history settings. generally, i would use the above query for very recent job executions. It is unlikely that you'd be able to find what job was running 6 weeks ago (depending on how busy your SQL Agent is, of course).<br />
<br />
I hope you find this helpful.<br />
<br />
Thanks to:<br />
<br />
<ul>
<li><a href="http://sqlandme.com/2013/07/22/sql-server-get-values-as-datetime-from-sysjobhistory/" target="_blank">SQL And Me</a> for drawing my attention to the agent_datetime function.</li>
<li>SwePeso for the DATEADD query suggested on <a href="http://www.sqlservercentral.com/Forums/Topic542581-145-1.aspx" target="_blank">SQLServerCentral</a></li>
<li>SQLDenis on <a href="http://blogs.lessthandot.com/index.php/datamgmt/datadesign/showing-sysjobhistory-run_duration-in-hhmmss/" target="_blank">LessThanDot</a> for the CTE to and time formatting of run_duration</li>
</ul>
Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com0tag:blogger.com,1999:blog-4616149892194368111.post-14008840244713063662015-07-24T09:57:00.000-07:002017-07-28T08:32:04.686-07:00SQL Server Optimization<span style="font-family: inherit;">This post aims to aggregate a lot of the best practice or otherwise recommended configuration for SQL Server performance. It is meant as a summary or quick-reference sheet. All of these concepts are discussed at length all over the internet, so I will not spend much time explaining them. </span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">As with any recommendations of this sort please use them carefully and test before deploying into a production environment. Also, depending on your specific environment's configuration, not all of the below may be advisable or bring about clear benefits. To quote <a href="http://www.sqlskills.com/blogs/jonathan" rel="author external" style="border: 0px; font-stretch: inherit; line-height: 16.7999992370605px; margin: 0px; outline: none; padding: 0px; text-decoration: none; vertical-align: baseline;" target="_blank" title="Visit Jonathan Kehayias’s website">Jonathan Kehayias</a>:</span> "<span style="line-height: 21px;"><span style="font-family: inherit;">While there is a lot of good guidance available for how to best configure SQL Server, the specifics of any given implementation is a very big “It Depends…”</span></span>" <span style="font-family: inherit;">What this post should </span>achieve<span style="font-family: inherit;"> is providing the reader with an awareness of features and options that influence a SQL Server instance's operation. </span><br />
<br />
<a name='more'></a>
<h3>
<span style="font-family: inherit;">
OS Level Settings</span></h3>
<h4>
<span style="font-family: inherit;">
Instant File Initialization (quick win)</span></h4>
<span style="font-family: inherit;">Ensure servers are set up to allow SQL Server to initialize data files instantaneously. From <a href="https://msdn.microsoft.com/en-us/ms175935.aspx">MSDN</a>:</span><br />
<blockquote class="tr_bq">
<span style="font-family: inherit; font-size: x-small; font-weight: normal;">In SQL Server, data files can be initialized instantaneously. This allows for fast execution of the previously mentioned file operations. Instant file initialization reclaims used disk space without filling that space with zeros. Instead, disk content is overwritten as new data is written to the files. Log files cannot be initialized instantaneously.Instant file initialization is available only on Microsoft Windows XP Professional or Windows Server 2003 or later versions.Instant file initialization is only available if the SQL Server (MSSQLSERVER) service account has been granted SE_MANAGE_VOLUME_NAME. Members of the Windows Administrator group have this right and can grant it to other users by adding them to the Perform Volume Maintenance Tasks security policy. For more information about assigning user rights, see the Windows documentation.CAVEAT: Instant file initialization is not available when TDE is enabled.</span></blockquote>
<span style="font-family: inherit;">As the above paragraph mentions, Log files are not affected. In some cases, security policies may be in place that prevent assigning the SQL Server service with these </span>privileges<span style="font-family: inherit;">.</span><br />
<span style="font-family: inherit;"><br /></span>
<h4>
<span style="font-family: inherit;">
File Indexing (quick win)</span></h4>
<span style="font-family: inherit;">Disable Windows file indexing on all drives.</span><br />
<span style="font-family: inherit;"><br /></span>
<h3>
<span style="font-family: inherit;">
SQL Server Level Settings</span></h3>
<div>
<span style="font-family: inherit;"><br /></span></div>
<h4>
<span style="font-family: inherit;">Max Memory </span></h4>
<div>
<span style="font-family: inherit;">Limiting the maximum memory that SQL Server will use is important. Due to SQL Server's method of claiming RAM and not releasing it (at least until a restart), you want to be sure that in the event that SQL Server experiences heavy load it never steals memory from the OS and any other critical components preventing them from functioning properly. <a href="https://www.sqlskills.com/blogs/jonathan/wow-an-online-calculator-to-misconfigure-your-sql-server-memory/" rel="author external" style="border: 0px; font-stretch: inherit; line-height: 16.7999992370605px; margin: 0px; outline: none; padding: 0px; text-decoration: none; vertical-align: baseline;" target="_blank" title="Visit Jonathan Kehayias’s website"><span style="color: blue;">Jonathan Kehayias</span></a> explains it all very well.</span></div>
<h4>
<br />
<span style="font-family: inherit;">
Trace Flags (quick win)</span></h4>
<span style="font-family: inherit;">DBCC TRACEON(1118,-1) – Improves page allocation efficiency for TempDB</span><br />
<span style="font-family: inherit;">DBCC TRACEON (1117,-1) – Forces all files in a filegroup to grow in synch</span><br />
<span style="font-family: inherit;">DBCC TRACEON(3226,-1) – Prevents successful backup operations from being logged</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">The above mentioned trace flags should be added to the startup parameters of the SQL server – or to a stored proc that is marked as ‘on startup’.
</span><br />
<span style="font-family: inherit;"><b>Trace Flag 1117 needs to be used carefully</b>. It is a server/instance wide setting so all databases on an instance will be affected. Additionally, if a database uses table partitioning over multiple filegroups, this trace flag will cause synchronized file growth on all files. This could cause unused/archived partitions to grow in line with new/active partitions.</span><br />
<h4>
<br />
<span style="font-family: inherit;">
Max Degree of Parallelism (quick win)</span></h4>
<span style="font-family: inherit;">This setting needs to be evaluated on a server-by-server
basis depending on the server CPU configuration. This setting comes into its
own when a server has a larger number of CPU cores, i.e. 8 or more. The reason
for setting a limit is to prevent any single parallel process/query from using
all the cores thereby preventing others from running. <o:p></o:p></span><br />
<span style="font-family: inherit;">From <a href="https://support.microsoft.com/en-us/kb/2806535?wa=wsignin1.0">Microsoft
Support website</a>:</span><br />
<span style="font-family: inherit;">Use the following guidelines when you configure the MAXDOP value for SQL Server
2005 and later versions:</span><br />
<span style="font-family: inherit;"><br /></span>
<table border="1" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="border-collapse: collapse; border: none; mso-border-bottom-alt: solid black .75pt; mso-padding-alt: 0cm 0cm 0cm 0cm; mso-yfti-tbllook: 1184; width: 100%px;">
<tbody>
<tr>
<td style="border-bottom: solid #C9C9C9 1.0pt; border-left: none; border-right: none; border-top: solid #C9C9C9 1.0pt; mso-border-bottom-alt: solid #C9C9C9 .75pt; mso-border-top-alt: solid #C9C9C9 .75pt; padding: 3.75pt 7.5pt 3.75pt 0cm; width: 23.46%;" valign="top" width="23%"><div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0cm;">
<span style="font-family: inherit; font-size: 8.0pt; line-height: 115%;">Server with single NUMA node<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid #C9C9C9 1.0pt; border-left: none; border-right: none; border-top: solid #C9C9C9 1.0pt; mso-border-bottom-alt: solid #C9C9C9 .75pt; mso-border-top-alt: solid #C9C9C9 .75pt; padding: 3.75pt 7.5pt 3.75pt 0cm; width: 33.34%;" valign="top" width="33%"><div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0cm;">
<span style="font-family: inherit; font-size: 8.0pt; line-height: 115%;">Less than 8 logical processors<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid #C9C9C9 1.0pt; border-left: none; border-right: none; border-top: solid #C9C9C9 1.0pt; mso-border-bottom-alt: solid #C9C9C9 .75pt; mso-border-top-alt: solid #C9C9C9 .75pt; padding: 3.75pt 7.5pt 3.75pt 0cm; width: 43.2%;" valign="top" width="43%"><div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0cm;">
<span style="font-family: inherit; font-size: 8.0pt; line-height: 115%;">Keep MAXDOP at or below # of logical
processors<o:p></o:p></span></div>
</td>
</tr>
<tr>
<td style="border-bottom: solid #C9C9C9 1.0pt; border: none; mso-border-bottom-alt: solid #C9C9C9 .75pt; mso-border-top-alt: solid #C9C9C9 .75pt; mso-border-top-alt: solid #C9C9C9 .75pt; padding: 3.75pt 7.5pt 3.75pt 0cm; width: 23.46%;" valign="top" width="23%"><div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0cm;">
<span style="font-family: inherit; font-size: 8.0pt; line-height: 115%;">Server with single NUMA node<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid #C9C9C9 1.0pt; border: none; mso-border-bottom-alt: solid #C9C9C9 .75pt; mso-border-top-alt: solid #C9C9C9 .75pt; mso-border-top-alt: solid #C9C9C9 .75pt; padding: 3.75pt 7.5pt 3.75pt 0cm; width: 33.34%;" valign="top" width="33%"><div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0cm;">
<span style="font-family: inherit; font-size: 8.0pt; line-height: 115%;">Greater than 8 logical processors<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid #C9C9C9 1.0pt; border: none; mso-border-bottom-alt: solid #C9C9C9 .75pt; mso-border-top-alt: solid #C9C9C9 .75pt; mso-border-top-alt: solid #C9C9C9 .75pt; padding: 3.75pt 7.5pt 3.75pt 0cm; width: 43.2%;" valign="top" width="43%"><div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0cm;">
<span style="font-family: inherit; font-size: 8.0pt; line-height: 115%;">Keep MAXDOP at 8<o:p></o:p></span></div>
</td>
</tr>
<tr>
<td style="border-bottom: solid #C9C9C9 1.0pt; border: none; mso-border-bottom-alt: solid #C9C9C9 .75pt; mso-border-top-alt: solid #C9C9C9 .75pt; mso-border-top-alt: solid #C9C9C9 .75pt; padding: 3.75pt 7.5pt 3.75pt 0cm; width: 23.46%;" valign="top" width="23%"><div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0cm;">
<span style="font-family: inherit; font-size: 8.0pt; line-height: 115%;">Server with multiple NUMA nodes<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid #C9C9C9 1.0pt; border: none; mso-border-bottom-alt: solid #C9C9C9 .75pt; mso-border-top-alt: solid #C9C9C9 .75pt; mso-border-top-alt: solid #C9C9C9 .75pt; padding: 3.75pt 7.5pt 3.75pt 0cm; width: 33.34%;" valign="top" width="33%"><div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0cm;">
<span style="font-family: inherit; font-size: 8.0pt; line-height: 115%;">Less than 8 logical processors per
NUMA node<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid #C9C9C9 1.0pt; border: none; mso-border-bottom-alt: solid #C9C9C9 .75pt; mso-border-top-alt: solid #C9C9C9 .75pt; mso-border-top-alt: solid #C9C9C9 .75pt; padding: 3.75pt 7.5pt 3.75pt 0cm; width: 43.2%;" valign="top" width="43%"><div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0cm;">
<span style="font-family: inherit; font-size: 8.0pt; line-height: 115%;">Keep MAXDOP at or below # of logical
processors per NUMA node<o:p></o:p></span></div>
</td>
</tr>
<tr>
<td style="border-bottom: solid black 1.0pt; border: none; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid #C9C9C9 .75pt; mso-border-top-alt: solid #C9C9C9 .75pt; padding: 3.75pt 7.5pt 3.75pt 0cm; width: 23.46%;" valign="top" width="23%"><div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0cm;">
<span style="font-family: inherit; font-size: 8.0pt; line-height: 115%;">Server with multiple NUMA nodes<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid black 1.0pt; border: none; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid #C9C9C9 .75pt; mso-border-top-alt: solid #C9C9C9 .75pt; padding: 3.75pt 7.5pt 3.75pt 0cm; width: 33.34%;" valign="top" width="33%"><div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0cm;">
<span style="font-family: inherit; font-size: 8.0pt; line-height: 115%;">Greater than 8 logical processors
per NUMA node<o:p></o:p></span></div>
</td>
<td style="border-bottom: solid black 1.0pt; border: none; mso-border-bottom-alt: solid black .75pt; mso-border-top-alt: solid #C9C9C9 .75pt; mso-border-top-alt: solid #C9C9C9 .75pt; padding: 3.75pt 7.5pt 3.75pt 0cm; width: 43.2%;" valign="top" width="43%"><div class="MsoNormal" style="margin-bottom: .0001pt; margin-bottom: 0cm;">
<span style="font-family: inherit; font-size: 8.0pt; line-height: 115%;">Keep MAXDOP at 8<o:p></o:p></span></div>
</td>
</tr>
</tbody></table>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">The above table is purely a guideline. MAXDOP settings are environment and workload specific. For example, on servers with 8 or fewer logical cores, you may as well leave MAXDOP at 0, since the </span>recommended<span style="font-family: inherit;"> setting is to keep MAXDOP at or below the # of logical cores. And as far as limiting a parallel process's access to all the cores, this is valid in a busy OLTP style environment. But if it is a data warehouse that runs non-parallel ETL procedures, why restrict them to only a few cores?</span><br />
<h4>
<span style="font-family: inherit;">
<br />
Cost threshold for parallelism (quick win)</span></h4>
<span style="font-family: inherit;">This setting should be reviewed for each server. It sets the
threshold for a query’s estimated cost before it is considered for parallel
execution (using multiple cores in parallel for a query)</span><br />
<h4>
<span style="font-family: inherit;">
<br />
Optimize for Ad-hoc queries setting (quick win)</span></h4>
<span style="font-family: inherit;">Evaluate servers for plan cache usage. If there are many queries
cached that have been run only once it may be a good idea to enable this
setting. The query below may help in making this evaluation:</span><br />
<span style="font-family: inherit;"><span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
SELECT objtype AS [CacheType]
, count_big(*) AS [Total Plans]
, sum(cast(size_in_bytes AS decimal(18,2)))/1024/1024 AS [Total MBs]
, avg(usecounts) AS [Avg Use Count]
, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) AS decimal(18,2)))/1024/1024 AS [Total MBs – USE Count 1]
, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans – USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs – USE Count 1] DESC;
]]></script>
</span>
</span><br />
<h3>
<span style="font-family: inherit;">
Database Level Settings</span></h3>
<br />
<div class="MsoNormal">
<h4>
<span style="font-family: inherit;">
Forced Parameterization (quick win)</span></h4>
</div>
<div class="MsoNormal">
<span style="font-family: inherit;">Many applications pass their SQL queries as literal strings to the server. This can cause bloat of the plan cache, which can be bad both in server memory management an the efficiency of the query optimizer in choosing appropriate plans. You can change this setting in the database options. <a href="http://www.brentozar.com/blitz/forced-parameterization/" target="_blank">Brent Ozar</a> explains it well.</span></div>
<h3>
<br />
<span style="font-family: inherit;">
Database Configuration/Design</span></h3>
<br />
<h4>
<span style="font-family: inherit;">TempDB</span></h4>
<div>
<span style="font-family: inherit;">The ideal configuration for Tempdb is as follows:</span></div>
<div>
<ul>
<li><span style="font-family: inherit;">Data files are placed on their own disk array (RAID 10 equivalent if possible)</span></li>
<li><span style="font-family: inherit;">The log file is placed on its own disk array (RAID 1 is sufficient)</span></li>
<li><span style="font-family: inherit;">Use multiple data files of the same size based on the number of logical cores.</span></li>
<li><span style="font-family: inherit;">Pre-grow the data and log files, i.e. set the max size of the files at time of creation to prevent auto-growth</span></li>
</ul>
<div>
Tempdb is a complex topic. the above suggestions are a good start. Here are some good resources on how it works and how best to configure it:</div>
</div>
<div>
<a href="http://blogs.msdn.com%20tempdb/" target="_blank">blogs.msdn.com tempdb</a></div>
<div>
<a href="http://brentozar.com%20tempdb%20data%20files/" target="_blank">brentozar.com tempdb data files</a></div>
<div>
<a href="http://solarwinds.com%20sql%20server%20tempdb%20best%20practices%20placement/" target="_blank">Solarwinds.com sql server tempdb best practices placement</a></div>
<h4>
<br />
<span style="font-family: inherit;">Filegroups</span></h4>
<span style="font-family: inherit;">Spread databases (especially large ones) over multiple Filegroups. The advantages of this are several fold, the main two are:
</span><br />
<ol>
<li><span style="font-family: inherit;">Performance – as I/O can be spread over multiple
files. </span>In addition to that, based on how many logical processors you have and the IO profile of the tables in the filegroup, consider configuring filegroups with more than 1 file.</li>
<li><span style="font-family: inherit;">RTO improvement – if the tables are distributed
over file groups based on their usage RTO can be improved by being able to
restore the crucial tables first to get the users/application up and running
before the entire database is restored. </span></li>
</ol>
<span style="font-family: inherit;">As an example: Database X has the following table/filegroup configuration:</span><br />
<ul>
<li><span style="font-family: inherit;">PRIMARY
filegroup:</span></li>
<ul>
<li><span style="font-family: inherit;">System/Merge tables on PRIMARY
filegroup (this is not definable by the user)</span></li>
</ul>
<li><span style="font-family: inherit;">FileGroup1</span></li>
<ul>
<li><span style="font-family: inherit;">Tables
crucial for application function (i.e. logging in, UI, data entry, etc.)</span></li>
</ul>
<li><span style="font-family: inherit;">Filegroup2, Filegroup3,FilegroupN</span></li>
<ul>
<li><span style="font-family: inherit;">Tables
used for reporting/data archive (i.e. not needed for basic application
functions)</span></li>
</ul>
</ul>
<span style="font-family: inherit;">In this scenario, if the database
needs to be restored users can log in to the application after restoring only
the PRIMARY and FileGroup1 filegroups. Ideally, these will not involve very
large tables. The other filegroups can then be restored in parallel with users
using the system. They may not be able to run some reports or view archive data
until those filegroups are done restoring, but basic usage will be available.</span><br />
<br />
I hope this post was helpful.Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com0tag:blogger.com,1999:blog-4616149892194368111.post-80484238215685158152015-05-26T01:38:00.000-07:002015-05-26T01:38:52.569-07:00Add an Operator to All SQL Server Agent AlertsI was investigating a new server I'd been assigned, looking for gaps in configuration using the trusty sp_Blitz tool. The organisation that owns this server instance is small and doesn't use third-party monitoring. I decided to add some basic alerting as recommended by Brent Ozar and Co. They've kindly provided a script to do this: <a href="http://www.brentozar.com/blitz/configure-sql-server-alerts/">Blitz Result: No SQL Server Agent Alerts Configured</a>. <br />
<br />
A little later on I realised that I needed to add an additional operator to these alerts and was loath to step through them one at a time and tick the appropriate box. So, I knocked out a quick and simple script (I've included two versions, one that adds the operator to all alerts and one that allows you to select a subset). I hope it is helpful.<br />
<a name='more'></a><br />
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
USE [msdb]
GO
--Add operator to all alerts
DECLARE @sql varchar(max);
DECLARE @alertname varchar(500);
DECLARE @loop int = 1;
WHILE @loop <= (select max(id) from dbo.sysalerts)
BEGIN
SELECT @alertname = name from dbo.sysalerts WHERE id = @loop;
SET @sql = 'EXEC msdb.dbo.sp_add_notification @alert_name=N''' + @alertname + ''', @operator_name=N''AgentivityDBA@Gmail'', @notification_method = 1;';
PRINT @sql;
SET @loop = @loop + 1;
END;
GO
--Add operator to subset of alerts
DECLARE @sql varchar(max);
DECLARE @alertname varchar(500);
DECLARE @alerts TABLE (id int IDENTITY (1,1), AlertName varchar(500));
DECLARE @loop int = 1;
INSERT INTO @alerts (AlertName)
SELECT name
FROM dbo.sysalerts
WHERE name NOT LIKE '%logShipping%'; --Modify the where clause to your needs
WHILE @loop <= (select max(id) from @alerts)
BEGIN
SELECT @alertname = name from dbo.sysalerts WHERE id = @loop;
SET @sql = 'EXEC msdb.dbo.sp_add_notification @alert_name=N''' + @alertname + ''', @operator_name=N''AgentivityDBA@Gmail'', @notification_method = 1;';
PRINT @sql;
SET @loop = @loop + 1;
END;
GO
]]></script>
</span>
Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com0tag:blogger.com,1999:blog-4616149892194368111.post-16245184953547058292015-05-19T03:18:00.000-07:002015-12-14T03:44:30.752-08:00Study Materials for MCSE Microsoft Exams 70-457, 70-458, 70-459<span style="color: orange;">/***UPDATE***/</span><br />
<span style="color: orange;">I passed the 70-457! Now studying for 70-458...</span><br />
<span style="color: orange;">I passed the 70-458! Now studying for 70-459, booked for Jan 29, 2016...</span><br />
<span style="color: orange;">/***UPDATE***/</span><br />
<br />
I am studying for the MCSE Certification and will be taking the upgrade from MCITP 2008R8 tests 70-457, 70-458 and 70-459. After trawling the web for study materials I stumbled upon a series of posts on <a href="http://coffeeandsql.com/">coffeeandsql.com</a> by Carla Abanes. It is a great resource, touching on all the topics covered by the 70-457 exam. I've collated the links below:<br />
<br />
<h3>
<span style="font-family: inherit;">Review Materials for 70-458</span></h3>
<a href="http://tracyboggiano.com/archive/2014/01/70-458-resources/" target="_blank">Exam 70-458 Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012</a><br />
<h3>
<span style="font-family: inherit;">Review Materials for 70-457</span></h3>
<dl>
<dt><span style="font-family: inherit;"><a href="http://coffeeandsql.com/2013/07/05/70-457-reviewer-01-rank-functions/" target="_blank">70-457 Reviewer #01, Rank Functions</a>
</span></dt>
<dt><span style="font-family: inherit;"><a href="http://coffeeandsql.com/2013/07/10/70-457-reviewer-02-what-is-columnstore-index/" target="_blank">70-457 Reviewer #02, What is Columnstore Index?</a>
</span></dt>
<dt><span style="font-family: inherit;"><a href="http://coffeeandsql.com/2013/07/16/70-457-reviewer-03-planning-an-installation-of-sql-server-2012/" target="_blank">70-457 Reviewer #03, Planning an Installation of SQL Server 2012</a>
</span></dt>
<dt><span style="font-family: inherit;"><a href="http://coffeeandsql.com/2013/07/18/70-457-reviewer-04-implement-a-migration-strategy/" target="_blank">70-457 Reviewer #04, Implement a Migration Strategy</a>
</span></dt>
<dt><span style="font-family: inherit;"><a href="http://coffeeandsql.com/2013/07/22/70-457-reviewer-05-configuring-sql-server-2012-components/" target="_blank">70-457 Reviewer #05, Configuring SQL Server 2012 Components</a>
</span></dt>
<dt><span style="font-family: inherit;"><a href="http://coffeeandsql.com/2013/07/24/70-457-reviewer-06-configuring-and-managing-database-in-sql-server-2012/" target="_blank">70-457 Reviewer #06, Configuring and Managing Database in SQL Server 2012</a>
</span></dt>
<dt><span style="font-family: inherit;"><a href="http://coffeeandsql.com/2013/07/25/70-457-reviewer-07-resolving-concurrency-problems-in-sql-server-2012/" target="_blank">70-457 Reviewer #07, Resolving Concurrency Problems in SQL Server 2012</a>
</span></dt>
<dt><span style="font-family: inherit;"><a href="http://coffeeandsql.com/2013/08/01/70-457-reviewer-08-implementing-clustered-instance-in-sql-server-2012/" target="_blank">70-457 Reviewer #08, Implementing Clustered Instance in SQL Server 2012</a>
</span></dt>
<dt><span style="font-family: inherit;"><a href="http://coffeeandsql.com/2013/08/13/70-457-reviewer-09-auditing-sql-server-2012/" target="_blank">70-457 Reviewer #09, Auditing SQL Server 2012</a>
</span></dt>
<dt><span style="font-family: inherit;"><a href="http://coffeeandsql.com/2013/08/31/70-457-reviewer-10-managing-sql-server-agent/" target="_blank">70-457 Reviewer #10 Managing SQL Server Agent</a>
</span></dt>
<dt><span style="font-family: inherit;"><a href="http://coffeeandsql.com/2013/09/01/70-457-reviewer-11-configuring-and-maintaining-a-backup-strategy/" target="_blank">70-457 Reviewer #11 Configuring and Maintaining a Backup Strategy</a>
</span></dt>
<dt><span style="font-family: inherit;"><a href="http://coffeeandsql.com/2013/09/08/70-457-reviewer-12-restoring-sql-server-databases/" target="_blank">70-457 Reviewer #12 Restoring SQL Server Databases</a>
</span></dt>
<dt><span style="font-family: inherit;"><a href="http://coffeeandsql.com/2013/11/10/70-457-reviewer-13-managing-sql-server-logins/" target="_blank">70-457 Reviewer #13 Managing SQL Server Logins</a>
</span></dt>
<dt><span style="font-family: inherit;"><a href="http://coffeeandsql.com/2013/11/10/70-457-reviewer-14-database-and-application-roles/" target="_blank">70-457 Reviewer #14 Database and Application Roles</a>
</span></dt>
<dt><span style="font-family: inherit;"><a href="http://coffeeandsql.com/2013/11/10/70-457-reviewer-15-implement-database-mirroring/" target="_blank">70-457 Reviewer #15 Implement Database Mirroring</a>
</span></dt>
<dt><span style="font-family: inherit;"><a href="http://coffeeandsql.com/2013/11/17/70-457-reviewer-16-implement-alwayson-in-sql-server-2012/" target="_blank">70-457 Reviewer #16 Implement AlwaysOn in SQL Server 2012</a>
</span></dt>
<dt><a href="http://coffeeandsql.com/2014/01/08/reviewer-17-database-replication-in-sql-server-2012/" target="_blank"><span style="font-family: inherit;">70-457 Reviewer #17 Database Replication in SQL Server 2012</span></a>
</dt>
</dl>Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com6tag:blogger.com,1999:blog-4616149892194368111.post-56660500978626434402015-05-15T08:35:00.000-07:002015-05-20T02:29:25.345-07:00SQL Login and User for Performance TuningI've run into an interesting dilemma: as a new staff member in a security conscious organisation, I can't really get any work done. There is, however, a lot of work a DBA can do in terms of analyzing SQL Server instances right from day one. There are lots of best practice recommendations that can be made that are application or db design agnostic. Alternately, it can be possible to investigate the database design without access to the underlying data itself. With that all in mind, I have put some scripts together that creates login/user permissions and roles that can give a SQL consultant useful, but security minded, access to a SQL instance.<br />
<br />
<a name='more'></a>I will break down my scripts into two: 2005-2008R2 and 2012-2014. The main reason for this is down to the introduction of User Defined Server Roles in SQL Server 2012. So, there will be some duplication between the two sets of scripts below.<br />
<h4>
<b>SQL Server 2005 to 2008R2</b></h4>
<br />
<b>First we need to execute the Server level scripts</b><br />
If the SQL login(s) doesn't yet exist execute the following for as many logins as needed:<br />
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
USE [master];
CREATE LOGIN [PerformanceTuner]
WITH PASSWORD=N'fuQFGWhgy2jHksKxsxRZC!DdQE' MUST_CHANGE --Password created by https://www.random.org/passwords/
,DEFAULT_DATABASE=[master]
,CHECK_EXPIRATION=ON
,CHECK_POLICY=ON;
]]></script>
</span>
<br />
Now, give the appropriate login(s) the server level permissions<br />
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
--Needs doing once per Login
GRANT CONNECT SQL TO [PerformanceTuner];
GRANT VIEW SERVER STATE TO [PerformanceTuner];
GRANT VIEW ANY DEFINITION TO [PerformanceTuner];
]]></script>
</span>
<br />
<b>Second the database level scripts</b><br />
Create the user defined database roles for master and your user database(s).<br />
For the master database the script checks for some common diagnostic stored procs, like sp_blitz and sp_whoisactive and grants exec rights to the database role.<br />
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
USE [master]
--Create the database user
CREATE USER [PerformanceTuner] FOR LOGIN [PerformanceTuner];
ALTER USER [PerformanceTuner] WITH DEFAULT_SCHEMA=[dbo];
--CREATE A specific DB Role for performance tuning. Only needs doing once per instance
CREATE ROLE [db_PerformanceTuning] AUTHORIZATION [dbo];
--Check for common performance tuning stored procs and add to DB Role
IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'sp_Blitz')
GRANT EXECUTE ON [dbo].[sp_Blitz] TO [db_PerformanceTuning];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'sp_BlitzCache')
GRANT EXECUTE ON [dbo].[sp_BlitzCache] TO [db_PerformanceTuning];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'sp_BlitzIndex')
GRANT EXECUTE ON [dbo].[sp_BlitzIndex] TO [db_PerformanceTuning];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'sp_BlitzTrace')
GRANT EXECUTE ON [dbo].[sp_BlitzTrace] TO [db_PerformanceTuning];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'sp_AskBrent')
GRANT EXECUTE ON [dbo].[sp_AskBrent] TO [db_PerformanceTuning];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'sp_WhoIsActive')
GRANT EXECUTE ON [dbo].[sp_WhoIsActive] TO [db_PerformanceTuning];
--Add User to above DB Role
EXEC sp_addrolemember N'db_PerformanceTuning', N'PerformanceTuner';
]]></script>
</span>
<br />
For the user database(s) the new db role is granted the showplan privilege and, optionally, added to the db_datareader role.<br />
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
USE [Testdb];
CREATE USER [PerformanceTuner] FOR LOGIN [PerformanceTuner];
ALTER USER [PerformanceTuner] WITH DEFAULT_SCHEMA=[dbo];
--Needs doing once per appropriate user database
CREATE ROLE [db_PerformanceTuning] AUTHORIZATION [dbo];
GRANT SHOWPLAN TO [db_PerformanceTuning];
--Add User to above DB Role
EXEC sp_addrolemember N'db_PerformanceTuning', N'PerformanceTuner';
--Optional
EXEC sp_addrolemember N'db_datareader', N'db_PerformanceTuning';
]]></script>
</span>
<br />
For msdb the user is given membership to the db_datareader db role<br />
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
USE [msdb];
CREATE USER [PerformanceTuner] FOR LOGIN [PerformanceTuner];
ALTER USER [PerformanceTuner] WITH DEFAULT_SCHEMA=[dbo];
EXEC sp_addrolemember N'db_datareader', N'PerformanceTuner';
]]></script>
</span>
<br />
<h4>
<b>SQL Server 2012 to 2014</b></h4>
<br />
<div>
<b>First we need to execute the Server level scripts</b></div>
<div>
With SQL Server 2012 and above, we can now create User Defined Server Roles. The script below creates a new server role with connect to sql, view server state and view any definition permissions granted. And the login(s) created is added as a member.</div>
<div>
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
USE [master];
CREATE LOGIN [PerformanceTuner]
WITH PASSWORD=N'fuQFGWhgy2jHksKxsxRZC!DdQE' MUST_CHANGE --Password created by https://www.random.org/passwords/
,DEFAULT_DATABASE=[master]
,CHECK_EXPIRATION=ON
,CHECK_POLICY=ON;
--Only needs doing once per instance
CREATE SERVER ROLE [sysPerformanceTuning] AUTHORIZATION [securityadmin];
GRANT CONNECT SQL TO [sysPerformanceTuning];
GRANT VIEW SERVER STATE TO [sysPerformanceTuning];
GRANT VIEW ANY DEFINITION TO [sysPerformanceTuning];
--Add Login to new Server Role
ALTER SERVER ROLE diskadmin ADD MEMBER [PerformanceTuner];
]]></script>
</span>
</div>
<br />
<div>
<b>Second the database level scripts</b></div>
<div>
These are the same as those defined above. First, create the user defined database roles for master and your user database(s).</div>
<div>
For the master database the script checks for some common diagnostic stored procs, like sp_blitz and sp_whoisactive and grants exec rights to the database role.<br />
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
USE [master]
--Create the database user
CREATE USER [PerformanceTuner] FOR LOGIN [PerformanceTuner];
ALTER USER [PerformanceTuner] WITH DEFAULT_SCHEMA=[dbo];
--CREATE A specific DB Role for performance tuning. Only needs doing once per instance
CREATE ROLE [db_PerformanceTuning] AUTHORIZATION [dbo];
--Check for common performance tuning stored procs and add to DB Role
IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'sp_Blitz')
GRANT EXECUTE ON [dbo].[sp_Blitz] TO [db_PerformanceTuning];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'sp_BlitzCache')
GRANT EXECUTE ON [dbo].[sp_BlitzCache] TO [db_PerformanceTuning];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'sp_BlitzIndex')
GRANT EXECUTE ON [dbo].[sp_BlitzIndex] TO [db_PerformanceTuning];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'sp_BlitzTrace')
GRANT EXECUTE ON [dbo].[sp_BlitzTrace] TO [db_PerformanceTuning];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'sp_AskBrent')
GRANT EXECUTE ON [dbo].[sp_AskBrent] TO [db_PerformanceTuning];
IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'sp_WhoIsActive')
GRANT EXECUTE ON [dbo].[sp_WhoIsActive] TO [db_PerformanceTuning];
--Add User to above DB Role
EXEC sp_addrolemember N'db_PerformanceTuning', N'PerformanceTuner';
]]></script>
</span>
<br />
For the user database(s) the new db role is granted the showplan privilege and, optionally, added to the db_datareader role.<br />
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
DECLARE @SQL NVARCHAR(MAX) = ''
DECLARE @datareader int
SET @datareader = 1
SELECT @SQL = @SQL +
'USE [' + name + '];' + CHAR(10) + --CHAR(13) +
'IF NOT EXISTS (SELECT 1 FROM [sys].[sysusers] WHERE name = ''PerformanceTuner'')' + CHAR(10) +
'BEGIN' + CHAR(10) +
' CREATE USER [PerformanceTuner] FOR LOGIN [PerformanceTuner] ' + CHAR(10) +
'END;' + CHAR(10) + --CHAR(13) +
'ALTER USER [PerformanceTuner] WITH DEFAULT_SCHEMA=[dbo];' + CHAR(10) + --CHAR(13) +
'IF NOT EXISTS (SELECT 1 FROM [sys].[database_principals] WHERE type = ''R'' AND name = ''db_PerformanceTuning'')' + CHAR(10) +
'BEGIN' + CHAR(10) +
' CREATE ROLE [db_PerformanceTuning] AUTHORIZATION [dbo];' + CHAR(10) + --CHAR(13) +
'END;' + CHAR(10) + --CHAR(13) +
'GRANT SHOWPLAN TO [db_PerformanceTuning];' + CHAR(10) + --CHAR(13) +
'EXEC sp_addrolemember N''db_PerformanceTuning'', N''PerformanceTuner'';' + CHAR(10) + --CHAR(13) +
--Optional
CASE WHEN @datareader = 1
THEN 'EXEC sp_addrolemember N''db_datareader'', N''db_PerformanceTuning'';' + CHAR(10)
ELSE '' + CHAR(10)
END
+ CHAR(13)
FROM sys.databases
WHERE database_id > 4
ORDER BY name
]]></script>
</span>
<br />
For msdb the user is given membership to the db_datareader db role<br />
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
USE [msdb];
CREATE USER [PerformanceTuner] FOR LOGIN [PerformanceTuner];
ALTER USER [PerformanceTuner] WITH DEFAULT_SCHEMA=[dbo];
EXEC sp_addrolemember N'db_datareader', N'PerformanceTuner';
]]></script>
</span>
</div>Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com0tag:blogger.com,1999:blog-4616149892194368111.post-77369616105547546432015-05-14T04:53:00.003-07:002015-05-14T04:57:14.230-07:00Database Backup MonitoringNot everyone has access to off-the-shelf monitoring tools. One of my clients is a very small shop with only a few databases and 2 database servers. But that doesn't mean they don't need monitoring. Over the past few weeks I've been implementing various system alerts and now I've deployed the backup monitoring stored procedure below.<br />
<a name='more'></a><br />
In summary it performs the following:<br />
<ul>
<li>Declares the age thresholds for the various backup types (Full, Diff, Log)</li>
<li>Interrogates the msdb backup history tables</li>
<li>Collects a list of files in the backup directories</li>
<li>Sends an email in the case any thresholds are breached or files are missing</li>
</ul>
<div>
There are a few details that need to be kept in mind as well:</div>
<div>
<ul>
<li>Security policies need to allow xp_cmdshell to be enabled (this script checks if it is and if so enables it. If it was originally disabled, it will then be disabled)</li>
<li>The SQL Agent account needs access to the backup directories. If it doesn't you may need to create a proxy</li>
<li>It relies on database mail, so be sure to enable and configure it on your instance</li>
</ul>
<div>
And here's the stored procedure:</div>
</div>
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
CREATE PROCEDURE dbo.sp_BackupMonitor
--Set the datediff thresholds for backup age
@FullDateLimit INT = 7, --days
@DiffDateLimit INT = 2, --days
@TranDateLimit INT = 1, --days
@SysDateLimit INT = 2 --days
-- Database Backup Monitor and Alert
-- Thomas Mucha
-- Last Modified: May 14, 2015
-- http://myhumblesqltips.blogspot.co.uk/
-- LinkedIn: https://uk.linkedin.com/in/thomasmucha
-- Twitter: @MyHumbleSQLTips
AS
BEGIN
SET NOCOUNT ON;
DECLARE @backups TABLE (Dbname SYSNAME, RecoveryModel VARCHAR(20), BackupDate DATETIME, BackupType VARCHAR(20), BackupFileName VARCHAR(256), BackupDirectory VARCHAR(256)
, filemissing INT DEFAULT(0), LogBackupMissing INT, DiffBackupMissing INT, FullBackupMissing INT, SystemFullBackupMissing INT, FullRecoveryNoTranLog INT);
DECLARE @file TABLE (FilePath VARCHAR(256));
DECLARE @FilePaths TABLE (FullPath VARCHAR(512));
DECLARE @FilePath VARCHAR(256);
DECLARE @sql VARCHAR(4000);
DECLARE @prevAdvancedOptions INT;
DECLARE @prevXpCmdshell INT;
DECLARE @error VARCHAR(MAX);
DECLARE @subject NVARCHAR(max) = 'Problem(s) found with one or more backups'; --email subject line
DECLARE @to NVARCHAR(max) = 'email@email.com'; --target email address for error message
DECLARE @profile_name SYSNAME = 'DBmail'; --database mail profile name
--Collect backup history for server and determine if anything is missing. Include/Exclude databases as necessary.
WITH LastBackupDate AS (SELECT MAX(bs.backup_finish_date) AS LastBackupDate, bs.type,bs.database_name
FROM [msdb].[dbo].[backupfile] AS bf
join [msdb].[dbo].backupset AS bs ON bf.backup_set_id = bs.backup_set_id
GROUP BY bs.type,database_name)
INSERT INTO @backups (Dbname, RecoveryModel, BackupDate, BackupType, BackupFileName, BackupDirectory)
SELECT bs.database_name,convert(SYSNAME,DatabasePropertyEx(d.name,'Recovery')) RecoveryModel, lbd.LastBackupDate,CASE bs.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END
, reverse(left(reverse(bmf.physical_device_name),
charindex('\',reverse(bmf.physical_device_name),
1) - 1)) AS BackupFileName
, REPLACE(bmf.physical_device_name,reverse(left(reverse(bmf.physical_device_name),
charindex('\',reverse(bmf.physical_device_name),
1) - 1)),'') AS BackupDirectory
FROM LastBackupDate AS lbd
JOIN [msdb].[dbo].[backupset] AS bs ON bs.backup_finish_date = lbd.LastBackupDate AND bs.database_name = LBD.database_name
JOIN [msdb].[dbo].[backupmediafamily] AS bmf ON bs.media_set_id = bmf.media_set_id
JOIN [master].[sys].[sysdatabases] AS d ON d.name = bs.database_name
ORDER BY bs.database_name;
UPDATE bu
SET FullBackupMissing = CASE
WHEN BackupType = 'Full' AND DATEDIFF(dd,BackupDate,GETDATE()) > @FullDateLimit
THEN 1
ELSE 0
END,
DiffBackupMissing = CASE
WHEN BackupType = 'Differential' AND DATEDIFF(dd,BackupDate,GETDATE()) > @DiffDateLimit
THEN 1
ELSE 0
END,
LogBackupMissing = CASE
WHEN BackupType = 'Transaction log' AND DATEDIFF(dd,BackupDate,GETDATE()) > @TranDateLimit
THEN 1
ELSE 0
END,
SystemFullBackupMissing = CASE
WHEN Dbname IN ('master','model','msdb') AND BackupType = 'Full' AND DATEDIFF(dd,BackupDate,GETDATE()) > @SysDateLimit
THEN 1
ELSE 0
END,
FullRecoveryNoTranLog = CASE
WHEN RecoveryModel IN ('Full', 'BULK_LOGGED') AND NOT EXISTS (SELECT 1 FROM @backups AS b WHERE b.BackupType = 'Transaction log' AND b.Dbname = bu.Dbname)
THEN 1
ELSE 0
END
FROM @backups AS bu;
--Check if xp_cmdshell is enabled. If not save original settings and enable it.
SELECT @prevAdvancedOptions = CAST(value_in_use AS INT) FROM sys.configurations WHERE name = 'show advanced options';
SELECT @prevXpCmdshell = CAST(value_in_use AS INT) FROM sys.configurations WHERE name = 'xp_cmdshell';
IF (@prevAdvancedOptions = 0)
BEGIN
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
END;
IF (@prevXpCmdshell = 0)
BEGIN
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
END;
--Find files in the backup directories
INSERT INTO @FilePaths
SELECT DISTINCT BackupDirectory FROM @backups;
WHILE EXISTS (SELECT 1 FROM @FilePaths)
BEGIN
SET @FilePath = (SELECT TOP 1 FullPath FROM @FilePaths)
SET @sql = 'dir "' + @FilePath + '" /s/b'
INSERT INTO @file
EXEC xp_cmdshell @sql;
DELETE FROM @FilePaths WHERE FullPath = @FilePath
END;
DELETE FROM @file WHERE FilePath IS NULL;
--Check if xp_cmdshell was originally disabled. If so disable it.
IF (@prevXpCmdshell = 0)
BEGIN
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
END;
IF (@prevAdvancedOptions = 0)
BEGIN
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
END;
UPDATE bu
SET filemissing = 1
FROM @backups AS bu
LEFT JOIN @file AS f ON bu.BackupDirectory + bu.BackupFileName = f.FilePath
WHERE f.FilePath IS NULL;
--Analyze the results and compile the error message if necessary.
BEGIN TRY
IF EXISTS (SELECT 1
FROM @backups
WHERE filemissing > 0 OR LogBackupMissing > 0 OR DiffBackupMissing > 0 OR FullBackupMissing > 0 OR SystemFullBackupMissing > 0 OR FullRecoveryNoTranLog > 0
)
BEGIN
SET @error = 'Problem(s) found with one or more backups: ' + CHAR(10) + CHAR(13)
SELECT @error = @error
+ CASE WHEN filemissing > 0 THEN 'The backup file ' + BackupFileName + ' for ''' + Dbname + ''' is missing FROM the directory ' + BackupDirectory + CHAR(10) + CHAR(13) ELSE '' END
+ CASE WHEN FullBackupMissing = 1 THEN '''' + Dbname + ''' FULL Backup is out of date (last backup is over 7 days old)' + CHAR(10) + CHAR(13) ELSE '' END
+ CASE WHEN DiffBackupMissing = 1 THEN '''' + Dbname + ''' DIFF Backup is out of date (last backup is over 1 day old)' + CHAR(10) + CHAR(13) ELSE '' END
+ CASE WHEN LogBackupMissing = 1 THEN '''' + Dbname + ''' LOG Backup is out of date (last backup is over 1 day old)' + CHAR(10) + CHAR(13) ELSE '' END
+ CASE WHEN SystemFullBackupMissing = 1 THEN 'System database ''' + Dbname + ''' ' + BackupType + ' Backup is out of date (last backup is over 2 days old)' + CHAR(10) + CHAR(13) ELSE '' END
+ CASE WHEN FullRecoveryNoTranLog = 1 THEN '''' + Dbname + ''' is set to the ' + RecoveryModel + ' but has no Transaction Log backups' + CHAR(10) + CHAR(13) ELSE '' END
FROM @backups
ORDER BY Dbname, BackupType;
RAISERROR (@error,16,-1);
END;
END TRY
BEGIN CATCH
PRINT @error;
EXEC [msdb].[dbo].sp_send_dbmail @profile_name = @profile_name, @recipients = @to, @subject = @subject, @body = @error;
END CATCH;
END;
]]></script>
</span>
Acknowledgements:
- Geoff on <a href="http://stackoverflow.com/questions/5131491/enable-xp-cmdshell-sql-server" target="_blank">stackexchange</a> for the xp_cmdshell script; Kalman Toth (<a href="http://www.sqlusa.com/bestpractices/parsefilename/" target="_blank">sqlusa.com</a>) for the file path parsing script.Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com1tag:blogger.com,1999:blog-4616149892194368111.post-72782080928056034612015-04-30T10:18:00.001-07:002015-05-06T03:20:38.501-07:00Tracking Query Plan ChangesI was reading a blog post from Brent Ozar (<a href="http://www.brentozar.com/archive/2014/11/sql-server-query-store/" target="_blank">SQL Server Query Store</a>), which detailed Microsoft's announced future feature in SQL Server that aims to store a history of cached execution plans. The main purpose, it seems, of such a feature is to aid in performance troubleshooting and tuning. If something has changed recently and a query or store procedure begins behaving badly, there will be another tool in the DBA toolbelt to aid in finding what happened. Beyond that I'll let <a href="https://www.google.co.uk/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8" target="_blank">others explain it further</a>.
<br />
<a name='more'></a><br />
I was inspired by a comment under the post that suggested one could set up such a store of his/her own. Aha, I thought! Why don't I try that. Combine that with the fact that April is quickly coming to a close and I have another blog post to write for the #SQLNewBlogger challenge... and voila!<br />
<br />
Now, to caveat this: It is a work in progress and I welcome any and all input into how this could be developed - including any query improvements.<br />
<br />
Now, you might ask, why would I want to do this if Microsoft has plans to release it. Well, for a few reasons: 1. They haven't committed to a release date, I think; 2. Chances are that I and many others won't get our hands on that new release for a long while; 3. I thought it would be a fun challenge.<br />
<br />
<h3>
The Solution</h3>
There are a few components involved in this mini-solution:<br />
<ol>
<li>A table to store the query plan data</li>
<li>A query and scheduled job to collect the data</li>
<li>A query to report on the changes</li>
<li>A cleanup script to keep the query store table manageable</li>
</ol>
<h4>
Step 1 - The Table</h4>
<div>
I've created the table below to store what I felt might be the relevant data for future execution plan investigation. I hope it is self explanatory.</div>
<div>
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
USE [AdminDB]
GO
CREATE TABLE [dbo].[QueryPlans](
[id] INT primary key clustered identity(1,1),
[dbid] [INT] NOT NULL,
[usecounts] [INT] NOT NULL,
[size_in_bytes] [INT] NOT NULL,
[cacheobjtype] [NVARCHAR](50) NOT NULL,
[objtype] [NVARCHAR](20) NOT NULL,
[objectid] [INT] NULL,
[objectname] [SYSNAME] NULL,
[objcreatedate] [DATETIME] NULL,
[objupdatedate] [DATETIME] NULL,
[plan_handle] [VARBINARY](64) NOT NULL,
[query_plan] [XML] NULL,
[xml_plan_checksum] [BIGINT] NULL,
[text] [NVARCHAR](MAX) NULL,
[text_checksum] [BIGINT] NULL,
[DateAdded] [DATETIME] NULL DEFAULT (getdate())
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE INDEX [IX_QueryPlans_textplanchecksum_textchecksum] ON [dbo].[QueryPlans] ([xml_plan_checksum],[text_checksum])
CREATE PRIMARY XML INDEX IX_QueryPlans_queryplan_xml ON dbo.QueryPlans ([query_plan])
GO
]]></script>
</span>
</div>
<h4>
Step 2 - Data Collection</h4>
<div>
The queries below do two things: 1. Collect the execution plans and upsert them to the above defined table; 2. Update the table with the object name (where available) of the object collected.<br />
<br />
A few notes as to the logic of my data collection:<br />
<ul>
<li>In order to be able to more easily self-join the queries, without having to compare long text strings, there are two checksum columns: one to represent the query string; the other to represent the execution plan xml. Here I ran into a curious issue in that often, even if the execution plan logic is the same, the checksum is calculated differently. This is down to the fact that compilation cpu and elapsed times may differ. Therefore, I have found it hard to accurately differentiate between a recompiled plan vs a truly different plan. </li>
<li>Since the execution plans are held centrally for the whole server and the object names are referenced at the scope of the database, I needed to do this in multiple steps. The dynamic update query was modified from examples from <a href="http://blogs.lessthandot.com/index.php/datamgmt/datadesign/how-to-get-information-about-all-databas/" target="_blank">Naomi Nosonovsky</a>.</li>
<li><div>
I have filtered my results to only collect user database plans and non-system objects. This is easily customised, so you can collect as many or as few query plans as you like. For instance, you may want to only track certain stored procedures or a subset of databases.</div>
</li>
</ul>
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
USE AdminDB
GO
DECLARE @objects TABLE (DB SYSNAME, ObjectName SYSNAME, object_id BIGINT, create_date DATETIME, modify_date DATETIME, [type] CHAR(2), [dbid] INT);
DECLARE @sql NVARCHAR(MAX);
MERGE dbo.QueryPlans AS target
USING (SELECT st.[dbid], usecounts, size_in_bytes, cacheobjtype, objtype, st.objectid, cp.plan_handle, qp.query_plan, CHECKSUM(CONVERT(VARCHAR(MAX),qp.query_plan)) AS xml_plan_checksum, [text], CHECKSUM([text]) AS text_checksum
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) AS st
WHERE st.dbid > 4 AND objtype != 'Adhoc') AS source
ON source.text_checksum = target.text_checksum AND source.xml_plan_checksum = target.xml_plan_checksum
WHEN MATCHED AND target.usecounts != source.usecounts THEN
UPDATE SET usecounts = source.usecounts
WHEN NOT MATCHED THEN
INSERT ([dbid],usecounts,size_in_bytes,cacheobjtype,objtype,objectid,plan_handle,query_plan,[xml_plan_checksum],[text],text_checksum)
VALUES (source.[dbid], source.usecounts, source.size_in_bytes, source.cacheobjtype, source.objtype, source.objectid, source.plan_handle, source.query_plan, source.xml_plan_checksum, source.[text], source.text_checksum)
;
SET @sql = N'SELECT b.name AS "DB", a.name collate Latin1_General_CI_AI AS "ObjectName", object_id, a.create_date, a.modify_date, a.type, CAST(1 AS INT) AS database_id
FROM master.sys.objects a JOIN sys.databases b ON database_id= 1 AND a.type != ''S''
';
SELECT @sql = @sql + N' union SELECT b.name AS "DB", a.name collate Latin1_General_CI_AI, a.object_id, a.create_date, a.modify_date, a.type, ' + CAST(database_id AS NVARCHAR(10))
+ N' FROM ' + quotename(name) + N'.sys.objects a
JOIN sys.databases b ON database_id=' + CAST(database_id AS NVARCHAR(10))
+ ' JOIN AdminDB.dbo.[QueryPlans] AS qp ON ' + CAST(database_id AS NVARCHAR(10)) + ' = qp.[dbid]
WHERE a.type != ''S'''
FROM sys.databases WHERE database_id > 4;
INSERT INTO @objects
EXEC sp_executesql @sql;
UPDATE qp
SET [objectname] = o.ObjectName, [objcreatedate] = o.create_date, [objupdatedate] = o.modify_date
FROM dbo.QueryPlans AS qp
JOIN @objects AS o ON o.object_id = qp.objectid
WHERE qp.objectname IS NOT NULL;
GO
]]></script>
</span>
</div>
<h4>
Step 3 - Report Query</h4>
<div>
This query will compare the rows within the QueryPlans table and display the following*:</div>
<div>
<ul>
<li>Basic data</li>
<ul>
<li>db id</li>
<li>Object ID if applicable</li>
<li>Object name if applicable</li>
<li>Query text</li>
</ul>
<li>The three most recent:</li>
<ul>
<li>Execution plans</li>
<li>Execution plan capture date (when the data collector query was run)</li>
<li>Plan_handles</li>
</ul>
<li>Details of a changed object - in the case that a stored proc or function is modified</li>
<ul>
<li>Date of change - modified date from sys.objects</li>
<li>Changed query text</li>
<li>Execution plan of changed object</li>
</ul>
</ul>
*I am not 100% confident that all the data is displayed in the correct sequence. However, I feel that there is enough information to aid in highlighting any potential changes that may have occurred.</div>
<div>
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
USE AdminDB
GO
;WITH PlanCompare
AS ( SELECT i.id ,
i.[dbid] ,
i.[text_checksum] ,
i.xml_plan_checksum ,
i.DateAdded ,
i.objectid,
i.objectname,
i.[objupdatedate],
i.plan_handle,
i.query_plan,
i.[text],
ROW_NUMBER() OVER (PARTITION BY i.[text_checksum] ORDER BY i.DateAdded ) AS rownum
FROM dbo.QueryPlans i
),
MinRowNum
AS ( SELECT MIN(rownum) as minrownum,[text_checksum]
FROM PlanCompare
GROUP BY [text_checksum]
)
SELECT COALESCE([second].[dbid],[first].[dbid]/*,[third].[dbid]*/) AS [dbid],
COALESCE([second].objectid,[first].objectid/*,[third].objectid*/) AS ObjectID,
COALESCE([second].objectname,[first].objectname/*,[third].objectname*/) AS ObjectName,
[first].text_checksum AS [1st_QueryText_checksum],
[second].text_checksum AS [2nd_QueryText_checksum],
[third].text_checksum AS [3rd_QueryText_checksum],
[first].[text] AS QueryText,
[QueryChange].[text] as ChangedQuery,
[QueryChange].text_checksum as ChangedQuery_Checksum,
[first].DateAdded AS [1st_PlanDate] ,
[second].DateAdded AS [2nd_PlanDate],
[third].DateAdded AS [3rd_PlanDate],
[QueryChange].[objupdatedate] AS [QueryChangeDate],
[first].plan_handle AS [1st_PlanHandle],
[second].plan_handle AS [2nd_PlanHandle],
[third].plan_handle AS [3rd_PlanHandle],
[first].query_plan AS [1st_Plan],
[second].query_plan AS [2nd_Plan],
[third].query_plan AS [3rd_Plan],
[QueryChange].query_plan AS ChangedQueryPlan
FROM PlanCompare [second]
LEFT JOIN PlanCompare [first] ON [first].rownum = [second].rownum - 1
AND [second].[text_checksum] = [first].[text_checksum]
JOIN MinRowNum as minrow ON [first].rownum = minrow.minrownum AND [first].[text_checksum] = minrow.[text_checksum]
LEFT JOIN PlanCompare [third] ON [third].rownum = [second].rownum + 1
AND [second].[text_checksum] = [third].[text_checksum]
LEFT JOIN PlanCompare [QueryChange] ON ([first].[text_checksum] != [QueryChange].[text_checksum] AND [first].objectname = [QueryChange].objectname AND [first].DateAdded < [QueryChange].[objupdatedate])
OR ([second].[text_checksum] != [QueryChange].[text_checksum] AND [second].objectname = [QueryChange].objectname )
OR ([third].[text_checksum] != [QueryChange].[text_checksum] AND [third].objectname = [QueryChange].objectname AND [third].DateAdded > [QueryChange].[objupdatedate] )
WHERE ([first].xml_plan_checksum is not null AND [second].xml_plan_checksum is not null
OR [third].xml_plan_checksum is not null AND [second].xml_plan_checksum is not null)
ORDER BY ObjectID,[1st_PlanDate];
]]></script>
</span>
</div>
<h4>
Step 4 - Cleanup Script</h4>
<div>
Since the reporting query above is written to display the most recent 3 execution plans for a given query, the cleanup script will delete the oldest record for any query that has more than three rows.
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
USE AdminDB
;WITH cte as (SELECT ROW_NUMBER() OVER( PARTITION BY text_checksum ORDER BY DateAdded ASC) as rownum,text_checksum
FROM dbo.QueryPlans
)
DELETE FROM cte
WHERE rownum = 1
AND text_checksum in (SELECT text_checksum FROM cte WHERE rownum > 3)
]]></script>
</span>
</div>
<h3>
Conclusion</h3>
<div>
I hope that this has been an interesting post. If nothing else, writing it it has forced me to look deeper into how execution plans are held in SQL Server.<br />
<br />
Please feel free to give me any feedback and tips for improvements. As I mentioned at the beginning, this is a first draft and a work in progress.<br />
<br />
<br /></div>
Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com0tag:blogger.com,1999:blog-4616149892194368111.post-9876358419563385332015-04-17T08:59:00.001-07:002015-04-27T03:02:04.527-07:00Tracking Page SplitsAs a DBA, performance tuning is a regular ongoing task. In my experience it is both a reactive and proactive task. The goal, though, is for your proactive efforts to minimise the time spent on the reactive. This has lead me to focus more on page splitting and index fill factor. Specifically, their interaction. There is a lot of information on the web about the benefits and dangers of fill factor and the performance implications of high rates of page splitting, so I will not go into it too much. I will discuss what I've done to monitor page splits. I then tweak fillfactor on the worst offending tables and indexes.<br />
<a name='more'></a><br />
First off, if you want some good information on these concepts read the following articles:<br />
<ul>
<li><a href="http://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/" target="_blank">Fill Factor Intro by Kendra Little</a></li>
<li><a href="http://www.sqlskills.com/blogs/jonathan/tracking-problematic-pages-splits-in-sql-server-2012-extended-events-no-really-this-time/" target="_blank">Tracking Page Splits with Extended Events</a></li>
<li><a href="http://myhumblesqltips.blogspot.co.uk/2013/05/defragging-indexes-without-losing-index.html" target="_blank">Index Maintenance</a></li>
</ul>
The implementation of my solution involves the following:<br />
<ul>
<li>Extended events that track page splits based on the second post above. They track two things </li>
<ul>
<li>a running total of splits at the db level </li>
<li>a running total of splits at the table/index level.</li>
</ul>
<li>Creation of two tables to keep a history of pagesplits</li>
<ul>
<li>At database level</li>
<li>At table/index level</li>
</ul>
<li>An SSIS package that:</li>
<ul>
<li>Loads the results into a table in my AdminDB</li>
<li>Resets the counter by stopping extended events</li>
<li>Cleaning up the XML files</li>
</ul>
<li>Create 2 SQL Agent Jobs </li>
<ul>
<li>Start the sessions </li>
<li>Collect the data and stop the sessions</li>
</ul>
<li>An SSRS report showing page split changes over time</li>
</ul>
My main idea is to use this in a targeted manner to identify page splitting hotspots. By no means should these extended events, namely the table/index specific one, be run continuously. Lots of data will be collected, especially if your system experiences high page split volumes. For more info see the <a href="http://www.sqlskills.com/blogs/jonathan/tracking-problematic-pages-splits-in-sql-server-2012-extended-events-no-really-this-time/" target="_blank">Tracking Page Splits with Extended Events</a> blog post for more.<br />
<br />
The idea behind this is that two jobs are set up. The first checks for the existence of the objects and creates them if necessary. The second will run the SSIS package to both collect the data into the history tables and stop the sessions. They can then be scheduled in order to collect page split data for time periods you feel comfortable with, let's say for a few hours up to a full day. You can then interrogate the tables between runs and make any changes to indexes (i.e. FillFactor) that may help relieve the page splitting.<br />
<br />
The extended event can be created as follows:<br />
<ul>
<li>Database wide page splits</li>
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
CREATE EVENT SESSION [TrackPageSplits] ON SERVER
ADD EVENT sqlserver.transaction_log (
WHERE ( [Operation] = ( 11 ) ) )
ADD TARGET package0.histogram ( SET filtering_event_name = N'sqlserver.transaction_log' ,
source = N'database_id' ,
source_type = ( 0 ) )
WITH ( MAX_MEMORY = 4096 KB ,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS ,
MAX_DISPATCH_LATENCY = 30 SECONDS ,
MAX_EVENT_SIZE = 0 KB ,
MEMORY_PARTITION_MODE = NONE ,
TRACK_CAUSALITY = OFF ,
STARTUP_STATE = OFF )
GO
ALTER EVENT SESSION [TrackPageSplits]
ON SERVER
STATE=START;
GO
]]></script>
</span>
</ul>
<br />
<ul>
<li>Table/Index specific page splits</li>
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
CREATE EVENT SESSION [TrackPageSplits_Indexes] ON SERVER
ADD EVENT sqlserver.transaction_log (SET collect_database_name=(1)
WHERE [Operation] = 11 --LOP_DELETE_SPLIT
--AND database_id IN (5,6) -- CHANGE THIS TO THE DATABASE(S) YOU WANT TO TRACK!
)
ADD TARGET package0.asynchronous_file_target
(SET filename = 'C:\temp\XEventSessions\pagesplit.xel',
metadatafile = 'C:\temp\XEventSessions\pagesplit.xem',
max_file_size=5,
max_rollover_files=5)
WITH (MAX_DISPATCH_LATENCY = 5SECONDS);
GO
ALTER EVENT SESSION [TrackPageSplits_Indexes]
ON SERVER
STATE=START;
GO
]]></script>
</span>
The target of this event is an XML file for two reasons: 1. More data, e.g. database name, is exposed compared to the histogram; 2. The potentially high volume of data may cause memory pressure if the ring buffer or histogram is used. I've also set the SSIS package to delete the XML files after the data is collected in the tables below preventing large files form accumulating on disk.
</ul>
The table scripts are:<br />
<ul>
<li>Database wide page splits</li>
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
CREATE TABLE dbo.[atblPageSplitsDatabase]
(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DatabaseId INT NOT NULL,
DatabaseName SYSNAME NOT NULL,
SplitCount INT NOT NULL,
DateRecorded DATE DEFAULT (GETDATE())
);
]]></script>
</span>
</ul>
<br />
<ul>
<li>Table/Index specific page splits</li>
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
CREATE TABLE dbo.[atblPageSplitsIndexes]
(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
DatabaseId INT NOT NULL,
DatabaseName NVARCHAR(256) NOT NULL,
TableName NVARCHAR(256) NOT NULL,
IndexName NVARCHAR(256) NOT NULL,
SplitCount INT NOT NULL,
DateRecorded DATE DEFAULT (GETDATE())
);
]]></script>
</span>
</ul>
<br />
I have created the above tables in my AdminDB. Some of you may have read my <a href="http://myhumblesqltips.blogspot.co.uk/2013/05/defragging-indexes-without-losing-index.html" target="_blank">earlier posts</a> about index maintenance and the retention of index usage stats. This will come in handy when evaluating the page splits because you'll be maintaining a history, not only of index usage, but of index maintenance.<br />
<br />
You can find the SSIS package on my <a href="https://drive.google.com/folderview?id=0B-znT8ogfZ3LTHB2ZWdqRFNEWTg&usp=sharing" target="_blank">Google Drive</a>. The queries that collect the data from the Extended Event targets are as follows:<br />
<ul>
<li>Database wide page splits</li>
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
SELECT
n.value('(value)[1]', 'BIGINT') AS database_id,
DB_NAME(n.value('(value)[1]', 'BIGINT')) AS database_name,
n.value('(@count)[1]', 'BIGINT') AS split_count
FROM
(SELECT CAST(target_data AS XML) target_data
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets t
ON s.address = t.event_session_address
WHERE s.name = 'TrackPageSplits'
AND t.target_name = 'histogram' ) AS tab
CROSS APPLY target_data.nodes('HistogramTarget/Slot') AS q(n)
]]></script>
</span>
<li>Table/Index specific page splits</li>
<span style="font-size: x-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
DECLARE @table table (DBName SYSNAME, TableName SYSNAME, Index_Name SYSNAME, allocation_unit_id BIGINT,fill_factor INT)
DECLARE @SQL nvarchar(max)
SET @SQL = ''
SELECT @SQL = @SQL +
'Select ' + quotename(name,'''') + ' AS [DB Name],
object_Name(PS.Object_ID,' + convert(VARCHAR(10),database_id) + ') AS [Object],
I.Name AS [Index Name],
au.allocation_unit_id,
I.fill_factor
FROM ' + quotename(name) + '.sys.dm_db_index_physical_stats(' +
convert(VARCHAR(10),database_id) + ', NULL, NULL, NULL, NULL) PS
INNER JOIN ' + quotename(name) + '.sys.Indexes I on PS.Object_ID = I.Object_ID AND PS.Index_ID = I.Index_ID
INNER JOIN ' + quotename(name) + '.sys.partitions AS p ON p.object_id = i.object_id AND p.index_id = i.index_id
INNER JOIN ' + quotename(name) + '.sys.allocation_units AS au ON au.container_id = p.partition_id'
+ CHAR(13)
FROM sys.databases WHERE state_desc = 'ONLINE'
AND database_id > 4
INSERT into @table
EXECUTE(@SQL);
WITH events_cte AS (
SELECT
DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
xevents.event_data.value('(event/@timestamp)[1]',
'datetime2')) AS [event time] ,
xevents.event_data.value('(event/data[@name="database_id"]/value)[1]', 'int')
AS [database_id],
xevents.event_data.value('(event/data[@name="database_name"]/value)[1]', 'varchar(max)')
AS [database name],
xevents.event_data.value('(event/data[@name="alloc_unit_id"]/value)[1]', 'BIGINT')
AS [alloc_unit_id]
FROM sys.fn_xe_file_target_read_file
('C:\temp\XEventSessions\pagesplit*.xel',
'C:\temp\XEventSessions\pagesplit*.xem',
null, null)
CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) AS xevents
)
SELECT cte.database_id, DB_NAME(cte.database_id) as DatabaseName
,t.TableName,t.Index_Name
,count(cte.[alloc_unit_id])
FROM @table AS t
join events_cte AS cte on t.allocation_unit_id = cte.alloc_unit_id AND t.DBName = cte.[database name]
group by CONVERT(DATE,[event time]),cte.database_id, cte.[database name],t.TableName,t.Index_Name
]]></script>
</span>
</ul>
<div>
The SSRS report, also downloadable from my <a href="https://drive.google.com/file/d/0B-znT8ogfZ3LRUdLOElxZnZKRlk/view?usp=sharing" target="_blank">Google Drive</a>, that displays a graph of page splits by database, table and index over time:</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiexP1JOfmFFSnJmdo1uhHZsB70wIkMAYkQufib9Se159BtIajKKienWQWZ2QTbCMlC-mEA2A4Qc2mwXBN32dhS9g6PgXn7-lZ0fPsLgFT6a0w1tF6VjdM_ESF0lplj4W3VkCQTfRgOWdRi/s1600/PageSplitGraph.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiexP1JOfmFFSnJmdo1uhHZsB70wIkMAYkQufib9Se159BtIajKKienWQWZ2QTbCMlC-mEA2A4Qc2mwXBN32dhS9g6PgXn7-lZ0fPsLgFT6a0w1tF6VjdM_ESF0lplj4W3VkCQTfRgOWdRi/s1600/PageSplitGraph.png" height="252" width="320" /></a></div>
<div>
<br /></div>
<div>
Finally, here are links to the scripts for the two jobs mentioned above:<br />
<br />
<ul>
<li><a href="https://drive.google.com/file/d/0B-znT8ogfZ3LeTlfbFpESHkta3M/view?usp=sharing" target="_blank">PageSplits - Start ExtendedEvents</a></li>
<li><a href="https://drive.google.com/file/d/0B-znT8ogfZ3LMVhIclNXV3A2LU0/view?usp=sharing" target="_blank">PageSplits - Collect Data and Stop Sessions</a></li>
</ul>
<br />
<br />
I hope that this has been helpful.</div>Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com1tag:blogger.com,1999:blog-4616149892194368111.post-75209251188280122922015-04-09T08:07:00.002-07:002015-04-09T08:16:13.448-07:00Running DBCC CHECKDB on TEMPDBFirst, a confession. I've been doing it wrong for a long time. Only recently have I learned that TempDB should be included in regular CHECKDB maintenance. Ok, now that I've got that off my chest, I can continue.<br />
<br />
So now I've been diligently setting up this task on all servers. NB: TempDB is not available within Maintenance Plans, so you'll need to write the tsql in a job. I add it as a second step in my dbcc maintenance plan job.<br />
<a name='more'></a>
<br />
The problem, however, is that database snapshots in TempDB are not available and SQL Server takes a TABLOCK on all the objects being checked. I kept getting the following error message when checkdb was run, causing my job to fail:<br />
<blockquote class="tr_bq">
<span style="font-size: x-small;">Object ID 1065627435 (object 'dbo.#TempTable_________________________________________________________________________________________0000000011BE'): DBCC could not obtain a lock on this object because the lock request timeout period was exceeded. This object has been skipped and will not be processed. [SQLSTATE 42000] (Error 5245). </span></blockquote>
There were two options: 1. Schedule the maintenance task to run when nothing else was happening. Not really possible. 2. Write a script that checks for locking before it runs the DBCC command. I chose option 2.<br />
<br />
I wrote a loop that runs until there are no exclusive locks within TempDB. I put in a delay so that the loop gives the database server a bit of a break in the event of long running queries. Here it is:
<br />
<br />
<span style="font-size: xx-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
DECLARE @tab table (spid int, db int, ObjId int,IndId int,[Type] Varchar(10), Res Varchar(100), Mode Varchar(10), [Status] Varchar(20))
WHILE (1=1)
BEGIN
INSERT INTO @tab
exec sp_lock
if (select count(*)
from @tab
where db_name(db) = 'tempdb' and Mode = 'X') = 0
BEGIN
DBCC checkdb ('tempdb')
BREAK
END
DELETE FROM @tab
WAITFOR DELAY '00:00:10'
END;
]]>
</script>
</span>
<br />
I hope this is helpful.Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com0tag:blogger.com,1999:blog-4616149892194368111.post-56410417197944827692015-04-08T11:07:00.000-07:002015-04-13T03:06:59.655-07:00Full List of SQL Server 2014 DMVsI'm not sure how useful this will be to others, but I keep searching for a single, comprehensive list of all the SQL Server DMVs with short descriptions. I am looking to complete a certification and this should prove handy for studying. This is certainly no replacement for the full descriptions and examples on MSDN, but I thought it worth putting together.<br />
<a name='more'></a><br />
A simple query allows you to list all the DMVs on your SQL Server instance (see below - taken from <a href="http://blog.sqlauthority.com/2010/05/15/sql-server-list-all-the-dmv-and-dmf-on-server/">Pinal Dave's blog</a>), but it does not provide any descriptions.
<br />
<span style="font-size: xx-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
SELECT name, type, type_desc
FROM sys.system_objects
WHERE name LIKE 'dm_%'
ORDER BY name;
]]>
</script>
</span>
<br />
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 1200px;">
<colgroup><col style="mso-width-alt: 12580; mso-width-source: userset; width: 258pt;" width="344"></col>
<col style="mso-width-alt: 21394; mso-width-source: userset; width: 439pt;" width="585"></col>
</colgroup><tbody>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><b><u>AlwaysOn
Availability Group </u></b></td>
<td class="xl65" style="width: 439pt;" width="585"></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/hh213504.aspx">sys.dm_hadr_auto_page_repair</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td>Returns a row for every automatic page-repair attempt on any availability
database on an availability replica that is hosted for any availability group
by the server instance.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/ff878491.aspx">sys.dm_hadr_availability_group_states</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns a row for each AlwaysOn availability
group that possesses an availability replica on the local instance of SQL
Server. Each row displays the states that define the health of a given
availability group.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/hh510256.aspx">sys.dm_hadr_availability_replica_cluster_nodes</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns a row for every availability replica
(regardless of join state) of the AlwaysOn availability groups in the Windows
Server Failover Clustering (WSFC) cluster.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/hh403396.aspx">sys.dm_hadr_availability_replica_cluster_states</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns a row for each AlwaysOn availability
replica (regardless of its join state) of all AlwaysOn availability groups
(regardless of replica location) in the Windows Server Failover Clustering
(WSFC) cluster.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/ff878537.aspx">sys.dm_hadr_availability_replica_states</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns a row for each local availability
replica and a row for each remote availability replica in the same AlwaysOn
availability group as a local replica. Each row contains information about
the state of a given availability replica.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/hh212952.aspx">sys.dm_hadr_cluster</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">If the Windows Server Failover Clustering (WSFC) node that
hosts an instance of SQL Server that is enabled for AlwaysOn Availability
Groups has WSFC quorum, sys.dm_hadr_cluster returns a row that exposes the
cluster name and information about the quorum. If the WSFC node has no
quorum, no row is returned.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/hh231519.aspx">sys.dm_hadr_cluster_members</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">If the WSFC node that hosts a local instance of SQL Server
that is enabled for AlwaysOn Availability Groups has WSFC quorum, returns a
row for each of the members that constitute the quorum and the state of each
of them.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/hh213657.aspx">sys.dm_hadr_cluster_networks</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns a row for every WSFC cluster member
that is participating in an availability group's subnet configuration. You
can use this dynamic management view to validate the network virtual IP that
is configured for each availability replica.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/hh213319.aspx">sys.dm_hadr_database_replica_cluster_states</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns a row for each AlwaysOn availability
replica (regardless of its join state) of all AlwaysOn availability groups
(regardless of replica location) in the Windows Server Failover Clustering
(WSFC) cluster.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/ff877972.aspx">sys.dm_hadr_database_replica_states</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns a row for each database that is
participating in an AlwaysOn availability group for which the local instance
of SQL Server is hosting an availability replica.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/hh710076.aspx">sys.dm_hadr_instance_node_map</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">For every instance of SQL Server that hosts
an availability replica that is joined to its AlwaysOn availability group,
returns the name of the Windows Server Failover Clustering (WSFC) node that
hosts the server instance.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/hh710079.aspx">sys.dm_hadr_name_id_map</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Shows the mapping of AlwaysOn availability
groups that the current instance of SQL Server has joined to three unique
IDs: an availability group ID, a WSFC resource ID, and a WSFC Group ID. The
purpose of this mapping is to handle the scenario in which the WSFC
resource/group is renamed.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/hh245287.aspx">sys.dm_tcp_listener_states</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns a row containing dynamic-state
information for each TCP listener.
<br />
<br /></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><b><u>Change Data Capture</u></b></td>
<td class="xl67" style="width: 439pt;" width="585"></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb510694.aspx">sys.dm_cdc_log_scan_sessions</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns one row for each log scan session in
the current database. The last row returned represents the current session.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms180036.aspx">sys.dm_repl_traninfo</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns information on each replicated or
change data capture transaction.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb500301.aspx">sys.dm_cdc_errors</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns one row for each error encountered
during the change data capture log scan session.
<br />
<br /></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><b><u>Change Tracking</u></b></td>
<td class="xl67" style="width: 439pt;" width="585"></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/cc645959.aspx"> sys.dm_tran_commit_table </a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Displays one row for each transaction that is committed for a table that is tracked by SQL Server change tracking.
<br />
<br /></td></tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><b><u>Common Language Runtime</u></b></td>
<td class="xl67" style="width: 439pt;" width="585"></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms187720.aspx">sys.dm_clr_appdomains</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns a row for each application domain in
the server. Application domain (<span class="font6">AppDomain</span><span class="font5">) is a construct in the Microsoft .NET Framework common
language runtime (CLR) that is the unit of isolation for an application. You
can use this view to understand and troubleshoot CLR integration objects that
are executing in Microsoft SQL Server.</span></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms174983.aspx">sys.dm_clr_properties</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns a row for each property related to
SQL Server common language runtime (CLR) integration, including the version
and state of the hosted CLR. </td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms186922.aspx">sys.dm_clr_loaded_assemblies</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns a row for each managed user assembly
loaded into the server address space. Use this view to understand and
troubleshoot CLR integration managed database objects that are executing in
Microsoft SQL Server.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms177528.aspx">sys.dm_clr_tasks</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns a row for all common language runtime
(CLR) tasks that are currently running.
<br />
<br /></td></tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><b><u>Database
Mirroring</u></b></td>
<td class="xl67" style="width: 439pt;" width="585"></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms189796.aspx">sys.dm_db_mirroring_connections</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns a row for each connection established
for database mirroring.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb677259.aspx">sys.dm_db_mirroring_auto_page_repair</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns a row for every automatic page-repair
attempt on any mirrored database on the server instance. This view contains
rows for the latest automatic page-repair attempts on a given mirrored
database, with a maximum of 100 rows per database.
<br />
<br /></td></tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><b><u>Database
Related</u></b></td>
<td class="xl67" style="width: 439pt;" width="585"></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/ms174412.aspx">sys.dm_db_file_space_usage</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns space usage information for each file
in the database.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/gg492194.aspx">sys.dm_db_fts_index_physical_stats</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns a row for each full-text or semantic
index in each table that has an associated full-text or semantic index.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/ms187737.aspx">sys.dm_db_partition_stats</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns page and row-count information for
every partition in the current database.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/cc280724.aspx">sys.dm_db_persisted_sku_features</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Use the sys.dm_db_persisted_sku_features
dynamic management view to list all edition-specific features that are
enabled in the current database.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/ms187938.aspx">sys.dm_db_session_space_usage</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns the number of pages allocated and
deallocated by each session for the database.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/ms190288.aspx">sys.dm_db_task_space_usage</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns page allocation and deallocation
activity by task for the database.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/ff929336.aspx">sys.dm_db_uncontained_entities</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Shows any uncontained objects used in the
database. Uncontained objects are objects that cross the database boundary in
a contained database. </td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/dn269834.aspx">sys.dm_db_wait_stats
(Azure SQL Database)</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns information about all the waits
encountered by threads that executed during operation. You can use this
aggregated view to diagnose performance issues with Azure SQL Database and
also with specific queries and batches.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/dn270023.aspx">sys.dm_database_copies
(Azure SQL Database)</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns information about the database copy.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/dn270022.aspx">sys.dm_operation_status
(Azure SQL Database)</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns information about operations
performed on databases in a Azure SQL Database server.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/dn270019.aspx">sys.dm_db_objects_impacted_on_version_change
(Azure SQL Database)</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">This database-scoped system view is designed
to provide an early warning system to determine objects that will be impacted
by a major release upgrade in Azure SQL Database.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/dn800981.aspx">sys.dm_db_resource_stats
(Azure SQL Database)</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns CPU, I/O, and memory consumption for
an Azure SQL Database database. One row exists for every 15 seconds, even if
there is no activity in the database. Historical data is maintained for one
hour.<br />
<br /></td></tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" height="20" style="height: 15.0pt; width: 258pt;" width="344"><b><u>Execution Related</u></b></td>
<td class="xl65" style="width: 439pt;" width="585"></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms173512.aspx">sys.dm_exec_background_job_queue</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">Returns a row for each query processor job that is scheduled
for asynchronous (background) execution.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms176059.aspx">sys.dm_exec_background_job_queue_stats</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">Returns a row that provides aggregate statistics for each
query processor job submitted for asynchronous (background) execution.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms187404.aspx">sys.dm_exec_cached_plans</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">Returns a row for each query plan that is cached by SQL Server
for faster query execution. You can use this dynamic management view to find
cached query plans, cached query text, the amount of memory taken by cached
plans, and the reuse count of the cached plans.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms403826.aspx">sys.dm_exec_cached_plan_dependent_objects</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">Returns a row for each Transact-SQL execution plan, common
language runtime (CLR) execution plan, and cursor associated with a plan.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms181509.aspx">sys.dm_exec_connections</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">Returns information about the connections established to this
instance of SQL Server and the details of each connection.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms190346.aspx">sys.dm_exec_cursors</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">Returns information about the cursors that are open in various
databases.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ff878258.aspx">sys.dm_exec_describe_first_result_set</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">This dynamic management function takes a
Transact-SQL statement as a parameter and describes the metadata of
the first result set for the statement.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ff878236.aspx">sys.dm_exec_describe_first_result_set_for_object</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">This dynamic management function takes an @object_id as a
parameter and describes the first result metadata for the module with that
ID. The @object_id specified can be the ID of a Transact-SQL stored procedure
or a Transact-SQL trigger. If it is the ID of any other object (such as a
view, table, function, or CLR procedure), an error will be specified in the
error columns of the result.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms189472.aspx">sys.dm_exec_plan_attributes</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">Returns one row per plan attribute for the plan specified by
the plan handle. You can use this table-valued function to get details about
a particular plan, such as the cache key values or the number of current
simultaneous executions of the plan.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/cc280701.aspx">sys.dm_exec_procedure_stats</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">Returns aggregate performance statistics for cached stored
procedures. The view returns one row for each cached stored procedure plan,
and the lifetime of the row is as long as the stored procedure remains
cached. </td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms365393.aspx">sys.dm_exec_query_memory_grants</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">Returns information about the queries that have acquired a
memory grant or that still require a memory grant to execute. Queries that do
not have to wait on a memory grant will not appear in this view.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms175002.aspx">sys.dm_exec_query_optimizer_info</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">Returns detailed statistics about the operation of the SQL
Server query optimizer. You can use this view when tuning a workload to
identify query optimization problems or improvements.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms189747.aspx">sys.dm_exec_query_plan</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">Returns the Showplan in XML format for the batch specified by
the plan handle. The plan specified by the plan handle can either be cached
or currently executing.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/dn223301.aspx">sys.dm_exec_query_profiles</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">Monitors real time query progress while the query is in
execution. </td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms366321.aspx">sys.dm_exec_query_resource_semaphores</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">Returns the information about the current query-resource
semaphore status in SQL Server. <span class="font6">sys.dm_exec_query_resource_semaphores</span><span class="font5"> provides general query-execution memory status and allows
you to determine whether the system can access enough memory. </span></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms189741.aspx">sys.dm_exec_query_stats</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">Returns aggregate performance statistics for cached query
plans in SQL Server. The view contains one row per query statement within the
cached plan, and the lifetime of the rows are tied to the plan itself. When a
plan is removed from the cache, the corresponding rows are eliminated from
this view.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms177648.aspx">sys.dm_exec_requests</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">Returns information about each request that is executing
within SQL Server.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms176013.aspx">sys.dm_exec_sessions</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">Returns one row per authenticated session on SQL Server.
sys.dm_exec_sessions is a server-scope view that shows information about
all active user connections and internal tasks. </td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms181929.aspx">sys.dm_exec_sql_text</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">Returns the text of the SQL batch that is identified by the
specified <span class="font7">sql_handle</span><span class="font5">.
This table-valued function replaces the system function </span><span class="font6">fn_get_sql</span><span class="font5">.</span></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb326654.aspx">sys.dm_exec_text_query_plan</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">Returns the Showplan in text format for a Transact-SQL batch
or for a specific statement within the batch. The query plan specified by the
plan handle can either be cached or currently executing. </td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/cc280646.aspx">sys.dm_exec_trigger_stats</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">Returns aggregate performance statistics for cached triggers.
The view contains one row per trigger, and the lifetime of the row is as long
as the trigger remains cached. </td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl64" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms408299.aspx">sys.dm_exec_xml_handles</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65">Returns information about active handles that have been opened
by<b> sp_xml_preparedocument</b>.
<br />
<br /></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td height="20" style="height: 15.0pt; width: 258pt;" width="344"><b><u>Extended Events</u></b></td>
<td class="xl66" style="width: 439pt;" width="585"></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb630350.aspx">sys.dm_xe_map_values</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns a mapping of internal numeric keys to
human-readable text.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb630367.aspx">sys.dm_xe_object_columns</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns the schema information for all the
objects.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb677276.aspx">sys.dm_xe_objects</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns a row for each object that is exposed
by an event package.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb677239.aspx">sys.dm_xe_packages</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Lists all the packages registered with the
extended events engine.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb677187.aspx">sys.dm_xe_session_event_actions</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns information about event session
actions. Actions are executed when events are fired. This management view
aggregates statistics about the number of times an action has run, and the
total run time of the action.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb677260.aspx">sys.dm_xe_session_events</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns information about session events.
Events are discrete execution points. Predicates can be applied to events to
stop them from firing if the event does not contain the required information.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb630380.aspx">sys.dm_xe_session_object_columns</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Shows the configuration values for objects
that are bound to a session.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb677313.aspx">sys.dm_xe_session_targets</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns information about session targets.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb630378.aspx">sys.dm_xe_sessions</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns information about an active extended
events session. This session is a collection of events, actions, and targets.
<br />
<br /></td></tr>
<tr height="20" style="height: 15.0pt;">
<td height="20" style="height: 15.0pt; width: 258pt;" width="344"><b><u>Filestream and
FileTable</u></b></td>
<td class="xl67" style="width: 439pt;" width="585"></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ff773107.aspx">sys.dm_filestream_file_io_handles</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" style="width: 439pt;" width="585">Displays the currently open
transactional file handles.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ff773041.aspx">sys.dm_filestream_file_io_requests</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" style="width: 439pt;" width="585">Displays current file input and
file output requests.<br />
<br /></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td height="20" style="height: 15.0pt; width: 258pt;" width="344"><b><u>Full-Text and Semantic Search</u></b></td>
<td class="xl66" style="width: 439pt;" width="585"></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="38" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms177686.aspx">sys.dm_fts_active_catalogs</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" style="width: 439pt;" width="585">Returns information on the
full-text catalogs that have some population activity in progress on the
server.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="38" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/cc280764.aspx">sys.dm_fts_fdhosts</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" style="width: 439pt;" width="585">Returns information on the
current activity of the filter daemon host or hosts on the server instance.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="38" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/cc280607.aspx">sys.dm_fts_index_keywords_by_document</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" style="width: 439pt;" width="585">Returns information about the
document-level content of a full-text index for the specified table. A given
keyword can appear in several documents.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="57" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ee677646.aspx">sys.dm_fts_index_keywords_by_property</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" style="width: 439pt;" width="585">Returns all property-related
content in the full-text index of a given table. This includes all data that
belongs to any property registered by the search property list associated
with that full-text index.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/cc280900.aspx">sys.dm_fts_index_keywords</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" style="width: 439pt;" width="585">Returns information about the
content of a full-text index for the specified table.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms186897.aspx">sys.dm_fts_index_population</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" style="width: 439pt;" width="585">Returns information about the full-text index populations currently in progress.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="38" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms180044.aspx">sys.dm_fts_memory_buffers</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" style="width: 439pt;" width="585">Returns information about memory
buffers belonging to a specific memory pool that are used as part of a
full-text crawl or a full-text crawl range.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="38" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms176075.aspx">sys.dm_fts_memory_pools</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" style="width: 439pt;" width="585">Returns information about the
shared memory pools available to the Full-Text Gatherer component for a
full-text crawl or a full-text crawl range.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/cc280742.aspx">sys.dm_fts_outstanding_batches</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" style="width: 439pt;" width="585">Returns information about each
full-text indexing batch.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="57" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/cc280463.aspx">sys.dm_fts_parser</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" style="width: 439pt;" width="585">Returns the final tokenization
result after applying a given word breaker, thesaurus, and stoplist
combination to a query string input. The output is equivalent to the output
if the specified given query string were issued to the Full-Text Engine.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms179869.aspx">sys.dm_fts_population_ranges</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" style="width: 439pt;" width="585">Returns information about the
specific ranges related to a full-text index population currently in
progress.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/gg492070.aspx">sys.dm_fts_semantic_similarity_population</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66" style="width: 439pt;" width="585">Returns one row of status
information about the population of the document similarity index for each
similarity index in each table that has an associated semantic index.<br />
<br /></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td height="20" style="height: 15.0pt; width: 258pt;" width="344"><b><u>Index Related</u></b></td>
<td class="xl66" style="width: 439pt;" width="585"></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms174281.aspx">sys.dm_db_index_operational_stats</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl68" style="width: 439pt;" width="585">Returns current lowore-level
I/O, locking, latching, and access method activity for each partition of a
table or index in the database.<br />
Memory-optimized indexes do not appear in this DMV.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms188917.aspx">sys.dm_db_index_physical_stats</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td>Returns size and fragmentation information for the data and indexes of
the specified table or view in SQL Server. For an index, one row is returned
for each level of the B-tree in each partition. For a heap, one row is
returned for the IN_ROW_DATA allocation unit of each partition. For large
object (LOB) data, one row is returned for the LOB_DATA allocation unit of
each partition. If row-overflow data exists in the table, one row is returned
for the ROW_OVERFLOW_DATA allocation unit in each partition. Does not return
information about xVelocity memory optimized columnstore indexes.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms188755.aspx">sys.dm_db_index_usage_stats</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns counts of different types of index
operations and the time each type of operation was last performed in SQL
Server.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms345364.aspx">sys.dm_db_missing_index_columns</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns information about database table
columns that are missing an index, excluding spatial indexes. <span class="font6">sys.dm_db_missing_index_columns</span><span class="font5"> is
a dynamic management function.</span></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms345434.aspx">sys.dm_db_missing_index_details</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns detailed information about missing
indexes, excluding spatial indexes.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms345421.aspx">sys.dm_db_missing_index_group_stats</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns summary information about groups of
missing indexes, excluding spatial indexes.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms345407.aspx">sys.dm_db_missing_index_groups</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl67">Returns information about what missing
indexes are contained in a specific missing index group, excluding spatial
indexes.<br />
<br /></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td height="20" style="height: 15.0pt; width: 258pt;" width="344"><b><u>I/O Related</u></b></td>
<td style="width: 439pt;" width="585"></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/ms176071.aspx">sys.dm_io_backup_tapes</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns the list of tape devices and the
status of mount requests for backups.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/ms188930.aspx">sys.dm_io_cluster_shared_drives</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">This view returns the drive name of each of
the shared drives if the current server instance is a clustered server. If
the current server instance is not a clustered instance it returns an empty
rowset.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/dn509548.aspx">sys.dm_io_cluster_valid_path_names</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns information on all valid shared
disks, including clustered shared volumes, for a SQL Server failover cluster
instance. If the instance is not clustered, an empty rowset is returned.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/ms188762.aspx">sys.dm_io_pending_io_requests</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns a row for each pending I/O request in
SQL Server.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/ms190326.aspx">sys.dm_io_virtual_file_stats</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td>Returns
I/O statistics for data and log files. This dynamic management view replaces
the fn_virtualfilestats function.<br />
<br /></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td height="20" style="height: 15.0pt; width: 258pt;" width="344"><b><u>Memory-Optimized
Table</u></b></td>
<td style="width: 439pt;" width="585"></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/dn133201.aspx">sys.dm_db_xtp_checkpoint_files</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Displays information about checkpoint files,
including file size, physical location and the transaction ID.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/dn133197.aspx">sys.dm_db_xtp_checkpoint_stats</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns statistics about the In-Memory OLTP
checkpoint operations in the current database. If the database has no
In-Memory OLTP objects, returns an empty result set.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/dn268337.aspx">sys.dm_db_xtp_gc_cycle_stats</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Outputs the current state of committed
transactions that have deleted one or more rows. The idle garbage collection
thread wakes every minute or when the number of committed DML transactions
exceeds an internal threshold since the last garbage collection cycle.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/dn296679.aspx">sys.dm_db_xtp_hash_index_stats</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">These statistics are useful for understanding
and tuning the bucket counts. It can also be used to detect cases where the
index key has many duplicates.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/dn133081.aspx">sys.dm_db_xtp_index_stats</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Contains index statistics collected since the
last database restart.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/dn133206.aspx">sys.dm_db_xtp_memory_consumers</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Reports the database-level memory consumers
in the In-Memory OLTP database engine. The view returns a row for each memory
consumer that the database engine uses.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/dn465868.aspx">sys.dm_db_xtp_merge_requests</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td>Tracks
database merge requests. The merge request may have been generated by SQL
Server or the request could have been made by a user
withsys.sp_xtp_merge_checkpoint_files.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/dn645468.aspx">sys.dm_db_xtp_nonclustered_index_stats</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">This view includes statistics about
operations on nonclustered indexes in memory-optimized tables. It contains
one row for each nonclustered index on a memory-optimized table in the
current database.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/dn133191.aspx">sys.dm_db_xtp_object_stats</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Reports the number rows affected by
operations on each of the In-Memory OLTP objects since the last database
restart. Statistics are updated when the operation executes, regardless of
whether the transaction commits or was rolled back.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/dn169142.aspx">sys.dm_db_xtp_table_memory_stats</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns memory usage statistics for each
In-Memory OLTP table (user and system) in the current database. </td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/dn133194.aspx">sys.dm_db_xtp_transactions</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Reports the active transactions in the
In-Memory OLTP database engine.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/dn268336.aspx">sys.dm_xtp_gc_queue_stats</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Outputs information about each garbage
collection worker queue on the server, and various statistics about each.
There is one queue per logical CPU.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/dn133196.aspx">sys.dm_xtp_gc_stats</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Provides information (the overall statistics)
about the current behavior of the In-Memory OLTP garbage-collection process.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/dn133200.aspx">sys.dm_xtp_system_memory_consumers</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Reports system level memory consumers for
In-Memory OLTP. The memory for these consumers come either from the default
pool (when the allocation is in the context of a user thread) or from
internal pool (if the allocation is in the context of a system thread).</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/dn133198.aspx">sys.dm_xtp_transaction_stats</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Reports statistics about transactions that
have run since the server started.<br />
<br /></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td height="20" style="height: 15.0pt; width: 258pt;" width="344"><b><u>Object Related</u></b></td>
<td style="width: 439pt;" width="585"></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb677185.aspx">sys.dm_sql_referenced_entities</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns one row for each user-defined entity
referenced by name in the definition of the specified referencing entity in
SQL Server. <span class="font5">For example, if a stored procedure is the
specified referencing entity, this function returns all user-defined entities
that are referenced in the stored procedure such as tables, views,
user-defined types (UDTs), or other stored procedures.</span></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb630351.aspx">sys.dm_sql_referencing_entities</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns one row for each entity in the
current database that references another user-defined entity by name. <span class="font5">For example, if a user-defined type (UDT) is specified as the
referenced entity, this function returns each user-defined entity that
reference that type by name in its definition. </span></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/jj553546.aspx">sys.dm_db_stats_properties</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns properties of statistics for the
specified database object (table or indexed view) in the current SQL Server
database.<br />
<br /></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td height="20" style="height: 15.0pt; width: 258pt;" width="344"><b><u>Query Notifications</u></b></td>
<td style="width: 439pt;" width="585"></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt;"><a href="https://msdn.microsoft.com/en-us/library/ms187793.aspx">sys.dm_qn_subscriptions</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns information about the active query
notifications subscriptions in the server. You can use this view to check for
active subscriptions in the server or a specified database, or to check for a
specified server principal.<br />
<br /></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td height="20" style="height: 15.0pt; width: 258pt;" width="344"><b><u>Replication</u></b></td>
<td style="width: 439pt;" width="585"></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms186235.aspx">sys.dm_repl_articles</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns information about database objects
published as articles in a replication topology.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms186331.aspx">sys.dm_repl_schemas</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns information about table columns
published by replication.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms173459.aspx">sys.dm_repl_tranhash</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns information about transactions being
replicated in a transactional publication.</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15.0pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms180036.aspx">sys.dm_repl_traninfo</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns information on each replicated or
change data capture transaction.<br />
<br /></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td height="20" style="height: 15.75pt; width: 258pt;" width="344"><b><u>Resource Governor</u></b></td>
<td style="width: 439pt;" width="585"></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb934099.aspx">sys.dm_resource_governor_configuration</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns a row that contains the current in-memory
configuration state of Resource Governor.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb934023.aspx">sys.dm_resource_governor_resource_pools</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns information about the current resource pool state, the
current configuration of resource pools, and resource pool statistics.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/dn358348.aspx">sys.dm_resource_governor_resource_pool_volumes</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns information about the current resource pool IO
statistics for each disk volume. This information is also available at the
resource pool level in sys.dm_resource_governor_resource_pools.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb934197.aspx">sys.dm_resource_governor_workload_groups</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns workload group statistics and the current in-memory
configuration of the workload group. This view can be joined with
sys.dm_resource_governor_resource_pools to get the resource pool name.<br />
<br /></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td height="20" style="height: 15.75pt; width: 258pt;" width="344"><b><u>Security-Related</u></b></td>
<td style="width: 439pt;" width="585"></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/cc280725.aspx">sys.dm_audit_actions</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns a row for every audit action that can
be reported in the audit log and every audit action group that can be
configured as part of SQL Server Audit.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/cc280820.aspx">sys.dm_audit_class_type_map</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns a table that maps the class_type
field in the audit log to the class_desc field in sys.dm_audit_actions. </td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb677339.aspx">sys.dm_cryptographic_provider_algorithms</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns the algorithms supported by an
Extensible Key Management (EKM) provider.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb677275.aspx">sys.dm_cryptographic_provider_keys</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns information about the keys provided
by a Extensible Key Management (EKM) provider.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb677182.aspx">sys.dm_cryptographic_provider_properties</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns information about registered
cryptographic providers.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb630258.aspx">sys.dm_cryptographic_provider_sessions</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns information about open sessions for a
cryptographic provider.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb677274.aspx">sys.dm_database_encryption_keys</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns information about the encryption
state of a database and its associated database encryption keys.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/cc280524.aspx">sys.dm_server_audit_status</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns a row for each server audit
indicating the current state of the audit.<br />
<br /></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td height="20" style="height: 15.75pt; width: 258pt;" width="344"><b><u>Service Broker</u></b></td>
<td style="width: 439pt;" width="585"></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms175029.aspx">sys.dm_broker_activated_tasks</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns a row for each stored procedure
activated by Service Broker.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms189490.aspx">sys.dm_broker_connections</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns a row for each Service Broker network
connection.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms179920.aspx">sys.dm_broker_forwarded_messages</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns a row for each Service Broker message
that an instance of SQL Server is in the process of forwarding.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms177628.aspx">sys.dm_broker_queue_monitors</a></td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td class="xl66">Returns a row for each queue monitor in the
instance. A queue monitor manages activation for a queue.<br />
<br /></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td height="20" style="height: 15.75pt; width: 258pt;" width="344"><b><u>SQL Server
Operating System</u></b></td>
<td style="width: 439pt;" width="585"></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms173442.aspx">sys.dm_os_buffer_descriptors</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns information about all the data pages that are
currently in the SQL Server buffer pool. The output of this view can be used
to determine the distribution of database pages in the buffer pool according
to database, object, or type. In SQL Server 2014, this dynamic management
view also returns information about the data pages in the buffer pool
extension file.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/dn133204.aspx">sys.dm_os_buffer_pool_extension_configuration </a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns configuration information about the
buffer pool extension in SQL Server. Returns one row for each buffer pool
extension file.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms165698.aspx">sys.dm_os_child_instances</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">{DEPRECATED} Returns a row for each user
instance that has been created from the parent server instance.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms187341.aspx">sys.dm_os_cluster_nodes</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns one row for each node in the failover
cluster instance configuration. If the current instance is a failover
clustered instance, it returns a list of nodes on which this failover cluster
instance (formerly "virtual server") has been defined. If the
current server instance is not a failover clustered instance, it returns an
empty rowset.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/gg471591.aspx">sys.dm_os_cluster_properties</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns one row with the current settings for
the SQL Server cluster resource properties identified in this topic. No data
is returned if this view is run on a stand-alone instance of SQL Server.
These properties are used to set the values that affect failure detection,
failure response time, and the logging for monitoring the health status of
the SQL Server failover cluster instance.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb630336.aspx">sys.dm_os_dispatcher_pools</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns information about session dispatcher pools. Dispatcher
pools are thread pools used by system components to perform background
processing.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms187800.aspx">sys.dm_os_hosts</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns all the hosts currently registered in an instance of
SQL Server. This view also returns the resources that are used by these
hosts. </td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms175066.aspx">sys.dm_os_latch_stats</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns information about all latch waits
organized by class.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms179907.aspx">sys.dm_os_loaded_modules</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns a row for each module loaded into the
server address space.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb522548.aspx">sys.dm_os_memory_brokers</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Allocations that are internal to SQL Server
use the SQL Server memory manager. Tracking the difference between process
memory counters from <span class="font6">sys.dm_os_process_memory</span><span class="font5"> and internal counters can indicate memory use from
external components in the SQL Server memory space.</span></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms173786.aspx">sys.dm_os_memory_cache_clock_hands</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns the status of each hand for a
specific cache clock.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms188760.aspx">sys.dm_os_memory_cache_counters</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns a snapshot of the health of a cache
in SQL Server. <span class="font6">sys.dm_os_memory_cache_counters</span><span class="font5"> provides run-time information about the cache entries
allocated, their use, and the source of memory for the cache entries.</span></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms189488.aspx">sys.dm_os_memory_cache_entries</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns information about all entries in
caches in SQL Server. Use this view to trace cache entries to their
associated objects. You can also use this view to obtain statistics on cache
entries.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms182388.aspx">sys.dm_os_memory_cache_hash_tables</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns a row for each active cache in the
instance of SQL Server.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms175019.aspx">sys.dm_os_memory_clerks</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns the set of all memory clerks that are
currently active in the instance of SQL Server. Inside SQL Server, only
memory clerks have access to memory nodes. Memory clerks access memory node
interfaces to allocate memory. Memory nodes also track the memory allocated
by using the clerk for diagnostics. Every component that allocates a
significant amount of memory must create its own memory clerk and allocate
all its memory by using the clerk interfaces. Frequently, components create
their corresponding clerks at the time SQL Server is started.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb510622.aspx">sys.dm_os_memory_nodes</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl67">Allocations that are internal to SQL Server use the SQL Server
memory manager. Tracking the difference between process memory counters
from <span class="font6">sys.dm_os_process_memory</span><span class="font5"> and internal counters can indicate memory use from
external components in the SQL Server memory space. Nodes are created per
physical NUMA memory nodes. These might be different from the CPU nodes
in sys.dm_os_nodes. No allocations done directly through Windows memory
allocations routines are tracked. The following table provides information
about memory allocations done only by using SQL Server memory manager
interfaces.</span></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms179875.aspx">sys.dm_os_memory_objects</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns memory objects that are currently
allocated by SQL Server. You can use <span class="font6">sys.dm_os_memory_objects</span><span class="font5"> to analyze memory use and to identify possible memory
leaks.</span></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms175022.aspx">sys.dm_os_memory_pools</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns a row for each object store in the
instance of SQL Server. You can use this view to monitor cache memory use and
to identify bad caching behavior</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb510628.aspx">sys.dm_os_nodes</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">An internal component named the SQLOS creates
node structures that mimic hardware processor locality. These structures can
be changed by using soft-NUMA to create custom node layouts.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms187743.aspx">sys.dm_os_performance_counters</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns a row per performance counter
maintained by the server. </td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb510747.aspx">sys.dm_os_process_memory</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl68">This
view displays basic memory related information for the server. It provides
detail for physical memory, available memory , total page file and available
page file and high/low memory status. A low memory status indicates
memory pressure. </td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms177526.aspx">sys.dm_os_schedulers</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns one row per scheduler in SQL Server
where each scheduler is mapped to an individual processor. Use this view to
monitor the condition of a scheduler or to identify runaway tasks.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/gg471697.aspx">sys.dm_os_server_diagnostics_log_configurations</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns one row with the current
configuration for the SQL Server failover cluster diagnostic log. These
property settings determine whether the diagnostic logging is on or off, and
the location, number, and size of the log files.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms188008.aspx">sys.dm_os_stacks</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl68">This dynamic management view is used internally by SQL Server
to do the following: 1. Keep track of debug data such as outstanding
allocations; 2. Assume or validate logic that is used by SQL Server
components in places where the component assumes that a certain call has been
made.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms175048.aspx">sys.dm_os_sys_info</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns a miscellaneous set of useful
information about the computer, and about the resources available to and
consumed by SQL Server.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/bb510493.aspx">sys.dm_os_sys_memory</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns memory information from the operating
system.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms174963.aspx">sys.dm_os_tasks</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns one row for each task that is active
in the instance of SQL Server.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms187818.aspx">sys.dm_os_threads</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns a list of all SQL Server Operating
System threads that are running under the SQL Server process.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms186294.aspx">sys.dm_os_virtual_address_dump</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns information about a range of pages in
the virtual address space of the calling process.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/hh223223.aspx">sys.dm_os_volume_stats</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns information about the operating
system volume (directory) on which the specified databases and files are
stored in SQL Server. Use this dynamic management function to check the
attributes of the physical disk drive or return available free space information
about the directory.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms179984.aspx">sys.dm_os_wait_stats</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns information about all the waits
encountered by threads that executed. You can use this aggregated view to
diagnose performance issues with SQL Server and also with specific queries
and batches.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms188743.aspx">sys.dm_os_waiting_tasks</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns information about the wait queue of
tasks that are waiting on some resource.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/hh204565.aspx">sys.dm_os_windows_info</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns one row that displays Windows
operating system version information.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms178626.aspx">sys.dm_os_workers</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns a row for every worker in the system.<br />
<br /></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td height="20" style="height: 15.75pt; width: 258pt;" width="344"><b><u>Transaction
Related</u></b></td>
<td style="width: 439pt;" width="585"></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms180023.aspx">sys.dm_tran_active_snapshot_database_transactions</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td>Returns a virtual table for all active transactions that generate or
potentially access row versions. Transactions are included for one or more of
the following conditions:<br />
When either or both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT database
options are set to ON; When a trigger is fired, there is one row for the
transaction under which the trigger is executing; When an online indexing
procedure is running, there is one row for the transaction that is creating
the index; When Multiple Active Results Sets (MARS) session is enabled, there
is one row for each transaction that is accessing row versions.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms174302.aspx">sys.dm_tran_active_transactions</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns information about transactions for
the instance of SQL Server.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms184390.aspx">sys.dm_tran_current_snapshot</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns a virtual table that displays all
active transactions at the time when the current snapshot transaction starts.
If the current transaction is not a snapshot transaction, this function
returns no rows.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms186327.aspx">sys.dm_tran_current_transaction</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns a single row that displays the state
information of the transaction in the current session.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms186957.aspx">sys.dm_tran_database_transactions</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns information about transactions at the
database level.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms190345.aspx">sys.dm_tran_locks</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td>Returns information about currently active lock manager resources in SQL
Server 2014. Each row represents a currently active request to the lock
manager for a lock that has been granted or is waiting to be granted.<br />
The columns in the result set are divided into two main groups: resource
and request. The resource group describes the resource on which the lock
request is being made, and the request group describes the lock request.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms188739.aspx">sys.dm_tran_session_transactions</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns correlation information for
associated transactions and sessions.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms188778.aspx">sys.dm_tran_top_version_generators</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns a virtual table for the objects that
are producing the most versions in the version store. <span class="font6">sys.dm_tran_top_version_generators</span><span class="font5"> returns the top 256 aggregated record lengths that are
grouped by the </span><span class="font6">database_id</span><span class="font5"> and </span><span class="font6">rowset_id</span><span class="font5">. </span><span class="font6">sys.dm_tran_top_version_generators</span><span class="font5"> retrieves data by querying the </span><span class="font6">dm_tran_version_store</span><span class="font5"> virtual
table. </span><span class="font6">sys.dm_tran_top_version_generators</span><span class="font5"> is an inefficient view to run because this view queries
the version store, and the version store can be very large. We recommend that
you use this function to find the largest consumers of the version store.</span></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms173540.aspx">sys.dm_tran_transactions_snapshot</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td>Returns a virtual table for the sequence_number of transactions that are
active when each snapshot transaction starts. The information that is
returned by this view can you help you do the following:<br />
1. Find the number of currently active snapshot transactions; 2. Identify
data modifications that are ignored by a particular snapshot transaction. For
a transaction that is active when a snapshot transaction starts, all data
modifications by that transaction, even after that transaction commits, are
ignored by the snapshot transaction.</td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl65" height="20" style="border-top: none; height: 15.75pt; width: 258pt;" width="344"><a href="https://msdn.microsoft.com/en-us/library/ms186328.aspx">sys.dm_tran_version_store</a></td>
</tr>
<tr height="20" style="height: 15.75pt;">
<td class="xl66">Returns a virtual table that displays all version records in
the version store. sys.dm_tran_version_store is inefficient to run because it
queries the entire version store, and the version store can be very large.</td>
</tr>
</tbody></table>
Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com2tag:blogger.com,1999:blog-4616149892194368111.post-91952049916429381652015-02-12T04:20:00.001-08:002015-02-13T00:55:15.246-08:00Get table and row size dataIt often happens that you need to know how big a table is. There are many reasons for this: finding which tables have the most rows; understanding which tables tend to grow the fastest; find which table is using the most space.<br />
<br />
I found a nice <a href="http://dba.stackexchange.com/questions/55368/list-table-sizes-for-all-tables-on-all-databases" target="_blank">query</a> on <a href="http://stackexchange.com/">stackexchange.com</a> that listed the tables, row counts and data sizes in a database:<br /><br />
<a name='more'></a>
<span style="font-size: xx-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name;
]]>
</script>
</span>
<br />
I added a few things to give me a little more information, namely showing the total and used space in KB, MB and GB. In addition I added an average row size column to give me a quick view into which table(s) were the heaviest. If you use either of these queries on a system database you will need to comment out the WHERE clause.<br />
The first script below shows the total size of the table and all its indexes. The second script groups by index. The third script shows the table data only, i.e. either the size of the HEAP or Clustered Index. Finally, the fourth script handles partitioned tables (there is a slight discrepancy with row counts between this script and the other ones).<br /><br />
<span style="font-size: xx-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
--1. Total table size including all indexes
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CONVERT(DECIMAL(20,2),(SUM(a.total_pages) * 8.0)/1024) AS TotalSpaceMB,
CONVERT(DECIMAL(20,2),((SUM(a.total_pages) * 8.0)/1024)/1024) AS TotalSpaceGB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CONVERT(DECIMAL(20,2),(SUM(a.used_pages) * 8.0)/1024) AS UsedSpaceMB,
CONVERT(DECIMAL(20,2),((SUM(a.used_pages) * 8.0)/1024)/1024) AS UsedSpaceGB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CASE WHEN p.rows > 0 THEN CONVERT(DECIMAL(20,2),(SUM(a.used_pages) * 8.0)/p.rows)
ELSE 0 END as AvgRowSizeKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name;
--2. Table size grouped by index
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
i.index_id,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CONVERT(DECIMAL(20,2),(SUM(a.total_pages) * 8.0)/1024) AS TotalSpaceMB,
CONVERT(DECIMAL(20,2),((SUM(a.total_pages) * 8.0)/1024)/1024) AS TotalSpaceGB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CONVERT(DECIMAL(20,2),(SUM(a.used_pages) * 8.0)/1024) AS UsedSpaceMB,
CONVERT(DECIMAL(20,2),((SUM(a.used_pages) * 8.0)/1024)/1024) AS UsedSpaceGB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CASE WHEN p.rows > 0 THEN CONVERT(DECIMAL(20,2),(SUM(a.used_pages) * 8.0)/p.rows)
ELSE 0 END as AvgRowSizeKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows, i.index_id
ORDER BY
t.Name;
--3. Table size, data only
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CONVERT(DECIMAL(20,2),(SUM(a.total_pages) * 8.0)/1024) AS TotalSpaceMB,
CONVERT(DECIMAL(20,2),((SUM(a.total_pages) * 8.0)/1024)/1024) AS TotalSpaceGB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CONVERT(DECIMAL(20,2),(SUM(a.used_pages) * 8.0)/1024) AS UsedSpaceMB,
CONVERT(DECIMAL(20,2),((SUM(a.used_pages) * 8.0)/1024)/1024) AS UsedSpaceGB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CASE WHEN p.rows > 0 THEN CONVERT(DECIMAL(20,2),(SUM(a.used_pages) * 8.0)/p.rows)
ELSE 0 END as AvgRowSizeKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
AND i.index_id in (0,1)
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name;
--4. Handles partitioned tables, total size including indexes
;WITH rowcounts as (
select sum(rows) as rows,p.object_id as objectid,p.index_id, sum(a.total_pages) as total_pages,sum(a.used_pages) as used_pages,count(p.partition_id) as partitions
from sys.partitions as p
join sys.allocation_units as a on p.partition_id = a.container_id
join sys.indexes as i on i.object_id = p.object_id and i.index_id = p.index_id
group by p.object_id,p.index_id)
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
p.partitions as No_of_Partitions,
SUM(p.total_pages) * 8 AS TotalSpaceKB,
CONVERT(DECIMAL(20,2),(SUM(p.total_pages) * 8.0)/1024) AS TotalSpaceMB,
CONVERT(DECIMAL(20,2),((SUM(p.total_pages) * 8.0)/1024)/1024) AS TotalSpaceGB,
SUM(p.used_pages) * 8 AS UsedSpaceKB,
CONVERT(DECIMAL(20,2),(SUM(p.used_pages) * 8.0)/1024) AS UsedSpaceMB,
CONVERT(DECIMAL(20,2),((SUM(p.used_pages) * 8.0)/1024)/1024) AS UsedSpaceGB,
(SUM(p.total_pages) - SUM(p.used_pages)) * 8 AS UnusedSpaceKB,
CASE WHEN max(p.rows) > 0 THEN CONVERT(DECIMAL(20,2),(SUM(p.used_pages) * 8.0)/max(p.rows))
ELSE 0 END as AvgRowSizeKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
rowcounts p ON i.object_id = p.objectid AND i.index_id = p.index_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.rows, p.partitions
ORDER BY
t.Name;
]]>
</script>
</span>
<br />
I've added this script to my locally saved version of the <a href="http://bradmcgehee.com/2010/07/19/free-sql-server-dmv-starter-pack/" target="_blank">DMV Starter Pack</a><br />Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com1tag:blogger.com,1999:blog-4616149892194368111.post-1429483634807093882014-12-21T08:57:00.002-08:002016-08-01T03:40:01.944-07:00Emergency Access to SQL ServerThere are times when you need to gain access to SQL Server in an emergency. It doesn't happen very often, but it does happen. Actually, part of the problem is that it happens so rarely! We all get to grips with the various back-door commands required of us DBAs when we're training or taking a certification course. But after six months, even two or more years, it all gets a bit rusty.
<br />
<br />
Therefore, I decided to put together an emergency cheat-sheet. All of these processes are very well documented around the web. My purpose here is purely to list the necessary steps to get access to your server quickly. So I won't be explaining how things work. I will list different options of getting the various steps done.<br />
<br />
<a name='more'></a>If you'd like to get more detailed info on how all this works and what to do in some worst case scenarios see the following articles:<br />
<span style="font-size: x-small;">
<a href="https://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/" target="_blank">Troubleshooting a SQL Server instance that will not start - <i>Gail Shaw</i></a><br />
<a href="http://www.brentozar.com/archive/2011/08/dedicated-admin-connection-why-want-when-need-how-tell-whos-using/" target="_blank">The Dedicated Admin Connection: Why You Want It, When You Need It, and How To Tell Who’s Using It - <i>Kendra Little</i></a><br />
<a href="http://msdn.microsoft.com/en-us/library/dd207003.aspx" target="_blank">Rebuilding System Databases - <i>MSDN</i></a><br />
<a href="http://www.sqlmaestros.com/sql-server-trace-flag-3608-might-encounter/#comment-166" target="_blank">Interesting behaviour with trace flag 3608 - <i>Amit R S Bansal</i></a><br />
<a href="http://www.mssqltips.com/sqlservertip/2333/using-the-emergency-state-for-a-corrupt-sql-server-database/" target="_blank">Using the emergency state for a corrupt sql server database - <i>MSSQL Tips</i></a><br />
</span>
<br />
<h4>
DAC (Dedicated Administrator Connection)</h4>
<br />
The first scenario involves a SQL Server instance that is blocking new connections or is so maxed out it just won't accept any additional connections. It's time to use DAC.<br />
<br />
As general preparation for these scenarios, be sure to have remote DAC connections enabled on all SQL Server instances: <br />
<span style="font-size: xx-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO
]]>
</script>
</span>
<br />
<ul>
<li>Access using DAC (Dedicated Administrator Connection)</li>
<ul>
<li>Make sure SQL Browser is running</li>
<ul>
<li>Local connection</li>
<ul>
<li>cmd prompt: NET Start sqlbrowser</li>
<li>Powershell: <br />
<span style="font-size: xx-small;">
<script class="brush: ps" type="syntaxhighlighter">
<![CDATA[ Get-Service -Name sqlbrowser -ComputerName Hostname | Set-Service -Status Running
]]>
</script>
</span></li>
</ul>
<li>Remote connection</li>
<ul>
<li>cmd prompt: <br />
<span style="font-size: xx-small;">
<script class="brush: ps" type="syntaxhighlighter">
<![CDATA[
sc \\hostname start sqlbrowser
]]>
</script>
</span></li>
<li>Powershell: <br />
<span style="font-size: xx-small;">
<script class="brush: ps" type="syntaxhighlighter">
<![CDATA[
Invoke-Command -ComputerName HostName -Credential Domain\User -ScriptBlock {Get-Service -Name sqlbrowser -ComputerName Hostname | Set-Service -Status Running}
]]>
</script>
</span></li>
</ul>
</ul>
<li>Start sqlcmd via CMD prompt or sqlps (powershell)</li>
<ul>
<li>Default instance: <br />
<span style="font-size: xx-small;">
<script class="brush: ps" type="syntaxhighlighter">
<![CDATA[
sqlcmd -U DomainName\Username -P Pa$$w0rd -d master -A
]]>
</script>
</span></li>
<li>Named instance: <br />
<span style="font-size: xx-small;">
<script class="brush: ps" type="syntaxhighlighter">
<![CDATA[
sqlcmd -S Hostname\Instancename -U Domain\user -P Pa$$w0rd -d master -A
]]>
</script>
</span></li>
</ul>
<li>DAC via SSMS</li>
<ul>
<li>Open a new Database Query Connection</li>
<li>Prefix the sql server instance name with 'admin:' in the server name field</li>
</ul>
</ul>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiq-sYko8eTgBtqC5BqRYfFvLdGDdhGdFod1k4odSnGhr0fARc1e9121ISPpHOeGwgBcaiDFTyg8tx2lTEoVTDg8oyy8xBN3TKD7ZXCLM6aKAW0maoTcT68LWK7NQhZM4XpSPf8IUigs7-g/s1600/QueryConnection.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="241" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiq-sYko8eTgBtqC5BqRYfFvLdGDdhGdFod1k4odSnGhr0fARc1e9121ISPpHOeGwgBcaiDFTyg8tx2lTEoVTDg8oyy8xBN3TKD7ZXCLM6aKAW0maoTcT68LWK7NQhZM4XpSPf8IUigs7-g/s1600/QueryConnection.jpg" width="320"></a></div>
<br />
<h4>
Start SQL Server in master-only recovery mode</h4>
<br />
Once, in attempting to move tempdb files we ran into an issue where Windows denied access to the new location after we moved the files and attempted to restart SQL Server. Despite the entire Operations team's best efforts we couldn't get SQL Server access to the new directory. The only way round it was to fail back to the original configuration. And, the only way to do that would be to start SQL Server in master-only recovery mode, then run an alter database move file script. <br />
<br />
<ul>
<li>Start SQL Server from a CMD prompt:</li>
<ul>
<li>Default instance:<br />
<span style="font-size: xx-small;">
<script class="brush: ps" type="syntaxhighlighter">
<![CDATA[
NET START MSSQLSERVER /f /T3608
]]>
</script>
</span>
</li>
<li>Named instance:<br />
<span style="font-size: xx-small;">
<script class="brush: ps" type="syntaxhighlighter">
<![CDATA[
NET START MSSQL$InstanceName /f /T3608
]]>
</script>
</span>
</li>
</ul>
<li>Start SQL Server from sqlps (powershell):</li>
<ul>
<li>Default Instance<br />
<span style="font-size: xx-small;">
<script class="brush: ps" type="syntaxhighlighter">
<![CDATA[
Start-Service 'MSSQLSERVER' /f /T3608
]]>
</script>
</span></li>
<li>Named Instance<br />
<span style="font-size: xx-small;">
<script class="brush: ps" type="syntaxhighlighter">
<![CDATA[
Start-Service 'MSSQL$InstanceName' /f /T3608
]]>
</script>
</span></li>
</ul>
</ul>
<br />
<h4>
Fixing a corrupt or suspect database (only if no usable DB backups are available)</h4>
<br />
There may be occasions where a database is marked as Suspect or Corrupt. And in a worst case scenario, there will be no backups that can be used. For example, they may be too old or in an odd and evil alignment of the planets, the disks/tapes with the relevant backups are also damaged or inaccessible (no, this has never happened to me!). In such a case, you will need to perform the following steps and hope the database can be fixed and not too much data is lost. <br />
<span style="font-size: xx-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
--Set the database to Emergency mode
ALTER DATABASE [Database Name] SET EMERGENCY
GO
--Set the database to SINGLE USER mode
ALTER DATABASE [Database Name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
--Run the DBCC CHECKDB command to repair/rebuild the log file
DBCC CHECKDB ([Database Name], REPAIR_ALLOW_DATA_LOSS)
GO
--Set the database back to MULTI USER mode
ALTER DATABASE [Database Name] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
--Set the database to Normal/ONLINE mode i.e. take it out of Emergency mode if the corruption is fixed
ALTER DATABASE [Database Name] SET ONLINE
GO
]]>
</script>
</span>Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com0tag:blogger.com,1999:blog-4616149892194368111.post-57197319949934647562014-10-14T11:03:00.000-07:002014-10-23T01:21:38.603-07:00Automating Sliding Partition Windows - Part 4<h2>
<b><span style="font-size: large;">Recurring Tasks</span></b></h2>
In the <a href="http://myhumblesqltips.blogspot.co.uk/search/label/sliding%20partition%20window"><span style="color: blue;">previous posts</span></a> of this series I both
explained the concept of the sliding window partition as well as the way I have
configured my system for an automated sliding window. This post will delve into
the automated part of the process, the tasks that recur on a regular, in my
case monthly, basis.<br />
<br />
I have
decided to use an SSIS package to execute the various steps of the sliding
window, but it is just as valid to wrap the steps into cursors or loops
(obviously, you only need a loop if you have more than one table, partition
scheme and/or function in your database). Depending on how much you plan on
automating there are some challenges in using SSIS. I will be explaining how
I've constructed my SSIS package, but it's outside the scope of this post
series to explain how to use SSIS. There are many resources on the web that can
help you if needed.<br />
<br />
<a name='more'></a><h3>
<b>Switching Partitions</b></h3>
The first
step involved is switching partitions between tables. If you remember from my
previous posts I created a staging table for each partitioned table. So, at the
end of each month (my partitions are all based on dates and partitioned
monthly) partitions that are over a month old are switched to the staging
table(s). In my scenario, the data in the staging tables are then moved to an
archive database and the staging tables truncated.<br />
<br />
You may
also remember that I referred to a great query that provides me with all the
partition info in my database. A cut-down version of that will provide me with
the meta-data needed to perform the switch tasks. The query below finds all the
tables and related partition schemes that match my particular naming convention
(scheme names start with 'ps' function name start with ‘pf’ and the staging
table names end with '_stg_Archive'. This is all up to you, but it certainly
helps to have a set naming convention to stick to.<br />
<br />
<span style="font-size: xx-small;">
<script class="brush: sql" type="syntaxhighlighter">
<![CDATA[
SELECT MainPartitions.PartitionScheme
,MainPartitions.PartitionFunction
,MainPartitions.TableName as SourceTable
,StagingPartitions.TableName as DestinationTable
,MIN(MainPartitions.PartitionNumber) as MinPartitionNumber
,MAX(MainPartitions.PartitionNumber) as MaxPartitionNumber
,convert(CHAR(10),MIN(MainPartitions.BoundaryValue),102) as MinBoundaryValue
,convert(CHAR(10),MAX(StagingPartitions.BoundaryValue),102) as MaxBoundaryValue
FROM (SELECT DISTINCT N'RowsOfData' = p.[rows] , N'TableName' = o.name, N'PartitionFileGroup' = ds2.name, N'PartitionNumber' = dds.destination_id
, N'BoundaryValue' = CONVERT(datetime,prv.value), N'PartitionScheme' = ps.name, N'PartitionFunction' = pf.name, N'DataSpaceName' = ds.name
, N'DataSpaceType' = ds.type_desc, N'RightBoundary' = pf.boundary_value_on_right,N'DatabaseName' = DB_NAME()
FROM sys.objects AS o
INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.partitions AS p ON o.[object_id] = p.[object_id]
INNER JOIN sys.indexes AS i ON p.[object_id] = i.[object_id]
AND p.index_id = i.index_id
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
LEFT OUTER JOIN sys.partition_schemes AS ps ON ds.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id
AND p.partition_number = prv.boundary_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds ON ps.data_space_id = dds.partition_scheme_id
AND p.partition_number = dds.destination_id
LEFT OUTER JOIN sys.data_spaces AS ds2 ON dds.data_space_id = ds2.data_space_id
WHERE s.name = N'dbo' -- schema name
AND ps.name Like 'ps%' --'psInteractionsMonthlyDate%'
AND o.name NOT LIKE '%_stg_Archive'
AND i.[index_id] = 1) as MainPartitions
JOIN (SELECT DISTINCT N'RowsOfData' = p.[rows] , N'TableName' = o.name, N'PartitionFileGroup' = ds2.name, N'PartitionNumber' = dds.destination_id
, N'BoundaryValue' = CONVERT(datetime,prv.value), N'PartitionScheme' = ps.name, N'PartitionFunction' = pf.name, N'DataSpaceName' = ds.name
, N'DataSpaceType' = ds.type_desc, N'RightBoundary' = pf.boundary_value_on_right,N'DatabaseName' = DB_NAME()
FROM sys.objects AS o
INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.partitions AS p ON o.[object_id] = p.[object_id]
INNER JOIN sys.indexes AS i ON p.[object_id] = i.[object_id]
AND p.index_id = i.index_id
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
LEFT OUTER JOIN sys.partition_schemes AS ps ON ds.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id
AND p.partition_number = prv.boundary_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds ON ps.data_space_id = dds.partition_scheme_id
AND p.partition_number = dds.destination_id
LEFT OUTER JOIN sys.data_spaces AS ds2 ON dds.data_space_id = ds2.data_space_id
WHERE s.name = N'dbo'
AND ps.name Like 'ps%'
AND o.name LIKE '%_stg_Archive'
AND i.[index_id] = 1
) as StagingPartitions ON MainPartitions.TableName = REPLACE(StagingPartitions.TableName,'_stg_Archive','')
AND MainPartitions.PartitionNumber = StagingPartitions.PartitionNumber
GROUP BY MainPartitions.PartitionScheme,MainPartitions.PartitionFunction,MainPartitions.TableName, StagingPartitions.TableName;
]]>
</script>
</span>
In my
SSIS package, the above query results are stored in a system.object variable
(essentially a table variable). I then use a ForEach Loop container and loop
through the result set to dynamically create and execute the split partition
SQL statements.<br />
<br />
For the
sql statement I've defined the following variables that are populated from the
query above:<br />
<span style="font-size: xx-small;">
<script class="brush: sql" type="syntaxhighlighter"><![CDATA[
DECLARE @psname SYSNAME
DECLARE @pfName SYSNAME
DECLARE @sourceTable SYSNAME
DECLARE @destinationTable SYSNAME
DECLARE @MinPartNumber INT
DECLARE @MaxPartNumber INT
DECLARE @MinRange VARCHAR(11)
DECLARE @MaxRange VARCHAR(11)
DECLARE @SQL VARCHAR(MAX)
DECLARE @MinSwitchPartNumber INT
DECLARE @MinSwitchRange VARCHAR(11)
DECLARE @DBName SYSNAME
SET @psname = ?
SET @pfName = ?
SET @sourceTable = ?
SET @destinationTable = ?
SET @MinPartNumber = ?
SET @MaxPartNumber = ?
SET @MinRange = ?
SET @MaxRange = ?
SET @SwitchRangeAge = ?
SET @DBName = DB_NAME()
-- Find min/max partitions with rows >0
SELECT @MinSwitchPartNumber = MIN(partition_number),@MinSwitchRange = MIN(CONVERT(DATETIME,prv.value,102))
FROM sys.objects AS o
INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.partitions AS p ON o.[object_id] = p.[object_id]
INNER JOIN sys.indexes AS i ON p.[object_id] = i.[object_id]
AND p.index_id = i.index_id
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
LEFT OUTER JOIN sys.partition_schemes AS ps ON ds.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id
AND p.partition_number = prv.boundary_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds ON ps.data_space_id = dds.partition_scheme_id
AND p.partition_number = dds.destination_id
LEFT OUTER JOIN sys.data_spaces AS ds2 ON dds.data_space_id = ds2.data_space_id
WHERE o.name = @sourceTable
AND ps.name = @psname
AND i.index_id = 1
AND [rows] > 0
-- Switch min partition with rows >0 if range value < getdate by at least 1 month
IF (SELECT DATEDIFF(mm,@MinSwitchRange,GETDATE())) >=@SwitchRangeAge
BEGIN
SET @SQL = 'ALTER TABLE ' + @sourceTable + ' SWITCH PARTITION ' + CAST(@MinSwitchPartNumber AS VARCHAR(2)) + ' TO ' + @destinationTable + ' PARTITION ' + CAST(@MinSwitchPartNumber AS VARCHAR(2)) +';'
PRINT @SQL
EXEC (@SQL)
END
]]></script>
</span>
<br />
You'll notice that there a a few extra parameters defined, specifically
the @MinPartNumber and @MaxPartNumber. As it is not worth switching
partitions that are empty, the script is written to find the minimum partition
number that has more than 0 rows and switch that one to the staging table.
Currently, this procedure switches only one partition per table for each run.
It is good practice to leave at least one empty partition at the beginning of
your partition scheme due to some quirks in SQL Server - Kendra Little of Brent
Ozar Unlimited explains it well in her post <a href="http://www.brentozar.com/archive/2013/01/best-practices-table-partitioning-merging-boundary-points/"><span style="color: blue;">Best Practices + Table Partitioning: Merging
Boundary Points</span></a><br />
<br />
At this point, it will be a good idea to know what you want to do with the data
that has just been switched. The reason for this is that merging two partitions
that each have data in them is very time and resource intensive and will block
any queries attempting to access the tables using the partitions in question.<br />
<h3>
<b>Cleaning Up the Staging Tables</b></h3>
In the
process that I have been outlining here I switch the data into staging tables
and then transfer the data to an archive database. Once that has completed, I
truncate the staging tables. This ensures that the partition is empty and any
merge operation will be quick. If you are not archiving the data and do not
need it, then you can go straight to the truncate statement. Since I'm doing
this all in ans SSIS ForEach Loop container, my truncate looks like this:<br />
<br />
<span style="font-size: xx-small;">
<script class="brush: sql" type="syntaxhighlighter"><![CDATA[
DECLARE @sql VARCHAR(MAX)DECLARE @destinationTable varchar(255)DECLARE @RunTruncate int
SET @destinationTable = ?
SET @RunTruncate = ?
IF @destinationTable NOT LIKE '%stg_Archive'RETURN
IF @RunTruncate = 1
beginSELECT @sql = 'TRUNCATE TABLE dbo.' + @destinationTablePRINT @sqlEXEC (@sql)end
]]></script>
</span>
<br />
Because I am transferring data between servers, I put additional checks in to make sure
all the data has been moved. I will describe that in a later post.
<br />
<br />
One last
thing to understand about partition merging is the direction they are merged
in. Kendra Little explains it well in the link above. But, in short, when you
merge Partitions 1 and 2, SQL Server moves partition 2 into 1 - backwards,
essentially. This is important for two reasons: 1. you need to know which
partition needs to be empty; 2. the filegroup that remains used is the one
associated with the partition that is merged into, i.e. partition 1.
<br />
<br />
For this
reason I like to build my partition scheme using PRIMARY as the first
filegroup. So, as the sliding partition progresses, I can remove the custom filegroups
I create along the way and keep things tidy. I'll go into file and filegroup
removal in a later post.<br />
<h3>
<b>Splitting Partitions</b></h3>
The next step in the sliding window is the partition split. As a reminder, the sliding
partition allows you to create future partitions on the fly. The reason for
this is multi-fold, not least that there are scenarios where you would exceed
the number of allowed partitions, or, as in my case, space limitations and
performance.<br />
<br />
Similarly
to the first query in this post, the query below finds the partition schemes
and functions, along with their min and max partition numbers and values. The
results are then used by the subsequent query within a ForEach loop container
in my SSIS package. Once again, depending on how many partition functions
and/or schemes you have yo can either run this on its own or, if you don't want
to use SSIS, in a tsql loop.<br />
<br />
<span style="font-size: xx-small;">
<script class="brush: sql" type="syntaxhighlighter"><![CDATA[
--Partition Function and scheme related data
SELECT MainPartitions.PartitionScheme ,
MainPartitions.PartitionFunction ,
MIN(MainPartitions.PartitionNumber) AS MinPartitionNumber ,
MAX(MainPartitions.PartitionNumber) AS MaxPartitionNumber ,
CONVERT(CHAR(10), MIN(MainPartitions.BoundaryValue), 102) AS MinBoundaryValue ,
CONVERT(CHAR(10), MAX(StagingPartitions.BoundaryValue), 102) AS MaxBoundaryValue
FROM ( SELECT DISTINCT
N'RowsOfData' = p.[rows] ,
N'TableName' = o.name ,
N'PartitionFileGroup' = ds2.name ,
N'PartitionNumber' = dds.destination_id ,
N'BoundaryValue' = CONVERT(CHAR(10), prv.value, 102) ,
N'PartitionScheme' = ps.name ,
N'PartitionFunction' = pf.name ,
N'DataSpaceName' = ds.name ,
N'DataSpaceType' = ds.type_desc ,
N'RightBoundary' = pf.boundary_value_on_right ,
N'DatabaseName' = DB_NAME()
FROM sys.objects AS o
INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.partitions AS p ON o.[object_id] = p.[object_id]
INNER JOIN sys.indexes AS i ON p.[object_id] = i.[object_id]
AND p.index_id = i.index_id
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
LEFT OUTER JOIN sys.partition_schemes AS ps ON ds.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id
AND p.partition_number = prv.boundary_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds ON ps.data_space_id = dds.partition_scheme_id
AND p.partition_number = dds.destination_id
LEFT OUTER JOIN sys.data_spaces AS ds2 ON dds.data_space_id = ds2.data_space_id
WHERE s.name = N'dbo'
AND ps.name LIKE 'ps%'
AND o.name NOT LIKE '%_stg_Archive'
AND i.[index_id] = 1
) AS MainPartitions
JOIN ( SELECT DISTINCT
N'RowsOfData' = p.[rows] ,
N'TableName' = o.name ,
N'PartitionFileGroup' = ds2.name ,
N'PartitionNumber' = dds.destination_id ,
N'BoundaryValue' = CONVERT(CHAR(10), prv.value, 102) ,
N'PartitionScheme' = ps.name ,
N'PartitionFunction' = pf.name ,
N'DataSpaceName' = ds.name ,
N'DataSpaceType' = ds.type_desc ,
N'RightBoundary' = pf.boundary_value_on_right ,
N'DatabaseName' = DB_NAME()
FROM sys.objects AS o
INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.partitions AS p ON o.[object_id] = p.[object_id]
INNER JOIN sys.indexes AS i ON p.[object_id] = i.[object_id]
AND p.index_id = i.index_id
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
LEFT OUTER JOIN sys.partition_schemes AS ps ON ds.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id
AND p.partition_number = prv.boundary_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds ON ps.data_space_id = dds.partition_scheme_id
AND p.partition_number = dds.destination_id
LEFT OUTER JOIN sys.data_spaces AS ds2 ON dds.data_space_id = ds2.data_space_id
WHERE s.name = N'dbo'
AND ps.name LIKE 'ps%'
AND o.name LIKE '%_stg_Archive'
AND i.[index_id] = 1
) AS StagingPartitions ON MainPartitions.TableName = REPLACE(StagingPartitions.TableName,
'_stg_Archive',
'')
AND MainPartitions.PartitionNumber = StagingPartitions.PartitionNumber
GROUP BY MainPartitions.PartitionScheme ,
MainPartitions.PartitionFunction;
]]></script>
</span>
<br />
Once
again, in the SSIS package, the above script's results are
stored in system.object variable which is then used in a ForEach Loop container
with the query below.<br />
There are
a couple of things that are required for splitting a partition:
<br />
<ul>
<li>Setting the Next Used
filegroup for the partition scheme</li>
<li>Making sure that filegroup exists</li>
</ul>
<br />
The above is listed in reverse execution order, as SQL Server won't allow you to set a
filegroup as the next used filegroup if it doesn't exist. Now, if you've
decided that your partitions will use the PRIMARY filegroup, you only need to
set the next used to PRIMARY, and you can remove the 'Add filegroup' section of
the query below. Otherwise, the query will determine what the next filegroup
should be for each partition scheme by referencing the filegroup mapping table
(see <a href="http://myhumblesqltips.blogspot.co.uk/2013/10/automating-sliding-partition-windows.html"><span style="color: blue;">previous post</span></a>) and then check for its existence.
It will then add it to the database if needed.<br />
<span style="font-size: xx-small;">
<script class="brush: sql" type="syntaxhighlighter"><![CDATA[
DECLARE @psname SYSNAME
DECLARE @pfName SYSNAME
DECLARE @sourceTable SYSNAME
DECLARE @destinationTable SYSNAME
DECLARE @MinPartNumber INT
DECLARE @MaxPartNumber INT
DECLARE @MinRange VARCHAR(11)
DECLARE @MaxRange VARCHAR(11)
DECLARE @SQL VARCHAR(MAX)
DECLARE @MinSwitchPartNumber INT
DECLARE @MinSwitchRange VARCHAR(11)
DECLARE @AddNewFileGroup BIT
DECLARE @DBName SYSNAME
DECLARE @NextUsedFG SYSNAME
DECLARE @SplitRange VARCHAR(11)
DECLARE @FilePath VARCHAR(255)
SET @psname = ?
SET @pfName = ?
SET @MinPartNumber = ?
SET @MaxPartNumber = ?
SET @MinRange = ?
SET @MaxRange = ?
SET @AddNewFileGroup = ?
SET @DBName = DB_NAME()
-- 1. Define Next Used FileGroup
SET @NextUsedFG = (SELECT TOP 1 FileGroupname
FROM HTK_Admin.dbo.atblFGPSMapping
WHERE CONVERT(DATE,PSvalue,102) > CONVERT(DATE,@MaxRange,102)
AND PartitionSchemename = @psname
AND FileGroupname NOT IN
(SELECT DISTINCT ds2.name --,prv.value
FROM sys.objects AS o
INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.partitions AS p ON o.[object_id] = p.[object_id]
INNER JOIN sys.indexes AS i ON p.[object_id] = i.[object_id]
AND p.index_id = i.index_id
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
LEFT OUTER JOIN sys.partition_schemes AS ps ON ds.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id
AND p.partition_number = prv.boundary_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds ON ps.data_space_id = dds.partition_scheme_id
AND p.partition_number = dds.destination_id
LEFT OUTER JOIN sys.data_spaces AS ds2 ON dds.data_space_id = ds2.data_space_id
WHERE ps.name = @psname
AND i.[index_id] = 1)
ORDER BY PSvalue
)
SET @SplitRange = (SELECT TOP 1 PSvalue FROM HTK_Admin.dbo.atblFGPSMapping WHERE CAST(PSvalue AS DATETIME) > CAST(@MaxRange AS DATETIME) ORDER BY CAST(PSvalue AS DATETIME))
--2. Find filepath
SELECT DISTINCT
@FilePath = REVERSE(SUBSTRING(REVERSE(physical_name),CHARINDEX('\', REVERSE(physical_name), 1) + 1,LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name),1)))
FROM sys.objects AS o
INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.partitions AS p ON o.[object_id] = p.[object_id]
INNER JOIN sys.indexes AS i ON p.[object_id] = i.[object_id]
AND p.index_id = i.index_id
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
LEFT OUTER JOIN sys.partition_schemes AS ps ON ds.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id
AND p.partition_number = prv.boundary_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds ON ps.data_space_id = dds.partition_scheme_id
AND p.partition_number = dds.destination_id
LEFT OUTER JOIN sys.data_spaces AS ds2 ON dds.data_space_id = ds2.data_space_id
FULL OUTER JOIN ( SELECT DB_NAME() AS dbname ,
database_files.name AS [FileName] ,
database_files.physical_name ,
database_files.data_space_id AS dbfileid ,
filegroups.name AS filegroupname
FROM sys.database_files --use sys.master_files if the database is read only and you want to see the metadata that is the database
JOIN sys.filegroups ON database_files.data_space_id = filegroups.data_space_id
JOIN sys.dm_io_virtual_file_stats(DB_ID(),
DEFAULT) DIVFS ON database_files.file_id = DIVFS.file_id
) AS files ON files.filegroupname = ds2.name
WHERE ps.name = @psname
AND CONVERT(DATE,prv.value,102) = CONVERT(DATE,@MaxRange,102)
AND i.[index_id] = 1
--3. Check for next used and existence of filegroup
IF DATEDIFF(mm,GETDATE(),@MaxRange) < 2
BEGIN
IF NOT EXISTS (
SELECT FileGroupName, Destination_ID, Data_Space_ID, psName,dest_rank FROM
(
SELECT FG.Name AS FileGroupName
, dds.destination_id
, dds.data_space_id
, prv.value
, ps.Name AS psName
, RANK() OVER (PARTITION BY ps.name ORDER BY dds.destination_Id) AS dest_rank
FROM sys.partition_schemes PS
INNER JOIN sys.destination_data_spaces AS DDS
ON DDS.partition_scheme_id = PS.data_space_id
INNER JOIN sys.filegroups AS FG
ON FG.data_space_id = DDS.data_space_ID
LEFT JOIN sys.partition_range_values AS PRV
ON PRV.Boundary_ID = DDS.destination_id AND prv.function_id=ps.function_id
WHERE prv.Value IS NULL
AND ps.name = @psname
) AS a
WHERE dest_rank > 1
)
BEGIN
IF NOT EXISTS (SELECT 1 FROM sys.filegroups WHERE name = @NextUsedFG)
AND @AddNewFileGroup = 1
AND @NextUsedFG IS NOT NULL
BEGIN
SET @SQL = 'ALTER DATABASE ' + @DBName + ' ADD FILEGROUP [' + @NextUsedFG + '];'
PRINT @SQL
EXEC (@SQL)
SET @SQL = 'ALTER DATABASE ' + @DBName + ' ADD FILE ( NAME = ' + @NextUsedFG + ', FILENAME = '''
+ @FilePath + '\' + @DBName + '_' + @NextUsedFG
+ '.ndf'', SIZE = 2048MB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024MB) TO FILEGROUP ' + @NextUsedFG + ';'
EXEC (@SQL)
PRINT @SQL
END
SET @SQL = 'ALTER PARTITION SCHEME ' + @psname + ' NEXT USED [' + @NextUsedFG + ']'
PRINT @SQL
EXEC (@SQL)
SET @SQL = 'ALTER PARTITION FUNCTION ' + @pfName + '() SPLIT RANGE (''' + @SplitRange + ''');'
PRINT @SQL
EXEC (@SQL)
END
END
]]></script>
</span>
<br />
<h3>
<b>Merging Partitions</b></h3>
Keeping in mind the idea of leaving at least one empty partition in
place, the script below, that identifies the partitions to merge, will choose
lowest partition number with more than zero rows but is also not the absolute minimum
partition number. (If you've skipped to this section you may want to read the
last two paragraphs of the switching partitions section above)<br />
<br />
For example, if the database partitions look like:<br />
The query below will select partition 3 as the partition to merge. So
partition 3 will be merged into partition 2 and we will be left with four
partitions - renumbered as 1 - 4. You will notice that I have included a loop
into the query as a safe-guard and also in order to allow introducing this
process into an already existing partitioning scheme.<br />
<br />
For example, if the database partitions look like:<br />
<br />
Partition 1 0 rows
<br />
Partition 2 0 rows
<br />
Partition 3 1000000 rows
<br />
Partition 4 1000000 rows
<br />
Partition 5 2000000 rows
<br />
<br />
The query below will select partition 3 to merge. So, partition 3 will
be merged into partition 2 and we will be left with 4 partitions - renumbered
as 1 - 4.You will notice that I have included a loop into the query as a
safe-guard and also in order to allow introducing this process into an already
existing partitioning scheme.<br />
<br />
<span style="font-size: xx-small;">
<script class="brush: sql" type="syntaxhighlighter"><![CDATA[
DECLARE @psname SYSNAME
DECLARE DECLARE @pfName SYSNAME
DECLARE DECLARE @MinPartNumber INT
DECLARE DECLARE @MaxPartNumber INT
DECLARE DECLARE @MinRange VARCHAR(11)
DECLARE @MaxRange VARCHAR(11)
DECLARE @MinSwitchPartNumber INT
DECLARE DECLARE @MinSwitchRange VARCHAR(11)
DECLARE @MergeRange VARCHAR(11)
DECLARE @SQL VARCHAR(MAX)
DECLARE @DBName SYSNAME
SET @psname = ?
SET @pfName = ?
SET @MinPartNumber = ?
SET @MaxPartNumber = ?
SET @MinRange = ?
SET @MaxRange = ?
SELECT @MinSwitchPartNumber = MIN(partition_number),@MinSwitchRange = MIN(CONVERT(DATETIME,prv.value,102))
FROM sys.objects AS o
INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.partitions AS p ON o.[object_id] = p.[object_id]
INNER JOIN sys.indexes AS i ON p.[object_id] = i.[object_id]
AND p.index_id = i.index_id
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
LEFT OUTER JOIN sys.partition_schemes AS ps ON ds.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id
AND p.partition_number = prv.boundary_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds ON ps.data_space_id = dds.partition_scheme_id
AND p.partition_number = dds.destination_id
LEFT OUTER JOIN sys.data_spaces AS ds2 ON dds.data_space_id = ds2.data_space_id
WHERE ps.name = @psname
AND i.index_id = 1
AND [rows] > 0
--1. Merge min partition if more than 1 partition with 0 rowsWHILE(SELECT COUNT(DISTINCT partition_number)
FROM sys.objects AS o
INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.partitions AS p ON o.[object_id] = p.[object_id]
INNER JOIN sys.indexes AS i ON p.[object_id] = i.[object_id]
AND p.index_id = i.index_id
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
LEFT OUTER JOIN sys.partition_schemes AS ps ON ds.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id
AND p.partition_number = prv.boundary_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds ON ps.data_space_id = dds.partition_scheme_id
AND p.partition_number = dds.destination_id
LEFT OUTER JOIN sys.data_spaces AS ds2 ON dds.data_space_id = ds2.data_space_id
WHERE ps.name = @psname
AND i.index_id = 1
AND [rows] = 0
AND partition_number < @MinSwitchPartNumber
) > 1
BEGIN SELECT @MergeRange = CONVERT( VARCHAR(11),prv.VALUE,102)
FROM sys.objects AS o
INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.partitions AS p ON o.[object_id] = p.[object_id]
INNER JOIN sys.indexes AS i ON p.[object_id] = i.[object_id]
AND p.index_id = i.index_id
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
LEFT OUTER JOIN sys.partition_schemes AS ps ON ds.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id
AND p.partition_number = prv.boundary_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds ON ps.data_space_id = dds.partition_scheme_id
AND p.partition_number = dds.destination_id
LEFT OUTER JOIN sys.data_spaces AS ds2 ON dds.data_space_id = ds2.data_space_id
WHERE ps.name = @psname
AND p.partition_number = @MinPartNumber + 1
SET @SQL = 'ALTER PARTITION FUNCTION ' + @pfName + '() MERGE RANGE (''' + @MergeRange + ''');'
PRINT @SQL
EXEC (@SQL)
SELECT @MinPartNumber = MIN(MainPartitions.PartitionNumber)
, ,@MinRange = convert(CHAR(10),MIN(MainPartitions.BoundaryValue),102)
FROM (SELECT DISTINCT N'RowsOfData' = SUM(p.[rows]) , N'PartitionFileGroup' = ds2.name, N'PartitionNumber' = dds.destination_id
, N'BoundaryValue' = CONVERT(datetime,prv.value), N'PartitionScheme' = ps.name, N'PartitionFunction' = pf.name
FROM sys.objects AS o
INNER JOIN sys.schemas AS s ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.partitions AS p ON o.[object_id] = p.[object_id]
INNER JOIN sys.indexes AS i ON p.[object_id] = i.[object_id]
AND p.index_id = i.index_id
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
LEFT OUTER JOIN sys.partition_schemes AS ps ON ds.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id
AND p.partition_number = prv.boundary_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds ON ps.data_space_id = dds.partition_scheme_id
AND p.partition_number = dds.destination_id
LEFT OUTER JOIN sys.data_spaces AS ds2 ON dds.data_space_id = ds2.data_space_id
WHERE pf.name = @pfName
AND CONVERT(DATE,prv.value,102) < CONVERT(DATE,GETDATE(),102)
GROUP BY ds2.name, dds.destination_id,CONVERT(datetime,prv.value), ps.name, pf.name
HAVING SUM(p.[rows]) = 0
) as MainPartitions
GROUP BY MainPartitions.PartitionScheme,MainPartitions.PartitionFunction;
SET @MinSwitchPartNumber = @MinSwitchPartNumber - 1
SET @MinSwitchRange = DATEADD(mm,-1,@MinSwitchRange)
END;
]]></script>
</span>
<br />
And with that, we have a basic automatic sliding partition window. Set
up as a monthly job either as a stored procedure or using an SSIS package, your
partitions will be maintained almost on their own. Obviously, testing needs to
be performed before deploying to a production environment and I present all
these queries "as is" with no warranty.<br />
<br />
I thought I would also make a sample SSIS package available on my Google
Drive (<a href="https://drive.google.com/file/d/0B-znT8ogfZ3La1EyUkZGRFIwVUk/view?usp=sharing"><span style="color: blue;">Sliding Partition Window.dtsx</span></a>). It is a cut down
version of the one I'm using. It performs only the tasks that I have outlined
in this post.<br />
<br />
Future posts will cover additional tasks that I require including
transferring data to an archive database and removing empty files and
filegroups.Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com1tag:blogger.com,1999:blog-4616149892194368111.post-90772859015860801382014-05-13T04:11:00.002-07:002014-10-17T01:49:47.522-07:00Automating Sliding Partition Windows - Part 3It's been a long while since I've worked on this - and believe me I want to as every month I'm spending too much time manually sliding my various partitions! Which is very prone to mistakes.<br />
<br />
In the meantime I found a really good post describing an automated sliding partition window that achieves something a little different. It works on the following premise:<br />
<br />
<ul>
<li>7 day partitions that, at the end of each week, are merged into</li>
<li>5 weekly partitions that are then merged into </li>
<li>6 monthly partitions that are finally merged into </li>
<li>1 large archive partition</li>
</ul>
<br />
I'm looking at implementing something like this in my company's OLAP database and combined with SQL Server 2012's columnstore indexes I am hoping to get some great performance improvement!<br />
<br />
Here's the post:<br />
<a href="http://blogs.msdn.com/b/menzos/archive/2008/06/30/table-partitioning-sliding-window-case.aspx" target="_blank">http://blogs.msdn.com/b/menzos/archive/2008/06/30/table-partitioning-sliding-window-case.aspx</a><br />
<br />
<br />Thomas Muchahttp://www.blogger.com/profile/06864799585515493782noreply@blogger.com0