Seeking Recommendation Confirmation

26 Jan 2006 12:28 PM

Robert R via SQLMonster.com

I have a SQL2K enterprise box with performance issues. It is composed of a
single RAID 5 with 6 disks, identified as the "D" drive. It has 8Gb RAM, a
fixed memory setting of 6079, with AWE enabled and 3GB/PAE set in boot.ini.
Once the 8GB was added and AWE enabled, performance increased by about 50%,
but performance is still unacceptable.

I have posted Memory and Physical Disk counters on this website and was told
that both are acceptable. While I have not posted the CPU counters on this
website, I am fairly confident that they are acceptable too.

I have run Profiler and it does appear some queries could benefit from some
optimization. However, my instinct is that due to the across board,
unacceptable performance, it goes beyond query tuning, though I am sure that
would be of some help.

In looking at the Log file and Data file placements, both the Log and Data
files are place on the RAID 5, "D" drive. From the Physical Disk counters (as
posted on this website) it appears I have 1% Read Time versus 70% Write Time.
My recommendation would be:
1. Place the Log files on RAID 1
2. Obtain another RAID 5, separating the tables and indexes into separate
filegroups, placing tables on one RAID 5 and indexes on the other RAID 5.

Would you concur with the above as a reasonable recommendation?

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1

26 Jan 2006 12:41 PM

Uri Dimant

Robert
How big is your database?
Obviously , you need to separate .MDF and .LDF files
We have in our company
Multiple drives
DATA: RAID-5
LOG:Separate ,physical RAID-1 devices

> 2. Obtain another RAID 5, separating the tables and indexes into separate
> filegroups, placing tables on one RAID 5 and indexes on the other RAID 5.

Well , you will be benefit from it only if your database is pretty big and
don't forget that it is not easy to manage

In my opinion , 80% of perfomance problems caused by an applications (bad
written query, lack of indexes....) and 10% by Hardware.

"Robert R via SQLMonster.com" <u3288@uwe> wrote in message

news:5aeee3f236468@uwe...
Show quoteHide quote

>I have a SQL2K enterprise box with performance issues. It is composed of a
> single RAID 5 with 6 disks, identified as the "D" drive. It has 8Gb RAM, a
> fixed memory setting of 6079, with AWE enabled and 3GB/PAE set in
> boot.ini.
> Once the 8GB was added and AWE enabled, performance increased by about
> 50%,
> but performance is still unacceptable.
>
> I have posted Memory and Physical Disk counters on this website and was
> told
> that both are acceptable. While I have not posted the CPU counters on this
> website, I am fairly confident that they are acceptable too.
>
> I have run Profiler and it does appear some queries could benefit from
> some
> optimization. However, my instinct is that due to the across board,
> unacceptable performance, it goes beyond query tuning, though I am sure
> that
> would be of some help.
>
> In looking at the Log file and Data file placements, both the Log and Data
> files are place on the RAID 5, "D" drive. From the Physical Disk counters
> (as
> posted on this website) it appears I have 1% Read Time versus 70% Write
> Time.
> My recommendation would be:
> 1. Place the Log files on RAID 1
> 2. Obtain another RAID 5, separating the tables and indexes into separate
> filegroups, placing tables on one RAID 5 and indexes on the other RAID 5.
>
> Would you concur with the above as a reasonable recommendation?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1

26 Jan 2006 2:41 PM

Robert R via SQLMonster.com

The database size is only about 4GB.

I have two profiler traces:
1. Based on query duration
2. Based on table scans

Would you say that between the two of those I should be able analyze and make
some perfomance gains?

Uri Dimant wrote:
Show quoteHide quote

>Robert
>How big is your database?
>Obviously , you need to separate .MDF and .LDF files
>We have in our company
>Multiple drives
>DATA: RAID-5
>LOG:Separate ,physical RAID-1 devices
>
>> 2. Obtain another RAID 5, separating the tables and indexes into separate
>> filegroups, placing tables on one RAID 5 and indexes on the other RAID 5.
>
>Well , you will be benefit from it only if your database is pretty big and
>don't forget that it is not easy to manage
>
>In my opinion , 80% of perfomance problems caused by an applications (bad
>written query, lack of indexes....) and 10% by Hardware.
>
>>I have a SQL2K enterprise box with performance issues. It is composed of a
>> single RAID 5 with 6 disks, identified as the "D" drive. It has 8Gb RAM, a
>[quoted text clipped - 27 lines]
>>
>> Would you concur with the above as a reasonable recommendation?

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1

26 Jan 2006 6:32 PM

Michael Hotek

You have the 3GB switch on along with PAE and AWE giving you up to 6GB of
memory. This along with a 4GB database means that you basically have
everything in memory. Yes, you can get disk I/O contention if everything is
on one array, but not to any great extent because you will very quickly be
reading and writing entirely to memory.

Can you define what you mean by "across the board bad performance"? Do you
have lots of cursors and temp tables in the code? Are you table scanning
everything?

You could spend a lot of time and money playing with disk configurations,
but since you have a database that is going to fit inside memory, you
probably aren't going to see much of an appreciable impact. Most of your
performance issues are a combination of database structure, indexes, and
code.

