SQL server disk block size formatted as 4KB runs slow

Question

Post reply

SQL server disk block size formatted as 4KB runs slow

Saran

Right there with Babe

Points: 760
More actions
March 6, 2024 at 8:52 am

#4390482

Hello All,
One of my SQL server 2014 all the disk are formatted as 4KB. I see Microsoft recommends to have 64KB.
I feel the query running slow and most of the query waits shows as two waits 1. PAGEIOLATCH_SH 2.CXPACKET and overall server wait also same.
Please share your experience.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 1

I have no experience with speed differences because of blocksize.

pageiolatch: you are retrieving data from storage subsystem

cxpacket: your query went parallel ( do you need this ?)

https://www.sqlskills.com/help/waits/pageiolatch_sh/

https://www.sqlskills.com/help/waits/cxpacket/

ScottPletcher SSC Guru Points: 101120 More actions · Answer 2

The *data* file drives should be 64K. Log file drives should still be 4K. If you mix data and log files on the same drive, you'll want to use 64K.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Saran Right there with Babe Points: 760 More actions · Answer 3

Thank your for your suggestion. Yes, All the drives are 4KB format only - Data , log etc.

I will check top duration query and will see to be indexed. I believe that will reduce the storage subsystem IO.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

ScottPletcher wrote:

The *data* file drives should be 64K. Log file drives should still be 4K. If you mix data and log files on the same drive, you'll want to use 64K.

What is the Microsoft link to backup those recommendations, please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ratbak Ten Centuries Points: 1381 More actions · Answer 5

The closest I could find are this article from 2009 and this article from 2009

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 6

Both places I have worked some of the drives were formatted to 4k instead of 64k. Personally I don't think it truly makes that big of a difference from what I have seen. However, maybe your environment is different than ours.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

ratbak wrote:

The closest I could find are this article from 2009 and this article from 2009

I'm still waiting for Scott to backup his claim with a link.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Saran Right there with Babe Points: 760 More actions · Answer 8

We have migrated to premium disk in Azure, now all running fine and faster.

Thanks all.

Ant-Green SSC Guru Points: 113507 More actions · Answer 9

Make sure you go through the Azure SQL VM checklist to get the best bang for your buck on what you’re paying out for.

https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-checklist?view=azuresql