SQLsensei

Plan Explorer PRO 2.5: Query plans your way

2013-03-08T17:57:00.001-05:00

Have you ever worked with a query plan that was so large and unwieldy that it was next to impossible to make any sense of it? It can be a frustrating endeavor, for sure. Plan Explorer has always done some things that can help with these plans, such as color-scaling costs and vertically compressing subtrees. Even so, when you're zoomed out to 10% and still only fitting a quarter of the plan on screen, color-scaling won't buy you much.

The new version of Plan Explorer PRO 2.5 is focused on making these big plans easier to manage, and it does so using a variety of techniques that you have been asking for, which I will cover below. All of these new features are managed by this new toolbar at bottom of the plan diagram:

Operation Filtering

The quickest way to shrink most plans is to apply a cost filter using the Filter slider. This is just as it sounds – if an operator's (aka, iterator's) cost is less than the specified filter value, it will disappear from view. There is a caveat: any operators "upstream" in the same subtree as an unfiltered operator (one that exceeds the filter value) will still be shown, in an effort to avoid changing the meaning of the plan too dramatically. The net effect is that as you increase the filter value, op nodes will start dropping off at the leaf levels, working up towards the root node.

Here's a nice example using a plan Jonathan Kehayias (blog | twitter) shared with me a while back, aptly named "ReallyBadPlan.sqlplan":

Here is the original, with no filter:

... and with a .1% filter applied:

...and with a 2.3% filter applied:

As you can see, with big plans a little filtering can go a long way. The zoom level is reduced significantly, and the whole (filtered) plan is in view. The more op nodes, the lower the average cost per node, so this makes sense.

Collapsible Subtrees

Another way to quickly reduce a plan is by collapsing subtrees that aren't of interest. You do this via the new expander element on the head node of each subtree:

The expander's primary function here is to collapse, but "collapser" just doesn't have the same ring to it ;-)

Multiple Layout Modes

Some plan types simply don't fit well on screen, and this is where using a different layout mode can be helpful. It'll be easiest to illustrate this with a couple of common examples.

First, let me say that these "non-standard" plan layouts may be unsettling for some. If for whatever reason you are only willing or able to process plans using the traditional layout, my recommendation is this: don't use this feature, and skip to the next section now, lest you may have these images permanently seared into your brain!

Stair Step Plans

First up is a fairly extreme example of a "stair step" type plan, which I see most often with data warehouse queries against star schemas:

The plan is so vertically intensive that there is no way to get the entire thing in view and still have it be usable. By usable I mean in a state where I can clearly see the costs, colorization, line widths, operator glyphs, and other visual cues necessary for troubleshooting.

By changing to Alt-1 mode we can vertically compress this plan in dramatic fashion:

I haven't applied any filters, or changed any other settings, and as you can see it's already much more usable. The trick is being able to do a little neural rewiring such that the non-standard orientation of some of the nodes with respect to each other doesn't interfere too much with your interpreting the plan.

UNION Query Plans

With UNION queries you effectively have multiple individual plans in all their glory stacked on top of each other. This can make them quite tall due to the potentially large subtrees off of the concatenation operators, as in this plan:

Bus mode can sometimes help with this, like so:

With Bus, subtrees can be centered and staggered, taking better advantage of the horizontal screen real estate, typically a less precious commodity on modern displays.

Be warned, not every layout mode will work well with every plan. For example, Bus mode applied to the "Really Bad Plan" above will create something far nastier and far less usable than the original. Every plan is different, so you may need to experiment to find which layout works best.

Rotation

As Paul White (blog | twitter) describes in his post Iterators, Query Plans, and Why They Run Backwards, plan execution order flows left-to-right, which is likely why Microsoft decided to put the root node on the left. He also covers how data flows in the opposite direction, right-to-left. So if you are concerned about IOs, you may commonly start on the right, as I do. Since this is opposite of normal reading direction for many of us, a mirrored view may be easier to process. This can be accomplished by clicking either rotate arrow twice to get to 180 degrees:

If you change to Centered mode and click the right rotation arrow once to 90 degrees, the plan starts to look like an org chart, which may work better for those who read top-to-bottom (or who work in HR):

Dynamic Auto-fit

Your wrist will appreciate this feature – it automatically resizes new layouts to fit the screen, so you don't have to continually change the zoom level after applying filters or otherwise changing the plan layout. (Astute readers may notice that this checkbox is shown in some of the shots and not others – that's because it was added while I was writing this, and I didn't feel like redoing a bunch of shots ;-)

Persisting and Sharing Custom Layouts

Custom layouts are automatically persisted when saving a plan with Plan Explorer as a .queryanalysis or .pesession file, and these files can be easily shared by clicking the Post to SQLPerformance.com toolbar button, emailing, etc. Note that manually repositioned nodes are not currently persisted, but we're working on that.

Did I mention that you can now easily upload your plans to Answers.SQLPerformance.com from all versions of Plan Explorer, and receive input from some of the world's foremost experts on query plans, including Paul White (blog | twitter)? Aaron Bertand (blog | twitter) covers this feature in detail here.

Plan Explorer PRO users who don't want to use the custom layout in a shared plan can hit the Defaults button to reset everything. Plan Explorer FREE users can view, but cannot modify, Plan Explorer PRO custom layouts via the Apply Embedded Layout Options checkbox at bottom left of the diagram:

Closing

There are probably as many different optimal layouts as there are big plans. Our goal with this release was to provide more control over how plans are presented, so you can view them in ways that work best for you. You'll need to experiment to see which combinations of modes and settings work best for each plan. I've personally had a lot of fun playing around... even the really bad layouts can provide some entertainment value ;-)

Kudos to the dev team led by Brooke Philpott (blog | twitter) and QA team lead by Steve Wright (blog | twitter) for their great work here. Thanks also to those of you who have shared your ideas with us... this release is a direct result of that feedback.

Oh, and if you find settings that work particularly well for certain plan shapes, please do let me know: ggonzalez at sqlsentry.net. Enjoy!

SQL Sentry v7.2 does Windows (and a whole lot more)

2012-10-26T17:16:00.001-04:00

Since releasing SQL Sentry v7 with Fragmentation Manager in May, we've been hard at work on v7.2. Development ran concurrently with Plan Explorer PRO, which was released last week. v7.2 includes two exciting new products: Performance Advisor for Windows and Event Manager for Windows. The release candidate was recently published, and you can download it from our customer portal. (Existing customers please contact us for eval licenses.)

The new features fall into three main categories: Query Plan Analysis, Windows Monitoring, and History/Alert Filtering.

Query Plan Analysis

As you may have guessed, the new version of Performance Advisor for SQL Server includes all Plan Explorer PRO features! Read about those in my last post.

Windows Monitoring

One of the most common requests we've received over the years has been for the ability to monitor any Windows computer, such as one running SharePoint, IIS, SSRS, or SSIS services, but no SQL Server or SSAS services. Performance Advisor for SQL Server has actually always had Windows monitoring built in – it's effectively the left-hand side of the dashboard – the problem was, you only got it if you also had SQL Server or SSAS installed on the machine.

I'm happy to say that not only has this restriction been removed in the new Performance Advisor for Windows, but when combined with the new Event Manager for Windows you now have truly unprecedented capabilities for monitoring Windows performance. Keep reading for a rundown.

Service & Process-level Metrics

To date, probably the most common way to find out what's happening with Windows processes has been via Windows Task Manager. Process Explorer is a more robust tool that is also used, however, both tools suffer from some critical shortcomings:

It's not easy to tell which Windows services are associated with which processes. Task Manager now has "Go to Service|Process" context menus, but in practical use they are not very helpful. Process Explorer requires a few clicks to view associated services. Most importantly, both tools are "one-process-at-a-time".
Lack of historical data. Process Explorer has some limited charting, but neither tool can show which process(es) brought a server to its knees 3 hours ago, let alone view the data in context with what was happening with SQL Server or other key metrics at the time.
No way to easily get performance metrics for individual Windows services, or combined metrics for multiple related processes/services. SharePoint and IIS in particular utilize several processes and services, and the problem may actually be the cumulative impact.

Performance Advisor for Windows addresses all of these issues by:

Auto-correlating processes with services.
Providing historical performance data for processes and services.
Organizing related processes into friendly groups, with individual and group level metrics.

In the dashboard shot below, the tannish series represents all SharePoint processes and services, and by hovering over it I can instantly see that two SharePoint search processes together were mostly responsible for the CPU spike:

If I want full details, I can simply select the range and then Jump To > Processes:

...which takes me directly to the new Processes tab, where I have a full list of all active processes and metrics for the range, organized by service and group:

(click to enlarge)

It's just as easy to find the largest memory consumers – here there are several IIS worker processes consuming over 1GB of RAM, and causing memory pressure for a SQL Server instance on the same machine (not a recommended configuration!):

Several groups come preconfigured, including SharePoint, IIS, SSIS, SSRS, and SQL Sentry, and we'll be expanding this list as time goes on. You can also easily add new groups yourself – more on that in a future post.

If, like me, you've spent way too much time using the "old school" ways of troubleshooting process/service-related performance issues, these new features should be a godsend.

Processor Groups + NUMA Support

With Windows Server 2008 R2, Microsoft introduced processor groups as part of support for more than 64 processors on a single computer. Lately we've been seeing more and more of these systems come online for both multi-instance SQL Server environments, as well as VMWare or Hyper-V hosts running tens or hundreds of VMs.

Previously the PA dashboard maxed out at 40 processors due to limitations in how perflib reported instance level data. Now the dashboard will show utilization across all processors on a system. Here's a shot of an 80-processor system (4 x 10 cores, hyperthreaded):

(click to enlarge)

If you look closely, you'll see that the NUMA node associated with each processor is indicated using the format <NUMA Node ID>:<Processor ID>. This system has 4 NUMA nodes, and here only nodes 1 and 3 are active. This information can be invaluable for evaluating how your NUMA resources are being utilized, and for judging the effectiveness of configured affinity settings.

Windows Event Log Monitoring

We've rolled the former Event Manager for Task Scheduler into the new Event Manager for Windows product, and added Windows event log monitoring, another hugely popular request!

You can selectively enable/disable monitoring for the Application, Security and System logs individually. Once enabled, events will show up on the calendar and become available for alerting via the new Windows Event Log: Event condition. By default, only the Application and System logs are watched by SQL Sentry, and only events from SQL Server or SQL Sentry services are collected using the new history filters.

History and Alert Filtering

Which leads me to our next topic: filtering. There are two types of filters – history (or source) filters and alert (or condition) filters. They're both configured the exact same way.

History Filtering

Many event sources now have a configurable filter which allows you to tell SQL Sentry exactly which events you want to collect and store from that source, giving you much greater control over associated storage requirements, which events will show up on the calendar, and which are available for alerting. You'll find this under Settings > History Filter for each source. This shot shows the new Windows Event Logs Source filter:

This is the default filter (as mentioned above) which collects only events for the SQL Sentry and SQL Server applications, and only non-backup events (since you'll often already have the associated job and Top SQL events on the calendar). We've made this pretty restrictive to start since the Windows logs can get flooded with events from various sources, and we didn't want to overload you with stuff you may not want to see. That said, you are free to modify these filters to your heart's content ;-)

Alert Filtering

You can think of alert filtering as a layer on top of history filtering. For example, using the history filter you may want to collect all SQL Server related events from the Windows logs and see them on the calendar, but you only want to be alerted for errors. This is trivial to configure by selecting the Condition Settings tab for the Windows Event Log: Event condition:

A really cool aspect of filter configuration is that you pick from a list of filterable fields applicable to that particular source, and if the possible values are known, you pick from those too. Here are some other common use cases:

Only send an email for deadlocks NOT caused by a particular application (ahem, SharePoint ;-)
Only send an SNMP trap to SCOM for SQL Agent job failures for jobs in a certain category
Only alert for Top SQL failures coming from a specific host machine

These examples are only scratching the surface of what's possible with the new filtering. I'm always interested in hearing how new features like this are put to use, so please let me know about your favorite filters.

More than Windows?

Although I've been using "Windows monitoring" as a general term, these new features are especially applicable to some specific scenarios such as managing a SharePoint Server farm where you have different services split across many servers. You'll still have one or more SQL Servers playing a large role in farm performance, of course, but your visibility won't be limited to those servers... for the first time you can have a complete picture of performance across the entire farm!

We've published pricing for Performance Advisor for Windows, Event Manager for Windows, and the combined Power Suite for Windows here. The full product pages should be live by early next week.

We'll be showing off v7.2 in person over the next two weeks at DevConnections and then the PASS Summit, so please stop by our booth for a full tour!

It's Time to Go PRO!

2012-10-11T16:33:00.001-04:00

It's hard to believe that we released the first version of Plan Explorer almost two years ago! The acceptance it has seen by the SQL Server community during that time has been amazing, and tremendously gratifying for all of us here at SQL Sentry.

From the start we've had many requests for additional features and functionality. Many of those went directly into the free tool (for a full list go here). For others, it was obvious that some serious heavy lifting and/or an increase in the support requirements would be involved, making them not well-suited for a free tool.

Before I get into PRO, let me clarify something up front:

* The FREE version of Plan Explorer is not going anywhere *

We are simply rebranding it "Plan Explorer FREE", and it will continue to be enhanced, as it has been in this release.

PRO Features

Plan Explorer PRO is the result of many months of building out several of those high effort/high value features you've been asking for. Initially we've targeted the features that we believe will have the greatest impact on efficiency and information sharing. Here are the top 5 major features in PRO:

1) Multi-tab Interface

This is just as it sounds. You no longer need to launch a new version of the app for each query tuning session, you just open a new tab!

2) Session History Tracking

Multi-tab is great, but after using PRO daily for some time now, I cannot understate the value of history tracking. PRO tracks every new plan that's generated and auto-assigns it a version. With a single click you can go back to any version of a query to see how well your T-SQL optimizations or indexing changes are working:

You can easily add comments to each version via the dropdown or Comments pane. You also have the option of tracking only when the query text changes, which can reduce the number of versions created. I prefer to keep full tracking on, since generally when I generate a new plan and haven't changed the query text it's because I've made an indexing change, and I want to see the impact. I know I can always delete unwanted versions by right-clicking and selecting Delete.

You can save a query tuning session as a .pesession file for archiving or sharing. For example, if you're a consultant doing query tuning work, you can send the client the .pesession file to show all of the work you did to improve query performance, step-by-step. I've found that this is great for educational purposes as well, as I'm often trying to teach someone how to use Plan Explorer... now they're able to use .pesession files as a reference for future query optimizations.

History tracking not only saves the last statement selected in each version, but also saves the exact position and zoom level of the plan diagram for each statement! This is extremely helpful when you're dealing with large plans that require scrolling to a specific point – you can maintain that position and flip back and forth between versions for comparison, without any scrolling/zooming. All of this information is persisted in .pesession files.

Plan Explorer FREE is able to open .pesession files, but it can only view one plan version at a time, and will prompt you to select one upon opening.

3) Wait Statistics

For SQL Server 2008 and above, when generating an actual plan you'll see a new Wait Stats tab appear with the cumulative waits for that execution:

There is a lot of information out there on wait stats analysis, including this great post by Paul Randal (b|t), so I'm not going to delve further into that here.

4) Full Query Call Stack

This has been one of the true power features in SQL Sentry Performance Advisor for some time – I blogged about it here. Now it's available in Plan Explorer PRO at a fraction of the price.

Previously, when generating an actual plan with Plan Explorer the Statements Tree was effectively a flat list of statements, similar to how SSMS does it. In PRO you will see the full hierarchy of all statements with conditional logic and looping, and even statements called via dynamic SQL!

5) Deadlocks

PRO contains another power feature from Performance Advisor: it can open deadlocks (.xdl files). You see the same awesome deadlocks view as in Performance Advisor – an optimized, sequenced deadlock graph synced with a grid with details for each node, including the query text:

This is also great for information sharing – if you're a DBA and use Performance Advisor, you can easily export deadlocks it captures and send them to the development team for analysis, without providing access to the full SQL Sentry Client. All they need is Plan Explorer PRO.

Other Features Shared with Plan Explorer FREE

There are quite a few fixes and minor features shared between PRO and FREE, including:

The new Status Bar at bottom which shows various connection details, query compilation and execution times, and actual rows affected.
The Command Text pane is now full screen! If you are a heavy user, you probably know how annoying it was to continually resize this pane as query changes were made.
Persistent connections. This is another big one. Previously, every time you hit the Estimated or Actual Plan buttons you'd be prompted for the connection. Now it works more like SSMS: it only prompts when needed, the connection details are shown in the status bar, and you can change the active connection at any time using Edit > Connection.
New Show Estimated Plan toolbar button. I kid ;-) This function has been there since we added the ability to generate actual plans, it was just well hidden behind a tiny little button... which was a shame since many people missed it, and the ability to toggle between actual and estimated plans is an invaluable feature when query tuning. It's now much more prominent on the toolbar. If you haven't used it before I encourage you to try it, as it can make inaccurate estimates at the operation level really jump out.

As you can see, Plan Explorer FREE is already benefitting from the increased effort we've put into making Plan Explorer truly more of a professional application... versus what we had before, which was effectively a big chunk of code extracted from Performance Advisor, with some "wrapper" code added to make it function as a standalone app.

Setup, Licensing, etc.

Both Plan Explorer FREE and PRO use the same codebase and setup. When you first launch Plan Explorer after install, you'll be prompted whether to go directly into FREE mode, or evaluate PRO for 15 days. This is not a persistent nag screen, you will only see it once after the initial install. However, if you decide to use FREE, you can always go back and start the PRO eval at any time using the Help menu.

