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
Wednesday, October 30, 2013
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.
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.
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.
*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.
Subscribe to:
Posts (Atom)