Performance Monitoring

I am trying to run some performance monitor counters against our database
server and am a little confused by some of the figures. I am monitoring the
following
Network - Bytes Total/Sec
Physical Disk - %Disk Time (Database Drive)
Physical Disk - %Disk Read Time (Database Drive)
Physical Disk - %Disk Write Time (Database Drive)
Physical Disk - %Idle Time (database Drive)
Processor - %Privileged Time (Total)
Processor - %Processor Time (Total)
SQLServer : Databases - Active Transactions
SQLServer : General Statistics - User Connections
every 15 seconds for the duration of the working day.
The figures from the % Disk Time don't make sence to me for instance for one
day the figures are
Minimum value = 0.078882822
Maximum Value = 21856.8565
Average Value = 1686.297629
What are these figures measured in? This is a SAN connection if it makes any
difference.
thanks
GavGav
Do you observe any perfomance decrease?
http://msdn2.microsoft.com/en-us/library/ms175903.aspx
http://www.oreillynet.com/pub/a/network/2002/01/18/diskperf.html
"Gav" <gav@.nospam.com> wrote in message
news:e1x9cP10HHA.4476@.TK2MSFTNGP06.phx.gbl...
>I am trying to run some performance monitor counters against our database
>server and am a little confused by some of the figures. I am monitoring the
>following
> Network - Bytes Total/Sec
> Physical Disk - %Disk Time (Database Drive)
> Physical Disk - %Disk Read Time (Database Drive)
> Physical Disk - %Disk Write Time (Database Drive)
> Physical Disk - %Idle Time (database Drive)
> Processor - %Privileged Time (Total)
> Processor - %Processor Time (Total)
> SQLServer : Databases - Active Transactions
> SQLServer : General Statistics - User Connections
> every 15 seconds for the duration of the working day.
> The figures from the % Disk Time don't make sence to me for instance for
> one day the figures are
> Minimum value = 0.078882822
> Maximum Value = 21856.8565
> Average Value = 1686.297629
> What are these figures measured in? This is a SAN connection if it makes
> any difference.
> thanks
> Gav
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OEVqmY10HHA.4928@.TK2MSFTNGP03.phx.gbl...
> Gav
> Do you observe any perfomance decrease?
> http://msdn2.microsoft.com/en-us/library/ms175903.aspx
> http://www.oreillynet.com/pub/a/network/2002/01/18/diskperf.html
>
> "Gav" <gav@.nospam.com> wrote in message
> news:e1x9cP10HHA.4476@.TK2MSFTNGP06.phx.gbl...
We are having some performance issues, not major at the mooment but
certainly things could be better. The database is for our SAP system and
from within SAP I can see that at certain times of the day the database
seems to slow down a bit. I'm trying to monitor the database server now to
match up the slowness in SAP with stats from the server. I'm still trying to
refine the counters I am using to get a good picture of the activity on the
server, I'll add in the disk queue length tomorrow and see what it is doing.
thanks for the links
Gav|||When you say "certain times of the day", are you able to provide any more
detail about what happens during those times? Is there any specific reports
etc that are being run then?
Some other ideas:
Is it simply due to more workload?
You might also consider capturing SQL Statistics : Batches / sec to see if
more queries are being submitted to the server during slow downs.
Is it related to blocking?
You didn't say whether this is SQL 2000 or 2005, but if 2005, I suggest you
use the Profiler's Blocked Process Report (under Errors & Warnings). If
you're using SQL 2000, perhaps some simple polling with this query might
help:
http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2005/10/02/50.aspx
Is it related to file growth?
Track the Databases / file growth events in SQL Profiler - maybe your
transaction logs are being auto-grown when the slow-down occurs?
Is it related to query optimisation problems?
Track Stored Procedure: RPC Completed & TSQL:Batch Completed with Profiler
and see if you have any run-away queries which might need tuning / indexing
(might need discussion with SAP). Look for long running queries or queries
with high Reads.
Are your indexes being rebuilt?
Often, if index maintenance tasks stop working for one reason or another,
index stats might not be updated & SQL Server suddenly starts scanning
tables to execute queries. Make sure any scheduled maintenance tasks are
completing successfully..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Gav" <gav@.nospam.com> wrote in message
news:eqPARl10HHA.4932@.TK2MSFTNGP03.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OEVqmY10HHA.4928@.TK2MSFTNGP03.phx.gbl...
>> Gav
>> Do you observe any perfomance decrease?
>> http://msdn2.microsoft.com/en-us/library/ms175903.aspx
>> http://www.oreillynet.com/pub/a/network/2002/01/18/diskperf.html
>>
>> "Gav" <gav@.nospam.com> wrote in message
>> news:e1x9cP10HHA.4476@.TK2MSFTNGP06.phx.gbl...
> We are having some performance issues, not major at the mooment but
> certainly things could be better. The database is for our SAP system and
> from within SAP I can see that at certain times of the day the database
> seems to slow down a bit. I'm trying to monitor the database server now to
> match up the slowness in SAP with stats from the server. I'm still trying
> to refine the counters I am using to get a good picture of the activity on
> the server, I'll add in the disk queue length tomorrow and see what it is
> doing.
> thanks for the links
> Gav
>|||"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:uBD4$Y30HHA.1208@.TK2MSFTNGP03.phx.gbl...
> When you say "certain times of the day", are you able to provide any more
> detail about what happens during those times? Is there any specific
> reports etc that are being run then?
No large reports appear to be running at the times of slowdown. I have a
couple of users that inform me when things slow down and I check workload
(SAP level), usually things look pretty normal.
> Some other ideas:
> Is it simply due to more workload?
> You might also consider capturing SQL Statistics : Batches / sec to see if
> more queries are being submitted to the server during slow downs.
Quite possibly just load, but this is what I need to establish. Noticed the
"batches/sec" couter in SAP earlier today and have it on my list to add into
the counters tomorrow.
> Is it related to blocking?
> You didn't say whether this is SQL 2000 or 2005, but if 2005, I suggest
> you use the Profiler's Blocked Process Report (under Errors & Warnings).
> If you're using SQL 2000, perhaps some simple polling with this query
> might help:
> http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2005/10/02/50.aspx
Sorry it is SQL 2000. Nope its not a blocking problem I've already checked
this out.
> Is it related to file growth?
> Track the Databases / file growth events in SQL Profiler - maybe your
> transaction logs are being auto-grown when the slow-down occurs?
Nope, I manage and monitor all the database files manually, auto grow is
turned off so I can manage growth.
> Is it related to query optimisation problems?
> Track Stored Procedure: RPC Completed & TSQL:Batch Completed with Profiler
> and see if you have any run-away queries which might need tuning /
> indexing (might need discussion with SAP). Look for long running queries
> or queries with high Reads.
Does not appear to be, I have done a SQL trace through SAP to see what is
happening when a user sees the slowdown. When I pick through the trace and
run the query in query analizer the query runs great. Plus I get users
telling me that things have been running fine then we get some time of poor
responces where the user is doing the same task (for instance entering
invoices all day).
> Are your indexes being rebuilt?
> Often, if index maintenance tasks stop working for one reason or another,
> index stats might not be updated & SQL Server suddenly starts scanning
> tables to execute queries. Make sure any scheduled maintenance tasks are
> completing successfully..
From what I can see in SAP stats there are hardly any table scans happening.
What do you mean by index maintenance tasks?
Its early days yet so I've a lot to look at, just trying to fully understand
the performance counters so I can cross reference them to my SAP stats.
thanks for your detailed response.
Gav
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performance
> http://www.SQLBenchmarkPro.com
>
> "Gav" <gav@.nospam.com> wrote in message
> news:eqPARl10HHA.4932@.TK2MSFTNGP03.phx.gbl...
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OEVqmY10HHA.4928@.TK2MSFTNGP03.phx.gbl...
>> Gav
>> Do you observe any perfomance decrease?
>> http://msdn2.microsoft.com/en-us/library/ms175903.aspx
>> http://www.oreillynet.com/pub/a/network/2002/01/18/diskperf.html
>>
>> "Gav" <gav@.nospam.com> wrote in message
>> news:e1x9cP10HHA.4476@.TK2MSFTNGP06.phx.gbl...
>> We are having some performance issues, not major at the mooment but
>> certainly things could be better. The database is for our SAP system and
>> from within SAP I can see that at certain times of the day the database
>> seems to slow down a bit. I'm trying to monitor the database server now
>> to match up the slowness in SAP with stats from the server. I'm still
>> trying to refine the counters I am using to get a good picture of the
>> activity on the server, I'll add in the disk queue length tomorrow and
>> see what it is doing.
>> thanks for the links
>> Gav
>|||Hi Gav
Index maintenance jobs usually rebuild indexes, either via SQL Server
Maintenance Tasks or via custom-scripted jobs (usually using DBCC
DBREINDEX). In addition to de-fragmenting the layout of the indexes in the
physical database, this also recomputes index histogram statistics which
tell SQL Server's query optimiser how "selective" index columns (singular or
compound) are. If this isn't performed regularly, SQL Server's query
optimiser sometimes changes query execution plans as it cannot be sure
whether using index seeks is more efficient than simply scanning the tables
accessed by those queries. Hence, index maintenance is a routine part of
database management. If you're not doing this (or if SAP isn't doing this
already) you really need to look into it.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Gav" <gav@.nospam.com> wrote in message
news:%23vw2n530HHA.4932@.TK2MSFTNGP03.phx.gbl...
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:uBD4$Y30HHA.1208@.TK2MSFTNGP03.phx.gbl...
>> When you say "certain times of the day", are you able to provide any more
>> detail about what happens during those times? Is there any specific
>> reports etc that are being run then?
> No large reports appear to be running at the times of slowdown. I have a
> couple of users that inform me when things slow down and I check workload
> (SAP level), usually things look pretty normal.
>> Some other ideas:
>> Is it simply due to more workload?
>> You might also consider capturing SQL Statistics : Batches / sec to see
>> if more queries are being submitted to the server during slow downs.
> Quite possibly just load, but this is what I need to establish. Noticed
> the "batches/sec" couter in SAP earlier today and have it on my list to
> add into the counters tomorrow.
>> Is it related to blocking?
>> You didn't say whether this is SQL 2000 or 2005, but if 2005, I suggest
>> you use the Profiler's Blocked Process Report (under Errors & Warnings).
>> If you're using SQL 2000, perhaps some simple polling with this query
>> might help:
>> http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2005/10/02/50.aspx
> Sorry it is SQL 2000. Nope its not a blocking problem I've already checked
> this out.
>> Is it related to file growth?
>> Track the Databases / file growth events in SQL Profiler - maybe your
>> transaction logs are being auto-grown when the slow-down occurs?
> Nope, I manage and monitor all the database files manually, auto grow is
> turned off so I can manage growth.
>> Is it related to query optimisation problems?
>> Track Stored Procedure: RPC Completed & TSQL:Batch Completed with
>> Profiler and see if you have any run-away queries which might need tuning
>> / indexing (might need discussion with SAP). Look for long running
>> queries or queries with high Reads.
> Does not appear to be, I have done a SQL trace through SAP to see what is
> happening when a user sees the slowdown. When I pick through the trace and
> run the query in query analizer the query runs great. Plus I get users
> telling me that things have been running fine then we get some time of
> poor responces where the user is doing the same task (for instance
> entering invoices all day).
>> Are your indexes being rebuilt?
>> Often, if index maintenance tasks stop working for one reason or another,
>> index stats might not be updated & SQL Server suddenly starts scanning
>> tables to execute queries. Make sure any scheduled maintenance tasks are
>> completing successfully..
> From what I can see in SAP stats there are hardly any table scans
> happening. What do you mean by index maintenance tasks?
>
> Its early days yet so I've a lot to look at, just trying to fully
> understand the performance counters so I can cross reference them to my
> SAP stats.
> thanks for your detailed response.
> Gav
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> Benchmark your query performance
>> http://www.SQLBenchmarkPro.com
>>
>> "Gav" <gav@.nospam.com> wrote in message
>> news:eqPARl10HHA.4932@.TK2MSFTNGP03.phx.gbl...
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:OEVqmY10HHA.4928@.TK2MSFTNGP03.phx.gbl...
>> Gav
>> Do you observe any perfomance decrease?
>> http://msdn2.microsoft.com/en-us/library/ms175903.aspx
>> http://www.oreillynet.com/pub/a/network/2002/01/18/diskperf.html
>>
>> "Gav" <gav@.nospam.com> wrote in message
>> news:e1x9cP10HHA.4476@.TK2MSFTNGP06.phx.gbl...
>> We are having some performance issues, not major at the mooment but
>> certainly things could be better. The database is for our SAP system and
>> from within SAP I can see that at certain times of the day the database
>> seems to slow down a bit. I'm trying to monitor the database server now
>> to match up the slowness in SAP with stats from the server. I'm still
>> trying to refine the counters I am using to get a good picture of the
>> activity on the server, I'll add in the disk queue length tomorrow and
>> see what it is doing.
>> thanks for the links
>> Gav
>>
>|||"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:eBKaBJ90HHA.1208@.TK2MSFTNGP05.phx.gbl...
> Hi Gav
> Index maintenance jobs usually rebuild indexes, either via SQL Server
> Maintenance Tasks or via custom-scripted jobs (usually using DBCC
> DBREINDEX). In addition to de-fragmenting the layout of the indexes in the
> physical database, this also recomputes index histogram statistics which
> tell SQL Server's query optimiser how "selective" index columns (singular
> or compound) are. If this isn't performed regularly, SQL Server's query
> optimiser sometimes changes query execution plans as it cannot be sure
> whether using index seeks is more efficient than simply scanning the
> tables accessed by those queries. Hence, index maintenance is a routine
> part of database management. If you're not doing this (or if SAP isn't
> doing this already) you really need to look into it.
Oke doke, thought I was missing something there for a moment. I already do
this, its not scheduled though I run it manually.