Speeding up PowerCLI

As you may have seen, I like PowerCLI. Only thing is, it's not the fastest to start up, and running the first cmdlet takes some time. After that, it goes fine.

The first tip from the VMware PowerCLI Blog is to go to:

Windows > Control Panel > Internet Options > Advanced > Security > Check for publisher's certificate revocation and uncheck that box (also helps the SQL Server Management Studio start up faster if it is not connected to the internet).

Unfortunately, this did not help me so much with running the first cmdlet. So I was mighty glad that I came across this article from vNugglets to speed up PowerCLI 5. This led to another VMware PowerCLI Blog article with all the steps for earlier versions.

Basically, the issue is that due to the fact that the .Net framework compiles the underlying code on first use, so you can precompile things to speed up first run:

Open up an administrative command prompt, and run the following commands:

C:\Windows\Microsoft.NET\Framework\v2.0.50727\ngen.exe install "VimService41.XmlSerializers, Version=4.1.0.0, Culture=neutral, PublicKeyToken=10980b081e887e9f"

C:\Windows\Microsoft.NET\Framework\v2.0.50727\ngen.exe install "VimService40.XmlSerializers, Version=4.0.0.0, Culture=neutral, PublicKeyToken=10980b081e887e9f"

C:\Windows\Microsoft.NET\Framework\v2.0.50727\ngen.exe install "VimService25.XmlSerializers, Version=2.5.0.0, Culture=neutral, PublicKeyToken=10980b081e887e9f"

If you are running on 64-bit OS, you need to run the following as well:

C:\Windows\Microsoft.NET\Framework64\v2.0.50727\ngen.exe install "VimService41.XmlSerializers, Version=4.1.0.0, Culture=neutral, PublicKeyToken=10980b081e887e9f"

C:\Windows\Microsoft.NET\Framework64\v2.0.50727\ngen.exe install "VimService40.XmlSerializers, Version=4.0.0.0, Culture=neutral, PublicKeyToken=10980b081e887e9f"

C:\Windows\Microsoft.NET\Framework64\v2.0.50727\ngen.exe install "VimService25.XmlSerializers, Version=2.5.0.0, Culture=neutral, PublicKeyToken=10980b081e887e9f"

This needs to be done once, and works for all users and applications.

I did a test on one setup, using the test  on the PowerCLI blog which is:

Measure-Command { Get-VMHost } | fl TotalSeconds

This test went from 24.4 seconds to 4.6 seconds! More than 5 times as fast!! Again, thanks go to vNugglets!

Using Perfmon for SQL Performance Tuning

A while ago I found this older but still fantastic blog post about SQL performance tuning:

http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

Excellent video in there, well worth watching. Yes, the video is from 2006, but even now, it is just as relevant.

A little after I saw the blog post, I got the chance to put this in practice too; A customer suspected his SQL cluster wasn't performing too well. I added the counters from the video to the perfmon counter log, and kept the perfmon running.

Then our customer came back after a half a year; he found the SQL performance had degraded even more. We had a nice list of performance statistics now that went deeper than just CPU and memory usage. Now, I got the number of User Connections, and saw that it went up 4 times.

The perfmon counters Brent Ozar was talking about:

• These are listed OBJECT first, then COUNTER
• Memory – Available MBytes
• Paging File – % Usage
• Physical Disk – Avg. Disk sec/Read
• Physical Disk – Avg. Disk sec/Write
• Physical Disk – Disk Reads/sec
• Physical Disk – Disk Writes/sec
• Processor – % Processor Time
• SQLServer: Buffer Manager – Buffer cache hit ratio
• SQLServer: Buffer Manager – Page life expectancy
• SQLServer: General Statistics – User Connections
• SQLServer: Memory Manager – Memory Grants Pending
• SQLServer: SQL Statistics – Batch Requests/sec
• SQLServer: SQL Statistics – Compilations/sec
• SQLServer: SQL Statistics – Recompilations/sec
• System – Processor Queue Length

To find out what they all mean, see the video:

http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/