Performance best practices for SQL Server in Azure Virtual Machines

Quick check list

The following is a quick check list for optimal performance of SQL Server on Azure Virtual Machines:

Area	Optimizations
VM size	DS3 or higher for SQL Enterprise edition. DS2 or higher for SQL Standard and Web editions.
Storage	Use Premium Storage. Standard storage is only recommended for dev/test. Keep the storage account and SQL Server VM in the same region. Disable Azure geo-redundant storage (geo-replication) on the storage account.
Disks	Use a minimum of 2 P30 disks (1 for log files; 1 for data files and TempDB). Avoid using operating system or temporary disks for database storage or logging. Enable read caching on the disk(s) hosting the data files and TempDB. Do not enable caching on disk(s) hosting the log file. Important: Stop the SQL Server service when changing the cache settings for an Azure VM disk. Stripe multiple Azure data disks to get increased IO throughput. Format with documented allocation sizes.
I/O	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. Setup default backup and database file locations. Enable locked pages. Apply SQL Server performance fixes.

Go to here for more details

Script to check Disk IO

select db_name(database_id) as DatabaseName, file_id,io_stall_read_ms,num_of_reads
,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
,io_stall_write_ms,num_of_writes,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms',
io_stall_read_ms + io_stall_write_ms as io_stalls,num_of_reads + num_of_writes as total_io,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
from sys.dm_io_virtual_file_stats(null,null)
order by [DatabaseName] desc

Monitoring SQL Server Performance using Query Store

Query Store is a new functionality introduced since SQL Server 2016, I really love this.

What is Query Store: SQL Server Query Store feature provides you with insight on query plan choice and performance. It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes.

Why I have to use Query Store: Quey store automatically capture a history of queries, plan, and runtime statistics, and retain these for your review. well if you want to choose the hard path to solve performance issues then don't use query store.

How to use Query Store: Well I like this question, I will try to explain whatever I understood.

1. Go to SQL Server Mangement Studio
2. Object Explorer, right-click on a database, and select properties
3. From Database Properties window select Query Store Page
4.  From Operation Mode( Requested) Select read write or read only

    *** you cannot enable Query Store for master and tempdb
After enabling Query store, go to required Database --> Query Store

Query Store will log information about each query including:
1. Number of executions
2. execution time
3. Memory consumption
4. Logical Reads
5. Logical Writes
6. Physical Reads
7. Number of execution plan changes

To reduce the load on the server, this information is aggregated into a fixed window. If you need more precise data, you should look to Extended Events.

Now open regressed queries view. you will see a similer window like below.

This tool will allow you to see regressions based on any of the recorded metrics. If you see a regression, you have the option to force SQL Server to use an older execution plan.

SQL Server Performance Checklist

SQL: Scale Up vs. Scale Out
	Optimize the application before scaling up or scaling out.
	Address historical and reporting data.
	Scale up for most applications.
	Scale out when scaling up does not suffice or is cost-prohibitive.
Schema
	Devote the appropriate resources to schema design
	Separate online analytical processing (OLAP) and online transaction processing (OLTP) workloads.
	Normalize first, denormalize later for performance
	Define all primary keys and foreign key relationships
	Define all unique constraints and check constraints.
	Choose the most appropriate data type.
	Use indexed views for denormalization.
	Partition tables vertically and horizontally.
Queries
	Know the performance and scalability characteristics of queries.
	Write correctly formed queries.
	Return only the rows and columns needed.
	Avoid expensive operators such as NOT LIKE.
	Avoid explicit or implicit functions in WHERE clauses.
	Use locking and isolation level hints to minimize locking
	Use stored procedures or parameterized queries.
	Minimize cursor use.
	Avoid long actions in triggers.
	Use temporary tables and table variables appropriately
	Limit query and index hint use.
	Fully qualify database objects.
Indexes
	Create indexes based on use.
	Keep clustered index keys as small as possible
	Consider range data for clustered indexes.
	Create an index on all foreign keys.
	Create highly selective indexes.
	Create a covering index for often-used, high-impact queries.
	Use multiple narrow indexes rather than a few wide indexes.
	Create composite indexes with the most restrictive column first.
	Consider indexes on columns used in WHERE, ORDER BY, GROUP BY, and DISTINCT clauses.
	Remove unused indexes.
	Use the Index Tuning Wizard.
Transactions
	Avoid long-running transactions.
	Avoid transactions that require user input to commit.
	Access heavily used data at the end of the transaction.
	Try to access resources in the same order.
	Use isolation level hints to minimize locking.
	Ensure that explicit transactions commit or roll back.
Stored Procedures
	Use Set NOCOUNT ON in stored procedures.
	Do not use the sp_prefix for custom stored procedures.
Execution Plans
	Evaluate the query execution plan.
	Avoid table and index scans
	Evaluate hash joins.
	Evaluate bookmarks.
	Evaluate sorts and filters.
	Compare actual versus estimated rows and executions.
Execution Plan Recompiles
	Use stored procedures or parameterized queries.
	Use sp_executesql for dynamic code.
	Avoid interleaving data definition language (DDL) and data manipulation language (DML) in stored procedures, including the tempdb database DDL.
	Avoid cursors over temporary tables.
SQL XML
	Avoid OPENXML over large XML documents.
	Avoid large numbers of concurrent OPENXML statements over XML documents
Tuning
	Use SQL Profiler to identify long-running queries
	Take note of small queries called often.
	Use sp_lock and sp_who2 to evaluate locking and blocking
	Evaluate waittype and waittime in master..sysprocesses.
	Use DBCC OPENTRAN to locate long-running transactions.
Testing
	Ensure that your transactions logs do not fill up.
	Budget your database growth.
	Use tools to populate data.
	Use existing production data.
	Use common user scenarios, with appropriate balances between reads and writes.
	Use testing tools to perform stress and load tests on the system.
Monitoring
	Keep statistics up to date
	Use SQL Profiler to tune long-running queries.
	Use SQL Profiler to monitor table and index scans.
	Use Performance Monitor to monitor high resource usage
	Set up an operations and development feedback loop
Deployment Considerations
	Use default server configuration settings for most applications.
	Locate logs and the tempdb database on separate devices from the data.
	Provide separate devices for heavily accessed tables and indexes.
	Use the correct RAID configuration.
	Use multiple disk controllers.
	Pre-grow databases and logs to avoid automatic growth and fragmentation performance impact.
	Maximize available memory.
	Manage index fragmentation
	Keep database administrator tasks in mind.