performance tips

got a friend with sql server 2000 that keeps running slow, he'll restart it
and it will run fine for awhile but will gradually get slower.
He's a developer that got DBA thrust upon him.
Wht are some performance tips to help fix this?
thankssome other links that are very useful
Overview of General SQL Server Performance methodology
http://support.microsoft.com/defaul...kb;en-us;298475
Online seminar on SQL2k Performance by a MS PSS guy who knows his stuff
http://support.microsoft.com/defaul...kb;en-us;838622
cheers,
Andy.
"mike w." <mikew@.discussions.microsoft.com> wrote in message
news:D93FCECC-D391-4FC1-95C6-BBEC8AF36109@.microsoft.com...
> got a friend with sql server 2000 that keeps running slow, he'll restart
it and it will run fine for awhile but will gradually get slower.
> He's a developer that got DBA thrust upon him.
> Wht are some performance tips to help fix this?
> thanks|||Great Links Andy. Thanks a ton. I have seen some other
links posted in below threads. Very useful!
Regards
Thirumal
www.thirumal.com
>--Original Message--
>some other links that are very useful
>Overview of General SQL Server Performance methodology
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;298475
>Online seminar on SQL2k Performance by a MS PSS guy who
knows his stuff
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;838622
>cheers,
>Andy.
>
>"mike w." <mikew@.discussions.microsoft.com> wrote in
message
>news:D93FCECC-D391-4FC1-95C6-BBEC8AF36109@.microsoft.com...
slow, he'll restart[vbcol=seagreen]
>it and it will run fine for awhile but will gradually get
slower.
>
>.
>

performance tips

got a friend with sql server 2000 that keeps running slow, he'll restart it and it will run fine for awhile but will gradually get slower.
He's a developer that got DBA thrust upon him.
Wht are some performance tips to help fix this?
thanksHi Mike,
You need to check whether any other applications running
on the same box are competing with SQL Server for memory
and CPU. Sounds he might be having blocking issues too.
With little information you provided its tough to guess
the problem. I suggest your friend to start here..
Great stuff on SQL Server performance tuning.
http://www.sql-server-performance.com
The Definitive GuideT to SQL Server Performance
Optimization eBook
http://www.veritas.com/Vrt/offer?
_requestid=28701&a_id=3807&
Also checkout the book must for a DBA on performance
tuning wrote by whalen and team at microsoft.
HTH
--
Regards
Thirumal
www.thirumal.com
>--Original Message--
>got a friend with sql server 2000 that keeps running
slow, he'll restart it and it will run fine for awhile but
will gradually get slower.
>He's a developer that got DBA thrust upon him.
>Wht are some performance tips to help fix this?
>thanks
>.
>|||some other links that are very useful
Overview of General SQL Server Performance methodology
http://support.microsoft.com/default.aspx?scid=kb;en-us;298475
Online seminar on SQL2k Performance by a MS PSS guy who knows his stuff
http://support.microsoft.com/default.aspx?scid=kb;en-us;838622
cheers,
Andy.
"mike w." <mikew@.discussions.microsoft.com> wrote in message
news:D93FCECC-D391-4FC1-95C6-BBEC8AF36109@.microsoft.com...
> got a friend with sql server 2000 that keeps running slow, he'll restart
it and it will run fine for awhile but will gradually get slower.
> He's a developer that got DBA thrust upon him.
> Wht are some performance tips to help fix this?
> thanks|||Great Links Andy. Thanks a ton. I have seen some other
links posted in below threads. Very useful!
Regards
Thirumal
www.thirumal.com
>--Original Message--
>some other links that are very useful
>Overview of General SQL Server Performance methodology
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;298475
>Online seminar on SQL2k Performance by a MS PSS guy who
knows his stuff
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;838622
>cheers,
>Andy.
>
>"mike w." <mikew@.discussions.microsoft.com> wrote in
message
>news:D93FCECC-D391-4FC1-95C6-BBEC8AF36109@.microsoft.com...
>> got a friend with sql server 2000 that keeps running
slow, he'll restart
>it and it will run fine for awhile but will gradually get
slower.
>> He's a developer that got DBA thrust upon him.
>> Wht are some performance tips to help fix this?
>> thanks
>
>.
>

performance tips

