Microsoft Links for SQL Capacity planning [closed]

Posted on

Question :

I went through this website because we have migrations coming up pretty soon for Landesk Management Suite.

We are planning to move to SQL 2014 but the site’s contents are mostly relevant to SQL 2005.

Are there any other good websites or documentation from microsoft which I can go through to estimate I/O, disk and CPU planning before the Migration?

The url which I went through was this

Editing. I am trying this tsql code to get min,max and avg size of databases since 2015. Would this help?

SET NOCOUNT ON
DECLARE @endDate datetime, @months smallint; 
SET @endDate = GetDate();  -- Data atual
SET @months = 12;          -- Nr. de meses a analisar

;WITH HIST AS 
   (SELECT BS.database_name AS DatabaseName 
          ,YEAR(BS.backup_start_date) * 100 
           + MONTH(BS.backup_start_date) AS YearMonth 
          ,CONVERT(numeric(10, 1), MIN(BS.backup_size / 1048576.0)) AS MinSizeMB 
          ,CONVERT(numeric(10, 1), MAX(BS.backup_size / 1048576.0)) AS MaxSizeMB 
          ,CONVERT(numeric(10, 1), AVG(BS.backup_size / 1048576.0)) AS AvgSizeMB 
    FROM msdb.dbo.backupset as BS 
    WHERE NOT BS.database_name IN 
              ('master', 'msdb', 'model', 'tempdb') 
          AND BS.type = 'D' 
          AND BS.backup_start_date 
              BETWEEN DATEADD(mm, - @months, @endDate) AND @endDate 
    GROUP BY BS.database_name 
            ,YEAR(BS.backup_start_date) 
            ,MONTH(BS.backup_start_date)) 
SELECT @@SERVERNAME
      ,MAIN.DatabaseName 
      ,MAIN.YearMonth 
      ,MAIN.MinSizeMB 
      ,MAIN.MaxSizeMB 
      ,MAIN.AvgSizeMB 
      ,MAIN.AvgSizeMB  
       - (SELECT TOP 1 SUB.AvgSizeMB 
          FROM HIST AS SUB 
          WHERE SUB.DatabaseName = MAIN.DatabaseName 
                AND SUB.YearMonth < MAIN.YearMonth 
          ORDER BY SUB.YearMonth DESC) AS GrowthMB 
FROM HIST AS MAIN 
ORDER BY MAIN.DatabaseName 
        ,MAIN.YearMonth

Answer :

What I would think to do is see how your current server is performing by benchmarking the older hardware. This will allow you to get a good baseline so you do not go backwards from the migration.

To really hammer the disks have a few workstations use CrystalDiskMark and all point to the server, this will allow you to get concurrency testing also to simulate load.

As far as CPU, I default to Perfmon to see how the CPUs are holding up on the older hardware. If they are getting hammered then you either need to get more CPU or/and start tuning.

For the new server, Brent Ozar Unlimited has a first responder kit that has a good setup guide for SQL Server. However, he has a section in the setup guide that is a bit outdated, “Format the drives with 64K allocation blocks”. Honestly, I don’t believe this applies anymore 🙂

You can get this guide and a bunch of other goodies at First aid.

When you have the new server online, I would then start to have your QA team run through testing to make sure that everything works and that you are also getting the same or better performance from the server. Remember in 2014 we have a new cardinality estimator along with other features.

Leave a Reply

Your email address will not be published.