Wednesday, October 30, 2013

SQL Server 2008 R2 fixes for non-yielding scheduler and such

 This ain't gonna be entertaining, and for most not very enlightening, either.  I just came across this list in my stuff and didn't want to lose it again :).

Non-yielding scheduler error or error 8623; query with large IN clause
http://support.microsoft.com/kb/982376
2008 R2 CU3 10.50.1734.0

non-yielding scheduler error; complex view with large number of nested views or tables
http://support.microsoft.com/kb/2306162
2008 R2 CU5 10.50.1753.0

Non-yielding Scheduler error; CONTAINSTABLE with many OR/AND predicates
http://support.microsoft.com/kb/2344600
2008 R2 CU5 10.50.1753.0

Non-yielding Scheduler error after Microsoft SQL Server Native Client 10.0 application connection
http://support.microsoft.com/kb/2491214
2008 R2 CU6 10.50.1765.0

Non-yielding scheduler error; Table valued parameter + SQL Profiler or SQL Server Extended Events
http://support.microsoft.com/kb/2520808
2008 R2 CU7 10.50.1777.0

Non-yielding Resource Monitor
http://support.microsoft.com/kb/2216485
2008 R2 SP1 10.50.2500.0

Non-yielding Scheduler error; CHARINDEX function
http://support.microsoft.com/kb/2633357
2008 R2 SP1 CU5 10.50.2806.0

Non-yielding Scheduler; profiling session (to calculate cost of object cache) not closed correctly
http://support.microsoft.com/kb/2699013
2008 R2 CU 14 10.50.1817.0, SP1 CU7 10.50.2817.0

Non-yielding IO Completion Listener or IOCP Listener; Winsock behavior when 2398202 and 2491214 are installed
http://support.microsoft.com/kb/2711549
2008 R2 SP1 CU7 10.50.2817.0

Thursday, October 17, 2013

SQL Server 2012 - 8015 is still a startup trace flag but not 8048 or 2335

My @@version:
Microsoft SQL Server 2012 (SP1) - 11.0.3368.0 (X64)
    May 22 2013 17:10:44
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

I've done a lot of work over the last year with trace flags 8015 and 8048.  Recently just started to work with trace flag 2335.  In various locations all three are documented as startup trace flags.  Startup trace flags can only be activated/deactivated at SQL Server startup.  Other trace flags can be set dynamically at the global, session, or query level.

Interestingly, trace flag 2335 was initially documented as a startup trace flag.
http://support.microsoft.com/kb/2413549

But, in the KB article that documented the querytraceon function, it specifically mentions support for trace flag 2335.

http://support.microsoft.com/kb/2801413

If a call to dbcc traceon is made in an inappropriate context for a given trace flag, (such as trying to enable a startup trace flag after startup), the following error message is returned. 







Ignoring trace flag <n>. It is either an invalid trace flag or a trace flag that can only be specified during server startup.

So... of the trace flags I am most concerned with (8048, 8015, and 2335) - which ones still register the error indicating that the must be specified at startup?

Only 8015.  Looks like trace flags 8048 and 2335 no longer require activation at SQL Server startup - if they ever did. 




Wednesday, October 16, 2013

Trace Flag 2335 and SQL Server Crash

**Update by SQL.sasquatch 20131017**
We were not able to reproduce the crash.  This was almost certainly some condition that we teased out unrelated to trace flag 2335 itself.
**End update**

The Knowledge Base article that discusses trace flag 2335 indicates it applies to SQL Server 2005 through SQL Server 2008 R2.

Some performance testing showed that this trace flag can still have a significant effect on SQL Server 2012.

At 80 concurrent queries (the baseline workflow), the pending memory grants dropped and query concurrency was higher.  That was exactly what was hoped for.  Because there was still CPU avialble, the test was repeated with 100 concurrent queries.  During that test, SQL Server crashed with the following dump.  Not sure why yet.  Hopefully not because of the trace flag - it was exciting to see a way to increase the query concurrency for this workload without changing the maximum query memory grant in Resource Governor.

