Using Central Management Server to provide convincing performance statistics

Central Management Server

The company I work offers dedicated VMware hosted servers to its Windows clients. Among those Windows clients, there are essentially 60 single instance SQL Servers ranging in versions from 2000 to 2008. Among them you will find 40 Express editions, 10 Standard editions and several MSDE and workgroup editions.

Via a shiny new SQL Server 2008 R2 Standard edition configured as the Central Management Server, I can access every one of these remote machines simultaneously. Each server is registered in a group based on its version, 8, 9 or 10. This means that I can query each group of servers based on their version to avoid compatibility errors.

The problem

Normally, we can only sell the idea of increasing RAM, CPUs or moving SQL Server to a dedicated machine because the customer asked us to do so for N reasons.

The goal

I would like to use Central Management Server to go one step further by going to our customers and saying, if you did "this" then your website or application would be faster times X. Imagine an ASP web page that takes 3 seconds to load, by increasing the RAM, in other words by what the buffer pool really needs, that same page would load in 1 second.

I found this line of T-SQL code from Brent Ozar's Take Over an Unknown Server

```
SELECT @@SERVERNAME AS ServerName ,
YEAR(backup_finish_date) AS backup_year ,
MONTH(backup_finish_date) AS backup_month ,
CAST(AVG(( backup_size / ( DATEDIFF(ss, bset.backup_start_date,
bset.backup_finish_date) )
/ 1048576 )) AS INT) AS throughput_mb_sec_avg ,
CAST(MIN(( backup_size / ( DATEDIFF(ss, bset.backup_start_date,
bset.backup_finish_date) )
/ 1048576 )) AS INT) AS throughput_mb_sec_min ,
CAST(MAX(( backup_size / ( DATEDIFF(ss, bset.backup_start_date,

How can I, only using Central Management Server, provide convincing
performance related information for the individual servers managed
with it?

Unless I've missed a key feature somewhere along the line, you can't. What does it give you?

• Logical organisation of your servers and instances.

• Policy Based Management can be applied to server groups.

• Multi-server queries.

For performance data you'll want to look at installing the Management Data Warehouse alongside your Central Management Server. It isn't as useful as the various 3rd party tools for collecting SQL performance data but it's an improvement on roll-your-own Perfmon/SSRS/Excel solutions.


I would like to use Central Management Server to go one step further
by going to our customers and saying, if you did "this" then your
website or application would be faster times X.

If only it were that simple. You're going to be on shaky ground if you offer this sort of advice to customers, especially if you're sending them a bill for the proposed upgrades. You can certainly offer some insight but not guarantees:

• ServerX has a low page life expectancy, it would benefit from additional RAM.

• ServerY has increased from an average 50% to 70% CPU in the past month. Has anything changed or have you seen an increase in visitors/customers? Perhaps you should plan for an upgrade.

• ServerZ used an average 500IOPS at the start of the year, it's now reached 750IOPS.

StackExchange Database Administrators Q#12170, answer score: 5