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)
.