Disk block size with PowerShell

Recently while working with one of the customer engagement, we had a discussion about recommendation on disk block size for SQL Server.

Do you need to worry about disk block size?
I would suggest you to read below articles for getting better understanding on this topic

Disk Partition Alignment Best Practices for SQL Server
WHAT IS SQL SERVER’S IO BLOCK SIZE?

Post discussion, the next question was how do we check the disk block size for a given server?
You can do it from command line using FSutil utility. But let's do it with PowerShell. Here is the code which I ran to get the block size details

Get-CimInstance -ClassName Win32_Volume -Filter "FileSystem='NTFS'" | Select-Object Name, Label, BlockSize | Format-Table -AutoSize

OR

Get-WmiObject -Class Win32_volume -Filter "FileSystem='NTFS'" | Select-Object Name, Label, BlockSize | Format-Table -AutoSize

Click here, if you are confused whether you should use Get-CimInstance or Get-WmiObject  ☺

After showing it on one server, then customer gave me a list of SQL Servers on which we should get the similar details.

PowerShell makes things more simple. I got the server list in a text file and saved it on my client machine desktop. Open PowerShell and run the below code.

$ServerList = Get-Content 'C:\Users\user1\Desktop\ServerList.txt' 

Get-WmiObject -Class Win32_volume -Filter "FileSystem='NTFS'" -ComputerName $ServerList | Select-Object PSComputerName, Name, Lable, BlockSize | Format-Table -AutoSize

Here is the sample output. Just make sure you replace 'C:\Users\user1\Desktop\ServerList.txt' path as per your requirement.

Hope this helps

Cheers,
Naveen

SQL Server 2017 Hidden Gems – Part 2

In part-1, I discussed about tracking modified extent page counts.  Starting from SQL Server 2017 CTP 2.0 processor information is visible in sys.dm_os_sys_info dmv. This was another highly requested feature from the community. New columns which are added in sys.dm_os_sys_info dmv are socket_count, cores_per_socket, numa_node_count.

SELECT @@version as 'SQL Version', socket_count, cores_per_socket, numa_node_count FROM sys.dm_os_sys_info

Some of the other ways to get the processor information

Using PowerShell

Get-WmiObject -Class win32_processor | select DeviceID, SocketDesignation, NumberOfCores, NumberOfLogicalProcessors

Using System Information

Open run (Windows Key + R) -> Type msinfo32 -> Hit Enter

Hope this helps

Cheers,

Naveen

PowerShell Pipelines and Filtering Output – Part 3

In this post we will see how pipeline works and how you can filter output in PowerShell.

If you are new to PowerShell, I would recommend you to read my previous blog Part-1 and Part-2 before going through this post.

By understanding Pipelines and filtering objects, you take your PowerShell scripting to the next level. What pipeline does is it take a set of output (which is referred as object) and its sent across the pipe as an input to next cmdlet to do something.

Let's jump into some examples to see pipeline in action

First, I am going to start a notepad process. Then we will see how the process information can be sent as an input to stop/terminate the same process.

-Confirm parameters comes handy when you writes scripts which is doing some changes to your system. Its always good to get a message like whether you want to perform this action

Now let's see internally on what logic these objects are passed over the pipeline


To demonstrate this, let's take the below example

This is a best example I have seen to explain the way how pipeline works. Thanks to Jeffery Snover who showed this in one of his presentation.

So let's do a Get-Help on Get-ChildItem to see list of parameters which accepts pipeline inputs

Then pass Get-Process to Get-Memeber which show us the list of methods, properties and typename for a object.

In our case -Path parameter matches with the Path property in Get-Process

So internally the path property of Calc process is passed as an input to Get-ChildItem. This is how pipelines work in PowerShell. By knowing the way how pipeline works will definitely help you to troubleshoot your scripts easily and write better codes as well.

So that's it about pipelines. Now we'll see how to filter the output using where-object

