Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

The disk allocation unit verification for SQL server 2008

Dear expertise,

right now as we found out the volume the SQL server data/log running on is 4k instead of 8k or 64k, we will start a new project and format the disk to 64k.

However, I have one question, how can I make sure that SQL server 2008 make sure of this change  so that any select query can run faster? right now the benchmark result is not as fast as it expected (64/4= 16 times faster).

Do we need to change any reg . ?

DBA100.
0
marrowyung
Asked:
marrowyung
  • 3
  • 2
2 Solutions
 
jorgedeoliveiraborgesCommented:
I guess the benchmark is the best approach in sql server 2008 environment.
The sql server 2008 environment is quite differente from another provider.

0
 
mozculluCommented:
not only cluster size but also disk offset is something important. Also the performance changes according to your environment. 64k is the most widely used cluster size.

check this link:
http://msdn.microsoft.com/en-us/library/dd758814(v=sql.100).aspx
0
 
marrowyungAuthor Commented:
jorgedeoliveiraborges and mozcullu,

Thanks for all of your help. But any way to verify SQL server already make use of this after changing from 4k to 64k ?

DBA100.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mozculluCommented:
there is SQLIO utility for benchmarking Disk IO for SQL but the results will not differ with block size change for this ustiliy as it is not using SQL patterns to write the disk.

The best approcah will be testing the performace on you application itself. you can use SQL profiler to track the changes on the performance of each operation on changed disk clsuter sizes.
0
 
marrowyungAuthor Commented:
mozcullu,

Any information on how to use the SQLIP utilty to benchmark Disk I/O? for me, it is a capacify planning tools but not benchmark tools.

How to use SQL profiler to track the change on performance.

DBA100.
0
 
marrowyungAuthor Commented:
also after format the disk to 64k, any thing needs to be done before formatting ? we are connected to the EMC SAN and according to EMC best practise, we have to use diskpart.exe to do physical format, right?

On Windows 2008, do we need to do this?

DBA100.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now