Useful SQL Queries

/**************************************************************
   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

/**************************************************************
    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') ) 
     BEGIN 
     DROP TABLE #Sizes; 
     END 

--Create temp table, #Sizes 
CREATE 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

/**************************************************************
    This is a more efficient way to get Row Counts
    but will not include any Physical Sizing data
   
**************************************************************/ 

SELECT 
     sc.name +'.'+ ta.name TableName
     , SUM(pa.rows) RowCnt
FROM 
     sys.tables ta
     INNER JOIN sys.partitions pa
          INNER JOIN sys.schemas sc
          ON ta.schema_id = sc.schema_id
WHERE 
     ta.is_ms_shipped = 0 
      AND pa.index_id IN (1,0)
GROUP BY 
     sc.name
     , ta.name
ORDER BY 
     SUM(pa.rows) DESC;

/**************************************************************
   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. 
**************************************************************/ 

SELECT
     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'
FROM
     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]
ORDER BY
     avg_io_stall_ms DESC ;
GO

/**************************************************************
   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
**************************************************************/ 

SELECT TOP 25
     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 
FROM
     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 
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
ORDER BY
     avg_physical_reads DESC --AvgPhysicalReads

/**************************************************************
   Example(s) of common column names:
   DeviceId, LocationGroupId, ApplicationId, ProfileId, 
   CoreUserId
**************************************************************/ 

SELECT * FROM sysobjects WHERE id IN 
      (SELECT id FROM syscolumns WHERE name LIKE '%ColumnName%')

/**************************************************************  
   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   
  
SELECT *   
FROM #sp_who2   
WHERE DBName LIKE '%AirWatch%'  
GO   
  
DROP TABLE #sp_who2   
GO

/**************************************************************
   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)
**************************************************************/ 

SELECT *
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 script provides you with a list of modules in the  
   event log. These can be used to filter the following  
   query.  
**************************************************************/   
SELECT DISTINCT el.Module   
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%')

/**************************************************************
   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
   script.
**************************************************************/ 

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

/**************************************************************
   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
       )

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
 
SET NOCOUNT ON;
 
BEGIN TRY
    IF  OBJECT_ID(N'adp.ADPExportTracking') IS NOT NULL
    BEGIN
        UPDATE adp.ADPExportTracking
            SET [Status] = 2
        WHERE [Status] = -1 -- -1 = Failed Status
    END
     
END TRY
BEGIN CATCH
    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);
 
END CATCH
GO