got a friend with sql server 2000 that keeps running slow, he'll restart it and it will run fine for awhile but will gradually get slower.
He's a developer that got DBA thrust upon him.
Wht are some performance tips to help fix this?
thanks
some other links that are very useful
Overview of General SQL Server Performance methodology
http://support.microsoft.com/default...b;en-us;298475
Online seminar on SQL2k Performance by a MS PSS guy who knows his stuff
http://support.microsoft.com/default...b;en-us;838622
cheers,
Andy.
"mike w." <mikew@.discussions.microsoft.com> wrote in message
news:D93FCECC-D391-4FC1-95C6-BBEC8AF36109@.microsoft.com...
> got a friend with sql server 2000 that keeps running slow, he'll restart
it and it will run fine for awhile but will gradually get slower.
> He's a developer that got DBA thrust upon him.
> Wht are some performance tips to help fix this?
> thanks
|||Great Links Andy. Thanks a ton. I have seen some other
links posted in below threads. Very useful!
Regards
Thirumal
www.thirumal.com
>--Original Message--
>some other links that are very useful
>Overview of General SQL Server Performance methodology
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;298475
>Online seminar on SQL2k Performance by a MS PSS guy who
knows his stuff
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;838622
>cheers,
>Andy.
>
>"mike w." <mikew@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:D93FCECC-D391-4FC1-95C6-BBEC8AF36109@.microsoft.com...
slow, he'll restart
>it and it will run fine for awhile but will gradually get
slower.
>
>.
>

performance problem

