When it comes to performance tuning or troubleshooting, for a standard SQL server many tools and techniques exist. For example query execution plans can be analysed, profiler exists, and a host of other dmvs which a DBA cannot live without.
In an Azure Data warehouse there are similar dmvs. However some basic principles can be applied to guarantee optimal performance:
- Create statistics on every column
- Use indexes on join predicates. Non clustered indexes for small tables or tables with a column store index or instead of a clustered index
- Use a consistent distribution key for all dimension and fact tables
- Use column store indexes for extra large tables
- Use clustered indexes for large tables
- Rebuild indexes regularly as part of a inter-day or weekly maintenance process
- Update statistics regularly as part of a intra-day or inter-day maintenance process
- Limit concurrency utilisation using resource classes
- Choose the correct distribution type to avoid expensive DMS operations
[…] DMS operation, specifically shufflemove and broadcastmove. Performing the basic steps outlined in part 1, will in most cases reduce the impact of the costly operations. However to completely eliminate […]