We're planning to migrate our db to new and more disk drives, faster
RAID levels and more dedicated disk usage(e.g. placing the translog on
dedicated disks). The db server runs on Win2003.
Right now we're thinking about what file system to use on the new
drives. We opt for performance, but expect reliability as well.(Goes
without saying, IMHO ;-))
>From what I can tell, NTFS is a journaling file system, at least it
does some journaling. I'd prefer to have no journaling and sacrifice
boot time for performance, and was wondering if it either is possible
to turn journaling off in NTFS or at least move the journal to a
separate disk. Is it doable, has anyone done this and are there any
benefits? Any downside?
Are there any commonly recommended tweaks to NTFS when the server and
disk is dedicated to SQL Server? Stuff like
NTFSDisableLast AccessUpdate.
How about alternatives to to NTFS? Is FAT32 viable or do we need to
look at Veritas or others?
TIA for all comments.
Bjørn Augestad 18  4777  bj************@ gmail.com (bj************ @gmail.com) writes:
We're planning to migrate our db to new and more disk drives, faster
RAID levels and more dedicated disk usage(e.g. placing the translog on
dedicated disks). The db server runs on Win2003.
Right now we're thinking about what file system to use on the new
drives. We opt for performance, but expect reliability as well.(Goes
without saying, IMHO ;-))
>>From what I can tell, NTFS is a journaling file system, at least it
does some journaling. I'd prefer to have no journaling and sacrifice
boot time for performance, and was wondering if it either is possible
to turn journaling off in NTFS or at least move the journal to a
separate disk. Is it doable, has anyone done this and are there any
benefits? Any downside?
Are there any commonly recommended tweaks to NTFS when the server and
disk is dedicated to SQL Server? Stuff like
NTFSDisableLast AccessUpdate.
How about alternatives to to NTFS? Is FAT32 viable or do we need to
look at Veritas or others?
No, FAT32 is not a very good alternative. There are several features in
SQL Server that are not available with FAT32. I got this list from one
of the SQL Server developers:
1. Database snapshots - require support for sparse files which is available
only under NTFS
2. DBCC snapshot usage - requires support for alternate streams which is
available only under NTFS
3. Mount points under cluster
4. Data file compression which uses NTFS compression
5. Encryption of database files is supported only under NTFS
6. Differential backup, restore of full-text catalog files is supported only
under NTFS
He says the list may not be complete. He also added "I believe that recovery
mechanisms are more robust under NTFS and protects against system failures.
I think the user is worrying too much about NTFS when there are other
obvious performance bottlenecks."
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Erland Sommarskog wrote:
bj************@ gmail.com (bj************ @gmail.com) writes:
We're planning to migrate our db to new and more disk drives, faster
RAID levels and more dedicated disk usage(e.g. placing the translog on
dedicated disks). The db server runs on Win2003.
Right now we're thinking about what file system to use on the new
drives. We opt for performance, but expect reliability as well.(Goes
without saying, IMHO ;-))
>From what I can tell, NTFS is a journaling file system, at least it
does some journaling. I'd prefer to have no journaling and sacrifice
boot time for performance, and was wondering if it either is possible
to turn journaling off in NTFS or at least move the journal to a
separate disk. Is it doable, has anyone done this and are there any
benefits? Any downside?
Are there any commonly recommended tweaks to NTFS when the server and
disk is dedicated to SQL Server? Stuff like
NTFSDisableLast AccessUpdate.
How about alternatives to to NTFS? Is FAT32 viable or do we need to
look at Veritas or others?
No, FAT32 is not a very good alternative. There are several features in
SQL Server that are not available with FAT32. I got this list from one
of the SQL Server developers:
1. Database snapshots - require support for sparse files which is available
only under NTFS
2. DBCC snapshot usage - requires support for alternate streams which is
available only under NTFS
3. Mount points under cluster
4. Data file compression which uses NTFS compression
5. Encryption of database files is supported only under NTFS
6. Differential backup, restore of full-text catalog files is supported only
under NTFS
He says the list may not be complete. He also added "I believe that recovery
mechanisms are more robust under NTFS and protects against system failures.
I think the user is worrying too much about NTFS when there are other
obvious performance bottlenecks."
I get your point(s) :-) So FAT32 is out, not a big surprise.
Once upon a time I was playing around with file systems(JFS, XFS and
others), Oracle and AIX as well as OSF/1. I spent a considerable time
measuring performance for different configurations and journaling file
systems *with* the journal on the same disk as the db data(or translog)
was awfully slow compared to other configurations. I assumed that is
was due to disk head movements back and forth between the journal and
the file.
So even if the file system isn't the performance bottleneck right now,
I'd prefer to configure all the new disk drives optimally from day one.
I read the NTFS spec and browsed through all the options, but couldn't
find anything about tuning the journal. I'll have to reread it, I
guess...
Thanks,
Bjørn
>I get your point(s) :-) So FAT32 is out, not a big surprise.
>
Once upon a time I was playing around with file systems(JFS, XFS and
others), Oracle and AIX as well as OSF/1. I spent a considerable time
measuring performance for different configurations and journaling file
systems *with* the journal on the same disk as the db data(or translog)
was awfully slow compared to other configurations. I assumed that is
was due to disk head movements back and forth between the journal and
the file.
So even if the file system isn't the performance bottleneck right now,
I'd prefer to configure all the new disk drives optimally from day one.
I read the NTFS spec and browsed through all the options, but couldn't
find anything about tuning the journal. I'll have to reread it, I
guess...
There is the option of raw partition. A fellow MVP told me that he
made some tests some years back (so it was not on SQL 2005), and got
a 20% improvement with raw partition. But raw partitions are difficult
to manage, and I guess that you would still have to use NTFS for things
like database snapshots. Personally, I would never consider using raw
partition for a production system.
20% may sound significant, but there other ways to get a 20% speedup,
for instance by sticking to a binary collation, or using an SQL
collation for varchar data. In fact, for a query like.
SELECT ... FROM tbl WHER col LIKE '%Whatever%'
the difference between a CI/CS collation and a binary collation can be
factor seven.
You also lose 90% in performance by writing a bad query.
You may be interested in this article: http://www.microsoft.com/technet/pro...hysdbstor.mspx.
--
Erland Sommarskog, Stockholm, es****@sommarsk og.se
Erland Sommarskog wrote:
>>I get your point(s) :-) So FAT32 is out, not a big surprise.
Once upon a time I was playing around with file systems(JFS, XFS and
others), Oracle and AIX as well as OSF/1. I spent a considerable time
measuring performance for different configurations and journaling file
systems *with* the journal on the same disk as the db data(or translog)
was awfully slow compared to other configurations. I assumed that is
was due to disk head movements back and forth between the journal and
the file.
So even if the file system isn't the performance bottleneck right now,
I'd prefer to configure all the new disk drives optimally from day one.
I read the NTFS spec and browsed through all the options, but couldn't
find anything about tuning the journal. I'll have to reread it, I
guess...
There is the option of raw partition. A fellow MVP told me that he
made some tests some years back (so it was not on SQL 2005), and got
a 20% improvement with raw partition. But raw partitions are difficult
to manage, and I guess that you would still have to use NTFS for things
like database snapshots. Personally, I would never consider using raw
partition for a production system.
I thought that support for raw partitions was dropped? Even if it still
exists, I won't use it for data, maybe for a transaction log safely
placed on a tiny slice of a 15K RAID-1 disk. I hope I'll find the time
to test and measure the performance for such a setup. Nice just to know,
even if it can be a pita to find the proper statement mix to test with.
The best solution, IMHO, would be to have NTFS without journaling or
NTFS with the journal on a separate disk. If that's not possible, we'll
probably just settle for regular NTFS partitions, or maybe throw in a
couple of RamSan-400 boxes ;-)
>
20% may sound significant, but there other ways to get a 20% speedup,
for instance by sticking to a binary collation,
What about proper sorting of our æøå's and other funny european
characters? Won't work, will it?
or using an SQL
collation for varchar data. In fact, for a query like.
SELECT ... FROM tbl WHER col LIKE '%Whatever%'
the difference between a CI/CS collation and a binary collation can be
factor seven.
You also lose 90% in performance by writing a bad query.
That I've noticed. Lots of 'silly' gotchas to remember and beware of.
BTW, I did som more profiling today on one of the production servers,
expected everything to be quiet as it is sunday. Quite a surprise to
find that it was performing 662 calls to sp_reset_connec tion per second
over a period of 45 minutes. As you may have guessed, the system has
'issues'...
>
You may be interested in this article:
http://www.microsoft.com/technet/pro...hysdbstor.mspx.
Thanks, I'll read it asap.
Another thing: Have you got any links to a site describing proper use of
SARGs and operators and the query optimizer? I threw away my copy of SQL
Server Unleashed (never do that) and have forgotten all about it. The
Unleashed for 2005 won't we available until Nov. 27th...
Bjørn
Bjørn Augestad (bj************ @gmail.com) writes:
Erland Sommarskog wrote:
>20% may sound significant, but there other ways to get a 20% speedup,
for instance by sticking to a binary collation,
What about proper sorting of our æøå's and other funny european
characters? Won't work, will it?
Correct. A binary collation comes with a price. So does raw partitions.
Another thing: Have you got any links to a site describing proper use of
SARGs and operators and the query optimizer? I threw away my copy of SQL
Server Unleashed (never do that) and have forgotten all about it. The
Unleashed for 2005 won't we available until Nov. 27th...
No direct link, but the obvious place to look around would be http://www.sql-server-performance.com/
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
Bjørn Augestad wrote:
The best solution, IMHO, would be to have NTFS without journaling or
NTFS with the journal on a separate disk. If that's not possible, we'll
probably just settle for regular NTFS partitions, or maybe throw in a
couple of RamSan-400 boxes ;-)
I'm by no means an expert on NTFS but as far as I can determine
journaling applies only to file allocation, not to data. In a
well-managed environment SQL Server's storage should generally be
pre-allocated so I don't see why journaling should need to be a
performance constraint at runtime.
Maybe I've missed something but I have actually never heard anyone
raise a concern about this before and I've found virtually no relevant
information on it. That suggests to me that it's not an issue I need to
be concerned about. My impression is the same as Erland's: other
factors must rate as far more important determinants of DBMS
performance.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online: http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--
Erland Sommarskog wrote:
Bjørn Augestad (bj************ @gmail.com) writes:
>Erland Sommarskog wrote:
>>20% may sound significant, but there other ways to get a 20% speedup,
for instance by sticking to a binary collation,
What about proper sorting of our æøå's and other funny european
characters? Won't work, will it?
Correct. A binary collation comes with a price. So does raw partitions.
Would you please explain what you intend with this comment about block
devices. With any database, other apparently than Microsoft's, there is
an advantage that accrues to those who forgo file systems. Why is SQL
Server different?
Thanks.
--
Daniel A. Morgan
University of Washington da******@x.wash ington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group www.psoug.org
DA Morgan (da******@psoug .org) writes:
Erland Sommarskog wrote:
>Correct. A binary collation comes with a price. So does raw partitions.
Would you please explain what you intend with this comment about block
devices. With any database, other apparently than Microsoft's, there is
an advantage that accrues to those who forgo file systems. Why is SQL
Server different?
I referred previously in the thread to a fellow MVP who had found a 20%
improvement with raw devices over the file system.
The price I'm talking about is the more complex administration that raw
devices buy you.
As for why they are complex... Many years ago when I worked with Sybase,
I had to travel to customer to perform a triple upgrade: upgrade HP-UX
from 8.0 to 9.0, Sybase from 4.2 to 4.9 and our own application. The
customer had adequate staff to run the OS upgrade themselves, but still
they wanted us to it. The reason: in this OS upgrade HP-UX introduced
logical volumes, having had only fixed partitions before, and the customer
did not know how to handle Sybase, where all data was on raw partitions.
Had the databases been on plain files, the OS upgrade would have been
trivial for them. But in those days, Sybase strongly discouraged you from
running production databases on file-system devices, and with a good reason,
since buffering in the file system could cause problems.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
DA Morgan (da******@psoug .org) writes:
Erland Sommarskog wrote:
>Correct. A binary collation comes with a price. So does raw partitions.
Would you please explain what you intend with this comment about block
devices. With any database, other apparently than Microsoft's, there is
an advantage that accrues to those who forgo file systems. Why is SQL
Server different?
It occurred to me that is another reason why SQL Server is different, which
is inherent in its architecture. Other engines have to support multiple
platforms and file systems, so they need to implement some abstract
interface to handle the file system. Using a raw device means that that
layer they can cirumventet.
Since SQL Server supports only Windows, it can be spared that layer of
abstraction, and thus the gain with raw devices is not equally compelling.
I like to stress that all this is pure speculation on my part.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: tiacux |
last post by:
Tech Associates is recruiting for:
A SQL Server Database Systems Administrator for Jacksonville, Florida.
Salary to $75,000.
Description
The Database Systems Administrator's role is to direct, evaluate,
review, and manage database resources and services across the
organization while ensuring high levels of performance and
availability. This individual is also responsible for developing,
|
by: Water Cooler v2 |
last post by:
There are two datatypes for storing binary data type in the SQL Server:
1. binary - for fixed length binary data
2. varbinary - for variable length data
My question is: how is data inserted into them? Do they have any
delimiters that go into the insert statement like strings and datetimes
have? What format (hex/decimal?) do they accept data in? Can you please
give me an insert statement example?
|
by: AlexDP |
last post by:
My program has to access an SQL Server DB file that is in the same folder as
the executable. i dont want to setup a server. i was able to do this on my
own computer, the program would access any sql server db file without it
being attached to the server. When i take it to another system it fails to
run.
All systems have SQL Server Express and SQL Server 2005 installed.
Can someone please direct me through the steps to fix this problem?
...
|
by: dotnetfellow |
last post by:
The system has worked for two years.
SQL Server 2000 runs on Windows 2000 Server "A".
Another instance of SQL Server 2000 was moved from Windows 2000 Server
"B" old to Windows 2003 Server "B" new, by restoring a backed
up copy from old to new.
The system has cross server updates, where Server "A" is updating
records in very large tables on Server "B".
|
by: Jobs |
last post by:
Download the JAVA , .NET and SQL Server interview with answers
Download the JAVA , .NET and SQL Server interview sheet and rate
yourself. This will help you judge yourself are you really worth of
attending interviews. If you own a company best way to judge if the
candidate is worth of it.
http://www.questpond.com/InterviewRatingSheet.zip
| | |
by: Developer |
last post by:
Hello All,
i have recently installed VS2005 and was trying to install SQL sever
2000.
I have Win XP' SP2. But when I tried installing, it only installed
client tools and not the database.
Can anyone please help me with this as I want to install SQL server and
also wouold be grateful, if you can suggest me any workaround to
dealwith this problem.(Like should I install any new OS etc).
Any help would be appreciated.
|
by: Coldfire |
last post by:
Since i cannot show the differences in a two-column like table. I am first putting
MS SQL Server 2005 and then MySQL 5.x.
MS SQL Server 2005
Brief Overview
- SQL Server is a full-fledged database system developed specifically for large enterprise databases. All advanced features of a relational database are fully implemented.
- Once you purchase the product, you are only limited to the Sybase-derived engine.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |
