4

My company has a small SQL environment for LANDesk and they want to migrate all the servers to a single SQL cluster per region. What I want to know is, how do I determine the capacity for such a scenario? I have obtained the database growth per individual server (All Landesk) and can I take this data as a rough estimate for the consolidation in to the single SQL cluster? The Windows team tells me that the data from these servers is kinda wonky and might not give an accurate result what they want.

The new cluster in question already has a couple of databases but it is not at it's fullest capacity yet. Once they start adding the remaining data from the servers, it should reach it's full capacity. I have been running Perfmon on this for a while and for checking database growth I used the below TSQL code from sqlskills.com

SELECT
[database_name] AS "Database",
DATEPART(month,[backup_start_date]) AS "Month",
AVG([backup_size]/1024/1024) AS "Backup Size MB",
AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",
AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"
FROM msdb.dbo.backupset
WHERE [database_name] = N'AdventureWorks'
AND [type] = 'D'
GROUP BY [database_name],DATEPART(mm,[backup_start_date]);

Is there anything I can do to get an accurate result? The LANDesk team doesn't want me to use the older servers and the only option I have is to use the new cluster where they plan to migrate all the data and I only have 6 months worth of data on this. Should I ask them questions like number of years this new cluster be used? etc...

Thank you all for your answers. Here is what I have from the PAL tool

I used the PAL tool to determine perfmon data and these are some of the warnings it gave
1)Response time greater than 25ms 2) Less than 10% Idle Time - the disk queue has at least 1 outstanding I/O 90% of the time 3)Greater than or equal to 64 KB IO sizes. Generally, the larger the IO size, the more data can be transferred per second, but the response times are longer.

4)Physical disk read latency - greater than 25ms 5)Process % Privileged Time - SQL server using more than 30% of privilged mode CPU usage 6)SQL Server Access methods FreeSpace Scans/sec - A ratio of more than 1 freespace scan for every 10 batch requests - confirmed with High paglatches, async i/o completion and low page life expectancy 7)SQLServer:Access Methods Workfiles Created/sec - A ratio of more than 1 workfile created for every 20 batch requests 8)SQL Buffer cache hit ratio : Less than 97 percent buffer cache hit ratio 9)Red: Greater than 20 Lazy Writes per second 10)Red: Page life expectancy is less than 5 minutes (300 seconds) 11)Yellow : A ratio of more than 1 SQL Re-Compilation for every 10 SQL Compilations

Regarding I/O we are moving to SQL 2012 cluster so I am not sure which is another possible way to measure IOPS. Would you suggest SQLsim or IOmeter etc? Thanks again!

  • Disk space isn't your only issue with capacity planning. You also need to consider RAM, CPU and latency (especially around tempdb contention). – Randolph West Nov 5 '16 at 9:19
5

Like Randolph mentioned, disk is the kind of the last thing you need to worry about. If it's clustered as you mentioned, you can easily add storage to accommodate data growth.

The things that are harder to add and cost real money are:

CPUs (Licensing) Memory (If you're on standard edition, you're limited to 128 GB)

In a consolidation scenario, you need to get a baseline of CPU use (easy perfmon--SQL Server CPU) and memory--harder (you'll want to track to page life expectancy as it relates to total memory use by SQL). Finally, you'll want to tack IOPS--the performance component of storage--if your source environments are on SQL 2014 or higher, there is a counter you can check (disk read/write IO/sec) to get that data. The disk data will simply be cumulative, whilst the CPU and memory data will need to evaluated. In a consolidated environment, you'll probably use a little less CPU and memory.

Finally, for a shared environment like this, you probably want to use Enterprise Edition with Resource Governor so you can prevent the "noisy neighbor" problem.

  • I am using SQL 2012, can I go with SQLIOSIM or crystaldiskmark to calculate IOPS? Also, SQL Server is it a good idea to benchmark the storage? – user2923332 Nov 14 '16 at 18:34
2

If I understand your requirements correctly, you have multiple SQL Server instances that are currently hosting LANDesk databases and your firm wants to consolidate these instances into a single SQL Server Cluster instance with a database per region?

Yes, you would need to know how long the systems will be in place and if the disks can be expanded at a future date.

If you are only worried about disk capacity, assuming you know your avg.daily growth rate using a method of your choosing; the numbers for 3 months down the line can be calculated using:

(avg.daily growth*[daystocalc.for])*(1+x)-(existing free space in DB)

Where, x is a fraction indicating how much free space you want to have in your DB when you start planning for expansion. Let’s say you want 10% free on your disk and in your DB then, x is going to be 0.2.

If you need to do detailed capacity planning then you need to track CPU and Memory utilisation as well.

  • Thank you. I have added the results of my perfmon and from that data I am assuming that there is memory pressure because of Disk latency and also because of High PLE . There are also a number of tables where Indexes are not present. But this is a vendor based application.Would adding indexes make sense? – user2923332 Nov 8 '16 at 19:07
  • If the numbers for PLE are high, that is a good thing. I assume you mean the numbers for PLE are low, meaning a sql server page is not staying in memory very long. If you think adding indexes would help, you should run this idea by the vendor first to make sure that they don't have some indexes they advise you add which they have already tested. – rvsc48 Nov 8 '16 at 19:39
  • Yes PLE is low sorry. Also, I ran the sp_blitzindex today and it came up with a number of suggestions where number of indexes are missing. Do you suggest that I first check with the vendor first about this situation? – user2923332 Nov 9 '16 at 2:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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