For PRO, we've opted against charging annual software maintenance (ASM), and instead charge only a per seat license fee of $295 (volume discounts apply). This covers support, and any incremental releases for that major version. When the major version changes, e.g., when we release Plan Explorer PRO v2, there will be an optional discounted upgrade fee. This keeps things simpler for everyone. We won't be bothering you every year for ASM, and you decide whether or not it's worth it to upgrade. This also helps to ensure that we continue to add high value features to PRO ;-)

Please give the new Plan Explorer a try, and let us know what you think!

Kevin Kline joins the SQL Sentry Team!

2012-07-16T10:53:00.001-04:00

Almost 10 years ago, SQL Sentry software was in its infancy, and was still used exclusively by our hosting business (now Peak 10). I remember clearly bringing the entire hosting team together one afternoon to watch a webcast on query tuning... a webcast put on by none other than Kevin Kline (b|t). At that point Kevin was already one of the most active and visible people in the SQL Server community, but I'd never heard him speak. I recall being impressed by the quality of technical content as well as Kevin's seasoned presentation skills... and this was back when webcasts of this type were not commonplace. Kevin was ahead of the curve. Several people in the room were more networking or hardware-focused, but Kevin was so good at distilling complex subject matter down to easily digestible essentials that even they left with new query tuning skills.

I didn't meet Kevin until a few years later when he came to Charlotte to present at a CSSUG meeting. I must admit I was a little nervous and maybe even a bit star struck going in. (Please don't tell Kevin ;-) I was half expecting someone arrogant, self-absorbed, and possibly even combative given that our companies were direct competitors by then. Boy, was I off the mark! Here was a guy that was friendly, humble and engaging, pretty much the opposite of what I'd anticipated... no sign whatsoever of any of the trappings common to years in the spotlight. (I'm sure that those of you that know Kevin are chuckling at my severe misreckoning)

When reading Kevin's bio, I can't help but feel humbled by the magnitude of his selfless efforts in the SQL Server community over the years. For a recap, I'd recommend checking out this SQLMag interview. If reading it makes you ask yourself, "What have I done lately to give back?", you are not alone. Kevin is one of the good guys, and we are extremely fortunate to have him as part of our team.

Not to worry, you won't be seeing any less of Kevin. I expect him to be just as engaged in the SQL Server community as he's always been, perhaps more so. Our goal is to tap into Kevin's unique abilities to the greatest extent possible, and enable him to get back to his roots in some respects. He's certainly going to be involved in some exciting community-focused initiatives here at SQL Sentry.

It's tempting to draw correlations with LeBron's move to the Heat – the initial shock felt by many, and the eventual benefit to both the individual and the team – but I will refrain ;-)

One thing is sure: although the uniform will be changing, it'll be the same Kevin you know and love underneath.

SQL Sentry v7 goes Gold!

2012-05-03T08:56:00.001-04:00

After what has been a relatively smooth beta, we're going gold with v7 today! Read more about v7 features in my last post. I've also got some exciting news regarding new fragmentation-related features included in the base software, and how to get FREE licenses of both Performance Advisor and Fragmentation Manager.

New Base Software Features

Originally Fragmentation Manager was designed to be either ON or OFF, there was no in between. When it was OFF for a server, no table and index size or fragmentation-related data whatsoever was made available inside either Performance Advisor or Event Manager.

We've changed this, in a big way. A significant portion of the new features previously found only in Fragmentation Manager are now ON by default, whether or not you've purchased a license for it. Here's what you now get out of the box with v7, for both new installs and upgrades:

New "Indexes" tab in Performance Advisor, with table/index size and other details
Scheduled and manual fragmentation analysis with adjustable scan mode, from the SQL Server instance down to partition level
Table/index size and other details on the Performance Advisor Disk Space tab, organized by data file
Calendar display of all historical and future fragmentation analysis events, with drag-and-drop support
New "Databases" node for each SQL Server instance in the Navigator pane, listing all table and indexes
Partitioning sliding window support for fragmentation analysis (defrag only max partition, or all others)

These are some significant capabilities which should add value for most any environment.

If you purchase and enable Fragmentation Manager, here are the additional features that become available:

Automated Defragmentation
- Scheduled and manual rebuilds & reorgs, from the SQL Server instance down to partition level
- Support for multiple concurrent defrag operations, which can dramatically reduce overall defrag time
- Adjustable rebuild/reorg thresholds, scan mode and many other options
- Post-defrag analysis capability, so you can instantly see gains from defrag
Calendar display of all historical and future defrag events, with drag-and-drop support
Alerting for defrag success, failure, and completion
Partitioning sliding window support for defrag (defrag only max partition, or all others)
Historical trending via multiple charts on the Indexes tab:
- Total server fragmentation, by fragmented % range
- Total server disk space used, and wasted by fragmentation
- Total server buffer space used, and wasted by fragmentation
- Index fragmentation
- Index disk/buffer space used/wasted
- Index activity

In a nutshell, the base v7 software provides dramatically increased visibility into table/index size and fragmentation information, and Fragmentation Manager gets you automated defragmentation and many associated options, as well as historical trending.

FREE Licenses of Performance Advisor and Fragmentation Manager!

If the above included features weren't enough, we've introduced 2 new ways for you to get FREE software licenses:

If you own five (5) licenses of Performance Advisor and five (5) licenses of Event Manager, you will automatically get:
- One (1) Performance Advisor license, for monitoring the SQL Server instance where your SQLSentry database is located ($1495 value)
- One (1) Fragmentation Manager license, for defragmenting the SQL Server instance where your SQLSentry database is located ($795 value)
Regardless of the number of licenses you own, if you monitor the SQL Server instance where your SQLSentry database is located with a paid license of Performance Advisor, you will automatically get:
- One (1) Fragmentation Manager license, for defragmenting this instance only ($795 value)

Some important points:

These free licenses won't show up in your license counts, they are just "there" ;-)
If you meet the criteria for #1 above and are already monitoring the SQL Server instance holding your SQLSentry database with a paid Performance Advisor license, then you're effectively getting a 6th PA license for free, which you can use to monitor any server.
There are no ASM (annual software maintenance) costs for these free licenses, and they are perpetual.

Thanks again to all who participated in the beta, and we hope you like what you see in SQL Sentry v7!

Download SQL Sentry v7 here: New Users | Existing Users

SQL Sentry v7 Beta: First Look

2012-04-10T02:35:00.001-04:00

It's been a while since my last post. Yes, we're still here (as you well know if you follow us on Facebook or Twitter), we've just been heads down since the PASS Summit getting v7 ready to ship. It's been a long road, but we're releasing the public beta today!

v7 represents the culmination of almost a year of effort, and ideas going back much, much further than that. We've completely redone several aspects of the software such as alerting (condition and action) configuration, and we've added some awesome new features like automated defrag, computer groups, and CMS support to boot. Did I mention SQL Server 2012? ;-)

Terminology Changes

We've made some long overdue changes to the SQL Sentry lexicon in the interest of making things clearer, and since I'll be using these new terms I wanted to get this out of the way first:

A Device is now a Computer (pretty sure I just heard a collective HOORAY! – trust me, we had our reasons for devices, but we won't get into that here ;-)
The former Global node is now the Shared Groups node
The SQL Sentry Console is now the SQL Sentry Client
The SQL Sentry Server Service is now the SQL Sentry Monitoring Service

Computer Groups

The first thing existing users will notice when they open the client is the new Shared Groups node at the very top of the Navigator. This node represents your entire SQL Sentry environment organized by Site. It is called "shared" because every SQL Sentry user sees exactly the same view here. The user-specific device registrations and groups (formerly Global) has been moved and renamed to Local Groups to better reflect what they actually are. You can still configure server-specific settings and below here, but not global settings – those are now set at the Shared Groups root node only.

Sites have always been there to enable logical partitioning of servers and monitoring services. For example, if your HQ is in Atlanta, but you have 100 SQL Servers in Miami and 200 SQL Servers in New York, you might install one monitoring service in Miami, and two in New York. You would create a site for each location, and place the monitoring services in the appropriate site so that they only monitor the servers in their location.

In v7, you can now easily apply special alerting rules to the servers in Miami and New York, versus having to touch each server in order to override global alerting settings:

In addition, you can create unlimited nested child groups in each site, and – you guessed it – apply specialized rules to those groups as well. The inheritance works exactly as it always has in SQL Sentry, you start at the highest level (Shared Groups), then override those global settings as needed at lower levels. Previously alerting & setting configuration looked like this:

Global
- Computer
  - SQL Server
    - Object (job, report, etc.)

Now it looks like this:

Shared Groups
- Site
  - [Child Group] [,...n]
    - Computer
      - SQL Server
        
        Object

As you can see, the ability to group servers can dramatically reduce the alerting configuration required for many environments.

Custom Object Groups

Being able to click on a group node in the Navigator and easily change settings for a bunch of servers at once is great, but it's inherently limited by the fact that a computer node can only exist in one group at a time in the navigator. What if you want to have another set of rules for servers that effectively "cuts across" navigator groups? For example, "All QA Servers" in both Miami and New York?

This is easy to do with custom groups. You simply create a new group by double-clicking the Object Groups node in the navigator, add the QA servers to it, then adjust the settings:

Similarly, if you wanted to disable Runtime Threshold alerts for all transaction log backup jobs, you can easily search on the jobs using a name pattern, use Shift + left click to highlight and add several at once, add the Runtime Max condition, then select "Disable".

Automated Defragmentation

Your first thought here may be, "I already have scripts that perform automated defragmentation, why do I need a tool?" Good question! Here are three compelling reasons:

Manageability
Visibility
Defrag Speed

Manageability

There are several great scripts out there that many use to perform automated defrag. They can get the job done, but the main issue is that they are all, well, scripts. Configuring exactly which databases and indexes are defragmented and when can be a challenging and time-consuming task, especially if you are talking about 10s or 100s of SQL Servers. Manual script changes and multiple jobs on each server are typically required.

With our new Fragmentation Manager module, just like everything else in SQL Sentry, you can start at the top and work your way down. For example, if you have 20 SQL Servers, you can set a default global defrag schedule of 2am for all servers at once by enabling it at the Shared Groups level:

So in 30 seconds or less, you've configured enterprise-wide defrag!

DISCLAIMER: I am NOT recommending that you do this, as every environment is different, and you'd of course want to disable any existing defrag jobs first. I'm just letting you know what is possible ;-)

Typically you'll want to enable Fragmentation Manager at the SQL Server instance level by right-clicking the instance in the navigator pane, or clicking the Enable button on the new Indexes tab inside Performance Advisor.

Once you've enabled one or more defrag schedules, if you view the "Defragmentation Schedule" sample event view, or the calendars for any of those servers, you'll see defrag instances show up alongside other events:

You can of course drag-and-drop to move them. But what if you have a 100GB index on one of the servers that really needs to be analyzed and defragged separately? You simply select the index and override the inherited schedule:

It's that easy. Everything is point-and-click, and since the SQL Sentry monitoring service manages all of the defrag tasks, there are no scripts or jobs required.

Visibility

Once you've enabled the Fragmentation Management Module on a SQL Server, you'll see a new Fragmentation tab appear inside Performance Advisor:

This tab has tons of good information about your indexes, including 6 charts showing disk and buffer space, used and wasted, both total and at the index level. The purpose of this tab is not only let you know the state of fragmentation on a server, but help you make good decisions about how and when to defrag your indexes, adjust fill factors, or even change index definitions. One of the coolest charts on this tab is Index Space Usage (center bottom) – it shows you exactly how much of an index is on disk and in buffer over time, and how much disk and buffer space is wasted due to non-full pages.

There are also 3 new alerting conditions: Defrag Started, Completed, and Failure, so you can be as informed as you want to be regarding the status of your SQL Sentry defrag operations.

Speed

No, we haven't invented some magical new higher performance technology for analyzing and defragging your indexes... however, we have come up with a unique approach for potentially dramatically speeding up your regular defrag process, thereby reducing the maintenance window required for defrag – by allowing more than one concurrent defrag operation:

If your disk system can handle it, why not run multiple analysis or defrag tasks in tandem? Most systems we've tested have no problem running 2 or 3 concurrent defrag ops, especially when indexes are split across multiple data files and disks. An op can be an analysis, reorg, or rebuild. Currently this setting is capped at 5 for safety. I recommend starting with 2 concurrent ops on a test server, and see how it performs. With the Performance Advisor dashboard and Disk Activity views, you can easily assess the performance impact of increasing the concurrent defrag ops.

Alerting Enhancements

In addition to group-based alerting configuration, many other major improvements have been made in the area of alerting:

You can now configure multiple actions of the same type for the same condition! For example, you can have 3 different Send Email actions for the Job Failure condition, each with different alert targets (users or groups), different rulesets, and different alert windows.
What's this, "windows"? Yes, that's right, you can now set exactly when contacts should be alerted using configurable ranges of time, for example "Business Hours" or "Weekends". You can even create compound windows which combine multiple windows together.
We no longer list all conditions by default, only those that are in effect. This can dramatically reduce the noise when viewing and configuring alerts.
Inherited conditions/actions are displayed in one pane, and conditions/actions set at the current level are in another (Explicit).
Since there can now be multiple levels of inheritance with groups, we show you exactly where the inherited settings are coming from via the Object column.
You can choose to Disable, Override, or Combine with an inherited condition action. Combine works just as it sounds – you can set the same action again at the current level, but leave the inherited action in effect.

Together, I think you'll find that these changes make for the most flexible and robust alerting system we've ever had.

Performance Advisor Dashboard Enhancements

Aside from various cosmetic improvements, the two primary new features on the dashboard are NUMA support and mirroring queue monitoring. When monitoring a NUMA system, you'll notice that both the Windows and SQL Server memory charts are now split to show exactly how much memory is allocated to and used by each NUMA node. In addition, page life expectancy history is also shown for each node. When monitoring a system acting as a primary, mirror, or both, the Send and/or Redo Queues are shown on the same chart previously used to show backup/restore activity.

Beta Download

For a full list of all changes in v7 click here. I've really only scratched the surface. Please take the beta for a spin, and let us know what you think – we want your feedback!

New Users
Existing Users

As always, upgrading your existing SQL Sentry environment to the beta, and from the beta to v7 RTM is fully supported. Be sure to take a backup of your current SQL Sentry database first. Rolling back for any reason is easy – uninstall the beta, restore the database backup, then reinstall the previous version and point it to the database. No settings will be lost.

SQL Sentry v6.2: SharePoint Timer Jobs, Oh My!

2011-10-07T17:40:00.001-04:00

We recently released SQL Sentry v6.2, and it introduces an exciting new product: Event Manager for SharePoint. v6.2 contains several other new features as well, including SSAS usage metrics, performance dashboard event overlay, enhanced console security, and a streamlined setup process. SharePoint support and SSAS usage in particular have been heavily requested, so this release should make quite a few customers very happy!

SharePoint Timer Job Monitoring

If you already use SharePoint, you may be familiar with the concept of timer jobs. SharePoint 2010 has its own scheduler service that carries out various background tasks on an ongoing basis – over 100 timer jobs out of the box, ranging from health checks to data imports to history cleanup. These jobs can run on any server in the farm, and they can access resources on other servers. Most of the jobs are set to run on predefined recurring schedules, and the net result is a significant amount of timer job activity going on pretty much all day and night. Since much of this activity is touching SQL Server, it's critical for the database administrator to have visibility into exactly when the jobs are running, and to be able to ascertain the impact they are having on performance.

Using the Event Manager for SharePoint calendar, here's a shot of a typical SharePoint timer job schedule over two days:

The instances in orange indicate a runtime overlap of at least 20 seconds, so it's apparent that many timer jobs run at the same time – that's right, the dreaded schedule collisions! One of the primary reasons for the existence of the Event Manager calendar is of course to be able to detect and resolve schedule collisions, so even if you've already cleaned up your SQL Agent schedules, you'll be able to put it to good use again for timer jobs ;-)

Here's a shot of the 12am slot with timer jobs only:

We have no less than 20 timer jobs running concurrently at midnight every night. The calendar view can be easily expanded to include other events to see if these jobs are colliding with SQL Agent jobs, for example.

Here's a closer view of the 4pm slot, with SQL events from the timer jobs included. You can distinguish the timer jobs from the SQL events at a glance by the small glyphs in the upper left of each instance:

I know, this is pretty ugly, so I'll apply a quick filter to show only events with duration >3 seconds:

Again, these are all out of the box jobs and schedules! In multi-server farms, not all jobs may be running on the same server, so the server on which a job ran is shown in its tooltip along with many other details.

Timer job schedules can work quite differently than SQL Agent, Windows Scheduler, or other schedulers you may have seen. They can use the concept of "ranges" – you set possible start and end times, and the job can run any time during that range. Supposedly the randomness will help avoid job collisions... but note the "SharePoint BI Maintenance" job in the middle of this shot with odd start time of "12:00:40 PM":

Yep, you guessed it, it's using a range! If I right-click the job and select Properties, I'm launched into the job schedule in the SharePoint Central Administration web interface:

We can see that this job is set to run hourly, at any time during the hour. So the randomness didn't help much here – it ended up running concurrently with 4 other jobs. Personally, I like to control exactly when my jobs run and not to leave that up to chance, so I changed both range values to 30, which effectively tells the job to run only at 30 minutes past the hour. (NOTE: You need to be setup as a farm administrator to access the schedule properties.)

