Good morning/afternoon,
We have two instances of sql-server 2k running. They start out fine
running about 50-100 meg of storage, then gradually increase to over 750mg
each, then one of the other stops responding and needs a restart.
Though I'm an old hand at database systems, I'm new to SQL-server and would
like to find a way to resolve this. I've run a couple of traces, but not
know what parameters to set, haven't really found a problem. (Though I did
add a couple of indexes which sped the system up.)
Where do we go from here?eg1,
By storage, do you mean memoy as in your subject line? Check the SQL Server
logand Windows event log to see if there are any messages that might offer a
hint. Check to see if your paging file is filling up (use Performance
Monitor or Task Manager).
The two instances might be fighting each other for memory resources. If all
else fails, try limiting the memory for each instance to 700 MB (or maybe a
little less). SQL Server is an aggressive user of system memory.
-- Bill
"eg1" <Bor@.kurzikstan.com> wrote in message
news:Xns98BA9DAEAED0BBorkurzikstancom@.20
7.46.248.16...
> Good morning/afternoon,
> We have two instances of sql-server 2k running. They start out fine
> running about 50-100 meg of storage, then gradually increase to over 750mg
> each, then one of the other stops responding and needs a restart.
> Though I'm an old hand at database systems, I'm new to SQL-server and
> would
> like to find a way to resolve this. I've run a couple of traces, but not
> know what parameters to set, haven't really found a problem. (Though I
> did
> add a couple of indexes which sped the system up.)
> Where do we go from here?
Showing posts with label diagnose. Show all posts
Showing posts with label diagnose. Show all posts
Friday, March 30, 2012
How to diagnose memory bloating?
Good morning/afternoon,
We have two instances of sql-server 2k running. They start out fine
running about 50-100 meg of storage, then gradually increase to over 750mg
each, then one of the other stops responding and needs a restart.
Though I'm an old hand at database systems, I'm new to SQL-server and would
like to find a way to resolve this. I've run a couple of traces, but not
know what parameters to set, haven't really found a problem. (Though I did
add a couple of indexes which sped the system up.)
Where do we go from here?eg1,
By storage, do you mean memoy as in your subject line? Check the SQL Server
logand Windows event log to see if there are any messages that might offer a
hint. Check to see if your paging file is filling up (use Performance
Monitor or Task Manager).
The two instances might be fighting each other for memory resources. If all
else fails, try limiting the memory for each instance to 700 MB (or maybe a
little less). SQL Server is an aggressive user of system memory.
-- Bill
"eg1" <Bor@.kurzikstan.com> wrote in message
news:Xns98BA9DAEAED0BBorkurzikstancom@.207.46.248.16...
> Good morning/afternoon,
> We have two instances of sql-server 2k running. They start out fine
> running about 50-100 meg of storage, then gradually increase to over 750mg
> each, then one of the other stops responding and needs a restart.
> Though I'm an old hand at database systems, I'm new to SQL-server and
> would
> like to find a way to resolve this. I've run a couple of traces, but not
> know what parameters to set, haven't really found a problem. (Though I
> did
> add a couple of indexes which sped the system up.)
> Where do we go from here?
We have two instances of sql-server 2k running. They start out fine
running about 50-100 meg of storage, then gradually increase to over 750mg
each, then one of the other stops responding and needs a restart.
Though I'm an old hand at database systems, I'm new to SQL-server and would
like to find a way to resolve this. I've run a couple of traces, but not
know what parameters to set, haven't really found a problem. (Though I did
add a couple of indexes which sped the system up.)
Where do we go from here?eg1,
By storage, do you mean memoy as in your subject line? Check the SQL Server
logand Windows event log to see if there are any messages that might offer a
hint. Check to see if your paging file is filling up (use Performance
Monitor or Task Manager).
The two instances might be fighting each other for memory resources. If all
else fails, try limiting the memory for each instance to 700 MB (or maybe a
little less). SQL Server is an aggressive user of system memory.
-- Bill
"eg1" <Bor@.kurzikstan.com> wrote in message
news:Xns98BA9DAEAED0BBorkurzikstancom@.207.46.248.16...
> Good morning/afternoon,
> We have two instances of sql-server 2k running. They start out fine
> running about 50-100 meg of storage, then gradually increase to over 750mg
> each, then one of the other stops responding and needs a restart.
> Though I'm an old hand at database systems, I'm new to SQL-server and
> would
> like to find a way to resolve this. I've run a couple of traces, but not
> know what parameters to set, haven't really found a problem. (Though I
> did
> add a couple of indexes which sped the system up.)
> Where do we go from here?
How to diagnose memory bloating?
Good morning/afternoon,
We have two instances of sql-server 2k running. They start out fine
running about 50-100 meg of storage, then gradually increase to over 750mg
each, then one of the other stops responding and needs a restart.
Though I'm an old hand at database systems, I'm new to SQL-server and would
like to find a way to resolve this. I've run a couple of traces, but not
know what parameters to set, haven't really found a problem. (Though I did
add a couple of indexes which sped the system up.)
Where do we go from here?
eg1,
By storage, do you mean memoy as in your subject line? Check the SQL Server
logand Windows event log to see if there are any messages that might offer a
hint. Check to see if your paging file is filling up (use Performance
Monitor or Task Manager).
The two instances might be fighting each other for memory resources. If all
else fails, try limiting the memory for each instance to 700 MB (or maybe a
little less). SQL Server is an aggressive user of system memory.
-- Bill
"eg1" <Bor@.kurzikstan.com> wrote in message
news:Xns98BA9DAEAED0BBorkurzikstancom@.207.46.248.1 6...
> Good morning/afternoon,
> We have two instances of sql-server 2k running. They start out fine
> running about 50-100 meg of storage, then gradually increase to over 750mg
> each, then one of the other stops responding and needs a restart.
> Though I'm an old hand at database systems, I'm new to SQL-server and
> would
> like to find a way to resolve this. I've run a couple of traces, but not
> know what parameters to set, haven't really found a problem. (Though I
> did
> add a couple of indexes which sped the system up.)
> Where do we go from here?
sql
We have two instances of sql-server 2k running. They start out fine
running about 50-100 meg of storage, then gradually increase to over 750mg
each, then one of the other stops responding and needs a restart.
Though I'm an old hand at database systems, I'm new to SQL-server and would
like to find a way to resolve this. I've run a couple of traces, but not
know what parameters to set, haven't really found a problem. (Though I did
add a couple of indexes which sped the system up.)
Where do we go from here?
eg1,
By storage, do you mean memoy as in your subject line? Check the SQL Server
logand Windows event log to see if there are any messages that might offer a
hint. Check to see if your paging file is filling up (use Performance
Monitor or Task Manager).
The two instances might be fighting each other for memory resources. If all
else fails, try limiting the memory for each instance to 700 MB (or maybe a
little less). SQL Server is an aggressive user of system memory.
-- Bill
"eg1" <Bor@.kurzikstan.com> wrote in message
news:Xns98BA9DAEAED0BBorkurzikstancom@.207.46.248.1 6...
> Good morning/afternoon,
> We have two instances of sql-server 2k running. They start out fine
> running about 50-100 meg of storage, then gradually increase to over 750mg
> each, then one of the other stops responding and needs a restart.
> Though I'm an old hand at database systems, I'm new to SQL-server and
> would
> like to find a way to resolve this. I've run a couple of traces, but not
> know what parameters to set, haven't really found a problem. (Though I
> did
> add a couple of indexes which sped the system up.)
> Where do we go from here?
sql
How to diagnose IO Problems
Recently our production database began experiencing very poor I/O
performance. How do I begin to narrow down the queries or tables that are
affecting this?It would be useful to know which platform is in use (SQL 2000 or SQL 2005),
because there are some changes in the way you can investigate.
1) Verify the server situation: is there any fragmentation problem
(especially on the pagefile)?
2) verify the cause of the wait, using DBCC SQLPERF(WAITSTATS) in SQL 2000
or querying the sys.dm_os_wait_stats DMV in SQL 2005;
you should be able to identify the correct area of investigation:
there is a simple example of using the SQL 2005 DMV:
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_type
3) Get some useful information about the involved resources using
master.dbo.sysperfinfo in SQL 2000 or sys.dm_os_performance_counters in SQL
2005;
4) Use ::fn_virtualfilestats in SQL 2000 or sys.dm_io_virtual_file_stats to
identify the more stressed database files:
here is an example of using the DMO:
WITH DBIO AS
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
JOIN sys.master_files AS MF
ON IVFS.database_id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id), MF.type
5)Pay attention to the transaction log of the critical databases (location,
number of virtual logs, etc.) and dedicate special attention to the tempDB
(it is usually the more stressed file of a SQL Instance); look at cursor
usage, temporary tables and table variables.
6)Finally, when you have sufficient elements of investigation, you can
profile your instance or your databases with a SQL Profiler trace using the
appropriate event classes.
In this moment i think is premature suggest you which kind of profiling
could be the best: you should decide it when you will have gathered all of
the infos from the previously described steps.
Gilberto
"Dan" wrote:
> Recently our production database began experiencing very poor I/O
> performance. How do I begin to narrow down the queries or tables that are
> affecting this?
performance. How do I begin to narrow down the queries or tables that are
affecting this?It would be useful to know which platform is in use (SQL 2000 or SQL 2005),
because there are some changes in the way you can investigate.
1) Verify the server situation: is there any fragmentation problem
(especially on the pagefile)?
2) verify the cause of the wait, using DBCC SQLPERF(WAITSTATS) in SQL 2000
or querying the sys.dm_os_wait_stats DMV in SQL 2005;
you should be able to identify the correct area of investigation:
there is a simple example of using the SQL 2005 DMV:
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_type
3) Get some useful information about the involved resources using
master.dbo.sysperfinfo in SQL 2000 or sys.dm_os_performance_counters in SQL
2005;
4) Use ::fn_virtualfilestats in SQL 2000 or sys.dm_io_virtual_file_stats to
identify the more stressed database files:
here is an example of using the DMO:
WITH DBIO AS
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
JOIN sys.master_files AS MF
ON IVFS.database_id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id), MF.type
5)Pay attention to the transaction log of the critical databases (location,
number of virtual logs, etc.) and dedicate special attention to the tempDB
(it is usually the more stressed file of a SQL Instance); look at cursor
usage, temporary tables and table variables.
6)Finally, when you have sufficient elements of investigation, you can
profile your instance or your databases with a SQL Profiler trace using the
appropriate event classes.
In this moment i think is premature suggest you which kind of profiling
could be the best: you should decide it when you will have gathered all of
the infos from the previously described steps.
Gilberto
"Dan" wrote:
> Recently our production database began experiencing very poor I/O
> performance. How do I begin to narrow down the queries or tables that are
> affecting this?
How to diagnose IO Problems
Recently our production database began experiencing very poor I/O
performance. How do I begin to narrow down the queries or tables that are
affecting this?It would be useful to know which platform is in use (SQL 2000 or SQL 2005),
because there are some changes in the way you can investigate.
1) Verify the server situation: is there any fragmentation problem
(especially on the pagefile)?
2) verify the cause of the wait, using DBCC SQLPERF(WAITSTATS) in SQL 2000
or querying the sys.dm_os_wait_stats DMV in SQL 2005;
you should be able to identify the correct area of investigation:
there is a simple example of using the SQL 2005 DMV:
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_type
3) Get some useful information about the involved resources using
master.dbo.sysperfinfo in SQL 2000 or sys.dm_os_performance_counters in SQL
2005;
4) Use ::fn_virtualfilestats in SQL 2000 or sys.dm_io_virtual_file_stats to
identify the more stressed database files:
here is an example of using the DMO:
WITH DBIO AS
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
JOIN sys.master_files AS MF
ON IVFS.database_id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id), MF.type
5)Pay attention to the transaction log of the critical databases (location,
number of virtual logs, etc.) and dedicate special attention to the tempDB
(it is usually the more stressed file of a SQL Instance); look at cursor
usage, temporary tables and table variables.
6)Finally, when you have sufficient elements of investigation, you can
profile your instance or your databases with a SQL Profiler trace using the
appropriate event classes.
In this moment i think is premature suggest you which kind of profiling
could be the best: you should decide it when you will have gathered all of
the infos from the previously described steps.
Gilberto
"Dan" wrote:
> Recently our production database began experiencing very poor I/O
> performance. How do I begin to narrow down the queries or tables that are
> affecting this?
performance. How do I begin to narrow down the queries or tables that are
affecting this?It would be useful to know which platform is in use (SQL 2000 or SQL 2005),
because there are some changes in the way you can investigate.
1) Verify the server situation: is there any fragmentation problem
(especially on the pagefile)?
2) verify the cause of the wait, using DBCC SQLPERF(WAITSTATS) in SQL 2000
or querying the sys.dm_os_wait_stats DMV in SQL 2005;
you should be able to identify the correct area of investigation:
there is a simple example of using the SQL 2005 DMV:
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_type
3) Get some useful information about the involved resources using
master.dbo.sysperfinfo in SQL 2000 or sys.dm_os_performance_counters in SQL
2005;
4) Use ::fn_virtualfilestats in SQL 2000 or sys.dm_io_virtual_file_stats to
identify the more stressed database files:
here is an example of using the DMO:
WITH DBIO AS
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
JOIN sys.master_files AS MF
ON IVFS.database_id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id), MF.type
5)Pay attention to the transaction log of the critical databases (location,
number of virtual logs, etc.) and dedicate special attention to the tempDB
(it is usually the more stressed file of a SQL Instance); look at cursor
usage, temporary tables and table variables.
6)Finally, when you have sufficient elements of investigation, you can
profile your instance or your databases with a SQL Profiler trace using the
appropriate event classes.
In this moment i think is premature suggest you which kind of profiling
could be the best: you should decide it when you will have gathered all of
the infos from the previously described steps.
Gilberto
"Dan" wrote:
> Recently our production database began experiencing very poor I/O
> performance. How do I begin to narrow down the queries or tables that are
> affecting this?
How to diagnose IO Problems
Recently our production database began experiencing very poor I/O
performance. How do I begin to narrow down the queries or tables that are
affecting this?
It would be useful to know which platform is in use (SQL 2000 or SQL 2005),
because there are some changes in the way you can investigate.
1) Verify the server situation: is there any fragmentation problem
(especially on the pagefile)?
2) verify the cause of the wait, using DBCC SQLPERF(WAITSTATS) in SQL 2000
or querying the sys.dm_os_wait_stats DMV in SQL 2005;
you should be able to identify the correct area of investigation:
there is a simple example of using the SQL 2005 DMV:
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_type
3) Get some useful information about the involved resources using
master.dbo.sysperfinfo in SQL 2000 or sys.dm_os_performance_counters in SQL
2005;
4) Use ::fn_virtualfilestats in SQL 2000 or sys.dm_io_virtual_file_stats to
identify the more stressed database files:
here is an example of using the DMO:
WITH DBIO AS
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
JOIN sys.master_files AS MF
ON IVFS.database_id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id), MF.type
5)Pay attention to the transaction log of the critical databases (location,
number of virtual logs, etc.) and dedicate special attention to the tempDB
(it is usually the more stressed file of a SQL Instance); look at cursor
usage, temporary tables and table variables.
6)Finally, when you have sufficient elements of investigation, you can
profile your instance or your databases with a SQL Profiler trace using the
appropriate event classes.
In this moment i think is premature suggest you which kind of profiling
could be the best: you should decide it when you will have gathered all of
the infos from the previously described steps.
Gilberto
"Dan" wrote:
> Recently our production database began experiencing very poor I/O
> performance. How do I begin to narrow down the queries or tables that are
> affecting this?
performance. How do I begin to narrow down the queries or tables that are
affecting this?
It would be useful to know which platform is in use (SQL 2000 or SQL 2005),
because there are some changes in the way you can investigate.
1) Verify the server situation: is there any fragmentation problem
(especially on the pagefile)?
2) verify the cause of the wait, using DBCC SQLPERF(WAITSTATS) in SQL 2000
or querying the sys.dm_os_wait_stats DMV in SQL 2005;
you should be able to identify the correct area of investigation:
there is a simple example of using the SQL 2005 DMV:
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_type
3) Get some useful information about the involved resources using
master.dbo.sysperfinfo in SQL 2000 or sys.dm_os_performance_counters in SQL
2005;
4) Use ::fn_virtualfilestats in SQL 2000 or sys.dm_io_virtual_file_stats to
identify the more stressed database files:
here is an example of using the DMO:
WITH DBIO AS
SELECT
DB_NAME(IVFS.database_id) AS db,
CASE WHEN MF.type = 1 THEN 'log' ELSE 'data' END AS file_type,
SUM(IVFS.num_of_bytes_read + IVFS.num_of_bytes_written) AS io,
SUM(IVFS.io_stall) AS io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS IVFS
JOIN sys.master_files AS MF
ON IVFS.database_id = MF.database_id
AND IVFS.file_id = MF.file_id
GROUP BY DB_NAME(IVFS.database_id), MF.type
5)Pay attention to the transaction log of the critical databases (location,
number of virtual logs, etc.) and dedicate special attention to the tempDB
(it is usually the more stressed file of a SQL Instance); look at cursor
usage, temporary tables and table variables.
6)Finally, when you have sufficient elements of investigation, you can
profile your instance or your databases with a SQL Profiler trace using the
appropriate event classes.
In this moment i think is premature suggest you which kind of profiling
could be the best: you should decide it when you will have gathered all of
the infos from the previously described steps.
Gilberto
"Dan" wrote:
> Recently our production database began experiencing very poor I/O
> performance. How do I begin to narrow down the queries or tables that are
> affecting this?
How to Diagnose IO Performance On A SAN
I'm runing SQL2000 with SP4 on Win 2003 Ent Srvr and use a SAN for the
database data and log files.
The SAN drives are configured as RAID5.
My question is when diagnosing IO performance issues, what perfmon counters
are recommended for use when the databases are on a SAN? I used to rely on
current disk queue length but have been told that counter is skewed when the
storage is SAN. Also if other counters are recommended, what would be the
thresholds for judging poor performance? Any utilites recommended?
thksTom,
See if this helps.
http://blogs.msdn.com/sqlcat/archive/2005/11/17/493944.aspx
AMB
"Tom Frost" wrote:
> I'm runing SQL2000 with SP4 on Win 2003 Ent Srvr and use a SAN for the
> database data and log files.
> The SAN drives are configured as RAID5.
> My question is when diagnosing IO performance issues, what perfmon counters
> are recommended for use when the databases are on a SAN? I used to rely on
> current disk queue length but have been told that counter is skewed when the
> storage is SAN. Also if other counters are recommended, what would be the
> thresholds for judging poor performance? Any utilites recommended?
> thks|||Hi Tom
"Tom Frost" wrote:
> I'm runing SQL2000 with SP4 on Win 2003 Ent Srvr and use a SAN for the
> database data and log files.
> The SAN drives are configured as RAID5.
> My question is when diagnosing IO performance issues, what perfmon counters
> are recommended for use when the databases are on a SAN? I used to rely on
> current disk queue length but have been told that counter is skewed when the
> storage is SAN. Also if other counters are recommended, what would be the
> thresholds for judging poor performance? Any utilites recommended?
> thks
Check out http://support.microsoft.com/kb/224587/
http://www.sql-server-performance.com/ew_san.asp
http://www.sql-server-performance.com/qdpma/inst_3_pmlogs.as
http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp
You SAN vendor should also have tools which you can monitor the performance,
this will be necessary to rule out interface issues i.e. the SAN is fine, but
the OS thinks it's slow!
John|||Hi,
I understand that you would like to know what performance counters should
be used to troubleshoot your SQL Server 2000 performance issue.
If I have misunderstood, please let me know.
I recommend that you refer to the following articles for monitoring your
SQL Server Performance:
TechNet Support WebCast:Performance troubleshooting and analysis in
Microsoft SQL Server 2000
http://support.microsoft.com/kb/838622
Microsoft SQL Server 2000 RDBMS Performance Tuning Guide for Data
Warehousing
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
SQL Server 2000 I/O Basics (SQLIOStress.exe for stress test)
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.m
spx
If you have any other questions or concerns, please feel free to let me
know.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi,
Just check with you to see if the suggestions were helpful. Please let us
know if you would like further assistance.
Have a great day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
database data and log files.
The SAN drives are configured as RAID5.
My question is when diagnosing IO performance issues, what perfmon counters
are recommended for use when the databases are on a SAN? I used to rely on
current disk queue length but have been told that counter is skewed when the
storage is SAN. Also if other counters are recommended, what would be the
thresholds for judging poor performance? Any utilites recommended?
thksTom,
See if this helps.
http://blogs.msdn.com/sqlcat/archive/2005/11/17/493944.aspx
AMB
"Tom Frost" wrote:
> I'm runing SQL2000 with SP4 on Win 2003 Ent Srvr and use a SAN for the
> database data and log files.
> The SAN drives are configured as RAID5.
> My question is when diagnosing IO performance issues, what perfmon counters
> are recommended for use when the databases are on a SAN? I used to rely on
> current disk queue length but have been told that counter is skewed when the
> storage is SAN. Also if other counters are recommended, what would be the
> thresholds for judging poor performance? Any utilites recommended?
> thks|||Hi Tom
"Tom Frost" wrote:
> I'm runing SQL2000 with SP4 on Win 2003 Ent Srvr and use a SAN for the
> database data and log files.
> The SAN drives are configured as RAID5.
> My question is when diagnosing IO performance issues, what perfmon counters
> are recommended for use when the databases are on a SAN? I used to rely on
> current disk queue length but have been told that counter is skewed when the
> storage is SAN. Also if other counters are recommended, what would be the
> thresholds for judging poor performance? Any utilites recommended?
> thks
Check out http://support.microsoft.com/kb/224587/
http://www.sql-server-performance.com/ew_san.asp
http://www.sql-server-performance.com/qdpma/inst_3_pmlogs.as
http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp
You SAN vendor should also have tools which you can monitor the performance,
this will be necessary to rule out interface issues i.e. the SAN is fine, but
the OS thinks it's slow!
John|||Hi,
I understand that you would like to know what performance counters should
be used to troubleshoot your SQL Server 2000 performance issue.
If I have misunderstood, please let me know.
I recommend that you refer to the following articles for monitoring your
SQL Server Performance:
TechNet Support WebCast:Performance troubleshooting and analysis in
Microsoft SQL Server 2000
http://support.microsoft.com/kb/838622
Microsoft SQL Server 2000 RDBMS Performance Tuning Guide for Data
Warehousing
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/rdbmspft.mspx
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
SQL Server 2000 I/O Basics (SQLIOStress.exe for stress test)
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.m
spx
If you have any other questions or concerns, please feel free to let me
know.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi,
Just check with you to see if the suggestions were helpful. Please let us
know if you would like further assistance.
Have a great day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
How to diagnose a deadly embrace
I have an application which is running on 40 active workstations. I am
encountering lock outs many times a day. When I run the stored procedure
"sp_who2" I can see the spids that are blocked and by which other spid.
If I "kill xx" where xx is the head of the blocking chain the system frees
up. When doing the "sp_who2" I see a command column which only shows the
start of a command like "insert", 'update", "select" or "AWAITING COMMAND".
Is there a way to see the whole command to isolate which table(s) are
causing the problem?
Are there other tools to help me out?
Desparately in need of help...
Mark
Mark Butler wrote:
> I have an application which is running on 40 active workstations. I
> am encountering lock outs many times a day. When I run the stored
> procedure "sp_who2" I can see the spids that are blocked and by which
> other spid.
> If I "kill xx" where xx is the head of the blocking chain the system
> frees up. When doing the "sp_who2" I see a command column which only
> shows the start of a command like "insert", 'update", "select" or
> "AWAITING COMMAND". Is there a way to see the whole command to
> isolate which table(s) are causing the problem?
> Are there other tools to help me out?
> Desparately in need of help...
> Mark
You can use Profiler to see what command are starting and not completing
or committing as the case may be. If you look at the starting and
completed events, you can see if something did not complete (missing
completed event). That might mean that data was not rolled back. It
sounds like you might have open transactions. Make sure no one is using
SQL Enterprise Manager (or other tools that do not fetch all data at
once) to edit data in tables as they will leave open locks on data.
If the same user is responsibl, you can filter the Profiler data. The
output may generate a lot of information. I would start looking at
SQL:BatchStarting/Completed and RPC:Starting/Completed. If yo uneed more
detail, you can add SQL:StmtStarting/Completed and
SP:StmtStarting/Completed.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||You could also use DBCC INPUTBUFFER or fn_get_sql on specific spids to find
out what they are doing at a given point in time. See SQL Server Books
Online for more information. You may find my code useful in this scenario:
http://vyaskn.tripod.com/fn_get_sql.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
news:eYpyqXLfFHA.1284@.TK2MSFTNGP14.phx.gbl...
> I have an application which is running on 40 active workstations. I am
> encountering lock outs many times a day. When I run the stored procedure
> "sp_who2" I can see the spids that are blocked and by which other spid.
> If I "kill xx" where xx is the head of the blocking chain the system frees
> up. When doing the "sp_who2" I see a command column which only shows the
> start of a command like "insert", 'update", "select" or "AWAITING
COMMAND".
> Is there a way to see the whole command to isolate which table(s) are
> causing the problem?
> Are there other tools to help me out?
> Desparately in need of help...
> Mark
>
|||You can start SQL Server Profiler to trace which table is causing the lock.
John King
http://www.agileinfollc.com
"Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
news:eYpyqXLfFHA.1284@.TK2MSFTNGP14.phx.gbl...
>I have an application which is running on 40 active workstations. I am
>encountering lock outs many times a day. When I run the stored procedure
>"sp_who2" I can see the spids that are blocked and by which other spid.
> If I "kill xx" where xx is the head of the blocking chain the system frees
> up. When doing the "sp_who2" I see a command column which only shows the
> start of a command like "insert", 'update", "select" or "AWAITING
> COMMAND". Is there a way to see the whole command to isolate which
> table(s) are causing the problem?
> Are there other tools to help me out?
> Desparately in need of help...
> Mark
>
encountering lock outs many times a day. When I run the stored procedure
"sp_who2" I can see the spids that are blocked and by which other spid.
If I "kill xx" where xx is the head of the blocking chain the system frees
up. When doing the "sp_who2" I see a command column which only shows the
start of a command like "insert", 'update", "select" or "AWAITING COMMAND".
Is there a way to see the whole command to isolate which table(s) are
causing the problem?
Are there other tools to help me out?
Desparately in need of help...
Mark
Mark Butler wrote:
> I have an application which is running on 40 active workstations. I
> am encountering lock outs many times a day. When I run the stored
> procedure "sp_who2" I can see the spids that are blocked and by which
> other spid.
> If I "kill xx" where xx is the head of the blocking chain the system
> frees up. When doing the "sp_who2" I see a command column which only
> shows the start of a command like "insert", 'update", "select" or
> "AWAITING COMMAND". Is there a way to see the whole command to
> isolate which table(s) are causing the problem?
> Are there other tools to help me out?
> Desparately in need of help...
> Mark
You can use Profiler to see what command are starting and not completing
or committing as the case may be. If you look at the starting and
completed events, you can see if something did not complete (missing
completed event). That might mean that data was not rolled back. It
sounds like you might have open transactions. Make sure no one is using
SQL Enterprise Manager (or other tools that do not fetch all data at
once) to edit data in tables as they will leave open locks on data.
If the same user is responsibl, you can filter the Profiler data. The
output may generate a lot of information. I would start looking at
SQL:BatchStarting/Completed and RPC:Starting/Completed. If yo uneed more
detail, you can add SQL:StmtStarting/Completed and
SP:StmtStarting/Completed.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||You could also use DBCC INPUTBUFFER or fn_get_sql on specific spids to find
out what they are doing at a given point in time. See SQL Server Books
Online for more information. You may find my code useful in this scenario:
http://vyaskn.tripod.com/fn_get_sql.htm
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
news:eYpyqXLfFHA.1284@.TK2MSFTNGP14.phx.gbl...
> I have an application which is running on 40 active workstations. I am
> encountering lock outs many times a day. When I run the stored procedure
> "sp_who2" I can see the spids that are blocked and by which other spid.
> If I "kill xx" where xx is the head of the blocking chain the system frees
> up. When doing the "sp_who2" I see a command column which only shows the
> start of a command like "insert", 'update", "select" or "AWAITING
COMMAND".
> Is there a way to see the whole command to isolate which table(s) are
> causing the problem?
> Are there other tools to help me out?
> Desparately in need of help...
> Mark
>
|||You can start SQL Server Profiler to trace which table is causing the lock.
John King
http://www.agileinfollc.com
"Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
news:eYpyqXLfFHA.1284@.TK2MSFTNGP14.phx.gbl...
>I have an application which is running on 40 active workstations. I am
>encountering lock outs many times a day. When I run the stored procedure
>"sp_who2" I can see the spids that are blocked and by which other spid.
> If I "kill xx" where xx is the head of the blocking chain the system frees
> up. When doing the "sp_who2" I see a command column which only shows the
> start of a command like "insert", 'update", "select" or "AWAITING
> COMMAND". Is there a way to see the whole command to isolate which
> table(s) are causing the problem?
> Are there other tools to help me out?
> Desparately in need of help...
> Mark
>
Subscribe to:
Posts (Atom)