Is there any way to get Standard SQL Server 2000 to use more than 2GB of
memory? I am running on a 2003 standard server w/the 3GB switch in the
boot.ini, but SQL still appears to only use 2GB of memory in the task manager.
Thanks> Is there any way to get Standard SQL Server 2000 to use more than 2GB of
> memory?
No. You need to step up to Enterprise Edition to use more than 2GB. See
'Maximum Capacity Specifications' in the Books Online for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt L." <Matt L.@.discussions.microsoft.com> wrote in message
news:6795FFF1-A0DB-40CB-82C5-35D3473FFB42@.microsoft.com...
> Is there any way to get Standard SQL Server 2000 to use more than 2GB of
> memory? I am running on a 2003 standard server w/the 3GB switch in the
> boot.ini, but SQL still appears to only use 2GB of memory in the task
> manager.
> Thanks|||OK, Thanks Dan
"Dan Guzman" wrote:
> > Is there any way to get Standard SQL Server 2000 to use more than 2GB of
> > memory?
> No. You need to step up to Enterprise Edition to use more than 2GB. See
> 'Maximum Capacity Specifications' in the Books Online for details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Matt L." <Matt L.@.discussions.microsoft.com> wrote in message
> news:6795FFF1-A0DB-40CB-82C5-35D3473FFB42@.microsoft.com...
> > Is there any way to get Standard SQL Server 2000 to use more than 2GB of
> > memory? I am running on a 2003 standard server w/the 3GB switch in the
> > boot.ini, but SQL still appears to only use 2GB of memory in the task
> > manager.
> >
> > Thanks
>
>
Showing posts with label standard. Show all posts
Showing posts with label standard. Show all posts
Wednesday, March 28, 2012
More Memory
Is there any way to get Standard SQL Server 2000 to use more than 2GB of
memory? I am running on a 2003 standard server w/the 3GB switch in the
boot.ini, but SQL still appears to only use 2GB of memory in the task manage
r.
Thanks> Is there any way to get Standard SQL Server 2000 to use more than 2GB of
> memory?
No. You need to step up to Enterprise Edition to use more than 2GB. See
'Maximum Capacity Specifications' in the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt L." <Matt L.@.discussions.microsoft.com> wrote in message
news:6795FFF1-A0DB-40CB-82C5-35D3473FFB42@.microsoft.com...
> Is there any way to get Standard SQL Server 2000 to use more than 2GB of
> memory? I am running on a 2003 standard server w/the 3GB switch in the
> boot.ini, but SQL still appears to only use 2GB of memory in the task
> manager.
> Thanks|||OK, Thanks Dan
"Dan Guzman" wrote:
> No. You need to step up to Enterprise Edition to use more than 2GB. See
> 'Maximum Capacity Specifications' in the Books Online for details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Matt L." <Matt L.@.discussions.microsoft.com> wrote in message
> news:6795FFF1-A0DB-40CB-82C5-35D3473FFB42@.microsoft.com...
>
>
memory? I am running on a 2003 standard server w/the 3GB switch in the
boot.ini, but SQL still appears to only use 2GB of memory in the task manage
r.
Thanks> Is there any way to get Standard SQL Server 2000 to use more than 2GB of
> memory?
No. You need to step up to Enterprise Edition to use more than 2GB. See
'Maximum Capacity Specifications' in the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Matt L." <Matt L.@.discussions.microsoft.com> wrote in message
news:6795FFF1-A0DB-40CB-82C5-35D3473FFB42@.microsoft.com...
> Is there any way to get Standard SQL Server 2000 to use more than 2GB of
> memory? I am running on a 2003 standard server w/the 3GB switch in the
> boot.ini, but SQL still appears to only use 2GB of memory in the task
> manager.
> Thanks|||OK, Thanks Dan
"Dan Guzman" wrote:
> No. You need to step up to Enterprise Edition to use more than 2GB. See
> 'Maximum Capacity Specifications' in the Books Online for details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Matt L." <Matt L.@.discussions.microsoft.com> wrote in message
> news:6795FFF1-A0DB-40CB-82C5-35D3473FFB42@.microsoft.com...
>
>
Friday, March 9, 2012
monitoring application performence
hi all
I'm running a sql 2000 standard edition server on win 2000 server.
one instance on a 3 gb memory, 2 cpus ,machine.
there are about 20 databases on the server; however I'm encounering a
problem with a spacific 500 mb db.
the problem is: 7 client stations located at a different city communicating
(only with)the problematic db on the server threw a single application via
iis. for the last few months the respons time is queit slow. tables are
reletivly small (the biggest is 60000 rows but the rest a only a few hunderd
each)
now... I'm trying yo isolate the problem.
my first question: is it true to say that since the rest of the applications
that are running against the other dbs are running smoothly the problem is
not at the server lelvel or at the network level?
also, opening the task mamager on the server machine I see that the cpu isnt
realy busy and that there is no paging.
I captured the a application queries with the profiler and I saw that the
cpu/ read/writes times are low.
there is no fragmentation on the indexes.
I also run the index tunning wizard with the profiler file. it indicated a
44% improvment and suggested 5 new indexes; but I still belive the problem
lays somewhere else.
and, the system guy, who is monitoring the network traffic, tells me it's
not the network as the bendwidth is 50% free.
so... where should I aim at next?
thanks,
alon.If the rest of the databases are doing fine except this one, then yes, the
problem is at the database level not at the server level.
You said, Index Tuning Wizard suggested new indexes, and the predicted
performance gain is substantial. I think you should go for it, after
testing.
To start with identify which stored procedures are running slow. Use
Profiler to identify the long running stored procedures (look at the
duration column, time is in milliseconds). once you identified the stored
procedures, try running them individually from Query Analyzer, with
'graphical execution plan' turned on. If you see index or table scans, then
the query is not written well, or there are no supporting indexes for that
query. Then try applying the indexes suggested by Index Tuning Wizard and
see if your query plan and response times improve.
Again, simply keep adding indexes is not really good for an OLTP system. if
you are trying to run report-like queries on an OLTP system, then you might
want to replicate the data to a different database, and run your queries
there. You could use transactional replication for this purpose. With
transactional replication the subscriber is only a few seconds behind the
publisher, in general.
Using Profiler, if you do find that the stored procedures and queries are
indeed running fine, the bottle neck is either the network or the
application.
Also check that there's no blockig going on in your database. You could use
sp_who or sp_who2 to see if any of the spids are getting blocked. Blocked
spid have a non-zero value in the 'blocked' column.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"alon" <alon@.discussions.microsoft.com> wrote in message
news:6467A9F4-A2C8-4EB8-9C2D-60286D564E6C@.microsoft.com...
> hi all
> I'm running a sql 2000 standard edition server on win 2000 server.
> one instance on a 3 gb memory, 2 cpus ,machine.
> there are about 20 databases on the server; however I'm encounering a
> problem with a spacific 500 mb db.
> the problem is: 7 client stations located at a different city
> communicating
> (only with)the problematic db on the server threw a single application
> via
> iis. for the last few months the respons time is queit slow. tables are
> reletivly small (the biggest is 60000 rows but the rest a only a few
> hunderd
> each)
> now... I'm trying yo isolate the problem.
> my first question: is it true to say that since the rest of the
> applications
> that are running against the other dbs are running smoothly the problem is
> not at the server lelvel or at the network level?
> also, opening the task mamager on the server machine I see that the cpu
> isnt
> realy busy and that there is no paging.
> I captured the a application queries with the profiler and I saw that the
> cpu/ read/writes times are low.
> there is no fragmentation on the indexes.
> I also run the index tunning wizard with the profiler file. it indicated a
> 44% improvment and suggested 5 new indexes; but I still belive the problem
> lays somewhere else.
> and, the system guy, who is monitoring the network traffic, tells me it's
> not the network as the bendwidth is 50% free.
> so... where should I aim at next?
> thanks,
> alon.
>
I'm running a sql 2000 standard edition server on win 2000 server.
one instance on a 3 gb memory, 2 cpus ,machine.
there are about 20 databases on the server; however I'm encounering a
problem with a spacific 500 mb db.
the problem is: 7 client stations located at a different city communicating
(only with)the problematic db on the server threw a single application via
iis. for the last few months the respons time is queit slow. tables are
reletivly small (the biggest is 60000 rows but the rest a only a few hunderd
each)
now... I'm trying yo isolate the problem.
my first question: is it true to say that since the rest of the applications
that are running against the other dbs are running smoothly the problem is
not at the server lelvel or at the network level?
also, opening the task mamager on the server machine I see that the cpu isnt
realy busy and that there is no paging.
I captured the a application queries with the profiler and I saw that the
cpu/ read/writes times are low.
there is no fragmentation on the indexes.
I also run the index tunning wizard with the profiler file. it indicated a
44% improvment and suggested 5 new indexes; but I still belive the problem
lays somewhere else.
and, the system guy, who is monitoring the network traffic, tells me it's
not the network as the bendwidth is 50% free.
so... where should I aim at next?
thanks,
alon.If the rest of the databases are doing fine except this one, then yes, the
problem is at the database level not at the server level.
You said, Index Tuning Wizard suggested new indexes, and the predicted
performance gain is substantial. I think you should go for it, after
testing.
To start with identify which stored procedures are running slow. Use
Profiler to identify the long running stored procedures (look at the
duration column, time is in milliseconds). once you identified the stored
procedures, try running them individually from Query Analyzer, with
'graphical execution plan' turned on. If you see index or table scans, then
the query is not written well, or there are no supporting indexes for that
query. Then try applying the indexes suggested by Index Tuning Wizard and
see if your query plan and response times improve.
Again, simply keep adding indexes is not really good for an OLTP system. if
you are trying to run report-like queries on an OLTP system, then you might
want to replicate the data to a different database, and run your queries
there. You could use transactional replication for this purpose. With
transactional replication the subscriber is only a few seconds behind the
publisher, in general.
Using Profiler, if you do find that the stored procedures and queries are
indeed running fine, the bottle neck is either the network or the
application.
Also check that there's no blockig going on in your database. You could use
sp_who or sp_who2 to see if any of the spids are getting blocked. Blocked
spid have a non-zero value in the 'blocked' column.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"alon" <alon@.discussions.microsoft.com> wrote in message
news:6467A9F4-A2C8-4EB8-9C2D-60286D564E6C@.microsoft.com...
> hi all
> I'm running a sql 2000 standard edition server on win 2000 server.
> one instance on a 3 gb memory, 2 cpus ,machine.
> there are about 20 databases on the server; however I'm encounering a
> problem with a spacific 500 mb db.
> the problem is: 7 client stations located at a different city
> communicating
> (only with)the problematic db on the server threw a single application
> via
> iis. for the last few months the respons time is queit slow. tables are
> reletivly small (the biggest is 60000 rows but the rest a only a few
> hunderd
> each)
> now... I'm trying yo isolate the problem.
> my first question: is it true to say that since the rest of the
> applications
> that are running against the other dbs are running smoothly the problem is
> not at the server lelvel or at the network level?
> also, opening the task mamager on the server machine I see that the cpu
> isnt
> realy busy and that there is no paging.
> I captured the a application queries with the profiler and I saw that the
> cpu/ read/writes times are low.
> there is no fragmentation on the indexes.
> I also run the index tunning wizard with the profiler file. it indicated a
> 44% improvment and suggested 5 new indexes; but I still belive the problem
> lays somewhere else.
> and, the system guy, who is monitoring the network traffic, tells me it's
> not the network as the bendwidth is 50% free.
> so... where should I aim at next?
> thanks,
> alon.
>
monitoring application performence
hi all
I'm running a sql 2000 standard edition server on win 2000 server.
one instance on a 3 gb memory, 2 cpus ,machine.
there are about 20 databases on the server; however I'm encounering a
problem with a spacific 500 mb db.
the problem is: 7 client stations located at a different city communicating
(only with)the problematic db on the server threw a single application via
iis. for the last few months the respons time is queit slow. tables are
reletivly small (the biggest is 60000 rows but the rest a only a few hunderd
each)
now... I'm trying yo isolate the problem.
my first question: is it true to say that since the rest of the applications
that are running against the other dbs are running smoothly the problem is
not at the server lelvel or at the network level?
also, opening the task mamager on the server machine I see that the cpu isnt
realy busy and that there is no paging.
I captured the a application queries with the profiler and I saw that the
cpu/ read/writes times are low.
there is no fragmentation on the indexes.
I also run the index tunning wizard with the profiler file. it indicated a
44% improvment and suggested 5 new indexes; but I still belive the problem
lays somewhere else.
and, the system guy, who is monitoring the network traffic, tells me it's
not the network as the bendwidth is 50% free.
so... where should I aim at next?
thanks,
alon.If the rest of the databases are doing fine except this one, then yes, the
problem is at the database level not at the server level.
You said, Index Tuning Wizard suggested new indexes, and the predicted
performance gain is substantial. I think you should go for it, after
testing.
To start with identify which stored procedures are running slow. Use
Profiler to identify the long running stored procedures (look at the
duration column, time is in milliseconds). once you identified the stored
procedures, try running them individually from Query Analyzer, with
'graphical execution plan' turned on. If you see index or table scans, then
the query is not written well, or there are no supporting indexes for that
query. Then try applying the indexes suggested by Index Tuning Wizard and
see if your query plan and response times improve.
Again, simply keep adding indexes is not really good for an OLTP system. if
you are trying to run report-like queries on an OLTP system, then you might
want to replicate the data to a different database, and run your queries
there. You could use transactional replication for this purpose. With
transactional replication the subscriber is only a few seconds behind the
publisher, in general.
Using Profiler, if you do find that the stored procedures and queries are
indeed running fine, the bottle neck is either the network or the
application.
Also check that there's no blockig going on in your database. You could use
sp_who or sp_who2 to see if any of the spids are getting blocked. Blocked
spid have a non-zero value in the 'blocked' column.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"alon" <alon@.discussions.microsoft.com> wrote in message
news:6467A9F4-A2C8-4EB8-9C2D-60286D564E6C@.microsoft.com...
> hi all
> I'm running a sql 2000 standard edition server on win 2000 server.
> one instance on a 3 gb memory, 2 cpus ,machine.
> there are about 20 databases on the server; however I'm encounering a
> problem with a spacific 500 mb db.
> the problem is: 7 client stations located at a different city
> communicating
> (only with)the problematic db on the server threw a single application
> via
> iis. for the last few months the respons time is queit slow. tables are
> reletivly small (the biggest is 60000 rows but the rest a only a few
> hunderd
> each)
> now... I'm trying yo isolate the problem.
> my first question: is it true to say that since the rest of the
> applications
> that are running against the other dbs are running smoothly the problem is
> not at the server lelvel or at the network level?
> also, opening the task mamager on the server machine I see that the cpu
> isnt
> realy busy and that there is no paging.
> I captured the a application queries with the profiler and I saw that the
> cpu/ read/writes times are low.
> there is no fragmentation on the indexes.
> I also run the index tunning wizard with the profiler file. it indicated a
> 44% improvment and suggested 5 new indexes; but I still belive the problem
> lays somewhere else.
> and, the system guy, who is monitoring the network traffic, tells me it's
> not the network as the bendwidth is 50% free.
> so... where should I aim at next?
> thanks,
> alon.
>
I'm running a sql 2000 standard edition server on win 2000 server.
one instance on a 3 gb memory, 2 cpus ,machine.
there are about 20 databases on the server; however I'm encounering a
problem with a spacific 500 mb db.
the problem is: 7 client stations located at a different city communicating
(only with)the problematic db on the server threw a single application via
iis. for the last few months the respons time is queit slow. tables are
reletivly small (the biggest is 60000 rows but the rest a only a few hunderd
each)
now... I'm trying yo isolate the problem.
my first question: is it true to say that since the rest of the applications
that are running against the other dbs are running smoothly the problem is
not at the server lelvel or at the network level?
also, opening the task mamager on the server machine I see that the cpu isnt
realy busy and that there is no paging.
I captured the a application queries with the profiler and I saw that the
cpu/ read/writes times are low.
there is no fragmentation on the indexes.
I also run the index tunning wizard with the profiler file. it indicated a
44% improvment and suggested 5 new indexes; but I still belive the problem
lays somewhere else.
and, the system guy, who is monitoring the network traffic, tells me it's
not the network as the bendwidth is 50% free.
so... where should I aim at next?
thanks,
alon.If the rest of the databases are doing fine except this one, then yes, the
problem is at the database level not at the server level.
You said, Index Tuning Wizard suggested new indexes, and the predicted
performance gain is substantial. I think you should go for it, after
testing.
To start with identify which stored procedures are running slow. Use
Profiler to identify the long running stored procedures (look at the
duration column, time is in milliseconds). once you identified the stored
procedures, try running them individually from Query Analyzer, with
'graphical execution plan' turned on. If you see index or table scans, then
the query is not written well, or there are no supporting indexes for that
query. Then try applying the indexes suggested by Index Tuning Wizard and
see if your query plan and response times improve.
Again, simply keep adding indexes is not really good for an OLTP system. if
you are trying to run report-like queries on an OLTP system, then you might
want to replicate the data to a different database, and run your queries
there. You could use transactional replication for this purpose. With
transactional replication the subscriber is only a few seconds behind the
publisher, in general.
Using Profiler, if you do find that the stored procedures and queries are
indeed running fine, the bottle neck is either the network or the
application.
Also check that there's no blockig going on in your database. You could use
sp_who or sp_who2 to see if any of the spids are getting blocked. Blocked
spid have a non-zero value in the 'blocked' column.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"alon" <alon@.discussions.microsoft.com> wrote in message
news:6467A9F4-A2C8-4EB8-9C2D-60286D564E6C@.microsoft.com...
> hi all
> I'm running a sql 2000 standard edition server on win 2000 server.
> one instance on a 3 gb memory, 2 cpus ,machine.
> there are about 20 databases on the server; however I'm encounering a
> problem with a spacific 500 mb db.
> the problem is: 7 client stations located at a different city
> communicating
> (only with)the problematic db on the server threw a single application
> via
> iis. for the last few months the respons time is queit slow. tables are
> reletivly small (the biggest is 60000 rows but the rest a only a few
> hunderd
> each)
> now... I'm trying yo isolate the problem.
> my first question: is it true to say that since the rest of the
> applications
> that are running against the other dbs are running smoothly the problem is
> not at the server lelvel or at the network level?
> also, opening the task mamager on the server machine I see that the cpu
> isnt
> realy busy and that there is no paging.
> I captured the a application queries with the profiler and I saw that the
> cpu/ read/writes times are low.
> there is no fragmentation on the indexes.
> I also run the index tunning wizard with the profiler file. it indicated a
> 44% improvment and suggested 5 new indexes; but I still belive the problem
> lays somewhere else.
> and, the system guy, who is monitoring the network traffic, tells me it's
> not the network as the bendwidth is 50% free.
> so... where should I aim at next?
> thanks,
> alon.
>
monitoring application performence
hi all
I'm running a sql 2000 standard edition server on win 2000 server.
one instance on a 3 gb memory, 2 cpus ,machine.
there are about 20 databases on the server; however I'm encounering a
problem with a spacific 500 mb db.
the problem is: 7 client stations located at a different city communicating
(only with)the problematic db on the server threw a single application via
iis. for the last few months the respons time is queit slow. tables are
reletivly small (the biggest is 60000 rows but the rest a only a few hunderd
each)
now... I'm trying yo isolate the problem.
my first question: is it true to say that since the rest of the applications
that are running against the other dbs are running smoothly the problem is
not at the server lelvel or at the network level?
also, opening the task mamager on the server machine I see that the cpu isnt
realy busy and that there is no paging.
I captured the a application queries with the profiler and I saw that the
cpu/ read/writes times are low.
there is no fragmentation on the indexes.
I also run the index tunning wizard with the profiler file. it indicated a
44% improvment and suggested 5 new indexes; but I still belive the problem
lays somewhere else.
and, the system guy, who is monitoring the network traffic, tells me it's
not the network as the bendwidth is 50% free.
so... where should I aim at next?
thanks,
alon.
If the rest of the databases are doing fine except this one, then yes, the
problem is at the database level not at the server level.
You said, Index Tuning Wizard suggested new indexes, and the predicted
performance gain is substantial. I think you should go for it, after
testing.
To start with identify which stored procedures are running slow. Use
Profiler to identify the long running stored procedures (look at the
duration column, time is in milliseconds). once you identified the stored
procedures, try running them individually from Query Analyzer, with
'graphical execution plan' turned on. If you see index or table scans, then
the query is not written well, or there are no supporting indexes for that
query. Then try applying the indexes suggested by Index Tuning Wizard and
see if your query plan and response times improve.
Again, simply keep adding indexes is not really good for an OLTP system. if
you are trying to run report-like queries on an OLTP system, then you might
want to replicate the data to a different database, and run your queries
there. You could use transactional replication for this purpose. With
transactional replication the subscriber is only a few seconds behind the
publisher, in general.
Using Profiler, if you do find that the stored procedures and queries are
indeed running fine, the bottle neck is either the network or the
application.
Also check that there's no blockig going on in your database. You could use
sp_who or sp_who2 to see if any of the spids are getting blocked. Blocked
spid have a non-zero value in the 'blocked' column.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"alon" <alon@.discussions.microsoft.com> wrote in message
news:6467A9F4-A2C8-4EB8-9C2D-60286D564E6C@.microsoft.com...
> hi all
> I'm running a sql 2000 standard edition server on win 2000 server.
> one instance on a 3 gb memory, 2 cpus ,machine.
> there are about 20 databases on the server; however I'm encounering a
> problem with a spacific 500 mb db.
> the problem is: 7 client stations located at a different city
> communicating
> (only with)the problematic db on the server threw a single application
> via
> iis. for the last few months the respons time is queit slow. tables are
> reletivly small (the biggest is 60000 rows but the rest a only a few
> hunderd
> each)
> now... I'm trying yo isolate the problem.
> my first question: is it true to say that since the rest of the
> applications
> that are running against the other dbs are running smoothly the problem is
> not at the server lelvel or at the network level?
> also, opening the task mamager on the server machine I see that the cpu
> isnt
> realy busy and that there is no paging.
> I captured the a application queries with the profiler and I saw that the
> cpu/ read/writes times are low.
> there is no fragmentation on the indexes.
> I also run the index tunning wizard with the profiler file. it indicated a
> 44% improvment and suggested 5 new indexes; but I still belive the problem
> lays somewhere else.
> and, the system guy, who is monitoring the network traffic, tells me it's
> not the network as the bendwidth is 50% free.
> so... where should I aim at next?
> thanks,
> alon.
>
I'm running a sql 2000 standard edition server on win 2000 server.
one instance on a 3 gb memory, 2 cpus ,machine.
there are about 20 databases on the server; however I'm encounering a
problem with a spacific 500 mb db.
the problem is: 7 client stations located at a different city communicating
(only with)the problematic db on the server threw a single application via
iis. for the last few months the respons time is queit slow. tables are
reletivly small (the biggest is 60000 rows but the rest a only a few hunderd
each)
now... I'm trying yo isolate the problem.
my first question: is it true to say that since the rest of the applications
that are running against the other dbs are running smoothly the problem is
not at the server lelvel or at the network level?
also, opening the task mamager on the server machine I see that the cpu isnt
realy busy and that there is no paging.
I captured the a application queries with the profiler and I saw that the
cpu/ read/writes times are low.
there is no fragmentation on the indexes.
I also run the index tunning wizard with the profiler file. it indicated a
44% improvment and suggested 5 new indexes; but I still belive the problem
lays somewhere else.
and, the system guy, who is monitoring the network traffic, tells me it's
not the network as the bendwidth is 50% free.
so... where should I aim at next?
thanks,
alon.
If the rest of the databases are doing fine except this one, then yes, the
problem is at the database level not at the server level.
You said, Index Tuning Wizard suggested new indexes, and the predicted
performance gain is substantial. I think you should go for it, after
testing.
To start with identify which stored procedures are running slow. Use
Profiler to identify the long running stored procedures (look at the
duration column, time is in milliseconds). once you identified the stored
procedures, try running them individually from Query Analyzer, with
'graphical execution plan' turned on. If you see index or table scans, then
the query is not written well, or there are no supporting indexes for that
query. Then try applying the indexes suggested by Index Tuning Wizard and
see if your query plan and response times improve.
Again, simply keep adding indexes is not really good for an OLTP system. if
you are trying to run report-like queries on an OLTP system, then you might
want to replicate the data to a different database, and run your queries
there. You could use transactional replication for this purpose. With
transactional replication the subscriber is only a few seconds behind the
publisher, in general.
Using Profiler, if you do find that the stored procedures and queries are
indeed running fine, the bottle neck is either the network or the
application.
Also check that there's no blockig going on in your database. You could use
sp_who or sp_who2 to see if any of the spids are getting blocked. Blocked
spid have a non-zero value in the 'blocked' column.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"alon" <alon@.discussions.microsoft.com> wrote in message
news:6467A9F4-A2C8-4EB8-9C2D-60286D564E6C@.microsoft.com...
> hi all
> I'm running a sql 2000 standard edition server on win 2000 server.
> one instance on a 3 gb memory, 2 cpus ,machine.
> there are about 20 databases on the server; however I'm encounering a
> problem with a spacific 500 mb db.
> the problem is: 7 client stations located at a different city
> communicating
> (only with)the problematic db on the server threw a single application
> via
> iis. for the last few months the respons time is queit slow. tables are
> reletivly small (the biggest is 60000 rows but the rest a only a few
> hunderd
> each)
> now... I'm trying yo isolate the problem.
> my first question: is it true to say that since the rest of the
> applications
> that are running against the other dbs are running smoothly the problem is
> not at the server lelvel or at the network level?
> also, opening the task mamager on the server machine I see that the cpu
> isnt
> realy busy and that there is no paging.
> I captured the a application queries with the profiler and I saw that the
> cpu/ read/writes times are low.
> there is no fragmentation on the indexes.
> I also run the index tunning wizard with the profiler file. it indicated a
> 44% improvment and suggested 5 new indexes; but I still belive the problem
> lays somewhere else.
> and, the system guy, who is monitoring the network traffic, tells me it's
> not the network as the bendwidth is 50% free.
> so... where should I aim at next?
> thanks,
> alon.
>
Subscribe to:
Posts (Atom)