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.

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 (AT) BBBhotmailAAA (DOT) com> wrote

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 (AT) nowhere (DOT) com> wrote