Every now and then, I have to tackle a performance problem on a SQL Server I’ve never seen before. One of the things I’ll be looking for is the block size of the disks. The block size for drives containing SQL database files has to be 64k for optimal performance. This is well documented, in lots of Microsoft documents, including this one: https://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx .
I used to check the block size using the DOS command fsutil. Recently, I decided to enter the 21st century and start using Powershell to perform this check. I asked my friend Google to provide me a little script. I found a lot of them, but none that worked the way I wanted. I want to see all drives and all mountpoints, using a single script without having to rely on additional functions. It should also show the size and free space, because a drive that’s almost full needs attention as well. And o yeah, it needs to display all this in a legible format.
So I decided to make my own script, and post it here, for future reference and the benefit of other beginning Powershell users.
1 2 3 4 5 6 7 8 9 |
$wql = "SELECT Label, Capacity, Blocksize, Name, FreeSpace FROM Win32_Volume WHERE FileSystem='NTFS'" Get-WmiObject -Query $wql -ComputerName '.' ` | Select-Object Name, Label, Blocksize` , @{name="Free (%)";expression={[decimal]::Round((($_.freespace/$_.Capacity)*100),1)}}` , @{name="Capacity (GB)";expression={[decimal]::Round($_.Capacity/1gb,2)}} ` , @{name="FreeSpace (GB)";expression={[decimal]::Round($_.FreeSpace/1gb,2)}} ` | sort-object name ` | Format-Table -AutoSize |
I’m relatively new to Powershell, so maybe I’m not using the optimal way to achieve this. So, for future reference, a little explanation on the code:
- To make the code easier to read, the WMI query is created as a separate variable.
- Also for readability, I’ve cut the second line into chuncks using the backtick `
- Both free space and capacity are returned as bytes, and that’s pretty hard to interpret. Powershell provides the ability to convert these bytes into Gigabytes, using: FreeSpace/1gb. You can’t use Freespace directly, you’ll have to use the notation $_.Freespace, so the conversion into gigabytes becomes $_.FreeSpace/1gb.
- This will result in a decimal number with a lot of precision, but two decimals is accurate enough for me. So I’ll have to use a little .NET: [decimal]::Round($_.FreeSpace/1gb,2)
- To return a calculation with the Select-Object cmdlet, there’s this bit of pretty code: @{ name = “label”; expression = {…} } . Replace the … with [decimal]::Round($_.FreeSpace/1gb,2), and you’ve got the final result.