2013-10-16 09:11:58.12 spid69      ***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\LOG\SQLDump0001.txt
2013-10-16 09:11:58.12 spid69      * *******************************************************************************
2013-10-16 09:11:58.12 spid69      *
2013-10-16 09:11:58.12 spid69      * BEGIN STACK DUMP:
2013-10-16 09:11:58.12 spid69      *   10/16/13 09:11:58 spid 3240
2013-10-16 09:11:58.12 spid69      *
2013-10-16 09:11:58.12 spid69      * ex_terminator - Last chance exception handling
2013-10-16 09:11:58.12 spid69      *
2013-10-16 09:11:58.12 spid69      * Input Buffer 510 bytes -
2013-10-16 09:11:58.12 spid69      *   SELECT "DIM1"."COL1", "DIM2"."COL2", "F
2013-10-16 09:11:58.12 spid69      *  ACT1"."COL1", "FACT1"."COL2", "FACT1"."C
2013-10-16 09:11:58.12 spid69      *  OL3", "FACT1"."COL4", "FACT1"."COL
2013-10-16 09:11:58.12 spid69      *  5", "FACT1"."COL6", "FACT1"."COL7
2013-10-16 09:11:58.12 spid69      *  ", "FACT2"."COL1", "DIM2"."COL3", /* handle missing
2013-10-16 09:11:58.12 spid69      *   entity name */    case when "FACT2"."COL1" is null     
2013-10-16 09:11:58.12 spid69      *                        then '*No Value'       when "DIM1"."CO
2013-10-16 09:11:58.12 spid69      *  L5" is null                       then '*Unknown Value'  
2013-10-16 09:11:58.12 spid69      *            when "DIM1"."COL1" is null             then
2013-10-16 09:11:58.12 spid69      *  '*Unnamed Entity'       else                                 "DI
2013-10-16 09:11:58.12 spid69      *  M1"."COL1"  end  , /* handle missing person name */    c
2013-10-16 09:11:58.12 spid69      *  ase when "FACT2"."VISIT_COL3" is null                            th
2013-10-16 09:11:58.12 spid69      *  en '*No Person'       when "DIM2"."COL3" is null           
2013-10-16 09:11:58.12 spid69      *             then '*Unknown Value'             when "DIM2"."COL
2013-10-16 09:11:58.12 spid69      *  6" is null             then '*Unknown Value'       else         
2013-10-16 09:11:58.12 spid69      *                         "DIM2"."COL2"  end    FROM   (("Test
2013-10-16 09:11:58.12 spid69      *  db"."dbo"."FACT1" "FACT1" LEFT OUTER JOIN "Testdb"."dbo"."F
2013-10-16 09:11:58.12 spid69      *  ACT2" "FACT2" ON "FACT1"."FACT2_UNIQ"="FACT2"."FACT2_UNIQ
2013-10-16 09:11:58.12 spid69      *  ") LEFT OUTER JOIN "Testdb"."dbo"."DIM1" "DIM1" ON "FACT2
2013-10-16 09:11:58.12 spid69      *  "."COL12"="DIM1"."COL12") LEFT OUTER JOIN "Te
2013-10-16 09:11:58.12 spid69      *  stdb"."dbo"."DIM2" "DIM2" ON "FACT2"."COL6"=
2013-10-16 09:11:58.12 spid69      *  "DIM2"."COL3" WHERE  ("FACT1"."COL7">={ts '2010-08
2013-10-16 09:11:58.12 spid69      *  -01 00:00:00'} AND "FACT1"."COL7"<{ts '2010-08-08 00:00:00'}
2013-10-16 09:11:58.12 spid69      *  ) AND "FACT2"."COL12" IS  NOT  NULL  AND ("FACT2"."COL1
2013-10-16 09:11:58.12 spid69      *  "=46002 OR "FACT2"."COL12"=46002) ORDER BY "DIM1"."
2013-10-16 09:11:58.12 spid69      *  COL1", "DIM2"."COL2"
2013-10-16 09:11:58.12 spid69      * 
2013-10-16 09:11:58.12 spid69      *
2013-10-16 09:11:58.12 spid69      *  MODULE                          BASE      END       SIZE
2013-10-16 09:11:58.12 spid69      * sqlservr                       00000000FF3B0000  00000000FF3EDFFF  0003e000
2013-10-16 09:11:58.12 spid69      * ntdll                          0000000076EA0000  0000000077048FFF  001a9000
2013-10-16 09:11:58.12 spid69      * kernel32                       0000000076D80000  0000000076E9EFFF  0011f000
2013-10-16 09:11:58.12 spid69      * KERNELBASE                     000007FEFD090000  000007FEFD0FAFFF  0006b000
2013-10-16 09:11:58.12 spid69      * ADVAPI32                       000007FEFDA10000  000007FEFDAEAFFF  000db000
2013-10-16 09:11:58.12 spid69      * msvcrt                         000007FEFDEE0000  000007FEFDF7EFFF  0009f000
2013-10-16 09:11:58.12 spid69      * sechost                        000007FEFD7C0000  000007FEFD7DEFFF  0001f000
2013-10-16 09:11:58.12 spid69      * RPCRT4                         000007FEFF080000  000007FEFF1ACFFF  0012d000
2013-10-16 09:11:58.12 spid69      * MSVCR100                       0000000074AE0000  0000000074BB1FFF  000d2000
2013-10-16 09:11:58.12 spid69      * MSVCP100                       0000000074A40000  0000000074AD7FFF  00098000
2013-10-16 09:11:58.12 spid69      * sqlos                          0000000074A20000  0000000074A26FFF  00007000
2013-10-16 09:11:58.12 spid69      * NETAPI32                       000007FEFACB0000  000007FEFACC5FFF  00016000
2013-10-16 09:11:58.12 spid69      * netutils                       000007FEFC670000  000007FEFC67BFFF  0000c000
2013-10-16 09:11:58.12 spid69      * srvcli                         000007FEFC890000  000007FEFC8B2FFF  00023000
2013-10-16 09:11:58.12 spid69      * wkscli                         000007FEFAC90000  000007FEFACA4FFF  00015000
2013-10-16 09:11:58.12 spid69      * pdh                            000007FEFA200000  000007FEFA24DFFF  0004e000
2013-10-16 09:11:58.12 spid69      * opends60                       0000000074A30000  0000000074A37FFF  00008000
2013-10-16 09:11:58.12 spid69      * sqlmin                         000007FEF8280000  000007FEFA1C8FFF  01f49000
2013-10-16 09:11:58.12 spid69      * USER32                         0000000076C80000  0000000076D79FFF  000fa000
2013-10-16 09:11:58.12 spid69      * GDI32                          000007FEFD750000  000007FEFD7B6FFF  00067000
2013-10-16 09:11:58.12 spid69      * LPK                            000007FEFD920000  000007FEFD92DFFF  0000e000
2013-10-16 09:11:58.12 spid69      * USP10                          000007FEFD1C0000  000007FEFD288FFF  000c9000
2013-10-16 09:11:58.12 spid69      * ole32                          000007FEFD380000  000007FEFD582FFF  00203000
2013-10-16 09:11:58.12 spid69      * OLEAUT32                       000007FEFD930000  000007FEFDA06FFF  000d7000
2013-10-16 09:11:58.12 spid69      * CRYPT32                        000007FEFCEE0000  000007FEFD046FFF  00167000
2013-10-16 09:11:58.12 spid69      * MSASN1                         000007FEFCE90000  000007FEFCE9EFFF  0000f000
2013-10-16 09:11:58.12 spid69      * Secur32                        000007FEFC9C0000  000007FEFC9CAFFF  0000b000
2013-10-16 09:11:58.12 spid69      * SSPICLI                        000007FEFCC50000  000007FEFCC74FFF  00025000
2013-10-16 09:11:58.12 spid69      * PSAPI                          0000000077070000  0000000077076FFF  00007000
2013-10-16 09:11:58.12 spid69      * WS2_32                         000007FEFD290000  000007FEFD2DCFFF  0004d000
2013-10-16 09:11:58.12 spid69      * NSI                            000007FEFDED0000  000007FEFDED7FFF  00008000
2013-10-16 09:11:58.12 spid69      * sqlTsEs                        000007FEF79F0000  000007FEF8276FFF  00887000
2013-10-16 09:11:58.12 spid69      * sqldk                          000007FEF70E0000  000007FEF755CFFF  0047d000
2013-10-16 09:11:58.12 spid69      * WINMM                          000007FEF7560000  000007FEF759AFFF  0003b000
2013-10-16 09:11:58.12 spid69      * sqllang                        000007FEE5330000  000007FEE73BEFFF  0208f000
2013-10-16 09:11:58.12 spid69      * USERENV                        000007FEFC1E0000  000007FEFC1FDFFF  0001e000
2013-10-16 09:11:58.12 spid69      * profapi                        000007FEFCDF0000  000007FEFCDFEFFF  0000f000
2013-10-16 09:11:58.12 spid69      * LOGONCLI                       000007FEFC680000  000007FEFC6AFFFF  00030000
2013-10-16 09:11:58.12 spid69      * SAMCLI                         000007FEFA360000  000007FEFA373FFF  00014000
2013-10-16 09:11:58.12 spid69      * AUTHZ                          000007FEFC830000  000007FEFC85EFFF  0002f000
2013-10-16 09:11:58.12 spid69      * WINTRUST                       000007FEFCEA0000  000007FEFCED9FFF  0003a000
2013-10-16 09:11:58.12 spid69      * IMM32                          000007FEFD720000  000007FEFD74DFFF  0002e000
2013-10-16 09:11:58.12 spid69      * MSCTF                          000007FEFD590000  000007FEFD698FFF  00109000
2013-10-16 09:11:58.12 spid69      * CRYPTBASE                      000007FEFCCE0000  000007FEFCCEEFFF  0000f000
2013-10-16 09:11:58.12 spid69      * instapi110                     000007FEFA710000  000007FEFA71DFFF  0000e000
2013-10-16 09:11:58.12 spid69      * cscapi                         000007FEFA6F0000  000007FEFA6FEFFF  0000f000
2013-10-16 09:11:58.12 spid69      * sqlevn70                       0000000074310000  0000000074589FFF  0027a000
2013-10-16 09:11:58.12 spid69      * CRYPTSP                        000007FEFC870000  000007FEFC886FFF  00017000
2013-10-16 09:11:58.12 spid69      * rsaenh                         000007FEFC190000  000007FEFC1D6FFF  00047000
2013-10-16 09:11:58.12 spid69      * imagehlp                       000007FEFD860000  000007FEFD876FFF  00017000
2013-10-16 09:11:58.12 spid69      * ncrypt                         000007FEFC7E0000  000007FEFC82DFFF  0004e000
2013-10-16 09:11:58.12 spid69      * bcrypt                         000007FEFC7B0000  000007FEFC7D1FFF  00022000
2013-10-16 09:11:58.12 spid69      * bcryptprimitives               000007FEFC2E0000  000007FEFC32BFFF  0004c000
2013-10-16 09:11:58.12 spid69      * GPAPI                          000007FEFC100000  000007FEFC11AFFF  0001b000
2013-10-16 09:11:58.12 spid69      * cryptnet                       000007FEF51E0000  000007FEF5205FFF  00026000
2013-10-16 09:11:58.12 spid69      * WLDAP32                        000007FEFDF80000  000007FEFDFD1FFF  00052000
2013-10-16 09:11:58.12 spid69      * SHLWAPI                        000007FEFD6A0000  000007FEFD710FFF  00071000
2013-10-16 09:11:58.12 spid69      * VERSION                        000007FEFBF00000  000007FEFBF0BFFF  0000c000
2013-10-16 09:11:58.12 spid69      * credssp                        000007FEFC3E0000  000007FEFC3E9FFF  0000a000
2013-10-16 09:11:58.12 spid69      * msv1_0                         000007FEFC540000  000007FEFC590FFF  00051000
2013-10-16 09:11:58.12 spid69      * cryptdll                       000007FEFC9A0000  000007FEFC9B3FFF  00014000
2013-10-16 09:11:58.12 spid69      * kerberos                       000007FEFC5A0000  000007FEFC653FFF  000b4000
2013-10-16 09:11:58.12 spid69      * schannel                       000007FEFC380000  000007FEFC3D6FFF  00057000
2013-10-16 09:11:58.12 spid69      * MSCOREE                        000007FEF41E0000  000007FEF424EFFF  0006f000
2013-10-16 09:11:58.12 spid69      * mscoreei                       000007FEF4140000  000007FEF41D8FFF  00099000
2013-10-16 09:11:58.12 spid69      * CLUSAPI                        000007FEF47C0000  000007FEF480FFFF  00050000
2013-10-16 09:11:58.12 spid69      * RESUTILS                       000007FEF4780000  000007FEF4798FFF  00019000
2013-10-16 09:11:58.12 spid69      * security                       00000000745A0000  00000000745A2FFF  00003000
2013-10-16 09:11:58.12 spid69      * CLBCatQ                        000007FEFD880000  000007FEFD918FFF  00099000
2013-10-16 09:11:58.12 spid69      * sqlncli11                      0000000070C90000  0000000070FE2FFF  00353000
2013-10-16 09:11:58.12 spid69      * COMCTL32                       000007FEF4060000  000007FEF40FFFFF  000a0000
2013-10-16 09:11:58.12 spid69      * COMDLG32                       000007FEFD2E0000  000007FEFD376FFF  00097000
2013-10-16 09:11:58.12 spid69      * SHELL32                        000007FEFE1C0000  000007FEFEF47FFF  00d88000
2013-10-16 09:11:58.12 spid69      * SQLNCLIR11                     0000000074690000  00000000746C7FFF  00038000
2013-10-16 09:11:58.12 spid69      * clr                            000007FEF6780000  000007FEF70DDFFF  0095e000
2013-10-16 09:11:58.13 spid69      * MSVCR110_CLR0400               000007FEF3620000  000007FEF36F1FFF  000d2000
2013-10-16 09:11:58.13 spid69      * BatchParser                    00000000742E0000  000000007430AFFF  0002b000
2013-10-16 09:11:58.13 spid69      * mscorlib.ni                    000007FEE3DB0000  000007FEE532CFFF  0157d000
2013-10-16 09:11:58.13 spid69      * SqlAccess                      0000000074270000  00000000742DCFFF  0006d000
2013-10-16 09:11:58.13 spid69      * nlssorting                     000007FEF79D0000  000007FEF79E5FFF  00016000
2013-10-16 09:11:58.13 spid69      * clrjit                         000007FEF7890000  000007FEF79C0FFF  00131000
2013-10-16 09:11:58.13 spid69      * mswsock                        000007FEFC6B0000  000007FEFC704FFF  00055000
2013-10-16 09:11:58.13 spid69      * wship6                         000007FEFC730000  000007FEFC736FFF  00007000
2013-10-16 09:11:58.13 spid69      * wshtcpip                       000007FEFC000000  000007FEFC006FFF  00007000
2013-10-16 09:11:58.13 spid69      * ntmarta                        000007FEFBF10000  000007FEFBF3CFFF  0002d000
2013-10-16 09:11:58.13 spid69      * ntdsapi                        000007FEFA6A0000  000007FEFA6C6FFF  00027000
2013-10-16 09:11:58.13 spid69      * DNSAPI                         000007FEFC430000  000007FEFC48AFFF  0005b000
2013-10-16 09:11:58.13 spid69      * IPHLPAPI                       000007FEFB370000  000007FEFB396FFF  00027000
2013-10-16 09:11:58.13 spid69      * WINNSI                         000007FEFB230000  000007FEFB23AFFF  0000b000
2013-10-16 09:11:58.13 spid69      * rasadhlp                       000007FEFA670000  000007FEFA677FFF  00008000
2013-10-16 09:11:58.13 spid69      * fwpuclnt                       000007FEFB170000  000007FEFB1C2FFF  00053000
2013-10-16 09:11:58.13 spid69      * SAMLIB                         000007FEFA320000  000007FEFA33CFFF  0001d000
2013-10-16 09:11:58.13 spid69      * dbghelp                        0000000029290000  0000000029425FFF  00196000
2013-10-16 09:11:58.13 spid69      *
2013-10-16 09:11:58.13 spid69      *     P1Home: 00000000CD3EEB90:  0000000000000000  08C67AC316FA6237  000007FEE5A87EF0  0050005C003A0043  00720067006F0072  00460020006D0061 
2013-10-16 09:11:58.13 spid69      *     P2Home: 00000000003C0000:  0000000000000000  01007A83F5FA62D4  00000000FFEEFFEE  000000000D840018  00000000003C0128  00000000003C0000 
2013-10-16 09:11:58.13 spid69      *     P3Home: 0000000076FB5400:  006C0064002E006C  000000000000006C  0000000076F66FB0  0000000076F70590  0000000076F85C00  0000000000000000 
2013-10-16 09:11:58.13 spid69      *     P4Home: 0000000500000143:  7878787878207878  7878207878787878  7878787878787878  5245544E49787878  4445202C45434146  43534E415254534D 
2013-10-16 09:11:58.13 spid69      *     P5Home: 0000000554396170:  480143013E013A01  5C01570152014D01  70016B0166016101  84017F017A017501  9C01960190018A01  B401AE01A801A201 
2013-10-16 09:11:58.13 spid69      *     P6Home: 000007FEF837F3D0:  000007FEF844BC90  000007FEF83F1C70  000007FEF83E2F50  000007FEF8CD5F70  000007FEF8CD60C0  000007FEF83B0A90 
2013-10-16 09:11:58.13 spid69      * ContextFlags: 000000000010000F:  0000000000006400  0000000000002000  0000010000001400  0000340000000200  000001000000D400  0000000000000000 
2013-10-16 09:11:58.13 spid69      *      MxCsr: 0000000000001FA0: 
2013-10-16 09:11:58.13 spid69      *      SegCs: 0000000000000033: 
2013-10-16 09:11:58.13 spid69      *      SegDs: 000000000000002B: 
2013-10-16 09:11:58.13 spid69      *      SegEs: 000000000000002B: 
2013-10-16 09:11:58.13 spid69      *      SegFs: 0000000000000053: 
2013-10-16 09:11:58.13 spid69      *      SegGs: 000000000000002B: 
2013-10-16 09:11:58.13 spid69      *      SegSs: 000000000000002B: 
2013-10-16 09:11:58.13 spid69      *     EFlags: 0000000000000202: 
2013-10-16 09:11:58.13 spid69      *        Rax: 000000009A7EB17A:  0000000000000000  0000000000000000  0000000000000000  0000000000000000  0000000000000000  0000000000000000 
2013-10-16 09:11:58.13 spid69      *        Rcx: 00000000EC5B8070:  00000000CD3EEB90  00000000003C0000  0000000076FB5400  0000000500000143  0000000554396170  000007FEF837F3D0 
2013-10-16 09:11:58.13 spid69      *        Rdx: 0000000000000000: 
2013-10-16 09:11:58.13 spid69      *        Rbx: 0000000000000000: 
2013-10-16 09:11:58.13 spid69      *        Rsp: 00000000EC5B8680:  0000000000000000  00000007F4BD2160  00000000EC5B8900  0000000400000002  00000000000042AC  0000000000000000 
2013-10-16 09:11:58.13 spid69      *        Rbp: 000007FEF71AF660:  0074005F00780065  0069006D00720065  006F00740061006E  0020002D00200072  007400730061004C  0061006800630020 
2013-10-16 09:11:58.13 spid69      *        Rsi: 00000007F4BD2160:  000000096A72CA90  00000007F4BD2840  000000000000000F  0000000000000000  00000007F4BD2180  00000007F4BD2180 
2013-10-16 09:11:58.13 spid69      *        Rdi: 00000000EC5B8900:  00000000CD3EEBA0  00000003A661C290  0000000000000000  0000000700000000  0000000000000000  0000000000000000 
2013-10-16 09:11:58.13 spid69      *         R8: 0000000000000000: 
2013-10-16 09:11:58.13 spid69      *         R9: 0000000000000000: 
2013-10-16 09:11:58.13 spid69      *        R10: 0000000000000000: 
2013-10-16 09:11:58.13 spid69      *        R11: 00000000EC5B9380:  00000000CD3EEB90  00000000003C0000  0000000076FB5400  0000000500000143  0000000554396170  000007FEF837F3D0 
2013-10-16 09:11:58.13 spid69      *        R12: 0000000000000001: 
2013-10-16 09:11:58.13 spid69      *        R13: 0000000000000001: 
2013-10-16 09:11:58.13 spid69      *        R14: 0000000000000000: 
2013-10-16 09:11:58.13 spid69      *        R15: 000000000000002F: 
2013-10-16 09:11:58.13 spid69      *        Rip: 000007FEFD09A49D:  C3000000C8C48148  003B830874F68548  0001BD1689660376  02D6840FFD3B0000  F3840F02FF830000  870F07FF83000002 
2013-10-16 09:11:58.13 spid69      * *******************************************************************************
2013-10-16 09:11:58.13 spid69      * -------------------------------------------------------------------------------
2013-10-16 09:11:58.13 spid69      * Short Stack Dump
2013-10-16 09:11:58.19 spid69      000007FEFD09A49D Module(KERNELBASE+000000000000A49D)
2013-10-16 09:11:58.19 spid69      000007FEE64B81BE Module(sqllang+00000000011881BE)
2013-10-16 09:11:58.19 spid69      000007FEE64BD0C3 Module(sqllang+000000000118D0C3)
2013-10-16 09:11:58.19 spid69      000007FEE64BC8CA Module(sqllang+000000000118C8CA)
2013-10-16 09:11:58.19 spid69      000007FEF71AF527 Module(sqldk+00000000000CF527)
2013-10-16 09:11:58.20 spid69      000007FEF71AEE0D Module(sqldk+00000000000CEE0D)
2013-10-16 09:11:58.20 spid69      0000000074B3F769 Module(MSVCR100+000000000005F769)
2013-10-16 09:11:58.20 spid69      0000000074B40BCC Module(MSVCR100+0000000000060BCC)
2013-10-16 09:11:58.20 spid69      0000000074B41235 Module(MSVCR100+0000000000061235)
2013-10-16 09:11:58.20 spid69      0000000074B4144D Module(MSVCR100+000000000006144D)
2013-10-16 09:11:58.21 spid69      0000000074B3F66B Module(MSVCR100+000000000005F66B)
2013-10-16 09:11:58.22 spid69      000007FEF84223A2 Module(sqlmin+00000000001A23A2)
2013-10-16 09:11:58.22 spid69      0000000076EC9D0D Module(ntdll+0000000000029D0D)
2013-10-16 09:11:58.22 spid69      0000000076EB91AF Module(ntdll+00000000000191AF)
2013-10-16 09:11:58.22 spid69      0000000076EF1278 Module(ntdll+0000000000051278)
2013-10-16 09:11:58.22 spid69      000007FEF70EF65F Module(sqldk+000000000000F65F)
2013-10-16 09:11:58.22 spid69      000007FEF70EF5E1 Module(sqldk+000000000000F5E1)
2013-10-16 09:11:58.22 spid69      000007FEF70EF988 Module(sqldk+000000000000F988)
2013-10-16 09:11:58.22 spid69      000007FEF70EFBF0 Module(sqldk+000000000000FBF0)
2013-10-16 09:11:58.22 spid69      000007FEF7118A2C Module(sqldk+0000000000038A2C)
2013-10-16 09:11:58.22 spid69      000007FEF711891F Module(sqldk+000000000003891F)
2013-10-16 09:11:58.22 spid69      000007FEF93FB487 Module(sqlmin+000000000117B487)
2013-10-16 09:11:58.22 spid69      000007FEF93FB3D1 Module(sqlmin+000000000117B3D1)
2013-10-16 09:11:58.22 spid69      000007FEF93F9588 Module(sqlmin+0000000001179588)
2013-10-16 09:11:58.22 spid69      000007FEF856A6B0 Module(sqlmin+00000000002EA6B0)
2013-10-16 09:11:58.22 spid69      000007FEF8292A8E Module(sqlmin+0000000000012A8E)
2013-10-16 09:11:58.22 spid69      000007FEF8294376 Module(sqlmin+0000000000014376)
2013-10-16 09:11:58.22 spid69      000007FEF8294B18 Module(sqlmin+0000000000014B18)
2013-10-16 09:11:58.22 spid69      000007FEF82A2836 Module(sqlmin+0000000000022836)
2013-10-16 09:11:58.22 spid69      000007FEF82A2464 Module(sqlmin+0000000000022464)
2013-10-16 09:11:58.22 spid69      000007FEF82A224F Module(sqlmin+000000000002224F)
2013-10-16 09:11:58.22 spid69      000007FEF82CA9F6 Module(sqlmin+000000000004A9F6)
2013-10-16 09:11:58.22 spid69      000007FEF79F6FD7 Module(sqlTsEs+0000000000006FD7)
2013-10-16 09:11:58.22 spid69      000007FEF82CAA84 Module(sqlmin+000000000004AA84)
2013-10-16 09:11:58.22 spid69      000007FEF82CAC70 Module(sqlmin+000000000004AC70)
2013-10-16 09:11:58.22 spid69      000007FEF832B9D1 Module(sqlmin+00000000000AB9D1)
2013-10-16 09:11:58.22 spid69      000007FEF83B8126 Module(sqlmin+0000000000138126)
2013-10-16 09:11:58.22 spid69      000007FEF83B8BA5 Module(sqlmin+0000000000138BA5)
2013-10-16 09:11:58.23 spid69      000007FEF83B8B45 Module(sqlmin+0000000000138B45)
2013-10-16 09:11:58.23 spid69      000007FEF83B9BC1 Module(sqlmin+0000000000139BC1)
2013-10-16 09:11:58.23 spid69      000007FEF8475952 Module(sqlmin+00000000001F5952)
2013-10-16 09:11:58.23 spid69      000007FEF70F37E0 Module(sqldk+00000000000137E0)
2013-10-16 09:11:58.23 spid69      000007FEF70F3C84 Module(sqldk+0000000000013C84)
2013-10-16 09:11:58.23 spid69      000007FEF70F3967 Module(sqldk+0000000000013967)
2013-10-16 09:11:58.23 spid69      000007FEF711D63F Module(sqldk+000000000003D63F)
2013-10-16 09:11:58.23 spid69      000007FEF711E0B0 Module(sqldk+000000000003E0B0)
2013-10-16 09:11:58.23 spid69      000007FEF711F0BB Module(sqldk+000000000003F0BB)
2013-10-16 09:11:58.23 spid69      000007FEF711D898 Module(sqldk+000000000003D898)
2013-10-16 09:11:58.23 spid69      0000000076D9652D Module(kernel32+000000000001652D)
2013-10-16 09:11:58.23 spid69      0000000076ECC521 Module(ntdll+000000000002C521)
2013-10-16 09:11:58.25 spid69      Stack Signature for the dump is 0x000000007F859E47
2013-10-16 09:12:00.21 spid69      External dump process return code 0x20000001.
External dump process returned no errors.

Tuesday, October 1, 2013

SQL Server: How much stuff in this database is compressed?



One of the best ways to reduce IO waits is to do less IO :).  Compression/decompression comes at a CPU cost*, but if a system has CPU to spare while experiencing a storage bandwidth, latency, or capacity issue then compression should be one of the first considerations.

This is just a starting point for evaluating the use of compression in the database.  Prolly also makes sense to look for compression candidates based on size (might not make sense to compress a table that will never consume more than 8 pages) and qualification for online index rebuild (some data types cannot be rebuilt online).    
 
SELECT
   pt.data_compression_desc,
   COUNT(pt.data_compression_desc) AS [partition_count],
   SUM(ptstats.used_page_count) * 8 AS [UsedPagesKB],
   SUM(ptstats.reserved_page_count) * 8 [AS ReservedPageKB]
FROM
   sys.dm_db_partition_stats AS ptstats
JOIN
   sys.partitions as pt
   ON
   ptstats.partition_id = pt.partition_id
GROUP BY
   pt.data_compression_desc


*Lets talk a little about the CPU cost of compression.  First of all - don't just compare peak CPU busy before and after implementing compression :)  Got to also consider how long a particular workload takes to complete.  If the workload took 4 hours to complete before compression at an average of 40% CPU utilized, and it now takes 2 hours at 85% CPU utilization... its not fair to say that compression had a 45% CPU utilization overhead.  If the workload completed in half the time (2 hours) at twice the CPU utilization (80%), the compression would have had no net overhead.  As it is, in half the time at more than double the CPU utilization it is about a 5% overhead.

Its best if in the baseline, you can actually sum the worker time, physical IO, and logical IO across all queries in the workload.  Compare that to worker time, physical IO, and logical IO after implementing compression.  Sure, go ahead and throw in workload elapsed time and average CPU utilization :)  

Its important to note that the net CPU overhead of compression may be LESS than expected based on the amount of CPU ticks involved in compress/uncompress operations.  Here's why: 
1. You'll likely do less total physical IO.  Transfering physical IO contents into the bpool takes a small amount of CPU.
2. The database pages are compressed in memory - so a given query of page compressed contents (assuming reasonable compression rate) should perform fewer logical reads than the same query against uncompressed data.  Fewer logical reads means less total CPU consumption.

So, compress/uncompress gobbles up some CPU.  But fewer physical and logical IOs will give some back.  And most systems I observe have a lot more CPU resources to spare than IO resources.