Can timer jobs impact SQL Server performance? You'd better believe it! I'll demonstrate using another new feature, Dashboard Event Overlay (more on this below). By right-clicking a longer running timer job and selecting Jump To > Performance Advisor > Dashboard...

...I'm taken right into the Performance Advisor dashboard where the event duration is overlaid on the X-axis of each chart:

We can see that there is a strong correlation with this job and several metrics, including:

Network inbound
CPU
Windows hard faults
Disk latency
Transactions and batches
Bookmark lookups
Disk waits
Log flushes

Since this particular job runs every 30 minutes, the real question becomes, how will it impact user activity against SharePoint databases on the same server throughout the day? More on this in a future post.

SSAS Usage Totals

The next major new feature is the SSAS Usage Totals tab, which is part of Performance Advisor for SSAS:

Previously, SSAS usage information was only available at the query level, and you were unable to see the actual attribute members requested. This made it difficult to get a server-wide perspective on data access patterns. You can now see this server-level data aggregated three ways – by attribute, aggregation, or partition – over any date range. If you spot a particular attribute combination that is heavily requested by many queries, you can right-click the row and copy the attribute vector to the clipboard, then paste it into Agg Manager to create the aggregation. (Look for this capability to be fully integrated in a future release.)

Enhanced Console Security

A common need, especially in larger, more segmented environments, is the ability to restrict visibility and access to certain servers to specific users. Previously, in the SQL Sentry Console you could use groups to effectively hide servers from unauthorized users, and we've always leveraged native security to ensure that there are no escalation of privileges risks – just because someone could "see" a server didn't mean they could do anything with it. Although this was fine for many environments, in others, unauthorized users aren't even allowed to know that certain servers exist! This was a problem.

Hence the need for this new capability, which allows a SQL Sentry administrator to truly restrict SQL Sentry users to specific SQL Servers via their Windows login:

Once associated, you simply select the servers that the user can access:

For existing environments, nothing changes automatically when upgrading to v6.2. However, once a user has been associated with at least one SQL Server, from that point forward they can no longer access any other SQL Servers monitored by SQL Sentry in any way. This goes for any new servers that may be added later – the user must be explicitly associated with those servers before they are accessible via the console.

Dashboard Event Overlay

A powerful new feature is the ability to overlay events across the Performance Advisor dashboard charts (see screenshots above). Previously, you might know that a SQL statement ran between X time and Y time, and you suspected that it was impacting performance, but when you flipped over to the dashboard there were no visual cues as to exactly when the event ran – you had to perform the correlation in your head. Now, if you use the Jump to Dashboard context menu from either the Event Manager calendar or the Top SQL tab, you'll notice new horizontal indicators on the X-axes representing the selected event(s). When coming from the calendar, any event highlighted using the new enhanced highlighting options will have a corresponding indicator on the dashboard. This means that you can easily see what impact a particular user, application, or even SPID, is having on performance!

Streamlined Setup

Last but not least, we took a hard look at the installation process and initial setup wizard. Several screens were eliminated and/or consolidated, and the process was simplified overall. In addition, we've added a new Start Page which gives you quick access to some of the more commonly used functions, every time you launch the console:

We'll be adding more to this page as time goes on.

Take v6.2 for a spin and let us know what you think: new users | current users. I'm at the PASS Summit this week, so stop by for an in person demonstration, as well as a sneak peak at some exciting new features coming in v6.5!

The 2013 PASS Summit in Charlotte – Good Call!

2011-05-18T16:32:00.001-04:00

We woke up today to the great news that the Summit is coming to Charlotte in 2013! This is something that we'd hoped would happen eventually, but with all of the back and forth on this topic over the past couple of years, we honestly weren't holding our collective breath. Although Charlotte appeared to be on pretty much everyone's Top 10 list of possible future Summit locations, for good reason, there just didn't seem to be any kind of critical mass. There is a huge group that would be happy if the Summit never left Seattle... and, like us, pretty much everyone else wants the Summit in their city. ;-)

As a SQL Server ISV based in the Charlotte area, we've advocated bringing the Summit to Charlotte at times, whenever folks would listen... but we aren't the loudest bunch, and we're also not ones to get too wrapped up in the political side of things. We keep a fairly low profile, we'll state our case and leave it at that... we're just not going to try too hard to sway anyone in any particular direction, for better or worse.

