I have a client that is having some performance issues with sql 2000
database. The application seems to move slow on the server as well as the
workstations so it does seem like a network or workstation issue. It seems
to be a sql issue. The server is a decent server with high P3 processor, 1
gig of memory, RAID 5 scsi drive array running 10k rpm. At this point they
are only "testing" this application, only a hand full of users are using
app. Any suggestions on performance tweaks would be great. They had a
puesdo-administrator that forced sql to use 500 megs of the 1 gig in the
server... other than that its all default.
thanks for the help,
rob
MOve the log files to somewhere which is mirrored and on a different
physical drive than the data...Also the Data files for your database should
be on a different physical drive than master, model, tempdb.
This sets you up in a position where you can get up to the minute recovery
with no loss of data (using the backup with notruncate option.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Microsoft" <temp@.dstek.com> wrote in message
news:e%23Yb%23b6VEHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have a client that is having some performance issues with sql 2000
> database. The application seems to move slow on the server as well as the
> workstations so it does seem like a network or workstation issue. It
seems
> to be a sql issue. The server is a decent server with high P3 processor,
1
> gig of memory, RAID 5 scsi drive array running 10k rpm. At this point
they
> are only "testing" this application, only a hand full of users are using
> app. Any suggestions on performance tweaks would be great. They had a
> puesdo-administrator that forced sql to use 500 megs of the 1 gig in the
> server... other than that its all default.
> thanks for the help,
> rob
>
|||maybe these links could help him in troubleshooting --
SQL Server 7.0 Performance Tuning Guide :
http://www.microsoft.com/SQL/techinf...tuninguide.htm
Proper SQL Server Configuration Settings :
http://support.microsoft.com/support.../Q166/9/67.ASP
Q175658 - How to gather Information for effective troubleshooting of
Performance :
http://support.microsoft.com/support.../Q175/6/58.ASP
Q224587 INF: Troubleshooting Application Performance with SQL Server
http://support.microsoft.com/support.../q224/5/87.asp
SQL Server Query Performance Troubleshooter :
http://support.microsoft.com/support...7queryperf.asp
Index Tuning Wizard 2000
http://msdn.microsoft.com/library/techart/itwforsql.htm
Troubleshooting the Index Tuning Wizard
http://msdn.microsoft.com/library/ps...tools_48ro.htm
Designing an Index
http://msdn.microsoft.com/library/ps...es_05_2ri0.htm
Thanks,
Vikram
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Showing posts with label slow. Show all posts
Showing posts with label slow. Show all posts
Friday, March 30, 2012
Performance Tweaks
I have a client that is having some performance issues with sql 2000
database. The application seems to move slow on the server as well as the
workstations so it does seem like a network or workstation issue. It seems
to be a sql issue. The server is a decent server with high P3 processor, 1
gig of memory, RAID 5 scsi drive array running 10k rpm. At this point they
are only "testing" this application, only a hand full of users are using
app. Any suggestions on performance tweaks would be great. They had a
puesdo-administrator that forced sql to use 500 megs of the 1 gig in the
server... other than that its all default.
thanks for the help,
robMOve the log files to somewhere which is mirrored and on a different
physical drive than the data...Also the Data files for your database should
be on a different physical drive than master, model, tempdb.
This sets you up in a position where you can get up to the minute recovery
with no loss of data (using the backup with notruncate option.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Microsoft" <temp@.dstek.com> wrote in message
news:e%23Yb%23b6VEHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have a client that is having some performance issues with sql 2000
> database. The application seems to move slow on the server as well as the
> workstations so it does seem like a network or workstation issue. It
seems
> to be a sql issue. The server is a decent server with high P3 processor,
1
> gig of memory, RAID 5 scsi drive array running 10k rpm. At this point
they
> are only "testing" this application, only a hand full of users are using
> app. Any suggestions on performance tweaks would be great. They had a
> puesdo-administrator that forced sql to use 500 megs of the 1 gig in the
> server... other than that its all default.
> thanks for the help,
> rob
>|||maybe these links could help him in troubleshooting --
SQL Server 7.0 Performance Tuning Guide :
http://www.microsoft.com/SQL/techinfo/perftuninguide.htm
Proper SQL Server Configuration Settings :
http://support.microsoft.com/support/kb/articles/Q166/9/67.ASP
Q175658 - How to gather Information for effective troubleshooting of
Performance :
http://support.microsoft.com/support/kb/articles/Q175/6/58.ASP
Q224587 INF: Troubleshooting Application Performance with SQL Server
http://support.microsoft.com/support/kb/articles/q224/5/87.asp
SQL Server Query Performance Troubleshooter :
http://support.microsoft.com/support/tshoot/sql7queryperf.asp
Index Tuning Wizard 2000
http://msdn.microsoft.com/library/techart/itwforsql.htm
Troubleshooting the Index Tuning Wizard
http://msdn.microsoft.com/library/psdk/sql/tr_servtools_48ro.htm
Designing an Index
http://msdn.microsoft.com/library/psdk/sql/cm_8_des_05_2ri0.htm
Thanks,
Vikram
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.sql
database. The application seems to move slow on the server as well as the
workstations so it does seem like a network or workstation issue. It seems
to be a sql issue. The server is a decent server with high P3 processor, 1
gig of memory, RAID 5 scsi drive array running 10k rpm. At this point they
are only "testing" this application, only a hand full of users are using
app. Any suggestions on performance tweaks would be great. They had a
puesdo-administrator that forced sql to use 500 megs of the 1 gig in the
server... other than that its all default.
thanks for the help,
robMOve the log files to somewhere which is mirrored and on a different
physical drive than the data...Also the Data files for your database should
be on a different physical drive than master, model, tempdb.
This sets you up in a position where you can get up to the minute recovery
with no loss of data (using the backup with notruncate option.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Microsoft" <temp@.dstek.com> wrote in message
news:e%23Yb%23b6VEHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have a client that is having some performance issues with sql 2000
> database. The application seems to move slow on the server as well as the
> workstations so it does seem like a network or workstation issue. It
seems
> to be a sql issue. The server is a decent server with high P3 processor,
1
> gig of memory, RAID 5 scsi drive array running 10k rpm. At this point
they
> are only "testing" this application, only a hand full of users are using
> app. Any suggestions on performance tweaks would be great. They had a
> puesdo-administrator that forced sql to use 500 megs of the 1 gig in the
> server... other than that its all default.
> thanks for the help,
> rob
>|||maybe these links could help him in troubleshooting --
SQL Server 7.0 Performance Tuning Guide :
http://www.microsoft.com/SQL/techinfo/perftuninguide.htm
Proper SQL Server Configuration Settings :
http://support.microsoft.com/support/kb/articles/Q166/9/67.ASP
Q175658 - How to gather Information for effective troubleshooting of
Performance :
http://support.microsoft.com/support/kb/articles/Q175/6/58.ASP
Q224587 INF: Troubleshooting Application Performance with SQL Server
http://support.microsoft.com/support/kb/articles/q224/5/87.asp
SQL Server Query Performance Troubleshooter :
http://support.microsoft.com/support/tshoot/sql7queryperf.asp
Index Tuning Wizard 2000
http://msdn.microsoft.com/library/techart/itwforsql.htm
Troubleshooting the Index Tuning Wizard
http://msdn.microsoft.com/library/psdk/sql/tr_servtools_48ro.htm
Designing an Index
http://msdn.microsoft.com/library/psdk/sql/cm_8_des_05_2ri0.htm
Thanks,
Vikram
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.sql
Performance Tweaks
I have a client that is having some performance issues with sql 2000
database. The application seems to move slow on the server as well as the
workstations so it does seem like a network or workstation issue. It seems
to be a sql issue. The server is a decent server with high P3 processor, 1
gig of memory, RAID 5 scsi drive array running 10k rpm. At this point they
are only "testing" this application, only a hand full of users are using
app. Any suggestions on performance tweaks would be great. They had a
puesdo-administrator that forced sql to use 500 megs of the 1 gig in the
server... other than that its all default.
thanks for the help,
rob500 MB memory is not much for SQL Server, however, it all depends on how
big is your database and how many concurrent users, it may be enough, it
may be not. Open perfmon to check if there's any paging going on on the
server. Also, do you have correct index? How did you configure your data
files? Is your application using alot stored procedures? Are those
stored procedures very CPU intensive?
Without any detail, it's hard to determine what's going on.
--
Eric Li
SQL DBA
MCDBA
Microsoft wrote:
> I have a client that is having some performance issues with sql 2000
> database. The application seems to move slow on the server as well as the
> workstations so it does seem like a network or workstation issue. It seems
> to be a sql issue. The server is a decent server with high P3 processor, 1
> gig of memory, RAID 5 scsi drive array running 10k rpm. At this point they
> are only "testing" this application, only a hand full of users are using
> app. Any suggestions on performance tweaks would be great. They had a
> puesdo-administrator that forced sql to use 500 megs of the 1 gig in the
> server... other than that its all default.
> thanks for the help,
> rob
>
>|||RAID 5 is slow for log files.
"Microsoft" <temp@.dstek.com> wrote in message
news:eB9SBc6VEHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have a client that is having some performance issues with sql 2000
> database. The application seems to move slow on the server as well as the
> workstations so it does seem like a network or workstation issue. It
seems
> to be a sql issue. The server is a decent server with high P3 processor,
1
> gig of memory, RAID 5 scsi drive array running 10k rpm. At this point
they
> are only "testing" this application, only a hand full of users are using
> app. Any suggestions on performance tweaks would be great. They had a
> puesdo-administrator that forced sql to use 500 megs of the 1 gig in the
> server... other than that its all default.
> thanks for the help,
> rob
>
>|||so are 10k drives.
you'll need to capture perfmon data and see where your bottlenecks are.
usual suspects.
RAM - 500mb is low and 1GB could be
CPU - Maybe you need additional CPUs
IO - RAID 5 with 10k Drives....that is rather slow. You may be Queueing up
IO requests
Indexes, bad sprocs, excessive blocking, deadlocks, etc
lots of work to do to figure this out
Greg Jackson
PDX, Oregon
database. The application seems to move slow on the server as well as the
workstations so it does seem like a network or workstation issue. It seems
to be a sql issue. The server is a decent server with high P3 processor, 1
gig of memory, RAID 5 scsi drive array running 10k rpm. At this point they
are only "testing" this application, only a hand full of users are using
app. Any suggestions on performance tweaks would be great. They had a
puesdo-administrator that forced sql to use 500 megs of the 1 gig in the
server... other than that its all default.
thanks for the help,
rob500 MB memory is not much for SQL Server, however, it all depends on how
big is your database and how many concurrent users, it may be enough, it
may be not. Open perfmon to check if there's any paging going on on the
server. Also, do you have correct index? How did you configure your data
files? Is your application using alot stored procedures? Are those
stored procedures very CPU intensive?
Without any detail, it's hard to determine what's going on.
--
Eric Li
SQL DBA
MCDBA
Microsoft wrote:
> I have a client that is having some performance issues with sql 2000
> database. The application seems to move slow on the server as well as the
> workstations so it does seem like a network or workstation issue. It seems
> to be a sql issue. The server is a decent server with high P3 processor, 1
> gig of memory, RAID 5 scsi drive array running 10k rpm. At this point they
> are only "testing" this application, only a hand full of users are using
> app. Any suggestions on performance tweaks would be great. They had a
> puesdo-administrator that forced sql to use 500 megs of the 1 gig in the
> server... other than that its all default.
> thanks for the help,
> rob
>
>|||RAID 5 is slow for log files.
"Microsoft" <temp@.dstek.com> wrote in message
news:eB9SBc6VEHA.3120@.TK2MSFTNGP12.phx.gbl...
> I have a client that is having some performance issues with sql 2000
> database. The application seems to move slow on the server as well as the
> workstations so it does seem like a network or workstation issue. It
seems
> to be a sql issue. The server is a decent server with high P3 processor,
1
> gig of memory, RAID 5 scsi drive array running 10k rpm. At this point
they
> are only "testing" this application, only a hand full of users are using
> app. Any suggestions on performance tweaks would be great. They had a
> puesdo-administrator that forced sql to use 500 megs of the 1 gig in the
> server... other than that its all default.
> thanks for the help,
> rob
>
>|||so are 10k drives.
you'll need to capture perfmon data and see where your bottlenecks are.
usual suspects.
RAM - 500mb is low and 1GB could be
CPU - Maybe you need additional CPUs
IO - RAID 5 with 10k Drives....that is rather slow. You may be Queueing up
IO requests
Indexes, bad sprocs, excessive blocking, deadlocks, etc
lots of work to do to figure this out
Greg Jackson
PDX, Oregon
Wednesday, March 28, 2012
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.
>
>.
>
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
>
>.
>
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.
>
>.
>
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 slow in Vista. Please help.
I have an application I'm trying to run under Vista using SQL 2005 as the
backend database. Right now the application is running super slow and I dont
know why. If I run SQL 2005 under a Virtual machine running xp, the app runs
fine but if I run the application with SQL 2005 under vista performance is
slow.
Configuration:
Vista all patches according to Windows Update
SQL 2005 - SP2
Doesnt work
Vista - running SQL 2005 in a VM under XP
Works
Can someone point me in the correct direction? Thanks in advance!!
MikeWhen an SQL-Server application is running slow, the first thing to do is to
make sure that the statistics are up to date by using sp_updatestats stored
procedure (or use UPDATE STATISTICS is you want to work on a more detailed
level) and cleaning the caches after that:
DBCC FLUSHPROCINDB
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
(See
http://sqlknowledge.com/index.php?option=com_content&task=view&id=65&Itemid=41 )
.
If this doesn't work, you can try reindexing everything:
http://blog.sqlauthority.com/2007/01/31/sql-server-reindexing-database-tables-and-update-statistics-on-tables/
Finally, things like using the option WITH RECOMPILE or playing with the
parameters sniffing (search this newsgroup with Google for more details) can
help you; as well as other things related to optimizing any sql-server
application.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:0EE6E1F8-2CEF-4B06-AE2B-B19F01D9C954@.microsoft.com...
>I have an application I'm trying to run under Vista using SQL 2005 as the
> backend database. Right now the application is running super slow and I
> dont
> know why. If I run SQL 2005 under a Virtual machine running xp, the app
> runs
> fine but if I run the application with SQL 2005 under vista performance is
> slow.
> Configuration:
> Vista all patches according to Windows Update
> SQL 2005 - SP2
> Doesnt work
>
> Vista - running SQL 2005 in a VM under XP
> Works
> Can someone point me in the correct direction? Thanks in advance!!
> Mike|||I have the same problem and I believe it has nothing to do with SQL Server
maintenance. It look to me that Vista has a bug when running SQL Server 2005.
I am running a .NET 2.0 winform application on a Vista machine. The app runs
about 200 updates on a single table. When updating the Vista SQL Server 2005
instance the time is more then double comparing to the time to complete when
updating the XP SQL Server 2005 instance. Plus the Vista machine is Intel
Core 2 Quad, while the XP machine is Intel Pentium D.
Alex
"Sylvain Lafontaine" wrote:
> When an SQL-Server application is running slow, the first thing to do is to
> make sure that the statistics are up to date by using sp_updatestats stored
> procedure (or use UPDATE STATISTICS is you want to work on a more detailed
> level) and cleaning the caches after that:
> DBCC FLUSHPROCINDB
> DBCC DROPCLEANBUFFERS
> DBCC FREEPROCCACHE
> (See
> http://sqlknowledge.com/index.php?option=com_content&task=view&id=65&Itemid=41 )
> ..
> If this doesn't work, you can try reindexing everything:
> http://blog.sqlauthority.com/2007/01/31/sql-server-reindexing-database-tables-and-update-statistics-on-tables/
> Finally, things like using the option WITH RECOMPILE or playing with the
> parameters sniffing (search this newsgroup with Google for more details) can
> help you; as well as other things related to optimizing any sql-server
> application.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:0EE6E1F8-2CEF-4B06-AE2B-B19F01D9C954@.microsoft.com...
> >I have an application I'm trying to run under Vista using SQL 2005 as the
> > backend database. Right now the application is running super slow and I
> > dont
> > know why. If I run SQL 2005 under a Virtual machine running xp, the app
> > runs
> > fine but if I run the application with SQL 2005 under vista performance is
> > slow.
> >
> > Configuration:
> >
> > Vista all patches according to Windows Update
> > SQL 2005 - SP2
> >
> > Doesnt work
> >
> >
> > Vista - running SQL 2005 in a VM under XP
> >
> > Works
> >
> > Can someone point me in the correct direction? Thanks in advance!!
> >
> > Mike
>
>
backend database. Right now the application is running super slow and I dont
know why. If I run SQL 2005 under a Virtual machine running xp, the app runs
fine but if I run the application with SQL 2005 under vista performance is
slow.
Configuration:
Vista all patches according to Windows Update
SQL 2005 - SP2
Doesnt work
Vista - running SQL 2005 in a VM under XP
Works
Can someone point me in the correct direction? Thanks in advance!!
MikeWhen an SQL-Server application is running slow, the first thing to do is to
make sure that the statistics are up to date by using sp_updatestats stored
procedure (or use UPDATE STATISTICS is you want to work on a more detailed
level) and cleaning the caches after that:
DBCC FLUSHPROCINDB
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
(See
http://sqlknowledge.com/index.php?option=com_content&task=view&id=65&Itemid=41 )
.
If this doesn't work, you can try reindexing everything:
http://blog.sqlauthority.com/2007/01/31/sql-server-reindexing-database-tables-and-update-statistics-on-tables/
Finally, things like using the option WITH RECOMPILE or playing with the
parameters sniffing (search this newsgroup with Google for more details) can
help you; as well as other things related to optimizing any sql-server
application.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:0EE6E1F8-2CEF-4B06-AE2B-B19F01D9C954@.microsoft.com...
>I have an application I'm trying to run under Vista using SQL 2005 as the
> backend database. Right now the application is running super slow and I
> dont
> know why. If I run SQL 2005 under a Virtual machine running xp, the app
> runs
> fine but if I run the application with SQL 2005 under vista performance is
> slow.
> Configuration:
> Vista all patches according to Windows Update
> SQL 2005 - SP2
> Doesnt work
>
> Vista - running SQL 2005 in a VM under XP
> Works
> Can someone point me in the correct direction? Thanks in advance!!
> Mike|||I have the same problem and I believe it has nothing to do with SQL Server
maintenance. It look to me that Vista has a bug when running SQL Server 2005.
I am running a .NET 2.0 winform application on a Vista machine. The app runs
about 200 updates on a single table. When updating the Vista SQL Server 2005
instance the time is more then double comparing to the time to complete when
updating the XP SQL Server 2005 instance. Plus the Vista machine is Intel
Core 2 Quad, while the XP machine is Intel Pentium D.
Alex
"Sylvain Lafontaine" wrote:
> When an SQL-Server application is running slow, the first thing to do is to
> make sure that the statistics are up to date by using sp_updatestats stored
> procedure (or use UPDATE STATISTICS is you want to work on a more detailed
> level) and cleaning the caches after that:
> DBCC FLUSHPROCINDB
> DBCC DROPCLEANBUFFERS
> DBCC FREEPROCCACHE
> (See
> http://sqlknowledge.com/index.php?option=com_content&task=view&id=65&Itemid=41 )
> ..
> If this doesn't work, you can try reindexing everything:
> http://blog.sqlauthority.com/2007/01/31/sql-server-reindexing-database-tables-and-update-statistics-on-tables/
> Finally, things like using the option WITH RECOMPILE or playing with the
> parameters sniffing (search this newsgroup with Google for more details) can
> help you; as well as other things related to optimizing any sql-server
> application.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:0EE6E1F8-2CEF-4B06-AE2B-B19F01D9C954@.microsoft.com...
> >I have an application I'm trying to run under Vista using SQL 2005 as the
> > backend database. Right now the application is running super slow and I
> > dont
> > know why. If I run SQL 2005 under a Virtual machine running xp, the app
> > runs
> > fine but if I run the application with SQL 2005 under vista performance is
> > slow.
> >
> > Configuration:
> >
> > Vista all patches according to Windows Update
> > SQL 2005 - SP2
> >
> > Doesnt work
> >
> >
> > Vista - running SQL 2005 in a VM under XP
> >
> > Works
> >
> > Can someone point me in the correct direction? Thanks in advance!!
> >
> > Mike
>
>
performance slow down at a regurlar basis...
Hi,
I've a strange behavior on my server.
The response time is good after a reboot of the computer, but, after a long
work on it (half day or a day)
The performance slow down, so I reboot the server to recover my performance.
Why?
How to diagnostic this problem?
Thanks.
Jerome.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.595 / Virus Database: 378 - Release Date: 2004-02-25Have a look at perfmon
If you have to do this every day/after using it a while then a resource is
being depleted seriously and a reboot frees it up again.
Look at Memory, Processor to start with.
What version and SP of SQL Server ?
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Jj" <willgart@._A_hAotmail_A_.com> wrote in message
news:e6Ke4vrCEHA.2908@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've a strange behavior on my server.
> The response time is good after a reboot of the computer, but, after a
long
> work on it (half day or a day)
> The performance slow down, so I reboot the server to recover my
performance.
> Why?
> How to diagnostic this problem?
> Thanks.
> Jerome.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.595 / Virus Database: 378 - Release Date: 2004-02-25
>|||Fairly Normal with Windows
"Jj" <willgart@._A_hAotmail_A_.com> wrote in message
news:e6Ke4vrCEHA.2908@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've a strange behavior on my server.
> The response time is good after a reboot of the computer, but, after a
long
> work on it (half day or a day)
> The performance slow down, so I reboot the server to recover my
performance.
> Why?
> How to diagnostic this problem?
> Thanks.
> Jerome.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.595 / Virus Database: 378 - Release Date: 2004-02-25
>|||I've the SP3 on a Windows 2000 Server + SP4 server.
I've 3 drives, and I've splitted differents database files on these 3 drives
(tempdb on 1; data of my data warehouse on 2; data warehouse log file on 3)
"Allan Mitchell" <allan@.no-spam.sqldts.com> a crit dans le message de
news:OCcnxMCDEHA.580@.TK2MSFTNGP11.phx.gbl...
> Have a look at perfmon
> If you have to do this every day/after using it a while then a resource is
> being depleted seriously and a reboot frees it up again.
> Look at Memory, Processor to start with.
> What version and SP of SQL Server ?
>
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "Jj" <willgart@._A_hAotmail_A_.com> wrote in message
> news:e6Ke4vrCEHA.2908@.TK2MSFTNGP09.phx.gbl...
> long
> performance.
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.595 / Virus Database: 378 - Release Date: 2004-02-25
I've a strange behavior on my server.
The response time is good after a reboot of the computer, but, after a long
work on it (half day or a day)
The performance slow down, so I reboot the server to recover my performance.
Why?
How to diagnostic this problem?
Thanks.
Jerome.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.595 / Virus Database: 378 - Release Date: 2004-02-25Have a look at perfmon
If you have to do this every day/after using it a while then a resource is
being depleted seriously and a reboot frees it up again.
Look at Memory, Processor to start with.
What version and SP of SQL Server ?
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Jj" <willgart@._A_hAotmail_A_.com> wrote in message
news:e6Ke4vrCEHA.2908@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've a strange behavior on my server.
> The response time is good after a reboot of the computer, but, after a
long
> work on it (half day or a day)
> The performance slow down, so I reboot the server to recover my
performance.
> Why?
> How to diagnostic this problem?
> Thanks.
> Jerome.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.595 / Virus Database: 378 - Release Date: 2004-02-25
>|||Fairly Normal with Windows
"Jj" <willgart@._A_hAotmail_A_.com> wrote in message
news:e6Ke4vrCEHA.2908@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I've a strange behavior on my server.
> The response time is good after a reboot of the computer, but, after a
long
> work on it (half day or a day)
> The performance slow down, so I reboot the server to recover my
performance.
> Why?
> How to diagnostic this problem?
> Thanks.
> Jerome.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.595 / Virus Database: 378 - Release Date: 2004-02-25
>|||I've the SP3 on a Windows 2000 Server + SP4 server.
I've 3 drives, and I've splitted differents database files on these 3 drives
(tempdb on 1; data of my data warehouse on 2; data warehouse log file on 3)
"Allan Mitchell" <allan@.no-spam.sqldts.com> a crit dans le message de
news:OCcnxMCDEHA.580@.TK2MSFTNGP11.phx.gbl...
> Have a look at perfmon
> If you have to do this every day/after using it a while then a resource is
> being depleted seriously and a reboot frees it up again.
> Look at Memory, Processor to start with.
> What version and SP of SQL Server ?
>
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "Jj" <willgart@._A_hAotmail_A_.com> wrote in message
> news:e6Ke4vrCEHA.2908@.TK2MSFTNGP09.phx.gbl...
> long
> performance.
>
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.595 / Virus Database: 378 - Release Date: 2004-02-25
Monday, March 26, 2012
Performance Question
Hi!
If I have a table in SQL 2000 with 500,000 registry and I do one query to
that table, what shall be the answer time? Is to slow? Should I separate the
data for more tables?
I am structuralizing one db.
Thanks
Albano Alves
Impossible to answer. It depends on:
A) What the datatypes of your columns are / how many columns there are / how
"wide" the table is
B) What indexes are created on the table / how the indexes are being used /
whether statistics are up to date
C) What kind of hardware you have: Disks / disk configuration / memory /
processor
D) What other activity is happening on the server
So to answer your question: Test it on your end.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate
the
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>
|||This depends upon a lot of factors such as the where query the index's on
your whether its using a cursor table, the speed of your hard disks, the
state of fragmentation,of your data files, and so in.
Please post the table structure then the query so we can have a look at it
and sugest improvments.
Formally 'Peter The Spate'
"All generalizations are false, including this one."
Mark Twain
"Albano Alves" wrote:
> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate the
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>
>
|||I have more or less 20 field (varchar and int), and the ID can be related
with another Table. The server is a good machine, but it will have many
users, more or less 1,000
In my tests I feel that how much bigger will be I number it of returned
data, minor is the performance and that when to only return a small one
number of registers the performance is good. That is truth?
My debt is if I should have one alone table for, suppliers, customers and
all stakeholders... or some tables, one for each stakeholder.
Thanks
Albano Alves
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escreveu na mensagem
news:eCOVBj%23IFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Impossible to answer. It depends on:
> A) What the datatypes of your columns are / how many columns there are /
> how
> "wide" the table is
> B) What indexes are created on the table / how the indexes are being used
> /
> whether statistics are up to date
> C) What kind of hardware you have: Disks / disk configuration / memory /
> processor
> D) What other activity is happening on the server
> So to answer your question: Test it on your end.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
> news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
> the
>
|||"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:ejb%23uF$IFHA.2844@.TK2MSFTNGP10.phx.gbl...
> My debt is if I should have one alone table for, suppliers, customers and
> all stakeholders... or some tables, one for each stakeholder.
I don't recommend that -- it will mean that your application or stored
procedure will have to figure out what table to query at runtime, or you'll
have to have a bunch of partitioned views that you update every time data
changes. It will be a maintenence nightmare. Try to solve issues with
indexes first.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
If I have a table in SQL 2000 with 500,000 registry and I do one query to
that table, what shall be the answer time? Is to slow? Should I separate the
data for more tables?
I am structuralizing one db.
Thanks
Albano Alves
Impossible to answer. It depends on:
A) What the datatypes of your columns are / how many columns there are / how
"wide" the table is
B) What indexes are created on the table / how the indexes are being used /
whether statistics are up to date
C) What kind of hardware you have: Disks / disk configuration / memory /
processor
D) What other activity is happening on the server
So to answer your question: Test it on your end.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate
the
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>
|||This depends upon a lot of factors such as the where query the index's on
your whether its using a cursor table, the speed of your hard disks, the
state of fragmentation,of your data files, and so in.
Please post the table structure then the query so we can have a look at it
and sugest improvments.
Formally 'Peter The Spate'
"All generalizations are false, including this one."
Mark Twain
"Albano Alves" wrote:
> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate the
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>
>
|||I have more or less 20 field (varchar and int), and the ID can be related
with another Table. The server is a good machine, but it will have many
users, more or less 1,000
In my tests I feel that how much bigger will be I number it of returned
data, minor is the performance and that when to only return a small one
number of registers the performance is good. That is truth?
My debt is if I should have one alone table for, suppliers, customers and
all stakeholders... or some tables, one for each stakeholder.
Thanks
Albano Alves
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escreveu na mensagem
news:eCOVBj%23IFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Impossible to answer. It depends on:
> A) What the datatypes of your columns are / how many columns there are /
> how
> "wide" the table is
> B) What indexes are created on the table / how the indexes are being used
> /
> whether statistics are up to date
> C) What kind of hardware you have: Disks / disk configuration / memory /
> processor
> D) What other activity is happening on the server
> So to answer your question: Test it on your end.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
> news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
> the
>
|||"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:ejb%23uF$IFHA.2844@.TK2MSFTNGP10.phx.gbl...
> My debt is if I should have one alone table for, suppliers, customers and
> all stakeholders... or some tables, one for each stakeholder.
I don't recommend that -- it will mean that your application or stored
procedure will have to figure out what table to query at runtime, or you'll
have to have a bunch of partitioned views that you update every time data
changes. It will be a maintenence nightmare. Try to solve issues with
indexes first.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
Friday, March 23, 2012
Performance Question
Hi!
If I have a table in SQL 2000 with 500,000 registry and I do one query to
that table, what shall be the answer time? Is to slow? Should I separate the
data for more tables?
I am structuralizing one db.
Thanks
Albano AlvesImpossible to answer. It depends on:
A) What the datatypes of your columns are / how many columns there are / how
"wide" the table is
B) What indexes are created on the table / how the indexes are being used /
whether statistics are up to date
C) What kind of hardware you have: Disks / disk configuration / memory /
processor
D) What other activity is happening on the server
So to answer your question: Test it on your end.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate
the
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>|||This depends upon a lot of factors such as the where query the index's on
your whether its using a cursor table, the speed of your hard disks, the
state of fragmentation,of your data files, and so in.
Please post the table structure then the query so we can have a look at it
and sugest improvments.
Formally 'Peter The Spate'
"All generalizations are false, including this one."
Mark Twain
"Albano Alves" wrote:
> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate the
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>
>|||I have more or less 20 field (varchar and int), and the ID can be related
with another Table. The server is a good machine, but it will have many
users, more or less 1,000
In my tests I feel that how much bigger will be I number it of returned
data, minor is the performance and that when to only return a small one
number of registers the performance is good. That is truth?
My debt is if I should have one alone table for, suppliers, customers and
all stakeholders... or some tables, one for each stakeholder.
Thanks
Albano Alves
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escreveu na mensagem
news:eCOVBj%23IFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Impossible to answer. It depends on:
> A) What the datatypes of your columns are / how many columns there are /
> how
> "wide" the table is
> B) What indexes are created on the table / how the indexes are being used
> /
> whether statistics are up to date
> C) What kind of hardware you have: Disks / disk configuration / memory /
> processor
> D) What other activity is happening on the server
> So to answer your question: Test it on your end.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
> news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
>> Hi!
>> If I have a table in SQL 2000 with 500,000 registry and I do one query to
>> that table, what shall be the answer time? Is to slow? Should I separate
> the
>> data for more tables?
>> I am structuralizing one db.
>> Thanks
>> Albano Alves
>>
>|||"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:ejb%23uF$IFHA.2844@.TK2MSFTNGP10.phx.gbl...
> My debt is if I should have one alone table for, suppliers, customers and
> all stakeholders... or some tables, one for each stakeholder.
I don't recommend that -- it will mean that your application or stored
procedure will have to figure out what table to query at runtime, or you'll
have to have a bunch of partitioned views that you update every time data
changes. It will be a maintenence nightmare. Try to solve issues with
indexes first.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
If I have a table in SQL 2000 with 500,000 registry and I do one query to
that table, what shall be the answer time? Is to slow? Should I separate the
data for more tables?
I am structuralizing one db.
Thanks
Albano AlvesImpossible to answer. It depends on:
A) What the datatypes of your columns are / how many columns there are / how
"wide" the table is
B) What indexes are created on the table / how the indexes are being used /
whether statistics are up to date
C) What kind of hardware you have: Disks / disk configuration / memory /
processor
D) What other activity is happening on the server
So to answer your question: Test it on your end.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate
the
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>|||This depends upon a lot of factors such as the where query the index's on
your whether its using a cursor table, the speed of your hard disks, the
state of fragmentation,of your data files, and so in.
Please post the table structure then the query so we can have a look at it
and sugest improvments.
Formally 'Peter The Spate'
"All generalizations are false, including this one."
Mark Twain
"Albano Alves" wrote:
> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate the
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>
>|||I have more or less 20 field (varchar and int), and the ID can be related
with another Table. The server is a good machine, but it will have many
users, more or less 1,000
In my tests I feel that how much bigger will be I number it of returned
data, minor is the performance and that when to only return a small one
number of registers the performance is good. That is truth?
My debt is if I should have one alone table for, suppliers, customers and
all stakeholders... or some tables, one for each stakeholder.
Thanks
Albano Alves
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escreveu na mensagem
news:eCOVBj%23IFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Impossible to answer. It depends on:
> A) What the datatypes of your columns are / how many columns there are /
> how
> "wide" the table is
> B) What indexes are created on the table / how the indexes are being used
> /
> whether statistics are up to date
> C) What kind of hardware you have: Disks / disk configuration / memory /
> processor
> D) What other activity is happening on the server
> So to answer your question: Test it on your end.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
> news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
>> Hi!
>> If I have a table in SQL 2000 with 500,000 registry and I do one query to
>> that table, what shall be the answer time? Is to slow? Should I separate
> the
>> data for more tables?
>> I am structuralizing one db.
>> Thanks
>> Albano Alves
>>
>|||"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:ejb%23uF$IFHA.2844@.TK2MSFTNGP10.phx.gbl...
> My debt is if I should have one alone table for, suppliers, customers and
> all stakeholders... or some tables, one for each stakeholder.
I don't recommend that -- it will mean that your application or stored
procedure will have to figure out what table to query at runtime, or you'll
have to have a bunch of partitioned views that you update every time data
changes. It will be a maintenence nightmare. Try to solve issues with
indexes first.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
Performance Question
Hi!
If I have a table in SQL 2000 with 500,000 registry and I do one query to
that table, what shall be the answer time? Is to slow? Should I separate the
data for more tables?
I am structuralizing one db.
Thanks
Albano AlvesImpossible to answer. It depends on:
A) What the datatypes of your columns are / how many columns there are / how
"wide" the table is
B) What indexes are created on the table / how the indexes are being used /
whether statistics are up to date
C) What kind of hardware you have: Disks / disk configuration / memory /
processor
D) What other activity is happening on the server
So to answer your question: Test it on your end.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate
the
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>|||This depends upon a lot of factors such as the where query the index's on
your whether its using a cursor table, the speed of your hard disks, the
state of fragmentation,of your data files, and so in.
Please post the table structure then the query so we can have a look at it
and sugest improvments.
Formally 'Peter The Spate'
"All generalizations are false, including this one."
Mark Twain
"Albano Alves" wrote:
> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate t
he
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>
>|||I have more or less 20 field (varchar and int), and the ID can be related
with another Table. The server is a good machine, but it will have many
users, more or less 1,000
In my tests I feel that how much bigger will be I number it of returned
data, minor is the performance and that when to only return a small one
number of registers the performance is good. That is truth?
My debt is if I should have one alone table for, suppliers, customers and
all stakeholders... or some tables, one for each stakeholder.
Thanks
Albano Alves
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escreveu na mensagem
news:eCOVBj%23IFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Impossible to answer. It depends on:
> A) What the datatypes of your columns are / how many columns there are /
> how
> "wide" the table is
> B) What indexes are created on the table / how the indexes are being used
> /
> whether statistics are up to date
> C) What kind of hardware you have: Disks / disk configuration / memory /
> processor
> D) What other activity is happening on the server
> So to answer your question: Test it on your end.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
> news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
> the
>|||"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:ejb%23uF$IFHA.2844@.TK2MSFTNGP10.phx.gbl...
> My debt is if I should have one alone table for, suppliers, customers and
> all stakeholders... or some tables, one for each stakeholder.
I don't recommend that -- it will mean that your application or stored
procedure will have to figure out what table to query at runtime, or you'll
have to have a bunch of partitioned views that you update every time data
changes. It will be a maintenence nightmare. Try to solve issues with
indexes first.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
If I have a table in SQL 2000 with 500,000 registry and I do one query to
that table, what shall be the answer time? Is to slow? Should I separate the
data for more tables?
I am structuralizing one db.
Thanks
Albano AlvesImpossible to answer. It depends on:
A) What the datatypes of your columns are / how many columns there are / how
"wide" the table is
B) What indexes are created on the table / how the indexes are being used /
whether statistics are up to date
C) What kind of hardware you have: Disks / disk configuration / memory /
processor
D) What other activity is happening on the server
So to answer your question: Test it on your end.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate
the
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>|||This depends upon a lot of factors such as the where query the index's on
your whether its using a cursor table, the speed of your hard disks, the
state of fragmentation,of your data files, and so in.
Please post the table structure then the query so we can have a look at it
and sugest improvments.
Formally 'Peter The Spate'
"All generalizations are false, including this one."
Mark Twain
"Albano Alves" wrote:
> Hi!
> If I have a table in SQL 2000 with 500,000 registry and I do one query to
> that table, what shall be the answer time? Is to slow? Should I separate t
he
> data for more tables?
> I am structuralizing one db.
> Thanks
> Albano Alves
>
>|||I have more or less 20 field (varchar and int), and the ID can be related
with another Table. The server is a good machine, but it will have many
users, more or less 1,000
In my tests I feel that how much bigger will be I number it of returned
data, minor is the performance and that when to only return a small one
number of registers the performance is good. That is truth?
My debt is if I should have one alone table for, suppliers, customers and
all stakeholders... or some tables, one for each stakeholder.
Thanks
Albano Alves
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> escreveu na mensagem
news:eCOVBj%23IFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Impossible to answer. It depends on:
> A) What the datatypes of your columns are / how many columns there are /
> how
> "wide" the table is
> B) What indexes are created on the table / how the indexes are being used
> /
> whether statistics are up to date
> C) What kind of hardware you have: Disks / disk configuration / memory /
> processor
> D) What other activity is happening on the server
> So to answer your question: Test it on your end.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
> news:egT7h69IFHA.1860@.TK2MSFTNGP15.phx.gbl...
> the
>|||"Albano Alves" <albano.alves@.vpconsulting.pt> wrote in message
news:ejb%23uF$IFHA.2844@.TK2MSFTNGP10.phx.gbl...
> My debt is if I should have one alone table for, suppliers, customers and
> all stakeholders... or some tables, one for each stakeholder.
I don't recommend that -- it will mean that your application or stored
procedure will have to figure out what table to query at runtime, or you'll
have to have a bunch of partitioned views that you update every time data
changes. It will be a maintenence nightmare. Try to solve issues with
indexes first.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
Performance problems with SQL commands in data flow task
SQL statement within an OLE DB Command component is extremely slow (hours, days). Same SQL statement executed within a query window of SQL Server Management Studio takes only a few seconds. Using a fairly simple SQL UPDATE statement against a table with only 21,000 rows. Query:
UPDATE Pearson_Load
SET Process_Flag = 'E',
Error_Msg = 'Error: Missing address elements Address_Line_1, City, and/or State'
WHERE (Address_Line_1 = ' '
OR City = ' '
OR State = ' ')
AND Process_Flag = ' '
Any suggestions on how to improve the performance of this task or an alternate solution are appreciated. Thank you.
Jeff-B wrote:
SQL statement within an OLE DB Command component is extremely slow (hours, days). Same SQL statement executed within a query window of SQL Server Management Studio takes only a few seconds. Using a fairly simple SQL UPDATE statement against a table with only 21,000 rows. Query:
UPDATE Pearson_Load
SET Process_Flag = 'E',
Error_Msg = 'Error: Missing address elements Address_Line_1, City, and/or State'
WHERE (Address_Line_1 = ' '
OR City = ' '
OR State = ' ')
AND Process_Flag = ' 'Any suggestions on how to improve the performance of this task or an alternate solution are appreciated. Thank you.
You should redirect those rows destined for update to a table and then use an Execute SQL task in the control flow to perform a set-based update. What you've got now is a new, distinct update command for every row on the update path. This is costly.|||
Thank you Phil! I just moved the queries (I actually had 4 separate queries) that I was executing as separate OLE DB Command components in the data flow task into an Execute SQL task in the control flow and the process ran in seconds. I don't think that is exactly what you meant, but I wasn't sure what you meant by the suggestion to "redirect those rows destined for update to a table and then use an Execute SQL task in the control flow to perform a set-based update".
If you have time to comment so I understand the problem correctly, what I was doing wrong by using a data flow task with a table as an OLE DB source was executing the SQL statement in each OLE DB Command component I defined 21,000 times - once for each row in the table. So instead of executing 4 distinct queries, I was really executing 84,000 queries. If that is the case, when is it OK (if ever) to use such a scenario? Should the SQL command being executed be defined to only work on the current table entry? What would the syntax look like?
|||Thank you Phil! I just moved the queries (I actually had 4 separate queries) that I was executing as separate OLE DB Command components in the data flow task into an Execute SQL task in the control flow and the process ran in seconds. I don't think that is exactly what you meant, but I wasn't sure what you meant by the suggestion to "redirect those rows destined for update to a table and then use an Execute SQL task in the control flow to perform a set-based update".
If you have time to comment so I understand the problem correctly, what I was doing wrong by using a data flow task with a table as an OLE DB source was executing the SQL statement in each OLE DB Command component I defined 21,000 times - once for each row in the table. So instead of executing 4 distinct queries, I was really executing 84,000 queries. If that is the case, when is it OK (if ever) to use such a scenario? Should the SQL command being executed be defined to only work on the current table entry? What would the syntax look like?
My suggestion of moving the data to a table was assuming you were doing a parameter-based update query.
Your understanding is correct. You were executing 84,000 updates, and generally there is never a good time to do that. If you need to perform an update in the data flow on all of those rows, it would be best to insert the changes into a separate table, to be used later in a set-based update.|||Thank you for your latest response and your help with this problem.|||
Thank you for your latest response and your help with this problem.
Jeff,
According to your post you managed to achieve this with an Execute SQL Task. Am I correct?
If using an Execute SQL Task is an option for you then I would go with that over a data-flow every time. SSIS will almost never be able to perform quicker than a RDBMS engine.
-Jamie
|||
Jamie,
Yes, I did solve this using an Execute SQL Task. It was a rather straightforward solution with this particular package because I wasn't using a parameterized query. I may have to use what Phil initially suggested above for another, similar package but one that one uses parameters in the query. One parameter needs to be referenced in a sub-query which it isn't allowed. That limitation is what led me to use a data flow task. I just wasn't aware of the inefficiency of that tack. Thanks.
|||Jamie,
Yes, I did solve this using an Execute SQL Task. It was a rather straightforward solution with this particular package because I wasn't using a parameterized query. I may have to use what Phil initially suggested above for another, similar package but one that one uses parameters in the query. One parameter needs to be referenced in a sub-query which it isn't allowed. That limitation is what led me to use a data flow task. I just wasn't aware of the inefficiency of that tack. Thanks.
Caveat that with the fact that its efficient in certain circumstances - unfortunetely doing updates is one of those scenarios. That's due to the vary nature of updates.
-Jamie
Tuesday, March 20, 2012
Performance problem - can't identify cause
Hi
We host a series of databases (SQL Server 2000 sp4) for customers; they are
all of identical schema. Recently, one of them became extremely slow. We
tried to find out what was wrong, but couldn't. I'll give you a quick
account of what we did in the hope that you might be able to suggest
something we overlooked.
After a bit of investigative work, we found that there were loads of locks
present and that CPU on the server was maxed out.
1 - we know that the DB schema is very optimised, so used SQL Compare to
compare this database's schema with another of our customer's in case an
index had been dropped. SQL Compare showed that the schemas were identical.
2 - we rebooted the Windows server in case there were any environmental
problems
3 - we updated the usage/statistics for the database (though these are set
to update automatically I believe) and rebuilt all the indexes.
4 - we ran a script to increase the free-space in the tables (normally set @.
10% free space - I think this script set this to 15% and forced that to
implement there and then)
5 - we ran the DBCC scripts for checking the database etc. This reported
that everything was fine.
6 - we moved the database onto another server (backup & restore) - the
performance problems exhibited themselves on this server too so we knew that
the problem (whatever it was) was specific to this database.
We then built a brand new DB using t-SQL statements and then migrated the
data from the old DB into this new one using DTS packages (only about 4 GB
of data). SQL Compare showed that the new DB has exactly the same schema as
the original DB but thankfully the new DB has NONE of the performance
problems.
So - there evidently was a problem with the database but none of the tools
we used showed up the problem. What did we miss?
Thanks everyone for any suggestions
GriffDid u Run the DBCC SHOWCONTIG or similar to check for Index
Fragmentation that could be one of the issues:
you said you recreated the schema and Copied the Data, well this is
most likely the cause of the Problem.
now these several similar databases, are they accessed in a Similar
pattern by the application, are they in a High availability scenario
etc.
Resting doubt, the TOP Issue (might) be index fragmentation:
Maninder
MCDBA
On Jan 29, 12:21 pm, "Griff" <griffithsj_...@.hotmail.com> wrote:
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they are
> all of identical schema. Recently, one of them became extremely slow. We
> tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set @.
> 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew that
> the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema as
> the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff|||You've tried a bunch of different things without first identifying the cause
of the problem. Now that it's gone, it'll be pretty tough trying to figure
it out.
If this happens again, check to see what's using up the CPU resources, disk
IO activity and whether you're having memory pressure (at SQL Server and/or
OS level in case you locked SQL Server memory settings). Also, check for
blocking and recompilation activity.
Perf tuning is a lot of very invovled work.
http://msdn2.microsoft.com/en-us/sql/aa336291.aspx is a good place to start.
joe.
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:euJVgn8QHHA.4188@.TK2MSFTNGP06.phx.gbl...
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they
> are all of identical schema. Recently, one of them became extremely slow.
> We tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were
> identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set
> @. 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew
> that the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema
> as the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff
>|||Griff (griffithsj_520@.hotmail.com) writes:
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
You did a lot of things, but not the things I would have done. I would
have set up a trace, preferably server-side, and traced for statements
with a duration over some limit, say 100 ms, and then analysed that
trace to find out where the main bottlenecks are. I would then have looked
at the query plans for these queries and compared with the other databases.
I would also have looked at blocking.
My guess is that statistics for the data in some places led the optimizer
astray for one or more queries. It was resolved when you reloaded the data
through DTS, because data may have been loaded in slightly different order.
It may sound crazy, but if the optimizer has a close call between two plans,
small differences in statistics may matter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||I don't know, Erland, if he updated the stats and rebuilt with more
free space, that should tickle the recompiles.
Remember, he said he had a lot of blocks, presumably more than the
similar systems, though these could be a secondary sign of slow
performance. So, it could just be the data, but then rebuilding it
onto a clean server wouldn't help.
Of course I agree I'd do a profiler/plan based analysis, but just
speculating, it's hard to guess what it would turn up.
So I have no idea, but I wonder if the problems will recur in short
order, if they are related to data mal-distributions causing bad
plans, or different traffic patterns.
J.
On Tue, 30 Jan 2007 08:20:08 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:
>Griff (griffithsj_520@.hotmail.com) writes:
>> So - there evidently was a problem with the database but none of the tools
>> we used showed up the problem. What did we miss?
>You did a lot of things, but not the things I would have done. I would
>have set up a trace, preferably server-side, and traced for statements
>with a duration over some limit, say 100 ms, and then analysed that
>trace to find out where the main bottlenecks are. I would then have looked
>at the query plans for these queries and compared with the other databases.
>I would also have looked at blocking.
>My guess is that statistics for the data in some places led the optimizer
>astray for one or more queries. It was resolved when you reloaded the data
>through DTS, because data may have been loaded in slightly different order.
>It may sound crazy, but if the optimizer has a close call between two plans,
>small differences in statistics may matter.|||Hi Erland & "J" (and everyone else on this thread)
I did mention briefly that we performed "a bit of investigative work". This
included using the performance analyser and getting traces for all stored
procedures and any dynamic SQL. There were some very slow running
queries....
We used the index tuning wizard and it suggested no new indexes were
required. As mentioned, the schema is identical for other customers (some
much larger DBs) and they have great performance. We also checked the
execution plan for one or two queries and they was the same on our problem
database and it was on another customer's database. So, we were fairly
confident that things were okay as far as the schema was concerned.
That's when we started running out of ideas. Are there any other symptoms
that we should have checked for - things that should have shouted to us to
look at the SHOWCONTIG output?
Griff|||One place to start would be the query plans. Compare the actual
execution plans for the problematic queries from the old database and the
newly built database. This will provide clues as to what might be wrong.
Before doing this, please make sure that the stats are updated and the
databases are identical in all respects (indexes, constraints, data etc). In
SQL Server 2000, you can use the SET STATISTICS PROFILE command to get the
actual execution plan. You can also use the "Show Plan Statistics" profiler
event.
--
Umachandar Jayachandran
Microsoft SQL Server Performance Team
SQL Server Engine Team Tips Blog at
http://blogs.msdn.com/sqltips/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Griff wrote on Mon, 29 Jan 2007 17:21:10 -0000:
G> Hi
G> We host a series of databases (SQL Server 2000 sp4) for customers;
G> they are all of identical schema. Recently, one of them became
G> extremely slow. We tried to find out what was wrong, but couldn't.
G> I'll give you a quick account of what we did in the hope that you
G> might be able to suggest something we overlooked.
G> After a bit of investigative work, we found that there were loads of
G> locks present and that CPU on the server was maxed out.
G> 1 - we know that the DB schema is very optimised, so used SQL Compare
G> to compare this database's schema with another of our customer's in
G> case an index had been dropped. SQL Compare showed that the schemas
G> were identical.
G> 2 - we rebooted the Windows server in case there were any
G> environmental problems
G> 3 - we updated the usage/statistics for the database (though these
G> are set to update automatically I believe) and rebuilt all the
G> indexes.
G> 4 - we ran a script to increase the free-space in the tables
G> (normally set @.
G> 10% free space - I think this script set this to 15% and forced that
G> to implement there and then)
G> 5 - we ran the DBCC scripts for checking the database etc. This
G> reported that everything was fine.
G> 6 - we moved the database onto another server (backup & restore) -
G> the performance problems exhibited themselves on this server too so
G> we knew that the problem (whatever it was) was specific to this
G> database.
G> We then built a brand new DB using t-SQL statements and then migrated
G> the data from the old DB into this new one using DTS packages (only
G> about 4 GB of data). SQL Compare showed that the new DB has exactly
G> the same schema as the original DB but thankfully the new DB has
G> NONE of the performance problems.
G> So - there evidently was a problem with the database but none of the
G> tools we used showed up the problem. What did we miss?
G> Thanks everyone for any suggestions
G> Griff|||Griff (griffithsj_520@.hotmail.com) writes:
> We used the index tuning wizard and it suggested no new indexes were
> required. As mentioned, the schema is identical for other customers (some
> much larger DBs) and they have great performance. We also checked the
> execution plan for one or two queries and they was the same on our problem
> database and it was on another customer's database. So, we were fairly
> confident that things were okay as far as the schema was concerned.
If the particular queries you looked at were slow due to blocking, it is
not surprising the plans were the same. Else it sounds very strange.
Difference in execution time is usually due to bad plans or looking. But
really bad fragmentation can play in as well.
> That's when we started running out of ideas. Are there any other symptoms
> that we should have checked for - things that should have shouted to us to
> look at the SHOWCONTIG output?
You said that you reindexed all tables, and in such case you should have
resolved such problems. Provided that all your tables has clustered indexes.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||OK. You really need to do a trace of your system and find the
slow-running queries. Make a list of them. I will bet that there's a
lot of table scanning or hashing going on. Do you have a sample of one
of the queries that you can post? Can you post the DDL of the base
tables being queried, as well as some distributions of key data? The
Index Tuning Wizard is just not something that I'd run or trust for
telling me how to run my production system...
-Dave
Griff wrote:
> Hi Erland & "J" (and everyone else on this thread)
> I did mention briefly that we performed "a bit of investigative work". This
> included using the performance analyser and getting traces for all stored
> procedures and any dynamic SQL. There were some very slow running
> queries....
> We used the index tuning wizard and it suggested no new indexes were
> required. As mentioned, the schema is identical for other customers (some
> much larger DBs) and they have great performance. We also checked the
> execution plan for one or two queries and they was the same on our problem
> database and it was on another customer's database. So, we were fairly
> confident that things were okay as far as the schema was concerned.
> That's when we started running out of ideas. Are there any other symptoms
> that we should have checked for - things that should have shouted to us to
> look at the SHOWCONTIG output?
> Griff
>
-Dave Markle
http://www.markleconsulting.com/blog|||Are you in a hyperthreading mode ? If yes, try to turn it off. See Slava
Ocks paper.
A +
Griff a écrit :
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they are
> all of identical schema. Recently, one of them became extremely slow. We
> tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set @.
> 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew that
> the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema as
> the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
We host a series of databases (SQL Server 2000 sp4) for customers; they are
all of identical schema. Recently, one of them became extremely slow. We
tried to find out what was wrong, but couldn't. I'll give you a quick
account of what we did in the hope that you might be able to suggest
something we overlooked.
After a bit of investigative work, we found that there were loads of locks
present and that CPU on the server was maxed out.
1 - we know that the DB schema is very optimised, so used SQL Compare to
compare this database's schema with another of our customer's in case an
index had been dropped. SQL Compare showed that the schemas were identical.
2 - we rebooted the Windows server in case there were any environmental
problems
3 - we updated the usage/statistics for the database (though these are set
to update automatically I believe) and rebuilt all the indexes.
4 - we ran a script to increase the free-space in the tables (normally set @.
10% free space - I think this script set this to 15% and forced that to
implement there and then)
5 - we ran the DBCC scripts for checking the database etc. This reported
that everything was fine.
6 - we moved the database onto another server (backup & restore) - the
performance problems exhibited themselves on this server too so we knew that
the problem (whatever it was) was specific to this database.
We then built a brand new DB using t-SQL statements and then migrated the
data from the old DB into this new one using DTS packages (only about 4 GB
of data). SQL Compare showed that the new DB has exactly the same schema as
the original DB but thankfully the new DB has NONE of the performance
problems.
So - there evidently was a problem with the database but none of the tools
we used showed up the problem. What did we miss?
Thanks everyone for any suggestions
GriffDid u Run the DBCC SHOWCONTIG or similar to check for Index
Fragmentation that could be one of the issues:
you said you recreated the schema and Copied the Data, well this is
most likely the cause of the Problem.
now these several similar databases, are they accessed in a Similar
pattern by the application, are they in a High availability scenario
etc.
Resting doubt, the TOP Issue (might) be index fragmentation:
Maninder
MCDBA
On Jan 29, 12:21 pm, "Griff" <griffithsj_...@.hotmail.com> wrote:
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they are
> all of identical schema. Recently, one of them became extremely slow. We
> tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set @.
> 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew that
> the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema as
> the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff|||You've tried a bunch of different things without first identifying the cause
of the problem. Now that it's gone, it'll be pretty tough trying to figure
it out.
If this happens again, check to see what's using up the CPU resources, disk
IO activity and whether you're having memory pressure (at SQL Server and/or
OS level in case you locked SQL Server memory settings). Also, check for
blocking and recompilation activity.
Perf tuning is a lot of very invovled work.
http://msdn2.microsoft.com/en-us/sql/aa336291.aspx is a good place to start.
joe.
"Griff" <griffithsj_520@.hotmail.com> wrote in message
news:euJVgn8QHHA.4188@.TK2MSFTNGP06.phx.gbl...
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they
> are all of identical schema. Recently, one of them became extremely slow.
> We tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were
> identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set
> @. 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew
> that the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema
> as the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff
>|||Griff (griffithsj_520@.hotmail.com) writes:
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
You did a lot of things, but not the things I would have done. I would
have set up a trace, preferably server-side, and traced for statements
with a duration over some limit, say 100 ms, and then analysed that
trace to find out where the main bottlenecks are. I would then have looked
at the query plans for these queries and compared with the other databases.
I would also have looked at blocking.
My guess is that statistics for the data in some places led the optimizer
astray for one or more queries. It was resolved when you reloaded the data
through DTS, because data may have been loaded in slightly different order.
It may sound crazy, but if the optimizer has a close call between two plans,
small differences in statistics may matter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||I don't know, Erland, if he updated the stats and rebuilt with more
free space, that should tickle the recompiles.
Remember, he said he had a lot of blocks, presumably more than the
similar systems, though these could be a secondary sign of slow
performance. So, it could just be the data, but then rebuilding it
onto a clean server wouldn't help.
Of course I agree I'd do a profiler/plan based analysis, but just
speculating, it's hard to guess what it would turn up.
So I have no idea, but I wonder if the problems will recur in short
order, if they are related to data mal-distributions causing bad
plans, or different traffic patterns.
J.
On Tue, 30 Jan 2007 08:20:08 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:
>Griff (griffithsj_520@.hotmail.com) writes:
>> So - there evidently was a problem with the database but none of the tools
>> we used showed up the problem. What did we miss?
>You did a lot of things, but not the things I would have done. I would
>have set up a trace, preferably server-side, and traced for statements
>with a duration over some limit, say 100 ms, and then analysed that
>trace to find out where the main bottlenecks are. I would then have looked
>at the query plans for these queries and compared with the other databases.
>I would also have looked at blocking.
>My guess is that statistics for the data in some places led the optimizer
>astray for one or more queries. It was resolved when you reloaded the data
>through DTS, because data may have been loaded in slightly different order.
>It may sound crazy, but if the optimizer has a close call between two plans,
>small differences in statistics may matter.|||Hi Erland & "J" (and everyone else on this thread)
I did mention briefly that we performed "a bit of investigative work". This
included using the performance analyser and getting traces for all stored
procedures and any dynamic SQL. There were some very slow running
queries....
We used the index tuning wizard and it suggested no new indexes were
required. As mentioned, the schema is identical for other customers (some
much larger DBs) and they have great performance. We also checked the
execution plan for one or two queries and they was the same on our problem
database and it was on another customer's database. So, we were fairly
confident that things were okay as far as the schema was concerned.
That's when we started running out of ideas. Are there any other symptoms
that we should have checked for - things that should have shouted to us to
look at the SHOWCONTIG output?
Griff|||One place to start would be the query plans. Compare the actual
execution plans for the problematic queries from the old database and the
newly built database. This will provide clues as to what might be wrong.
Before doing this, please make sure that the stats are updated and the
databases are identical in all respects (indexes, constraints, data etc). In
SQL Server 2000, you can use the SET STATISTICS PROFILE command to get the
actual execution plan. You can also use the "Show Plan Statistics" profiler
event.
--
Umachandar Jayachandran
Microsoft SQL Server Performance Team
SQL Server Engine Team Tips Blog at
http://blogs.msdn.com/sqltips/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Griff wrote on Mon, 29 Jan 2007 17:21:10 -0000:
G> Hi
G> We host a series of databases (SQL Server 2000 sp4) for customers;
G> they are all of identical schema. Recently, one of them became
G> extremely slow. We tried to find out what was wrong, but couldn't.
G> I'll give you a quick account of what we did in the hope that you
G> might be able to suggest something we overlooked.
G> After a bit of investigative work, we found that there were loads of
G> locks present and that CPU on the server was maxed out.
G> 1 - we know that the DB schema is very optimised, so used SQL Compare
G> to compare this database's schema with another of our customer's in
G> case an index had been dropped. SQL Compare showed that the schemas
G> were identical.
G> 2 - we rebooted the Windows server in case there were any
G> environmental problems
G> 3 - we updated the usage/statistics for the database (though these
G> are set to update automatically I believe) and rebuilt all the
G> indexes.
G> 4 - we ran a script to increase the free-space in the tables
G> (normally set @.
G> 10% free space - I think this script set this to 15% and forced that
G> to implement there and then)
G> 5 - we ran the DBCC scripts for checking the database etc. This
G> reported that everything was fine.
G> 6 - we moved the database onto another server (backup & restore) -
G> the performance problems exhibited themselves on this server too so
G> we knew that the problem (whatever it was) was specific to this
G> database.
G> We then built a brand new DB using t-SQL statements and then migrated
G> the data from the old DB into this new one using DTS packages (only
G> about 4 GB of data). SQL Compare showed that the new DB has exactly
G> the same schema as the original DB but thankfully the new DB has
G> NONE of the performance problems.
G> So - there evidently was a problem with the database but none of the
G> tools we used showed up the problem. What did we miss?
G> Thanks everyone for any suggestions
G> Griff|||Griff (griffithsj_520@.hotmail.com) writes:
> We used the index tuning wizard and it suggested no new indexes were
> required. As mentioned, the schema is identical for other customers (some
> much larger DBs) and they have great performance. We also checked the
> execution plan for one or two queries and they was the same on our problem
> database and it was on another customer's database. So, we were fairly
> confident that things were okay as far as the schema was concerned.
If the particular queries you looked at were slow due to blocking, it is
not surprising the plans were the same. Else it sounds very strange.
Difference in execution time is usually due to bad plans or looking. But
really bad fragmentation can play in as well.
> That's when we started running out of ideas. Are there any other symptoms
> that we should have checked for - things that should have shouted to us to
> look at the SHOWCONTIG output?
You said that you reindexed all tables, and in such case you should have
resolved such problems. Provided that all your tables has clustered indexes.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||OK. You really need to do a trace of your system and find the
slow-running queries. Make a list of them. I will bet that there's a
lot of table scanning or hashing going on. Do you have a sample of one
of the queries that you can post? Can you post the DDL of the base
tables being queried, as well as some distributions of key data? The
Index Tuning Wizard is just not something that I'd run or trust for
telling me how to run my production system...
-Dave
Griff wrote:
> Hi Erland & "J" (and everyone else on this thread)
> I did mention briefly that we performed "a bit of investigative work". This
> included using the performance analyser and getting traces for all stored
> procedures and any dynamic SQL. There were some very slow running
> queries....
> We used the index tuning wizard and it suggested no new indexes were
> required. As mentioned, the schema is identical for other customers (some
> much larger DBs) and they have great performance. We also checked the
> execution plan for one or two queries and they was the same on our problem
> database and it was on another customer's database. So, we were fairly
> confident that things were okay as far as the schema was concerned.
> That's when we started running out of ideas. Are there any other symptoms
> that we should have checked for - things that should have shouted to us to
> look at the SHOWCONTIG output?
> Griff
>
-Dave Markle
http://www.markleconsulting.com/blog|||Are you in a hyperthreading mode ? If yes, try to turn it off. See Slava
Ocks paper.
A +
Griff a écrit :
> Hi
> We host a series of databases (SQL Server 2000 sp4) for customers; they are
> all of identical schema. Recently, one of them became extremely slow. We
> tried to find out what was wrong, but couldn't. I'll give you a quick
> account of what we did in the hope that you might be able to suggest
> something we overlooked.
> After a bit of investigative work, we found that there were loads of locks
> present and that CPU on the server was maxed out.
> 1 - we know that the DB schema is very optimised, so used SQL Compare to
> compare this database's schema with another of our customer's in case an
> index had been dropped. SQL Compare showed that the schemas were identical.
> 2 - we rebooted the Windows server in case there were any environmental
> problems
> 3 - we updated the usage/statistics for the database (though these are set
> to update automatically I believe) and rebuilt all the indexes.
> 4 - we ran a script to increase the free-space in the tables (normally set @.
> 10% free space - I think this script set this to 15% and forced that to
> implement there and then)
> 5 - we ran the DBCC scripts for checking the database etc. This reported
> that everything was fine.
> 6 - we moved the database onto another server (backup & restore) - the
> performance problems exhibited themselves on this server too so we knew that
> the problem (whatever it was) was specific to this database.
> We then built a brand new DB using t-SQL statements and then migrated the
> data from the old DB into this new one using DTS packages (only about 4 GB
> of data). SQL Compare showed that the new DB has exactly the same schema as
> the original DB but thankfully the new DB has NONE of the performance
> problems.
> So - there evidently was a problem with the database but none of the tools
> we used showed up the problem. What did we miss?
> Thanks everyone for any suggestions
> Griff
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
Performance problem
After a disk failure ( disk was in raid 1 configuration ) SQL Server
start to work very slow.
SqlServer start to raise following event
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 7/3/2006
Time: 8:54:12 AM
User: N/A
Computer: SRVCA2
Description:
17052 :
This SQL Server has been optimized for 8 concurrent queries. This limit
has been exceeded by 19 queries and performance may be adversely
affected.
SQL Server worked fine before this. I replaced hard disk and rebuild of
configuration was sucesfull. In SQL events i saw that SQL Server has
performed recovery of databases.
I have SQL Server Personal edition installed.
Can someone help me with this one please.The verion of the db engine shipped with the Personal Edition has an inbuilt
restriction , that once more than a certain number of activities pass the
restriction.
There are a number of different activities which contribute to the count.
Check DBCC CONCURRENCYVIOLATION to gauge how often the service goes over
the limit. Maybe you could upgrade
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sale" <aleksandar.stancic@.gmail.com> wrote in message
news:1151913565.386437.173310@.75g2000cwc.googlegroups.com...
> After a disk failure ( disk was in raid 1 configuration ) SQL Server
> start to work very slow.
> SqlServer start to raise following event
> Event Type: Information
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17055
> Date: 7/3/2006
> Time: 8:54:12 AM
> User: N/A
> Computer: SRVCA2
> Description:
> 17052 :
> This SQL Server has been optimized for 8 concurrent queries. This limit
> has been exceeded by 19 queries and performance may be adversely
> affected.
>
> SQL Server worked fine before this. I replaced hard disk and rebuild of
> configuration was sucesfull. In SQL events i saw that SQL Server has
> performed recovery of databases.
> I have SQL Server Personal edition installed.
> Can someone help me with this one please.
>|||"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:TI-dnbeidMt4RzXZnZ2dnUVZ8s-dnZ2d@.bt.com...
> The verion of the db engine shipped with the Personal Edition has an
inbuilt
> restriction , that once more than a certain number of activities pass the
> restriction.
> There are a number of different activities which contribute to the count.
> Check DBCC CONCURRENCYVIOLATION to gauge how often the service goes over
> the limit. Maybe you could upgrade
Most likely the slower diskI/O during the failure was the cause here.
You may want to upgrade to something like SQL 2005 Express which removes
this particular restriction. (but has others).
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Sale" <aleksandar.stancic@.gmail.com> wrote in message
> news:1151913565.386437.173310@.75g2000cwc.googlegroups.com...
>|||I Upgrade Personal edition to Standard, and after this everything work
fine.
Thanks for your help people
start to work very slow.
SqlServer start to raise following event
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 7/3/2006
Time: 8:54:12 AM
User: N/A
Computer: SRVCA2
Description:
17052 :
This SQL Server has been optimized for 8 concurrent queries. This limit
has been exceeded by 19 queries and performance may be adversely
affected.
SQL Server worked fine before this. I replaced hard disk and rebuild of
configuration was sucesfull. In SQL events i saw that SQL Server has
performed recovery of databases.
I have SQL Server Personal edition installed.
Can someone help me with this one please.The verion of the db engine shipped with the Personal Edition has an inbuilt
restriction , that once more than a certain number of activities pass the
restriction.
There are a number of different activities which contribute to the count.
Check DBCC CONCURRENCYVIOLATION to gauge how often the service goes over
the limit. Maybe you could upgrade
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sale" <aleksandar.stancic@.gmail.com> wrote in message
news:1151913565.386437.173310@.75g2000cwc.googlegroups.com...
> After a disk failure ( disk was in raid 1 configuration ) SQL Server
> start to work very slow.
> SqlServer start to raise following event
> Event Type: Information
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17055
> Date: 7/3/2006
> Time: 8:54:12 AM
> User: N/A
> Computer: SRVCA2
> Description:
> 17052 :
> This SQL Server has been optimized for 8 concurrent queries. This limit
> has been exceeded by 19 queries and performance may be adversely
> affected.
>
> SQL Server worked fine before this. I replaced hard disk and rebuild of
> configuration was sucesfull. In SQL events i saw that SQL Server has
> performed recovery of databases.
> I have SQL Server Personal edition installed.
> Can someone help me with this one please.
>|||"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:TI-dnbeidMt4RzXZnZ2dnUVZ8s-dnZ2d@.bt.com...
> The verion of the db engine shipped with the Personal Edition has an
inbuilt
> restriction , that once more than a certain number of activities pass the
> restriction.
> There are a number of different activities which contribute to the count.
> Check DBCC CONCURRENCYVIOLATION to gauge how often the service goes over
> the limit. Maybe you could upgrade
Most likely the slower diskI/O during the failure was the cause here.
You may want to upgrade to something like SQL 2005 Express which removes
this particular restriction. (but has others).
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Sale" <aleksandar.stancic@.gmail.com> wrote in message
> news:1151913565.386437.173310@.75g2000cwc.googlegroups.com...
>|||I Upgrade Personal edition to Standard, and after this everything work
fine.
Thanks for your help people
Monday, March 12, 2012
Performance problem
After a disk failure ( disk was in raid 1 configuration ) SQL Server
start to work very slow.
SqlServer start to raise following event
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 7/3/2006
Time: 8:54:12 AM
User: N/A
Computer: SRVCA2
Description:
17052 :
This SQL Server has been optimized for 8 concurrent queries. This limit
has been exceeded by 19 queries and performance may be adversely
affected.
SQL Server worked fine before this. I replaced hard disk and rebuild of
configuration was sucesfull. In SQL events i saw that SQL Server has
performed recovery of databases.
I have SQL Server Personal edition installed.
Can someone help me with this one please.The verion of the db engine shipped with the Personal Edition has an inbuilt
restriction , that once more than a certain number of activities pass the
restriction.
There are a number of different activities which contribute to the count.
Check DBCC CONCURRENCYVIOLATION to gauge how often the service goes over
the limit. Maybe you could upgrade
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sale" <aleksandar.stancic@.gmail.com> wrote in message
news:1151913565.386437.173310@.75g2000cwc.googlegroups.com...
> After a disk failure ( disk was in raid 1 configuration ) SQL Server
> start to work very slow.
> SqlServer start to raise following event
> Event Type: Information
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17055
> Date: 7/3/2006
> Time: 8:54:12 AM
> User: N/A
> Computer: SRVCA2
> Description:
> 17052 :
> This SQL Server has been optimized for 8 concurrent queries. This limit
> has been exceeded by 19 queries and performance may be adversely
> affected.
>
> SQL Server worked fine before this. I replaced hard disk and rebuild of
> configuration was sucesfull. In SQL events i saw that SQL Server has
> performed recovery of databases.
> I have SQL Server Personal edition installed.
> Can someone help me with this one please.
>|||"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:TI-dnbeidMt4RzXZnZ2dnUVZ8s-dnZ2d@.bt.com...
> The verion of the db engine shipped with the Personal Edition has an
inbuilt
> restriction , that once more than a certain number of activities pass the
> restriction.
> There are a number of different activities which contribute to the count.
> Check DBCC CONCURRENCYVIOLATION to gauge how often the service goes over
> the limit. Maybe you could upgrade
Most likely the slower diskI/O during the failure was the cause here.
You may want to upgrade to something like SQL 2005 Express which removes
this particular restriction. (but has others).
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Sale" <aleksandar.stancic@.gmail.com> wrote in message
> news:1151913565.386437.173310@.75g2000cwc.googlegroups.com...
> > After a disk failure ( disk was in raid 1 configuration ) SQL Server
> > start to work very slow.
> >
> > SqlServer start to raise following event
> >
> > Event Type: Information
> > Event Source: MSSQLSERVER
> > Event Category: (2)
> > Event ID: 17055
> > Date: 7/3/2006
> > Time: 8:54:12 AM
> > User: N/A
> > Computer: SRVCA2
> > Description:
> > 17052 :
> > This SQL Server has been optimized for 8 concurrent queries. This limit
> > has been exceeded by 19 queries and performance may be adversely
> > affected.
> >
> >
> > SQL Server worked fine before this. I replaced hard disk and rebuild of
> > configuration was sucesfull. In SQL events i saw that SQL Server has
> > performed recovery of databases.
> >
> > I have SQL Server Personal edition installed.
> >
> > Can someone help me with this one please.
> >
>|||I Upgrade Personal edition to Standard, and after this everything work
fine.
Thanks for your help people
start to work very slow.
SqlServer start to raise following event
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 7/3/2006
Time: 8:54:12 AM
User: N/A
Computer: SRVCA2
Description:
17052 :
This SQL Server has been optimized for 8 concurrent queries. This limit
has been exceeded by 19 queries and performance may be adversely
affected.
SQL Server worked fine before this. I replaced hard disk and rebuild of
configuration was sucesfull. In SQL events i saw that SQL Server has
performed recovery of databases.
I have SQL Server Personal edition installed.
Can someone help me with this one please.The verion of the db engine shipped with the Personal Edition has an inbuilt
restriction , that once more than a certain number of activities pass the
restriction.
There are a number of different activities which contribute to the count.
Check DBCC CONCURRENCYVIOLATION to gauge how often the service goes over
the limit. Maybe you could upgrade
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sale" <aleksandar.stancic@.gmail.com> wrote in message
news:1151913565.386437.173310@.75g2000cwc.googlegroups.com...
> After a disk failure ( disk was in raid 1 configuration ) SQL Server
> start to work very slow.
> SqlServer start to raise following event
> Event Type: Information
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17055
> Date: 7/3/2006
> Time: 8:54:12 AM
> User: N/A
> Computer: SRVCA2
> Description:
> 17052 :
> This SQL Server has been optimized for 8 concurrent queries. This limit
> has been exceeded by 19 queries and performance may be adversely
> affected.
>
> SQL Server worked fine before this. I replaced hard disk and rebuild of
> configuration was sucesfull. In SQL events i saw that SQL Server has
> performed recovery of databases.
> I have SQL Server Personal edition installed.
> Can someone help me with this one please.
>|||"Jack Vamvas" <DEL_TO_REPLYtechsupport@.ciquery.com> wrote in message
news:TI-dnbeidMt4RzXZnZ2dnUVZ8s-dnZ2d@.bt.com...
> The verion of the db engine shipped with the Personal Edition has an
inbuilt
> restriction , that once more than a certain number of activities pass the
> restriction.
> There are a number of different activities which contribute to the count.
> Check DBCC CONCURRENCYVIOLATION to gauge how often the service goes over
> the limit. Maybe you could upgrade
Most likely the slower diskI/O during the failure was the cause here.
You may want to upgrade to something like SQL 2005 Express which removes
this particular restriction. (but has others).
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> ___________________________________
>
> "Sale" <aleksandar.stancic@.gmail.com> wrote in message
> news:1151913565.386437.173310@.75g2000cwc.googlegroups.com...
> > After a disk failure ( disk was in raid 1 configuration ) SQL Server
> > start to work very slow.
> >
> > SqlServer start to raise following event
> >
> > Event Type: Information
> > Event Source: MSSQLSERVER
> > Event Category: (2)
> > Event ID: 17055
> > Date: 7/3/2006
> > Time: 8:54:12 AM
> > User: N/A
> > Computer: SRVCA2
> > Description:
> > 17052 :
> > This SQL Server has been optimized for 8 concurrent queries. This limit
> > has been exceeded by 19 queries and performance may be adversely
> > affected.
> >
> >
> > SQL Server worked fine before this. I replaced hard disk and rebuild of
> > configuration was sucesfull. In SQL events i saw that SQL Server has
> > performed recovery of databases.
> >
> > I have SQL Server Personal edition installed.
> >
> > Can someone help me with this one please.
> >
>|||I Upgrade Personal edition to Standard, and after this everything work
fine.
Thanks for your help people
Performance Opening Report in Preview
Opening the report in preview is very slow, as if the main query is running
without the parameters. Is there a setting to make sure a dataset is not
queried until after the report view button is clicked?Do you have defaults for all the parameters? If so then it runs as soon as
you preview the report. To have it not run until you click on the view
button at least one parameter needs to be without a default.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"WillC" <WillC@.discussions.microsoft.com> wrote in message
news:3578FA47-2646-419F-B683-32E8C70B4622@.microsoft.com...
> Opening the report in preview is very slow, as if the main query is
> running
> without the parameters. Is there a setting to make sure a dataset is not
> queried until after the report view button is clicked?
without the parameters. Is there a setting to make sure a dataset is not
queried until after the report view button is clicked?Do you have defaults for all the parameters? If so then it runs as soon as
you preview the report. To have it not run until you click on the view
button at least one parameter needs to be without a default.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"WillC" <WillC@.discussions.microsoft.com> wrote in message
news:3578FA47-2646-419F-B683-32E8C70B4622@.microsoft.com...
> Opening the report in preview is very slow, as if the main query is
> running
> without the parameters. Is there a setting to make sure a dataset is not
> queried until after the report view button is clicked?
Wednesday, March 7, 2012
Performance of dataflow too slow
I was transfering more that 100,000 records from flat file to sql table
It took about 1 hour.Is this the way it is?i used oledb command.
As the data passes by i got to insert to several table.Like i insert some of incoming data to one table then get the key from that table and insert rest of the data with the key field from previous table to another table.
In this case i felt OLedb would be best as we can use query.
I cannot use oledb destination as it has only error output(to insert some of incoming data and i want to have a look up to get the key but oledb des has only error output)
i cannot use sql destination as the database is sql server 2000.It dosent let me.
How can i increase the performance?Please let me know
So if I understand correctly, you are using an OLE DB Command to insert your new records into Table A, then a Lookup in non-cached mode to get the key you just inserted, so you can load the rows (with the key) into Table B with an OLE DB Destination. So you're doing 100K inserts, 100K selects, and then a fast load of 100K rows. I'm not surprised that's slow.I think you would get much better performance to break this up into two data flows. The first data flow reads the file and populates Table A using an OLE DB Destination in fast load mode. The second Data Flow reads the file again, uses a full-cache lookup from Table A to get your new keys, and then fast loads into Table B.
If you want to get fancy (I like to get fancy), you can read the file only once, assign the Table A key in script, fast load both tables at the same time, and not have to do any lookups.
|||
That's sound advice from Jay. To re-iterate something he said, do what you can to eliminate the OLE DB Commnd. its a very unperformant component.
|||The best way to improve performance is to get rid of the OLE DB command. If you need to update multiple tables, maybe you should use multiple data flows.
Updated: Jay and Jamie beat me to the punch. You'll notice a common thread in everyone's comments, though...
sureshv wrote:
Does it normally take around an hour to insert or update 100,000 records in ssis?
If you aren't using the "fast load" option, yes, I can see how that might be the case.|||
Does it normally take around an hour to insert or update 100,000 records in ssis?
Well it really isn't a matter of SSIS performance, the primary factor in your scenario is the relational engine having to process 100,000 commands. Indexes would play a large role in that; if you have too many indexes your inserts will suffer, if you don't have the right one your selects will suffer. Put a WHERE 1=0 at the end of your statements and the performance should improve significantly.
So, yes, it's certainly possible that it could take an hour to insert 100,000 rows. It could also probably be done in 10 minutes with no changes to SSIS. The difference would be in the database itself. But regardless of the database, I think you can improve your SSIS design.
|||
Put a WHERE 1=0 at the end of your statements and the performance should improve significantly. .
Really? By not selecting any data?|||
Put a WHERE 1=0 at the end of your statements and the performance should improve significantly.
Really? By not selecting any data?
I said it would be fast, not that it would work.
(It was a joke)
|||
Does it normally take around an hour to insert or update 100,000 records in ssis?
Its impossible to answer this question with a simple yes or no because there are so many variables. Record width, physical location, network latency, CPU, memory, indexes at the destination, insertion method, driver/provider, destination type, .... I could go on and on.
What I CAN tell you is that under the right circumstances it is possible to insert 100000 into a SQL Server table using SSIS withn a few seconds. As I hope you appreciate though, there are many factors.
-Jamie
Saturday, February 25, 2012
Performance Monitoring
I ran performance monitoring on one of my server that is
performing slow in order to determine if there is a memory
or hardware bottleneck and came up with the following
statistics. Please help diagnose this and let me know if
we need to increase memory or configure something in sql
server. This is an average of statistics collected
during peak periods 19 times. Thanks for your help. I
already suggested that ram needs to be added to the
server, but I need more assured technical justification
as I am new to this. Thx.
PROCESSOR TIME
max 36.42%
Average 9.53%
System
Processor Queue Length (max) 1
MEMORY
Pages/sec 121
Available Mbytes 156
Page Faults/sec 735
total physical memory 1986302
total sql server memory - mb 1589485
Processes page fault - Sql Server 73
Maximum workspace memory 1081725
DISK
Disk Reads per second 244
Disk Writes per second 99
Average Disk Queue length 4
SQL SERVER GENERAL STATISTICS
# connections 15
SQL Server Buffer Manager cache hit ratio 94
Hardware Info
Hard drive :
C' drive total hd space = 33.9G space
available = 20.2 G
D' drive total hd space = 273G space
available = 272G
E' drive total hd space = 273G space
available = 172 G
F' drive total hd space = 273G space
available = 127 G
F' drive total hd space = 819G space
available = 431 G
CPU = 2.80 GHz
RAM = 2.09G
Operating System = Windows 2000 SP4
Sql Server 2000 sp3aUnfortunatly its a bit hard to determine whether your
server is running slow or not, because there is no point
of reference. Probably the best way would be to run it
once on a weekly basis and get the average.
You may want to monitor network trafic, read writes and
memory (can anyone think of more ?), personally though
7/10 its the network thats slow and not SQL.
Be that as it may there are some options that may speed
you SQL Server up, if you feel its underperforming.
Firstly have a look at the DBCC SHOWCONTIG command. This
will show how dragmented your indexes are, if they are
defragmented then run DBCC DBREINDEX.
Secondly set up a profile trace for performance
enhancement. Allow it to run for at least a couple of days
then run the Index Tuning wizard. This will put in some
indexes for you based upon the queries your users have
been making.
Finally mark you store procedures for re-compulation in
case the execution plan is out of date.
If you would like any further help my email in reverse
order is moc.liamtoh@.em_yrewolf_elttil
J
>--Original Message--
>I ran performance monitoring on one of my server that is
>performing slow in order to determine if there is a
memory
>or hardware bottleneck and came up with the following
>statistics. Please help diagnose this and let me know if
>we need to increase memory or configure something in sql
>server. This is an average of statistics collected
>during peak periods 19 times. Thanks for your help. I
>already suggested that ram needs to be added to the
>server, but I need more assured technical justification
>as I am new to this. Thx.
>PROCESSOR TIME
>max 36.42%
>Average 9.53%
>System
>Processor Queue Length (max) 1
>MEMORY
>Pages/sec 121
>Available Mbytes 156
>Page Faults/sec 735
>total physical memory 1986302
>total sql server memory - mb 1589485
>Processes page fault - Sql Server 73
>Maximum workspace memory 1081725
>DISK
>Disk Reads per second 244
>Disk Writes per second 99
>Average Disk Queue length 4
>SQL SERVER GENERAL STATISTICS
># connections 15
>SQL Server Buffer Manager cache hit ratio 94
>Hardware Info
>Hard drive :
>C' drive total hd space = 33.9G space
>available = 20.2 G
>D' drive total hd space = 273G space
>available = 272G
>E' drive total hd space = 273G space
>available = 172 G
>F' drive total hd space = 273G space
>available = 127 G
>F' drive total hd space = 819G space
>available = 431 G
>CPU = 2.80 GHz
>RAM = 2.09G
>Operating System = Windows 2000 SP4
>Sql Server 2000 sp3a
>.
>|||Julie,
Thank you so much for response you gave to my performance
problems.
The statistics I sent in the Microsoft newsgroup is an
average of every thing I log for 9 days now. Would you
rather I send you what I collected today? The network may
notbe the issue because I have an identical server with
more memory that returns stuff quicker. I will try the
trace profiler also. I reindex the indexes weekly - but I
will still run dbcc showcontig. Finally, how do you
recompile stored procedures? Thank you.
Jasmine
>--Original Message--
>Unfortunatly its a bit hard to determine whether your
>server is running slow or not, because there is no point
>of reference. Probably the best way would be to run it
>once on a weekly basis and get the average.
>You may want to monitor network trafic, read writes and
>memory (can anyone think of more ?), personally though
>7/10 its the network thats slow and not SQL.
>Be that as it may there are some options that may speed
>you SQL Server up, if you feel its underperforming.
>Firstly have a look at the DBCC SHOWCONTIG command. This
>will show how dragmented your indexes are, if they are
>defragmented then run DBCC DBREINDEX.
>Secondly set up a profile trace for performance
>enhancement. Allow it to run for at least a couple of
days
>then run the Index Tuning wizard. This will put in some
>indexes for you based upon the queries your users have
>been making.
>Finally mark you store procedures for re-compulation in
>case the execution plan is out of date.
>If you would like any further help my email in reverse
>order is moc.liamtoh@.em_yrewolf_elttil
>J
>>--Original Message--
>>I ran performance monitoring on one of my server that is
>>performing slow in order to determine if there is a
>memory
>>or hardware bottleneck and came up with the following
>>statistics. Please help diagnose this and let me know
if
>>we need to increase memory or configure something in sql
>>server. This is an average of statistics collected
>>during peak periods 19 times. Thanks for your help. I
>>already suggested that ram needs to be added to the
>>server, but I need more assured technical justification
>>as I am new to this. Thx.
>>PROCESSOR TIME
>>max 36.42%
>>Average 9.53%
>>System
>>Processor Queue Length (max) 1
>>MEMORY
>>Pages/sec 121
>>Available Mbytes 156
>>Page Faults/sec 735
>>total physical memory 1986302
>>total sql server memory - mb 1589485
>>Processes page fault - Sql Server 73
>>Maximum workspace memory 1081725
>>DISK
>>Disk Reads per second 244
>>Disk Writes per second 99
>>Average Disk Queue length 4
>>SQL SERVER GENERAL STATISTICS
>># connections 15
>>SQL Server Buffer Manager cache hit ratio 94
>>Hardware Info
>>Hard drive :
>>C' drive total hd space = 33.9G space
>>available = 20.2 G
>>D' drive total hd space = 273G space
>>available = 272G
>>E' drive total hd space = 273G space
>>available = 172 G
>>F' drive total hd space = 273G space
>>available = 127 G
>>F' drive total hd space = 819G space
>>available = 431 G
>>CPU = 2.80 GHz
>>RAM = 2.09G
>>Operating System = Windows 2000 SP4
>>Sql Server 2000 sp3a
>>.
>.
>|||also check out the kb below for a more general overview of how to
troubleshoot performance issues
http://support.microsoft.com/default.aspx?scid=kb;en-us;298475
Andy.
"Jasmine Gwendle" <anonymous@.discussions.microsoft.com> wrote in message
news:173c901c448ab$17949f00$a001280a@.phx.gbl...
> I ran performance monitoring on one of my server that is
> performing slow in order to determine if there is a memory
> or hardware bottleneck and came up with the following
> statistics. Please help diagnose this and let me know if
> we need to increase memory or configure something in sql
> server. This is an average of statistics collected
> during peak periods 19 times. Thanks for your help. I
> already suggested that ram needs to be added to the
> server, but I need more assured technical justification
> as I am new to this. Thx.
> PROCESSOR TIME
> max 36.42%
> Average 9.53%
> System
> Processor Queue Length (max) 1
> MEMORY
> Pages/sec 121
> Available Mbytes 156
> Page Faults/sec 735
> total physical memory 1986302
> total sql server memory - mb 1589485
> Processes page fault - Sql Server 73
> Maximum workspace memory 1081725
> DISK
> Disk Reads per second 244
> Disk Writes per second 99
> Average Disk Queue length 4
> SQL SERVER GENERAL STATISTICS
> # connections 15
> SQL Server Buffer Manager cache hit ratio 94
> Hardware Info
> Hard drive :
> C' drive total hd space = 33.9G space
> available = 20.2 G
> D' drive total hd space = 273G space
> available = 272G
> E' drive total hd space = 273G space
> available = 172 G
> F' drive total hd space = 273G space
> available = 127 G
> F' drive total hd space = 819G space
> available = 431 G
> CPU = 2.80 GHz
> RAM = 2.09G
> Operating System = Windows 2000 SP4
> Sql Server 2000 sp3a
>|||Hi Jasmine
Firstly the store procedures
EXEC sp_recompile <store procedure name>
Personally I create a cursor with
select name from sysobjects where xtype = 'P' and status
> 0
That gets out all the sp for your database, then I add
the EXEC sp_recompile
As for the performance could you give me somthing like
Mondays 9am to 5pm Average, Tuesdays average 9am to 5pm
ect.
You can also configure the performance monitor to sent
you pop-ups when for instance network is at 100%.
Other things.
1 Is there anything else running on your server?
2 Is your memory set up the same
3 Do you have autoclose turned off your dbs
4 Is the problem saving, loading or both
J
>--Original Message--
>Julie,
>Thank you so much for response you gave to my
performance
>problems.
>The statistics I sent in the Microsoft newsgroup is an
>average of every thing I log for 9 days now. Would you
>rather I send you what I collected today? The network
may
>notbe the issue because I have an identical server with
>more memory that returns stuff quicker. I will try the
>trace profiler also. I reindex the indexes weekly - but
I
>will still run dbcc showcontig. Finally, how do you
>recompile stored procedures? Thank you.
>Jasmine
>
>>--Original Message--
>>Unfortunatly its a bit hard to determine whether your
>>server is running slow or not, because there is no
point
>>of reference. Probably the best way would be to run it
>>once on a weekly basis and get the average.
>>You may want to monitor network trafic, read writes and
>>memory (can anyone think of more ?), personally though
>>7/10 its the network thats slow and not SQL.
>>Be that as it may there are some options that may speed
>>you SQL Server up, if you feel its underperforming.
>>Firstly have a look at the DBCC SHOWCONTIG command.
This
>>will show how dragmented your indexes are, if they are
>>defragmented then run DBCC DBREINDEX.
>>Secondly set up a profile trace for performance
>>enhancement. Allow it to run for at least a couple of
>days
>>then run the Index Tuning wizard. This will put in some
>>indexes for you based upon the queries your users have
>>been making.
>>Finally mark you store procedures for re-compulation in
>>case the execution plan is out of date.
>>If you would like any further help my email in reverse
>>order is moc.liamtoh@.em_yrewolf_elttil
>>J
>>--Original Message--
>>I ran performance monitoring on one of my server that
is
>>performing slow in order to determine if there is a
>>memory
>>or hardware bottleneck and came up with the following
>>statistics. Please help diagnose this and let me know
>if
>>we need to increase memory or configure something in
sql
>>server. This is an average of statistics collected
>>during peak periods 19 times. Thanks for your help.
I
>>already suggested that ram needs to be added to the
>>server, but I need more assured technical justification
>>as I am new to this. Thx.
>>PROCESSOR TIME
>>max 36.42%
>>Average 9.53%
>>System
>>Processor Queue Length (max) 1
>>MEMORY
>>Pages/sec 121
>>Available Mbytes 156
>>Page Faults/sec 735
>>total physical memory 1986302
>>total sql server memory - mb 1589485
>>Processes page fault - Sql Server 73
>>Maximum workspace memory 1081725
>>DISK
>>Disk Reads per second 244
>>Disk Writes per second 99
>>Average Disk Queue length 4
>>SQL SERVER GENERAL STATISTICS
>># connections 15
>>SQL Server Buffer Manager cache hit ratio 94
>>Hardware Info
>>Hard drive :
>>C' drive total hd space = 33.9G space
>>available = 20.2 G
>>D' drive total hd space = 273G space
>>available = 272G
>>E' drive total hd space = 273G space
>>available = 172 G
>>F' drive total hd space = 273G space
>>available = 127 G
>>F' drive total hd space = 819G space
>>available = 431 G
>>CPU = 2.80 GHz
>>RAM = 2.09G
>>Operating System = Windows 2000 SP4
>>Sql Server 2000 sp3a
>>.
>>.
>.
>
performing slow in order to determine if there is a memory
or hardware bottleneck and came up with the following
statistics. Please help diagnose this and let me know if
we need to increase memory or configure something in sql
server. This is an average of statistics collected
during peak periods 19 times. Thanks for your help. I
already suggested that ram needs to be added to the
server, but I need more assured technical justification
as I am new to this. Thx.
PROCESSOR TIME
max 36.42%
Average 9.53%
System
Processor Queue Length (max) 1
MEMORY
Pages/sec 121
Available Mbytes 156
Page Faults/sec 735
total physical memory 1986302
total sql server memory - mb 1589485
Processes page fault - Sql Server 73
Maximum workspace memory 1081725
DISK
Disk Reads per second 244
Disk Writes per second 99
Average Disk Queue length 4
SQL SERVER GENERAL STATISTICS
# connections 15
SQL Server Buffer Manager cache hit ratio 94
Hardware Info
Hard drive :
C' drive total hd space = 33.9G space
available = 20.2 G
D' drive total hd space = 273G space
available = 272G
E' drive total hd space = 273G space
available = 172 G
F' drive total hd space = 273G space
available = 127 G
F' drive total hd space = 819G space
available = 431 G
CPU = 2.80 GHz
RAM = 2.09G
Operating System = Windows 2000 SP4
Sql Server 2000 sp3aUnfortunatly its a bit hard to determine whether your
server is running slow or not, because there is no point
of reference. Probably the best way would be to run it
once on a weekly basis and get the average.
You may want to monitor network trafic, read writes and
memory (can anyone think of more ?), personally though
7/10 its the network thats slow and not SQL.
Be that as it may there are some options that may speed
you SQL Server up, if you feel its underperforming.
Firstly have a look at the DBCC SHOWCONTIG command. This
will show how dragmented your indexes are, if they are
defragmented then run DBCC DBREINDEX.
Secondly set up a profile trace for performance
enhancement. Allow it to run for at least a couple of days
then run the Index Tuning wizard. This will put in some
indexes for you based upon the queries your users have
been making.
Finally mark you store procedures for re-compulation in
case the execution plan is out of date.
If you would like any further help my email in reverse
order is moc.liamtoh@.em_yrewolf_elttil
J
>--Original Message--
>I ran performance monitoring on one of my server that is
>performing slow in order to determine if there is a
memory
>or hardware bottleneck and came up with the following
>statistics. Please help diagnose this and let me know if
>we need to increase memory or configure something in sql
>server. This is an average of statistics collected
>during peak periods 19 times. Thanks for your help. I
>already suggested that ram needs to be added to the
>server, but I need more assured technical justification
>as I am new to this. Thx.
>PROCESSOR TIME
>max 36.42%
>Average 9.53%
>System
>Processor Queue Length (max) 1
>MEMORY
>Pages/sec 121
>Available Mbytes 156
>Page Faults/sec 735
>total physical memory 1986302
>total sql server memory - mb 1589485
>Processes page fault - Sql Server 73
>Maximum workspace memory 1081725
>DISK
>Disk Reads per second 244
>Disk Writes per second 99
>Average Disk Queue length 4
>SQL SERVER GENERAL STATISTICS
># connections 15
>SQL Server Buffer Manager cache hit ratio 94
>Hardware Info
>Hard drive :
>C' drive total hd space = 33.9G space
>available = 20.2 G
>D' drive total hd space = 273G space
>available = 272G
>E' drive total hd space = 273G space
>available = 172 G
>F' drive total hd space = 273G space
>available = 127 G
>F' drive total hd space = 819G space
>available = 431 G
>CPU = 2.80 GHz
>RAM = 2.09G
>Operating System = Windows 2000 SP4
>Sql Server 2000 sp3a
>.
>|||Julie,
Thank you so much for response you gave to my performance
problems.
The statistics I sent in the Microsoft newsgroup is an
average of every thing I log for 9 days now. Would you
rather I send you what I collected today? The network may
notbe the issue because I have an identical server with
more memory that returns stuff quicker. I will try the
trace profiler also. I reindex the indexes weekly - but I
will still run dbcc showcontig. Finally, how do you
recompile stored procedures? Thank you.
Jasmine
>--Original Message--
>Unfortunatly its a bit hard to determine whether your
>server is running slow or not, because there is no point
>of reference. Probably the best way would be to run it
>once on a weekly basis and get the average.
>You may want to monitor network trafic, read writes and
>memory (can anyone think of more ?), personally though
>7/10 its the network thats slow and not SQL.
>Be that as it may there are some options that may speed
>you SQL Server up, if you feel its underperforming.
>Firstly have a look at the DBCC SHOWCONTIG command. This
>will show how dragmented your indexes are, if they are
>defragmented then run DBCC DBREINDEX.
>Secondly set up a profile trace for performance
>enhancement. Allow it to run for at least a couple of
days
>then run the Index Tuning wizard. This will put in some
>indexes for you based upon the queries your users have
>been making.
>Finally mark you store procedures for re-compulation in
>case the execution plan is out of date.
>If you would like any further help my email in reverse
>order is moc.liamtoh@.em_yrewolf_elttil
>J
>>--Original Message--
>>I ran performance monitoring on one of my server that is
>>performing slow in order to determine if there is a
>memory
>>or hardware bottleneck and came up with the following
>>statistics. Please help diagnose this and let me know
if
>>we need to increase memory or configure something in sql
>>server. This is an average of statistics collected
>>during peak periods 19 times. Thanks for your help. I
>>already suggested that ram needs to be added to the
>>server, but I need more assured technical justification
>>as I am new to this. Thx.
>>PROCESSOR TIME
>>max 36.42%
>>Average 9.53%
>>System
>>Processor Queue Length (max) 1
>>MEMORY
>>Pages/sec 121
>>Available Mbytes 156
>>Page Faults/sec 735
>>total physical memory 1986302
>>total sql server memory - mb 1589485
>>Processes page fault - Sql Server 73
>>Maximum workspace memory 1081725
>>DISK
>>Disk Reads per second 244
>>Disk Writes per second 99
>>Average Disk Queue length 4
>>SQL SERVER GENERAL STATISTICS
>># connections 15
>>SQL Server Buffer Manager cache hit ratio 94
>>Hardware Info
>>Hard drive :
>>C' drive total hd space = 33.9G space
>>available = 20.2 G
>>D' drive total hd space = 273G space
>>available = 272G
>>E' drive total hd space = 273G space
>>available = 172 G
>>F' drive total hd space = 273G space
>>available = 127 G
>>F' drive total hd space = 819G space
>>available = 431 G
>>CPU = 2.80 GHz
>>RAM = 2.09G
>>Operating System = Windows 2000 SP4
>>Sql Server 2000 sp3a
>>.
>.
>|||also check out the kb below for a more general overview of how to
troubleshoot performance issues
http://support.microsoft.com/default.aspx?scid=kb;en-us;298475
Andy.
"Jasmine Gwendle" <anonymous@.discussions.microsoft.com> wrote in message
news:173c901c448ab$17949f00$a001280a@.phx.gbl...
> I ran performance monitoring on one of my server that is
> performing slow in order to determine if there is a memory
> or hardware bottleneck and came up with the following
> statistics. Please help diagnose this and let me know if
> we need to increase memory or configure something in sql
> server. This is an average of statistics collected
> during peak periods 19 times. Thanks for your help. I
> already suggested that ram needs to be added to the
> server, but I need more assured technical justification
> as I am new to this. Thx.
> PROCESSOR TIME
> max 36.42%
> Average 9.53%
> System
> Processor Queue Length (max) 1
> MEMORY
> Pages/sec 121
> Available Mbytes 156
> Page Faults/sec 735
> total physical memory 1986302
> total sql server memory - mb 1589485
> Processes page fault - Sql Server 73
> Maximum workspace memory 1081725
> DISK
> Disk Reads per second 244
> Disk Writes per second 99
> Average Disk Queue length 4
> SQL SERVER GENERAL STATISTICS
> # connections 15
> SQL Server Buffer Manager cache hit ratio 94
> Hardware Info
> Hard drive :
> C' drive total hd space = 33.9G space
> available = 20.2 G
> D' drive total hd space = 273G space
> available = 272G
> E' drive total hd space = 273G space
> available = 172 G
> F' drive total hd space = 273G space
> available = 127 G
> F' drive total hd space = 819G space
> available = 431 G
> CPU = 2.80 GHz
> RAM = 2.09G
> Operating System = Windows 2000 SP4
> Sql Server 2000 sp3a
>|||Hi Jasmine
Firstly the store procedures
EXEC sp_recompile <store procedure name>
Personally I create a cursor with
select name from sysobjects where xtype = 'P' and status
> 0
That gets out all the sp for your database, then I add
the EXEC sp_recompile
As for the performance could you give me somthing like
Mondays 9am to 5pm Average, Tuesdays average 9am to 5pm
ect.
You can also configure the performance monitor to sent
you pop-ups when for instance network is at 100%.
Other things.
1 Is there anything else running on your server?
2 Is your memory set up the same
3 Do you have autoclose turned off your dbs
4 Is the problem saving, loading or both
J
>--Original Message--
>Julie,
>Thank you so much for response you gave to my
performance
>problems.
>The statistics I sent in the Microsoft newsgroup is an
>average of every thing I log for 9 days now. Would you
>rather I send you what I collected today? The network
may
>notbe the issue because I have an identical server with
>more memory that returns stuff quicker. I will try the
>trace profiler also. I reindex the indexes weekly - but
I
>will still run dbcc showcontig. Finally, how do you
>recompile stored procedures? Thank you.
>Jasmine
>
>>--Original Message--
>>Unfortunatly its a bit hard to determine whether your
>>server is running slow or not, because there is no
point
>>of reference. Probably the best way would be to run it
>>once on a weekly basis and get the average.
>>You may want to monitor network trafic, read writes and
>>memory (can anyone think of more ?), personally though
>>7/10 its the network thats slow and not SQL.
>>Be that as it may there are some options that may speed
>>you SQL Server up, if you feel its underperforming.
>>Firstly have a look at the DBCC SHOWCONTIG command.
This
>>will show how dragmented your indexes are, if they are
>>defragmented then run DBCC DBREINDEX.
>>Secondly set up a profile trace for performance
>>enhancement. Allow it to run for at least a couple of
>days
>>then run the Index Tuning wizard. This will put in some
>>indexes for you based upon the queries your users have
>>been making.
>>Finally mark you store procedures for re-compulation in
>>case the execution plan is out of date.
>>If you would like any further help my email in reverse
>>order is moc.liamtoh@.em_yrewolf_elttil
>>J
>>--Original Message--
>>I ran performance monitoring on one of my server that
is
>>performing slow in order to determine if there is a
>>memory
>>or hardware bottleneck and came up with the following
>>statistics. Please help diagnose this and let me know
>if
>>we need to increase memory or configure something in
sql
>>server. This is an average of statistics collected
>>during peak periods 19 times. Thanks for your help.
I
>>already suggested that ram needs to be added to the
>>server, but I need more assured technical justification
>>as I am new to this. Thx.
>>PROCESSOR TIME
>>max 36.42%
>>Average 9.53%
>>System
>>Processor Queue Length (max) 1
>>MEMORY
>>Pages/sec 121
>>Available Mbytes 156
>>Page Faults/sec 735
>>total physical memory 1986302
>>total sql server memory - mb 1589485
>>Processes page fault - Sql Server 73
>>Maximum workspace memory 1081725
>>DISK
>>Disk Reads per second 244
>>Disk Writes per second 99
>>Average Disk Queue length 4
>>SQL SERVER GENERAL STATISTICS
>># connections 15
>>SQL Server Buffer Manager cache hit ratio 94
>>Hardware Info
>>Hard drive :
>>C' drive total hd space = 33.9G space
>>available = 20.2 G
>>D' drive total hd space = 273G space
>>available = 272G
>>E' drive total hd space = 273G space
>>available = 172 G
>>F' drive total hd space = 273G space
>>available = 127 G
>>F' drive total hd space = 819G space
>>available = 431 G
>>CPU = 2.80 GHz
>>RAM = 2.09G
>>Operating System = Windows 2000 SP4
>>Sql Server 2000 sp3a
>>.
>>.
>.
>
Monday, February 20, 2012
Performance Monitoring
I ran performance monitoring on one of my server that is
performing slow in order to determine if there is a memory
or hardware bottleneck and came up with the following
statistics. Please help diagnose this and let me know if
we need to increase memory or configure something in sql
server. This is an average of statistics collected
during peak periods 19 times. Thanks for your help. I
already suggested that ram needs to be added to the
server, but I need more assured technical justification
as I am new to this. Thx.
PROCESSOR TIME
max36.42%
Average9.53%
System
Processor Queue Length (max)1
MEMORY
Pages/sec121
Available Mbytes 156
Page Faults/sec735
total physical memory1986302
total sql server memory - mb1589485
Processes page fault - Sql Server 73
Maximum workspace memory 1081725
DISK
Disk Reads per second244
Disk Writes per second99
Average Disk Queue length 4
SQL SERVER GENERAL STATISTICS
# connections 15
SQL Server Buffer Manager cache hit ratio 94
Hardware Info
Hard drive :
C' drive total hd space = 33.9G space
available = 20.2 G
D' drive total hd space = 273G space
available = 272G
E' drive total hd space = 273G space
available = 172 G
F' drive total hd space = 273G space
available = 127 G
F' drive total hd space = 819G space
available = 431 G
CPU = 2.80 GHz
RAM = 2.09G
Operating System = Windows 2000 SP4
Sql Server 2000 sp3a
also check out the kb below for a more general overview of how to
troubleshoot performance issues
http://support.microsoft.com/default...b;en-us;298475
Andy.
"Jasmine Gwendle" <anonymous@.discussions.microsoft.com> wrote in message
news:173c901c448ab$17949f00$a001280a@.phx.gbl...
> I ran performance monitoring on one of my server that is
> performing slow in order to determine if there is a memory
> or hardware bottleneck and came up with the following
> statistics. Please help diagnose this and let me know if
> we need to increase memory or configure something in sql
> server. This is an average of statistics collected
> during peak periods 19 times. Thanks for your help. I
> already suggested that ram needs to be added to the
> server, but I need more assured technical justification
> as I am new to this. Thx.
> PROCESSOR TIME
> max 36.42%
> Average 9.53%
> System
> Processor Queue Length (max) 1
> MEMORY
> Pages/sec 121
> Available Mbytes 156
> Page Faults/sec 735
> total physical memory 1986302
> total sql server memory - mb 1589485
> Processes page fault - Sql Server 73
> Maximum workspace memory 1081725
> DISK
> Disk Reads per second 244
> Disk Writes per second 99
> Average Disk Queue length 4
> SQL SERVER GENERAL STATISTICS
> # connections 15
> SQL Server Buffer Manager cache hit ratio 94
> Hardware Info
> Hard drive :
> C' drive total hd space = 33.9G space
> available = 20.2 G
> D' drive total hd space = 273G space
> available = 272G
> E' drive total hd space = 273G space
> available = 172 G
> F' drive total hd space = 273G space
> available = 127 G
> F' drive total hd space = 819G space
> available = 431 G
> CPU = 2.80 GHz
> RAM = 2.09G
> Operating System = Windows 2000 SP4
> Sql Server 2000 sp3a
>
performing slow in order to determine if there is a memory
or hardware bottleneck and came up with the following
statistics. Please help diagnose this and let me know if
we need to increase memory or configure something in sql
server. This is an average of statistics collected
during peak periods 19 times. Thanks for your help. I
already suggested that ram needs to be added to the
server, but I need more assured technical justification
as I am new to this. Thx.
PROCESSOR TIME
max36.42%
Average9.53%
System
Processor Queue Length (max)1
MEMORY
Pages/sec121
Available Mbytes 156
Page Faults/sec735
total physical memory1986302
total sql server memory - mb1589485
Processes page fault - Sql Server 73
Maximum workspace memory 1081725
DISK
Disk Reads per second244
Disk Writes per second99
Average Disk Queue length 4
SQL SERVER GENERAL STATISTICS
# connections 15
SQL Server Buffer Manager cache hit ratio 94
Hardware Info
Hard drive :
C' drive total hd space = 33.9G space
available = 20.2 G
D' drive total hd space = 273G space
available = 272G
E' drive total hd space = 273G space
available = 172 G
F' drive total hd space = 273G space
available = 127 G
F' drive total hd space = 819G space
available = 431 G
CPU = 2.80 GHz
RAM = 2.09G
Operating System = Windows 2000 SP4
Sql Server 2000 sp3a
also check out the kb below for a more general overview of how to
troubleshoot performance issues
http://support.microsoft.com/default...b;en-us;298475
Andy.
"Jasmine Gwendle" <anonymous@.discussions.microsoft.com> wrote in message
news:173c901c448ab$17949f00$a001280a@.phx.gbl...
> I ran performance monitoring on one of my server that is
> performing slow in order to determine if there is a memory
> or hardware bottleneck and came up with the following
> statistics. Please help diagnose this and let me know if
> we need to increase memory or configure something in sql
> server. This is an average of statistics collected
> during peak periods 19 times. Thanks for your help. I
> already suggested that ram needs to be added to the
> server, but I need more assured technical justification
> as I am new to this. Thx.
> PROCESSOR TIME
> max 36.42%
> Average 9.53%
> System
> Processor Queue Length (max) 1
> MEMORY
> Pages/sec 121
> Available Mbytes 156
> Page Faults/sec 735
> total physical memory 1986302
> total sql server memory - mb 1589485
> Processes page fault - Sql Server 73
> Maximum workspace memory 1081725
> DISK
> Disk Reads per second 244
> Disk Writes per second 99
> Average Disk Queue length 4
> SQL SERVER GENERAL STATISTICS
> # connections 15
> SQL Server Buffer Manager cache hit ratio 94
> Hardware Info
> Hard drive :
> C' drive total hd space = 33.9G space
> available = 20.2 G
> D' drive total hd space = 273G space
> available = 272G
> E' drive total hd space = 273G space
> available = 172 G
> F' drive total hd space = 273G space
> available = 127 G
> F' drive total hd space = 819G space
> available = 431 G
> CPU = 2.80 GHz
> RAM = 2.09G
> Operating System = Windows 2000 SP4
> Sql Server 2000 sp3a
>
Performance Monitoring
I ran performance monitoring on one of my server that is
performing slow in order to determine if there is a memory
or hardware bottleneck and came up with the following
statistics. Please help diagnose this and let me know if
we need to increase memory or configure something in sql
server. This is an average of statistics collected
during peak periods 19 times. Thanks for your help. I
already suggested that ram needs to be added to the
server, but I need more assured technical justification
as I am new to this. Thx.
PROCESSOR TIME
max 36.42%
Average 9.53%
System
Processor Queue Length (max) 1
MEMORY
Pages/sec 121
Available Mbytes 156
Page Faults/sec 735
total physical memory 1986302
total sql server memory - mb 1589485
Processes page fault - Sql Server 73
Maximum workspace memory 1081725
DISK
Disk Reads per second 244
Disk Writes per second 99
Average Disk Queue length 4
SQL SERVER GENERAL STATISTICS
# connections 15
SQL Server Buffer Manager cache hit ratio 94
Hardware Info
Hard drive :
C' drive total hd space = 33.9G space
available = 20.2 G
D' drive total hd space = 273G space
available = 272G
E' drive total hd space = 273G space
available = 172 G
F' drive total hd space = 273G space
available = 127 G
F' drive total hd space = 819G space
available = 431 G
CPU = 2.80 GHz
RAM = 2.09G
Operating System = Windows 2000 SP4
Sql Server 2000 sp3aalso check out the kb below for a more general overview of how to
troubleshoot performance issues
http://support.microsoft.com/defaul...kb;en-us;298475
Andy.
"Jasmine Gwendle" <anonymous@.discussions.microsoft.com> wrote in message
news:173c901c448ab$17949f00$a001280a@.phx
.gbl...
> I ran performance monitoring on one of my server that is
> performing slow in order to determine if there is a memory
> or hardware bottleneck and came up with the following
> statistics. Please help diagnose this and let me know if
> we need to increase memory or configure something in sql
> server. This is an average of statistics collected
> during peak periods 19 times. Thanks for your help. I
> already suggested that ram needs to be added to the
> server, but I need more assured technical justification
> as I am new to this. Thx.
> PROCESSOR TIME
> max 36.42%
> Average 9.53%
> System
> Processor Queue Length (max) 1
> MEMORY
> Pages/sec 121
> Available Mbytes 156
> Page Faults/sec 735
> total physical memory 1986302
> total sql server memory - mb 1589485
> Processes page fault - Sql Server 73
> Maximum workspace memory 1081725
> DISK
> Disk Reads per second 244
> Disk Writes per second 99
> Average Disk Queue length 4
> SQL SERVER GENERAL STATISTICS
> # connections 15
> SQL Server Buffer Manager cache hit ratio 94
> hardware Info
> Hard drive :
> C' drive total hd space = 33.9G space
> available = 20.2 G
> D' drive total hd space = 273G space
> available = 272G
> E' drive total hd space = 273G space
> available = 172 G
> F' drive total hd space = 273G space
> available = 127 G
> F' drive total hd space = 819G space
> available = 431 G
> CPU = 2.80 GHz
> RAM = 2.09G
> Operating System = Windows 2000 SP4
> Sql Server 2000 sp3a
>
performing slow in order to determine if there is a memory
or hardware bottleneck and came up with the following
statistics. Please help diagnose this and let me know if
we need to increase memory or configure something in sql
server. This is an average of statistics collected
during peak periods 19 times. Thanks for your help. I
already suggested that ram needs to be added to the
server, but I need more assured technical justification
as I am new to this. Thx.
PROCESSOR TIME
max 36.42%
Average 9.53%
System
Processor Queue Length (max) 1
MEMORY
Pages/sec 121
Available Mbytes 156
Page Faults/sec 735
total physical memory 1986302
total sql server memory - mb 1589485
Processes page fault - Sql Server 73
Maximum workspace memory 1081725
DISK
Disk Reads per second 244
Disk Writes per second 99
Average Disk Queue length 4
SQL SERVER GENERAL STATISTICS
# connections 15
SQL Server Buffer Manager cache hit ratio 94
Hardware Info
Hard drive :
C' drive total hd space = 33.9G space
available = 20.2 G
D' drive total hd space = 273G space
available = 272G
E' drive total hd space = 273G space
available = 172 G
F' drive total hd space = 273G space
available = 127 G
F' drive total hd space = 819G space
available = 431 G
CPU = 2.80 GHz
RAM = 2.09G
Operating System = Windows 2000 SP4
Sql Server 2000 sp3aalso check out the kb below for a more general overview of how to
troubleshoot performance issues
http://support.microsoft.com/defaul...kb;en-us;298475
Andy.
"Jasmine Gwendle" <anonymous@.discussions.microsoft.com> wrote in message
news:173c901c448ab$17949f00$a001280a@.phx
.gbl...
> I ran performance monitoring on one of my server that is
> performing slow in order to determine if there is a memory
> or hardware bottleneck and came up with the following
> statistics. Please help diagnose this and let me know if
> we need to increase memory or configure something in sql
> server. This is an average of statistics collected
> during peak periods 19 times. Thanks for your help. I
> already suggested that ram needs to be added to the
> server, but I need more assured technical justification
> as I am new to this. Thx.
> PROCESSOR TIME
> max 36.42%
> Average 9.53%
> System
> Processor Queue Length (max) 1
> MEMORY
> Pages/sec 121
> Available Mbytes 156
> Page Faults/sec 735
> total physical memory 1986302
> total sql server memory - mb 1589485
> Processes page fault - Sql Server 73
> Maximum workspace memory 1081725
> DISK
> Disk Reads per second 244
> Disk Writes per second 99
> Average Disk Queue length 4
> SQL SERVER GENERAL STATISTICS
> # connections 15
> SQL Server Buffer Manager cache hit ratio 94
> hardware Info
> Hard drive :
> C' drive total hd space = 33.9G space
> available = 20.2 G
> D' drive total hd space = 273G space
> available = 272G
> E' drive total hd space = 273G space
> available = 172 G
> F' drive total hd space = 273G space
> available = 127 G
> F' drive total hd space = 819G space
> available = 431 G
> CPU = 2.80 GHz
> RAM = 2.09G
> Operating System = Windows 2000 SP4
> Sql Server 2000 sp3a
>
Subscribe to:
Posts (Atom)