We have a series of SPs, poorly written, but that's not the issue here.
Two machines, hardware identical, except mostly SAN (Prod) vs. Local Dasd
(Dev), dual P3, 2gig ram. Pretty much just DB servers.
But we've even tried the local Dasd on the Prod (Std) box.
The process runs 2 1/2 to 3 hours on Dev, 9 to 10 on Prod. On Dev that's
even everything on a single RAID drive. Prod at least has db and log
separate, more at times.
We've checked everything, and can't find any differences. Running on Prod
Off hours when it's not even breaking a sweat.
The proc does nothing special, except a cursor for Update (Ooouugghh).
Any ideas what to look at ? We compared everything we can think of, no
differences SQL, Win2000... No errors in the event log ..., Query plans
match even cost estimates.
Could Developer (which is Enterprise, right) provide that much boost vs.
Std, even with nothing, that I know of that would be using EE features.
Note to date everything I've wrritten, even some larger procs (10 to 20
million row processes) seem to run as expected. Slightly faster on Prod.
KlK, MCSE
Hi
As a good benchmark, run the SQL IO stress tools on your Dev and Production
server. It will indicate if your SAN is considerably slower than the local
drives.
Look at the links off http://msmvps.com/epprecht/archive/2.../24/10591.aspx
EE does have some performance enhancements over standard (pre-fetch) but the
Disk might be your issue.
Regards
Mike
"KevinK" wrote:
> We have a series of SPs, poorly written, but that's not the issue here.
> Two machines, hardware identical, except mostly SAN (Prod) vs. Local Dasd
> (Dev), dual P3, 2gig ram. Pretty much just DB servers.
> But we've even tried the local Dasd on the Prod (Std) box.
> The process runs 2 1/2 to 3 hours on Dev, 9 to 10 on Prod. On Dev that's
> even everything on a single RAID drive. Prod at least has db and log
> separate, more at times.
> We've checked everything, and can't find any differences. Running on Prod
> Off hours when it's not even breaking a sweat.
> The proc does nothing special, except a cursor for Update (Ooouugghh).
> Any ideas what to look at ? We compared everything we can think of, no
> differences SQL, Win2000... No errors in the event log ..., Query plans
> match even cost estimates.
> Could Developer (which is Enterprise, right) provide that much boost vs.
> Std, even with nothing, that I know of that would be using EE features.
> Note to date everything I've wrritten, even some larger procs (10 to 20
> million row processes) seem to run as expected. Slightly faster on Prod.
>
> --
> KlK, MCSE
|||Thanks Mike, unfortunately we have tested it using only the local drive on
Prod.
We have a 70gig local raid drive that is normally used for Backups etc.
It didn't help.
We do know the SAN drives are slower than locals, but it isn't that
significant, maybe 20% (I think 12 to 15 was the official number.
But when we run it on the SAN we are using multiple drives, log and data.
I am trying to get them to do a DBHammer
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> As a good benchmark, run the SQL IO stress tools on your Dev and Production
> server. It will indicate if your SAN is considerably slower than the local
> drives.
> Look at the links off http://msmvps.com/epprecht/archive/2.../24/10591.aspx
> EE does have some performance enhancements over standard (pre-fetch) but the
> Disk might be your issue.
> Regards
> Mike
> "KevinK" wrote:
Showing posts with label written. Show all posts
Showing posts with label written. Show all posts
Monday, March 26, 2012
Performance Developer vs. Std
We have a series of SPs, poorly written, but that's not the issue here.
Two machines, hardware identical, except mostly SAN (Prod) vs. Local Dasd
(Dev), dual P3, 2gig ram. Pretty much just DB servers.
But we've even tried the local Dasd on the Prod (Std) box.
The process runs 2 1/2 to 3 hours on Dev, 9 to 10 on Prod. On Dev that's
even everything on a single RAID drive. Prod at least has db and log
separate, more at times.
We've checked everything, and can't find any differences. Running on Prod
Off hours when it's not even breaking a sweat.
The proc does nothing special, except a cursor for Update (Ooouugghh).
Any ideas what to look at ' We compared everything we can think of, no
differences SQL, Win2000... No errors in the event log ..., Query plans
match even cost estimates.
Could Developer (which is Enterprise, right) provide that much boost vs.
Std, even with nothing, that I know of that would be using EE features.
Note to date everything I've wrritten, even some larger procs (10 to 20
million row processes) seem to run as expected. Slightly faster on Prod.
--
KlK, MCSEHi
As a good benchmark, run the SQL IO stress tools on your Dev and Production
server. It will indicate if your SAN is considerably slower than the local
drives.
Look at the links off http://msmvps.com/epprecht/archive/2004/07/24/10591.aspx
EE does have some performance enhancements over standard (pre-fetch) but the
Disk might be your issue.
Regards
Mike
"KevinK" wrote:
> We have a series of SPs, poorly written, but that's not the issue here.
> Two machines, hardware identical, except mostly SAN (Prod) vs. Local Dasd
> (Dev), dual P3, 2gig ram. Pretty much just DB servers.
> But we've even tried the local Dasd on the Prod (Std) box.
> The process runs 2 1/2 to 3 hours on Dev, 9 to 10 on Prod. On Dev that's
> even everything on a single RAID drive. Prod at least has db and log
> separate, more at times.
> We've checked everything, and can't find any differences. Running on Prod
> Off hours when it's not even breaking a sweat.
> The proc does nothing special, except a cursor for Update (Ooouugghh).
> Any ideas what to look at ' We compared everything we can think of, no
> differences SQL, Win2000... No errors in the event log ..., Query plans
> match even cost estimates.
> Could Developer (which is Enterprise, right) provide that much boost vs.
> Std, even with nothing, that I know of that would be using EE features.
> Note to date everything I've wrritten, even some larger procs (10 to 20
> million row processes) seem to run as expected. Slightly faster on Prod.
>
> --
> KlK, MCSE|||Thanks Mike, unfortunately we have tested it using only the local drive on
Prod.
We have a 70gig local raid drive that is normally used for Backups etc.
It didn't help.
We do know the SAN drives are slower than locals, but it isn't that
significant, maybe 20% (I think 12 to 15 was the official number.
But when we run it on the SAN we are using multiple drives, log and data.
I am trying to get them to do a DBHammer
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> As a good benchmark, run the SQL IO stress tools on your Dev and Production
> server. It will indicate if your SAN is considerably slower than the local
> drives.
> Look at the links off http://msmvps.com/epprecht/archive/2004/07/24/10591.aspx
> EE does have some performance enhancements over standard (pre-fetch) but the
> Disk might be your issue.
> Regards
> Mike
> "KevinK" wrote:
> > We have a series of SPs, poorly written, but that's not the issue here.
> >
> > Two machines, hardware identical, except mostly SAN (Prod) vs. Local Dasd
> > (Dev), dual P3, 2gig ram. Pretty much just DB servers.
> >
> > But we've even tried the local Dasd on the Prod (Std) box.
> >
> > The process runs 2 1/2 to 3 hours on Dev, 9 to 10 on Prod. On Dev that's
> > even everything on a single RAID drive. Prod at least has db and log
> > separate, more at times.
> >
> > We've checked everything, and can't find any differences. Running on Prod
> > Off hours when it's not even breaking a sweat.
> >
> > The proc does nothing special, except a cursor for Update (Ooouugghh).
> >
> > Any ideas what to look at ' We compared everything we can think of, no
> > differences SQL, Win2000... No errors in the event log ..., Query plans
> > match even cost estimates.
> >
> > Could Developer (which is Enterprise, right) provide that much boost vs.
> > Std, even with nothing, that I know of that would be using EE features.
> >
> > Note to date everything I've wrritten, even some larger procs (10 to 20
> > million row processes) seem to run as expected. Slightly faster on Prod.
> >
> >
> >
> > --
> > KlK, MCSEsql
Two machines, hardware identical, except mostly SAN (Prod) vs. Local Dasd
(Dev), dual P3, 2gig ram. Pretty much just DB servers.
But we've even tried the local Dasd on the Prod (Std) box.
The process runs 2 1/2 to 3 hours on Dev, 9 to 10 on Prod. On Dev that's
even everything on a single RAID drive. Prod at least has db and log
separate, more at times.
We've checked everything, and can't find any differences. Running on Prod
Off hours when it's not even breaking a sweat.
The proc does nothing special, except a cursor for Update (Ooouugghh).
Any ideas what to look at ' We compared everything we can think of, no
differences SQL, Win2000... No errors in the event log ..., Query plans
match even cost estimates.
Could Developer (which is Enterprise, right) provide that much boost vs.
Std, even with nothing, that I know of that would be using EE features.
Note to date everything I've wrritten, even some larger procs (10 to 20
million row processes) seem to run as expected. Slightly faster on Prod.
--
KlK, MCSEHi
As a good benchmark, run the SQL IO stress tools on your Dev and Production
server. It will indicate if your SAN is considerably slower than the local
drives.
Look at the links off http://msmvps.com/epprecht/archive/2004/07/24/10591.aspx
EE does have some performance enhancements over standard (pre-fetch) but the
Disk might be your issue.
Regards
Mike
"KevinK" wrote:
> We have a series of SPs, poorly written, but that's not the issue here.
> Two machines, hardware identical, except mostly SAN (Prod) vs. Local Dasd
> (Dev), dual P3, 2gig ram. Pretty much just DB servers.
> But we've even tried the local Dasd on the Prod (Std) box.
> The process runs 2 1/2 to 3 hours on Dev, 9 to 10 on Prod. On Dev that's
> even everything on a single RAID drive. Prod at least has db and log
> separate, more at times.
> We've checked everything, and can't find any differences. Running on Prod
> Off hours when it's not even breaking a sweat.
> The proc does nothing special, except a cursor for Update (Ooouugghh).
> Any ideas what to look at ' We compared everything we can think of, no
> differences SQL, Win2000... No errors in the event log ..., Query plans
> match even cost estimates.
> Could Developer (which is Enterprise, right) provide that much boost vs.
> Std, even with nothing, that I know of that would be using EE features.
> Note to date everything I've wrritten, even some larger procs (10 to 20
> million row processes) seem to run as expected. Slightly faster on Prod.
>
> --
> KlK, MCSE|||Thanks Mike, unfortunately we have tested it using only the local drive on
Prod.
We have a 70gig local raid drive that is normally used for Backups etc.
It didn't help.
We do know the SAN drives are slower than locals, but it isn't that
significant, maybe 20% (I think 12 to 15 was the official number.
But when we run it on the SAN we are using multiple drives, log and data.
I am trying to get them to do a DBHammer
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> As a good benchmark, run the SQL IO stress tools on your Dev and Production
> server. It will indicate if your SAN is considerably slower than the local
> drives.
> Look at the links off http://msmvps.com/epprecht/archive/2004/07/24/10591.aspx
> EE does have some performance enhancements over standard (pre-fetch) but the
> Disk might be your issue.
> Regards
> Mike
> "KevinK" wrote:
> > We have a series of SPs, poorly written, but that's not the issue here.
> >
> > Two machines, hardware identical, except mostly SAN (Prod) vs. Local Dasd
> > (Dev), dual P3, 2gig ram. Pretty much just DB servers.
> >
> > But we've even tried the local Dasd on the Prod (Std) box.
> >
> > The process runs 2 1/2 to 3 hours on Dev, 9 to 10 on Prod. On Dev that's
> > even everything on a single RAID drive. Prod at least has db and log
> > separate, more at times.
> >
> > We've checked everything, and can't find any differences. Running on Prod
> > Off hours when it's not even breaking a sweat.
> >
> > The proc does nothing special, except a cursor for Update (Ooouugghh).
> >
> > Any ideas what to look at ' We compared everything we can think of, no
> > differences SQL, Win2000... No errors in the event log ..., Query plans
> > match even cost estimates.
> >
> > Could Developer (which is Enterprise, right) provide that much boost vs.
> > Std, even with nothing, that I know of that would be using EE features.
> >
> > Note to date everything I've wrritten, even some larger procs (10 to 20
> > million row processes) seem to run as expected. Slightly faster on Prod.
> >
> >
> >
> > --
> > KlK, MCSEsql
Performance Developer vs. Std
We have a series of SPs, poorly written, but that's not the issue here.
Two machines, hardware identical, except mostly SAN (Prod) vs. Local Dasd
(Dev), dual P3, 2gig ram. Pretty much just DB servers.
But we've even tried the local Dasd on the Prod (Std) box.
The process runs 2 1/2 to 3 hours on Dev, 9 to 10 on Prod. On Dev that's
even everything on a single RAID drive. Prod at least has db and log
separate, more at times.
We've checked everything, and can't find any differences. Running on Prod
Off hours when it's not even breaking a sweat.
The proc does nothing special, except a cursor for Update (Ooouugghh).
Any ideas what to look at ' We compared everything we can think of, no
differences SQL, Win2000... No errors in the event log ..., Query plans
match even cost estimates.
Could Developer (which is Enterprise, right) provide that much boost vs.
Std, even with nothing, that I know of that would be using EE features.
Note to date everything I've wrritten, even some larger procs (10 to 20
million row processes) seem to run as expected. Slightly faster on Prod.
KlK, MCSEHi
As a good benchmark, run the SQL IO stress tools on your Dev and Production
server. It will indicate if your SAN is considerably slower than the local
drives.
Look at the links off [url]http://msmvps.com/epprecht/archive/2004/07/24/10591.aspx[/ur
l]
EE does have some performance enhancements over standard (pre-fetch) but the
Disk might be your issue.
Regards
Mike
"KevinK" wrote:
> We have a series of SPs, poorly written, but that's not the issue here.
> Two machines, hardware identical, except mostly SAN (Prod) vs. Local Dasd
> (Dev), dual P3, 2gig ram. Pretty much just DB servers.
> But we've even tried the local Dasd on the Prod (Std) box.
> The process runs 2 1/2 to 3 hours on Dev, 9 to 10 on Prod. On Dev that's
> even everything on a single RAID drive. Prod at least has db and log
> separate, more at times.
> We've checked everything, and can't find any differences. Running on Prod
> Off hours when it's not even breaking a sweat.
> The proc does nothing special, except a cursor for Update (Ooouugghh).
> Any ideas what to look at ' We compared everything we can think of, no
> differences SQL, Win2000... No errors in the event log ..., Query plans
> match even cost estimates.
> Could Developer (which is Enterprise, right) provide that much boost vs.
> Std, even with nothing, that I know of that would be using EE features.
> Note to date everything I've wrritten, even some larger procs (10 to 20
> million row processes) seem to run as expected. Slightly faster on Prod.
>
> --
> KlK, MCSE|||Thanks Mike, unfortunately we have tested it using only the local drive on
Prod.
We have a 70gig local raid drive that is normally used for Backups etc.
It didn't help.
We do know the SAN drives are slower than locals, but it isn't that
significant, maybe 20% (I think 12 to 15 was the official number.
But when we run it on the SAN we are using multiple drives, log and data.
I am trying to get them to do a DBHammer
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> As a good benchmark, run the SQL IO stress tools on your Dev and Productio
n
> server. It will indicate if your SAN is considerably slower than the local
> drives.
> Look at the links off [url]http://msmvps.com/epprecht/archive/2004/07/24/10591.aspx[/
url]
> EE does have some performance enhancements over standard (pre-fetch) but t
he
> Disk might be your issue.
> Regards
> Mike
> "KevinK" wrote:
>
Two machines, hardware identical, except mostly SAN (Prod) vs. Local Dasd
(Dev), dual P3, 2gig ram. Pretty much just DB servers.
But we've even tried the local Dasd on the Prod (Std) box.
The process runs 2 1/2 to 3 hours on Dev, 9 to 10 on Prod. On Dev that's
even everything on a single RAID drive. Prod at least has db and log
separate, more at times.
We've checked everything, and can't find any differences. Running on Prod
Off hours when it's not even breaking a sweat.
The proc does nothing special, except a cursor for Update (Ooouugghh).
Any ideas what to look at ' We compared everything we can think of, no
differences SQL, Win2000... No errors in the event log ..., Query plans
match even cost estimates.
Could Developer (which is Enterprise, right) provide that much boost vs.
Std, even with nothing, that I know of that would be using EE features.
Note to date everything I've wrritten, even some larger procs (10 to 20
million row processes) seem to run as expected. Slightly faster on Prod.
KlK, MCSEHi
As a good benchmark, run the SQL IO stress tools on your Dev and Production
server. It will indicate if your SAN is considerably slower than the local
drives.
Look at the links off [url]http://msmvps.com/epprecht/archive/2004/07/24/10591.aspx[/ur
l]
EE does have some performance enhancements over standard (pre-fetch) but the
Disk might be your issue.
Regards
Mike
"KevinK" wrote:
> We have a series of SPs, poorly written, but that's not the issue here.
> Two machines, hardware identical, except mostly SAN (Prod) vs. Local Dasd
> (Dev), dual P3, 2gig ram. Pretty much just DB servers.
> But we've even tried the local Dasd on the Prod (Std) box.
> The process runs 2 1/2 to 3 hours on Dev, 9 to 10 on Prod. On Dev that's
> even everything on a single RAID drive. Prod at least has db and log
> separate, more at times.
> We've checked everything, and can't find any differences. Running on Prod
> Off hours when it's not even breaking a sweat.
> The proc does nothing special, except a cursor for Update (Ooouugghh).
> Any ideas what to look at ' We compared everything we can think of, no
> differences SQL, Win2000... No errors in the event log ..., Query plans
> match even cost estimates.
> Could Developer (which is Enterprise, right) provide that much boost vs.
> Std, even with nothing, that I know of that would be using EE features.
> Note to date everything I've wrritten, even some larger procs (10 to 20
> million row processes) seem to run as expected. Slightly faster on Prod.
>
> --
> KlK, MCSE|||Thanks Mike, unfortunately we have tested it using only the local drive on
Prod.
We have a 70gig local raid drive that is normally used for Backups etc.
It didn't help.
We do know the SAN drives are slower than locals, but it isn't that
significant, maybe 20% (I think 12 to 15 was the official number.
But when we run it on the SAN we are using multiple drives, log and data.
I am trying to get them to do a DBHammer
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> As a good benchmark, run the SQL IO stress tools on your Dev and Productio
n
> server. It will indicate if your SAN is considerably slower than the local
> drives.
> Look at the links off [url]http://msmvps.com/epprecht/archive/2004/07/24/10591.aspx[/
url]
> EE does have some performance enhancements over standard (pre-fetch) but t
he
> Disk might be your issue.
> Regards
> Mike
> "KevinK" wrote:
>
Friday, March 23, 2012
Performance counters disappeared
I have an application written in C++ that querys all
available performance counters via RegQueryValueEx() API
at 10 second interval on some Windows 2000 computers that
have SQL Server 7.0 installed. After the application have
run for some period of time (several days or longer), the
SQL Server related performance counters disappeared. They
are disabeld by the OS. The knowledge base article
Q248993 mentions that the performance library of Windows
2000 disables any performance DLL that returns data in the
incorrect format, causes an unhandled program fault, or
takes too long to return the performance data (this is
usually the cause of a library being disabled). It is
obvious the incorrect format does not apply to my case
since it would have caused the problem right after it
started not several days later. Anyone knows what causes
the problem?Did you see Q812915?
"Tianxiang Zhang" <tianxiang_zhang@.bmc.com> wrote in message
news:013701c344b5$7f9caed0$a501280a@.phx.gbl...
> I have an application written in C++ that querys all
> available performance counters via RegQueryValueEx() API
> at 10 second interval on some Windows 2000 computers that
> have SQL Server 7.0 installed. After the application have
> run for some period of time (several days or longer), the
> SQL Server related performance counters disappeared. They
> are disabeld by the OS. The knowledge base article
> Q248993 mentions that the performance library of Windows
> 2000 disables any performance DLL that returns data in the
> incorrect format, causes an unhandled program fault, or
> takes too long to return the performance data (this is
> usually the cause of a library being disabled). It is
> obvious the incorrect format does not apply to my case
> since it would have caused the problem right after it
> started not several days later. Anyone knows what causes
> the problem?|||I did. They don't seem to be the same issue. Thanks for
the help.
>--Original Message--
>Did you see Q812915?
>
>"Tianxiang Zhang" <tianxiang_zhang@.bmc.com> wrote in
message
>news:013701c344b5$7f9caed0$a501280a@.phx.gbl...
>> I have an application written in C++ that querys all
>> available performance counters via RegQueryValueEx() API
>> at 10 second interval on some Windows 2000 computers
that
>> have SQL Server 7.0 installed. After the application
have
>> run for some period of time (several days or longer),
the
>> SQL Server related performance counters disappeared.
They
>> are disabeld by the OS. The knowledge base article
>> Q248993 mentions that the performance library of Windows
>> 2000 disables any performance DLL that returns data in
the
>> incorrect format, causes an unhandled program fault, or
>> takes too long to return the performance data (this is
>> usually the cause of a library being disabled). It is
>> obvious the incorrect format does not apply to my case
>> since it would have caused the problem right after it
>> started not several days later. Anyone knows what
causes
>> the problem?
>
>.
>sql
available performance counters via RegQueryValueEx() API
at 10 second interval on some Windows 2000 computers that
have SQL Server 7.0 installed. After the application have
run for some period of time (several days or longer), the
SQL Server related performance counters disappeared. They
are disabeld by the OS. The knowledge base article
Q248993 mentions that the performance library of Windows
2000 disables any performance DLL that returns data in the
incorrect format, causes an unhandled program fault, or
takes too long to return the performance data (this is
usually the cause of a library being disabled). It is
obvious the incorrect format does not apply to my case
since it would have caused the problem right after it
started not several days later. Anyone knows what causes
the problem?Did you see Q812915?
"Tianxiang Zhang" <tianxiang_zhang@.bmc.com> wrote in message
news:013701c344b5$7f9caed0$a501280a@.phx.gbl...
> I have an application written in C++ that querys all
> available performance counters via RegQueryValueEx() API
> at 10 second interval on some Windows 2000 computers that
> have SQL Server 7.0 installed. After the application have
> run for some period of time (several days or longer), the
> SQL Server related performance counters disappeared. They
> are disabeld by the OS. The knowledge base article
> Q248993 mentions that the performance library of Windows
> 2000 disables any performance DLL that returns data in the
> incorrect format, causes an unhandled program fault, or
> takes too long to return the performance data (this is
> usually the cause of a library being disabled). It is
> obvious the incorrect format does not apply to my case
> since it would have caused the problem right after it
> started not several days later. Anyone knows what causes
> the problem?|||I did. They don't seem to be the same issue. Thanks for
the help.
>--Original Message--
>Did you see Q812915?
>
>"Tianxiang Zhang" <tianxiang_zhang@.bmc.com> wrote in
message
>news:013701c344b5$7f9caed0$a501280a@.phx.gbl...
>> I have an application written in C++ that querys all
>> available performance counters via RegQueryValueEx() API
>> at 10 second interval on some Windows 2000 computers
that
>> have SQL Server 7.0 installed. After the application
have
>> run for some period of time (several days or longer),
the
>> SQL Server related performance counters disappeared.
They
>> are disabeld by the OS. The knowledge base article
>> Q248993 mentions that the performance library of Windows
>> 2000 disables any performance DLL that returns data in
the
>> incorrect format, causes an unhandled program fault, or
>> takes too long to return the performance data (this is
>> usually the cause of a library being disabled). It is
>> obvious the incorrect format does not apply to my case
>> since it would have caused the problem right after it
>> started not several days later. Anyone knows what
causes
>> the problem?
>
>.
>sql
Labels:
api,
application,
available,
counters,
database,
disappeared,
interval,
microsoft,
mysql,
oracle,
performance,
querys,
regqueryvalueex,
second,
server,
sql,
via,
windows,
written
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
> 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
>
>
> 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.
>
Performance
Hello Everybody,
We have arround 10 reports, i have written store procedures and i have
created proper indexes on all the required columns. When i run specific
report, it takes around 40 secondes but when around 20 users log in to the
system (we have microsoft reporting server as a reporting tool and sql serve
r
2000 as a DB)
and start executing reports in that case the report which is taking around
40 seconds(when around couple of users log in to the system) takes around 10
minutes to run.
Here in all the reports we do only reads, no write operation and i do not
have any transactions in any of report store procedure.
I know when more than one person try to run same store procedure
at the same time in that case all the underlying table get shared lock.
I have checked DB Server CPU utilization, it is quiet normal.
So clearly it looks like when many users log in to the system, report
execution becomes very slow.
so what are the possible causes ?
Pls let me know.mvp wrote:
> Hello Everybody,
> We have arround 10 reports, i have written store procedures and i have
> created proper indexes on all the required columns. When i run
> specific report, it takes around 40 secondes but when around 20 users
> log in to the system (we have microsoft reporting server as a
> reporting tool and sql server 2000 as a DB)
> and start executing reports in that case the report which is taking
> around 40 seconds(when around couple of users log in to the system)
> takes around 10 minutes to run.
> Here in all the reports we do only reads, no write operation and i do
> not have any transactions in any of report store procedure.
>
> I know when more than one person try to run same store procedure
> at the same time in that case all the underlying table get shared
> lock.
> I have checked DB Server CPU utilization, it is quiet normal.
>
> So clearly it looks like when many users log in to the system, report
> execution becomes very slow.
> so what are the possible causes ?
Tons. Just to name a few
- sub optimal indexes (check execution plans)
- too few mem for SQL Server
- too much mem for SQL Server (-> paging)
- slow disks
- users select different data sets which reduce cache efficiency
- not enough CPU power
- locking
...
I'd start by using Profiler to see what's slow, and then dig further
probably using perfmon.
Good luck
robert|||Low CPU utilization in a case like this usually suggests another bottleneck.
Most likely disks or memory. If the reports are strictly read only and the
data is not being changed you might want to consider using the Read
Uncommitted transaction Isolation Level for the reports. It's not to reduce
blocking since you shouldn't have any but more to reduce the number of locks
and free up resources associated with them. Here are some links that should
help to find the bottlenecks:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:6688A51E-0414-4D52-B1C0-339F153ED13C@.microsoft.com...
> Hello Everybody,
> We have arround 10 reports, i have written store procedures and i have
> created proper indexes on all the required columns. When i run specific
> report, it takes around 40 secondes but when around 20 users log in to the
> system (we have microsoft reporting server as a reporting tool and sql
> server
> 2000 as a DB)
> and start executing reports in that case the report which is taking around
> 40 seconds(when around couple of users log in to the system) takes around
> 10
> minutes to run.
> Here in all the reports we do only reads, no write operation and i do not
> have any transactions in any of report store procedure.
>
> I know when more than one person try to run same store procedure
> at the same time in that case all the underlying table get shared lock.
> I have checked DB Server CPU utilization, it is quiet normal.
>
> So clearly it looks like when many users log in to the system, report
> execution becomes very slow.
> so what are the possible causes ?
> Pls let me know.|||The most likely reason the reports run slower while other users are accessin
g
the db is locking. The default transaction isolation for SQL is read
committed. The report (reader) is being blocked by the other users (writers)
who have uncommitted transactions. Using sp_who2 while the report is running
will show if the problem is due to locking.
If you are willing to accept the possibility of transactionally inconsistent
data in your reports, you could alter the stored procedures that generate th
e
reports to run in READ UNCOMMITTED isolation. That way, the report
transaction will not be blocked by any writers. If you can't run that risk,
you could replicate the database to another db, and use the replicated
(subscriber) db solely for reporting.
Or, migrate to SQL 2005 and use the new READ COMMITTED SNAPSHOT isolation
level ;-)
"mvp" wrote:
> Hello Everybody,
> We have arround 10 reports, i have written store procedures and i have
> created proper indexes on all the required columns. When i run specific
> report, it takes around 40 secondes but when around 20 users log in to the
> system (we have microsoft reporting server as a reporting tool and sql ser
ver
> 2000 as a DB)
> and start executing reports in that case the report which is taking around
> 40 seconds(when around couple of users log in to the system) takes around
10
> minutes to run.
> Here in all the reports we do only reads, no write operation and i do not
> have any transactions in any of report store procedure.
>
> I know when more than one person try to run same store procedure
> at the same time in that case all the underlying table get shared lock.
> I have checked DB Server CPU utilization, it is quiet normal.
>
> So clearly it looks like when many users log in to the system, report
> execution becomes very slow.
> so what are the possible causes ?
> Pls let me know.|||Hello Andrew/Mark,
Thanks for the reply. yes we do have report db is read only. We just load
data feed twice in a w
in night time.
so how can i change it to read uncommited ?
So Should I add WITH NOLOCK after each table in my store procedure.. Or just
add
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before the start of all my
report procedures ?
Will this reduct access time of each report when multiple user will login to
system ?
Pls let me know
"Andrew J. Kelly" wrote:
> Low CPU utilization in a case like this usually suggests another bottlenec
k.
> Most likely disks or memory. If the reports are strictly read only and th
e
> data is not being changed you might want to consider using the Read
> Uncommitted transaction Isolation Level for the reports. It's not to reduc
e
> blocking since you shouldn't have any but more to reduce the number of loc
ks
> and free up resources associated with them. Here are some links that shoul
d
> help to find the bottlenecks:
>
> http://www.microsoft.com/sql/techin.../perftuning.asp
> Performance WP's
> http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
> http://www.sql-server-performance.c...mance_audit.asp
> hardware Performance CheckList
> http://www.sql-server-performance.c...rmance_tips.asp
> SQL 2000 Performance tuning tips
> http://www.support.microsoft.com/?id=q224587 Troubleshooting App
> Performance
> http://msdn.microsoft.com/library/d...
fmon_24u1.asp
> Disk Monitoring
> --
> Andrew J. Kelly SQL MVP
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:6688A51E-0414-4D52-B1C0-339F153ED13C@.microsoft.com...
>
>|||Is the db actually placed in READ_ONLY mode? If so then SQL Server will not
use locks anyway and most of that is moot. That would probably be the best
way to handle it anyway. If you only update it a few times a w
then keep
it in Read_Only mode for all times other than when you update it. But I
still think you have a memory or disk issue as well. Please see the links I
posted to determine which one(s) you may have.
Andrew J. Kelly SQL MVP
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:F46DDE9A-99DA-476F-AF29-C00313190BBC@.microsoft.com...
> Hello Andrew/Mark,
> Thanks for the reply. yes we do have report db is read only. We just load
> data feed twice in a w
in night time.
> so how can i change it to read uncommited ?
> So Should I add WITH NOLOCK after each table in my store procedure.. Or
> just
> add
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before the start of all
> my
> report procedures ?
> Will this reduct access time of each report when multiple user will login
> to
> system ?
> Pls let me know
>
> "Andrew J. Kelly" wrote:
>|||Hi Andres,
yes my db will be read only except between friday to suday.
so what should i do,
should i put WITH NOLOCK after each table in select statement of my report
store procedures ? or just add
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
at the top of the report store procedure ?
If i will do above thing
will it not put shared locks on the tables of procedures if multiple users
will execute it ?
Pls let me know.
Thx
Let me know.
"Andrew J. Kelly" wrote:
> Is the db actually placed in READ_ONLY mode? If so then SQL Server will n
ot
> use locks anyway and most of that is moot. That would probably be the bes
t
> way to handle it anyway. If you only update it a few times a w
then ke
ep
> it in Read_Only mode for all times other than when you update it. But I
> still think you have a memory or disk issue as well. Please see the links
I
> posted to determine which one(s) you may have.
> --
> Andrew J. Kelly SQL MVP
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:F46DDE9A-99DA-476F-AF29-C00313190BBC@.microsoft.com...
>
>|||OK I am not sure we are talking the same thing here or not. When I say the
db is Read_only I mean you have actually done an ALTER DATABASE and set it
to read_only. That is different than just saying that no one will edit any
rows during. When the DB is put into this state it is truly READ_ONLY and
as such the engine knows that it does not require any locks since there is
no way the data will change. As such when you are in that state there is no
need to change the transaction level or to use NOLOCK. The whole purpose of
locking a row when you are editing it is so someone else doesn't edit that
same row at the same time. Also so someone doesn't read a value that is not
committed. When it is in READ_ONLY mode these conditions will never exist
so locking is not required.
But again, while this may help with memory utilization I don't think it is
the root cause of your issues. If there is no editing going on the reports
are using SHARED locks. That means there is no blocking. Please refer to
the links to get to the root cause.
Andrew J. Kelly SQL MVP
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:7E9A8245-DFE6-4237-A55A-200A3165335F@.microsoft.com...
> Hi Andres,
> yes my db will be read only except between friday to suday.
> so what should i do,
> should i put WITH NOLOCK after each table in select statement of my report
> store procedures ? or just add
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> at the top of the report store procedure ?
> If i will do above thing
> will it not put shared locks on the tables of procedures if multiple users
> will execute it ?
> Pls let me know.
> Thx
> Let me know.
> "Andrew J. Kelly" wrote:
>|||Hi Andrew,
Thanks for the reply.
So what i understand from your reply is that when database is in READ_ONLY
mode, still we can update/delete/insert rows into db but when we read, it
will not read only commited transaction, it may read dirty transactions too.
My Reports run during mon-fri and insert/update/delete happens during friday
night to sunday night.
So you are saying following thing,
If multiple users are attacking reports at same time during mon-fri and if i
put NOLOCK or SET TRANSACTION ISOLATION LEVEL UNCOMMITED, it will not help m
e
because in my case tables gets shared locks and they do not block read or
hurt performance, right ?
let me know if i misunderstand anything
"Andrew J. Kelly" wrote:
> OK I am not sure we are talking the same thing here or not. When I say th
e
> db is Read_only I mean you have actually done an ALTER DATABASE and set it
> to read_only. That is different than just saying that no one will edit an
y
> rows during. When the DB is put into this state it is truly READ_ONLY and
> as such the engine knows that it does not require any locks since there is
> no way the data will change. As such when you are in that state there is
no
> need to change the transaction level or to use NOLOCK. The whole purpose
of
> locking a row when you are editing it is so someone else doesn't edit that
> same row at the same time. Also so someone doesn't read a value that is no
t
> committed. When it is in READ_ONLY mode these conditions will never exist
> so locking is not required.
> But again, while this may help with memory utilization I don't think it is
> the root cause of your issues. If there is no editing going on the report
s
> are using SHARED locks. That means there is no blocking. Please refer to
> the links to get to the root cause.
> --
> Andrew J. Kelly SQL MVP
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:7E9A8245-DFE6-4237-A55A-200A3165335F@.microsoft.com...
>
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:573339
On Wed, 21 Dec 2005 14:49:02 -0800, mvp wrote:
>Hi Andrew,
>Thanks for the reply.
>So what i understand from your reply is that when database is in READ_ONLY
>mode, still we can update/delete/insert rows into db but when we read, it
>will not read only commited transaction, it may read dirty transactions too.[/color
]
Hi mvp,
No. If database is in READ_ONLY mode, all INSERT, UPDATE and DELETE
statements will fail. Only SELECT is permitted.
>My Reports run during mon-fri and insert/update/delete happens during frida
y
>night to sunday night.
>So you are saying following thing,
>If multiple users are attacking reports at same time during mon-fri and if
i
>put NOLOCK or SET TRANSACTION ISOLATION LEVEL UNCOMMITED, it will not help
me
>because in my case tables gets shared locks and they do not block read or
>hurt performance, right ?
No. If database is in READ_ONLY mode, SQL Server will not use any locks
at all. Adding NOLOCK or setting transaction level to read uncommited
has no effect at all, since no locks are taken anyway.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
We have arround 10 reports, i have written store procedures and i have
created proper indexes on all the required columns. When i run specific
report, it takes around 40 secondes but when around 20 users log in to the
system (we have microsoft reporting server as a reporting tool and sql serve
r
2000 as a DB)
and start executing reports in that case the report which is taking around
40 seconds(when around couple of users log in to the system) takes around 10
minutes to run.
Here in all the reports we do only reads, no write operation and i do not
have any transactions in any of report store procedure.
I know when more than one person try to run same store procedure
at the same time in that case all the underlying table get shared lock.
I have checked DB Server CPU utilization, it is quiet normal.
So clearly it looks like when many users log in to the system, report
execution becomes very slow.
so what are the possible causes ?
Pls let me know.mvp wrote:
> Hello Everybody,
> We have arround 10 reports, i have written store procedures and i have
> created proper indexes on all the required columns. When i run
> specific report, it takes around 40 secondes but when around 20 users
> log in to the system (we have microsoft reporting server as a
> reporting tool and sql server 2000 as a DB)
> and start executing reports in that case the report which is taking
> around 40 seconds(when around couple of users log in to the system)
> takes around 10 minutes to run.
> Here in all the reports we do only reads, no write operation and i do
> not have any transactions in any of report store procedure.
>
> I know when more than one person try to run same store procedure
> at the same time in that case all the underlying table get shared
> lock.
> I have checked DB Server CPU utilization, it is quiet normal.
>
> So clearly it looks like when many users log in to the system, report
> execution becomes very slow.
> so what are the possible causes ?
Tons. Just to name a few
- sub optimal indexes (check execution plans)
- too few mem for SQL Server
- too much mem for SQL Server (-> paging)
- slow disks
- users select different data sets which reduce cache efficiency
- not enough CPU power
- locking
...
I'd start by using Profiler to see what's slow, and then dig further
probably using perfmon.
Good luck
robert|||Low CPU utilization in a case like this usually suggests another bottleneck.
Most likely disks or memory. If the reports are strictly read only and the
data is not being changed you might want to consider using the Read
Uncommitted transaction Isolation Level for the reports. It's not to reduce
blocking since you shouldn't have any but more to reduce the number of locks
and free up resources associated with them. Here are some links that should
help to find the bottlenecks:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:6688A51E-0414-4D52-B1C0-339F153ED13C@.microsoft.com...
> Hello Everybody,
> We have arround 10 reports, i have written store procedures and i have
> created proper indexes on all the required columns. When i run specific
> report, it takes around 40 secondes but when around 20 users log in to the
> system (we have microsoft reporting server as a reporting tool and sql
> server
> 2000 as a DB)
> and start executing reports in that case the report which is taking around
> 40 seconds(when around couple of users log in to the system) takes around
> 10
> minutes to run.
> Here in all the reports we do only reads, no write operation and i do not
> have any transactions in any of report store procedure.
>
> I know when more than one person try to run same store procedure
> at the same time in that case all the underlying table get shared lock.
> I have checked DB Server CPU utilization, it is quiet normal.
>
> So clearly it looks like when many users log in to the system, report
> execution becomes very slow.
> so what are the possible causes ?
> Pls let me know.|||The most likely reason the reports run slower while other users are accessin
g
the db is locking. The default transaction isolation for SQL is read
committed. The report (reader) is being blocked by the other users (writers)
who have uncommitted transactions. Using sp_who2 while the report is running
will show if the problem is due to locking.
If you are willing to accept the possibility of transactionally inconsistent
data in your reports, you could alter the stored procedures that generate th
e
reports to run in READ UNCOMMITTED isolation. That way, the report
transaction will not be blocked by any writers. If you can't run that risk,
you could replicate the database to another db, and use the replicated
(subscriber) db solely for reporting.
Or, migrate to SQL 2005 and use the new READ COMMITTED SNAPSHOT isolation
level ;-)
"mvp" wrote:
> Hello Everybody,
> We have arround 10 reports, i have written store procedures and i have
> created proper indexes on all the required columns. When i run specific
> report, it takes around 40 secondes but when around 20 users log in to the
> system (we have microsoft reporting server as a reporting tool and sql ser
ver
> 2000 as a DB)
> and start executing reports in that case the report which is taking around
> 40 seconds(when around couple of users log in to the system) takes around
10
> minutes to run.
> Here in all the reports we do only reads, no write operation and i do not
> have any transactions in any of report store procedure.
>
> I know when more than one person try to run same store procedure
> at the same time in that case all the underlying table get shared lock.
> I have checked DB Server CPU utilization, it is quiet normal.
>
> So clearly it looks like when many users log in to the system, report
> execution becomes very slow.
> so what are the possible causes ?
> Pls let me know.|||Hello Andrew/Mark,
Thanks for the reply. yes we do have report db is read only. We just load
data feed twice in a w
so how can i change it to read uncommited ?
So Should I add WITH NOLOCK after each table in my store procedure.. Or just
add
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before the start of all my
report procedures ?
Will this reduct access time of each report when multiple user will login to
system ?
Pls let me know
"Andrew J. Kelly" wrote:
> Low CPU utilization in a case like this usually suggests another bottlenec
k.
> Most likely disks or memory. If the reports are strictly read only and th
e
> data is not being changed you might want to consider using the Read
> Uncommitted transaction Isolation Level for the reports. It's not to reduc
e
> blocking since you shouldn't have any but more to reduce the number of loc
ks
> and free up resources associated with them. Here are some links that shoul
d
> help to find the bottlenecks:
>
> http://www.microsoft.com/sql/techin.../perftuning.asp
> Performance WP's
> http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
> http://www.sql-server-performance.c...mance_audit.asp
> hardware Performance CheckList
> http://www.sql-server-performance.c...rmance_tips.asp
> SQL 2000 Performance tuning tips
> http://www.support.microsoft.com/?id=q224587 Troubleshooting App
> Performance
> http://msdn.microsoft.com/library/d...
fmon_24u1.asp
> Disk Monitoring
> --
> Andrew J. Kelly SQL MVP
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:6688A51E-0414-4D52-B1C0-339F153ED13C@.microsoft.com...
>
>|||Is the db actually placed in READ_ONLY mode? If so then SQL Server will not
use locks anyway and most of that is moot. That would probably be the best
way to handle it anyway. If you only update it a few times a w
it in Read_Only mode for all times other than when you update it. But I
still think you have a memory or disk issue as well. Please see the links I
posted to determine which one(s) you may have.
Andrew J. Kelly SQL MVP
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:F46DDE9A-99DA-476F-AF29-C00313190BBC@.microsoft.com...
> Hello Andrew/Mark,
> Thanks for the reply. yes we do have report db is read only. We just load
> data feed twice in a w
> so how can i change it to read uncommited ?
> So Should I add WITH NOLOCK after each table in my store procedure.. Or
> just
> add
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED before the start of all
> my
> report procedures ?
> Will this reduct access time of each report when multiple user will login
> to
> system ?
> Pls let me know
>
> "Andrew J. Kelly" wrote:
>|||Hi Andres,
yes my db will be read only except between friday to suday.
so what should i do,
should i put WITH NOLOCK after each table in select statement of my report
store procedures ? or just add
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
at the top of the report store procedure ?
If i will do above thing
will it not put shared locks on the tables of procedures if multiple users
will execute it ?
Pls let me know.
Thx
Let me know.
"Andrew J. Kelly" wrote:
> Is the db actually placed in READ_ONLY mode? If so then SQL Server will n
ot
> use locks anyway and most of that is moot. That would probably be the bes
t
> way to handle it anyway. If you only update it a few times a w
ep
> it in Read_Only mode for all times other than when you update it. But I
> still think you have a memory or disk issue as well. Please see the links
I
> posted to determine which one(s) you may have.
> --
> Andrew J. Kelly SQL MVP
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:F46DDE9A-99DA-476F-AF29-C00313190BBC@.microsoft.com...
>
>|||OK I am not sure we are talking the same thing here or not. When I say the
db is Read_only I mean you have actually done an ALTER DATABASE and set it
to read_only. That is different than just saying that no one will edit any
rows during. When the DB is put into this state it is truly READ_ONLY and
as such the engine knows that it does not require any locks since there is
no way the data will change. As such when you are in that state there is no
need to change the transaction level or to use NOLOCK. The whole purpose of
locking a row when you are editing it is so someone else doesn't edit that
same row at the same time. Also so someone doesn't read a value that is not
committed. When it is in READ_ONLY mode these conditions will never exist
so locking is not required.
But again, while this may help with memory utilization I don't think it is
the root cause of your issues. If there is no editing going on the reports
are using SHARED locks. That means there is no blocking. Please refer to
the links to get to the root cause.
Andrew J. Kelly SQL MVP
"mvp" <mvp@.discussions.microsoft.com> wrote in message
news:7E9A8245-DFE6-4237-A55A-200A3165335F@.microsoft.com...
> Hi Andres,
> yes my db will be read only except between friday to suday.
> so what should i do,
> should i put WITH NOLOCK after each table in select statement of my report
> store procedures ? or just add
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> at the top of the report store procedure ?
> If i will do above thing
> will it not put shared locks on the tables of procedures if multiple users
> will execute it ?
> Pls let me know.
> Thx
> Let me know.
> "Andrew J. Kelly" wrote:
>|||Hi Andrew,
Thanks for the reply.
So what i understand from your reply is that when database is in READ_ONLY
mode, still we can update/delete/insert rows into db but when we read, it
will not read only commited transaction, it may read dirty transactions too.
My Reports run during mon-fri and insert/update/delete happens during friday
night to sunday night.
So you are saying following thing,
If multiple users are attacking reports at same time during mon-fri and if i
put NOLOCK or SET TRANSACTION ISOLATION LEVEL UNCOMMITED, it will not help m
e
because in my case tables gets shared locks and they do not block read or
hurt performance, right ?
let me know if i misunderstand anything
"Andrew J. Kelly" wrote:
> OK I am not sure we are talking the same thing here or not. When I say th
e
> db is Read_only I mean you have actually done an ALTER DATABASE and set it
> to read_only. That is different than just saying that no one will edit an
y
> rows during. When the DB is put into this state it is truly READ_ONLY and
> as such the engine knows that it does not require any locks since there is
> no way the data will change. As such when you are in that state there is
no
> need to change the transaction level or to use NOLOCK. The whole purpose
of
> locking a row when you are editing it is so someone else doesn't edit that
> same row at the same time. Also so someone doesn't read a value that is no
t
> committed. When it is in READ_ONLY mode these conditions will never exist
> so locking is not required.
> But again, while this may help with memory utilization I don't think it is
> the root cause of your issues. If there is no editing going on the report
s
> are using SHARED locks. That means there is no blocking. Please refer to
> the links to get to the root cause.
> --
> Andrew J. Kelly SQL MVP
>
> "mvp" <mvp@.discussions.microsoft.com> wrote in message
> news:7E9A8245-DFE6-4237-A55A-200A3165335F@.microsoft.com...
>
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:573339
On Wed, 21 Dec 2005 14:49:02 -0800, mvp wrote:
>Hi Andrew,
>Thanks for the reply.
>So what i understand from your reply is that when database is in READ_ONLY
>mode, still we can update/delete/insert rows into db but when we read, it
>will not read only commited transaction, it may read dirty transactions too.[/color
]
Hi mvp,
No. If database is in READ_ONLY mode, all INSERT, UPDATE and DELETE
statements will fail. Only SELECT is permitted.
>My Reports run during mon-fri and insert/update/delete happens during frida
y
>night to sunday night.
>So you are saying following thing,
>If multiple users are attacking reports at same time during mon-fri and if
i
>put NOLOCK or SET TRANSACTION ISOLATION LEVEL UNCOMMITED, it will not help
me
>because in my case tables gets shared locks and they do not block read or
>hurt performance, right ?
No. If database is in READ_ONLY mode, SQL Server will not use any locks
at all. Adding NOLOCK or setting transaction level to read uncommited
has no effect at all, since no locks are taken anyway.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Subscribe to:
Posts (Atom)