Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 MSSQL 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 MSSQL 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 MSSQL 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 TSQL 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 intersting 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?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

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?

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.
share|improve this answer
    
Thank you Mark, +1 vote for helping me to better define the question. Since the servers don't belong to us, everything we do has to be reporting only –  Craig Efrein Feb 3 '12 at 14:54
    
Can you use Management Data Warehouse to monitor SQL2005 or SQL2000 instances? –  datagod Feb 6 '12 at 18:43
    
Absolutely. I did have to seperate each server into a group based on its version to avoid errors. –  Craig Efrein Feb 7 '12 at 6:36
    
I updated the question. –  Craig Efrein Feb 7 '12 at 7:05

A tool that I have used for SQL performance is PAL. It gives you decent explanations about your current server's performance issues. It uses Perfmon data, and hence cannot be run via TSQL (although there are DMV's you can use to get Perfmon counters).

You will have to setup different data collectors for your different servers if you want to run this from a central location.

share|improve this answer
    
Thank you for that input. As mentioned eariler, I can not add anything to the client machines but a basic Windows login that has rights to also login to SQL Server. Any changes to the client's server would get me into trouble with Dad. I will check out PAL though. –  Craig Efrein Feb 7 '12 at 6:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.