--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.

"Robert R via SQLMonster.com" <u3288@uwe> wrote in message

news:5af00c3d5c644@uwe...
Show quoteHide quote

> The database size is only about 4GB.
>
> I have two profiler traces:
> 1. Based on query duration
> 2. Based on table scans
>
> Would you say that between the two of those I should be able analyze and
> make
> some perfomance gains?
>
> Uri Dimant wrote:
>>Robert
>>How big is your database?
>>Obviously , you need to separate .MDF and .LDF files
>>We have in our company
>>Multiple drives
>>DATA: RAID-5
>>LOG:Separate ,physical RAID-1 devices
>>
>>> 2. Obtain another RAID 5, separating the tables and indexes into
>>> separate
>>> filegroups, placing tables on one RAID 5 and indexes on the other RAID
>>> 5.
>>
>>Well , you will be benefit from it only if your database is pretty big
>>and
>>don't forget that it is not easy to manage
>>
>>In my opinion , 80% of perfomance problems caused by an applications (bad
>>written query, lack of indexes....) and 10% by Hardware.
>>
>>>I have a SQL2K enterprise box with performance issues. It is composed of
>>>a
>>> single RAID 5 with 6 disks, identified as the "D" drive. It has 8Gb RAM,
>>> a
>>[quoted text clipped - 27 lines]
>>>
>>> Would you concur with the above as a reasonable recommendation?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1

26 Jan 2006 12:50 PM

Dan Guzman

> In looking at the Log file and Data file placements, both the Log and Data
> files are place on the RAID 5, "D" drive. From the Physical Disk counters
> (as
> posted on this website) it appears I have 1% Read Time versus 70% Write
> Time.
> My recommendation would be:
> 1. Place the Log files on RAID 1
> 2. Obtain another RAID 5, separating the tables and indexes into separate
> filegroups, placing tables on one RAID 5 and indexes on the other RAID 5.
>
> Would you concur with the above as a reasonable recommendation?

Definitely separate data and log. Log is mostly sequential write and data
files are usually a mix of read/write and sequential/random access. Placing
both on the same physical disks is bad because random data I/O requests make
the sequential writes less efficient. Also, log I/O is synchronous during
commit so it is very important that log I/O complete as quickly as possible
for the best response time. RAID 1 or 10 is best for logs due to the
write-intensive nature. If your applications is write-intensive and you
budget permits, you might consider placing data files on RAID 10 instead of
5.

I would not go through the trouble of placing data and indexes on separate
arrays unless you understand your application data access patterns. The
main reason to do this is to isolate sequential and random I/O so
segregation makes sense if you can place sequentially accessed index/data on
a different filegroup. However, more often than not it's best to distribute
data and indexes over all available disks in order to balance the workload.
This is especially the case with random I/O.

In any case, I suggest you move your log locations and establish a baseline
before you proceed with further tuning. Change one thing at a time when
possible.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Robert R via SQLMonster.com" <u3288@uwe> wrote in message

news:5aeee3f236468@uwe...
Show quoteHide quote

>I have a SQL2K enterprise box with performance issues. It is composed of a
> single RAID 5 with 6 disks, identified as the "D" drive. It has 8Gb RAM, a
> fixed memory setting of 6079, with AWE enabled and 3GB/PAE set in
> boot.ini.
> Once the 8GB was added and AWE enabled, performance increased by about
> 50%,
> but performance is still unacceptable.
>
> I have posted Memory and Physical Disk counters on this website and was
> told
> that both are acceptable. While I have not posted the CPU counters on this
> website, I am fairly confident that they are acceptable too.
>
> I have run Profiler and it does appear some queries could benefit from
> some
> optimization. However, my instinct is that due to the across board,
> unacceptable performance, it goes beyond query tuning, though I am sure
> that
> would be of some help.
>
> In looking at the Log file and Data file placements, both the Log and Data
> files are place on the RAID 5, "D" drive. From the Physical Disk counters
> (as
> posted on this website) it appears I have 1% Read Time versus 70% Write
> Time.
> My recommendation would be:
> 1. Place the Log files on RAID 1
> 2. Obtain another RAID 5, separating the tables and indexes into separate
> filegroups, placing tables on one RAID 5 and indexes on the other RAID 5.
>
> Would you concur with the above as a reasonable recommendation?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1

26 Jan 2006 9:57 PM

Hugo Kornelis

On Thu, 26 Jan 2006 12:28:43 GMT, Robert R via SQLMonster.com wrote:

>I have run Profiler and it does appear some queries could benefit from some
>optimization. However, my instinct is that due to the across board,
>unacceptable performance, it goes beyond query tuning, though I am sure that
>would be of some help.

Hi Robert,

I guess that your instnict is wrong. Of course, I don't know your
database. If a lot of work has already been done in tuning the queries
and you are now looking at possibly squeezing the last few percents of
performance gain out of it, your instinct is right.

But if there has been little or no investment in query tuning until now,
than that would be the place to start. Performance gains of a few 100
percent are not unheard of. There's no way that you'll ever match that
with improving the hardware.

--
Hugo Kornelis, SQL Server MVP