4 Tips for Optimizing Service Manager 2012 SQL Performance in Azure

If you have not already heard, here at Cireson we recently moved our production System Center 2012 R2 Service Manager (SCSM) + Cireson environment up to Azure.

Our motivation was not only technical, but financial. As the saying goes “If it doesn’t make dollars, it doesn’t make sense”. In any case, I know performance of System Center SQL databases is a challenge for many organizations, so I want to share a few tips to ensure your move to Azure is a positive step for user experience in your SCSM environment.

Tip #1: Use Windows Storage Spaces for Presenting Storage

The ability to create software RAID on dynamic disks has been a feature of Windows for a long time, enabling disk striping (RAID 0), disk mirroring (RAID 1), and parity sets (RAID 5). In Azure, each virtual disk is actually stored in a blob in Azure Storage, which is replicated three times within the data center, so RAID 1 or 5 are completely unnecessary. You should always use RAID 0. However, Windows 2012 offers a better option in the form of Windows Storage Spaces.

With Storage Spaces, you can add multiple disks to a pool (a Storage Space) and then create virtual disks using the disks in the pool. Those virtual disks can be simple (no resiliency, similar to RAID 0), mirrored, or use parity. Because of the automatic background storage replication in Azure, we would always choose Simple.  Microsoft suggests always using the Storage Spaces feature when possible (requires Windows Server 2012 and later VMs) rather than traditional software RAID because you have greater flexibility in expanding storage and ease of management that comes with Storage Spaces. Since Storage Spaces is the future of software-defined storage for Windows, this is a great opportunity to get comfortable with the feature.

NOTE: This is another spot where Azure delivers an advantage of on-premises, as the Storage Spaces feature is supported in Azure VMs, but not Hyper-V VMs.

You can quickly learn more about Windows Storage Spaces from the “Storage Spaces Overview” at https://technet.microsoft.com/en-us/library/hh831739.aspx.

Tip #2: Break Your TempDB into Multiple Files

Using multiple files reduces tempdb storage contention and yields significantly better scalability. As general guidance, MS recommends 1 file per processor core. A dual core CPU would be considered 2 CPUs. This allows SQL to write to multiple files at the same time, increasing performance. Remember, only 1 file per core! Breaking TempDB into more than that will take performance in a negative direction.

Remember, a VM is just a VM, even in Azure. It’s the fabric that’s different! #2 is a tip that is equally applicable on-premises or in Azure.

BONUS TIP: While you are at it, set the recovery model on TempDB to ‘Simple’. This model automatically reclaims log space to keep space requirements small. Do pay attention to guidance at the URL below about initial / minimum file size.

For additional reading and a long list of tips around optimizing TempDB performance, see “Optimizing TempDB Performance” at https://technet.microsoft.com/en-us/library/ms175527%28v=sql.105%29.aspx.

Tip #3: Look at D Series VMs for Tier 1 Workloads

D, DS and G series VMs in Azure come with a premium storage option that includes local SSDs, enabling you to achieve up to 50,000 IOPS in a single VM. However, do NOT overallocate. You’re paying as you go, so pick an image size that is as much as you think you will need, but not more. If you discover you have oversized, scale down to a smaller image size.

The details on Azure VM image sizes are available at https://msdn.microsoft.com/en-us/library/azure/dn197896.aspx.

Tip #4:  Review the SQL Performance Guidance for Azure VMs Whitepaper

This whitepaper contains official guidance from Microsoft on how to optimize your SQL performance in Azure VMs (IaaS). Be sure to run through the ‘Quick Checklist’ on page 5, which contains a rundown of all the tips in the whitepaper.

Download the whitepaper at https://msdn.microsoft.com/en-us/library/azure/dn248436.aspx.

The Cireson Azure Story

Chris Ross and I delivered a webcast a few days ago, walking through the process step-by-step, providing tips for maximizing performance and availability while minimizing costs along the way. Catch the replay below.

SERVICE MANAGER + CIRESON + AZURE from Team Cireson on Vimeo.


Ready to transform your SCSM experience? View all of the exciting apps Cireson has to offer.

Learning about Azure

Experience Teams Ticketing Today

Start your 14-day free trial of Tikit. No credit card required.