File Allocation Unit Size – A Myth


The overall meaning of “Allocation Unit Size” in brief is….

The default allocation unit size on a windows drive is 4KB. When we format the drive, we can set that to a larger size – again 64KB is recommended for SQL Server data, log, and tempdb files.
The allocation unit size is basically the smallest unit of storage that any individual file can take up. If I set the allocation unit size on a drive to 64KB and then create a tiny text file which only takes up 11 bytes, that one file will take up 64KB on disk.

Continue reading

Advertisements

Multiple Transaction log files, Pros & Cons…

There is no performance benefit having multiple log files…We just need to make sure, that growth is set correctly. 

Continue reading

Unable to Shrink Tempdb Data and Log files

Unable to Shrink Tempdb Data files have grown large.

You may receive this error message.

File ID of database ID cannot be shrunk as it is either being shrunk by another process or is empty Continue reading

Convert Query Execution Plan XML to Graphical View

Convert Your SQL Server Query Execution Plan from XML to Graphical View Continue reading

SQL Server – Lock Pages in Memory

This security setting determines which accounts can use a process to keep data in physical memory, which prevents the system from paging the data to virtual memory on disk. Exercising this privilege could significantly affect system performance by decreasing the amount of available random access memory (RAM). Continue reading

SQL Server : How to move or add Tempdb data and log files

Problem:

– TempDB has grown big and the current disk drive does not have enough space.
– Adding TempDB data file to another physical drive helps to improve performance, as they can be read simultaneously.

Solution : Here I have tried to explain how can we move tempdb data files from one place to another. Continue reading

Major performance counters to look for

Below are some major perfmon counters with thresholds to be analyzed for SQL Server behaviour. Continue reading