MS SQL Data Warehousing - back again with my server performance issue...

Author

back again with my server performance issue...

Jéjé

2005-11-15, 8:23 pm

Hi again (and again) guys...

I have just read an article in the SQL Server magasine which says that its
recommanded to have 1 file by processor for the tempdb database.
does this recommandation is good for a data warehousing solution?

I'm suffering some disk issue and I have a lot of CXPacket wait types.
So I'm looking for solution to improve this...
but I can't do anything at the disk level.

My config:
Windows 2003 Ent.
SQL Server 2000 Ent.
4 Xeon 3.6Ghz with HT
4 Gb of RAM (/3Gb option set)
200 Gb on a SAN

I have some communication issue with the client I.T. Team, and I just know
that I have 200 Gb of disk, I have no idea about the disk config, cache
config etc...
I have no choice to use it.
I can't spread my files among dedicated disks (like moving log files, tempdb
database...)
the performance degrade specially when there is reading & writing activity
on the disk, like filling a table with the indexes in place or like doiong
an update on a "big" table (1 million of rows)

but complex select queries are slow too due to CXPacket waits.

I have added 7 files in for the tempdb database and 7 files for the staging
database.
For the moment I monitor the loading step which is slower then expected.
after this change the number of CXPacket wait types has increased from 10-20
waits to 150 waits!!!! (I'm using the sp_who1 cusomt procedure to see all
the wait type)

I have another client with near the same volume of data, a SAN and a smaller
hardware (2 cpu only, 2Gb of ram) and doing the same loading takes half of
the time!
I process 70 000rows / sec on this small server while I process 45
000rows/sec on my biggest but slower server....
I have a third client where the bottleneck is the disk controller, I reach
the maximum throughput of 80MB/s on it.
So our loading procedures are optimal for all client and the overral
solution is good except this one wherethesame procedures are slow.

what are my options?
The only answer I receive from the I.T. Team is: "The system is optimal, for
us there is no performance issue"
for us we clearly have issues on this server.
queries which generally takes 5 seconds to execute takes 35 seconds on this
server!
and we defrag ALL the indexes after every load.

thanks for your ideas if you have one :-)

Jerome.

Danny

2005-11-16, 7:23 am

Jeje,

If the Average Queue Length is high then there is a disk bottle neck. The
disk subsystem may be optimal but still not fast enough for what you need.
Moving to more than one file in a filegroup on a multiprocessor server is
generally helpful. You've effectively increased the number of parrallel
tasks. Have you tried lowering your mdop?

"Jéjé" < willgart@BBBhotmailA
AA.com> wrote in message
news:%23PsrShk6FHA.3888@TK2MSFTNGP10.phx.gbl...
> Hi again (and again) guys...
>
> I have just read an article in the SQL Server magasine which says that its
> recommanded to have 1 file by processor for the tempdb database.
> does this recommandation is good for a data warehousing solution?
>
> I'm suffering some disk issue and I have a lot of CXPacket wait types.
> So I'm looking for solution to improve this...
> but I can't do anything at the disk level.
>
> My config:
> Windows 2003 Ent.
> SQL Server 2000 Ent.
> 4 Xeon 3.6Ghz with HT
> 4 Gb of RAM (/3Gb option set)
> 200 Gb on a SAN
>
> I have some communication issue with the client I.T. Team, and I just know
> that I have 200 Gb of disk, I have no idea about the disk config, cache
> config etc...
> I have no choice to use it.
> I can't spread my files among dedicated disks (like moving log files,
> tempdb database...)
> the performance degrade specially when there is reading & writing activity
> on the disk, like filling a table with the indexes in place or like doiong
> an update on a "big" table (1 million of rows)
>
> but complex select queries are slow too due to CXPacket waits.
>
> I have added 7 files in for the tempdb database and 7 files for the
> staging database.
> For the moment I monitor the loading step which is slower then expected.
> after this change the number of CXPacket wait types has increased from
> 10-20 waits to 150 waits!!!! (I'm using the sp_who1 cusomt procedure to
> see all the wait type)
>
> I have another client with near the same volume of data, a SAN and a
> smaller hardware (2 cpu only, 2Gb of ram) and doing the same loading takes
> half of the time!
> I process 70 000rows / sec on this small server while I process 45
> 000rows/sec on my biggest but slower server....
> I have a third client where the bottleneck is the disk controller, I reach
> the maximum throughput of 80MB/s on it.
> So our loading procedures are optimal for all client and the overral
> solution is good except this one wherethesame procedures are slow.
>
> what are my options?
> The only answer I receive from the I.T. Team is: "The system is optimal,
> for us there is no performance issue"
> for us we clearly have issues on this server.
> queries which generally takes 5 seconds to execute takes 35 seconds on
> this server!
> and we defrag ALL the indexes after every load.
>
> thanks for your ideas if you have one :-)
>
> Jerome.
>
>

Jéjé

2005-11-16, 8:23 pm

Hi,

yes, I have played with the mdop option.
sometimes this improve the performance, sometimes this decrease the
performance.

for queries where I have "simple" joins and group by clause, I have less
issues then queries with outer join and lookup queires (queries statement
under the select statement select (select min(...) from tableA... ) as
mindate from tableB...))
The overall result of the entire process is a slower result when maxdop = 1.

update statements are always slow, and if I update a column with an index on
it... its really bad!

I have tried to add some files in my filegroups, but the result is not so
good, my staging queries generate 8 times more CXPacket locks.

I'll try some options and test it again.
if you have some other ideas, I'll take it ;-)

"Danny" <someone@nowhere.com> wrote in message
news:91Gef.7467$vS4.4914@trnddc01...
> Jeje,
>
> If the Average Queue Length is high then there is a disk bottle neck. The
> disk subsystem may be optimal but still not fast enough for what you need.
> Moving to more than one file in a filegroup on a multiprocessor server is
> generally helpful. You've effectively increased the number of parrallel
> tasks. Have you tried lowering your mdop?
>
>
> "Jéjé" < willgart@BBBhotmailA
AA.com> wrote in message
> news:%23PsrShk6FHA.3888@TK2MSFTNGP10.phx.gbl...
>
>

Sponsored Links