Hi,
I have an ERP system running on SQL server 2000. How do i actually go abt
doing performance tuning as a DBA ?
i can check the no of ppl using the ERP and their activities. but come to
slow performance , how do i actually go abt doing performance tuning ?
for example , for this functional module of the ERP system i will not be
able to tell what stored procedures/queries that are being executed ? even if
i do know , i won't be able to change as that might cause further problem (do
i need to call the vendor support in this case ?) and my support agreement
will not cover any issues caused by changing the database ..
In the above scenario , how should a dba go abt perfoming a db performance
tunning ?
hope those experienced dbas can give me some ideas on how it works
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200512/1
You should be able to use profiler to watch for long running procs, or procs
which are doing a lot of reads or writes. These are candidates to be tuned.
Also watch for stored procedure recompilations, etc.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:591855ccaf94c@.uwe...
> Hi,
> I have an ERP system running on SQL server 2000. How do i actually go abt
> doing performance tuning as a DBA ?
> i can check the no of ppl using the ERP and their activities. but come to
> slow performance , how do i actually go abt doing performance tuning ?
> for example , for this functional module of the ERP system i will not be
> able to tell what stored procedures/queries that are being executed ? even
> if
> i do know , i won't be able to change as that might cause further problem
> (do
> i need to call the vendor support in this case ?) and my support agreement
> will not cover any issues caused by changing the database ..
> In the above scenario , how should a dba go abt perfoming a db performance
> tunning ?
> hope those experienced dbas can give me some ideas on how it works
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...ation/200512/1
|||Hi,
would you recommend using a 3rd party s/w to monitor the SPs/Queries being
acccessed as i read somewhere that profiler is cpu-intensive and might "hang"
the SQL Server if running for a long period of time ?
tks & rdgs
Hilary Cotter wrote:[vbcol=seagreen]
>You should be able to use profiler to watch for long running procs, or procs
>which are doing a lot of reads or writes. These are candidates to be tuned.
>Also watch for stored procedure recompilations, etc.
>[quoted text clipped - 18 lines]
Message posted via http://www.droptable.com
Showing posts with label dba. Show all posts
Showing posts with label dba. Show all posts
Friday, March 30, 2012
Performance Tuning
I am a SQL DBA newbie. I like to tune a database that I
know very little about. Other than creating indexes on
the tables that are frequently used, and rebuilding them
occasionally, can you list the different tuning tasks I
need to do please. Thank you.
Dianemonitor your slowest sprocs and tune them individually. There is a template
in SQL Profiler that will catch sproc duration statistics for you.
check peformance counters (IO, RAM, CPU, Network) for bottlenecks
dont know what to fix if you dont know what is broken.
Greg Jackson
PDX, Oregon|||Thank you.
>--Original Message--
>monitor your slowest sprocs and tune them individually.
There is a template
>in SQL Profiler that will catch sproc duration statistics
for you.
>
>check peformance counters (IO, RAM, CPU, Network) for
bottlenecks
>dont know what to fix if you dont know what is broken.
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||Here are some links that may help:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/operate/opsguide/default.asp
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly
SQL Server MVP
"Diane Siona" <anonymous@.discussions.microsoft.com> wrote in message
news:abeb01c3ec26$6f7ab150$a301280a@.phx.gbl...
> I am a SQL DBA newbie. I like to tune a database that I
> know very little about. Other than creating indexes on
> the tables that are frequently used, and rebuilding them
> occasionally, can you list the different tuning tasks I
> need to do please. Thank you.
> Diane
know very little about. Other than creating indexes on
the tables that are frequently used, and rebuilding them
occasionally, can you list the different tuning tasks I
need to do please. Thank you.
Dianemonitor your slowest sprocs and tune them individually. There is a template
in SQL Profiler that will catch sproc duration statistics for you.
check peformance counters (IO, RAM, CPU, Network) for bottlenecks
dont know what to fix if you dont know what is broken.
Greg Jackson
PDX, Oregon|||Thank you.
>--Original Message--
>monitor your slowest sprocs and tune them individually.
There is a template
>in SQL Profiler that will catch sproc duration statistics
for you.
>
>check peformance counters (IO, RAM, CPU, Network) for
bottlenecks
>dont know what to fix if you dont know what is broken.
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||Here are some links that may help:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/operate/opsguide/default.asp
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly
SQL Server MVP
"Diane Siona" <anonymous@.discussions.microsoft.com> wrote in message
news:abeb01c3ec26$6f7ab150$a301280a@.phx.gbl...
> I am a SQL DBA newbie. I like to tune a database that I
> know very little about. Other than creating indexes on
> the tables that are frequently used, and rebuilding them
> occasionally, can you list the different tuning tasks I
> need to do please. Thank you.
> Diane
Performance Tuning
I am a SQL DBA newbie. I like to tune a database that I
know very little about. Other than creating indexes on
the tables that are frequently used, and rebuilding them
occasionally, can you list the different tuning tasks I
need to do please. Thank you.
Dianemonitor your slowest sprocs and tune them individually. There is a template
in SQL Profiler that will catch sproc duration statistics for you.
check peformance counters (IO, RAM, CPU, Network) for bottlenecks
dont know what to fix if you dont know what is broken.
Greg Jackson
PDX, Oregon|||Thank you.
>--Original Message--
>monitor your slowest sprocs and tune them individually.
There is a template
>in SQL Profiler that will catch sproc duration statistics
for you.
>
>check peformance counters (IO, RAM, CPU, Network) for
bottlenecks
>dont know what to fix if you dont know what is broken.
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||Here are some links that may help:
http://www.microsoft.com/technet/tr...ide/default.asp
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly
SQL Server MVP
"Diane Siona" <anonymous@.discussions.microsoft.com> wrote in message
news:abeb01c3ec26$6f7ab150$a301280a@.phx.gbl...
> I am a SQL DBA newbie. I like to tune a database that I
> know very little about. Other than creating indexes on
> the tables that are frequently used, and rebuilding them
> occasionally, can you list the different tuning tasks I
> need to do please. Thank you.
> Dianesql
know very little about. Other than creating indexes on
the tables that are frequently used, and rebuilding them
occasionally, can you list the different tuning tasks I
need to do please. Thank you.
Dianemonitor your slowest sprocs and tune them individually. There is a template
in SQL Profiler that will catch sproc duration statistics for you.
check peformance counters (IO, RAM, CPU, Network) for bottlenecks
dont know what to fix if you dont know what is broken.
Greg Jackson
PDX, Oregon|||Thank you.
>--Original Message--
>monitor your slowest sprocs and tune them individually.
There is a template
>in SQL Profiler that will catch sproc duration statistics
for you.
>
>check peformance counters (IO, RAM, CPU, Network) for
bottlenecks
>dont know what to fix if you dont know what is broken.
>
>Greg Jackson
>PDX, Oregon
>
>.
>|||Here are some links that may help:
http://www.microsoft.com/technet/tr...ide/default.asp
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly
SQL Server MVP
"Diane Siona" <anonymous@.discussions.microsoft.com> wrote in message
news:abeb01c3ec26$6f7ab150$a301280a@.phx.gbl...
> I am a SQL DBA newbie. I like to tune a database that I
> know very little about. Other than creating indexes on
> the tables that are frequently used, and rebuilding them
> occasionally, can you list the different tuning tasks I
> need to do please. Thank you.
> Dianesql
Tuesday, March 20, 2012
Performance Problem - Request for advice
I am a DBA working for a large business in the North East of England.
One of our main Database servers (SQL Server 2000 SP4) is configured as follows:
Storage - 1. Logical Drive configured as RAID5 with about 800MB of storage, 40% free space. Used for main database files. 8 Physical drives.
- 2. Logical Drive configured as RAID1 with about 140MB of storage, 20% free space. Used for sql log files.
Both 1 and 2 are on the same controller (SCSI).
- 3. System Drive (C:)
Storage configuration was done by an external consultancy and disk performance (when benchmarked) was excellent.
Memory - 8GB of memory 6.5GB allocated to SQL Server
Operating System: Windows server 2003 Enterprise Edition
SQL Server: 2000 SP4
We would appreciate your advice regarding the following problem that we have on busy times:
Typical number of users/connections to SQL Server is between 200 to 250 (sysprocesses)
Typical number of active processes can vary between 5 to 30 (all sorts of applications)
The problem we have is that on some busy days a combination of processes grinding the server to a halt.
When we check the activity (normally using sp_who2) we can see that there are no blocked processes, cpu and DiskIO progressing as normal
(some values maybe large but not beyond expectation).
However, when "the problem" occurs, everything (queries, updates, etc) is extremely slow for 5-20 minutes until the bottleneck is freed.
We would appreciate any advice (tempdb?, disk bottleneck?, etc) and recommendation for any useful tools.
Thank you,You say when the bottleneck is freed...do you know what it is? Do you assume that it's freed because performance come back.
IMHO, I would not mess with memory allocation. I would let SQL Server do whatever it wants
BTW, the number of users/connections seems pretty low.
My guess is that there is some background batch job kibking off during that time which is a pig....|||Hows your tempdb during the time of extremely slow?|||Other simple stuff,
Are your statistics upto date on tables?
Is your disk storage fragmented?
Any bad disk warnings from the raid control?
The memory allocation for db growth (i.e 10% or 10MB) in the Transaction log can bugger it up too depending on it's growth and backups.
..and the killer... any virus checking going on?
I've had issues where {insert top end brand name here} have caused massive performance reductions due to unnecessary scheduled scans (company policy) or signature update downloads.
Is someone starting up a games server on the box?
Cheers
Phil
--
Special one just for you:
Police in Yorkshire are concerned about an alarming rise in the new trend of drug takers using dentists's syringes to inject drugs directly into the mouth.
They have described this tactic as "E by Gum":shocked:
One of our main Database servers (SQL Server 2000 SP4) is configured as follows:
Storage - 1. Logical Drive configured as RAID5 with about 800MB of storage, 40% free space. Used for main database files. 8 Physical drives.
- 2. Logical Drive configured as RAID1 with about 140MB of storage, 20% free space. Used for sql log files.
Both 1 and 2 are on the same controller (SCSI).
- 3. System Drive (C:)
Storage configuration was done by an external consultancy and disk performance (when benchmarked) was excellent.
Memory - 8GB of memory 6.5GB allocated to SQL Server
Operating System: Windows server 2003 Enterprise Edition
SQL Server: 2000 SP4
We would appreciate your advice regarding the following problem that we have on busy times:
Typical number of users/connections to SQL Server is between 200 to 250 (sysprocesses)
Typical number of active processes can vary between 5 to 30 (all sorts of applications)
The problem we have is that on some busy days a combination of processes grinding the server to a halt.
When we check the activity (normally using sp_who2) we can see that there are no blocked processes, cpu and DiskIO progressing as normal
(some values maybe large but not beyond expectation).
However, when "the problem" occurs, everything (queries, updates, etc) is extremely slow for 5-20 minutes until the bottleneck is freed.
We would appreciate any advice (tempdb?, disk bottleneck?, etc) and recommendation for any useful tools.
Thank you,You say when the bottleneck is freed...do you know what it is? Do you assume that it's freed because performance come back.
IMHO, I would not mess with memory allocation. I would let SQL Server do whatever it wants
BTW, the number of users/connections seems pretty low.
My guess is that there is some background batch job kibking off during that time which is a pig....|||Hows your tempdb during the time of extremely slow?|||Other simple stuff,
Are your statistics upto date on tables?
Is your disk storage fragmented?
Any bad disk warnings from the raid control?
The memory allocation for db growth (i.e 10% or 10MB) in the Transaction log can bugger it up too depending on it's growth and backups.
..and the killer... any virus checking going on?
I've had issues where {insert top end brand name here} have caused massive performance reductions due to unnecessary scheduled scans (company policy) or signature update downloads.
Is someone starting up a games server on the box?
Cheers
Phil
--
Special one just for you:
Police in Yorkshire are concerned about an alarming rise in the new trend of drug takers using dentists's syringes to inject drugs directly into the mouth.
They have described this tactic as "E by Gum":shocked:
Subscribe to:
Posts (Atom)