Determining a LOW USAGE SQL Server

• aruram

  SSCrazy

  Points: 2379

  More actions

  February 9, 2009 at 1:07 pm

  #127452

  Hi,

  We are in the process of finding low usage SQL servers and are collecting the following counters:

  DMO TSQL @@ Connections

  DMO TSQL @@ CPU Busy (may overflow)

  DMO TSQL @@ Idle (may overflow)

  DMO TSQL @@ IO Busy (may overflow)

  DMO TSQL @@ Packets Received

  DMO TSQL @@ Packets Sent

  DMO TSQL @@ Time Ticks

  DMO TSQL @@ Total Disk Read

  DMO TSQL @@ Total Disk Write

  Computed Packets Received Per Day

  Computed Packets Sent Per Day

  Computed Disk Reads Per Day

  Computed Disk Writes Per Day

  The SQL servers are a mix, some of them being used as Reporting servers and some of them hosting other applications on them.

  What counters from the above list will help most to determine the low usage servers (best estimate)

  and what are the typical threshold values for those counters?

  ANy inputs are appreciated.

  Thanks

• HanShi

  SSC-Dedicated

  Points: 33508

  More actions

  February 10, 2009 at 6:19 am

  #941127

  I suggest to use Windows Performance counters for CPU, memory and disk-IO usage. Run it for a day or a week to get clear knowledge of the servers performance. This will include any other applications (non-SQL) running on the servers.

  This will also give you a baseline to which you can compare changes.

  ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

• Jonathan Kehayias

  One Orange Chip

  Points: 26778

  More actions

  February 10, 2009 at 9:01 am

  #941330

  For SQL I would also collect the Batch Requests/sec counter which will give you an idea of how busy SQL is alone. The server could be busy because of another app, but SQL is just nice and quiet.

  Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
  My Blog | Twitter | MVP Profile
  Training | Consulting | Become a SQLskills Insider
  Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736642

  More actions

  February 10, 2009 at 9:38 am

  #941375

  I tend to used CPU, users, and batches as my benchmarks. Run this across a few weeks, get samples on all days, exlude backup/maintenance times.

  Watch out for peaks. A backup can throw a 100% CPU spike, so don't measure that. Use averages to get an idea of how much the server is being used.

  I assume this is for consolidation of some sort. Be sure you have a backup plan to remove the db if you find that it overloads this server.