This is a continuation of my previous blog post 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:
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:
Discovered Child objects:
MP generated random performance and monitoring data with alerts. There are 10 groups on the dashboard:
Each Group has a few tiles configured:
Instance level dashboard has tiles configured as well:
The old 184.108.40.206 MP version of the Datacenter dashboard loaded in 7 seconds the first time (cold), and in 4 seconds the second time (warm). The current 220.127.116.11 version loaded in 7 seconds cold, and 2 seconds warm.
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 18.104.22.168 MP version of the Datacenter dashboard loaded cold in 30 seconds and warm in 23 seconds.
The 22.214.171.124 MP version loaded in 5(!) seconds and 2.5 seconds respectively – 10x performance improvement!
On a simulation of 5,000 database objects the 126.96.36.199 MP Instance dashboard never loaded.
The 188.8.131.52 MP Instance dashboard loaded cold in 10 seconds and 6 seconds warm.
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.
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: