I have a question about performance difference.
I was hoping that there would be a difference in performance between
using a "dynamic query" for "order by" vs
"sorting" in "table/matrix properties"
My assumption was that once the dataset is populated, sorting it
through the table properties should be instant.
Unfortunately, I didn't see a difference.
Reporting services just appears to rerun the query...!!
Is this true? If so, shouldn't that be made better?
ThanksWhy is it that the MSFT team doesn't answer more than 20% of the
questions - specially if they have not been covered before?
This is an important question, and I would appreciate an answer.
I consulted the documentation and the books/ newsgroups that I could
find and I didn't find the answer to my question.|||Two points. When you see MS answering questions it occurs for two reasons.
One, a MS employee just jumping in and answering because they want to. It is
not a specified part of their job. For instance, someone from the testing
group or documentation group or a developer might jump in. Second, this is a
managed newsgroup. What that means is that it gets monitored for MSDN
subscription posting and makes sure they get answered (not necessarily by MS
but by anyone). So, your assumption that only MS answers count (20% figure)
is wayyyy off with regards to the purpose of the group. It is mostly peer
support. If guaranteed answers are a requirement for you then you need to
get an MSDN subscription. Visit the MS website for information on this. So
remember, this is peer support newsgroup for the most part and people are
volunteering their time to answer.
Now, about your question. You have already figured out what is going on
(when you change a parameter it reruns your query). Your question was a why
does it do this. I am not part of the development team but I have a good
understanding of how it all works. Although it seems obvious to you that the
way it currently works is brain dead, it is not as simple an issue as it
might seem. Parameters can be used multiple places. A single parameter can
be involved in expressions (like the sorting expression for a table in your
case). The same parameter can be part of a cascading parameter. You can have
multiple datasets so it could also at the same time be the input for a query
parameter, etc etc. So, even if they wanted to do as you suggest it is not
that straight forward. My feeling is that there is another reason as well.
Architecture. What you are interacting with, putting in the parameter, is
not a monolithic application. It is a portal to Reporting Services. You can
replace this with your own app (many people do) and use web services or URL
integration to tightly tie your app to Reporting Services. You can do this
so tightly that people do not even realize that the report is being rendered
somewhere else.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Harry" <harshwardhan@.mailcity.com> wrote in message
news:e48fb32a.0410260704.75ba5cb8@.posting.google.com...
> Why is it that the MSFT team doesn't answer more than 20% of the
> questions - specially if they have not been covered before?
> This is an important question, and I would appreciate an answer.
> I consulted the documentation and the books/ newsgroups that I could
> find and I didn't find the answer to my question.
Showing posts with label dynamic. Show all posts
Showing posts with label dynamic. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
performance difference between query analyzer and .NET code.
Dear Sir,
I used dynamic SQL on stored procedure to do the paging in my site.
I often hit a connection time out problem with that dynamic SQl when I call
it from .NET code.
However if it run it under query analyzer, everything works fine.
Who can explain this to me and what should I do in this case?
regards,
Guoqi Zheng
http://www.ureader.comWhen you say "everything works fine", do you also mean that the execution
time in Query Analyzer is within the connection timeout you have set in your
.Net application?
ML
http://milambda.blogspot.com/|||> I used dynamic SQL on stored procedure to do the paging in my site.
> I often hit a connection time out problem with that dynamic SQl when I
> call
> it from .NET code.
> However if it run it under query analyzer, everything works fine.
> Who can explain this to me and what should I do in this case?
If "everything works fine" still means it's slow, and extends beyond the
timeout setting that ML mentioned, then everything is not working fine.
One thing you might try is a different paging approach. There are probably
a handful of samples here that will be faster and safer than dynamic SQL:
http://www.aspfaq.com/2120
I used dynamic SQL on stored procedure to do the paging in my site.
I often hit a connection time out problem with that dynamic SQl when I call
it from .NET code.
However if it run it under query analyzer, everything works fine.
Who can explain this to me and what should I do in this case?
regards,
Guoqi Zheng
http://www.ureader.comWhen you say "everything works fine", do you also mean that the execution
time in Query Analyzer is within the connection timeout you have set in your
.Net application?
ML
http://milambda.blogspot.com/|||> I used dynamic SQL on stored procedure to do the paging in my site.
> I often hit a connection time out problem with that dynamic SQl when I
> call
> it from .NET code.
> However if it run it under query analyzer, everything works fine.
> Who can explain this to me and what should I do in this case?
If "everything works fine" still means it's slow, and extends beyond the
timeout setting that ML mentioned, then everything is not working fine.
One thing you might try is a different paging approach. There are probably
a handful of samples here that will be faster and safer than dynamic SQL:
http://www.aspfaq.com/2120
Friday, March 9, 2012
Performance - tweaking possible?
Hi,
we have an app that is written in a "special" way. Not using SP's. Instead,
dynamic SQL is used.
Also, it's written in a way that in order to update single order, the app
sends 4-5 SQL's; some of them INSERTs and some SELECTs.
Now here is the problem: some of the procesess require updating hundreds of
thousands of "orders". Obviously the app will not (and IS not) going to
scale. When such processes executed, SQL Server is getting hammered. After
some time in the process - one CPU is getting to 100% utilization and stays
this way until the end; this slows down the system to the point that other,
read-only clients get "timeout expired".
Now here is the bad part - even running on top of the line quad server does
not help the problem - since one CPU gets to 100% sooner or later.
I was reading technet and they suggest that SQL Server cannot split very
small requests under such conditions. The problem that there are millions of
very small (uder 100ms) requests coming from single connection.
My question is: can I configure the server in a way that more than 1 CPU is
used?
Note: changing the code is not an option at this point.
Thanks,
Dima.First, check the server settings to ensure parallel execution is enabled
(it should be by default).
Second, start looking for another job. I'm not entirely joking, here. If it
is as bad as you describe and the management is unwilling to fix the
underlying flaws, then you are in database hell. Not a pretty place.
You said nothing about the indexing on the tables. Are there any? Any
clustered indexes? This is the one place where you can make changes that
won't likely break client side code.
Bob Castleman
DBA Poseur
"Dima Semensky" <dsemen@.newsgroup.nospam> wrote in message
news:%23YnIj09MFHA.1436@.TK2MSFTNGP10.phx.gbl...
> Hi,
> we have an app that is written in a "special" way. Not using SP's.
> Instead, dynamic SQL is used.
> Also, it's written in a way that in order to update single order, the app
> sends 4-5 SQL's; some of them INSERTs and some SELECTs.
> Now here is the problem: some of the procesess require updating hundreds
> of thousands of "orders". Obviously the app will not (and IS not) going to
> scale. When such processes executed, SQL Server is getting hammered. After
> some time in the process - one CPU is getting to 100% utilization and
> stays this way until the end; this slows down the system to the point that
> other, read-only clients get "timeout expired".
> Now here is the bad part - even running on top of the line quad server
> does not help the problem - since one CPU gets to 100% sooner or later.
> I was reading technet and they suggest that SQL Server cannot split very
> small requests under such conditions. The problem that there are millions
> of very small (uder 100ms) requests coming from single connection.
> My question is: can I configure the server in a way that more than 1 CPU
> is used?
> Note: changing the code is not an option at this point.
> Thanks,
> Dima.
>|||"Dima Semensky" <dsemen@.newsgroup.nospam> wrote in message
news:%23YnIj09MFHA.1436@.TK2MSFTNGP10.phx.gbl...
> Hi,
> we have an app that is written in a "special" way. Not using SP's.
> Instead, dynamic SQL is used.
> Also, it's written in a way that in order to update single order, the app
> sends 4-5 SQL's; some of them INSERTs and some SELECTs.
> Now here is the problem: some of the procesess require updating hundreds
> of thousands of "orders". Obviously the app will not (and IS not) going to
> scale. When such processes executed, SQL Server is getting hammered. After
> some time in the process - one CPU is getting to 100% utilization and
> stays this way until the end; this slows down the system to the point that
> other, read-only clients get "timeout expired".
> Now here is the bad part - even running on top of the line quad server
> does not help the problem - since one CPU gets to 100% sooner or later.
> I was reading technet and they suggest that SQL Server cannot split very
> small requests under such conditions. The problem that there are millions
> of very small (uder 100ms) requests coming from single connection.
> My question is: can I configure the server in a way that more than 1 CPU
> is used?
> Note: changing the code is not an option at this point.
>
Let me see if I get it:
You have one session running batch processes which are comprised of hundreds
of thousands of inexpensive queries (single-row updates, selects, etc).
When this process is running other connections experience poor performance
and even timeouts.
And you wonder if you can configure the server to utilize multiple CPU's for
the work of the batch process. If that is your question, the answer it no.
And you wouldn't want to even if you could. In such a situation you want
the batch session to consume LESS not MORE server resources, in order to not
adversely affect other users. Fundamentally there's nothing fatal about
having a long-running batch job spike one of your CPU's (unless that batch
job holds locks required by other sessions).
First, determine if the other session's timeouts are caused by CPU
contention or by lock waits. If the other sessions are waiting on locks
held by the batch process, then your situation is grim. If they are victims
of CPU contention, you might be able to reduce the CPU cost of the queries
and increase the CPU resources of the server and wiggle out of trouble.
Under your constraints, here's what you can do. Capture and analyze the
commands coming from the batch connection. Examine the query plans for each
one and try to optimize them using the index tuning wizard. But "tuning"
the database server rarely has much impact on poorly written applictations.
It's worth a try, but there are limits to what you can do. You might get
lucky and find some glaring omission in the indexing which improves the
application's performance (the worse the applictation, the poorer the
indexing, right?).
David|||Thanks David.
I have done what you suggesting and I am sure that this is CPU contention
problem.
Indexes are there and work fine in smaller batches. However, when there is
huge batch once in a while it overloads the CPU which affects whole server.
I was thinking about some setting I can set to evenly spread the load. So
instead of loading 1 CPU for 100%, I could load 4 with 20 each.
In my view, it is really a matter of configuration. If the app opened new
connection for every statement - this situation would not arise, however
there would be another performance problem - with too many opened/closed
connections per sec.
So, if I could tell SQL Server to e.g. handle each statement in different
threads, say in round robin way - this could be a way out.
Note: this is temporary solution for couple months to survive
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eKUpMt%23MFHA.244@.tk2msftngp13.phx.gbl...
> "Dima Semensky" <dsemen@.newsgroup.nospam> wrote in message
> news:%23YnIj09MFHA.1436@.TK2MSFTNGP10.phx.gbl...
> Let me see if I get it:
> You have one session running batch processes which are comprised of
> hundreds of thousands of inexpensive queries (single-row updates, selects,
> etc). When this process is running other connections experience poor
> performance and even timeouts.
> And you wonder if you can configure the server to utilize multiple CPU's
> for the work of the batch process. If that is your question, the answer
> it no. And you wouldn't want to even if you could. In such a situation
> you want the batch session to consume LESS not MORE server resources, in
> order to not adversely affect other users. Fundamentally there's nothing
> fatal about having a long-running batch job spike one of your CPU's
> (unless that batch job holds locks required by other sessions).
> First, determine if the other session's timeouts are caused by CPU
> contention or by lock waits. If the other sessions are waiting on locks
> held by the batch process, then your situation is grim. If they are
> victims of CPU contention, you might be able to reduce the CPU cost of the
> queries and increase the CPU resources of the server and wiggle out of
> trouble.
> Under your constraints, here's what you can do. Capture and analyze the
> commands coming from the batch connection. Examine the query plans for
> each one and try to optimize them using the index tuning wizard. But
> "tuning" the database server rarely has much impact on poorly written
> applictations. It's worth a try, but there are limits to what you can do.
> You might get lucky and find some glaring omission in the indexing which
> improves the application's performance (the worse the applictation, the
> poorer the indexing, right?).
> David
>|||Thanks Bob.
The reason for not changing the app is that new solution will be replacing
this soon. However, in the meantime I'm looking into what other options I
have there.
I'm just looking for a way to configure the SQL Server to may be somehow
"spread" the load
"Bob Castleman" <nomail@.here> wrote in message
news:%23ojhJl%23MFHA.2604@.TK2MSFTNGP10.phx.gbl...
> First, check the server settings to ensure parallel execution is enabled
> (it should be by default).
> Second, start looking for another job. I'm not entirely joking, here. If
> it is as bad as you describe and the management is unwilling to fix the
> underlying flaws, then you are in database hell. Not a pretty place.
> You said nothing about the indexing on the tables. Are there any? Any
> clustered indexes? This is the one place where you can make changes that
> won't likely break client side code.
> Bob Castleman
> DBA Poseur
> "Dima Semensky" <dsemen@.newsgroup.nospam> wrote in message
> news:%23YnIj09MFHA.1436@.TK2MSFTNGP10.phx.gbl...
>|||On Mon, 28 Mar 2005 16:28:51 -0500, "Dima Semensky"
<dsemen@.newsgroup.nospam> wrote:
>this way until the end; this slows down the system to the point that other,
>read-only clients get "timeout expired".
For one thing, check your isolation levels.
Some of your read-only clients are probably blocked, if it's
acceptable to run with dirty data you can run with NOLOCK and see if
that helps.
I can't think of any server-side tweaks that can help.
J.|||"Dima Semensky" <dsemen@.newsgroup.nospam> wrote in message
news:uF$k6V$MFHA.3900@.TK2MSFTNGP10.phx.gbl...
> Thanks David.
> I have done what you suggesting and I am sure that this is CPU contention
> problem.
> Indexes are there and work fine in smaller batches. However, when there is
> huge batch once in a while it overloads the CPU which affects whole
> server.
> I was thinking about some setting I can set to evenly spread the load. So
> instead of loading 1 CPU for 100%, I could load 4 with 20 each.
>
That's not what would happen. If you parallelize the load it would spike 4
CPU's to 100% for 1/4 the time (actually more since parallel plans are less
efficient).
> In my view, it is really a matter of configuration. If the app opened new
> connection for every statement - this situation would not arise, however
> there would be another performance problem - with too many opened/closed
> connections per sec.
The app would still be issuing the statements one at at time. But I'm
. How many CPU's are in the server?
> So, if I could tell SQL Server to e.g. handle each statement in different
> threads, say in round robin way - this could be a way out.
That's not really the case. Each thread is scheduled on any available CPU.
So a single connection issuing multiple single statements might have its
work scheduled on various CPU's or might have its work scheduled on the same
CPU each time. Which one of these happens is a detail of the SQL Server
User Mode Scheduler. But the net effect is the same. The batch processing
connection will account eat 1000ms of CPU time every second. If that is
250ms on each of 4 processors or 1000ms on 1 of 4 processors, the net effect
on other connections is the same. The batch process will occupy 25% of
available CPU resources.
One thing to check, though is that the batch process isn't using any
parallel query plans. With a parallel query plan the batch process could
monopolize more that 1 CPU. There is a server-wide setting for the maximum
degree of parallelism (MAXDOP). To protect online work from being adversely
affected by large batch jobs, you might want to set MAXDOP=1.
> Note: this is temporary solution for couple months to survive
>
Been there.
David|||Sorry for the sarcasm, but I still think you're in database hell.
You didn't say anything about memory utilization. Is just the CPU getting
consumed? What about other resources?
If the read only clients are truely read only (i.e. NEVER updating), then
how about replicating to another server? Then the read only clients would
not have to contend with cpu consumption on the transactional database.
Bob Castleman
DBA Poseur
"Dima Semensky" <dsemen@.newsgroup.nospam> wrote in message
news:evWx4W$MFHA.2580@.TK2MSFTNGP09.phx.gbl...
> Thanks Bob.
> The reason for not changing the app is that new solution will be replacing
> this soon. However, in the meantime I'm looking into what other options I
> have there.
> I'm just looking for a way to configure the SQL Server to may be somehow
> "spread" the load
> "Bob Castleman" <nomail@.here> wrote in message
> news:%23ojhJl%23MFHA.2604@.TK2MSFTNGP10.phx.gbl...
>|||4 CPU's. 4GB RAM, SAN
yes, the app would issue one at a time and there is no way around it.
However, if for example the SQL Server sent each statement to different
thread - then all CPU would be working instead of single.
It's a mistery for me why SQL Server goes to 100% at some point if the app
sends the statements in sequential order. May be something related to the
way it works with threads...
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OafLAh$MFHA.3668@.TK2MSFTNGP14.phx.gbl...
> "Dima Semensky" <dsemen@.newsgroup.nospam> wrote in message
> news:uF$k6V$MFHA.3900@.TK2MSFTNGP10.phx.gbl...
> That's not what would happen. If you parallelize the load it would spike
> 4 CPU's to 100% for 1/4 the time (actually more since parallel plans are
> less efficient).
>
> The app would still be issuing the statements one at at time. But I'm
>. How many CPU's are in the server?
>
> That's not really the case. Each thread is scheduled on any available
> CPU. So a single connection issuing multiple single statements might have
> its work scheduled on various CPU's or might have its work scheduled on
> the same CPU each time. Which one of these happens is a detail of the SQL
> Server User Mode Scheduler. But the net effect is the same. The batch
> processing connection will account eat 1000ms of CPU time every second.
> If that is 250ms on each of 4 processors or 1000ms on 1 of 4 processors,
> the net effect on other connections is the same. The batch process will
> occupy 25% of available CPU resources.
> One thing to check, though is that the batch process isn't using any
> parallel query plans. With a parallel query plan the batch process could
> monopolize more that 1 CPU. There is a server-wide setting for the
> maximum degree of parallelism (MAXDOP). To protect online work from being
> adversely affected by large batch jobs, you might want to set MAXDOP=1.
>
> Been there.
> David
>|||Thanks.
All reads are NOLOCK.
For a long time I couldn't understand why the thing times out. No or little
blocking, no deadlocks, all reads are done with NOLOCK.
Finally, I found that one of the CPU is hammered at 100% for several hours.
At this point (even this is quad server), reads start slowing down and
eventually time out.
"JRStern" <jxstern@.bogus.com> wrote in message
news:748h41l7i889u1n297o616jpagucieb4gs@.
4ax.com...
> On Mon, 28 Mar 2005 16:28:51 -0500, "Dima Semensky"
> <dsemen@.newsgroup.nospam> wrote:
> For one thing, check your isolation levels.
> Some of your read-only clients are probably blocked, if it's
> acceptable to run with dirty data you can run with NOLOCK and see if
> that helps.
> I can't think of any server-side tweaks that can help.
> J.
>
we have an app that is written in a "special" way. Not using SP's. Instead,
dynamic SQL is used.
Also, it's written in a way that in order to update single order, the app
sends 4-5 SQL's; some of them INSERTs and some SELECTs.
Now here is the problem: some of the procesess require updating hundreds of
thousands of "orders". Obviously the app will not (and IS not) going to
scale. When such processes executed, SQL Server is getting hammered. After
some time in the process - one CPU is getting to 100% utilization and stays
this way until the end; this slows down the system to the point that other,
read-only clients get "timeout expired".
Now here is the bad part - even running on top of the line quad server does
not help the problem - since one CPU gets to 100% sooner or later.
I was reading technet and they suggest that SQL Server cannot split very
small requests under such conditions. The problem that there are millions of
very small (uder 100ms) requests coming from single connection.
My question is: can I configure the server in a way that more than 1 CPU is
used?
Note: changing the code is not an option at this point.
Thanks,
Dima.First, check the server settings to ensure parallel execution is enabled
(it should be by default).
Second, start looking for another job. I'm not entirely joking, here. If it
is as bad as you describe and the management is unwilling to fix the
underlying flaws, then you are in database hell. Not a pretty place.
You said nothing about the indexing on the tables. Are there any? Any
clustered indexes? This is the one place where you can make changes that
won't likely break client side code.
Bob Castleman
DBA Poseur
"Dima Semensky" <dsemen@.newsgroup.nospam> wrote in message
news:%23YnIj09MFHA.1436@.TK2MSFTNGP10.phx.gbl...
> Hi,
> we have an app that is written in a "special" way. Not using SP's.
> Instead, dynamic SQL is used.
> Also, it's written in a way that in order to update single order, the app
> sends 4-5 SQL's; some of them INSERTs and some SELECTs.
> Now here is the problem: some of the procesess require updating hundreds
> of thousands of "orders". Obviously the app will not (and IS not) going to
> scale. When such processes executed, SQL Server is getting hammered. After
> some time in the process - one CPU is getting to 100% utilization and
> stays this way until the end; this slows down the system to the point that
> other, read-only clients get "timeout expired".
> Now here is the bad part - even running on top of the line quad server
> does not help the problem - since one CPU gets to 100% sooner or later.
> I was reading technet and they suggest that SQL Server cannot split very
> small requests under such conditions. The problem that there are millions
> of very small (uder 100ms) requests coming from single connection.
> My question is: can I configure the server in a way that more than 1 CPU
> is used?
> Note: changing the code is not an option at this point.
> Thanks,
> Dima.
>|||"Dima Semensky" <dsemen@.newsgroup.nospam> wrote in message
news:%23YnIj09MFHA.1436@.TK2MSFTNGP10.phx.gbl...
> Hi,
> we have an app that is written in a "special" way. Not using SP's.
> Instead, dynamic SQL is used.
> Also, it's written in a way that in order to update single order, the app
> sends 4-5 SQL's; some of them INSERTs and some SELECTs.
> Now here is the problem: some of the procesess require updating hundreds
> of thousands of "orders". Obviously the app will not (and IS not) going to
> scale. When such processes executed, SQL Server is getting hammered. After
> some time in the process - one CPU is getting to 100% utilization and
> stays this way until the end; this slows down the system to the point that
> other, read-only clients get "timeout expired".
> Now here is the bad part - even running on top of the line quad server
> does not help the problem - since one CPU gets to 100% sooner or later.
> I was reading technet and they suggest that SQL Server cannot split very
> small requests under such conditions. The problem that there are millions
> of very small (uder 100ms) requests coming from single connection.
> My question is: can I configure the server in a way that more than 1 CPU
> is used?
> Note: changing the code is not an option at this point.
>
Let me see if I get it:
You have one session running batch processes which are comprised of hundreds
of thousands of inexpensive queries (single-row updates, selects, etc).
When this process is running other connections experience poor performance
and even timeouts.
And you wonder if you can configure the server to utilize multiple CPU's for
the work of the batch process. If that is your question, the answer it no.
And you wouldn't want to even if you could. In such a situation you want
the batch session to consume LESS not MORE server resources, in order to not
adversely affect other users. Fundamentally there's nothing fatal about
having a long-running batch job spike one of your CPU's (unless that batch
job holds locks required by other sessions).
First, determine if the other session's timeouts are caused by CPU
contention or by lock waits. If the other sessions are waiting on locks
held by the batch process, then your situation is grim. If they are victims
of CPU contention, you might be able to reduce the CPU cost of the queries
and increase the CPU resources of the server and wiggle out of trouble.
Under your constraints, here's what you can do. Capture and analyze the
commands coming from the batch connection. Examine the query plans for each
one and try to optimize them using the index tuning wizard. But "tuning"
the database server rarely has much impact on poorly written applictations.
It's worth a try, but there are limits to what you can do. You might get
lucky and find some glaring omission in the indexing which improves the
application's performance (the worse the applictation, the poorer the
indexing, right?).
David|||Thanks David.
I have done what you suggesting and I am sure that this is CPU contention
problem.
Indexes are there and work fine in smaller batches. However, when there is
huge batch once in a while it overloads the CPU which affects whole server.
I was thinking about some setting I can set to evenly spread the load. So
instead of loading 1 CPU for 100%, I could load 4 with 20 each.
In my view, it is really a matter of configuration. If the app opened new
connection for every statement - this situation would not arise, however
there would be another performance problem - with too many opened/closed
connections per sec.
So, if I could tell SQL Server to e.g. handle each statement in different
threads, say in round robin way - this could be a way out.
Note: this is temporary solution for couple months to survive
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eKUpMt%23MFHA.244@.tk2msftngp13.phx.gbl...
> "Dima Semensky" <dsemen@.newsgroup.nospam> wrote in message
> news:%23YnIj09MFHA.1436@.TK2MSFTNGP10.phx.gbl...
> Let me see if I get it:
> You have one session running batch processes which are comprised of
> hundreds of thousands of inexpensive queries (single-row updates, selects,
> etc). When this process is running other connections experience poor
> performance and even timeouts.
> And you wonder if you can configure the server to utilize multiple CPU's
> for the work of the batch process. If that is your question, the answer
> it no. And you wouldn't want to even if you could. In such a situation
> you want the batch session to consume LESS not MORE server resources, in
> order to not adversely affect other users. Fundamentally there's nothing
> fatal about having a long-running batch job spike one of your CPU's
> (unless that batch job holds locks required by other sessions).
> First, determine if the other session's timeouts are caused by CPU
> contention or by lock waits. If the other sessions are waiting on locks
> held by the batch process, then your situation is grim. If they are
> victims of CPU contention, you might be able to reduce the CPU cost of the
> queries and increase the CPU resources of the server and wiggle out of
> trouble.
> Under your constraints, here's what you can do. Capture and analyze the
> commands coming from the batch connection. Examine the query plans for
> each one and try to optimize them using the index tuning wizard. But
> "tuning" the database server rarely has much impact on poorly written
> applictations. It's worth a try, but there are limits to what you can do.
> You might get lucky and find some glaring omission in the indexing which
> improves the application's performance (the worse the applictation, the
> poorer the indexing, right?).
> David
>|||Thanks Bob.
The reason for not changing the app is that new solution will be replacing
this soon. However, in the meantime I'm looking into what other options I
have there.
I'm just looking for a way to configure the SQL Server to may be somehow
"spread" the load
"Bob Castleman" <nomail@.here> wrote in message
news:%23ojhJl%23MFHA.2604@.TK2MSFTNGP10.phx.gbl...
> First, check the server settings to ensure parallel execution is enabled
> (it should be by default).
> Second, start looking for another job. I'm not entirely joking, here. If
> it is as bad as you describe and the management is unwilling to fix the
> underlying flaws, then you are in database hell. Not a pretty place.
> You said nothing about the indexing on the tables. Are there any? Any
> clustered indexes? This is the one place where you can make changes that
> won't likely break client side code.
> Bob Castleman
> DBA Poseur
> "Dima Semensky" <dsemen@.newsgroup.nospam> wrote in message
> news:%23YnIj09MFHA.1436@.TK2MSFTNGP10.phx.gbl...
>|||On Mon, 28 Mar 2005 16:28:51 -0500, "Dima Semensky"
<dsemen@.newsgroup.nospam> wrote:
>this way until the end; this slows down the system to the point that other,
>read-only clients get "timeout expired".
For one thing, check your isolation levels.
Some of your read-only clients are probably blocked, if it's
acceptable to run with dirty data you can run with NOLOCK and see if
that helps.
I can't think of any server-side tweaks that can help.
J.|||"Dima Semensky" <dsemen@.newsgroup.nospam> wrote in message
news:uF$k6V$MFHA.3900@.TK2MSFTNGP10.phx.gbl...
> Thanks David.
> I have done what you suggesting and I am sure that this is CPU contention
> problem.
> Indexes are there and work fine in smaller batches. However, when there is
> huge batch once in a while it overloads the CPU which affects whole
> server.
> I was thinking about some setting I can set to evenly spread the load. So
> instead of loading 1 CPU for 100%, I could load 4 with 20 each.
>
That's not what would happen. If you parallelize the load it would spike 4
CPU's to 100% for 1/4 the time (actually more since parallel plans are less
efficient).
> In my view, it is really a matter of configuration. If the app opened new
> connection for every statement - this situation would not arise, however
> there would be another performance problem - with too many opened/closed
> connections per sec.
The app would still be issuing the statements one at at time. But I'm
. How many CPU's are in the server?> So, if I could tell SQL Server to e.g. handle each statement in different
> threads, say in round robin way - this could be a way out.
That's not really the case. Each thread is scheduled on any available CPU.
So a single connection issuing multiple single statements might have its
work scheduled on various CPU's or might have its work scheduled on the same
CPU each time. Which one of these happens is a detail of the SQL Server
User Mode Scheduler. But the net effect is the same. The batch processing
connection will account eat 1000ms of CPU time every second. If that is
250ms on each of 4 processors or 1000ms on 1 of 4 processors, the net effect
on other connections is the same. The batch process will occupy 25% of
available CPU resources.
One thing to check, though is that the batch process isn't using any
parallel query plans. With a parallel query plan the batch process could
monopolize more that 1 CPU. There is a server-wide setting for the maximum
degree of parallelism (MAXDOP). To protect online work from being adversely
affected by large batch jobs, you might want to set MAXDOP=1.
> Note: this is temporary solution for couple months to survive
>
Been there.
David|||Sorry for the sarcasm, but I still think you're in database hell.
You didn't say anything about memory utilization. Is just the CPU getting
consumed? What about other resources?
If the read only clients are truely read only (i.e. NEVER updating), then
how about replicating to another server? Then the read only clients would
not have to contend with cpu consumption on the transactional database.
Bob Castleman
DBA Poseur
"Dima Semensky" <dsemen@.newsgroup.nospam> wrote in message
news:evWx4W$MFHA.2580@.TK2MSFTNGP09.phx.gbl...
> Thanks Bob.
> The reason for not changing the app is that new solution will be replacing
> this soon. However, in the meantime I'm looking into what other options I
> have there.
> I'm just looking for a way to configure the SQL Server to may be somehow
> "spread" the load
> "Bob Castleman" <nomail@.here> wrote in message
> news:%23ojhJl%23MFHA.2604@.TK2MSFTNGP10.phx.gbl...
>|||4 CPU's. 4GB RAM, SAN
yes, the app would issue one at a time and there is no way around it.
However, if for example the SQL Server sent each statement to different
thread - then all CPU would be working instead of single.
It's a mistery for me why SQL Server goes to 100% at some point if the app
sends the statements in sequential order. May be something related to the
way it works with threads...
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OafLAh$MFHA.3668@.TK2MSFTNGP14.phx.gbl...
> "Dima Semensky" <dsemen@.newsgroup.nospam> wrote in message
> news:uF$k6V$MFHA.3900@.TK2MSFTNGP10.phx.gbl...
> That's not what would happen. If you parallelize the load it would spike
> 4 CPU's to 100% for 1/4 the time (actually more since parallel plans are
> less efficient).
>
> The app would still be issuing the statements one at at time. But I'm
>
. How many CPU's are in the server?>
> That's not really the case. Each thread is scheduled on any available
> CPU. So a single connection issuing multiple single statements might have
> its work scheduled on various CPU's or might have its work scheduled on
> the same CPU each time. Which one of these happens is a detail of the SQL
> Server User Mode Scheduler. But the net effect is the same. The batch
> processing connection will account eat 1000ms of CPU time every second.
> If that is 250ms on each of 4 processors or 1000ms on 1 of 4 processors,
> the net effect on other connections is the same. The batch process will
> occupy 25% of available CPU resources.
> One thing to check, though is that the batch process isn't using any
> parallel query plans. With a parallel query plan the batch process could
> monopolize more that 1 CPU. There is a server-wide setting for the
> maximum degree of parallelism (MAXDOP). To protect online work from being
> adversely affected by large batch jobs, you might want to set MAXDOP=1.
>
> Been there.
> David
>|||Thanks.
All reads are NOLOCK.
For a long time I couldn't understand why the thing times out. No or little
blocking, no deadlocks, all reads are done with NOLOCK.
Finally, I found that one of the CPU is hammered at 100% for several hours.
At this point (even this is quad server), reads start slowing down and
eventually time out.
"JRStern" <jxstern@.bogus.com> wrote in message
news:748h41l7i889u1n297o616jpagucieb4gs@.
4ax.com...
> On Mon, 28 Mar 2005 16:28:51 -0500, "Dima Semensky"
> <dsemen@.newsgroup.nospam> wrote:
> For one thing, check your isolation levels.
> Some of your read-only clients are probably blocked, if it's
> acceptable to run with dirty data you can run with NOLOCK and see if
> that helps.
> I can't think of any server-side tweaks that can help.
> J.
>
Subscribe to:
Posts (Atom)