Long Running OPENXML Query

Hi,
I have a long running OPENXML query in a SP that runs for around 7 mins, it
runs 6 queries doing inserts and updates from the XML to the DB. The XML is
around 2.5MB in size.
On my test server which has 1 physical XEON CPU, SQL Server uses 50% of both
logical processors and all works fine.
However my live server has 2 physical CPUs but we only have a single proc
license. The SQL Server is configured to use 2 logical CPUs but just the 1
physical CPU. When the long running query begins the query runs on just 1
logical processor and all other connections to the SQL server timeout.
Does anyone know how to get the query to use both logical processors at 50%
and thus allow other connections to the server?
Thanks
Jason
Hi Jason,
Thanks for your posting!
From your descriptions, I understood you can make two CPU balance the
workload in test environment but failed to do so in live machine. If I have
misunderstood your concern, please feel free to point it out.
Based on my knowlegde, please check SQL Server Properties to see whether
you are using that two logic processor in SQL Server and selected Use.
However, we do not have a way to compose the query to use both logical
processor.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||What version of SQL Server 2000 are you running? Have you upgraded to SP4
(which should give you some improvement on the OpenXML front).
Best regards
Michael
"Jason" <techno@.noemail.nospam> wrote in message
news:%23JxasEJiFHA.3960@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a long running OPENXML query in a SP that runs for around 7 mins,
> it
> runs 6 queries doing inserts and updates from the XML to the DB. The XML
> is
> around 2.5MB in size.
> On my test server which has 1 physical XEON CPU, SQL Server uses 50% of
> both
> logical processors and all works fine.
> However my live server has 2 physical CPUs but we only have a single proc
> license. The SQL Server is configured to use 2 logical CPUs but just the 1
> physical CPU. When the long running query begins the query runs on just 1
> logical processor and all other connections to the SQL server timeout.
> Does anyone know how to get the query to use both logical processors at
> 50%
> and thus allow other connections to the server?
> Thanks
> Jason
>
|||Hi
Thanks for your replies.
We are running SP4. I have rewritten the query which has improved the speed
and also used NOLOCK to avoid locks being taken out for the duration of the
transaction.
The problem I still have is that SQL Server is only using 1 logical
processor for everything that it does. On the processor tab of the SQL
Properties window I have the following settings,
CPU 0 and 1 are checked, 2 and 3 are not.=20
Max worker threads is 255
Boost SQL server priority is checked
Parallism
Use all available processors is checked
Min query plan threshold is 5
When any SQL activity occurs the Task Manager performance window shows 25%
CPU usage on the left and out of the 4 available logical processors only 1
spikes.
Any ideas why its not using 50%?
Thanks
Jason
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23KJXNUdiFHA.576@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> What version of SQL Server 2000 are you running? Have you upgraded to SP4
> (which should give you some improvement on the OpenXML front).
> Best regards
> Michael
> "Jason" <techno@.noemail.nospam> wrote in message
> news:%23JxasEJiFHA.3960@.TK2MSFTNGP12.phx.gbl...
proc[vbcol=seagreen]
1[vbcol=seagreen]
1
>
|||OpenXML is implemented as a remote provider, so I believe parallelism is
disabled for OpenXML. I'm hardly an expert on the way SQL schedules, though.
Best regards
Michael
"Jason" <techno@.noemail.nospam> wrote in message
news:O$m92M3iFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Hi
> Thanks for your replies.
> We are running SP4. I have rewritten the query which has improved the
> speed
> and also used NOLOCK to avoid locks being taken out for the duration of
> the
> transaction.
> The problem I still have is that SQL Server is only using 1 logical
> processor for everything that it does. On the processor tab of the SQL
> Properties window I have the following settings,
> CPU 0 and 1 are checked, 2 and 3 are not.=20
> Max worker threads is 255
> Boost SQL server priority is checked
> Parallism
> Use all available processors is checked
> Min query plan threshold is 5
> When any SQL activity occurs the Task Manager performance window shows 25%
> CPU usage on the left and out of the 4 available logical processors only 1
> spikes.
> Any ideas why its not using 50%?
> Thanks
> Jason
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23KJXNUdiFHA.576@.tk2msftngp13.phx.gbl...
> proc
> 1
> 1
>