Is Anything Timing Out?

SELECT * FROM sys.dm_exec_query_optimizer_info 
WHERE counter = 'timeout'

Identify Queries Causing Waits

SELECT r.session_id -- new column for SPID
,r.database_id
,r.user_id
,r.status
,st.text
,r.wait_type
,r.wait_time
,r.last_wait_type
,r.command
,es.host_name
,es.program_name
,es.nt_domain
,es.nt_user_name
,es.login_name
,mg.dop --Degree of parallelism
,mg.request_time --Date and time when this query requested the memory grant.
,mg.grant_time --NULL means memory has not been granted
,mg.requested_memory_kb --Total requested amount of memory in kilobytes
,mg.granted_memory_kb --Total amount of memory actually granted in kilobytes. NULL if not granted
,mg.required_memory_kb --Minimum memory required to run this query in kilobytes.
,mg.query_cost --Estimated query cost.
,mg.timeout_sec --Time-out in seconds before this query gives up the memory grant request.
,mg.resource_semaphore_id --Nonunique ID of the resource semaphore on which this query is waiting.
,mg.wait_time_ms --Wait time in milliseconds. NULL if the memory is already granted.
,CASE mg.is_next_candidate --Is this process the next candidate for a memory grant
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Memory has been granted'
END AS 'Next Candidate for Memory Grant'
,rs.target_memory_kb --Grant usage target in kilobytes.
,rs.max_target_memory_kb --Maximum potential target in kilobytes. NULL for the small-query resource semaphore.
,rs.total_memory_kb --Memory held by the resource semaphore in kilobytes.
,rs.available_memory_kb --Memory available for a new grant in kilobytes.
,rs.granted_memory_kb --Total granted memory in kilobytes.
,rs.used_memory_kb --Physically used part of granted memory in kilobytes.
,rs.grantee_count --Number of active queries that have their grants satisfied.
,rs.waiter_count --Number of queries waiting for grants to be satisfied.
,rs.timeout_error_count --Total number of time-out errors since server startup. NULL for the small-query resource semaphore.
,rs.forced_grant_count --Total number of forced minimum-memory grants since server startup. NULL for the small-query resource semaphore.
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions es
ON r.session_id = es.session_id
INNER JOIN sys.dm_exec_query_memory_grants mg
ON r.session_id = mg.session_id
INNER JOIN sys.dm_exec_query_resource_semaphores rs
ON mg.resource_semaphore_id = rs.resource_semaphore_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)st

Wait Stats in SQL Server

/*
SQL Server Wait Information from sys.dm_os_wait_stats
Copyright (C) 2014, Brent Ozar Unlimited.
See http://BrentOzar.com/go/eula for the End User Licensing Agreement.
*/

/*********************************
Let's build a list of waits we can safely ignore.
*********************************/
IF OBJECT_ID('tempdb..#ignorable_waits') IS NOT NULL
    DROP TABLE #ignorable_waits;
GO

create table #ignorable_waits (wait_type nvarchar(256) PRIMARY KEY);
GO

/* We aren't using row constructors to be SQL 2005 compatible */
set nocount on;
insert #ignorable_waits (wait_type) VALUES ('REQUEST_FOR_DEADLOCK_SEARCH');
insert #ignorable_waits (wait_type) VALUES ('SQLTRACE_INCREMENTAL_FLUSH_SLEEP');
insert #ignorable_waits (wait_type) VALUES ('SQLTRACE_BUFFER_FLUSH');
insert #ignorable_waits (wait_type) VALUES ('LAZYWRITER_SLEEP');
insert #ignorable_waits (wait_type) VALUES ('XE_TIMER_EVENT');
insert #ignorable_waits (wait_type) VALUES ('XE_DISPATCHER_WAIT');
insert #ignorable_waits (wait_type) VALUES ('FT_IFTS_SCHEDULER_IDLE_WAIT');
insert #ignorable_waits (wait_type) VALUES ('LOGMGR_QUEUE');
insert #ignorable_waits (wait_type) VALUES ('CHECKPOINT_QUEUE');
insert #ignorable_waits (wait_type) VALUES ('BROKER_TO_FLUSH');
insert #ignorable_waits (wait_type) VALUES ('BROKER_TASK_STOP');
insert #ignorable_waits (wait_type) VALUES ('BROKER_EVENTHANDLER');
insert #ignorable_waits (wait_type) VALUES ('SLEEP_TASK');
insert #ignorable_waits (wait_type) VALUES ('WAITFOR');
insert #ignorable_waits (wait_type) VALUES ('DBMIRROR_DBM_MUTEX')
insert #ignorable_waits (wait_type) VALUES ('DBMIRROR_EVENTS_QUEUE')
insert #ignorable_waits (wait_type) VALUES ('DBMIRRORING_CMD');
insert #ignorable_waits (wait_type) VALUES ('DISPATCHER_QUEUE_SEMAPHORE');
insert #ignorable_waits (wait_type) VALUES ('BROKER_RECEIVE_WAITFOR');
insert #ignorable_waits (wait_type) VALUES ('CLR_AUTO_EVENT');
insert #ignorable_waits (wait_type) VALUES ('DIRTY_PAGE_POLL');
insert #ignorable_waits (wait_type) VALUES ('HADR_FILESTREAM_IOMGR_IOCOMPLETION');
insert #ignorable_waits (wait_type) VALUES ('ONDEMAND_TASK_QUEUE');
insert #ignorable_waits (wait_type) VALUES ('FT_IFTSHC_MUTEX');
insert #ignorable_waits (wait_type) VALUES ('CLR_MANUAL_EVENT');
insert #ignorable_waits (wait_type) VALUES ('SP_SERVER_DIAGNOSTICS_SLEEP');
insert #ignorable_waits (wait_type) VALUES ('QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP');
insert #ignorable_waits (wait_type) VALUES ('QDS_PERSIST_TASK_MAIN_LOOP_SLEEP');
GO

/* Want to manually exclude an event and recalculate?*/
/* insert #ignorable_waits (wait_type) VALUES (''); */

/*********************************
What are the highest overall waits since startup?
*********************************/
SELECT TOP 25
 os.wait_type,
 SUM(os.wait_time_ms) OVER (PARTITION BY os.wait_type) as sum_wait_time_ms,
 CAST(
  100.* SUM(os.wait_time_ms) OVER (PARTITION BY os.wait_type)
  / (1. * SUM(os.wait_time_ms) OVER () )
  AS NUMERIC(12,1)) as pct_wait_time,
 SUM(os.waiting_tasks_count) OVER (PARTITION BY os.wait_type) AS sum_waiting_tasks,
 CASE WHEN  SUM(os.waiting_tasks_count) OVER (PARTITION BY os.wait_type) > 0
 THEN
  CAST(
   SUM(os.wait_time_ms) OVER (PARTITION BY os.wait_type)
    / (1. * SUM(os.waiting_tasks_count) OVER (PARTITION BY os.wait_type))
   AS NUMERIC(12,1))
 ELSE 0 END AS avg_wait_time_ms,
 CURRENT_TIMESTAMP as sample_time
FROM sys.dm_os_wait_stats os
LEFT JOIN #ignorable_waits iw on
 os.wait_type=iw.wait_type
WHERE
 iw.wait_type is null
ORDER BY sum_wait_time_ms DESC;
GO

/*********************************
What are the higest waits *right now*?
*********************************/

/* Note: this is dependent on the #ignorable_waits table created earlier. */
if OBJECT_ID('tempdb..#wait_batches') is not null
 drop table #wait_batches;
if OBJECT_ID('tempdb..#wait_data') is not null
 drop table #wait_data;
GO