every 3-4 days I have to restart the sql service because some users are
complaining of poor performance in a specific form ( where they are
intensively add some small info but constantly with speed, and the process
of introduction is always behind where it should be ).
I made a test db where I restored the original db and I noticed that it's
working with normal speed in this parallel database.
My initial supposition was that it should work with the same low speed in
this new database, but it doesn't.
So I think this situation it's somewhat related with the database that it's
intensively worked with, and not with the memory of the server.
In 3-4 days the sql server process grows to about 500MB and if I let the
process for a week it goes to 800MB.
So to resume :
there is poor performance in 3-4 days from restart on a database that is
intensively worked on ,
but a normal performance on a duplicate database on the same server.
The server is a P4 at 2GHz with 1280MB of RAM and a scsi hard disk for
storing the databases.
Thanks for any clues
Fatboyslimro wrote:
> every 3-4 days I have to restart the sql service because some users
> are complaining of poor performance in a specific form ( where they
> are intensively add some small info but constantly with speed, and
> the process of introduction is always behind where it should be ).
> I made a test db where I restored the original db and I noticed that
> it's working with normal speed in this parallel database.
> My initial supposition was that it should work with the same low
> speed in this new database, but it doesn't.
> So I think this situation it's somewhat related with the database
> that it's intensively worked with, and not with the memory of the
> server.
> In 3-4 days the sql server process grows to about 500MB and if I let
> the process for a week it goes to 800MB.
>
> So to resume :
> there is poor performance in 3-4 days from restart on a database that
> is intensively worked on ,
> but a normal performance on a duplicate database on the same server.
>
> The server is a P4 at 2GHz with 1280MB of RAM and a scsi hard disk for
> storing the databases.
> Thanks for any clues
Memory issue sounds fine. SQL Server uses memory as needed up to its set
maximum or what's available in the OS and does not release memory
without a struggle. As more data is read into cache, the memory
footprint will increase. If you are running into a situation where the
OS needs more memory or you have other applications running on the same
server (lie IIS) and they are struggling, you need to set SQL Server to
only use a maximum amount of memory. You can do this easily from SQL
Enterprise Manager.
There could be other problems that are causing the slowness, however.
Report back to us whether you have other application running on the
server besides SQL Server.
David Gugick
Imceda Software
www.imceda.com
|||The server is also a file server and a DC.
But the thing is that in the same time that this database is running slow,
on a duplicate database on the same server the applications are running ok.
the sql server is limited to 800MB of RAM from a total of 1280MB
( before I made this duplicate database I thought that when it's going slow
on a database it will run slow on all the databases on the server - and I
wanted to do some maintenance operations on the duplicate db to see if this
is the problem, but if it runs slow on all the databases it isn't a problem
of indexing or something like this ).
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e7H8kE64EHA.2964@.TK2MSFTNGP15.phx.gbl...
> Fatboyslimro wrote:
> Memory issue sounds fine. SQL Server uses memory as needed up to its set
> maximum or what's available in the OS and does not release memory
> without a struggle. As more data is read into cache, the memory
> footprint will increase. If you are running into a situation where the
> OS needs more memory or you have other applications running on the same
> server (lie IIS) and they are struggling, you need to set SQL Server to
> only use a maximum amount of memory. You can do this easily from SQL
> Enterprise Manager.
> There could be other problems that are causing the slowness, however.
> Report back to us whether you have other application running on the
> server besides SQL Server.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Fatboyslimro wrote:
> The server is also a file server and a DC.
> But the thing is that in the same time that this database is running
> slow, on a duplicate database on the same server the applications are
> running ok.
> the sql server is limited to 800MB of RAM from a total of 1280MB
> ( before I made this duplicate database I thought that when it's
> going slow on a database it will run slow on all the databases on the
> server - and I wanted to do some maintenance operations on the
> duplicate db to see if this is the problem, but if it runs slow on
> all the databases it isn't a problem of indexing or something like
> this ).
>
By adding a second database (for testing?) you are putting further
strain on you memory resources. Why clog up the cache with duplicate
data from a second database?
You may want to update statistics on the database in question. It's not
clear what's going on, but it is clear that you are using your server
for file services and a domain controller and SQL Server and only have
1.2GB RAM. That may be enough (don't know your network and SQL specs),
but why not add more memory since it's so cheap. Putting 2GB RAM in
there would give you a little more headroom and then you could give the
server a little more than 400MB (which is not very much for a server).
David Gugick
Imceda Software
www.imceda.com
|||I don't think the problem is related to how much memory is in the server,
since 4 months ago when the server had only 512MB of RAM everything was ok.
Also, I don't think the problem is related to how much memory is in the
server because the same thing is happening when there is a lot of unused
memory ( say sql with 400MB and the rest for the system - which is a lot ).
I think we got to focus on the main issue: why on all the databases is
working ok, and only on one of them is working slowly after a couple of
days.
thanks for reply
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e2EoHkm5EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Fatboyslimro wrote:
> By adding a second database (for testing?) you are putting further
> strain on you memory resources. Why clog up the cache with duplicate
> data from a second database?
> You may want to update statistics on the database in question. It's not
> clear what's going on, but it is clear that you are using your server
> for file services and a domain controller and SQL Server and only have
> 1.2GB RAM. That may be enough (don't know your network and SQL specs),
> but why not add more memory since it's so cheap. Putting 2GB RAM in
> there would give you a little more headroom and then you could give the
> server a little more than 400MB (which is not very much for a server).
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

performance problem

every 3-4 days I have to restart the sql service because some users are
complaining of poor performance in a specific form ( where they are
intensively add some small info but constantly with speed, and the process
of introduction is always behind where it should be ).
I made a test db where I restored the original db and I noticed that it's
working with normal speed in this parallel database.
My initial supposition was that it should work with the same low speed in
this new database, but it doesn't.
So I think this situation it's somewhat related with the database that it's
intensively worked with, and not with the memory of the server.
In 3-4 days the sql server process grows to about 500MB and if I let the
process for a week it goes to 800MB.
So to resume :
there is poor performance in 3-4 days from restart on a database that is
intensively worked on ,
but a normal performance on a duplicate database on the same server.
The server is a P4 at 2GHz with 1280MB of RAM and a scsi hard disk for
storing the databases.
Thanks for any cluesFatboyslimro wrote:
> every 3-4 days I have to restart the sql service because some users
> are complaining of poor performance in a specific form ( where they
> are intensively add some small info but constantly with speed, and
> the process of introduction is always behind where it should be ).
> I made a test db where I restored the original db and I noticed that
> it's working with normal speed in this parallel database.
> My initial supposition was that it should work with the same low
> speed in this new database, but it doesn't.
> So I think this situation it's somewhat related with the database
> that it's intensively worked with, and not with the memory of the
> server.
> In 3-4 days the sql server process grows to about 500MB and if I let
> the process for a week it goes to 800MB.
>
> So to resume :
> there is poor performance in 3-4 days from restart on a database that
> is intensively worked on ,
> but a normal performance on a duplicate database on the same server.
>
> The server is a P4 at 2GHz with 1280MB of RAM and a scsi hard disk for
> storing the databases.
> Thanks for any clues
Memory issue sounds fine. SQL Server uses memory as needed up to its set
maximum or what's available in the OS and does not release memory
without a struggle. As more data is read into cache, the memory
footprint will increase. If you are running into a situation where the
OS needs more memory or you have other applications running on the same
server (lie IIS) and they are struggling, you need to set SQL Server to
only use a maximum amount of memory. You can do this easily from SQL
Enterprise Manager.
There could be other problems that are causing the slowness, however.
Report back to us whether you have other application running on the
server besides SQL Server.
David Gugick
Imceda Software
www.imceda.com|||The server is also a file server and a DC.
But the thing is that in the same time that this database is running slow,
on a duplicate database on the same server the applications are running ok.
the sql server is limited to 800MB of RAM from a total of 1280MB
( before I made this duplicate database I thought that when it's going slow
on a database it will run slow on all the databases on the server - and I
wanted to do some maintenance operations on the duplicate db to see if this
is the problem, but if it runs slow on all the databases it isn't a problem
of indexing or something like this ).
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e7H8kE64EHA.2964@.TK2MSFTNGP15.phx.gbl...
> Fatboyslimro wrote:
> > every 3-4 days I have to restart the sql service because some users
> > are complaining of poor performance in a specific form ( where they
> > are intensively add some small info but constantly with speed, and
> > the process of introduction is always behind where it should be ).
> > I made a test db where I restored the original db and I noticed that
> > it's working with normal speed in this parallel database.
> > My initial supposition was that it should work with the same low
> > speed in this new database, but it doesn't.
> > So I think this situation it's somewhat related with the database
> > that it's intensively worked with, and not with the memory of the
> > server.
> > In 3-4 days the sql server process grows to about 500MB and if I let
> > the process for a week it goes to 800MB.
> >
> >
> > So to resume :
> >
> > there is poor performance in 3-4 days from restart on a database that
> > is intensively worked on ,
> > but a normal performance on a duplicate database on the same server.
> >
> >
> > The server is a P4 at 2GHz with 1280MB of RAM and a scsi hard disk for
> > storing the databases.
> >
> > Thanks for any clues
> Memory issue sounds fine. SQL Server uses memory as needed up to its set
> maximum or what's available in the OS and does not release memory
> without a struggle. As more data is read into cache, the memory
> footprint will increase. If you are running into a situation where the
> OS needs more memory or you have other applications running on the same
> server (lie IIS) and they are struggling, you need to set SQL Server to
> only use a maximum amount of memory. You can do this easily from SQL
> Enterprise Manager.
> There could be other problems that are causing the slowness, however.
> Report back to us whether you have other application running on the
> server besides SQL Server.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Fatboyslimro wrote:
> The server is also a file server and a DC.
> But the thing is that in the same time that this database is running
> slow, on a duplicate database on the same server the applications are
> running ok.
> the sql server is limited to 800MB of RAM from a total of 1280MB
> ( before I made this duplicate database I thought that when it's
> going slow on a database it will run slow on all the databases on the
> server - and I wanted to do some maintenance operations on the
> duplicate db to see if this is the problem, but if it runs slow on
> all the databases it isn't a problem of indexing or something like
> this ).
>
By adding a second database (for testing?) you are putting further
strain on you memory resources. Why clog up the cache with duplicate
data from a second database?
You may want to update statistics on the database in question. It's not
clear what's going on, but it is clear that you are using your server
for file services and a domain controller and SQL Server and only have
1.2GB RAM. That may be enough (don't know your network and SQL specs),
but why not add more memory since it's so cheap. Putting 2GB RAM in
there would give you a little more headroom and then you could give the
server a little more than 400MB (which is not very much for a server).
David Gugick
Imceda Software
www.imceda.com|||I don't think the problem is related to how much memory is in the server,
since 4 months ago when the server had only 512MB of RAM everything was ok.
Also, I don't think the problem is related to how much memory is in the
server because the same thing is happening when there is a lot of unused
memory ( say sql with 400MB and the rest for the system - which is a lot ).
I think we got to focus on the main issue: why on all the databases is
working ok, and only on one of them is working slowly after a couple of
days.
thanks for reply
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:e2EoHkm5EHA.3756@.TK2MSFTNGP14.phx.gbl...
> Fatboyslimro wrote:
> > The server is also a file server and a DC.
> >
> > But the thing is that in the same time that this database is running
> > slow, on a duplicate database on the same server the applications are
> > running ok.
> >
> > the sql server is limited to 800MB of RAM from a total of 1280MB
> >
> > ( before I made this duplicate database I thought that when it's
> > going slow on a database it will run slow on all the databases on the
> > server - and I wanted to do some maintenance operations on the
> > duplicate db to see if this is the problem, but if it runs slow on
> > all the databases it isn't a problem of indexing or something like
> > this ).
> >
> By adding a second database (for testing?) you are putting further
> strain on you memory resources. Why clog up the cache with duplicate
> data from a second database?
> You may want to update statistics on the database in question. It's not
> clear what's going on, but it is clear that you are using your server
> for file services and a domain controller and SQL Server and only have
> 1.2GB RAM. That may be enough (don't know your network and SQL specs),
> but why not add more memory since it's so cheap. Putting 2GB RAM in
> there would give you a little more headroom and then you could give the
> server a little more than 400MB (which is not very much for a server).
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Performance Problem

After a server restart the server performance is much better
until the first backup at night occurs.
What could be the problem?
Thanks for some ideas!In article <eXY7Js$CIHA.748@.TK2MSFTNGP04.phx.gbl>, $Patrick.Gassmann
$@.selectchemie.ch says...
> After a server restart the server performance is much better
> until the first backup at night occurs.
> What could be the problem?
> Thanks for some ideas!
Maybe if you told us about the server, hardware, memory, typical memory
in use, what app you're using for backup, are you doing a backup to disk
and then disk to tape?
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
Did you monitor your server?
<$Patrick.Gassmann$@.selectchemie.ch> wrote in message
news:eXY7Js$CIHA.748@.TK2MSFTNGP04.phx.gbl...
> After a server restart the server performance is much better
> until the first backup at night occurs.
> What could be the problem?
> Thanks for some ideas!|||Windows 2000 SP4
MSSQL 2000 SP3 Standard Edition
Not Clustered
HP ML530 with 4 Raid 0 (System, Temp, LOG, Data)
Memory 3 GB
Memory used by SQL-Server 1.8 GB
Backup to Disk (not with a SQL-Agent) , Files are backuped
with BackupExec 9.1. (on the same server) to a tape.
Patrick|||Hi
I'm not fimilar with BackupExec 9.1 software, have you turned on SQL Server
Profiler while the backup is running?
<$Patrick.Gassmann$@.selectchemie.ch> wrote in message
news:unN8sSADIHA.4752@.TK2MSFTNGP04.phx.gbl...
> Windows 2000 SP4
> MSSQL 2000 SP3 Standard Edition
> Not Clustered
> HP ML530 with 4 Raid 0 (System, Temp, LOG, Data)
> Memory 3 GB
> Memory used by SQL-Server 1.8 GB
> Backup to Disk (not with a SQL-Agent) , Files are backuped
> with BackupExec 9.1. (on the same server) to a tape.
>
> Patrick
>
>|||Hi
I don't know how to use the profiler
>> Uri Dimant<urid@.iscar.co.il> 11.10.07 14:55:54 >>
Hi
I'm not fimilar with BackupExec 9.1 software, have you
turned on SQL Server
Profiler while the backup is running?
<$Patrick.Gassmann$@.selectchemie.ch> wrote in message
news:unN8sSADIHA.4752@.TK2MSFTNGP04.phx.gbl...
> Windows 2000 SP4
> MSSQL 2000 SP3 Standard Edition
> Not Clustered
> HP ML530 with 4 Raid 0 (System, Temp, LOG, Data)
> Memory 3 GB
> Memory used by SQL-Server 1.8 GB
> Backup to Disk (not with a SQL-Agent) , Files are
backuped
> with BackupExec 9.1. (on the same server) to a tape.
>
> Patrick
>
>

Performance Monitor Counters Disappeared

I rebooted my server (regular maint, every 2 month I just
restart it). When I started the performance monitor all
the counters that apply to SQL Server were gone. Without
reinstalling how do I get them back?Hi Gary,
What service pack do you have on SQL Server 2000? If it is SP 3, you want want to investigate if you are running into this problem.
FIX: "Performance Monitor Shared Memory Setup Failed: -1" Error Message ID: 812915.KB.EN-US
http://support.microsoft.com/?id=812915
Sincerely,
Yih-Yoon Lee [Microsoft]
Microsoft SQL Server Support
This posting is provided "AS IS" with no warranties, and confers no rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
--
| Content-Class: urn:content-classes:message
| From: "Gary" <gary.hughes@.mail.sprint.com>
| Sender: "Gary" <gary.hughes@.mail.sprint.com>
| Subject: Performance Monitor Counters Disappeared
| Date: Thu, 25 Sep 2003 09:10:53 -0700
| Lines: 4
| Message-ID: <172d01c3837f$9839dcc0$a001280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcODf5g5H2o4sj0ZSky9uKcnXc5MoA==| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:308334
| NNTP-Posting-Host: TK2MSFTNGXA08 10.40.1.160
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I rebooted my server (regular maint, every 2 month I just
| restart it). When I started the performance monitor all
| the counters that apply to SQL Server were gone. Without
| reinstalling how do I get them back?
|