Let's take Get-Process cmdlet for this demonstration. Assume you got a requirement to generate a report(html format) for every 15 minutes to get the list of process whose handles are greater than hundred and output should be sorted (descending).

Just the output of Get-Process

Filter our results using where-object. These are the different ways to write your where-object cmdlet. Everything gives you the same output.

Now sort the output in descending order, covert the output to html and make it as a report.

Get-Process | where Handles -ge 1000 |Select-Object Name, Handles, Product, Description, Company | Sort-Object Handles -Descending | ConvertTo-Html | Out-File ProcessReport.htm

We just created a html report. But wait, I know what you might be thinking? Format doesn't looks good, right? Yes, but again this can be customized which I will cover in the future post.

In the next blog we will see how this can be done on multiple machines from one server

Cheers,
Naveen

3 Important PowerShell Cmdlets – Part 2

In my previous blog, we saw a quick introduction to Windows PowerShell. Now let’s run through some of the important cmdlets.


Get-Help

Get-Help is the most important cmdlet. Microsoft has made lot of efforts to make Get-help a super cool cmdlet. This cmdlet gives you all information that you need to know about anything with PowerShell. If you feel that you got struck somewhere while working with PowerShell, Get-Help is the best place to look for your answers.
Here is the portion of Get-Help cmdlet output which explains how to use the cmdlet

Now let use the Get-ChildItem cmdlet with Get-Help. This is just the basic help content for Get-ChildItem.

Here are the few important things which you should look at

• Two sets of syntax are called as parameter sets. So we have two parameter sets for Get-ChildItem. 
• The text which you see after a dash (-) is called the list of parameters available for the cmdlet.
• Reason, why we have two sets of syntax is, if you look at the first set you've got -Path which is not available in the second set of syntax. And in the second set you have a parameter called -LiteralPath which is not available in the first set. So that’s the difference between those two set of parameter sets. Key thing to note is you cannot use both -Path and -LiteralPath parameters at the same time.

• Take a look at the square and angle brackets. Below is the portion of syntax output of Get-ChildItem help.

Get-ChildItem [[-Path] <string[]>] [[-Filter] <string>] [-Include <string[]>] [-Exclude <string[]>]

<string> -> This defines the datatype used by the parameter
<string[]> -> [ ] with the datatype indicates the parameter can accept multiple inputs
[-Include <string[]>] -> Parameter within [ ] means its optional parameter (Not mandatory)
[[-Path] <string[]>] -> Parameter defined under [ ]. In this case we have -Path and -Filter within [ ]. These parameters are called Positional Parameters. You need not mention the parameter name for positional parameter when passing inputs

If you look at the above code I have used asterisk ( * ) represents the wildcard which you can use to filter or search for a keyword.

At last you have the aliases information.

There are different parameters which you can use. Let’s see some of them

Get-Help Get-ChildItem -Full

Some of the key things to look under the parameters section. I have just highlighted 3 things, but that doesn’t mean other stuffs are not important. Just to start with, first, we can concentrate on those 3.

Get-Help Get-ChildItem -Detailed -> will give you description about each parameter

Get-Help Get-ChildItem -Examples -> will list you a set of examples which you can refer

You also have an option to get the output to a user interface window. Just add -ShowWindow switch to do it

Get-Help Get-ChildItem -ShowWindow

Get-Command

This cmdlet helps you to discover the list of cmdlets available. It’s kind of a dictionary of PowerShell cmdlets. Using Get-Help let’s see what parameters this cmdlet can take.

Let’s do a search using the -Name parameter and see if we have any cmdlets related to service.

Get-Command -Name *service*

So now we've got all command types which has a word service in its name. You can see all cmdlet and function follow a standard naming format which is verb-noun.

We got a cmdlet which is Get-Service. By looking the name itself, you can just imagine what could be the output. Let’s run and see what it gives

Get-Service

Okay, as expected it gave us the output of list of services available in my machine. It printed status, Name and DisplayName. But if you open services.msc, we have lot of other information like service startup type, description, dependent services and all other stuff.

