Azure Well‐Architected Framework. The Azure Well‐Architected Framework is a set of best practices to follow for running your workloads on the Azure platform. They include cost optimization, security, reliability, operation excellence, and performance efficiency.
Result set cache. When the same SQL queries are executed many times, performance can be improved by enabling the result set cache mechanism. The result_cache_hit column in the sys.dm_pdw_exec_requests table gives you insights about how often queries are using cache versus reading the data from disk.
I/O operations. When working with files or data that is stored on disk, the retrieval operations incur additional overhead, which increases execution time. The delay is caused by a thread switch from a procedural thread to an I/O thread and back. It is recommended to merge many small files into larger files to reduce the number of I/O operations. It is also recommended to enable cache, which reads data from memory instead of disk, which also avoids the I/O overhead cost.
Indexes. Columnstore indexes will be your most performant type of index when running on a dedicated SQL pool. Clustered indexes are identified by a sequence of columns that are to be used in the WHERE clause of a SQL query. When the WHERE clause matches the index, the data will be found faster because a scan of the table is not required; instead, the index points directly to the data on the table.
OLTP, OLAP, and HTAP. An OLTP database is a database users or customers send transactional data to in real time (for example, orders or brain wave readings). An OLAP is a data source where data is retrieved and used to gather business insights. Performing inserts and selects from the same datastore can cause contention issues. An HTAP is the solution to that scenario. When implemented, HTAP allows both inserts and selects to function on the same datastore without conflicting with each other.