Useful SQL Queries

SQL Database Health

Index Fragmentation Audit

This 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 Audit

This 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

Table search by column name

This 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_WHO2 Advanced Query

This 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,   
     BlkBy SYSNAME NULL,   
     DBName 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   

Get all devices from Location Group

This 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%'

This 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  
WHERE el.CreatedOn > DATEADD(DAY, -30, GETUTCDATE())  
AND el.Module LIKE 'Dashboard'  
--AND LocationGroupID IN (SELECT LocationGroupID   
--FROM LocationGroup WHERE Name LIKE '%AirWatch%')

Role compare script

This 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

Missing Custom Role script

This 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);