This is a continuation of my previous blog post (link here) from earlier this week, where I provided a high-level overview of the new Microsoft SQL Server Management Pack and installation procedures and issues. Now I will share performance details about this MP update.

Performance Improvements: After the deployment of this new Management Pack (MP), Operations Manager DW database contains a number of stored procedures:

scr2_1

These procedures are significantly enhanced compared to those in the previous release of dashboards. They now support Instance View in addition to Datacenter View, and all data are now returned to the dashboard already aggregated, resulting in improved performance.

I decided to test and compare the dashboard performance of the previous version with that of the current release.

For this test, I simulated an environment with 100 SQL Instances with 100 databases per instance, a total of 10,100 database objects (this is not a mistake, because there are 100 instances and 10,000 databases).

Discovered Parent objects:

scr2_2

Discovered Child objects:

scr2_3

MP generated random performance and monitoring data with alerts. There are 10 groups on the dashboard:

scr2_4

Each Group has a few tiles configured:

scr2_5

Instance level dashboard has tiles configured as well:

scr2_6

The old 6.6.0.0 MP version of the Datacenter dashboard loaded in 7 seconds the first time (cold), and in 4 seconds the second time (warm). The current 6.6.2.0 version loaded in 7 seconds cold, and 2 seconds warm.

scr2_7

This is not a major improvement because the previous release of Datacenter dashboard also worked with aggregated data, however, the dramatic changes are in the Instance View.

In this test I simulated 100 databases with 10 database files; this is 1100 database objects in total.

The 6.6.0.0 MP version of the Datacenter dashboard loaded cold in 30 seconds and warm in 23 seconds.

The 6.6.2.0 MP version loaded in 5(!) seconds and 2.5 seconds respectively – 10x performance improvement!

scr2_8

On a simulation of 5,000 database objects the 6.6.0.0 MP Instance dashboard never loaded.

The 6.6.2.0 MP Instance dashboard loaded cold in 10 seconds and 6 seconds warm.

scr2_9

Now the Instance dashboard loads about 10 times faster than in the previous release!!!

I hope you find all the above useful!

Stay tuned for VIAcode posts on dashboard improvements and reviews of other new features.

Like us on Facebook and follow us on Twitter!

P.S. I know some people are experiencing performance issues and errors with initial deployment, TempDB grown and transaction log grown on some environments. Additional information related to those issues can be found here:

Technet

SQL Release Service Blog