При появлении тормозов при работе с SQL возникает резонный вопрос, а что-же именно тормозит. Привожу скрипт который поможет найти слабое место. Его нужно выполнить в Мастерс.

CREATE proc get_waitstats
AS
 
SET nocount ON
 
DECLARE @now datetime,@totalwait NUMERIC(20,1)
   ,@endtime datetime,@begintime datetime
   ,@hr INT,@MIN INT,@sec INT
 
SELECT  @now=MAX(now),@begintime=MIN(now),@endtime=MAX(now)
FROM waitstats WHERE [wait TYPE] = 'Total'
 
-- Subtract waitfor, sleep, and resource_queue from total.
SELECT @totalwait = SUM([wait TIME]) + 1 FROM waitstats WHERE [wait TYPE] NOT IN ('WAITFOR','SLEEP','RESOURCE_QUEUE', 'Total',
  '***total***') AND now = @now
 
DELETE waitstats WHERE [wait TYPE] = '***total***' AND now = @now INSERT INTO waitstats SELECT '***total***',0,@totalwait,@totalwait,@now
 
SELECT [wait TYPE],[wait TIME],percentage=CAST (100*[wait TIME]/@totalwait
  AS NUMERIC(20,1))
FROM waitstats
WHERE [wait TYPE] NOT IN ('WAITFOR','SLEEP','RESOURCE_QUEUE','Total')
AND now = @now
ORDER BY percentage DESC
 
GO
 
CREATE proc track_waitstats (@num_samples INT=10,@delaynum INT=1,@delaytype
  nvarchar(10)='minutes')
AS
 
SET nocount ON
IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = 'waitstats')
   CREATE TABLE waitstats ([wait TYPE] VARCHAR(80),
      requests NUMERIC(20,1),
	  [wait TIME] NUMERIC (20,1),
	  [signal wait TIME] NUMERIC(20,1),
	  now datetime DEFAULT getdate())
ELSE    TRUNCATE TABLE waitstats
dbcc sqlperf (waitstats,clear)                            -- Clear out waitstats.
DECLARE @i INT,@delay VARCHAR(8),@dt VARCHAR(3),@now datetime,
   @totalwait NUMERIC(20,1),@endtime datetime,@begintime datetime,@hr INT,
   @MIN INT,@sec INT
SELECT @i = 1
SELECT @dt = CASE LOWER(@delaytype)
   WHEN 'minutes' THEN 'm'
   WHEN 'minute' THEN 'm'
   WHEN 'min' THEN 'm'
   WHEN 'mm' THEN 'm'
   WHEN 'mi' THEN 'm'
   WHEN 'm' THEN 'm'
   WHEN 'seconds' THEN 's'
   WHEN 'second' THEN 's'
   WHEN 'sec' THEN 's'
   WHEN 'ss' THEN 's'
   WHEN 's' THEN 's'
   ELSE @delaytype
END
IF @dt NOT IN ('s','m')
BEGIN
   PRINT 'please supply delay type e.g. seconds or minutes'
   RETURN
END
IF @dt = 's'
BEGIN
   SELECT @sec = @delaynum % 60
   SELECT @MIN = CAST((@delaynum / 60) AS INT)
   SELECT @hr = CAST((@MIN / 60) AS INT)
   SELECT @MIN = @MIN % 60
END
IF @dt = 'm'
BEGIN
   SELECT @sec = 0
   SELECT @MIN = @delaynum % 60
   SELECT @hr = CAST((@delaynum / 60) AS INT) END SELECT @delay= RIGHT('0'+ CONVERT(VARCHAR(2),@hr),2) + ':' +
   + RIGHT('0'+CONVERT(VARCHAR(2),@MIN),2) + ':' +
   + RIGHT('0'+CONVERT(VARCHAR(2),@sec),2)
IF @hr > 23 OR @MIN > 59 OR @sec > 59
BEGIN
   SELECT 'hh:mm:ss delay time cannot > 23:59:59'
   SELECT 'delay interval and type: ' + CONVERT (VARCHAR(10),@delaynum) + ',' + 
     @delaytype + ' converts to ' + @delay
   RETURN
END
WHILE (@i <= @num_samples)
BEGIN
             INSERT INTO waitstats ([wait TYPE], requests, [wait TIME],[signal wait TIME])
   EXEC ('dbcc sqlperf(waitstats)')
   SELECT @i = @i + 1
   waitfor delay @delay
END
-- Create report.
EXECUTE get_waitstats
 
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

EXEC track_waitstats 300, 1

300 - минуты сколько мерить 1 - интервал в минутах

Средство от Microsoft

Альтернативный источник