Yip. You can.
I was originally going to write this post about tuning tempDB in our Azure SQL database. Which would be a very short post.
You can’t.
So I’ll write about tuning tempDB in your Azure based SQL Server database.
Why tempDB tuning? Well I consider it a valuable tool in our tuning toolbelt – in fact I’ve presented about it a couple of times at SQL Saturdays:
As well as at my own User Group
So our SQL Server instance will be hosted on Azure Infrastructure as a Service (IaaS) utilising an Azure Virtual Machine. So in fact – most of the same database tuning methods and options will be applicable to SQL Server hosted here as you would for on-premises installations.
Firstly – you need to read this document which is extremely helpful in designing what your VMs and storage will look like:
Same as with on-premises we are going to size our tempDB database file(s) appropriately – I like to presize mine and I typically will put one database file per CPU. Autogrowth is set to 100MB – but I typically will have done performance testing using the databases that will be hosted here to know what size tempDB files to put down.
A very important setting that will make a difference especially if you have restore a database or have to autogrow your files is:
Instance File Initialization (IFI)
This reduces the I/O required to create files and extend files, it is not enabled by default in Azure VM images. You need to add your SQL Server service account to Perform Volume Maintenance Tasks security policy.
Some recommendations for IFI:
- Configure IFI before creating database
- Configure IFI first and then create & extend files for tempDB.
- Configure IFI first before restoring any database on the new VM
- Restart SQL Server service for the configuration manager after configuring IFI
The performance testing you will undertake for tempDB will utilise analysis of latch contention and rather than re-write something I’d prefer you went to the source:
http://www.sqlservercentral.com/blogs/robert_davis/2010/03/05/Breaking-Down-TempDB-Contention/
https://www.brentozar.com/archive/2014/05/tell-need-tempdb-files/
http://michaeljswart.com/2015/08/whats_going_on_inside_tempdb/
The articles above are really invaluable to help us analyse tempDB in our on-premises SQL Server installations – but they are just as applicable if you are running your SQL Server instance on a VM hosted in Azure.
So don’t let running in Azure fool you into everything will be OK – as you still need to tune tempDB (and other SQL Server metrics) but also – know that running in Azure there are a lot of things that you’ll get there (SSDs, scale, …) that you won’t get in on-premises.
By all means use IaaS for your SQL Server instance running on a Azure Vm – there are cases where you can’t just use Azure SQL. When you do you’ll find that performance might even be better than your on-premises SQL Server…
Yip.