CREATE TABLE #wait_batches (
 batch_id int identity primary key,
 sample_time datetime not null
);
CREATE TABLE #wait_data
    ( batch_id INT NOT NULL ,
      wait_type NVARCHAR(256) NOT NULL ,
      wait_time_ms BIGINT NOT NULL ,
      waiting_tasks BIGINT NOT NULL
    );
CREATE CLUSTERED INDEX cx_wait_data on #wait_data(batch_id);
GO

/*
This temporary procedure records wait data to a temp table.
*/
if OBJECT_ID('tempdb..#get_wait_data') IS NOT NULL
 DROP procedure #get_wait_data;
GO
CREATE PROCEDURE #get_wait_data
 @intervals tinyint = 2,
 @delay char(12)='00:00:30.000' /* 30 seconds*/
AS
DECLARE @batch_id int,
 @current_interval tinyint,
 @msg nvarchar(max);

SET NOCOUNT ON;
SET @current_interval=1;

WHILE @current_interval <= @intervals
BEGIN
 INSERT #wait_batches(sample_time)
 SELECT CURRENT_TIMESTAMP;

 SELECT @batch_id=SCOPE_IDENTITY();

 INSERT #wait_data (batch_id, wait_type, wait_time_ms, waiting_tasks)
 SELECT
  @batch_id,
  os.wait_type,
  SUM(os.wait_time_ms) OVER (PARTITION BY os.wait_type) as sum_wait_time_ms,
  SUM(os.waiting_tasks_count) OVER (PARTITION BY os.wait_type) AS sum_waiting_tasks
 FROM sys.dm_os_wait_stats os
 LEFT JOIN #ignorable_waits iw on
  os.wait_type=iw.wait_type
 WHERE
  iw.wait_type is null
 ORDER BY sum_wait_time_ms DESC;

 set @msg= CONVERT(char(23),CURRENT_TIMESTAMP,121)+ N': Completed sample '
  + cast(@current_interval as nvarchar(4))
  + N' of ' + cast(@intervals as nvarchar(4)) +
  '.'
 RAISERROR (@msg,0,1) WITH NOWAIT;

 SET @current_interval=@current_interval+1;

 if @current_interval <= @intervals
  WAITFOR DELAY @delay;
END
GO

/*
Let's take two samples 30 seconds apart
*/
exec #get_wait_data @intervals=2, @delay='00:00:30.000';
GO

/*
What were we waiting on?
This query compares the most recent two samples.
*/
with max_batch as (
 select top 1 batch_id, sample_time
 from #wait_batches
 order by batch_id desc
)
SELECT
 b.sample_time as [Second Sample Time],
 datediff(ss,wb1.sample_time, b.sample_time) as [Sample Duration in Seconds],
 wd1.wait_type,
 cast((wd2.wait_time_ms-wd1.wait_time_ms)/1000. as numeric(12,1)) as [Wait Time (Seconds)],
 (wd2.waiting_tasks-wd1.waiting_tasks) AS [Number of Waits],
 CASE WHEN (wd2.waiting_tasks-wd1.waiting_tasks) > 0
 THEN
  cast((wd2.wait_time_ms-wd1.wait_time_ms)/
   (1.0*(wd2.waiting_tasks-wd1.waiting_tasks)) as numeric(12,1))
 ELSE 0 END AS [Avg ms Per Wait]
FROM  max_batch b
JOIN #wait_data wd2 on
 wd2.batch_id=b.batch_id
JOIN #wait_data wd1 on
 wd1.wait_type=wd2.wait_type AND
 wd2.batch_id - 1 = wd1.batch_id
join #wait_batches wb1 on
 wd1.batch_id=wb1.batch_id
WHERE (wd2.waiting_tasks-wd1.waiting_tasks) > 0
ORDER BY [Wait Time (Seconds)] DESC;
GO

/*

For a description of each wait see: http://msdn.microsoft.com/en-us/library/ms179984

source: http://www.brentozar.com/responder/triage-wait-stats-in-sql-server/

*/

Figuring out when Wait Statistics were last cleared

SELECT
[wait_type],
[wait_time_ms],
-- ************************************************************************
-- If there's a conversion to int error than the wait_time_ms is too long.
-- Convert to seconds instead.
-- DATEADD(ss,-[wait_time_ms]/1000,getdate()) AS [Date/TimeCleared],
-- ************************************************************************
DATEADD(ms,-[wait_time_ms],getdate()) AS [Date/TimeCleared],
CASE
WHEN [wait_time_ms] < 1000 THEN CAST([wait_time_ms] AS VARCHAR(15)) + ' ms'
WHEN [wait_time_ms] between 1000 and 60000 THEN CAST(([wait_time_ms]/1000) AS VARCHAR(15)) + ' seconds'
WHEN [wait_time_ms] between 60001 and 3600000 THEN CAST(([wait_time_ms]/60000) AS VARCHAR(15)) + ' minutes'
WHEN [wait_time_ms] between 3600001 and 86400000 THEN CAST(([wait_time_ms]/3600000) AS VARCHAR(15)) + ' hours'
WHEN [wait_time_ms] > 86400000 THEN CAST(([wait_time_ms]/86400000) AS VARCHAR(15)) + ' days'
END [TimeSinceCleared]
FROM [sys].[dm_os_wait_stats]
WHERE [wait_type] = 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP';

/* check SQL Server start time - 2008 and higher */
SELECT
[sqlserver_start_time]
FROM [sys].[dm_os_sys_info];


-- ***********************************************************************
-- To clear statistics
-- DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
-- ***********************************************************************

source: http://www.sqlskills.com/blogs/erin/figuring-out-when-wait-stats-were-last-cleared/

BigInt, Identity, and Jumping IDs

If you have a table in SQL Server 2012 and you specify one field as bigint with identity set to Yes then you will probably run into jumping IDs should you ever restart the service.

Problem: SQL Server jumps auto increment by 10k records on bigint fields set to identity after a service restart.

1. I created a basic table to recreate the issue:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[testIdentity](
[MyBigInt] [bigint] IDENTITY(1,1) NOT NULL,
[MyDate] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO

2. I wrote a little bit of code to quickly insert a few records into the table:

declare @myCounter as int
set @myCounter = 0
WHILE @myCounter < 10
Begin
Insert Into [testIdentity] (MyDate)
Values (DateAdd(Day, @myCounter, GetDate()))
--print DateAdd(Day, @myCounter, GetDate())
set @myCounter = @myCounter + 1
If @myCounter > 10
BREAK
Else
CONTINUE
End 

3. Next, I restarted the SQL Server service for the instance.

4. I ran the above code again and looked at the results:

Right after the restart, SQL Server decided to skip some 10k numbers. The big problem with this is after the first time through this the problem appears intermittently. If you need to keep track of all records then this little feature bug is something you never want to see. It looks like you've deleted a ton of records.

Solution: Add a startup parameter under Configuration Manager.

1. Open SQL Server Configuration Manager
2. Right click the Instance and select Properties (not the Agent service)
3. Click the Startup Parameters tab
4. Under the Specify a startup parameter field enter: -t272
5. Restart the service

eol

Force a Full Backup in BizTalk Server

To force a full backup perform the following procedure.

Prerequisites
You must use an account that is a member of the SQL Server sysadmin role to perform this procedure.

Procedure
1. Launch SQL Server Management Studio
2. Connect to the server housing the BizTalkMgmtDb
3a. Run the stored procedure BizTalkMgmtDb.dbo.sp_ForceFullbackup
4a. Execute the job Backup BizTalk Server

Alternatively you can update the adm_BackupSettings table manually to force a full backup without executing step 3 above.

3b. Update adm_BackupSettings Set ForceFull = 1
4b. Execute the job Backup BizTalk Server

SQL Joins

I saw this graphic on Google+. It was too good to pass up so I'm re-posting it here.