Home Dashboard Directory Help
Search

SQL Server 2014 CTP2 ColumnStore Index concurrent queries drive CPU Utilization high by Eddie_liu


Status: 

Active


1
0
Sign in
to vote
Type: Bug
ID: 812009
Opened: 12/19/2013 11:23:24 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

Hi,
We have a customer, who uses SQL Server 2014 CTP2 to test the compatibility with their application. During the testing, they noticed that CPU utilization is driving high to 100%. Below are more detailed information:
1.    System is quite powerful, with 80 logical processors, 256GB memory. All of them are at 100% utilization.
2.    The query is quite simple. If we run below query individually, the query duration time is in microseconds, and the CPU utilization is extremely low. (Note, the clustered columnstore index is used)
3.    However, if we run above same query in parallel (with visual studio stress testing), it needs 8 seconds to finish each query, and the system’s overall CPU utilization is high to 100%. (With 80 logical processors).
4.    We captured two mini-dumps during high CPU utilization time to check the behavior of SQL Server, and noticed that there are 823 threads with below call stack.

2043 Id: 4050.1784 Suspend: 1 Teb: 000007f7`690d2000 Unfrozen
Child-SP         RetAddr         Call Site
00000074`cdbfd648 000007fc`ed377632 ntdll!ZwSignalAndWaitForSingleObject+0xa [o:\w8rtm.obj.amd64fre\minkernel\ntdll\daytona\objfre\amd64\usrstubs.asm @ 3348]
00000074`cdbfd650 000007fc`df413a05 KERNELBASE!SignalObjectAndWait+0x82 [d:\w8rtm\minkernel\kernelbase\synch.c @ 2133]
(Inline Function) --------`-------- sqldk!SystemThread::SignalAndWait+0x2a [e:\sql12_main_t\sql\common\dk\sos\include\worker.inl @ 665]
00000074`cdbfd700 000007fc`df411abd sqldk!SOS_Scheduler::Switch+0xb5 [e:\sql12_main_t\sql\common\dk\sos\src\scheduler.cpp @ 3159]
00000074`cdbfdc80 000007fc`dabb10d9 sqldk!SOS_Scheduler::SuspendNonPreemptive+0xd2 [e:\sql12_main_t\sql\common\dk\sos\src\scheduler.cpp @ 1714]
00000074`cdbfdcc0 000007fc`dabd62c1 sqlmin!SOS_Task::Sleep+0x13c [e:\sql12_main_t\sql\common\dk\sos\include\sos.inl @ 5937]
(Inline Function) --------`-------- sqlmin!SOS_Task::OSYield+0x8ccb [e:\sql12_main_t\sql\common\dk\sos\include\sos.inl @ 5784]
00000074`cdbfdd20 000007fc`db713f1e sqlmin!YieldAndCheckForAbort+0xc3 [e:\sql12_main_t\sql\ntdbms\include\common\execaborthandling.h @ 263]
00000074`cdbfdd50 000007fc`db74b695 sqlmin!CBpContext::BpYield+0xe [e:\sql12_main_t\sql\ntdbms\query\qebp\bpctxt.cpp @ 311]
00000074`cdbfdd80 000007fc`db752cf4 sqlmin!CBpQScan::GetRootBatchData+0x25 [e:\sql12_main_t\sql\ntdbms\query\qebp\bpqscan.cpp @ 367]
00000074`cdbfddb0 000007fc`dabde8af sqlmin!CQScanBatchHelper::GetRow+0xc4 [e:\sql12_main_t\sql\ntdbms\query\qebp\qsbphlp.cpp @ 292]
00000074`cdbfddf0 000007fc`dabde7a1 sqlmin!CQScanStreamAggregateNew::GetRowHelper+0x1fc [e:\sql12_main_t\sql\ntdbms\query\qeexec\qsstragg.cpp @ 647]
00000074`cdbfde20 000007fc`dabdbfc5 sqlmin!CQScanStreamAggregateNew::GetCalculatedRow+0x21 [e:\sql12_main_t\sql\ntdbms\query\qeexec\qsstragg.cpp @ 463]
00000074`cdbfde50 000007fc`dad3f262 sqlmin!CQScanNew::OpenHelper+0x41 [e:\sql12_main_t\sql\ntdbms\query\qeexec\qscan.cpp @ 170]
00000074`cdbfde80 000007fc`dad15d92 sqlmin!CQScanXProducerNew::Open+0xc8 [e:\sql12_main_t\sql\ntdbms\query\qeexec\qsxchng.cpp @ 2742]
00000074`cdbfdeb0 000007fc`dad17338 sqlmin!FnProducerOpen+0x44 [e:\sql12_main_t\sql\ntdbms\query\qeexec\qsxchng.cpp @ 2027]
00000074`cdbfdef0 000007fc`dad16acf sqlmin!FnProducerThread+0x8c3 [e:\sql12_main_t\sql\ntdbms\query\qeexec\qsxchng.cpp @ 2262]
00000074`cdbfe250 000007fc`df414790 sqlmin!SubprocEntrypoint+0xa7f [e:\sql12_main_t\sql\ntdbms\storeng\dfs\process\subproc.cpp @ 444]
00000074`cdbfef80 000007fc`df414574 sqldk!SOS_Task::Param::Execute+0x21e [e:\sql12_main_t\sql\common\dk\sos\include\sos.inl @ 8726]
00000074`cdbff580 000007fc`df414376 sqldk!SOS_Scheduler::RunTask+0xa8 [e:\sql12_main_t\sql\common\dk\sos\src\scheduler.cpp @ 988]
00000074`cdbff5f0 000007fc`df42d8ef sqldk!SOS_Scheduler::ProcessTasks+0x279 [e:\sql12_main_t\sql\common\dk\sos\src\scheduler.cpp @ 864]
00000074`cdbff670 000007fc`df42dd30 sqldk!SchedulerManager::WorkerEntryPoint+0x24c [e:\sql12_main_t\sql\common\dk\sos\src\node.cpp @ 1809]
00000074`cdbff710 000007fc`df42dbf6 sqldk!SystemThread::RunWorker+0x8f [e:\sql12_main_t\sql\common\dk\sos\include\worker.inl @ 828]
00000074`cdbff740 000007fc`df42e2c8 sqldk!SystemThreadDispatcher::ProcessWorker+0x3ab [e:\sql12_main_t\sql\common\dk\sos\src\node.cpp @ 449]
00000074`cdbff7f0 000007fc`ed8b167e sqldk!SchedulerManager::ThreadEntryPoint+0x226 [e:\sql12_main_t\sql\common\dk\sos\src\node.cpp @ 2022]
00000074`cdbff890 000007fc`effcc3f1 kernel32!BaseThreadInitThunk+0x1a [d:\w8rtm\base\win32\client\thread.c @ 65]
00000074`cdbff8c0 00000000`00000000 ntdll!RtlUserThreadStart+0x1d [d:\w8rtm\minkernel\ntdll\rtlstrt.c @ 1021]

Guess that sqlmin!YieldAndCheckForAbort causes CPU high. And from the call stack, it seems like it try to get root batch data, but I have no idea what’s the purpose of CBpQScan::GetRootBatchData, and how to avoid yield.

Any insights or suggestions are highly appreciated!
Details
Sign in to post a comment.
Posted by Eddie_liu on 12/20/2013 at 9:13 AM
Query against column store indexes will automatically use all threads available in the scheduler. I would recommend reducing the MAXDOP to something that is not 0 to limit the amount of threads that can be consumed by a query against column store indexes.

======================
If I reset the MAXDOP, will the query performance reduce when we run query under a few concurrent?
What do you suggestion to the value of MAXDOP?
Posted by New Registered User on 12/19/2013 at 11:35 AM
Also consider hyperthreading if your hardware is relatively new.
Posted by New Registered User on 12/19/2013 at 11:34 AM
Query against column store indexes will automatically use all threads available in the scheduler. I would recommend reducing the MAXDOP to something that is not 0 to limit the amount of threads that can be consumed by a query against column store indexes.
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
Perf-10Second_000001_Profiler_system information.zip 12/19/2013 297 KB
SQLDmpr0002.zip 12/19/2013 16.63 MB
SQLDmpr0001.zip 12/19/2013 15.9 MB