Search

SQLOleDB provider seems to run singlethreaded on SQL2008 (10.0.5500) by Wim SQL Server

Closed
as External Help for as External

1
0
Sign in
 to vote
Type: Bug
ID: 775958
Opened: 1/7/2013 5:22:58 AM
Access Restriction: Public
0
Workaround(s)
1
User(s) can reproduce this bug
We have a 8 core system with hyperthreading on. An old application using SQLOLEDB protocol version: 1895825409 connects all the time to sql server.
Users get timeout while we see 10% cpu load od sql server. Seen in CPU usage it looks like the sql is primarily using 1 core that is maxed out to 100% , this changes but most of the time only 1 cpu is reallt active.
Users run very small queries for which the application always creates a new connection.
So as it seems the bottleneck is the SQLOLEDB provider that's using just 1 core.
This way uses see very poor performance of SQL Server while I don't see any running queries.

Is this normal behaviour or shoudl the SQLOLEDB provider run multithreaded?
Since cpu usage is on average 10%.. users should not notice such a slow performance when connecting to the server.
Details (expand)

Product Language

English

Version

SQL Server 2008 SP3

Category

SQL Engine

Operating System

Other

Operating System Language

US English

Steps to Reproduce

system is running on 'Windows Server Standard without Hyper-V (2007) SP2
Memory 22GB
CPU: Intel Xeon CPU, E5520 @2.27GHz (2 Quad cores with hyperthreading on)
We have 5600connections while we only have 146 hosts.
And new connections are added very second..

Actual Results

1core loaded.. and having lots of 'connection timeouts'

Expected Results

use of all cpu cores.. so users would not have connection timeouts'

Platform

X64

Virtualization

 
File Attachments
File Name Submitted By Submitted On File Size  
New Microsoft Excel Worksheet.xlsx Wim SQL Server 1/18/2013 9 KB
Sign in to post a comment.
Posted by Microsoft on 2/13/2013 at 12:21 PM
Thanks,we will take a look and get back on what we find or if we need more data
Posted by Wim SQL Server on 1/18/2013 at 2:37 AM
We have a around 4000 to 6000 connections to the server. The program is a VB program (not .net) seems to create a connection everything a user wants information from teh server. Every query, procedure... all through a new connection.
Posted by Wim SQL Server on 1/18/2013 at 2:36 AM
We have a around 4000 to 6000 connections to the server. The program is a VB program (not .net) seems to create
Posted by Wim SQL Server on 1/18/2013 at 2:34 AM
Cpu: singal waits: 13.7%
plan reuse: 44% (lots of single user queries , that I cleanup every 15minutes)
cpxpacket: 9%
memory: 83% adhoc, Procedure: 17% (regularly adhoc are cleaned)
bufferhitratio: almost always 1
page life expextancy: 4000 (during night decreased to 600 due to batch job and rising to 10.000 during day.
memory grants pending:0

Io bottlenecks:
wait_type    waiting_tasks_count    wait_time_ms    max_wait_time_ms    signal_wait_time_ms    (No column name)
PAGEIOLATCH_SH    419158458    4845849029    77591    8027092    1.156090002840882671
PAGEIOLATCH_UP    75886    8228748    3692    2569    10.843565348022033049
PAGEIOLATCH_EX    5202607    27837314    32766    36031    0.535064708904593408
IO_COMPLETION    18260886    33634297    77606    143811    0.184187651135875882
WRITELOG    14652053    12819215    1311    1065175    0.087490913389406931

isolated top waits:
wait_type    Nbr    wait_time_s    pct    running_pct
PAGEIOLATCH_SH    419158697    4845850.57    41.79    41.79
CXPACKET    4522429562    3149259.10    27.16    68.95
BROKER_EVENTHANDLER    21    2140977.01    18.46    87.42
BACKUPBUFFER    118275524    328805.39    2.84    90.25
MSQL_XP    411920    211625.27    1.83    92.08
PREEMPTIVE_OS_GETPROCADDRESS    494304    211624.05    1.83    93.90
ASYNC_IO_COMPLETION    7840    208811.96    1.80    95.70

Io stalls:
(No column name)    file_id    io_stall_read_ms    num_of_reads    avg_read_stall_ms    io_stall_write_ms    num_of_writes    avg_write_stall_ms    io_stalls    total_io    avg_io_stall_ms
tempdb    4    63001092    10074804    6.3    6262951204    10153974    616.8    6325952296    20228778    312.7
tempdb    7    37170604    4982333    7.5    3080141499    5054761    609.4    3117312103    10037094    310.6
tempdb    3    65898045    9749005    6.8    6012160472    9831284    611.5    6078058517    19580289    310.4
tempdb    6    35230880    4983994    7.1    3079737454    5052824    609.5    3114968334    10036818    310.4
tempdb    5    63040461    9530967    6.6    5682287527    9610864    591.2    5745327988    19141831    300.1
tempdb    1    51310093    8325514    6.2    4428745909    8395537    527.5    4480056002    16721051    267.9
tempdb    2    201    53    3.7    88582080    2099644    42.2    88582281    2099697    42.2
DB        1    293769651    32977326    8.9    274731785    6236341    44.1    568501436    39213667    14.5
DB        3    98563858    14065692    7.0    126296112    3987752    31.7    224859970    18053444    12.5

the biggest activity is processing the opening/closing of connections but we have a 8 core system and still total cpu is 8 to 10%
Posted by Microsoft on 1/16/2013 at 2:47 PM
Hello, can you please upload perfmon counters especially capturing all SQL Server performance counters over some periodof time during the time you see this behaviour?
Sign in to post a workaround.
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement