We have been using an azure VM for hosting SQL Server. A4 size i.e. 4 cores & 7GB RAM
We have noticed intermittent slow performance of database.
We are worried that since azure VM is multi-tenant instance, it could not be working at 4 cores performance always.
We are trying to understand that when we fire a 4 core VM, does it
mean we always have that much compute power? or will it reduce
depending on other users?
The first thing you should do is measure why your your database is performing so slow. Are you hitting the memory limit of your VM? The CPU limit? Or is the performance of the data disks an issue (IOPS)?
On MSDN there's a checklist with things you need to consider when hosting SQL Server in Virtual Machines:
- Use minimum Standard Tier A2 for SQL Server VMs.
- Keep the storage account and SQL Server VM in the same region.
- Disable Azure geo-replication on the storage account.
- Avoid using operating system or temporary disks for database storage or logging.
- Avoid using Azure data disk caching options (caching policy = None).
- Stripe multiple Azure data disks to get increased IO throughput.
- Format with documented allocation sizes.
- Separate data and log file I/O paths to obtain dedicated IOPs for data and log.
- Enable database page compression.
- Enable instant file initialization for data files.
- Limit or disable autogrow on the database.
- Disable autoshrink on the database.
- Move all databases to data disks, including system databases.
- Move SQL Server error log and trace file directories to data disks.
- Apply SQL Server performance fixes.
- Setup default locations.
- Enable locked pages.
- Backup directly to blob storage.