That said, our partner liaison, Peter Shire (b|t), has worked tirelessly for several years on the Charlotte PASS Chapter to foster a vibrant SQL Server community here, with the help of our local Microsoft offices. (The #2 SQL Server support operation behind Dallas is located here in Charlotte.) In addition, last year volunteers from SQL Sentry and Microsoft helped to pull off a highly successful SQLSaturday event, which I'd like to think helped to put us on the map. It did appear to leave many with a positive impression of our city and its ability to support something like a Summit.

Ultimately, the folks on the PASS Board performed the necessary diligence to find the best possible location, and looking at the criteria board members like Tom LaRock used, they made the right call. Every city has its pros and cons, and although Charlotte may not be the strongest in all areas, overall it's just a tough city to beat – I'd call it "well-rounded." I've lived here since the early '80s, and I've spent time in most major cities around the U.S. and Europe, and without a doubt, Charlotte holds its own against any of them.

As many will find out, Charlotte is not only a great city, it's truly an optimal location to hold a PASS Summit. Will there be bumps in the road? Of course – it's not going to be perfect. There will be gripes about various things, there always are. But on balance I think it's the right move. Bring the Summit to a well-rounded East coast location with a strong Microsoft presence, and expose many database professionals that won't normally make it to Seattle to this great educational and community event.

For me, 2013 can't come soon enough!

Don't Fear the Trace

2011-04-26T11:20:00.001-04:00

Not a week goes by that I don't run across either a comparison of server-side (file) and Profiler (rowset) trace performance, with rowset ending up with the short end of the stick; and/or someone who's afraid (or not allowed) to run traces against a production server because it will undoubtedly kill SQL Server performance. The amount of misinformation out there on this topic is mind-boggling – IMO it's one of the most misrepresented aspects of SQL Server. Unfortunately, it seems many have taken the misinformation at face value, and have perpetuated it without performing sufficient diligence.

There are a some key points that always should be, but rarely seem to be, considered when discussing the performance impact of tracing:

Disk speed matters for server-side traces. File traces are "lossless", so can and will block SQL Server activity if the disk where the trace file is being written is not keeping up. A nice overview by Adam Machanic (b|t) is here: http://technet.microsoft.com/en-us/library/cc293610.aspx. This means that if you are writing too many events to a busy or otherwise slow disk, it can bring SQL Server to a screeching halt. This is easy to repro – try writing a heavy file trace to a USB thumb drive. This may sound extreme, but it's probably not far off many SQL Server disk systems I see where busy data and log files are mixed on the same set of spindles, causing severe contention.
Rowset traces will drop events to avoid stopping SQL Server activity. The longest a rowset trace will wait before dropping events is 20 seconds. This is less than the commonly used application query timeout of 30 seconds. This can be a bit harder to repro with Profiler, but we've done it in our lab by creating a .NET-based rowset trace consumer with artificial delays in code to slow trace event consumption. In this sense, rowset traces can be considered "safer" than file traces, since although they can slow SQL Server, they won't stop it completely.
Filters matter, for both trace types. Although the above points are important to understand, they only come into play in extreme scenarios that most of us should never see. You should almost never need to run an unfiltered trace, or use "Starting" or other events that aren't filterable. For example, if you use only RPC:Completed and SQL:BatchCompleted events and restrict the rows returned using selective filter thresholds against integer-based columns like Duration, CPU, etc., and avoid text filters, especially with wildcards, you can minimize the impact to performance of either trace type.
Profiler <> Rowset. Profiler uses a rowset trace, and so frequently when Profiler is knocked, rowset is condemned by extension. They are not one and the same. SQL Sentry Performance Advisor (and some other tools) also use a rowset trace. We stream the trace binary data back and decode it, as described at the bottom of this page: http://technet.microsoft.com/en-us/library/cc293613.aspx. While Profiler has a relatively heavy interface for working with trace data, our trace server, a Windows service, does not. It uses a highly optimized multi-threaded scheme for consuming trace rows as quickly as possible, and efficiently storing the trace data for later retrieval. So although the network overhead may be roughly equivalent for the same trace run by Profiler (remotely) or our trace server, that's where any similarity ends. If the trace uses smart filtering, the overhead should be a non-issue anyway – by default we use a conservative filter of Duration>=5000ms, with a floor of 100ms for safety if no other CPU or I/O filters have been applied.

A Simple Test

We recently conducted some cursory tests, primarily to confirm the blocking/non-blocking behavior described above. We simulated high activity via a lightweight T-SQL statement in a 1 million cycle loop, and used a trace with only "Completed" events and no filters. The goal was to generate 1 million trace events as quickly as possible, and assess the impact consuming those events by each trace type had on the total load run time. I want to emphasize that this is an isolated test scenario, and the results should not be over-generalized. We plan on conducting much more comprehensive testing in the near future. Nonetheless, the results are interesting:

Both server-side and rowset traces always caused the load to run slower than when there were no traces.
A server-side trace writing to slow local disk caused the load to run much slower than a rowset trace consumed locally.
A server-side trace writing to a fast local disk caused the load to run slightly faster than a rowset trace being consumed remotely over a 1000Mbps network.
After factoring in the additional overhead required to copy the trace file over the network and load it into Profiler, the overall time for the server-side trace from the last test was greater.

It seems that with remote tracing, you can pay me now with rowset, or pay me later with server-side. Although the results were repeatable, I'm not publishing test details at this point because these tests were not strictly controlled. The point here is that trace impact on performance is always an "it depends" scenario. Unfortunately it rarely seems to be presented as such – blanket statements such as "Profiler is dangerous" and "server-side traces are always faster" are commonplace. Sure, consumer type and location, network speed, etc., can certainly affect the performance impact of rowset traces, but so can writing a server-side trace file to the same spindles as a busy transaction log... and the impact can potentially be much more detrimental to your SQL Server.

The Source

Many thousands of SQL Servers are monitored 24/7 by our rowset-based trace server and we haven't had a single report where its impact on the target was even noticeable, so we're always puzzled by the anti-rowset sentiments. I've tried to assess how the confusion and fear started, and much of it seems to have a common source: the "Standard" Profiler trace:

It's been with us forever, and I'm not sure who created it as the default trace... but best case it is of little to no value, and worst case it is dangerous to your SQL Server. Presenting this trace as the default to a SQL Server newcomer is analogous to putting a loaded weapon in front of a toddler. There are no filters applied, and it includes Audit Login and SQL:BatchStarting events, neither of which are filterable by any performance metrics (Duration, CPU, Reads, Writes), meaning you will be capturing all of them. It also includes Audit Logout, which is effectively not filterable by Reads or Writes when connection pooling is in use, since those metrics are cumulative. On a busy OLTP system this trace is bad, very bad!

So unless you've changed the default trace (easy to do via Edit Template), every time you open Profiler you've got a very high overhead trace as the starting point. As a result, this trace is referenced in books, trace performance benchmarks have been conducted and published using it, and countless users have naively run it as-is against busy servers. I'm convinced that this is a big reason that Profiler is so frequently kicked around, and why so many are now afraid to use it at all.

Bottom line: Don't use this trace! Certainly don't use it alone for benchmarking the relative performance impact of the two trace types.

A true comparative test should consider:

Events and columns used
Trace consumer location (local or remote)
Disk location (local or remote)
Disk speed and type (shared or dedicated)
Network speed
Load type (T-SQL text size, DML mix, etc.)
Transactions/Batches per second on the target
CPU and memory resources available
Filter types
Filter thresholds
...

As I mentioned, we have more comprehensive testing coming which covers these factors, and we'll publish all of the details, with hopes of clearing up a lot of confusion and fear around tracing once and for all.

In the meantime, don't fear the trace. If you target the specific events and columns you need, and use conservative integer-based filters to restrict the rows returned, the benefits gained through intelligent analysis of the data should far outweigh any performance impact on your SQL Servers.

SQL Sentry v6.1: Something Old, Several Things New

2011-04-08T16:03:00.001-04:00

Since releasing v6 a couple of months ago, we've been hard at work on some cool new features for v6.1. Our x.1 releases are usually comprised mostly of fixes for bugs not caught during the major release (x.0) beta, and a few new features. This x.1 release is different in that contains many exciting and high impact new features, and a higher feature:fix ratio than normal. This is likely due to the lengthy v6 beta, as well as some new QA processes we've put in place. Change list is here.

We've just published the v6.1 Release Candidate to the website (new users|current users), and our team is showing it off now at SQLBits 8 in Brighton, UK. Read on for an overview...

SNMP Support

I'm covering this one first, as it's been on the feature list the longest. Believe it or not, we obtained our SNMP "enterprise number" and started on our MIBs in 2004, not long after the original release of Event Manager v1! Our enterprise number is 20707, and the latest enterprise number issued as of this post was... gulp... 37722. (I think I'll filter this feature from our "average turnaround time" calculation ;-) We didn't actually start coding SNMP support until recently though – for a few reasons it kept getting prioritized down: high complexity, relatively low demand, and the fact that users have been able to work around it and get alerts into other monitoring systems via our "execute" actions (Execute Process and Execute SQL). SNMP is not a feature that everyone uses, but those that do really depend on it.

Fortunately, earlier this year, the most knowledgeable and capable SNMP developer on the planet, Eric Eicke (b|t), joined our development team. Eric developed the first .NET-based SNMP component several years ago, and it's used by many large organizations, including Microsoft and HP. Eric was also the one that helped us get started on SNMP back in 2004. There was no fumbling around with RFC's, Eric knew exactly what needed to be done and did it, including v3 support, authentication, and encryption!

MIBs are installed as part of the new setup. Once you've loaded them into your enterprise monitoring system, activating SNMP in SQL Sentry is straightforward from the new SNMP Config tab:

Once configured, simply tick the Send SNMP Trap action for any condition (along with a ruleset if desired):

Qure Integration

This next feature is not quite as old, but is equally as exciting ;-) Performance Advisor has always had "Top SQL" collection and normalization/aggregation, and this year we became the first to introduce integrated query plan capture and analysis. So we automatically catch your heaviest queries, and give you best-in-class features for making manual query analysis as quick and easy as possible. If your server has 5 or 10 "problem" queries, and you've got some experience dealing with execution plans and indexes, no problem, you can use Performance Advisor to straighten everything out in an afternoon. But what if you're dealing with 20, 50, 100, or more problem queries? Or what if it's not the individual query that's the problem, but rather the cumulative impact?

For many systems, automated workload-level analysis may make more sense. That's where Qure comes in. Developed by DBSophic, Qure is the brainchild of MVP Ami Levin, and is the leading software available for SQL Server workload analysis. It uses a copy of your production database and makes index, schema, and/or query changes, then tests those changes with real workloads to see which work best, before making any recommendations. Of course, we all trust the query optimizer to behave predictably and make good decisions <ahem>, but there's just no substitute for good, old-fashioned brute force validation.

SQL Sentry and Qure provide complementary functionality with very little overlap, so bringing the two together made a lot of sense for both companies. As such, DBSophic has just released a new version of Qure which only works with trace data from the SQL Sentry repository database (v6 or above), Qure for SQL Sentry:

Here are my top 3 reasons to try Qure for SQL Sentry:

Faster Analysis. With the new special edition of Qure, you don't have to deal with manual workload traces. You simply point Qure to your existing SQL Sentry repository database, select the SQL Server and database to optimize, and hit "Go". Qure automatically pulls in all Top SQL trace and QuickTrace events previously captured by SQL Sentry, and uses it for the analysis.
Easier Validation. With the Performance Advisor dashboard's history mode, the Performance Counter Date Range Comparison report, query runtime graphs, and query plan history, with SQL Sentry you can easily validate the results of a Qure optimization from several angles.
Cost-effective. The new combined sku, Power Suite + Qure Quick Start Pack, gives you 5 perpetual Performance Advisor and Event Manager licenses plus 5 full Qure optimizations, all at a significant discount over purchasing the products separately. If you're already a SQL Sentry customer, you can purchase the new Qure edition separately as well.

You can run an unlimited number of sample analyses with the Qure for SQL Sentry trial software, and it will give you some good, actionable recommendations for FREE.

"Too Much Data" Begone!

Longtime Event Manager users will really appreciate this feature. Prior to this release, if you had too many event instances in a range to render them on the calendar in a meaningful fashion, we would show a block like this for the entire range:

When we introduced Performance Advisor and began showing performance events on the calendar, Top SQL in particular, these blocks became much more prevalent, and could really hamper visibility as well as increase time spent on filters.

Now, instead of a big "too much data" block, you'll see a small hatched rectangle on the far right of the range:

When you hover over it the entire range is highlighted, and a tooltip is displayed which shows how many other events exist in the range. The events actually displayed in the range have been prioritized by status and runtime – failures and longer running events are shown first, the rest are filtered from view. Just like before, you can double-click to zoom into a smaller range and see the other events.

Custom Calendar Highlighting

Previously, when you selected an event instance on the calendar, we would highlight other related instances using basic logic. For jobs and tasks we used the server and job name, and for Top SQL we used the "Text Data". So other Top SQL instances would only highlight if the SQL matched exactly. Depending on the scenario, this could be very limiting.

Now, you can pick and choose exactly which attribute or combination of attributes to use for highlighting related instances. Here are some examples:

You want to see all SQL, blocks and deadlocks for a particular Application
You want to see all SQL, blocks and deadlocks associated with a SQLAgent job
You want to see all SQLAgent jobs that are part of the same chain

All of these scenarios and more are possible. You simply right-click a calendar event and select the common attribute(s) using the new Highlight context menus:

Tab Undocking / Multi-monitor Support

You now have the ability to drag any SQL Sentry main level tab outside of the Console. This is especially nice for multiple monitor setups. This shot shows a main tab being undocked:

Fusion-io Drive Support

SSDs are definitely on the upswing in the SQL Server world. Over the past couple of years we've seen much greater acceptance and adoption in mission critical SQL Server environments, and the company that's making the most waves here is Fusion-io. Previously, none of their drives would show up in our Disk Activity view, however, due to their PCI-based architecture and the fact that they're represented differently in the Windows subsystems we use to gather drive data.

We've addressed this in v6.1, so if you have any Fusion-io drives, single or duo, they'll be rendered on Disk Activity just like all other drive types. This gives you a great way to validate their performance – latency on these drives should be low enough that the flow lines are always green. If they are not, there is a problem.

Here's a shot of a high volume OLTP system in the U.K. with lots of database files, running on a Fusion-io ioDrive and ioDrive Duo card with Windows RAID-0, courtesy of Christian Bolton (b|t) of Coeo:

New Plan Explorer Features

The new version of Plan Explorer is 1.2, and the features I'll cover below apply to both the full SQL Sentry v6.1 and Plan Explorer v1.2. The codebase is the same between them, but there are additional capabilities that open up when you use the full software, as covered in my post on SQL Sentry v6 Plan Analysis Features.

Actual Plan Retrieval

Previously you could retrieve the estimated plan from a server, but to view an actual plan you had to run the query in SSMS and copy the plan into Plan Explorer. Now you'll see an Actual Plan toolbar button that allows you to retrieve the actual plan for any query:

Just like SSMS, the query must be executed against the target in order to get the actual plan. You will see a progress bar while the query is running, but you won't see any query results when it completes, only the actual plan info along with actual CPU and read IO metrics.

Note the new Command Text tab above. This is an editable view of the query text, and this is what gets executed. The Text Data tab is no longer editable – that SQL comes directly from the plan, and there were several problems with making it executable that are beyond the scope of this post (and now water under the bridge ;-) I think you'll find the new design much simpler, and more robust.

We've had some lively discussion on whether or not to go down the path of showing results, but where we ended up is that Plan Explorer is a plan analysis tool, not a query tool. More often than not, viewing results isn't required to make good plan optimization decisions. We of course already show the number of estimated and actual rows, and in most cases this is sufficient.

One big advantage of not returning results is that you'll often get the actual plan back much faster than you would otherwise. For example, a query that returns 200,000 wide rows and takes 30 seconds to load in SSMS may take only 2-3 seconds in Plan Explorer.

Expressions Tab

Prior to this release, we've shown expressions only in operator tooltips. The higher you go up the plan tree, the more levels of nesting you can have, and tracing an expression all the way back to its source using tooltips could be daunting.

This is why we've added a new dedicated tab for expressions. The tab only appears if expressions exist in the plan, and it shows standard and expanded views of all expressions, along with the entire references tree for each:

I should warn you, there is a lot going on behind the scenes with expressions... the optimizer generates many expressions that you'd never normally see, and it can cause overload at first. More on this in a future post.

Join Diagram Tab

This tab only appears if the query has joins. It presents a view similar to the Query Designer diagram in SSMS, although only joined columns are shown for each table. Join information exists on the Plan Diagram and Query Columns tabs, but this is a different look at it which can be especially valuable if your query has views or nested views. The query optimizer flattens all views down to their base tables as part of the plan generation process, but in SSMS you see joins for the views, not the base tables. This can make it difficult to decipher which tables and columns are actually involved in a join. Fortunately, the plan contains this data, so we're able to reconstruct and show the base table joins from the plan XML. This can be very helpful for making good indexing decisions to support those joins.

This is our first cut at joins, and getting it to this point has not been trivial. What looks like a simple join on the plan diagram can be oh so much more behind the scenes. We're still finding scenarios that we haven't seen before, but we think it's far enough along that it can provide value. If you run into anything weird, please do let us know about it – email the plan to support at sqlsentry.net.

It's your feedback that enables us to continue to improve and evolve the software, so please keep it coming!

Reducing Plan Explorer Startup Time with NGen

2011-03-04T17:51:00.001-05:00

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!

SQL Sentry v6 Plan Analysis Features

2011-02-16T16:01:00.001-05:00

Almost a year ago, we embarked on the journey to SQL Sentry v6. This was not long after we had decided that our release cycles were too long, and I had just boldly proclaimed that there would be no more year-long cycles – we were shifting to smaller, incremental releases! They're easier to test, they get new features in the hands of our users faster, they keep up momentum, morale, etc.

All of this is true, but looking back it probably wasn't the right release to make that decision. ;-) At the time, our vision of plan analysis was hooking into SSMS – like we do in other parts of the app – in order to render the graphical plan diagram for statements captured via our "Top SQL" subsystem (a lightweight trace that runs continuously and catches "big hitter" queries). I'm not sure of the exact point at which we ditched that idea in favor of rolling our own plan analysis capability, but I remember it happened very quickly. It was probably right after our lead developer Brooke Philpott (blog|twitter), never one to shy away from a development challenge, threw together a quick prototype showing that we could indeed parse the plan XML and render a graphical tree, among other things. Next thing you know, what would have taken a few weeks took over 10 months!

That said, I'm glad we made the decision to build our own plan analysis system, and would do it again. We've received tremendous feedback since releasing the free Plan Explorer, and we're thrilled that it's making a real difference for SQL Server users.

FREE Plan Explorer vs. Performance Advisor v6

This is something we've been asked about a lot lately. Notice I didn't say "... vs. FULL Plan Explorer". That's because there is no "full" version of Plan Explorer per se; it's all the same code. Rather there is an "integrated" version that resides within the SQL Sentry Console, and it has additional capabilities that open up when you have a SQL Sentry Server service and database installed. There are also other plan analysis features included in v6 that exist completely outside of Plan Explorer.

You may have noticed that there are some grayed out columns in the free Plan Explorer statements tree, and when you hover over them you get this message:

No, this isn't some cheap attempt by marketing to lure you in and then force you to buy the full software. Rather, this is the development team telling you that what you are trying to do isn't technically possible... unless you have a SQL Sentry service and database. Which, if you did, would mean that Plan Explorer was no longer a tiny download with simple installation and minimal footprint. Sure, we want you to see what you're missing in the full software, but it was easier and less invasive to just gray columns and/or throw up a message box than add new code paths to selectively show/hide features.

When we unbolted Plan Explorer from the full software, anywhere there was something we just couldn't do, we added this note. Another place you'll see this is if you try to create or edit an index, since that would require SSMS integration, as well as the ability to determine the location of the server and database in question (since they aren't stored in a plan), and then authenticate. This would mean additional development and testing, new external dependencies, and opportunities for something to break.

As an example of what we deal with when integrating with SSMS, in SQL Server 2008 R2 some of our code – which worked perfectly in 2008 – suddenly ceased to function, but only if the SSMS Object Explorer was unpinned!? It took us a while to figure that one out. So index management is something technically we could pull off, and we may add to the free Plan Explorer at some point, but it's honestly not at the top of the list.

Active Plan Capture

When you install SQL Sentry v6, you gain what we call active plan capture, and all of the new plan analysis features depend on this. The Server service continuously monitors running queries and completed trace events and, when appropriate, queries the plan cache for plan XML and/or other plan details, and automatically assembling all of the pieces together. This enables us to do several things that aren't possible with the free Plan Explorer, or any other available software for that matter:

Capture a query's plan at the time of query execution and associate it with the query.
Assemble the entire call stack for a query, including any nested stored procedure and/or nested dynamic SQL calls.
Show statement level metrics from the execution plan side-by-side with trace and DMV metrics.
Keep a history of all plan versions used by a query over time, so you can see where plan changes impact performance.

I'll go into each of these in more detail below. If you want to know more about the graphical plan diagram and other shared Plan Explorer features first, check out this post or Aaron Bertrand's (blog|twitter) Plan Explorer posts.

The Query Plan at Query Execution

With the free Plan Explorer version, it's up to the user to identify problem queries, somehow capture the estimated or actual plans, and then manually load them in Plan Explorer. The full version of Performance Advisor does all of this for you. Heavy queries (batches or RPCs) are continuously picked up by the Top SQL trace as normal, based upon the filter settings you provide ("Duration > 5 seconds" by default), and plan information is immediately captured and associated with the queries. The only thing you need to access the plan is click the View button in the new Plan column:

As you can see, this column exists for both completed and running queries... so yes, you can view the plan while a query is running!

It's important to note that this is the estimated plan at time of query execution, not the actual plan including actual rows, executions and data for each plan operation. It is not currently possible to capture the actual plan continuously in a low overhead fashion. (No, not even with Extended Events ;-) We pretty much knew this going in, but we spent a fair amount of time trying some different approaches to pull it off anyway, to no avail. There is some hope that this will be possible in a future version of SQL Server, however. That said, the estimated plan at query execution is certainly the next best thing, and even has some advantages which I'll delve into in a future post.

I've manually added a couple of columns to the Completed Queries grid above: Has Plan and Has Statements. These are hidden by default, but are handy for quickly identifying batches and RPCs for which we were able to capture a plan and/or statements, which will impact what you see when you click "View." Depending on the type of query, timing, and other issues, it's not always possible to capture the plan. It's also possible to capture a batch or RPC with no statements, if none exceeded the trace filter thresholds (this has always been the case).

A Quick Aside: Other Top SQL Enhancements

Long-time Performance Advisor users will probably notice some other differences in the above Top SQL shot, so I'll touch on them briefly:

The Running Queries pane – we now show you heavy queries (again based upon your filter settings) while they are running, not just after they complete.
The Filter pane is hidden by default.
Statement events (SQL:StmtCompleted or SP:StmtCompleted events) are no longer shown here, only SQL:BatchCompleted and RPC:Completed events. This is because they are now integrated into the Plan Explorer view. More on this later.
Text Data (TSQL, MDX and XMLA) is now colorized.

Some additional changes to Top SQL that aren't apparent above:

Auto-growth and Auto-shrink events are now captured. Both can be a hidden performance killer and can bring activity to a screeching halt while file space is adjusted.
New columns have been added:
- Error - shows the general reason a query terminated prematurely. Rows with errors are also highlighted with a light red background.
- Information - shows either "rows affected" or "auto-growth size" depending on the event.
Blocked spids are shown with a light orange background in the Running Queries pane.

The Query Call Stack

A Profiler trace presents a stream of trace events, and there is little to no sense of how the events are related to one another. Throw in nested procedure calls and dynamic SQL, and all bets are off.

This is why one of the most significant features gained with the full software is the query call stack assembled in real time. This lets you see exactly how captured statements are related and where they fit into the query plan. Most importantly, it shows you which statements did the bulk of the work, and which are candidates for optimization.

Below is the Statements Tree pane for a stored procedure that calls some dynamic SQL via sp_executesql, which calls another stored procedure, which proceeds to call more dynamic SQL:

Plan Explorer Statements Tree – Fully Assembled Stack (Performance Advisor v6)

I've highlighted the dynamic SQL calls in yellow, the nested procedure calls in light blue, and the final dynamic SQL generated in pink. Note that the parent procedure itself in this case is effectively glue; it's the dynamic SQL that is doing the bulk of the work. If you were to look at the estimated plan in Plan Explorer, you'd see this:

Plan Explorer Statements Tree - Estimated Plan (free version)

If you click to enlarge you'll see the initial dynamic SQL call in yellow at the very bottom. That's the same line that makes the three initial dynamic SQL calls in the full stack shot above! Problem is, the compiled estimated plan has no visibility into what that dynamic SQL will actually look like, let alone how it will perform.

A big difference between the two views above is that the call stack shot is in Show Captured Statements Only mode, which is the default whenever we have at least one statement for a batch or RPC. In this mode you see only statements that exceeded the trace filters along with any relevant control structures, in this case an IF block and a WHILE loop. This mode can be toggled off via context menu item, but for complex procedures like this one it's really invaluable, since it filters the "noise" and directs your attention to the heaviest parts of the query.

Correlated Metrics

You probably noticed another difference between the two views – one has Duration and CPU data and the other has those annoying gray columns. Here's another view of a full stack with many captured statements:

When we have the actuals we're able to fill in those other columns, as well as compare the estimated and actual rows affected by each statement. When there's a significant discrepancy the cells are highlighted in orange, and if you hover over them you'll see a tooltip with more details.

Execution Plan History

On the new Query Plans tab you'll see all plans captured during the specified date range:

There are two modes, toggled using the "Show Totals" (sigma) toolbar button. One shows plans grouped by stored procedure (remember, starting in SQL Server 2005, with statement-level compilation there can be multiple plans per procedure), the other is a flat list of all statement plans. Both show average metrics and operation counts. For example, if you want to see the plan with the most "scan" operations over the last 2 days, simply set your range, sort by one of the "Index Scan" columns, then click the "View" button to open the plan:

The rows highlighted in red represent recompiles that occurred within the current date range. The other plans may have been used during the current range, but they must have been compiled earlier.

Here's another really cool use case: Using the new "Jump To->Query Plans" context menu item from the dashboard to find problematic plans. For example, if you see a spike in "Bookmark lookups", you can select it then jump to the plans tab to see only plans used during that range, then sort by "Lookup Operations" to find the plan(s) that may be responsible:

I should mention that "Bookmark lookups", "Forwarded records", and "Backup/Restore Throughput" are new to the dashboard in v6.

Performance Hit?

All of this looks great, but does the additional load on your servers and network outweigh the benefits of collecting and processing this information? Certainly not. As always, we've gone to extreme lengths to ensure that any hit caused by the new plan analysis features is minimal. Intelligent data collection, compression, and other techniques are in play to ensure the hit will be barely noticeable... not nearly enough to outweigh the performance gains you'll reap from using the new features.

In Closing...

Going in, our goal with v6 was to present the most complete and accurate picture of a query possible – what it did and why. Other than a few aspects that technically aren't achievable today, like continuously capturing the actual plans, I think we succeeded. I'd liken it to a 500-piece puzzle, fully assembled but with 50 pieces missing... which IMO is preferable to 450 pieces in the box, which is what we had before.

I'm really excited about this release and hope you are too! Take it for a spin and let us know what you think. Existing customers get it here.

SQL Sentry Sponsors Master Immersion, and 5 Reasons Why You Need to Go!

2011-02-08T09:39:00.001-05:00

Unless you've been under a rock lately (or just really, really busy ;-) you're probably aware of the MCM program changes, as well as SQLskills new training format, Master Immersion, which takes their former Immersion training a step further, making it even more MCM-focused. Three of us attended the Boston Immersion event last year (me, Brooke Philpott (blog | twitter), and Aaron Bertrand (blog | twitter)), and so we know firsthand the high quality training it provides. I blogged about the experience here. Now what was already great training has become even better, and if you want to apply it to your quest for MCM certification, you can.

While demonstrating our software, particularly Performance Advisor, over the past several years I've made an interesting observation. It's that a person’s depth of knowledge about SQL Server tends to be proportional to their appreciation for the things our software does. This isn’t a knock on junior level SQL Server professionals because everyone starts somewhere. It's merely a statement that if you don't start with an understanding and appreciation of the underlying issues impacting SQL Server performance, then you'll inherently have less appreciation for why we're showing certain information, and the fashion in which it's being shown.

Hey, everyone wants to be understood and appreciated, right? ;-) So this realization is one thing that has led us to increase our support for various "newer" educational events over the past couple of years. From helping to organize a SQLSaturday here in Charlotte, sponsoring other SQLSaturdays, being one of the first U.S.-based companies to support (and attend) SQLBits, to sending 4 people on the inaugural SQLCruise. Now I'm not going to ask you to believe there is no promotional aspect to what we do, of course there is. We have to sell our software to remain viable. But we truly believe in our software – we know it has substantial real world merit, and we know it is quality through and through. We work too hard for it not to be. Just like SQLskills knows their training is the best, because they put everything they are into making it so.

When we heard about the new Master Immersion Events, we couldn't help but get excited. So much so that we approached Paul Randal (blog | twitter) and Kimberly Tripp (blog | twitter) and asked if there was any way we could be involved. One thing led to another, and I'm proud to announce that we are the exclusive sponsor of the first two Master Immersion events! We are providing a full breakfast and lunch for attendees all week, and some really cool swag! We're also sending three more SQL Sentry employees through the training.

As I said in my previous post, it doesn't matter where you lie on the SQL Server skill or experience spectrum, if working with SQL Server represents a significant portion of your job, you will benefit greatly from this training. If this same material was in the hands of lesser instructors this simply would not be the case, but Kimberly and Paul do an incredible job of presenting deep material in an easily digestible, stimulating fashion.

If you can break away for a week in February or March, do it, you won't regret it. In case you need help justifying it with your boss, spouse, kids, whoever, here are 5 reasons you need to go:

5. It is the best SQL Server training, from the best instructors in the business. Period.

4. It costs no more than a week of run-of-the-mill SQL Server training at your local IT training center... except that Kimberly Tripp, Paul Randal, and Brent Ozar (blog | twitter) will be teaching the class.

3. It will make you better at your job, which may lead to increased job security, pay, and praise from management and peers.

2. It doesn't last 3 weeks or cost $18,500, and there is no killer pass/fail test at the end of the week.

1. You won't have to pay for breakfast or lunch since SQL Sentry is picking up the tab!

Believe it or not there are still a few seats left for the Feb 21st class on Internals and Performance, as well as the March 14 class on Performance Tuning. So if you've been thinking about it, don't wait, get going and register now!

Ensuring Maximum CPU Performance via Group Policy

2011-01-03T20:21:00.001-05:00

There has been quite a bit of talk recently about a performance issue I see regularly on Windows Server 2008 and 2008 R2 servers, and that's the use of the default Power Plan setting of "Balanced". Glenn Berry posted about the impact of this setting on CPU performance; Brent Ozar blogged about a real world case where query CPU time actually doubled on new hardware; Paul Randal is conducting a survey to ascertain the extent of the issue (when Paul does a survey like this it's usually a sign that all may not be well in the SQL Server world); and Steve Jones recently covered it in his editorial.

