Hi,
I have a server with performance issue and I want to know why or know how to
identify the cause.
its for a datawarehouse SQL database.
I'm using SQL 2005.
during loading process or during query against the largest table (40
millions of rows), the server provide a bad response time.
when I load this table the performance could be 150 000rows/sec or 10
000rows/sec!
so I try to find why sometimes the performance is average, and sometimes its
really bad. (for the same process & queries)
I have 16Gb of Ram, 2 * Opteron dual core CPUs and a SAN with only 1 HBA at
2Gbps
if I track the performance counters...
Sometimes I see a high pages/sec value (4000-6000)
sometimes its the Avg disk queue length value
the CPU is 25% of usage
sometimes the server become unresponsive or is responseive but no rows are
loaded!!!
doing the same process of a smaller table and on a smaller dev server
provide a far better throughput (200 000 rows/sec; near no stops in the
process; perf. counter are constant values etc...)
when a execute a query which is a simple group by (without joins) on the big
table, the server takes 3 to 4 minutes to answer.
using joins the same group by query takes 10 minutes and there is a lot of
CXPacket waitstate.
loading these 40 millions of rows can takes 20 minutes or 1hour...
If I take a look at the memory usage, the server starts to use memory (up to
8Gb) and sudenly release the memory (go down to 200Mb of physical memory,
but virtual memory is 9Gb)
what can I do to diagnose the problem?
Thanks
Jerome.Jeje wrote:
> Hi,
> I have a server with performance issue and I want to know why or know how to
> identify the cause.
> its for a datawarehouse SQL database.
> I'm using SQL 2005.
> during loading process or during query against the largest table (40
> millions of rows), the server provide a bad response time.
> when I load this table the performance could be 150 000rows/sec or 10
> 000rows/sec!
> so I try to find why sometimes the performance is average, and sometimes its
> really bad. (for the same process & queries)
> I have 16Gb of Ram, 2 * Opteron dual core CPUs and a SAN with only 1 HBA at
> 2Gbps
> if I track the performance counters...
> Sometimes I see a high pages/sec value (4000-6000)
> sometimes its the Avg disk queue length value
> the CPU is 25% of usage
> sometimes the server become unresponsive or is responseive but no rows are
> loaded!!!
> doing the same process of a smaller table and on a smaller dev server
> provide a far better throughput (200 000 rows/sec; near no stops in the
> process; perf. counter are constant values etc...)
> when a execute a query which is a simple group by (without joins) on the big
> table, the server takes 3 to 4 minutes to answer.
> using joins the same group by query takes 10 minutes and there is a lot of
> CXPacket waitstate.
> loading these 40 millions of rows can takes 20 minutes or 1hour...
> If I take a look at the memory usage, the server starts to use memory (up to
> 8Gb) and sudenly release the memory (go down to 200Mb of physical memory,
> but virtual memory is 9Gb)
> what can I do to diagnose the problem?
> Thanks
> Jerome.
>
Do you have the proper indexes in place to support the queries you're
running? You might also look into using MAXDOP, the wait type of
CXPACKET indicates parallel processes that are waiting on siblings to
catch up.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||the maxdop option reduce the cxpacket size numbers, but the query is a
little slower.
I have 1 index which cover all the columns used in the joins + 1 index on
each linked table.
all the joins except 1 are simple = on 1 column only.
there is no clustered indexes on these columns.
and I can't create or use them.
also... I see a high % disk time (up to 800%)
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:eh$Y2aesGHA.3556@.TK2MSFTNGP03.phx.gbl...
> Jeje wrote:
>> Hi,
>> I have a server with performance issue and I want to know why or know how
>> to identify the cause.
>> its for a datawarehouse SQL database.
>> I'm using SQL 2005.
>> during loading process or during query against the largest table (40
>> millions of rows), the server provide a bad response time.
>> when I load this table the performance could be 150 000rows/sec or 10
>> 000rows/sec!
>> so I try to find why sometimes the performance is average, and sometimes
>> its really bad. (for the same process & queries)
>> I have 16Gb of Ram, 2 * Opteron dual core CPUs and a SAN with only 1 HBA
>> at 2Gbps
>> if I track the performance counters...
>> Sometimes I see a high pages/sec value (4000-6000)
>> sometimes its the Avg disk queue length value
>> the CPU is 25% of usage
>> sometimes the server become unresponsive or is responseive but no rows
>> are loaded!!!
>> doing the same process of a smaller table and on a smaller dev server
>> provide a far better throughput (200 000 rows/sec; near no stops in the
>> process; perf. counter are constant values etc...)
>> when a execute a query which is a simple group by (without joins) on the
>> big table, the server takes 3 to 4 minutes to answer.
>> using joins the same group by query takes 10 minutes and there is a lot
>> of CXPacket waitstate.
>> loading these 40 millions of rows can takes 20 minutes or 1hour...
>> If I take a look at the memory usage, the server starts to use memory (up
>> to 8Gb) and sudenly release the memory (go down to 200Mb of physical
>> memory, but virtual memory is 9Gb)
>> what can I do to diagnose the problem?
>> Thanks
>> Jerome.
>>
> Do you have the proper indexes in place to support the queries you're
> running? You might also look into using MAXDOP, the wait type of CXPACKET
> indicates parallel processes that are waiting on siblings to catch up.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Hi
Have you done AWE /PAE to configure the memory?
http://support.microsoft.com/kb/274750/
"Jeje" <willgart@.hotmail.com> wrote in message
news:%23rk%238mdsGHA.1888@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a server with performance issue and I want to know why or know how
> to identify the cause.
> its for a datawarehouse SQL database.
> I'm using SQL 2005.
> during loading process or during query against the largest table (40
> millions of rows), the server provide a bad response time.
> when I load this table the performance could be 150 000rows/sec or 10
> 000rows/sec!
> so I try to find why sometimes the performance is average, and sometimes
> its really bad. (for the same process & queries)
> I have 16Gb of Ram, 2 * Opteron dual core CPUs and a SAN with only 1 HBA
> at 2Gbps
> if I track the performance counters...
> Sometimes I see a high pages/sec value (4000-6000)
> sometimes its the Avg disk queue length value
> the CPU is 25% of usage
> sometimes the server become unresponsive or is responseive but no rows are
> loaded!!!
> doing the same process of a smaller table and on a smaller dev server
> provide a far better throughput (200 000 rows/sec; near no stops in the
> process; perf. counter are constant values etc...)
> when a execute a query which is a simple group by (without joins) on the
> big table, the server takes 3 to 4 minutes to answer.
> using joins the same group by query takes 10 minutes and there is a lot of
> CXPacket waitstate.
> loading these 40 millions of rows can takes 20 minutes or 1hour...
> If I take a look at the memory usage, the server starts to use memory (up
> to 8Gb) and sudenly release the memory (go down to 200Mb of physical
> memory, but virtual memory is 9Gb)
> what can I do to diagnose the problem?
> Thanks
> Jerome.
>|||no, not required
its x64bits server (win 2003 ent x64 and SQL Server 2005x64 enterprise)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23bygttfsGHA.148@.TK2MSFTNGP05.phx.gbl...
> Hi
> Have you done AWE /PAE to configure the memory?
> http://support.microsoft.com/kb/274750/
>
>
>
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:%23rk%238mdsGHA.1888@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a server with performance issue and I want to know why or know how
>> to identify the cause.
>> its for a datawarehouse SQL database.
>> I'm using SQL 2005.
>> during loading process or during query against the largest table (40
>> millions of rows), the server provide a bad response time.
>> when I load this table the performance could be 150 000rows/sec or 10
>> 000rows/sec!
>> so I try to find why sometimes the performance is average, and sometimes
>> its really bad. (for the same process & queries)
>> I have 16Gb of Ram, 2 * Opteron dual core CPUs and a SAN with only 1 HBA
>> at 2Gbps
>> if I track the performance counters...
>> Sometimes I see a high pages/sec value (4000-6000)
>> sometimes its the Avg disk queue length value
>> the CPU is 25% of usage
>> sometimes the server become unresponsive or is responseive but no rows
>> are loaded!!!
>> doing the same process of a smaller table and on a smaller dev server
>> provide a far better throughput (200 000 rows/sec; near no stops in the
>> process; perf. counter are constant values etc...)
>> when a execute a query which is a simple group by (without joins) on the
>> big table, the server takes 3 to 4 minutes to answer.
>> using joins the same group by query takes 10 minutes and there is a lot
>> of CXPacket waitstate.
>> loading these 40 millions of rows can takes 20 minutes or 1hour...
>> If I take a look at the memory usage, the server starts to use memory (up
>> to 8Gb) and sudenly release the memory (go down to 200Mb of physical
>> memory, but virtual memory is 9Gb)
>> what can I do to diagnose the problem?
>> Thanks
>> Jerome.
>>
>
Showing posts with label performance. Show all posts
Showing posts with label performance. Show all posts
Friday, March 30, 2012
how to diagnostic performance issue?
Hi,
I have a server with performance issue and I want to know why or know how to
identify the cause.
its for a datawarehouse SQL database.
I'm using SQL 2005.
during loading process or during query against the largest table (40
millions of rows), the server provide a bad response time.
when I load this table the performance could be 150 000rows/sec or 10
000rows/sec!
so I try to find why sometimes the performance is average, and sometimes its
really bad. (for the same process & queries)
I have 16Gb of Ram, 2 * Opteron dual core CPUs and a SAN with only 1 HBA at
2Gbps
if I track the performance counters...
Sometimes I see a high pages/sec value (4000-6000)
sometimes its the Avg disk queue length value
the CPU is 25% of usage
sometimes the server become unresponsive or is responseive but no rows are
loaded!!!
doing the same process of a smaller table and on a smaller dev server
provide a far better throughput (200 000 rows/sec; near no stops in the
process; perf. counter are constant values etc...)
when a execute a query which is a simple group by (without joins) on the big
table, the server takes 3 to 4 minutes to answer.
using joins the same group by query takes 10 minutes and there is a lot of
CXPacket waitstate.
loading these 40 millions of rows can takes 20 minutes or 1hour...
If I take a look at the memory usage, the server starts to use memory (up to
8Gb) and sudenly release the memory (go down to 200Mb of physical memory,
but virtual memory is 9Gb)
what can I do to diagnose the problem?
Thanks
Jerome.Jeje wrote:
> Hi,
> I have a server with performance issue and I want to know why or know how
to
> identify the cause.
> its for a datawarehouse SQL database.
> I'm using SQL 2005.
> during loading process or during query against the largest table (40
> millions of rows), the server provide a bad response time.
> when I load this table the performance could be 150 000rows/sec or 10
> 000rows/sec!
> so I try to find why sometimes the performance is average, and sometimes i
ts
> really bad. (for the same process & queries)
> I have 16Gb of Ram, 2 * Opteron dual core CPUs and a SAN with only 1 HBA a
t
> 2Gbps
> if I track the performance counters...
> Sometimes I see a high pages/sec value (4000-6000)
> sometimes its the Avg disk queue length value
> the CPU is 25% of usage
> sometimes the server become unresponsive or is responseive but no rows are
> loaded!!!
> doing the same process of a smaller table and on a smaller dev server
> provide a far better throughput (200 000 rows/sec; near no stops in the
> process; perf. counter are constant values etc...)
> when a execute a query which is a simple group by (without joins) on the b
ig
> table, the server takes 3 to 4 minutes to answer.
> using joins the same group by query takes 10 minutes and there is a lot of
> CXPacket waitstate.
> loading these 40 millions of rows can takes 20 minutes or 1hour...
> If I take a look at the memory usage, the server starts to use memory (up
to
> 8Gb) and sudenly release the memory (go down to 200Mb of physical memory,
> but virtual memory is 9Gb)
> what can I do to diagnose the problem?
> Thanks
> Jerome.
>
Do you have the proper indexes in place to support the queries you're
running? You might also look into using MAXDOP, the wait type of
CXPACKET indicates parallel processes that are waiting on siblings to
catch up.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||the maxdop option reduce the cxpacket size numbers, but the query is a
little slower.
I have 1 index which cover all the columns used in the joins + 1 index on
each linked table.
all the joins except 1 are simple = on 1 column only.
there is no clustered indexes on these columns.
and I can't create or use them.
also... I see a high % disk time (up to 800%)
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:eh$Y2aesGHA.3556@.TK2MSFTNGP03.phx.gbl...
> Jeje wrote:
> Do you have the proper indexes in place to support the queries you're
> running? You might also look into using MAXDOP, the wait type of CXPACKET
> indicates parallel processes that are waiting on siblings to catch up.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Hi
Have you done AWE /PAE to configure the memory?
http://support.microsoft.com/kb/274750/
"Jeje" <willgart@.hotmail.com> wrote in message
news:%23rk%238mdsGHA.1888@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a server with performance issue and I want to know why or know how
> to identify the cause.
> its for a datawarehouse SQL database.
> I'm using SQL 2005.
> during loading process or during query against the largest table (40
> millions of rows), the server provide a bad response time.
> when I load this table the performance could be 150 000rows/sec or 10
> 000rows/sec!
> so I try to find why sometimes the performance is average, and sometimes
> its really bad. (for the same process & queries)
> I have 16Gb of Ram, 2 * Opteron dual core CPUs and a SAN with only 1 HBA
> at 2Gbps
> if I track the performance counters...
> Sometimes I see a high pages/sec value (4000-6000)
> sometimes its the Avg disk queue length value
> the CPU is 25% of usage
> sometimes the server become unresponsive or is responseive but no rows are
> loaded!!!
> doing the same process of a smaller table and on a smaller dev server
> provide a far better throughput (200 000 rows/sec; near no stops in the
> process; perf. counter are constant values etc...)
> when a execute a query which is a simple group by (without joins) on the
> big table, the server takes 3 to 4 minutes to answer.
> using joins the same group by query takes 10 minutes and there is a lot of
> CXPacket waitstate.
> loading these 40 millions of rows can takes 20 minutes or 1hour...
> If I take a look at the memory usage, the server starts to use memory (up
> to 8Gb) and sudenly release the memory (go down to 200Mb of physical
> memory, but virtual memory is 9Gb)
> what can I do to diagnose the problem?
> Thanks
> Jerome.
>|||no, not required
its x64bits server (win 2003 ent x64 and SQL Server 2005x64 enterprise)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23bygttfsGHA.148@.TK2MSFTNGP05.phx.gbl...
> Hi
> Have you done AWE /PAE to configure the memory?
> http://support.microsoft.com/kb/274750/
>
>
>
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:%23rk%238mdsGHA.1888@.TK2MSFTNGP03.phx.gbl...
>
I have a server with performance issue and I want to know why or know how to
identify the cause.
its for a datawarehouse SQL database.
I'm using SQL 2005.
during loading process or during query against the largest table (40
millions of rows), the server provide a bad response time.
when I load this table the performance could be 150 000rows/sec or 10
000rows/sec!
so I try to find why sometimes the performance is average, and sometimes its
really bad. (for the same process & queries)
I have 16Gb of Ram, 2 * Opteron dual core CPUs and a SAN with only 1 HBA at
2Gbps
if I track the performance counters...
Sometimes I see a high pages/sec value (4000-6000)
sometimes its the Avg disk queue length value
the CPU is 25% of usage
sometimes the server become unresponsive or is responseive but no rows are
loaded!!!
doing the same process of a smaller table and on a smaller dev server
provide a far better throughput (200 000 rows/sec; near no stops in the
process; perf. counter are constant values etc...)
when a execute a query which is a simple group by (without joins) on the big
table, the server takes 3 to 4 minutes to answer.
using joins the same group by query takes 10 minutes and there is a lot of
CXPacket waitstate.
loading these 40 millions of rows can takes 20 minutes or 1hour...
If I take a look at the memory usage, the server starts to use memory (up to
8Gb) and sudenly release the memory (go down to 200Mb of physical memory,
but virtual memory is 9Gb)
what can I do to diagnose the problem?
Thanks
Jerome.Jeje wrote:
> Hi,
> I have a server with performance issue and I want to know why or know how
to
> identify the cause.
> its for a datawarehouse SQL database.
> I'm using SQL 2005.
> during loading process or during query against the largest table (40
> millions of rows), the server provide a bad response time.
> when I load this table the performance could be 150 000rows/sec or 10
> 000rows/sec!
> so I try to find why sometimes the performance is average, and sometimes i
ts
> really bad. (for the same process & queries)
> I have 16Gb of Ram, 2 * Opteron dual core CPUs and a SAN with only 1 HBA a
t
> 2Gbps
> if I track the performance counters...
> Sometimes I see a high pages/sec value (4000-6000)
> sometimes its the Avg disk queue length value
> the CPU is 25% of usage
> sometimes the server become unresponsive or is responseive but no rows are
> loaded!!!
> doing the same process of a smaller table and on a smaller dev server
> provide a far better throughput (200 000 rows/sec; near no stops in the
> process; perf. counter are constant values etc...)
> when a execute a query which is a simple group by (without joins) on the b
ig
> table, the server takes 3 to 4 minutes to answer.
> using joins the same group by query takes 10 minutes and there is a lot of
> CXPacket waitstate.
> loading these 40 millions of rows can takes 20 minutes or 1hour...
> If I take a look at the memory usage, the server starts to use memory (up
to
> 8Gb) and sudenly release the memory (go down to 200Mb of physical memory,
> but virtual memory is 9Gb)
> what can I do to diagnose the problem?
> Thanks
> Jerome.
>
Do you have the proper indexes in place to support the queries you're
running? You might also look into using MAXDOP, the wait type of
CXPACKET indicates parallel processes that are waiting on siblings to
catch up.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||the maxdop option reduce the cxpacket size numbers, but the query is a
little slower.
I have 1 index which cover all the columns used in the joins + 1 index on
each linked table.
all the joins except 1 are simple = on 1 column only.
there is no clustered indexes on these columns.
and I can't create or use them.
also... I see a high % disk time (up to 800%)
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:eh$Y2aesGHA.3556@.TK2MSFTNGP03.phx.gbl...
> Jeje wrote:
> Do you have the proper indexes in place to support the queries you're
> running? You might also look into using MAXDOP, the wait type of CXPACKET
> indicates parallel processes that are waiting on siblings to catch up.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Hi
Have you done AWE /PAE to configure the memory?
http://support.microsoft.com/kb/274750/
"Jeje" <willgart@.hotmail.com> wrote in message
news:%23rk%238mdsGHA.1888@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a server with performance issue and I want to know why or know how
> to identify the cause.
> its for a datawarehouse SQL database.
> I'm using SQL 2005.
> during loading process or during query against the largest table (40
> millions of rows), the server provide a bad response time.
> when I load this table the performance could be 150 000rows/sec or 10
> 000rows/sec!
> so I try to find why sometimes the performance is average, and sometimes
> its really bad. (for the same process & queries)
> I have 16Gb of Ram, 2 * Opteron dual core CPUs and a SAN with only 1 HBA
> at 2Gbps
> if I track the performance counters...
> Sometimes I see a high pages/sec value (4000-6000)
> sometimes its the Avg disk queue length value
> the CPU is 25% of usage
> sometimes the server become unresponsive or is responseive but no rows are
> loaded!!!
> doing the same process of a smaller table and on a smaller dev server
> provide a far better throughput (200 000 rows/sec; near no stops in the
> process; perf. counter are constant values etc...)
> when a execute a query which is a simple group by (without joins) on the
> big table, the server takes 3 to 4 minutes to answer.
> using joins the same group by query takes 10 minutes and there is a lot of
> CXPacket waitstate.
> loading these 40 millions of rows can takes 20 minutes or 1hour...
> If I take a look at the memory usage, the server starts to use memory (up
> to 8Gb) and sudenly release the memory (go down to 200Mb of physical
> memory, but virtual memory is 9Gb)
> what can I do to diagnose the problem?
> Thanks
> Jerome.
>|||no, not required
its x64bits server (win 2003 ent x64 and SQL Server 2005x64 enterprise)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23bygttfsGHA.148@.TK2MSFTNGP05.phx.gbl...
> Hi
> Have you done AWE /PAE to configure the memory?
> http://support.microsoft.com/kb/274750/
>
>
>
> "Jeje" <willgart@.hotmail.com> wrote in message
> news:%23rk%238mdsGHA.1888@.TK2MSFTNGP03.phx.gbl...
>
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.
======================================================
Sunday, February 19, 2012
How To Defrag SQL Server 2000
Hi everyone, I am fairly new with SQL Server and need a little bit of help in regards to boosting my SQL servers performance. I have been advised that defraging SQL Server will definitely help solve my issue however I have never performed this function before. Can someone please help guide me through the necessary steps to execute this task. Thanks in advance.I have been advised that defraging SQL Server will definitely help solve my issue however I have never performed this function before.
Only one setting cannot solve your problem, you have to consider lots of things like server memory settings, disk space, indexes, query optimizing etc.
You can use database maintenance wizard from Enterprise Manager-> Tools-> Database maintenance planner.
You can fragment your indexes for better performance, syntax is given below.
DBCC INDEXDEFRAG
( { database_name | database_id | 0 }
, { table_name | table_id | 'view_name' | view_id }
, { index_name | index_id }
) [ WITH NO_INFOMSGS ]|||Only one setting cannot solve your problem, you have to consider lots of things like server memory settings, disk space, indexes, query optimizing etc.
You can use database maintenance wizard from Enterprise Manager-> Tools-> Database maintenance planner.
You can fragment your indexes for better performance, syntax is given below.
DBCC INDEXDEFRAG
( { database_name | database_id | 0 }
, { table_name | table_id | 'view_name' | view_id }
, { index_name | index_id }
) [ WITH NO_INFOMSGS ]
I am curious; is there any value in doing a backup/restore?
I have a daily scheduled run of Executive Software's Diskkeeper on all the servers. That keeps the files defragged on the file-system level, but of course doesn't reorder anything within the database.
As I understand it, the concept of Defragmenetation offers an optimization of physical aspects of the disk drive (rotations, head movements) and the software activities of piecing together the fragments. It follows that having all the bits of an index in order would have a similar effect (as you described above).
I guess in a database there's also a matter of eliminating all the holes left by prior deletes and of spreading indexes out more intelligently.
So then; I'm displaying a complete ignorance of "database layer fragmentation". Am I missing a lot of fundamentals in my thinking?
Question: Would it be a benifit to backup-then-restore a database?|||Backing up and restoring a database has no effect on fragmentation. Database fragmentation that is, the DBA's nerves will become highly fragmented if this sort of thing is implemented. In Oracle, you can export and import tables to remove fragmentation, which may be what you are thinking of. In SQL Server, a backup collects all pages that have data on them, and stashes them away. On a restore, the data pages are simply rewritten in place. without any moving of data around the pages.
Database fragmentation happens mainly with deletes, sometimes with updates, and somewhat less frequently with inserts (depending on your indexes).
Suppose you have a data page that originally has 20 entries (rows) in it. When you read in that page, you get 20 rows in memory. Suppose further that 19 of these rows are deleted. Now when you read in the same 8KB page, you only get 1 row of data. The space taken up by the rows that were there is not reclaimed automatically, and depending on insert/update activity and clustered index layout may not ever be reclaimed unless you rebuild the indexes. Rebuilding the indexes has the effect of re-arranging, or regenerating the entries packed closer together making read operations more efficient.
Here is a link to a decent paper about it. Note, users tend to not notice the difference, until tables get above 10,000 pages or so.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx|||I am curious; is there any value in doing a backup/restore?
yes, there is some value|||Check this, a single web page consist of various subjects for SQL Server Maintenance.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/default.mspx
Only one setting cannot solve your problem, you have to consider lots of things like server memory settings, disk space, indexes, query optimizing etc.
You can use database maintenance wizard from Enterprise Manager-> Tools-> Database maintenance planner.
You can fragment your indexes for better performance, syntax is given below.
DBCC INDEXDEFRAG
( { database_name | database_id | 0 }
, { table_name | table_id | 'view_name' | view_id }
, { index_name | index_id }
) [ WITH NO_INFOMSGS ]|||Only one setting cannot solve your problem, you have to consider lots of things like server memory settings, disk space, indexes, query optimizing etc.
You can use database maintenance wizard from Enterprise Manager-> Tools-> Database maintenance planner.
You can fragment your indexes for better performance, syntax is given below.
DBCC INDEXDEFRAG
( { database_name | database_id | 0 }
, { table_name | table_id | 'view_name' | view_id }
, { index_name | index_id }
) [ WITH NO_INFOMSGS ]
I am curious; is there any value in doing a backup/restore?
I have a daily scheduled run of Executive Software's Diskkeeper on all the servers. That keeps the files defragged on the file-system level, but of course doesn't reorder anything within the database.
As I understand it, the concept of Defragmenetation offers an optimization of physical aspects of the disk drive (rotations, head movements) and the software activities of piecing together the fragments. It follows that having all the bits of an index in order would have a similar effect (as you described above).
I guess in a database there's also a matter of eliminating all the holes left by prior deletes and of spreading indexes out more intelligently.
So then; I'm displaying a complete ignorance of "database layer fragmentation". Am I missing a lot of fundamentals in my thinking?
Question: Would it be a benifit to backup-then-restore a database?|||Backing up and restoring a database has no effect on fragmentation. Database fragmentation that is, the DBA's nerves will become highly fragmented if this sort of thing is implemented. In Oracle, you can export and import tables to remove fragmentation, which may be what you are thinking of. In SQL Server, a backup collects all pages that have data on them, and stashes them away. On a restore, the data pages are simply rewritten in place. without any moving of data around the pages.
Database fragmentation happens mainly with deletes, sometimes with updates, and somewhat less frequently with inserts (depending on your indexes).
Suppose you have a data page that originally has 20 entries (rows) in it. When you read in that page, you get 20 rows in memory. Suppose further that 19 of these rows are deleted. Now when you read in the same 8KB page, you only get 1 row of data. The space taken up by the rows that were there is not reclaimed automatically, and depending on insert/update activity and clustered index layout may not ever be reclaimed unless you rebuild the indexes. Rebuilding the indexes has the effect of re-arranging, or regenerating the entries packed closer together making read operations more efficient.
Here is a link to a decent paper about it. Note, users tend to not notice the difference, until tables get above 10,000 pages or so.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx|||I am curious; is there any value in doing a backup/restore?
yes, there is some value|||Check this, a single web page consist of various subjects for SQL Server Maintenance.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/default.mspx
Subscribe to:
Posts (Atom)