Useful Orchestrator SQL Queries

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

Advertisements
Tagged with: , , ,
Posted in General Information
6 comments on “Useful Orchestrator SQL Queries
  1. Jeroen says:

    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: &#8217) instead of ' If i change the queries with a normal single quote they will work ;).

  2. Robert says:

    Hi
    How often do you run the “Refresh Authorization Cache” query ?

    • randorfer says:

      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

      • Robert says:

        Hi
        Why did you disable the sql job ?

      • randorfer says:

        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

  3. Hank says:

    Hey – I’m looking for a query that lists all runbooks that are monitors and their current status. We have issues when orchestrator server is restarted the monitor is not automatically started.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: