/*SQL Differential Backup Size Prediction / Estimation 
 
IF isNULL(object_id('tempdb.dbo.##showFileStats'), 1) <> 1
 DROP TABLE ##showFileStats
 
CREATE TABLE ##showFileStats (
 fileID INT,
 fileGroup INT,
 totalExtents BIGINT,
 usedExtents BIGINT,
 logicalFileName VARCHAR (500), 
 filePath VARCHAR (1000)
)
 
IF isNULL(object_id('tempdb.dbo.##DCM'), 1) <> 1
 DROP TABLE ##DCM
 
CREATE TABLE ##DCM (
 parentObject VARCHAR(5000),
 [object] VARCHAR(5000),
 FIELD VARCHAR (5000),
 VALUE VARCHAR (5000)
)
 
/*we need to get a list of all the files in the database.  each file needs to be looked at*/ 
INSERT INTO ##showFileStats EXEC('DBCC SHOWFILESTATS with tableresults')
 
DECLARE @currentFileID INT,
 @totalExtentsOfFile BIGINT,
 @dbname VARCHAR(100),
 @SQL VARCHAR(200),
 @currentDCM BIGINT,
 @step INT
 
SET @dbname = db_name()
SET @step = 511232
 
DECLARE myCursor SCROLL CURSOR FOR
SELECT fileID, totalExtents 
FROM ##showFileStats
 
OPEN myCursor
FETCH NEXT FROM myCursor INTO @currentFileID, @totalExtentsOfFile
 
/*look at each differential change map page in each data file of the database and put the output into ##DCM*/
WHILE @@FETCH_STATUS = 0 
BEGIN
 
 SET @currentDCM = 6
 WHILE @currentDCM <= @totalExtentsOfFile*8
 BEGIN 
  SET @SQL = 'dbcc page('+ @dbname + ', ' + CAST(@currentFileID AS VARCHAR) + ', ' + CAST(@currentDCM AS VARCHAR) + ', 3) WITH TABLERESULTS'
  INSERT INTO ##DCM EXEC (@SQL)
  SET @currentDCM = @currentDCM + @step
 END
 
 FETCH NEXT FROM myCursor INTO @currentFileID, @totalExtentsOfFile
END
CLOSE myCursor
DEALLOCATE myCursor
 
/*remove all unneeded rows from our results table*/
DELETE FROM ##DCM WHERE VALUE = 'NOT CHANGED' OR parentObject NOT LIKE 'DIFF_MAP%'
--SELECT * FROM ##DCM
 
/*sum the extentTally column*/
SELECT SUM (extentTally) AS totalChangedExtents, SUM(extentTally)/16 AS 'diffPrediction(MB)', SUM(extentTally)/16/1024 AS 'diffPrediction(GB)' 
FROM
 /*create extentTally column*/
 (SELECT extentTally =
 CASE
  WHEN secondChangedExtent > 0 THEN CAST(secondChangedExtent AS BIGINT) - CAST(firstChangedExtent AS BIGINT) + 1
  ELSE 1
 END
 FROM
  /*parse the 'field' column to give us the first and last extents of the range*/
  (SELECT (SUBSTRING(FIELD,(SELECT CHARINDEX(':', FIELD, 0))+1,(CHARINDEX(')', FIELD, 0))-(CHARINDEX(':', FIELD, 0))-1))/8 AS firstChangedExtent,
  secondChangedExtent = 
  CASE 
   WHEN CHARINDEX(':', FIELD, CHARINDEX(':', FIELD, 0)+1) > 0 THEN (SUBSTRING(FIELD,(CHARINDEX(':', FIELD, CHARINDEX(':', FIELD, 0)+1)+1),(CHARINDEX(')', FIELD,CHARINDEX(')', FIELD, 0)+1))-(CHARINDEX(':', FIELD, CHARINDEX(':', FIELD, 0)+1))-1))/8
   ELSE ''
  END 
  FROM ##DCM)parsedFieldColumn)extentTallyColumn

SQL Differential Backup Size Prediction / Estimation

SQL 24X7 0

Read More

-- Shows the memory required by both running (non-null grant_time)
-- and waiting queries (null grant_time)
-- SQL Server 2008 version
SELECT DB_NAME(r.database_id) AS [DatabaseName]
, s.login_name
, s.session_id
, r.status
, r.blocking_session_id
, r.cpu_time
, r.logical_reads
, r.writes
, r.wait_resource
, r.wait_type
, r.wait_time
, r.granted_query_memory
, mg.requested_memory_kb
, mg.ideal_memory_kb
, mg.request_time
, mg.grant_time
, mg.query_cost
, mg.dop
, st.[text]
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests as r on r.session_id = s.session_id
INNER JOIN sys.dm_exec_query_memory_grants AS mg ON mg.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS st
--CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE MG.session_id <> @@SPID
ORDER BY mg.requested_memory_kb DESC
OPTION (MAXDOP 1, RECOMPILE)

Shows the memory required by both running (non-null grant_time) and waiting queries

SQL 24X7 0

Read More

-- Find Top 10 Most Expensive Statements by logical read/write or cpu time
SELECT TOP 10 SUBSTRING(qt.TEXT,(qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads,
qs.last_logical_reads,
qs.total_logical_writes,
qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_logical_reads DESC; --logical reads
--ORDER BY qs.total_logical_writes DESC; --logical writes
--ORDER BY qs.total_worker_time DESC; --CPU time

Find Top 10 Most Expensive Statements by logical read/write or cpu time

SQL 24X7 0

Read More

WITH Waits AS

(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,

100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,

ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn

FROM sys.dm_os_wait_stats

WHERE wait_type NOT IN

('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'

,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'

,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'

,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'

,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))

SELECT W1.wait_type,

CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

FROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.rn <= W1.rn

GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE) -- percentage threshold

GetTopSQLServerWaits_ObservingWaitStatistics

SQL 24X7 0

Read More