SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Can I tune the tempDB of an Azure based SQL Server database…

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:

Sydney

Brisbane

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:

https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-portal-sql-server-provision

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:

https://www.sqlskills.com/blogs/paul/the-accidental-dba-day-27-of-30-troubleshooting-tempdb-contention/

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.


The Hybrid DBA's Blog

Hamish is a Systems Management Specialist with a passion for efficient application deployment using DevOPs methodologies. He has 19 years IT experience in managing large scale databases SQL Server technologies. He has been managing SQL Server since SQL Server 2000 and pragmatic architectural design is his main focus in his current job. Educating and helping others learn is a driver for Hamish and he is a PASS User Group Leader, International speaker and a repeat guest lecturer at a local university.

Comments

Leave a comment on the original post [hybriddbablog.com, opens in a new window]

Loading comments...