Recently on Twitter I ran across a comment by Oscar Zamora (b|t) about some “slowness” with Plan Explorer. We’ve gone to great lengths to ensure Plan Explorer is snappy and responsive, even when working with giant plans, so needless to say I was surprised to hear this (and perhaps a tiny bit incredulous 😉 I contacted Oscar for more specifics about where exactly he was seeing the lag, and he was happy to help. As it turns out, it wasn’t UI responsiveness he was referring to, it was the application startup time. Here are the startup times he was seeing on two different machines:
- Desktop
- First start: 22 seconds
- Warm start: 5 seconds
- Laptop
- First start: 40 seconds
- Warm start: 10 seconds
Yikes! After a quick version check, we determined he was still on beta version .96. After upgrading to v1.1, his load times dropped a bit:
- Desktop
- First start: 16 seconds
- Warm start: 5 seconds
- Laptop
- First start: 25 seconds
- Warm start: 9 seconds
Things were looking better, but still not ideal, especially on his laptop. On my desktop and laptop, both the initial and subsequent loads always run around 5 seconds. Next I took at look at his laptop specs and Windows Experience Index details. His graphics and disk performance were about 1 point lower than my laptop, but processor and RAM were about 2 points lower. This seemed to point directly to it being a jitting performance issue. JIT (just-in-time compilation) is standard behavior for .NET apps, so a startup delay is not unusual. Exactly how much of a delay depends on several factors, including timing of method calls, the amount of code being jitted, as well as CPU, memory, and, to some extent, disk speed.
It’s a safe bet that the slower CPU and memory on Oscar’s laptop are causing jitting to take longer than normal, but what can we do about it? Since the Plan Explorer code itself is already well-optimized, there really was only one other option: using the Native Image Generator, or NGen.
NGen is a tool that effectively pre-compiles an entire .NET app before runtime, and caches an image of it on disk. We’ve known about NGen since its first release, but since prior versions suffered from various shortcomings, we had elected not to use it. However, in .NET 3.5 SP1 and in .NET 4.0 many improvements were made, and since we now require .NET 4.0 for both Plan Explorer and SQL Sentry v6, it was time for another look.
Using NGen is straightforward. You can easily create a pre-compiled image of Plan Explorer via these steps:
- Open a command prompt as an Administrator
- Change to the .NET 4.0 directory:
x86 systems: cd %windir%\Microsoft.NET\Framework\v4.0.30319\
x64 systems: cd %windir%\Microsoft.NET\Framework64\v4.0.30319\ - Run the following command (assuming you’ve used the default install path – if not, change it accordingly):
ngen install “C:\Program Files\SQL Sentry\SQL Sentry Plan Explorer\SQL Sentry Plan Explorer.exe”
You should see a bunch of compilation-related messages fly by. If you don’t, then something isn’t right, perhaps an invalid path. When the messages complete, go ahead and open Plan Explorer.
Or, if you’d prefer to forego the manual process above, you can download the new Plan Explorer release with NGen compilation built-in. The compilation is deferred; it doesn’t actually happen during setup. Rather, the NGen service performs the compilation behind the scenes after installation, the next time your computer is “idle”. For this reason, bear in mind it may not complete before you launch Plan Explorer for the first time. You can tell when it’s done by looking at the “Microsoft .NET Framework NGEN v4.0.30319_x64” service status. It only starts when there are items queued for compilation, so if it’s stopped that means it has finished its work.
Either way you go, you should be pleased with the new load time. On every machine we’ve tested, startup is almost instant. If you’re opening Plan Explorer regularly, this time savings can add up, leaving you more time for plan-hacking!
Greg is founder and Chief Scientist at SentryOne. He is a Microsoft developer by background and has been working with SQL Server since the mid 90s. He is perpetually engaged in the design and development of SQL Sentry, Plan Explorer and other SentryOne solutions for optimizing performance on the Microsoft Data Platform.
