Home Dashboard Directory Help
Search

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


Status: 

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)
view
1
User(s) can reproduce this bug

Description

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
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.
File Name Submitted By Submitted On File Size  
New Microsoft Excel Worksheet.xlsx 1/18/2013 9 KB