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.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
 
Metehan OzculluSQL Service ManagerCommented:
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
 
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
 
Metehan OzculluSQL Service ManagerCommented:
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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
marrowyungSenior Technical architecture (Data)Author 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
 
marrowyungSenior Technical architecture (Data)Author 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
 
marrowyungSenior Technical architecture (Data)Author 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.