There isn't much more I can add to the discussion of the impact of this setting on CPU and SQL Server performance, however, I will cover how easy it can be to make it a non-issue in your environment. I first ran across this issue and solution last year when deploying new server hardware. Our environment is based around the Intel Modular Server system and Windows Server 2008 R2, and we needed to be able to run hundreds of Hyper-V virtual machines and SQL Servers for QA testing, scalability testing, and production. The hosts themselves, and the majority of the VMs, are Windows Server 2008 (or R2), and so with the default "Balanced" plan we wouldn't be able to take full advantage of the significant CPU horsepower in which we'd invested (six "big blades", each with dual quad-core, HT-capable Intel X5550s).

The solution is simple: Group Policy. Like most other OS settings, the Power Plan can be set to whatever you want via policy, and any existing or new servers that use the policy will inherit this setting. There are a few ways to check and see whether a power plan policy has already been set; which you use will depend on the size of your environment and your level of access to group policy. The most basic approach is simply to inspect the power plan setting for each of your SQL Server machines and see whether the plan is set and/or editable:

If you see the "Some settings are managed by your sys admin" message, it's read-only and set to High performance, then policy is already set and you are good to go.

Alternatively, if you have access to group policy, open up the Group Policy Management Console in Server Manager, navigate down to Group Policy Objects, then select the Settings tab for each policy. Expand down to Administrative Templates > System/Power Management and see if "High Performance" has been set for Active Power Plan as shown below:

If you have multiple policies and it's not clear which your SQL Server machines may be using, you can use either the Group Policy Results or Group Policy Modeling wizards to view the Resultant Set of Policy for one or more servers. Once the results have been generated, check the Settings tab as above.

If it turns out that no policy has been set, you will need to either edit an existing policy or create a new policy that covers your SQL Server machines and apply the settings shown below:

If you need more details on this process, see Edit Administrative Template Policy Settings. If you don't have access to group policy at all, I'm sure you will tell whoever needs to know about this and get it changed... and hopefully they aren't too taken by the modest power savings of "Balanced" to accommodate. If they are, tell them to save the money on less powerful CPUs next time since that's the net effect ;-)

For the life of me I don't understand why a server OS would ship with this default (well, I do know why, but I certainly don't agree). I don't expect it will be changing anytime soon though, so hopefully a little group policy will help you get the most out of your CPU resources in the meantime.

SQL Sentry Plan Explorer Beta

2010-10-20T15:06:00.001-04:00

Recently at SQLBits York I demonstrated the new SQL Sentry Plan Explorer (formerly known as the "Plan Viewer") and officially announced its upcoming release. We are now pleased to make the beta version of the tool available for download!

Background

We had originally envisioned query analysis features only as part of the fully licensed version of SQL Sentry Performance Advisor. The goal was to build upon what we all have today with the graphical plan view in SSMS, and in a big way. Like many of you, I've been using this feature for years, going back to Enterprise Manager, and so had lots of ideas going in about what we could do to make plan analysis more efficient.

After a few significant reworks over many months, things really started coming together, and it was doing most everything I'd wanted and much more. Working with our lead developer, Brooke Philpott (blog | twitter), one idea had led to another idea, which led to another… it was a gradual, iterative process. I'm thankful that we embrace a style of agile development here, because this is one project that I believe would have been impossible to design to completion in advance. It was only through using it on a daily basis to solve real world query issues that the tool was able to evolve into what it is today.

Anyway, somewhere along the way a couple of things became clear to us:

These features are so cool and useful (IOHO) that we wanted to share the tool with anyone that wants it, so we are making it available to the SQL Server community for FREE!
Much of the code was client-side, so it would be fairly easy to break out into a standalone tool without any dependencies on our collection service or database (one of each is required for every full SQL Sentry installation).

Plan Explorer Features

I'll go ahead and run through some of the key features of the Plan Explorer, and in the process I'll compare and contrast with SSMS. I am not trying to bash SSMS (promise! ;-), I'm simply trying to illustrate the reasoning behind the design of the tool for longtime users of SSMS/Enterprise Manager.

When you launch the tool for the first time, you'll notice is that the UI is broken into 3 major sections:

Statements Tree
General Query & Plan Info
Plan Details

They're all empty until you load a plan. There are several ways to load a plan:

In SSMS, right-click a graphical plan and select “Show Execution Plan XML”, then copy and paste the plan XML into the Plan Explorer. It doesn’t matter which pane is active, the clipboard handler is global and will auto-detect the plan XML.
Save an execution plan from SSMS to a .sqlplan file, then open the file using the Open toolbar button, File->Open menu, or Ctrl + O. Raw plan XML files and .QueryAnalysis files (our own proprietary format) are also supported.
Right-click an existing .sqlplan file in Windows Explorer and select “Open with -> SQL Sentry Plan Explorer”.
Drag-and-drop a plan file onto the application.
Retrieve the estimated plan from TSQL. (See General Query & Plan Info section below).

Statements Tree Section

Let's start with how SSMS works. A batch or stored procedure can of course consist of one or more statements. With stored procedure estimated plans in SSMS, all individual statement plans are joined together at the top level:

When it's an actual plan for a batch or stored proc, or an estimated plan for a batch, each statement is broken out into a separate section, with embedded cost and other information in the section header:

When there is only one or a few statements, SSMS can do Ok with this approach. However, if it's a large plan with tens or hundreds of statements and conditional logic, looping, etc., it just doesn't scale. Some plans are so big and complex that it is literally impossible to find the heaviest statements and operations, and some can't even be loaded by SSMS!

We wanted to change all that, and so the Statements Tree represents one of the biggest design differences between the Plan Explorer and SSMS. Its function is critical when navigating multi-statement plans. What it shows is a logical breakdown of the entire plan tree, including all control structures, nested procedure calls, and estimated and/or actual metrics for each statement, as well as operation counts by statement:

If you click on any statement row in the grid, you'll be shown the associated plan for that statement only:

Likewise, if you click on any control structure (IF or WHILE) or EXEC <procname> higher up the tree, you'll see a filtered view of the plan starting from that point:

As you've probably guessed by now, you can of course sort the Statements Tree by clicking on any column header, and voila!, the highest cost trees and statements will immediately bubble up to the top:

In the shot above I've sorted by Estimated Cost (all numeric columns sort DESC on first click). I can see that one of the subtrees has a nested DELETE that accounts for 27.2% of the total procedure plan cost, and I've have selected that statement row to view its plan. If you enlarge the image you'll see that a clustered index seek (highlighted in red) accounts for 39.6% of the statement plan cost. So with a couple of clicks we've quickly ascertained the heaviest cost statement and operation in this rather large plan.

Whether there are 10 or 1,000 statements in the master plan doesn't really matter, it's trivial to find the most expensive areas so you can focus your attention on those. Big plans that simply weren't usable before can now be managed with ease.

General Query & Plan Info Section

The primary tab you will make use of in this section is "Text Data", which holds the color-coded TSQL batch or procedure definition:

If you loaded an existing execution plan, the TSQL will be auto-generated. You can also type TSQL, copy/paste it in, or open a .SQL file, then click the "Retrieve Estimated Plan" button on the toolbar to get the estimated plan.

NOTE: Executing TSQL to retrieve the actual plan is not yet supported, although opening an existing actual plan is fully supported.

You'll notice that, like plans, the TSQL statements are synchronized with the Statements Tree, meaning that if you select a row on the tree view it will auto-select the associated TSQL statement and its execution plan, and vice versa. See the shots above for examples.

Plan Details Section

This section contains 4 tabs, each of which serves a different purpose:

Plan Diagram
Plan Tree
Top Operations
Query Columns

If you select an operator node or row on one of the tabs, it will be auto-selected on all others, so you can switch back and forth between the different views of the plan without losing your place.

Plan Diagram

This is the view that will certainly be most familiar to everyone… although you should immediately notice some differences. First, there is color! That's right, the cost information is color-scaled so you can quickly see which operations are heaviest, and Lookups and Scans are also highlighted (if Rows > 100):

Pretty cool, eh? But wait, there's more! ;-)

If you right-click the diagram, context menus provide access to a variety of other functions. (bolded because this can be easy to miss!)

Using the context menus, in addition to scaling by total cost (CPU + I/O), you can also use CPU or I/O separately:

This can be very helpful if your hardware is more I/O constrained than CPU constrained, or vice versa.

To see which subtrees are most expensive, select the "Cumulative Costs" context item:

I won't go into detail on all of the other plan diagram enhancements in this already lengthy post, but here is a list of some of them:

Optimized plan node labels prevent truncation of object names in most cases, with menu option to disable truncation completely so full object names are always visible.
Optimized tooltips prevent wrapping of object names, column names and predicates.
Costs are always displayed above nodes for maximum readability.
Costs are shown to the first decimal place.
Connector line width can be scaled by either Rows or Data Size.
Rows and Data Size labels are displayed above connector lines.
Connector lines for bookmark (key|rid) lookups, table spools, and function calls show the true estimated rows or data size, and line width is scaled accordingly. (SSMS always shows the "per execution" rows, CPU and I/O costs for these operators in estimated plans, which can appear to dramatically understate the true costs. For example, if estimated rows=10 and estimated executions=1000 for a key lookup, the line is scaled to 10 rows, not 10,000! What makes this more confusing is that for actual plans SSMS does show the true rows and costs. So you really have to pay attention to the type of plan you're looking at in SSMS – if you're conditioned to always look for the operators with the fattest lines, you can completely miss the highest cost operations.)
The mouse scroll wheel can be used for both scrolling up down, and zooming in/out (by holding Ctrl) !

Last but certainly not least, you may notice that some plans seem, well, "different". This is likely due to the optimized layout algorithm which is able to render many plans using much less real estate than SSMS, meaning you are seeing more of the plan without having to scroll and zoom. It's easiest to demonstrate this with a couple of pictures. The shots below are of the same plan:

SSMS version:

Plan Explorer version:

Plan Tree

This view is just what it sounds like, a tree representation of the plan which shows all operations and associated metrics. On the surface it looks similar to a showplan_all, however it's different in several respects:

You can expand and collapse sections, and sort within levels.
Additional metrics are shown, such as "Estimated Data Size", as well as "Actual Rows" and "Actual Data Size" for actual plans, side-by-side with the estimates. There are many other plan metrics available by right-clicking any column header and selecting "Column Chooser".
Significant differences between estimates and actuals are highlighted.
Possibly problematic operations like scans and bookmark lookups are highlighted.

Top Operations

Another one that is as it sounds, a simple list of all plan operations, sorted DESC by total estimated cost by default. You can of course sort by any of the other columns, as well as group by operation type, object, etc. The same columns and highlighting as on the Plan Tree tab apply here.

Query Columns

This is one of the most useful views for many plans. What is shows is a list of all columns accessed by the query, for whatever reason (sarg, join, output list, sort), along with the table, operation, and index used. How is it useful?

Take the case of a bookmark lookup, where you have some columns that aren't covered by the index chosen by the optimizer. The traditional ways of figuring out which columns aren't covered is by hovering over multiple nodes on the graphical plan to determine the index used and columns accessed, clicking through the Properties pane for those nodes, or looking through a showplan_all, all of which can be tedious and error prone.

Instead, when you look at the Query Columns list, you'll see the columns for related operations grouped together (indicated by a thick separator bar), with the index used and the columns in the lookup highlighted in orange:

From here it's often a simple matter to make the appropriate index modifications to eliminate the lookup. In the case above, I can see that index [IDX_ClientID] isn't covering columns [LEASE_ID] and [DOCUMENT_SIZE], so I may want to add them as included columns to the existing index, or create a new covering index.

NOTE: In the full version of SQL Sentry v6, you can directly open the index properties from this and all other plan tabs using context menus, or by double-clicking nodes on the graphical plan. This is something you used to be able to do in Enterprise Manager but was dropped in SSMS, much to my dismay. We wanted to make this available in the Plan Explorer as well since it can be a huge timesaver, but currently the tool has no integration with SSMS in order to keep the install simple. Rest assured, if/when this changes we will add this capability to the tool.

Beta Downloads

You MUST HAVE .NET 4.0 PRE-INSTALLED to install and use the Plan Explorer. Download it here.

SQL Sentry Plan Explorer x64

SQL Sentry Plan Explorer x86

SQL Sentry Plan Explorer Documentation

Plan Explorer Change List

General discussion and support for the free tool will be provided through the SQL Sentry Plan Explorer forum.

This beta build has proven very solid, and handles all plans we've seen thus far, regardless of size or complexity. However, as it is a beta, you may find a quirk or two. If you run into any problems, or have any ideas, questions, etc., email me at greg at sqlsentry.net. For questions, please be sure to check the forum and the documentation first.

Special thanks the first beta testers who really hammered the tool and helped us get it where it is today! In particular, Jonathan Kehayias (blog | twitter), Mladen Prajdic (blog | twitter), Christian Bolton (blog | twitter), Brent Ozar (blog | twitter), Brian Kelley (blog | twitter), and Aaron Bertrand (blog | twitter) provided some great real-world plans and feedback.

There are many more features and use cases I'll be covering in the coming weeks, so stay tuned...

I hope you enjoy using it as much as we've enjoyed building it!

Aaron Bertrand Joins the SQL Sentry Team

2010-10-18T11:03:00.001-04:00

I'm pleased to announce that highly regarded SQL Server MVP, Aaron Bertrand (blog | twitter), will be joining the SQL Sentry team! His first day on the job will be next Monday, October 25th. We are all extremely excited to have him on board, for a variety of reasons. On the surface, it may appear to be an attempt to "keep up with the Jones'" in the sense of us bringing on a high profile community advocate, but this is actually much more than that. Aside from Aaron's vast knowledge and experience with SQL Server, having known and worked with him for several years now, he is a person that just gets things done. Sure, he has phenomenal communication skills and will undoubtedly continue blogging and speaking about SQL Server, but for us the move is just as operational in nature.

I mean, who better for a SQL Server ISV like us to have in house than the person who has far and away provided the most feedback to Microsoft on SQL Server itself? (see Connect stats lower right)

Aaron was a long time user of SQL Sentry software in his previous job, so he already knows our software inside and out and has provided lots of great feedback over the years. The SQL Server environment at OTOLabs is unique – high volume OLTP systems with many hundreds of databases – it definitely stressed our software early on and pushed us to come up with ways of dealing with that kind of scale.

If you know Aaron, you know that he says what he believes and doesn't pull any punches. He's exactly the kind of person we want here, someone with unquestioned integrity who isn't afraid to tell it like it is, and who will continue to look at our software with a critical eye. I have no doubt that Aaron will have a significant impact on our company and our software in many different ways, some we can anticipate, but many we probably can't.

I could go on, but from here I'll defer to Aaron's post about the move.

Welcome aboard, Aaron!

SQLCruise Contest Winner

2010-06-02T07:50:00.004-04:00

As you may have heard, as part of our sponsorship of the inaugural SQLCruise, we decided to run a contest to provide one lucky winner a free ticket for the cruise and training. When we started the contest we expected that most of the submissions would be in the form of tweets or short blog posts, and we did receive many of those which were quite good. We were surprised and impressed, however, by the effort, ingenuity and humor that went into four submissions in particular. So much so that we had a tremendously hard time selecting a winner… how could we possibly choose between:

Jorge Segarra (blog | twitter) – Here's a guy who, and I can't think of any other way to put it, was born to go on this cruise. Not only was his video hilarious, it was replete with gratuitous SQL Sentry references, and was actually submitted before the contest officially started. Talk about enthusiasm! It was a shot across the SQLCruise bow – "If you want to go on the cruise, you're gonna have to go through me!!" It also served to ensure we wouldn't spend any time reviewing half-hearted submissions (thanks man! ;-) How could we in good faith not send him, and deprive the other SQLCruisers of four days of laughs and learning with the Chicken?
Matthew Velic (blog | twitter) – A guy who's new to SQL Server and who desperately wants training, and who would apply that training daily for a very worthy cause, fighting Pediatric AIDS. He clearly spent a lot of time on his video as well, as the production values were through the roof. How could we not send Matthew?
Rebecca Mitchell (blog | twitter) – A DBA in Colorado who not only has the technical skills required to successfully manage lots of SQL Servers, but the creativity to write a poem about SQL Server (yes, that's right, and probably the longest poem I've ever seen, let alone read), with cleverly infused references to SQL Sentry's deadlock analysis and every DBA’s favorite breakfast meat to boot. How the same person can have both abilities is frankly beyond my comprehension. How could we not send this rare talent on the cruise?
Erin Stellato (twitter) – Erin lives and works in Cleveland. I mean, how could we not send her? ;-) Well, her video also happened to be extremely well done, with Hollywood quality acting, score, humor and special effects. Seriously, she too needed to go!

