New White Paper: SQL Server Performance Tuning in Google Compute Engine

You’re a database administrator, Windows admin, or developer. You might even be a marmot. You’re building your first SQL Servers in Google Compute Engine, and you’re stuck at the create instance screen. How many CPUs should you use? How much memory? How are you supposed to configure storage? Will it be fast enough, and what should you do if it isn’t?

Relax. Have a drink. In this white paper we built with Google, we’ll show you:

  • How to measure your current SQL Server using data you’ve already got
  • How to size a SQL Server in Google Compute Engine to perform similarly
  • After migration to GCE, how to measure your server’s bottleneck
  • How to tweak your SQL Server based on the performance metrics you’re seeing

We’re going to be using T-SQL to accomplish a lot of this, but you don’t need to be familiar with writing queries in order to follow along. The sample database and scripts are all located here.

We’ll even let you in on a secret: you don’t even have to be using Google Compute Engine in order to learn about SQL Server performance tuning methodologies covered in this white paper. These same techniques can be used for your on-premises VMs and bare metal SQL Servers, too. You might even be able to use them in other cloud hosting environments, if others exist. I’m really not sure about that part.

It’s available in our free First Responder Kit, and you can also get it from Google – and thanks for reading!

Previous Post
New White Paper: How to Build an Always On Availability Group in Google Compute Engine
Next Post
Announcing Google Managed PostgreSQL (and why SQL Server DBAs should care)

8 Comments. Leave new

  • Wes Crockett
    March 9, 2017 12:02 pm

    I may be completely ignorant but… What are some of the use cases where it makes sense to invest in Google Compute Engine for a SQL environment versus buying hardware for a local, physical, cluster or virtualization in ones own data-center? Thanks!

    Reply
  • Anthony Frampton
    March 13, 2017 8:48 am

    Thanks Brent! Its a good and interesting read.

    Reply
  • The pdf is a great read. Thanks for info and the scripts therein.

    Looking at your code on trending index maintenance…

    You do a datediff of seconds on the StartTime to EndTime yet call it Index_Minutes and you continue to refer to it as minutes. Your hour-long reorg was only 54 seconds

    Slightly off the point of this article but related to Ola and index maintenance — I also use Ola’s scripts and use the following queries to track index maintenance (yours will be to my list)

    select DatabaseName
    , IndexName
    –, Command
    , case
    when Command like ‘%REBUILD%’ then ‘Rebuild’
    when Command like ‘%REORGANIZE%’ then ‘Re-Organize’
    end as AlterType
    , StartTime
    , EndTime
    ,datediff(second,startTime,EndTime) [DurationInSeconds]
    ,ExtendedInfo.value(‘(/ExtendedInfo/PageCount)[1]’,’bigint’) as [pagecount]
    ,ExtendedInfo.value(‘(/ExtendedInfo/Fragmentation)[1]’,’numeric(7,5)’) as [Fragmentation]
    from CommandLog
    where CommandType = ‘ALTER_INDEX’
    order by DatabaseName, IndexName, StartTime

    select
    DatabaseName
    , ObjectName as TableName
    , IndexName
    , count(IndexName) as Maintained
    , sum(case
    when Command like ‘%REBUILD%’ then 1
    else 0
    end) as RebuildCount
    , sum(case
    when Command like ‘%REORGANIZE%’ then 1
    else 0
    end) as ReOrgCount
    from CommandLog
    where CommandType = ‘ALTER_INDEX’
    group by DatabaseName, ObjectName, IndexName
    — order by IndexName, StartTime

    I got the idea to add pagecount & fragmentation to my output from here: http://www.sqlservercentral.com/scripts/134092/

    Again, thanks for the great info

    Cheers

    Reply
    • Erik Darling
      March 16, 2017 7:20 am

      Ah, there must have been a script mixup. I was definitely looking in minutes the whole time. I’ll put that on the list of changes for revision. Thanks!

      Reply
      • One other error in the Index Maintenance query — The aggregation sums the page count along with finding the max. If there is more than one entry, the size will be off by the multiple of the number of times it was maintained

        WITH im AS (
        SELECT DatabaseName
        , Command
        , max(DATEDIFF(MINUTE ,StartTime, EndTime)) AS Index_Minutes
        , IndexName
        FROM master.dbo.CommandLog
        WHERE CommandType LIKE ‘%INDEX%’
        AND NOT DatabaseName IN ( ‘master’, ‘msdb’, ‘model’, ‘tempdb’ )
        GROUP BY DatabaseName, IndexName, Command
        )

        SELECT
        t.name
        , i.name AS IndexName
        , (a.used_pages *8) / 1024. AS [Index MB]
        , im.Index_Minutes AS WorldRecord
        , im.Command
        FROM sys.indexes AS i
        JOIN sys.partitions AS p ON p.object_id = i.object_id
        AND p.index_id = i.index_id
        JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
        JOIN sys.tables t ON t.object_id = i.object_id
        JOIN im ON im.IndexName = i.name
        WHERE t.is_ms_shipped = 0
        ORDER BY t.name, i.name;

        Cheers

        Reply
  • Argh…No Edit and bad copy/paste

    ;WITH im AS (
    SELECT DatabaseName
    , Command
    , max(DATEDIFF(SECOND ,StartTime, EndTime)) AS Index_Minutes
    , IndexName
    FROM master.dbo.CommandLog
    WHERE CommandType LIKE ‘%INDEX%’
    AND NOT DatabaseName IN ( ‘master’, ‘msdb’, ‘model’, ‘tempdb’ )
    GROUP BY DatabaseName, IndexName, Command
    )

    SELECT
    t.name
    , i.name AS IndexName
    , (SUM(a.used_pages) *8) / 1024. AS [Index MB]
    , im.Index_Minutes AS WorldRecord
    , im.Command
    FROM sys.indexes AS i
    JOIN sys.partitions AS p ON p.object_id = i.object_id
    AND p.index_id = i.index_id
    JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
    JOIN sys.tables t ON t.object_id = i.object_id
    JOIN im ON im.IndexName = i.name
    WHERE t.is_ms_shipped = 0
    GROUP BY t.name, i.name, im.Command, im.Index_Minutes
    ORDER BY t.name, i.name;

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}