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!
8 Comments. Leave new
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!
NEVERMIND! This is covered here: https://www.brentozar.com/archive/2017/02/always-availability-groups-now-supported-google-compute-engine/
Thanks Brent! Its a good and interesting read.
You’re welcome Jimmy!
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
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!
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
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;