Sql-server – 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,
                                                bset.backup_finish_date) )
                       / 1048576 )) AS INT) AS throughput_mb_sec_max
    FROM    msdb.dbo.backupset bset
    WHERE   bset.type = 'D' /* full backups only */
            AND bset.backup_size > 5368709120 /* 5GB or larger */
            AND DATEDIFF(ss, bset.backup_start_date, bset.backup_finish_date) > 1 /* backups lasting over a second */
    GROUP BY YEAR(backup_finish_date) ,
            MONTH(backup_finish_date)
    ORDER BY @@SERVERNAME ,
            YEAR(backup_finish_date) DESC ,
            MONTH(backup_finish_date) DESC
GO

This provides the kind of information I'm looking for.

Questions

The queries I've been using show general statistics about things like differences between the target and total memory, basic wait stats and surface area issues. That's great if you're in IT, but not very helpful or convincing for the guy who only cares about his web page or report opening up 2 seconds faster.

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

2. After finding interesting servers thanks to CMS, what 3rd party software do you recommend that is capable of providing decent performance graphs/charts without having to add anything to the client server or modify it in any way. Keep in mind, these servers always belong to someone else. We only have the right to monitor.

3. Question about security. I am using a windows login called dba who only has VIEW SERVER STATE for SQL Server 2005 and 2008. Is that safe? For SQL Server 2000, the user is simply able to log into SQL Server. Should I restrict his access in any way?