Just thought I’d post up some useful SQL queries that I use regularly in our environments!
Web Service Authorization Cache
–Refresh Authorization Cache
Use Orchestrator
Truncate table [Microsoft.SystemCenter.Orchestrator.Internal].AuthorizationCache
DECLARE @secToken INT
DECLARE tokenCursor CURSOR FOR
SELECT
Id
FROM
[Microsoft.SystemCenter.Orchestrator.Internal].SecurityTokens
OPEN tokenCursor
FETCH NEXT FROM tokenCursor
INTO @secToken
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ‘Computing Authorization Cache for Security Token: ‘ + Convert(Nvarchar, @secToken)
exec [Microsoft.SystemCenter.Orchestrator].ComputeAuthorizationCache @TokenId = @secToken
FETCH NEXT FROM tokenCursor
INTO @secToken
END
CLOSE tokenCursor
DEALLOCATE tokenCursor
Environment Health Checks
–Large Table Query
Use Orchestrator
SELECT TOP 1000
a2.name AS [tablename], (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
a1.rows as row_count, a1.data * 8 AS data,
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) – a1.data ELSE 0 END) * 8 AS index_size,
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) – a1.used ELSE 0 END) * 8 AS unused,
(row_number() over(order by (a1.reserved + ISNULL(a4.reserved,0)) desc))%2 as l1,
a3.name AS [schemaname]
FROM (SELECT ps.object_id, SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN (SELECT it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N’S’ and a2.type <> N’IT’
–DB File Usage and Size
Use Orchestrator
select a.FILEID,
[FILE_SIZE_MB]=convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB]=convert(decimal(12,2),round(fileproperty(a.name,’SpaceUsed’)/128.000,2)),
[FREE_SPACE_MB]=convert(decimal(12,2),round((a.size-fileproperty(a.name,’SpaceUsed’))/128.000,2)) ,
[GROWTH_MB]=convert(decimal(12,2),round(a.growth/128.000,2)),
NAME=left(a.NAME,15),
FILENAME=left(a.FILENAME,60)
from dbo.sysfiles a
–Check Frag Levels
Use Orchestrator
DECLARE @DBName NVARCHAR(128) = ” — Name of the db, empty = current catalog
DECLARE @ReorgLimit TINYINT = 15 — Minimum fragmentation % to recommend reorg
DECLARE @RebuildLimit TINYINT = 30 — Minimum fragmentation % to recommend rebuild
DECLARE @PageLimit SMALLINT = 10 — Minimum # of Pages before you worry about the index
DECLARE @ShowAllIndexes BIT = 0 — 0 = Only show reorg/rebuild recommended, 1 = Show All
SET NOCOUNT ON ;
SET DEADLOCK_PRIORITY LOW ;
BEGIN TRY
DECLARE @FullName NVARCHAR(400), @SQL NVARCHAR(1000), @Rebuild NVARCHAR(1000), @DBID SMALLINT ;
DECLARE @Error INT, @TableName NVARCHAR(128), @SchemaName NVARCHAR(128), @HasLobs TINYINT ;
DECLARE @object_id INT, @index_id INT, @partition_number INT, @AvgFragPercent TINYINT ;
DECLARE @IndexName NVARCHAR(128), @Partitions INT, @Print NVARCHAR(1000) ;
DECLARE @PartSQL NVARCHAR(600), @ReOrgFlag TINYINT, @IndexTypeDesc NVARCHAR(60) ;
— Get the ID of the Database Catalog
IF @DBName = ” SET @DBName = DB_NAME();
SET @DBID = DB_ID(@DBName) ;
IF OBJECT_ID(‘tempdb..#FragLevels’) IS NOT NULL DROP TABLE #FragLevels
— Create a temporary table to store results
CREATE TABLE #FragLevels (
[SchemaName] NVARCHAR(128) NULL, [TableName] NVARCHAR(128) NULL, [HasLOBs] TINYINT NULL,
[ObjectID] [int] NOT NULL, [IndexID] [int] NOT NULL, [PartitionNumber] [int] NOT NULL,
[AvgFragPercent] [tinyint] NOT NULL, [IndexName] NVARCHAR(128) NULL, [IndexTypeDesc] NVARCHAR(60) NOT NULL ) ;
— Get the initial list of indexes and partitions to work on filtering out heaps and meeting the specified thresholds
INSERT INTO #FragLevels
([ObjectID], [IndexID], [PartitionNumber], [AvgFragPercent], [IndexTypeDesc])
SELECT a.[object_id], a.[index_id], a.[partition_number], CAST(a.[avg_fragmentation_in_percent] AS TINYINT) AS [AvgFragPercent], a.[index_type_desc]
FROM sys.dm_db_index_physical_stats(@DBID, NULL, NULL, NULL , ‘LIMITED’) AS a
WHERE
((@ShowAllIndexes = 0 AND a.[avg_fragmentation_in_percent] >= @ReorgLimit) OR (@ShowAllIndexes <> 0)) AND
a.[page_count] >= @PageLimit AND
a.[index_id] > 0
— Create an index to make some of the updates & lookups faster
CREATE INDEX [IX_#FragLevels_OBJECTID] ON #FragLevels([ObjectID]) ;
— Get the Schema and Table names for each
UPDATE #FragLevels WITH (TABLOCK)
SET [SchemaName] = OBJECT_SCHEMA_NAME([ObjectID],@DBID),
[TableName] = OBJECT_NAME([ObjectID],@DBID) ;
— Determine if the index has a Large Object (LOB) datatype.
— LOBs prevent reindexing and rebuilding index online
SET @SQL = N’UPDATE #FragLevels WITH (TABLOCK) SET [HasLOBs] = (SELECT TOP 1 CASE WHEN t.[lob_data_space_id] = 0 THEN 0 ELSE 1 END ‘ +
N’ FROM [' + @DBName + N'].[sys].[tables] AS t WHERE t.[type] = ”U” AND t.[object_id] = #FragLevels.[ObjectID])’ ;
EXEC(@SQL) ;
– Get the index name
SET @SQL = N’UPDATE #FragLevels SET [IndexName] = (SELECT TOP 1 t.[name] FROM [' + @DBName + N'].[sys].[indexes] AS t WHERE t.[object_id] = #FragLevels.[ObjectID] ‘ +
‘ AND t.[index_id] = #FragLevels.[IndexID] )’ ;
EXEC(@SQL) ;
— Return the results
SELECT
F.SchemaName AS [Schema Name],
F.TableName AS [Table Name],
F.IndexName AS [Index Name],
F.IndexTypeDesc AS [Index Type],
F.AvgFragPercent AS [Avg Frag (%)],
CASE
WHEN F.AvgFragPercent >= @RebuildLimit THEN ‘Yes’
ELSE ‘No’
END AS [Should Rebuild],
CASE
WHEN F.AvgFragPercent >= @ReorgLimit AND F.AvgFragPercent < @RebuildLimit THEN ‘Yes’
ELSE ‘No’
END AS [Should Reorg],
CASE
WHEN F.HasLOBs = 1 THEN ‘Yes’
ELSE ‘No’
END AS [Has LOBs],
F.ObjectID AS [Object ID],
F.IndexID AS [Index ID],
F.PartitionNumber AS [Partition Number]
FROM #FragLevels AS F
ORDER BY AvgFragPercent DESC
IF OBJECT_ID(‘tempdb..#FragLevels’) IS NOT NULL DROP TABLE #FragLevels
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH ;
Environment Maintenance Tasks
–Re Index Tables
USE Orchestrator
go
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
EXEC SP_MSForEachTable “Print ‘Reindexing ‘+’?’ DBCC DBREINDEX (‘?’)”
–Update Statistics
USE Orchestrator
EXEC sp_MSforeachtable ‘UPDATE STATISTICS ? WITH FULLSCAN’
go
–Manually clear event logs
–Ensure that Management Services and Runbook Services are all stopped and the environment is not active before running
USE Orchestrator
DELETE FROM POLICYINSTANCES
TRUNCATE TABLE OBJECTINSTANCEDATA
TRUNCATE TABLE OBJECTINSTANCES
TRUNCATE TABLE EVENTS
–Clear Orphaned Runbook Instances
Use Orchestrator
EXEC [Microsoft.SystemCenter.Orchestrator.Runtime.Internal].[ClearOrphanedRunbookInstances]
Runbook Checks
–Find Non-Pipeline (IE Legacy) Runbooks
select * from POLICIES where RunInPipelineMode = 0
–Find all Runbooks with Parallel Processing Enabled
select * from POLICIES where MaxParallelRequests > 1
–Find all Runbooks with Object Specific Logging Enabled
select * from POLICIES where LogSpecificData = 1
–Find all Runbooks with Common Data Logging Enabled
select * from POLICIES where LogCommonData = 1
Runbook Modifications
–Turn Off Common Logging for All Runbooks
Use Orchestrator
DECLARE @id UNIQUEIDENTIFIER
DECLARE @name NVARCHAR(MAX)
DECLARE curs CURSOR FOR
SELECT
UniqueId, Name
FROM
POLICIES
WHERE
LogCommonData = 1
OPEN curs
FETCH NEXT FROM curs
INTO @id, @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ‘Turning Off Common Data Logging For : ‘ + @name
UPDATE POLICIES
SET LogCommonData = 0
WHERE UniqueID = @id
FETCH NEXT FROM curs
INTO @id, @name
END
CLOSE curs
DEALLOCATE curs
–Turn On Common Logging for All Runbooks
Use Orchestrator
DECLARE @id UNIQUEIDENTIFIER
DECLARE @name NVARCHAR(MAX)
DECLARE curs CURSOR FOR
SELECT
UniqueId, Name
FROM
POLICIES
WHERE
LogCommonData = 0
OPEN curs
FETCH NEXT FROM curs
INTO @id, @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ‘Turning On Common Data Logging For : ‘ + @name
UPDATE POLICIES
SET LogCommonData = 1
WHERE UniqueID = @id
FETCH NEXT FROM curs
INTO @id, @name
END
CLOSE curs
DEALLOCATE curs
–Turn Off Object Specific Logging for All Runbooks
Use Orchestrator
DECLARE @id UNIQUEIDENTIFIER
DECLARE @name NVARCHAR(MAX)
DECLARE curs CURSOR FOR
SELECT
UniqueId, Name
FROM
POLICIES
WHERE
LogSpecificData = 1
OPEN curs
FETCH NEXT FROM curs
INTO @id, @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ‘Turning Off Specific Data Logging For : ‘ + @name
UPDATE POLICIES
SET LogSpecificData = 0
WHERE UniqueID = @id
FETCH NEXT FROM curs
INTO @id, @name
END
CLOSE curs
DEALLOCATE curs
–Turn On Object Specific Logging for All Runbooks
Use Orchestrator
DECLARE @id UNIQUEIDENTIFIER
DECLARE @name NVARCHAR(MAX)
DECLARE curs CURSOR FOR
SELECT
UniqueId, Name
FROM
POLICIES
WHERE
LogSpecificData = 0
OPEN curs
FETCH NEXT FROM curs
INTO @id, @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ‘Turning Off Specific Data Logging For : ‘ + @name
UPDATE POLICIES
SET LogSpecificData = 1
WHERE UniqueID = @id
FETCH NEXT FROM curs
INTO @id, @name
END
CLOSE curs
DEALLOCATE curs
Hi, when i check all your queries i noticed that the wrong single quote is being used. copy pasting the queries in sql 2008 or 2012 will result in a error. I think wordpress is using the wrong code for sinlge quotes (html code: ’) instead of ' If i change the queries with a normal single quote they will work
.
Hi
How often do you run the “Refresh Authorization Cache” query ?
We disabled the SQL Job to do the automated truncate of the authroization table and run this query instead every hour. We also have an out of band request (surfaced in Service Manager) that people can use ad-hoc if they need a quicker web service refresh
Hi
Why did you disable the sql job ?
Disabled the SQL job so that the authorization table would not be cleared unless it was by our process, this minimizes the potential for a clear and then an incorrect calculation due to a web service hit (and managed code refresh). Our scheduled refresh clears the auth table then re-calculates the authorization table without a set timeout limiting the potential for a ‘half calculated’ authorization cache