So after going round and round for hours, we couldn't decide, which left us with a couple of options: either put it out for a community vote (which we may actually do next time), or just bag the whole idea. Unfortunately we had a hard deadline from Carnival so there was no time to put it out to vote. Punting on the contest was out too as we certainly couldn't punish everyone for being "too good"… that's just not how we operate here.

After some more agonizing, another idea came to us. It was extreme… but clearly it was the best option, and a quick review of the official contest rules confirmed that it was indeed "legal": Jorge, Matthew, Rebecca, Erin, pack your bags, you are all going on SQLCruise!!! ;-)

We have no doubt you will all make the most of this awesome opportunity. Have a great time, and be sure to spread the word!

Thanks to everyone who participated, I do wish we could send all of you… maybe someday. Meantime, I hear there are still a few seats left!

Getting Immersed in SQL Server

2010-04-05T10:02:00.002-04:00

I had the pleasure of spending the past week at the SQL Server Immersion event in Boston. I was joined by our lead developer, Brooke Philpott (blog | twitter). This was an intensive course on SQL Server internals, and much more, put on by the esteemed Paul Randal (blog | twitter) and Kimberly Tripp (blog | twitter), and hosted by Adam Machanic (blog | twitter). I knew going in that Paul and Kimberly were authorities in their respective areas – Paul in the storage engine and related areas like backup/recovery, and Kimberly in query tuning and index optimization. What I didn’t expect was the sheer breadth and depth of their knowledge, and that they also share quite a bit of knowledge in common. They are of course married, so I expect this leads to a lot of information-sharing at the dinner table that wouldn’t occur otherwise.

The material covered is far too extensive to get into here, but I will say that every day, and just about every hour of every day, was a challenge… and I have worked with SQL Server for almost 15 years. This was not basic stuff. A couple of times in the past I’ve made the mistake of paying for training where the outline looks great, but you get in class only to find that it’s far too basic and high level, and therefore not worth the time away to be there. This was not that class. Even though GAMS, IAMS, PFS pages, headers, slots and associated bits and bytes could be considered “basic” in the sense that they are the building blocks of SQL Server, they are certainly not simple. The amazing thing is that this class really costs no more than many other lower quality week-long SQL Server training courses, when in reality it should probably cost twice as much. It is an absolute bargain.

When it comes to the instructors themselves, Kimberly and Paul simply have no equal. Not only are they both ultimate authorities, but they have endless enthusiasm about the material, and spot on delivery. If either ever got tired they never showed it, even after going all day and all week. We witnessed countless demos over the course of the week, some extremely involved, multi-step processes, and I can’t recall a one that didn’t go the way it was supposed to. When has that ever happened? It certainly hasn’t for me. Seems as if whenever I demonstrate something in public there is invariably some glitch or another. These are not beginner presenters, and they put an extreme amount of preparation and attention to detail into everything that they do. Completely, utterly professional.

You might think that with this extreme level of skill comes extreme levels of egotism and lack of patience. Nothing could be further from the truth. There were no stupid questions the entire week – well, there were of course, but the response from Paul or Kimberly usually started with “Excellent question…” even when the question wasn’t so excellent. They simply know how to teach, and are approachable, humble, and patient.

Anyone, from the accidental DBA (there were actually several in our class) to the experienced MVP, will gain significantly from this training. Not everyone has 3 weeks and $20K to spend on MCM training, the first week of which is Paul and Kimberly on similar material. Is Immersion the same as the first week of MCM? According to Kimberly it is not quite as deep, which surprised me… I mean, how much deeper can you go? ;-) There is also no test at the end of the week at Immersion, so probably a lot less pressure as a result. However, if you are thinking about going after the MCM and want to stick some toes in the water first, I suspect Immersion will give you a good idea of what to expect, as well as serve as a serious primer for much of the first week.

What I think it comes down to is that Paul and Kimberly truly enjoy what they do. They have a passion for sharing the unique knowledge they have accumulated over the years while working at Microsoft and consulting with some of the largest SQL Server enterprises in the world, and it shows. The SQL Server community should be thankful that they are so willing to share this knowledge, and that they are able to do it so well. I count myself privileged to have been able to spend a week with them, and learn many things that will undoubtedly help me and my company, today and years into the future.

This was the only public Immersion event they will hold in the U.S. this year. Keep a close eye on the SQLskills.com upcoming events page for announcements about future events. I believe there is one in Dublin later this year, but I'm not seeing it on the site, so it may be sold out.

SQLSaturday Charlotte: Lessons Learned

2010-03-09T12:37:00.001-05:00

We just wrapped up what have been the busiest, most stress-filled, yet rewarding couple of weeks in recent memory. Overall the feedback we've received about the SQLSaturday Charlotte event has been amazing, thanks to all for the kind words that have been tweeted and blogged thus far! I do think for the most part the event went off about as well as it could have despite our lack of experience planning technical conferences. However, we definitely learned a lot that we'll be able to apply should we ever do it again. I had many questions when I first started working on this event, so I wanted to get my thoughts down while still fresh with hopes that it might be helpful for future SQLSaturday planners.

Scheduling Concerns

One of my primary tasks was trying to coordinate the flood of speakers and sessions we had for the event, a flood created by none other than CSSUG president and SQL Sentry partner channel manager Peter Shire, whose skills at promoting can only be described as P.T. Barnum-like. (I thought for sure that P.T.'s first name must have been "Peter", but turns out it was "Phineas") Peter did a phenomenal job securing the support of an amazing list of 35 speakers, and getting the word out to the SQL Server community to the tune of about 400 registered attendees, 280 of whom actually made it to the event.

The starting point for the schedule planning was the facility. The Microsoft campus here is a fantastic facility in a great location close to the airport, downtown, etc., however, it wasn't necessarily designed to run an event like this. The initial room breakdown was:

Building AP1:
     Room 1: 200 seats
     Rooms 2,3,4,5: 20 seats each
     Cafeteria: 220 seats

Building AP2:
Room 6,7,8: 50 seats each

The two buildings are about a 3-minute walk apart. Because of the relative size difference between the largest and smallest rooms, my biggest fears were putting a hot session in one of the smaller training rooms causing massive overflow, and putting a not-so-hot one of the big rooms… either of which can exacerbate the other. The original layout had us using the 4 training rooms in AP1, the same building with the cafeteria and the 200 seat room, however, about a week before the event we got word from Microsoft that we'd been bumped by some internal training and had to use 4 training rooms in AP2 instead. So that left us with:

Building AP1:
Room 1: 200 seats
Cafeteria: 220 seats

Building AP2:
Rooms 2,3,4,5: 20 seats
Room 6,7,8: 50 seats

The AP2 training rooms are an exact mirror of those in AP1, however it seemed to make for a very lopsided layout since it left us with a single large room in one building, and 8 rooms in the other building. This caused concern since we'd been counting on a lot of interplay between the 80-100 people in the training rooms and the 200-seat room where I'd put all of the "hottest" sessions. We didn't understand why the other group couldn't just use the AP2 training rooms, but eventually found out – they were doing lab sessions, and apparently the workstations in AP2 aren't the same class as those in AP1. We weren't using the workstations so it didn't really matter to us, but apparently it did to them ;)

As it turned out, it probably did end up causing lower attendance for the featured sessions and some overflow in some of the smaller rooms, mainly because some seemed reluctant to leave AP2 when there were so many other good sessions going on next right there. But did it even come close to ruining the conference? Certainly not.

Another issue with the change was that we didn't get the new room names in time to put them on the signage, so we ended up using numbered rooms that were offset from the tracks by one – Track 2 was in Training 1, Track 3 in Training 2, etc. This was very confusing, even to me. At one point I even directed Wayne Snyder into the wrong room for his session. Sorry Wayne!

The first thing I did was try and categorize the 80+ submitted sessions so I could get a better handle on the actual areas of interest we had to work with. What I was really looking for at that point was whether or not we could have any focused tracks. I ended up with 4 major categories and 24 subcategories:


Category – Subcategory	Session Count
Admin - Broker	2
Admin - Clustering	2
Admin - DB Design	2
Admin - Hardware	2
Admin - Maintenance	1
Admin - Memory	1
Admin - Performance	11
Admin - PowerShell	5
Admin - Replication	3
Admin - Troubleshooting	2
Admin - Virtualization	2
BI - Dev	5
BI - SSAS	1
BI - SSIS	5
BI - SSRS	4
Dev - DB Design	2
Dev - Performance	4
Dev - Tools	2
Dev - TSQL	2
Dev - XML	1
General - Career	2
General - General	4
General - Social	3
General - Tools	2

It was apparent that although for a couple like Performance and BI, focused tracks might work, but for others we only had one or two sessions so trying to do clean, interest-based tracks would be fruitless. I should mention that although for some sessions the categorization was easy, for others it was not clean at all, so I just took my best stab at it. Even so, there were a few others that seemed to defy categorization, like Kevin Kline's "Top 10 Mistakes on SQL Server" and Sergey Pustovit's "SQL Server 2008/R2 Overview", and those ended up in "General – General" ;)

I was also hoping that by having these categories, I'd be able to send them out to a few people that had planned other recent SQLSaturdays and ask them to order the list by most to least popular at their event. I got some extremely valuable feedback from those planners, but since they didn't have accurate attendance numbers for all topics we didn't get any rankings back. Since this info could have been invaluable to me and helped to avoid some of the session/room mismatches we experienced, I'm posting all of our attendance numbers here. Bear in mind they are only estimates and not exact. If you see anything that looks way off, let me know.

Next, to my sessions spreadsheet I added a few columns, and used them to come up with a universal rating measure to help decide where sessions should go. Here they are:

Hot Topic – Yes/No – Is the topic particularly popular right now, and are there a lot of people talking about it in the media? For example, I don't think that anyone would argue that PowerPivot is a hot topic right now. If Yes, 2 points were added to the rating.
Premiere Speaker – Yes/No – Certain speakers have significant name recognition, and will draw attendees regardless of the topic. If Yes, 3 points were added to the rating.
Appeal – This is a 1-10 point value added to the rating for how broad of an audience would possibly be interested in the topic, or the size of the base population. For example, PowerPivot may indeed be a "Hot Topic", but if the potential base of users is small the appeal number will be lower.

I used the above to calculate a simple rating value, and the higher the number the bigger the room. Certainly not very scientific, but probably good enough for our purposes. Ultimately there are so many other variables involved, IMO if you try to be any more exacting you run the risk of skewing things too far one way or another. For example, if I'd actually tried to estimate the number of "BI" users or "Admin" users that would show in lieu of the general "Appeal" number, and my estimates for those populations were off, it could cause trouble. Now, keep in mind that this approach is really most applicable to a first event, where you really have no idea who is going to show up. Next time we'll have a much better idea of the different populations here in our region and will use actual numbers as a basis for predicting session popularity.

Another idea that came up was surveying people in advance to see which sessions or categories they are most interested in. If we had had more time, and/or if there was a capability to do this built into the sqlsaturday.com website, we likely would have made use of it. As it was, there was just too much else going on with the planning to even think about attempting something like that.

Below I'll discuss a few of the bigger session surprises in a bit more detail.

Hotter-Than-Expected
TSQL
Specifically Mike Walsh's "You Can Improve Your Own SQL Code" and Geoff Hiten's "Bad SQL" both ended up in a smaller 20-seat room, and both could have easily been in a 50-seat room.

SSRS/SSIS
The 25-seat room we had set aside for these BI topics had 46 people crammed into it at one point. I didn't put this track into one of the larger rooms mainly because I just wasn't quite convinced it would compete with Performance, Virtualization, PowerShell, and other "hot" topics I had in the 50-person rooms. I also had a lot of people that know telling me that BI is always a smaller crowd at these things, so I went with our only "in between" space, the 25-seat conference room that we knew could handle up to 35 with some standing. Good call for not putting it in a 20-seat room I guess, but bad call for not giving it a 50-seater. If you were one of those that had to act like a sardine for an hour, my sincerest apologies, we'll give you more room next time.

Not-So-Hot
Virtualization
This one shocked me. With as much talk (and uncertainty) as there is right now about this topic and SQL Server, I was worried that a 50-seat room would overflow for Aaron Nelson's and Denny Cherry's sessions. I was way off. They would have both been fine in one of the 20-seat rooms.

SSDs (Solid State Drives)
Again, I was shocked when Kendal Van Dyke's session wasn't packed. Great, well-known speaker with what I thought would be a hot topic, but it would have been fine in a small room.

Data Compression
The two sessions we had on this topic were the most lightly attended of all, only 4-5 people in each. This was in a smaller room already, but I had expected to see more interest since it's new to 2008 and disk space is always a concern, however the Enterprise-only caveat or the other concurrent sessions may have been a big factor.

Too Much of a Good Thing?

The "good thing" in this case being PowerShell. I guesstimated that this would be a hot topic as it has only seemed to gather steam with DBAs over the past year or so, and with remoting and other cool features in Windows Server 2008 R2 and PowerShell 2.0 there's a lot of new stuff to learn about as well. Turns out I was right… kind of. Aaron Nelson's first two sessions in a 50-seat room were pretty much full, but Allen White's two sessions immediately following were, well, not so much. Again, it certainly wasn't Allen as a speaker since he's highly regarded and authoritative on this topic. However, I did hear from more than one person that two PowerShell sessions probably would have been sufficient for this crowd. I think Allen was a little disappointed, but being a glass is half full kind of guy, he said it was good practice for TechEd. Thanks Allen!

Premature Raffling

One of our biggest mix-ups of the day was the fact that we started the vendor giveaways right after the last session was supposed to end at 5pm. We still had a session going in the other building that went a bit long, so those attendees and the speaker missed out on the raffles. To make matters worse, that particular speaker would have won an iTouch had he been present! Sincere apologies here. We felt bad, and he did seem a bit upset -- I should have reminded him that he won a WinMo device at our very first SQL Sentry giveaway several years ago, although I'm not sure it would have helped at that point ;)

Bottom line, ensure all sessions have finished and folks have had time to get to the raffle before you start. Apologies to anyone else that missed out on this – if you did, send us an email and we'll send you either a SQL Sentry T-shirt, USB 2.0 hub, or iPhone cover.

Where'd the Speaker Go?

We had a particularly well-attended session on IO Performance… only problem is we had no speaker. I made a quick call over the two-way to registration to see if anyone had seen this particular speaker. They had not. I let the attendees know this, and they quickly filed out to look for another session, some with looks of disappointment. As it turns out, the speaker had cancelled a couple of weeks earlier, but apparently it got lost in the hundreds of other emails that were flying around about the event. Mistakes like this happen and stuff just gets missed sometimes… which is why there should have been a mechanism in place to catch this. Had we done a speaker roll check earlier in the day we would have caught it, and probably could have gotten another speaker to fill in, or if not, at least made attendees aware of the cancellation in advance. We just assumed all speakers were there – not safe with 35 speakers.

Bear in mind that some speakers may show up later if their first session isn't until later in the day, so you may not want to check roll first thing in the a.m., but either way it's generally an easy matter to at least confirm whether or not the speaker was in town. In this case they were not.

Plan for Cancellations

Jorge Segarra (aka, @SQLChicken), one of the organizers for the Tampa SQLSaturdays told me to expect between 5-7 cancellations. I thought that sounded high – how can that many speakers commit for something like this, then just cancel?!? Well, he was right. There were many different reasons we heard, but what can you do. Fortunately we had enough speakers with enough sessions to make up for it. I think the 5-7 backups is a good number to expect for an event of this size, probably less for smaller events. If you saw a session on the schedule that seemed a bit out of place for the room or other sessions around it, it was probably a backfill from a cancellation.

A Final Word

It's important to note that a lot of these scheduling dilemmas were due to the broad range of room sizes we had here. If your event has 300 people and 4-5 rooms that hold 75 people each you've got a lot more margin for error, and you're probably not going to need to go to this level of detail. Anyway, I hope some future SQLSaturday planners will find something here that's useful. If any of you planners have any questions about what we did or why, I'll be glad to help however I can. Please post them here or shoot me a DM or email.

A Huge Thanks to All

I wanted to take this opportunity to thank all of those that helped with the planning and organization for this great event. First, I wanted to thank Bill Walker, head of the SQL Server CSS team here in Charlotte, and his counterpart Lynne Moore for their immediate and unwavering support. From day one, they threw all of their considerable resources behind the event and without them it just wouldn't have happened. All of us were truly amazed at the forces they were able to mobilize so quickly to just get it done. I also wanted to thank Sergey Pustovit, Chris Skorlinski and Evan Basalik from the SQL Server CSS team for putting on some great sessions, and all of the other CSS members who volunteered their time whose names I don't know – they ran the "SQL Clinic" booth and helped out in innumerable other ways.

Next I wanted to thank all of the people here at SQL Sentry who volunteered their time, in no particular order -- Peter Shire, Karen Gonzalez, Nick Harshbarger, Brooke Philpott, Jason Hall, Natalie Wieland, Jason Ackerman, Steve Wright, and also Ken Teeter for the great photography.

Again in no certain order, sincere thanks go out to Jorge Segarra, Aaron Bertrand, Andy Kelly, Grant Fritchey, Tim Ford, John Welch, Rafael Salas, Geoff Hiten and of course Andy Warren for allowing me to pick their brains during the planning process. Not sure what I would have done without their input and words of wisdom.

And last but certainly not least, thanks to all of the speakers who came from near and far to be here. We are greatly appreciative that you decided to take the time to provide such a fantastic educational opportunity for our SQL Server community here in the Carolinas.

SQL Sentry v5.5 Need to Know

2010-02-24T18:53:00.001-05:00

SQL Sentry v5.5 Release Candidate 2 was just published to the website. Although it's a dot release, don't be fooled, v5.5 stands to be a watershed release for us in more ways than one. Not only does it represent the culmination of many months of hard work by the team, but it contains what have easily been the most heavily requested enterprise features since the original release of Performance Advisor for SQL Server in 2008: the Global Server Status View and Performance Reporting. There are also many very cool secondary features such as Automatic Disk Misalignment Detection, Mount Point Support, as well as a variety of performance optimizations. Read on for more details…

Global Server Status View

Finally, you can view key performance metrics for more than one server at a time from the SQL Sentry Console! You simply right-click the Devices root node, or any device group node, and select the "Open Performance Advisor Overview" context item:

There are a couple of different ways to look at the data. The default is Windows metrics at the top level:

From there you drill in to see metrics for each SQL Server or SSAS instance on the server. Just like on the PA dashboard, we also show how much Windows resources are being consumed by the SQL Server and SSAS processes for network, CPU, and memory:

The level below that shows the actual instance metrics, which will of course change depending on whether you’re looking at SQL Server or SSAS.

If you only want to see SQL Server and/or SSAS metrics without the Windows, simply uncheck Windows in the dropdown at top. This gives you a very clean look across all SQL/SSAS instances in your environment:

As shown in the shots above, if a performance metric exceeds commonly accepted preset thresholds (such as Page life expectancy < 300 secs), the cell and associated server name will turn red to highlight it. And to answer the question you are about to ask… no, there is currently no way to customize this, but this is coming ;-) For this release the primary focus was getting the alarming framework in place with some basic presets.

However, to hold you over until, you can set custom filters for any column or combination of columns. For example, if you want to see only servers with "Transactions/sec > 100 OR Blocks > 0 OR Deadlocks > 0", simply click the column header and set a filter:

As the view auto-refreshes and metrics change you’ll see server rows pop in and out. This way you can stay focused on the servers requiring your immediate attention, based on limits you’ve defined. Here's what a filter looks like once set:

Note there are now only two visible servers, the rest are hidden by the filter.

When you combine this with the ability to adjust the interval displayed, filtering becomes an even more powerful feature:

Let me elaborate. By default the grid shows the last raw sample value collected, but since many metrics are sampled every 10 seconds there may be temporary spikes that you want to ignore. You may not want the filtered server list changing every refresh. If you simply increase the interval to anything other than "Last Sample" -- say "10 Minutes" -- the metrics displayed will be last calculated average over that interval. This can effectively smooth out many spikes, and thus prevent a lot of volatility with the server list.

Even without the use of filters, this is a great feature for getting quick snapshot of how all servers have been performing over different ranges, up to the past 3 days.

One last item on the global view -- if you don’t care for the default dark skin, you can change to a standard light grid color using a new user preference:

Performance Reporting

There has always been an enormous amount of valuable performance data contained in the SQLSentry database, but aside from the PA dashboard there really wasn’t an easy way to get at it. With the new SSRS-based performance reporting, now there is! To see all of the new reports, select "Reports –> Performance Advisor" from the menu bar:

Here are a couple of quick shots of the "Waits Analysis" and "File Utilization by Database" reports:

Not only do we have a great list of the most commonly requested reports, but we have two ways to generate completely customized reports – the Performance Counter History report, which lets you select any combination of servers, counters and instances:

…as well as the Performance Counter Date Range Comparison report, which lets you do pretty much the same thing, but using two different date ranges.

One of the coolest aspects of our custom reports is that you’re not limited to only standard Windows perf counters – you can report on any of the performance data we collect, including data from DMV’s, WMI, and even internally calculated metrics like Waits by Category! And just like the PA dashboard, you can select any date range, from 3 minutes to 3 years… and it won't kill your server in the process. A lot of effort has been put into ensuring that the reports generate quickly and will minimally impact the performance of the SQLSentry database.

You can save any of the custom reports by clicking "Save" on the parameter selection window, as well as deploy the reports to any SSRS instance using the "Deploy Reports" menu item.

Disk Activity View Enhancements

We’ve introduced two significant enhancements to our patent-pending Disk Activity view: Auto-Misalignment Detection and Mount Point support. Which you will like best will depend a lot on your environment.

Automatic Disk Misalignment Detection

As you probably know, misalignment is no longer an issue with new Windows Server 2008 installs since it uses a default offset of 1024KB. However, what we have found is that many servers out there are either still running Windows Server 2003, or were upgraded from Server 2003 to 2008. As a result this is still a big issue in many environments, and we’ve just made it dramatically easier to detect – you simply watch a server with PA, then look at the Disk Activity screen. If the logical disk has a red background, it means the offset is suspect:

PA shows you exactly what the offset is in the tooltip for the logical disk. I wish I could say we will then auto-fix it for you, but you probably realize that would not be possible ;-) However, on this same screen you can clearly see whether or not latency is actually a problem on the misaligned disk, and from there determine whether or not you should actually go through the process of moving database files and rebuilding the partition with a proper offset. Here's another server where all partitions are misaligned, and where the disks are experiencing pretty severe latency:

BTW, if you haven't seen this screen before, all of those color-coded bars are database files -- data files and transaction logs. Same color means same database, diagonal hash marks mean transaction log. Bottom connector pipe for each disk is writes, top pipe is reads. Wide red fill in the pipes means high latency, thin green means IO activity but acceptable latency.

For more details on the performance implications of disk misalignment and what you can do about it, check out Microsoft's Disk Partition Alignment Best Practices for SQL Server whitepaper and Paul Randal's blog post on this topic.

Mount Point Support

Another common request that we’ve been getting more and more over the past year or so is for mount point support, in large part because of the explosion of VMs and associated LUNs in the environment. Now when you look at the Disk Activity screen, you’ll see mount points displayed alongside all other disks:

PA shows the same latency and volume information for mount points and database files on mount points as it always has for other disk types.

Technically these disk-related changes were no small feat to pull off, but once again the dev team rose to the occasion!

Performance Optimizations

We are always looking for new ways to increase the performance of SQL Sentry and minimize our footprint in the environment… a footprint, I might add, that is already dramatically lower than any competitive product. (We will actually be proving this very soon… stay tuned.) However, in this release we’ve made some changes that would be considered significant, even by us.

Redesigned Threading Model

Two changes have been made to the threading model. Previously, dedicated threads were spawned for each monitored server, and each would consume memory, so the more servers you were monitoring, the more memory would be used by the SQL Sentry Server service process (you need one of these services for approximately every 100 or so monitored servers). A new thread pooling system is now in place that eliminates most of these dedicated threads, dramatically reducing the process’ memory footprint and increasing scalability at the same time.

Next, for some of the collection subsystems we’ve moved from a fork/join threading model to more of an asynchronous work queue model. Fork/join threading is similar to parallelism with SQL Server. There are some benefits to this approach, but a primary drawback is that the process can only be as fast as the slowest thread. The new model effectively eliminates the possibility of collection delays caused by offline servers, network issues, etc., that we’ve sometimes seen affect other critical SQL Sentry processes like chaining and alerting.

Reduced Plan Cache Footprint

Prior to v5.5, ad hoc SQL was being used in some places by both the SQL Sentry Console and Server, and in certain scenarios over time this could lead to plan cache bloat and some buffer pressure on the server where the SQLSentry database was installed. In v5.5 we’ve eliminated almost all uses of ad hoc SQL, which dramatically reduces the plan cache footprint of the SQLSentry database. This will not impact monitored servers, only the server where the SQLSentry database resides (unless of course it is monitored as well ;-) After you upgrade, I recommend that you run DBCC FREEPROCCACHE on that server so the buffer can immediately reclaim any of the space previously used for the ad hoc cache.

I really hope you enjoy using v5.5. New users can get bits here, existing customers please login to your portal account. As always, we fully support upgrading to this build from any earlier SQL Sentry version, and from this build to the final release of v5.5. Thanks to everyone who has shared their ideas with us, our great beta testers, and of course to the entire SQL Sentry team for your efforts in finally making these great new features a reality!

Analysis Services and the Windows File System Cache

2010-02-05T17:50:00.006-05:00

In my last post I revealed a problem one of our customers encountered when SSAS memory limits were inadvertently set to KB instead of bytes, leading to abnormally low limits and some pretty serious performance issues. I alluded to a “saving grace” that prevented things from being much worse than they actually were, and that’s what I’ll cover in this post.

A little background first, or rather an expansion of something I touched on in my original post -- unlike the SQL Server Database Engine, much of the SSAS data can be resident in physical memory by virtue of the Windows file system cache. This is simply because SSAS databases consist of a collection of Windows files of various types, and SSAS does not directly manage memory/disk access to those files using Windows APIs as complex memory managers like SQL Server do.

This means that even if the SSAS internal caches are effectively non-existent, you can still have a significant amount of raw SSAS file data loaded into RAM which will not show up as part of the SSAS process memory, or in any way be governed by the SSAS memory limits. This was the saving grace here. If the storage engine actually had to read the files from disk for every query, performance would have been significantly worse than it already was.

I’m going to use the some Performance Advisor for Analysis Services dashboard charts to fully illustrate this phenomena. Since the customer’s server has 32GB of RAM, I’ve blown up the System Memory chart so it’s a little easier to see what’s going on:

The thin grey line shows the physical memory used by the file cache (about 700MB), and the even thinner orange line shows the memory used by the SSAS instance with invalid memory limits (about 450MB). The large teal-colored area is memory used by a SQL Server instance on the same server.

Assuming there wasn’t much file serving happening on this server, most of the file cache is likely comprised of SSAS database files. Without file serving activity, usually you will not see the file cache get this large, even though on 64 bit machines like this one it is capable of growing to use up all available memory. (On 32 bit systems the max file cache size is about 1 GB.) Read more about the Windows file cache here.

Ok, interesting theory, but how can we confirm that SSAS was able to get it’s data from the file cache? Fortunately the SSAS Storage\File Read/Write charts make this is pretty easy:

The File Queries chart on the right shows SSAS is querying database files at a rate of about 20 per sec, and the File Read KB chart shows that even though there is a lot of file read activity on the system (about 40MB per sec), almost none of it is coming off of disk. This is clear because the dashed orange Total Disk line is flat, and there are also no Windows read faults at that time:

Anytime SSAS files are read from disk into physical memory, you should see hard faults. To better demonstrate this, take a look at a similar chart for a different environment:

The off-white Total System series shows ALL file read activity flowing through the Windows system (both from memory and disk), the light orange Total SSAS series shows the portion of total read activity directly related to the SSAS process (barely visible here), and the blue series shows the portion of SSAS read activity from data files. The dashed Total Disk read line is overlaid on top of the stacked area chart so you can quickly see how everything correlates. It’s clear that a sizable chunk of the SSAS data file reads are coming off disk. Also note how this time we have significant Windows read faults:

To get a different look at the data as well as the actual file cache hit ratios, if we click on a point on the chart in the middle of the read activity spike, then click the Sample button on the toolbar it loads up the data for that specific point in time:

What this shows us is that approximately 81% of the file read activity for this sample came from the file system cache. This is not bad, but in general you always want Total Disk to be at or near zero, which will be the case whenever SSAS is pulling data from either its internal caches or the file system cache.

How can you tell whether the data is coming from SSAS’s internal caches versus the file cache? That’s easy -– you won’t see any storage engine file queries or associated file read activity at all, and the SSAS cache hit ratios (visible in sample mode, or when using the new Performance Advisor v5.5 reporting) will be high, such as in the shot below where the measure group cache hit ratio is 99%:

In addition to the cache hit ratio meters, there is also a visual hit ratio indicator right on the column chart -– the “fullness” of the columns reflect the hit ratios. The more gray fill you see between he column hash lines, the higher the hit ratio:

Note that the measure group column is only about half full, reflecting the 52% hit ratio. The calculation and flat cache hit ratios are both zero, so those columns are empty. Clever, eh? ;-) Perhaps even cleverer, on the measure group column there are two types of hash lines, horizontal and diagonal -- the horizontal hashes represent direct cache hits, while diagonal hashes represent filtered hits. These occur when existing cache entries have to be filtered first to obtain the desired data, and are slightly higher overhead than direct hits.

In an ideal world, aside from when the cache is first warming up, you don’t want to see SSAS file queries at all, except perhaps for the occasional aggregation hit, which is also shown on the File Queries chart. Bottom line, if you see file queries there were cache misses. Here’s a quick custom Performance Counter History report using Performance Advisor v5.5 which shows this clearly :

Even so, the Windows file cache hit ratio may be 100% if all of the referenced files were in the file cache. But in that scenario, as we saw in the last post, even though performance will be better than if they were read from disk, it will not be nearly as good as if the data had been found in the SSAS cache because of the additional overhead involved when the storage engine retrieves file data from Windows.

If you want to see how your Analysis Services system is performing in this regard, you can download a fully functional 15-day trial of Performance Advisor for Analysis Services here. Cheers.

Analysis Services Memory Limits, Part II

2009-06-19T18:33:00.001-04:00

In my last post I discussed some of the basics of SSAS memory management -- how it utilizes limits to control the amount of memory consumed by the SSAS process, and how the memory limits are set as a percentage of the total physical memory. I also mentioned that we had a beta tester who reported what they thought was an odd graphical anomaly on the Performance Advisor for Analysis Services “Memory Usage” chart, and I offered up a challenge to see if anyone could explain it. (Details about the winner at bottom.)

I’ll begin with an admission -– I intentionally left out a rather critical aspect of memory limit configuration. The omission was that, like several other SSAS settings, once the configured value goes over 100% it changes to an explicit setting. In the case of memory limits the explicit setting is translated as bytes.

Unfortunately, for some reason this is not covered in the SSAS documentation… however, it is covered elsewhere, including this doc: SQL Server 2005 Analysis Services (SSAS) Server Properties

In this case, the first thing we had the tester check was the actual configured setting values, as well as the values being returned from the associated Windows performance counters as a sanity check.

Here is what they sent back:
Memory Limit Setting –> Configured Value (bytes) –> Perf Counter Value (KB) Memory\LowMemoryLimit –> 2097152 –> 8192
Memory\TotalMemoryLimit –> 13531488 –> 13312

Hopefully the problem is starting to become clearer. Apparently the configuring user had tried to use an explicit amount of memory, however they had inadvertently entered kilobytes (KB) instead of bytes!

These values translated to approx 8MB and 13MB for the Low and Total limits respectively, which explains what they were seeing on the chart. It was no graphical anomaly. (Note that even though the configured Low value should have been approx 2MB, apparently the minimum SSAS will use is 8MB, which explains the associated perf counter value of 8192 KB.)

Ok, so we figured out what was going on on the chart, but what about the impact these abnormally low settings would have on SSAS performance? As I mentioned before, this client had had many problems with SSAS performance, and as a result had engaged Microsoft to come in and help.
Here’s where things get very interesting. First, take a look at the dashboard shot the client sent to us, which shows the memory issue along with the execution of a single MDX query that ran for a couple of minutes:

(click to enlarge)

Here’s a closeup of the Memory Usage chart:

What’s wrong with this picture? Well, a couple of things. First, SSAS memory usage is almost completely flat, and is almost entirely nonshrinkable. Typically SSAS memory will grow and shrink continuously as memory is allocated and deallocated for querying and processing operations, and will stabilize with a much larger base proportion of shrinkable memory than we see here.
To illustrate this better, on a test SSAS instance I configured the limits artificially low and let it run for a while, then increased the limits much higher so that there was no memory pressure:

The same system, zoomed into a 10 minute range:

What the flat memory line means is that the unusually low limits are effectively preventing SSAS from loading any data into either of its caches -- the Formula Engine (FE) cache (aka, “query engine cache”) or Storage Engine (SE) cache.

This is further confirmed by looking at a few other charts. Notice the Cache Activity chart shows almost continual evictions and memory shrinkage while the query is running:

This is not normal! You rarely want to see these values above zero, because when you do it means that memory cleaner threads have kicked in because of internal memory pressure and are moving data out of memory. In this case the data is being moved out as fast as it is moved in.
Also have a look at the Avg Time chart:

What this shows is that SSAS is spending almost all of it’s time on non-cached storage engine operations (SE non-cached), which is where you typically want it to spend the least amount of time. This means that every time a query needs data it’s unable to find it in the FE cache, so the formula engine issues requests to the storage engine, which is unable to find the data in its cache, so it has to go to the file system to get it from either measure group partitions or aggregations. Partitions can be orders of magnitude slower than retrieving the data directly from cache, and is almost certainly why the query took so long.

As an aside, originally I’d actually called this the “Wait Time” chart since what we are showing here is similar to what you see with SQL Server wait types, but Carl Rabeler convinced me otherwise since what the chart is actually showing is “time spent” on the different operations, versus time spent waiting. You could probably get into a semantical debate about this since many of the SQL Server wait types pretty much show “time spent” as well… but it’s just not worth any potential confusion from injecting some Database Engine terminology into the Analysis Services lexicon, no matter how tempting it may be ;)

In any event, this chart is critical because you can see exactly where SSAS is spending most of it’s time over any date range, which can be extremely important for profiling a server and determining where the bottlenecks lie.

Next have a look at the File Queries chart, the Total metric in particular:

As I mentioned, the formula engine generates requests to the storage engine for measure group data when it’s not in the FE cache. The total queries metric shows how many times the storage engine had to go to the file system to read data from a measure group partition (or aggregation) when the data wasn’t in the SE cache. Here again is another value you typically want to be as close to zero as possible, because when it’s >0 it means that SSAS couldn’t find the data in either the FE cache OR the SE cache.

Note that you will see values >0 when an aggregation is first hit and loaded into the SE cache, since Total covers both Agg hits and partition queries. But since agg hits are much lighter weight than partition queries this is generally not a problem.

It is also perfectly normal to see values >0 when the cache is cold and queries first start coming in. For example, if you process your cubes overnight the associated cache data will be invalidated, and you’ll probably see file queries spike up in the morning when users come in and start issuing MDX queries, then settle down as the data is cached. If you see that total queries are continually above zero throughout the day, memory pressure is a likely culprit.

Impact on MDX Query Performance

So what happened after the invalid memory limit settings were corrected? Here is some actual query data from the Performance Advisor Top Queries tab which our beta tester was nice enough to export for me:

(click to enlarge)

The queries in red ran with the invalid low memory limits. Notice every single one had to go to the storage engine for the data every time. All had 53 non-cached operations, high cpu cycles, durations of >10 seconds, and all spent 94-97% of that time in the storage engine, for obvious reasons.

Once the limits were changed, the 2nd execution of the query (green) took about 1.5 seconds, with zero non-cached ops, very low cpu, and 100% of the time was spent in the formula engine! This is a fantastic example of how things are supposed to work when there is memory available and limits are configured properly. Most future runs of this query actually dropped under our default trace filter of >=1000ms, and so no longer showed up at all in Top Queries.

Note that the first run of the query after changing the memory limits (orange) actually took longer than the runs with the invalid limits, although everything else was about the same. At first I was puzzled by this, but then it occurred to me that this may be because SSAS was finally able to actually load the data into cache, and the extra 6 seconds may just be overhead related to the associated memory allocations. Or it may have just been an anomaly related to other activity on the server at the time, not sure.

Even though the performance before the change was pretty bad, things actually could have been much worse. There was one very big saving grace for this client that allowed SSAS to run as well as it did with the invalid memory limits. It’s actually very clear on the dashboard what that is, but I’ll save that one for another post ;)

Mini-contest Winner!

Congrats to Norbert Kessler from WebTelligence, a SQL Server consulting firm in Germany and one of our beta testers, for nailing the answer pretty much right on the head. He wins the free license of Performance Advisor for Analysis Services. John Welch was very close, and actually brought up another pretty serious misconfiguration -- when the high and low limits are configured with the same value. This is a common practice in the SQL Server world, but a big no-no with SSAS. Another topic for a future post perhaps…

Analysis Services Memory Limits

2009-06-15T12:21:00.001-04:00

One of the least understood areas of Analysis Services performance seems to be that of memory limits – how they work, and how to configure them effectively. I was going to start with an overview of Performance Advisor for Analysis Services (aka, PA for SSAS), but during the beta I ran across such a good example of the software in action in diagnosing a problem with memory, I think it’ll work just as well to go through that issue step-by-step.

First some background. SSAS has two general categories of memory, shrinkable and nonshrinkable, and they work pretty much like it sounds. Shrinkable memory can be easily reduced and returned back to the OS. Nonshrinkable memory, on the other hand, is generally used for more essential system-related stuff such as memory allocators and metadata objects, and is not easily reduced.

PA for SSAS shows both types on the SSAS Memory Usage chart:

If you switch to “Memory by Category” mode via right-click menu, you get a more detailed breakdown. Just like the chart above, the memory with hash marks is nonshrinkable:

SSAS uses memory limit settings to determine how it allocates and manages its internal memory. Memory\LowMemoryLimit defaults to 65% of the total available physical memory on the machine (75% on AS2005), and Memory\TotalMemoryLimit (also sometimes called the High Memory Limit) defaults to 80%. This is the total amount of memory that the SSAS process itself (msmdsrv.exe) can consume. Note on the charts above, the orange line represents the Low memory limit and the red line the Total memory limit. This makes it very easy to see how close SSAS’s actual memory consumption is to these limits. Once memory usage hits the Low limit, memory cleaner threads will kick in and start moving data out of memory in a relatively non-aggressive fashion. If memory hits the Total limit, the cleaner goes into crisis mode… it spawns additional threads and gets much more aggressive about memory cleanup, and this can dramatically impact performance.

Between the Low and Total limits, SSAS uses an economic memory management model to determine which memory to cleanup. This model can be adjusted by some other parameters related to the memory price, but I’m not going to get into those here.

When it comes to memory management SSAS is entirely self-governing, and unlike SQL Server it doesn’t consider external low physical memory conditions (which can be signaled by Windows) or low VAS memory. This may be partly because SSAS is already much more subject to Windows own memory management than is SQL Server, since Analysis Services databases are a collection of files on the file system and can make heavy use of the file system cache, whereas SQL Server does not. This means that you can have a significant amount of SSAS data loaded into RAM by virtue of the file cache, and this will not show up as part of the SSAS process memory, or in any way be governed by the SSAS memory limits. (For this reason we now show system file cache usage and file cache hit ratios on the dashboard as well so you can see this.)

On a side note, a byproduct of this design is that if available memory is low and other processes on the server are competing for memory resources, SSAS will pretty much ignore them, and look only to its own memory limit settings to determine what to do. This means, for example, that memory for a SQL Server using the default “Minimum server memory” setting of 0 can be aggressively and dramatically reduced by an SSAS instance on the same machine with default memory limits, to the point where the SQL Server can become unresponsive. SQL Server is signaled by Windows that memory is low, and responds accordingly by releasing memory back to the OS, which is then gobbled up by SSAS.

Graphical Anomaly, or Serious Memory Problem?

Very early in our beta for PA for SSAS, a beta tester reported what they thought was an odd graphical anomaly in the bottom right of the SSAS Memory Usage chart:

To the untrained eye, this would certainly look like an anomaly, but once you’ve seen what this chart is supposed to look like, it’s fairly obvious that it’s an “H” on top of an “L”.

This particular tester had been eager to get the beta because they had suffered from poor performance and other strange behaviors for quite some time. They had actually already scheduled Microsoft to come in and help troubleshoot the issues.

What exactly did this “anomaly” mean? What caused it, how it was resolved, and what was the net effect on performance, if any? All great questions… and this will be the subject of my next post, so stay tuned ;-)

If anyone has any guesses in the meantime feel free to comment. The first person to get it right will win a free license of Performance Advisor for Analysis Services! (that’s a $2495 value)

More Fun with v4.1...

2008-10-08T18:40:00.004-04:00

I mentioned in my last post how some users weren't even aware of the Performance Advisor dashboard's History mode because of issues with the toolbar buttons. What I didn't mention was that for some an even more significant "hidden" feature was Performance Advisor itself!

First some background. Both Event Manager and Performance Advisor share the same code base, so you only need one console, service and database for both products. There were many reasons for taking this approach, including ease of installation via a unified setup program, ease of configuration, and seamless integration possibilities between the products. For example, we can show you all performance events (heavy SQL, blocks, & deadlocks) on the Event Manager calendar without any configuration (as long as you have a license for both products).

We also enable you to quickly jump back and forth between Event Manager and Performance Advisor via context menus and toolbar buttons. These menus are shown in both the Event Manager calendar shot above where a block is selected, and below with the same block selected in Performance Advisor. (I should mention that the easiest way to get from a block on the calendar to the same block in Performance Advisor is actually to double-click it, or right-click and select Open.)

Note how the two products give you a completely different yet complementary view of the same event. Performance Advisor shows you all of the great details of all versions of the block chain, while Event Manager shows you in chronological fashion how the block was interacting with all other events around the same timeframe (heavy SQL, SQLAgent jobs, SSIS, maint plans, etc.). You can stare at a grid for hours and not gain the same level of understanding about a performance event as you get after looking at the calendar for a few seconds. You'll see relationships between events that you never new existed... which enables you to be exponentially more effective when troubleshooting and resolving difficult performance problems.

Hopefully by now you can see some of the great benefits of a single SQL Sentry console for both products. However, a downside of the single console approach is that, well, there is only one console. Let me explain. If each product was its own Windows application, the separation between the products would be quite clear. There would be little chance of confusion regarding how to monitor (or "watch" in SQL Sentry lingo) a server with each product, how to open the primary screen for each product, etc.

But since we leverage the same Navigator pane that we've always had in Event Manager, and it looks pretty much the same as before, some users had difficulty figuring out how to pull up Performance Advisor. At first we were puzzled by this, since if you use the context menus it's clear that there are now two sets of Watch and Open child menu items on computer (aka Device) and SQL Server nodes, and you are forced to pick one or the other:

Ah, but there was yet another way to open the Event Manager calendar in previous versions -- via double-click of the left mouse button. As it turns out, many existing users had become accustomed to using this approach since it's faster/easier. Therein lied the "problem". Although in v4.0 we had added a User Preference to control the "default view" (or product) when left-clicking a node, we had set this to "Event Manager" for upgrades to avoid changing behavior for existing users. So if you were a left-clicker, unless you went into the User Prefs and adjusted this, you'd continue to get the Event Manager calendar and never see Performance Advisor! Right. 2+ years of effort down the drain :O

After many hours of careful deliberation, to address this problem we decided to introduce a new feature in v4.1 called the "Product Selector" ;) Now what you will get by default when you double-click a computer or SQL Server in the Navigator is this screen:

The screen actually serves a few purposes: 1) to let you know you have multiple products available, 2) to allow you to continue to double-click and let you choose which product to open, and 3) to allow you to adjust the new "default view" User Pref via the "Default" column checkbox.

NOTE: All Event Manager users who upgrade to v4.x get a fully functional 30-day, 5-server trial for Performance Advisor for your efforts, so #1 applies to you as well.

#3 only comes into play if you deselect the "Always prompt me" checkbox, after which point you will no longer see the Product Selector... we'll just open the product you checked as the default.

We've already found that this in itself is causing some confusion though! Some users expected the "Default" checkbox to control which product is pre-selected the next time you see the form, which it does not. To avoid this, in v4.2 we've simplified the form slightly; we've eliminated the Default column and just use the currently selected product as the default if you ever deselect the "Always prompt me" option. In addition, the last product selected will always be pre-selected the next time you open the form.

It's truly amazing to me that I was able to fill up an entire blog post with a relatively insignificant topic... although it is indeed typical of types of issues I spend much of my days on. Hopefully someone out there will find it helpful ;) Please bear with, this whole blogging thing is still new to me. I promise to extricate myself from the day-to-day details of UI design & usability issues soon and delve more into how to use SQL Sentry to solve real world performance issues. Until next time...

SQL Sentry v4.1 Released!

2008-10-03T12:38:00.000-04:00

Well, I've thought it would be a good idea to do this for some time now (i.e., blog)... but I can honestly say that it wasn't until now, with the v4.1 release behind us, that I've felt like the time spent on a blog would be better spent (or at least as well spent) as time spent working on our software. I know, lame excuse, but true nonetheless ;)

So without further ado, I'll get on with what we've been doing for the past few months. The new v4.1 release contains 20 new features and almost 60 fixes over the previous build that's been public since early August (v4.0.0.48). The complete list can always be found here. A huge focus for this release was streamlining and generally improving the initial setup process including the Quick Start wizard, as well as usability improvements for Performance Advisor.

We received a tremendous amount of feedback on v4.0.x builds, and many of the resulting changes have gone into v4.1. We've put out several incremental releases since the initial v4.0 release, but the v4.1 code branch was actually started before many of them because some of the changes were significant enough that we didn't want to run the risk of regressions. That said, v4.1 is undoubtedly the most thoroughly tested build we've ever released. We always test hard, but this time we set a new record for release candidates with 12!

An invaluable part of the feedback process for v4.1 involved onsite visits with many customers and evaluators both inside the U.S. and in Europe. We took the time to actually sit down with folks and go through the install and setup process with them in their own environments. Then we'd go through the various modules of the product to see which elements were causing confusion or usability problems. It's tempting to try and solicit this type of feedback exclusively via web meeting, phone, or email since it's generally "easier", but there is simply no substitute for face-to-face interaction. You see things and obtain feedback that you never would otherwise.

Setup Improvements

I'll start with one of the major setup improvements. In v4.0.x if you were trying to monitor a server with Performance Advisor for the first time and the service user didn't have the necessary rights on the target, you might have seen a cryptic ACCESS_DENIED error along with hex codes and stack traces from WMI. In v4.1 you will now see a friendly message like:

WMI access was denied. Please ensure the SQL Sentry Server user account has Windows Administrator privileges on the target server.

Seems simple enough, but inventorying the myriad error codes that can come from the various subsystems, classifying them, and creating friendly messages for each actually took significant effort.

Stack traces can be a bit scary for users -- even though we were simply relaying handled exceptions back to the user about the security (or other) problem, to the user it might look like a bug in the software itself. We obviously want to avoid that type of confusion whenever possible, and even better, help users to resolve the problem instead of having to contact our support group, or worst case bail out of the install.

Dashboard Improvements

Chart Resolution

We've also made many improvements to the Performance Advisor dashboard. For example, some users expressed confusion about how the apparent granularity of the performance charts would change when zooming out from say, a 30 minute view, to a 30 day view. In a 30 minute view we show the actual raw data, which for many metrics like SQL activity is collected every 10 seconds. Unfortunately we can't show a month's worth of 10 second samples on all of the dashboard charts -- trust me, we tried early on ;)

We gave up pretty quickly though when we realized that not only does it incur a major hit in rendering performance, it's just TMI -- the human brain simply can't effectively synthesize >250,000 points on a single simple line chart, let alone 250K times the number of series on the charts, times the number of charts on the dashboard. This would defeat the whole purpose of what we are trying to accomplish with the dashboard. Which is, by the way, to provide for the first time in history a single screen to which a DBA can look to determine in seconds where performance bottlenecks occur on a server, for any point in time. Much more on this topic in future posts…

We spent a lot of time developing the Performance Advisor background processes that continuously roll the raw data up into different "break levels", or aggregates. Depending on the size of the active date range, you may be looking at 2 minute, 10 minute, 30 minute, or 4 hour aggregates. Think about how Google Maps seamlessly changes the resolution of the map when you zoom in and out -- it's pretty much the same principle.

Back to the improvement -- we now show you the active break level, so you'll know whether you are looking detail or aggregated data when zooming:

Point and Range Selection

In the same area you'll notice fields for Sample Pos(ition), and Range Start/Range End. In the previous release, you could highlight a point or a range on one chart and the point or range would be synchronized across all other charts. This enabled you to easily correlate what was happening across multiple charts and metrics. However, we found that a big usability problem was that the default action after lifting your mouse was always to zoom in. The caused you to lose your place, so then you'd have to zoom back out and start over again.

In v4.1 we now show you exactly which point and/or range is being selected in real time, and instead of auto-zooming, we present several context menus which allow you to jump directly to one of the other Performance Advisor tabs with the selected range to see exactly which heavy SQL, blocks, or deadlocks occurred during that range. You can also jump directly to the Event Manager calendar for the selected range. When you go back to the dashboard, your original master range and range selection is still active and can be changed as often as needed, without losing your place!

Another nice thing we are doing is persisting the selected points/ranges while navigating around or zooming in/out.

New "History" Mode!

Well, not really. The dashboard has always had two modes: Sample and History. Sample mode displays bar charts for a single point in time (or sample), while history mode changes over to line-type charts in order to display multiple points across a date range. The default when you open the dashboard directly from the Navigator pane (versus jumping to the dashboard from an Event Manager calendar) is sample mode showing realtime data, where the bars are continuously moving as new data comes in.

One of the amazing things we saw when visiting clients is that many didn't even realize there was a history mode! This is also a perfect (and somewhat frightening) example of something that we may have never seen otherwise. Don’t get me wrong, realtime sample mode is great -- it's very cool to see all of the colorful bars jump around -- but the real power of the dashboard is with history mode, and many users didn't even realize it was there!

The reason for this confusion invariably turned out to be the fact that we had a rather insignificant, depressible button on the toolbar for toggling between sample and history. So unless you knew to click it, or to change the date range on the toolbar and click Go, you may not ever even see history mode… ughh!

In v4.1 we've replaced this button with a colorful toggle button which changes both the graphic and text when you click it, clearly highlighting the mode change. We've also done the same for the neighboring Refresh and Start/Pause realtime (or auto-refresh) mode buttons.

Seems rather simple and obvious I know. This one actually was simple to implement -- but it does serve as a good example of how such a small thing can lead to a big usability issue… as well as how we are always listening in an effort to improve the product, in ways both large and small ;)

Block Versioning

Now, moving off of the dashboard onto the Blocking SQL tab… In v4.0 we had block detection of course, but now instead of showing each detected version of a block as a separate entry, we group multiple versions of the same block together, and allow you to flip through the different versions via a new Version column with embedded dropdown control:

This can dramatically reduce the noise in many scenarios. Likewise, we now only show one instance of the block on the Event Manager calendar instead of multiple, and only send one email for all versions of the same block. (A more accurate way to put this is we only fire any actions configured for the Blocking SQL condition once for each unique block).

Actions and Settings Config

Another big improvement in v4.1 is we now enable easy configuration of all Performance Advisor-specific actions or settings from the associated Performance Advisor tabs, versus only from the SQL Server node in the Navigator pane. For example, you can access all of the settings for Top SQL, like the minimum duration, statement collection, etc., from the Top SQL tab. If you're on Top SQL, simply hover over one of the Settings or Actions tabs on the right-hand side of the screen and it will expand to show you available settings:

You can also access them by select the event source nodes in the Navigator pane... assuming you are also watching the server with Event Manager, which is required for these nodes to show up:

That’s all for now. I’ll have more coming on v4.1 improvements and other great info about how to get the most out of SQL Sentry software soon...