PowerShell can also give you all that information. Get-Member is the cmdlet which allows you to explore more property and methods for a cmdlet.

Get-Member

This cmdlet gives you details about cmdlet property and its methods. Let’s explore Get-Service cmdlet to see what else we can get from it

Get-Service | Get-Member

| -> This is called pipeline. I will cover more about this in the next blog. On a short note, the output of first cmdlet is passed as an input to the second cmdlet. In the above command output of Get-Service is sent across the pipe as an input to Get-Member.

Now we will filter the output to display only the properties for Get-Service cmdlet

Get-Service | Get-Member -MemberType Property

We have got a set of properties which can be displayed in the output. Now let’s see how to display other properties in your output. To keep it simple, we will just use services which has win in its name.

Get-Service -Name *win*

Now we will write the code which will display only Name, Status and dependent service 

Get-Service -Name *win* | Select-Object Name, Status, ServicesDependedOn

First, we filtered services with *win* in its name. Then we piped the output of that to next cmdlet called select-object. That’s simple, isn’t it?

Instead of select-object, you can just use select (select is the alias of Select-object)

If you want to get all the details, just do a select * (like how we do in SQL Server; select * from table).

Get-Server -Name WinRM | select *

In the next blog, we will discuss more about pipelines and filtering output

Cheers,

Naveen

Introduction to Windows PowerShell – Part 1

Windows PowerShell is a windows command-line shell designed especially for administrators. PowerShell comes with interactive prompt and integrated scripting environment. PowerShell is kind of very much advanced version of your windows dos. PowerShell is a powerful scripting environment where you can do all sorts of things from managing files, users, exchange, active directory, SQL Server and the list goes on. This powerful tool is built on top of .NET Framework CLR and .NET Framework.

Learning PowerShell will make your life easier. Just imagine you have been given a task to update a set of property for all the users in the AD or you need to pull a report which has information about all the servers in your network like IP address, CPU, RAM, storage and list of services etc. Performing these kind of tasks manually will take hours of your time to complete it. Believe me using PowerShell you can do it with just few lines of code in very less time.
I have decided to write a blog series which will be helpful especially for DBA’s who loves to learn PowerShell. This is the first blog post in this series, we will see how to open PowerShell and run few basic commands.

Let's get started!!

How to open PowerShell ?

There are different ways to open PowerShell. Launch start menu and just search for PowerShell..

Press Windows Key + R -> Type PowerShell and hit enter

If you open PowerShell, you will land up in a blue screen command line interface. Now let’s run some basic commands which we use to run in dos. Please note most of the command which you run in dos will work with PowerShell. 

In Windows PowerShell we have a concept called as cmdlet (command-let). PowerShell has more than hundreds of cmdlets and you can also make your own cmdlets too. All cmdlets have a standard naming format verb-noun format. Now let’s see how get the list of cmdlets and find out what version of PowerShell you’re running

Get-command will give you list of all cmdlets.

$PSVersionTable gives you the version of your PowerShell

Now we will see how to get the list of folders from a given directory using the actual PowerShell cmdlet. 

Get-ChildItem is the actual PowerShell cmdlet to list the folders.

So now we saw that dir, ls and Get-ChildItem all gave us the same output. Let’s see how things are linked with each other. PowerShell has a concept called alias using which you make your code shorter by just calling the aliases. 

Get-ChildItem has three aliases. Now the question is how to get the details about these aliases. PowerShell help is the best place to look for information about any cmdlet and its always recommended to update your help on regular basis. How to update help, I will cover on different post.

So now let’s see what’s there in help for our Get-ChildItem cmdlet.

Get-help cmdletName is the command which will give you information about the cmdlet.

There are ways to get the list of aliases for a given cmdlet. I will cover that in a different blog post.

In the next blog post we will see some more cmdlets and will focus on how things can be used in doing administrative tasks. I will be covering more specifically from server and database administrator’s standpoint.

Cheers,

Naveen