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 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.
>>
>