Consolidation Planner
Overview
Migration Planner sizes a new server for one instance. The Consolidation Planner answers the question that comes up when several instances are moving together: will these fit on one new server or VM, and how many cores do we need to license? It uses the history Historic Monitoring already collected, peaks and percentiles rather than guesses.
- Step 1: check the instances to put together (filtered by server group) and pick the period.
- Step 2: describe the target: cores, how fast its cores are, memory, storage, IOPS and MB/s.
- Step 3: read the result: a chart of the combined load with the target line, a fit table with Pass, Tight or Fail for each resource, a license estimate, and a row for each instance.
The plan exports to HTML (with the chart, to hand to a customer) or to Excel.
Where to find it
| Route | How |
|---|---|
| Menu bar | Tools → Consolidation Planner |
It opens in a window of its own, so the main window stays usable while it reads.
Step 1: instances and period
The list holds every instance connected in the server tree, with its server group in brackets. Pick a group at the top to show only that group, and Check All or Check None to check what is shown. Instances that are not connected are listed but cannot be read.
Pick the period: the last 7, 14, 30, 60 or 90 days, or Custom. By default the planner uses the CPU SQL Server used; check Use the whole server’s CPU to plan with the whole server’s CPU instead (other processes on the old servers included).
Click Read History. The instances are read one at a time on a background worker; Cancel stops after the one being read, and the plan is made from the instances read so far.
Where the history comes from
Each instance is read from its own DBHealthHistory database, so Historic Monitoring has to be collecting on it:
| Resource | Where it comes from |
|---|---|
| CPU | dbo.CPU, one sample a minute from the scheduler monitor ring buffer, averaged per 15 minutes |
| Memory | dbo.PerfCounterOverTime (Total Server Memory), the most per 15 minutes; with no memory history, the memory SQL Server holds now |
| Page life expectancy | dbo.PageLifeExpectancy, lowest and average over the period (shown per instance) |
| I/O | The file statistics snapshots in dbo.trackingOverTime, turned into IOPS and MB/s |
| Storage | Data and log files now (sys.master_files), and at the start of the period from dbo.databaseFileSizeHistory for the growth rate |
| Backups | The latest full backup of each database, from msdb (shown per instance) |
| Cores and memory settings | sys.dm_os_sys_info, sys.dm_os_sys_memory and max server memory |
Each part is read on its own. A table that is missing, too old or refused only drops that resource for that instance, with a note in the instance’s row.
Step 2: the target
| Field | Meaning |
|---|---|
| Cores | Cores on the new server or VM |
| CPU speed factor | How fast one target core is against the current servers’ cores. 1 is the same, 1.25 is 25% faster (compare benchmark scores such as SPECint rate per core) |
| Memory (GB) | Memory for SQL Server. Leave room for the operating system on top |
| Storage (GB) | Space for data and log files |
| IOPS | The I/O per second the storage can do |
| Throughput (MB/s) | The MB per second the storage can move; 0 leaves it unchecked |
| Storage growth to allow (months) | How much of the recent growth to add to storage |
Changing the target recalculates straight away from what was already read.
How the numbers are worked out
- CPU in cores: cores used on the target = CPU percent x logical CPUs / 100 / speed factor.
- Lined up in time: every series is put in 15 minute buckets, and the instances are added up bucket by bucket. Two instances that each peak at 4 cores at different times combine to less than 8. The worst case column is the sum of every instance’s own peak, as if they all lined up.
- Gaps: only buckets where every instance with data has a sample are added up. Buckets where one of them has a gap are left out, and the fit table says how many. An instance with no data at all for a resource is left out of that resource and named in the note.
- P95 and P99 are nearest rank percentiles of the combined total over those buckets. Peak is the highest combined bucket.
- Storage is what the files hold now, then with the growth allowance: the growth per day since the start of the period times the months entered.
- Headroom = (target – combined peak) / target. Fail means the peak is over the target (the headroom is negative), Tight means under 20% headroom, Pass otherwise.
- History: an instance with fewer than 7 days of history in the period is warned about; a week is the least that shows a full weekly cycle.
Reading the result
The summary says whether the instances fit, the combined CPU peak and the worst case, and lists any warning and any instance that could not be read.
The chart shows one resource at a time (CPU cores, memory, IOPS or MB/s), each instance as a band stacked on the others, with the target as a red line and the combined P95 and P99 marked. Hover over it to see every instance at that time. Only the buckets used for the totals are drawn.
The fit table has a row for CPU, memory, storage, IOPS and throughput: combined P95, combined peak, worst case, the target, the headroom and Pass, Tight or Fail.
The license estimate is the target’s cores rounded up to 2 core packs, with a minimum of 4 cores per server or VM, and the cores the combined CPU peak needs with 20% headroom. The License and Edition Footprint button opens that report for the instance selected in the tree, to check which edition each instance needs.
The instance grid lists each instance: its group, whether it was included, logical CPUs, days of history, average CPU, peak cores, max server memory, peak memory, lowest page life expectancy, storage, growth per day, the size of its latest full backups, peak IOPS and MB/s, and notes. A low page life expectancy means the instance was already short of memory, so its memory figure is a floor.
Exporting
Export to HTML writes one page with the summary, the target, the chart, the fit table, the license estimate and the instances. Export to Excel writes a workbook with a Plan sheet, a Fit sheet, an Instances sheet and the combined CPU by 15 minutes. Both grids also have the usual right-click export and copy.
Permissions and versions
- Each instance needs a DBHealthHistory database this login can open, and the instance listed in its own
instanceMonitor. History kept for an instance in another server’s DBHealthHistory is not read. - The CPU count and Total Server Memory need VIEW SERVER STATE; the file sizes need permission to see
sys.master_files; the backup sizes need read access to msdb. Anything that cannot be read is a note, not an error. - Works on SQL Server 2008 and later.