Intriguing SQL Server Performance Tuning Issue

Question

I've been working on a stored procedure performance issue for over a week now and is related to my other post on Stackoverflow here . Let me give you some background information.

We have a nightly process that starts and starts with a stored procedure that calls many many other stored procedures. Many called stored procedures call others, etc. I looked at some of the processes invoked and there are all sorts of frightening complex things, such as processing XML strings, unnecessary overuse of cursors, NOLOCK hints overused, rare use of complex processing, etc. - the list goes on, it's pretty awful.

This overnight process in our production environment takes an average of 1:15 to run. It sometimes takes 2 hours, which is unacceptable. I created a test environment on identical hardware for production and runs proc. It took 45 minutes when I started it. If I restored the database to the same point and started it again, it will take longer: indeed, if I repeat this action several times (recovery and repeated execution), the process takes a longer time until it becomes a plateau of about 2 hours. It really puzzles me, because every time I restore the database to the same point. There are no other user databases on the server.

I was thinking about two areas of investigation:

• Query Plans and Parameter Changes
• Tempdb

As a test, I restarted SQL Server to clear both cache and tempdb and restart proc with the same database recovery. The process took 45 minutes. I repeated this several times to make sure it was repeatable - again it took 45 minutes each time. Then I proceeded with a few tests to try to isolate the surprisingly longer run time when SQL Server does not restart:

• Run the initial stored procedure WITH RECOMPILE
• Before starting a procedure, run the DBCC FREEPROCCACHE command to clear the procedure cache
• Before starting the procedure, execute CHECKPOINT and then DBCC DROPCLEANBUFFERS to make sure that the cache is empty and clean.

• The following script is executed so that all stored procedures are marked for recompilation:

  DECLARE @proc_schema SYSNAME
  DECLARE @proc_name SYSNAME
  
  DECLARE prcCsr CURSOR local
      FOR SELECT  specific_schema,
                  specific_name
          FROM    INFORMATION_SCHEMA.routines
          WHERE   routine_type = 'PROCEDURE'
  
  OPEN prcCsr
  
  FETCH NEXT FROM prcCsr INTO @proc_schema, @proc_name
  
  DECLARE @stmt NVARCHAR(MAX)
  WHILE @@FETCH_STATUS = 0
      BEGIN
          SET @stmt = N'exec sp_recompile ''[' + @proc_schema + '].['
              + @proc_name + ']'''
  --        PRINT @stmt   -- DEBUG
          EXEC ( @stmt
              )
  
          FETCH NEXT FROM prcCsr INTO @proc_schema, @proc_name
      END
  

. , . - , 3-6 , , . , , proc , ?

tempdb , stackoverflow, , , ( , 24 ).

. 64- SQL Server 2005 3 (SP3) Windows 2003 R2 Ent..

, .

Answer 1

, , , XML-. SQL Server , , .

, XML-, :

EXEC sp_xml_preparedocument @idoc OUTPUT, @strXML

, :

EXEC sp_xml_removedocument @idoc

XML- - COM-, SQL Server. SQL Server . 5 , XML- .

Answer 2

, , , , - tempdb. :

• , , tempdb? ?
• "" ? ?
• , ?
• tempdb ?

, . , Microsoft SQL Technet - . ( , MS. , , . MS, !)

, . , temp table , . , , SQL Profiler . , , ( , - ). :

• SP: , , .
• SQL Profiler ; , , , , Profiler, , . procs, temp .
• , SP: StmtStarting . , 2 +/- , 100 . (: , , .) , , / temp, .

Answer 3

-

, 3-6 , , .

, , 30hrs +, , 2 ! , , OLTP:

• , , SP: StmtCompleted SQL: StmtCompleted. ( > 10 - ), , .

• //, . , :

  (A) / ( )

  (B) , .

(A) . . Dan Tow book "SQL Tuning" , .

(B) . ? , , ? , 10 000 , ?

, :

(C) 15 000 SQL, . , -, .

(D) - TempDB - . , , !

, .

Answer 4

:

• : [A] [B]. [A] - , [B] - .
• [A]
• [B] [A]

. , - (tempdb, , - ..). , (, ..).

!

Answer 5

script , :

select sum(single_pages_kb) as sum_bp_kb
  , sum(multi_pages_kb) as sum_va_kb
  , type
from sys.dm_os_memory_clerks
group by type
having sum(single_pages_kb+multi_pages_kb) > 16
order by sum(single_pages_kb+multi_pages_kb) desc

select sum(total_pages), type_desc
from tempdb.sys.allocation_units
group by type_desc;

select * from sys.dm_os_performance_counters
where counter_name in (
  'Log Truncations'
  ,'Log Growths'
  ,'Log Shrinks'
  ,'Data File(s) Size (KB)'
  ,'Log File(s) Size (KB)'
  ,'Active Temp Tables');

, - , , - .

Answer 6

, ?

, . ?

, , , , , .

, , :

• .
• , benificial.

Answer 7

-, -, , , . . , , , .

Answer 8

log_table , ..., , , ( , ).

- proc.

Answer 9

, . ( )

/ . / ? , , . Tempdb , , .

(#mytable) , , ( ), .

, COMMIT/ROLLBACK tempdb, . - syscolumns, sysindexes .., - , exeuctions .

, , - .

( :), SELECT INTO - .

(## myglobaltable ), ( , ), , , ( ). , , ?

( , ?)

Microsoft SQL Server Book Online, " . , . , ".

, temp , .

Answer 10

, , , .

sys.dm_os_wait_stats . 2 ( ) , - WAIT . sys.dm_os_wait_stats Excel VLOOKUP() . . , SQL Server ? SQL Server "" sys.dm_os_wait_stats!

, , - , , sproc (s) INFORMATION_SCHEMA.routines... 2 :

1) "top N" SQL SELECT, "-100" "-1000" ( ), , ... .. , , sproc.

2) , " N", (- ) , .

, .

, .

: Btw, SQL2008 , "" sys.dm_os_wait_stats. SQL2005 Excel script.

Answer 11

:

• , SQL Server , , .. SQL, , .
• , : ( , ). , , .

Answer 12

-, . , . .

, , . . 96 . - 50 . , , ( ).

- node , - , , .

1100 temp tables - , . sp_recompile proc . . , , SQL Server. , , - , . , , . , , .

.