Re: SQL 2005 Best Practice vs SQL 2000: Application Files Separate from data (and log) files
- From: "Rex Gibson" <noamt1@xxxxxxxxx>
- Date: Fri, 8 Feb 2008 13:27:57 -0500
We are on dedicated spindles.I checked when we did our initial migration
from local raid5. We actually saw a boost in peformance. Our san guy is
pretty sharp. Thank you though, I'm sure others will find this comment
usefull.
"Shan McArthur" <shan_mcarthur@xxxxxxxxxxx> wrote in message
news:94FC7E71-98CA-4F86-9C47-EE2AB3D2C97E@xxxxxxxxxxxxxxxx
Be careful with your SAN. I have had quite a number of clients that used
a SAN for a database server and suffered significant performance problems
because their underlying SAN infrastructure was a huge stripe across many
drives. When they thought that their database files were seperate because
they were on different volumes, they were actually using the same disk
spindles and were even shared with other servers. What most people
completely forget about is that moving the head on the disk is the slowest
operation and is most frequently the source of most I/O bottlenecks.
Modern disks can rattle off sustained transfer rates in excess of 100MB/s,
but if it takes 5 ms to move the head plus an additional rotation latency
of 2ms, that means that the disks are not transfering a single bit for
7ms. What complicates this is that when the underlying SAN services an I/O
request that uses the disk that the SQL box is using, it moves the head,
which also has to be moved back, meaning that there is 14ms of no disk
I/O.
Personally, I like to use direct-attached storage for SQL databases, but
if I had to use a SAN, I would insist on having independent spindles on
the drives that were being assigned to the SQL machine.
Think of it another way - having 2 volumes on the same underlying SAN
disks is no different than having 2 partitions on the same disk - it
simply won't improve your performance.
Shan McArthur - VP, Technology
ADXSTUDIO Inc. | 200 - 1445 Park Street | Regina, SK Canada | S4N
4C5
Tel: 306.569.6502 | Toll-Free: 800.508.7811 ext. 502 | Fax:
306.569.8518
"Rex Gibson" <noamt1@xxxxxxxxx> wrote in message
news:OUhBAPRZIHA.3964@xxxxxxxxxxxxxxxxxxxxxxx
Thank you Ekrem,
We are physically separating log and data files on separate disks. So
after thinking about it in need to rephase the question into 2 parts.
For SQL Server 2005:
1) In addition to separating data files and log files to separate
phyiscal for "user" databases, is it best also practice to separate out
the data and log files to different physical volumes for system databases
such as master, msdb, and tempdb? In which case this cannot be done via
the setup.exe but _must_ be done post installation using the following
techniques.
http://support.microsoft.com/kb/224071
Correct?
2) In a server configuration with 3 physical volumes, for example: C: for
system (local RAID), D: for data (SAN), E: for logs (SAN); is there any
reason to have the application files and folders on the C drive and the
data files on the D: drive and logs on the E: drive? By application files
I mean the binn folder which contains the sql server executable among
other things. Does it matter? Or can simply install SQL Server on D
drive, and do as advised in question 1 above with regards to user and
system db log files.
Thanks again for your kind attention.
-Rex
"Ekrem Önsoy" <ekrem@xxxxxxxxxxxx> wrote in message
news:E5D91BBB-241D-4709-871D-4F6A77385B40@xxxxxxxxxxxxxxxx
There was another post similar to yours by Saral6978 just 1 post before
yours.
Seperation of data and log files is a best practice. For example, locate
your SQL binary and Windows files on drive C: and put your data file on
drive D: and put the log file of your database on drive E:
The important thing here is putting those files physically seperated
disks. A logical seperation would not gain you performance.
You can of course change your database files' folders using ALTER
DATABASE command in SQL Server 2005 as well. To learn more about this
command visit Books Online =
http://msdn2.microsoft.com/en-us/library/ms174269.aspx
--
Ekrem Önsoy
"Rex Gibson" <noamt1@xxxxxxxxx> wrote in message
news:e%23DcqK5YIHA.4896@xxxxxxxxxxxxxxxxxxxxxxx
Dear readers,
I am finally coming out of the dark ages and starting to use SQL Server
2005. Due to the rather large nature of my organization, you must
forgive me for the rather late question.
I have operated in SQL 2000 under the assumption that a performace
boost is gained when the SQL Server Appliation files (such as the
server service executable, and agent service executable) were on
separate volumes/luns from the data files. Thus Application files were
installed on C. Data files on D and log files on E. Etc.
Now we are starting to move forward and use SQL2005 sp2 and during
installation, it appears we no longer have the same options (or
possibly we just don't understand). It appears that the SQL 2005
installation package does not allow for this type of configuration in
regards particularly to the application files being on a separate
location from the data files. We have tried a few ways now and done
several searches and are not finding any reference material on that
through we are seeing vague references that the same "best practice"
applies in 2005.
What I want is the binn folder on the c drive and the system db's on
the d drive. Obviously I can get the user dbs on the d drive. Is this
possible? Is it still a best practice? If not, why not? Anybody have a
link to a guide on how to do this? Proving me wrong in the first place
(This practice in SQL2000) is an acceptable solution too.
Thank you for your kind attention!
-Rex Gibson
DBA -- in some very large, very slow moving orgainization.
.
- References:
- Prev by Date: Re:missing Preformance Tools menu
- Next by Date: Re: SQL 2005 Best Practice vs SQL 2000: Application Files Separate from data (and log) files
- Previous by thread: Re: SQL 2005 Best Practice vs SQL 2000: Application Files Separate from data (and log) files
- Next by thread: Re: addind management studio after sp2
- Index(es):
Relevant Pages
|
