Newer is not always better!


The other day working with a client, I was attempting to move the TEMPDB data files to a new Solid State Drive (SSD) drive!  I was stoked to see the performance gain with this because I have yet to work with SSD in a SQL Environment.

Of course I hit a problem or I wouldn’t be writing a blog post about it!

it is on a volume with sector size 8192. SQL Server supports a maximum sector size of 4096 bytes

Excuse me?  SQL Server can’t support a drive? Well needless to say this was problematic.  After extensive research, the client purchased the latest and greatest SSD.  But unfortunately neither of us paid attention to the details.  As always, the Devil is in the Details.

https://support.microsoft.com/en-us/kb/926930 

Apparently MS SQL Server cannot use a physical sector size larger than 4096.  It can use smaller, but not larger!  OK, next step what exactly are we working with.

Using FSUTIL command (fsutil fsinfo sectorinfo) I did confirm the physical sector size for the SSD was 8192.  Repeated attempts to re-partition and/or re-format was not successful and it should not have been because the “physical” sectors are 8192, not the logical sectors.

Physical sector size is determined upon manufacturing and firmware. My client had to go back to the vendor to see if firmware was available to change the physical sector to 4096! 

Once again, lesson learned!  Be specific and diligent in your hardware details when purchasing SQL Server hardware.

Advertisements

Posted on February 26, 2016, in SQL and tagged , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Humor Me a T-SQL Princess

DBAs really do have a sense of humor

Andrea Allred presents RoyalSQL

Bringing happy endings to all your data stories.

IDERA Community

A community site for users of all IDERA product and SQL community members.

Strate SQL

Data Adventures with a Architect

SQL Sanctum

A Join on SQL And Everything Else

Grant Fritchey

Intimidating Databases and Code

rhondastephens

To Catch A Falling Cactus

SQLSwimmer

Swimming through the Sea of SQL

SQL Studies

 Live, Learn, Share

Aunt Kathi's SQL Server Home

Learn SQL Server from your favorite aunt!

Jen's Blog: We Owls

...stuff. Also, things.

Journey to SQL Authority with Pinal Dave

SQL, SQL Server, MySQL, Big Data and NoSQL

Voice of the DBA

Writings from Steve Jones, the Voice of the DBA

Glenn Berry's SQL Server Performance

Semi-random musings about SQL Server performance

%d bloggers like this: