Page tree
Skip to end of metadata
Go to start of metadata


My postings are my own and don’t necessarily represent VMware’s positions, strategies or opinions.

SQL Database Health

Query NameDescriptionQuery
Index Fragmentation AuditThis query provides you with the index fragmentation % for every table in the DB./**************************************************************
   You can use the following script to determine index 
   fragmentation by table. This can help to determine 
   that the database is causing performace problems.

SELECT OBJECT_NAME(object_id), index_id, index_type_desc, index_level, avg_fragmentation_in_percent, avg_Page_space_used_in_percent, page_count 
FROM sys.dm_db_index_physical_stats(DB_ID(N'AirWatch'), null, null, null, 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC
Table Size AuditThis query will provide physical sizing information of all the tables in the database./**************************************************************
    This query can help you determine what table is taking
    up the most disk space and potentially what tables have
    too many rows.

--If the temp table exisits drop temp table
IF EXISTS ( SELECT * FROM sys.objects 
     WHERE object_id = OBJECT_ID(N'#Sizes') 
     AND type in (N'U') ) 
     DROP TABLE #Sizes; 

--Create temp table, #Sizes 
--Table name 
     table_name nvarchar(255), 
--Number of rows 
     table_rows char(11),
--Physical space table is using
     table_reserved varchar(18),
--Phyiscal space table data is using
     table_data varchar(18),
--Physical space idexes are using
     table_index_size varchar(18),
--Physical space reserved
     table_unused varchar(18)

EXEC sp_MSforeachtable @command1='INSERT #Sizes (table_name, table_rows, table_reserved,
table_data, table_index_size, table_unused) EXEC sp_spaceused ''?'''
--This query orders the results by actual phyisical table size
SELECT * FROM #Sizes ORDER BY CAST ( SUBSTRING (table_data, 0, LEN(table_data) - 2) AS InT) DESC
--This query orders the results by row count
--SELECT * FROM #Sizes ORDER BY CAST (table_rows AS int) DESC
Table Row Count Only A more efficient script to get table row counts./**************************************************************
    This is a more efficient way to get Row Counts
    but will not include any Physical Sizing data

SELECT +'.'+ TableName
     , SUM(pa.rows) RowCnt
     sys.tables ta
     INNER JOIN sys.partitions pa
          INNER JOIN sys.schemas sc
          ON ta.schema_id = sc.schema_id
     ta.is_ms_shipped = 0 
      AND pa.index_id IN (1,0)
     SUM(pa.rows) DESC;
Database IO Stalls You can use this query to see which queries are causing IO stalls./**************************************************************
   The table valued dynamic management function, 
   sys.dm_io_virtual_file_stats provides a breakdown of SQL 
   Server reads, writes, and io_stalls for a particular
   database or transaction log file. IO_stalls is the total 
   cumulative time, in milliseconds, that users waited for
   I/O to be completed on the file since the last restart    of SQL Server. 

     DB_NAME(fs.database_id) AS [DB Name]
     , fs.file_id
     , mf.physical_name
     , io_stall_read_ms
     , num_of_reads
     , CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10 , 1)) AS 'avg_read_stall_ms'
     , io_stall_write_ms
     , num_of_writes
     , CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10 , 1)) AS 'avg_write_stall_ms'
     , io_stall_read_ms + io_stall_write_ms AS io_stalls
     , num_of_reads + num_of_writes AS total_io
     , CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads
          + num_of_writes ) AS NUMERIC(10 ,
          1)) AS 'avg_io_stall_ms'
     sys.dm_io_virtual_file_stats(NULL , NULL) AS fs
INNER JOIN sys.master_files AS mf
          ON fs.database_id = mf.database_id
          AND fs.[file_id] = mf.[file_id]
     avg_io_stall_ms DESC ;
Identify Expensive Operations
   This query provides you with operations that are expensive
   from a database standpoint. This query is useful in
   determining what is causing performance problems on a server

     DB_NAME(qp.[dbid]) AS dbname
     , qp.[dbid]
     , qp.objectid
     , qp.number 
     --, qp.query_plan 
     --the query plan can be *very* useful; enable if desired 
     , qt.[text]
     , SUBSTRING(qt.[text] , 
          ( qs.statement_start_offset / 2 ) + 1 ,
          ( ( CASE statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
          ELSE qs.statement_end_offset
     END - qs.statement_start_offset ) / 2 ) + 1) 
         AS statement_text
     , qs.creation_time
     , qs.last_execution_time
     , qs.execution_count
     , qs.total_worker_time / qs.execution_count 
         AS avg_worker_time
     , qs.total_physical_reads / qs.execution_count 
         AS avg_physical_reads
     , qs.total_logical_reads / qs.execution_count 
         AS avg_logical_reads
     , qs.total_logical_writes / qs.execution_count 
         AS avg_logical_writes
     , qs.total_elapsed_time / qs.execution_count 
         AS avg_elapsed_time
     , qs.total_clr_time / qs.execution_count
     nbsp;   AS avg_clr_time
     --, qs.total_worker_time 
     --, qs.last_worker_time 
     --, qs.min_worker_time 
     --, qs.max_worker_time 
     , qs.total_physical_reads
     , qs.last_physical_reads
     , qs.min_physical_reads
     , qs.max_physical_reads 
     --, qs.total_logical_reads 
     --, qs.last_logical_reads 
     --, qs.min_logical_reads 
     --, qs.max_logical_reads 
     --, qs.total_logical_writes 
     --, qs.last_logical_writes 
     --, qs.min_logical_writes 
     --, qs.max_logical_writes 
     --, qs.total_elapsed_time 
     --, qs.last_elapsed_time 
     --, qs.min_elapsed_time 
     --, qs.max_elapsed_time
     --, qs.total_clr_time 
     --, qs.last_clr_time 
     --, qs.min_clr_time 
     --, qs.max_clr_time 
     --, qs.[sql_handle] 
     --, qs.statement_start_offset 
     --, qs.statement_end_offset 
     --, qs.plan_generation_num 
     --, qp.encrypted 
     sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
     --sample WHERE 
     last_execution_time > '20120912 12:15'
     AND creation_time > '20130101'
     AND execution_count > 10
     --AND SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
     --((CASE statement_end_offset 
     --WHEN -1 THEN DATALENGTH(qt.text)
     --ELSE qs.statement_end_offset END 
     -- - qs.statement_start_offset)/2) 
     -- + 1)
     -- LIKE '%MyText%'

     --sample ORDER BY
     --ORDER BY qs.execution_count DESC --Frequency
     --ORDER BY qs.total_worker_time DESC --CPU
     --ORDER BY avg_worker_time DESC --CPU
     --ORDER BY qs.total_elapsed_time DESC --Durn
     --ORDER BY qs.total_logical_reads DESC --Reads 
     --ORDER BY qs.total_logical_writes DESC --Writes
     --ORDER BY qs.total_physical_reads DESC --PhysicalReads 
     --ORDER BY avg_worker_time DESC --AvgCPU
     --ORDER BY avg_elapsed_time DESC --AvgDurn 
     --ORDER BY avg_logical_reads DESC --AvgReads
     --ORDER BY avg_logical_writes DESC --AvgWrites
     avg_physical_reads DESC --AvgPhysicalReads

General SQL Queries

Query NameDescriptionQuery
Table search by column nameThis query allows you to see what tables / procedures have a specified column name in them./**************************************************************
   Example(s) of common column names:
   DeviceId, LocationGroupId, ApplicationId, ProfileId, 

SELECT * FROM sysobjects WHERE id IN 
      (SELECT id FROM syscolumns WHERE name LIKE '%ColumnName%')
sp_who2SP_WHO2 can let you check to see if the AirWatch DB has any active connections.sp_who2
SP_WHO2 Advanced QueryThis allows you to filter on SP_WHO2, which helps when backing up and restoring the AirWatch database./**************************************************************
   This query may look complex but can really
   narrow down if something is connected to 
   AirWatch or not. When you are upgrading
   or restoring a database you need to ensure
   that nothing is locking the databse. **************************************************************/ 

CREATE TABLE #sp_who2( 
     SPID INT, 
     Status VARCHAR(1000) NULL, 
     Login SYSNAME NULL, 
     HostName SYSNAME NULL, 
     Command VARCHAR(1000) NULL, 
     CPUTime INT NULL, 
     DiskIO INT NULL, 
     LastBatch VARCHAR(1000) NULL, 
     ProgramName VARCHAR(1000) NULL, 
     SPID2 INT 

INSERT INTO #sp_who2 EXEC sp_who2 

FROM #sp_who2 
WHERE DBName LIKE '%AirWatch%'

DROP TABLE #sp_who2 

Top 10 AirWatch Queries

[AirWatch Module] - Query NameDescriptionQuery
[Device] - Get all devices from Location GroupThis query can return all devices under one locationgroup tree. This is an especially helpful query when used with other queries./**************************************************************
   This script provides all devices residing under
   the parent location group. You can combine this
   query with other queries by using the WHERE column
   IN (SELECT query) filter (this script uses the
   same filter to filter the location groups)

FROM Device d (nolock)
INNER JOIN Location l (nolock)
ON l.LocationId = d.LocationId
INNER JOIN LocationGroup lg (nolock)
lg.DefaultLocationId = l.LocationId
WHERE lg.LocationGroupID IN 
      The below sub query provides all children
      Location Groups under one Parent. This can 
      also be useful with other queries.

   SELECT lgf.ChildLocationGroupID
   FROM LocationGroup lg (nolock)
   INNER JOIN LocationGroupFlat lgf (nolock)
   ON lgf.ParentLocationGroupID = lg.LocationGroupID
   WHERE lg.Name LIKE '%Customers LG%'
[Event Log] - Event Log SearchThis query allows you to search the event log. This is imperitive since the event log often times out from the console./**************************************************************
   This script provides you with a list of modules in the
   event log. These can be used to filter the following

FROM eventLog.EventLog el (nolock) 

   This query will select information from the event log 
   and allow you to filter on Modules. Modules provide you
   with context of the event that occured. The Name is 
   the actual name of the event that occured. This will 
   only show you the last 30 days. You can decrease this 
   number to improve performance.
   You can also filter on the LocationGroupID if you know 
   the location group name. Keep in mind that some modules 
   only report at Global.

   The Username is the Admin user who performed the task. 
   sysadmin is the system user for running stored procedures 
   however some modules (like the device module) show 
   sysadmin even if a user performed the action.

SELECT cu.UserName, el.Module, e.Name, el.*
FROM eventLog.EventLog el (nolock)
INNER JOIN eventLog.Event e (nolock)
ON e.EventId = el.EventId
INNER JOIN CoreUser cu (nolock)
ON cu.CoreUserId = el.ActionBy
AND el.Module LIKE 'Dashboard'
--AND LocationGroupID IN (SELECT LocationGroupID 
--FROM LocationGroup WHERE Name LIKE '%AirWatch%')
[Roles] - Role compare scriptThis script shows you two roles side by side for comparison./**************************************************************
   Update the values in @Role1 and @Role2
   to compare two roles. If you need to find
   why a custom role is missing use the next

DECLARE @Role1 AS nvarchar(50);
DECLARE @Role2 AS nvarchar(50);
SET @Role1 = 'AirWatch Administrator';
SET @Role2 = 'System Administrator';

SELECT re.ResourceID, c.Name AS 'Module', re.Name, m1.Allow AS 'Role1 Allowed', m2.Allow AS 'Role2 Allowed'
FROM Resource re 
INNER JOIN Category c (nolock)
ON c.CategoryID = re.CategoryID
INNER JOIN Mode m1 (nolock)
ON re.ResourceID = m1.ResourceID
INNER JOIN Role r1 (nolock)
ON r1.RoleId = m1.RoleId
INNER JOIN Mode m2 (nolock)
ON re.ResourceID = m2.ResourceID
INNER JOIN Role r2 (nolock)
ON r2.RoleID = m2.RoleID 
WHERE r1.Name = @Role1 AND r2.Name = @Role2
ORDER BY Module, ResourceID
[Roles] - Missing Custom Role scriptThis script shows you two roles side by side for comparison./**************************************************************
   Update the values in the variables to check
   if resources are missing from the role who is
   supposed to have higher priviledges.

DECLARE @HigherRole AS nvarchar(50);
DECLARE @LowerRole AS nvarchar(50);
SET @HigherRole = 'Role with higher privileges';
SET @LowerRole = 'Role with less privileges';

SELECT m.ModeID, r.Name, re.Name, m.Allow 
FROM Mode m (nolock)
INNER JOIN Role r (nolock)
ON r.RoleId = m.RoleId
INNER JOIN Resource re 
ON re.ResourceID = m.ResourceID
WHERE r.Name LIKE @LowerRole AND m.Allow = 1 AND
       m.ResourceID IN
       (SELECT ResourceID FROM Mode m 
       INNER JOIN Role r
       ON r.RoleId = m.RoleId
       WHERE r.Name LIKE @HigherRole AND
       m.Allow = 0

Status of Events stuck in 'Processing' or 'Failed' to 'Ready for Processing'

UPDATE adp.AdpExportTracking
    SET [Status] = 2 -- 2 = Completed Event
WHERE [Status] = 1; -- 2 = Pending Event

-- This script updates the status of Error Exports in adp.ADPExportTracking table


    IF  OBJECT_ID(N'adp.ADPExportTracking') IS NOT NULL
        UPDATE adp.ADPExportTracking
            SET [Status] = 2
        WHERE [Status] = -1 -- -1 = Failed Status
    DECLARE    @error_severity        INT,
            @error_state        INT,
            @error_message        NVARCHAR(2048);

    SELECT    @error_severity = ERROR_SEVERITY(),
            @error_state = ERROR_STATE(),
            @error_message = ERROR_MESSAGE();

    RAISERROR(@error_message, @error_severity, @error_state);