Microsoft Fabric: Data Warehouse Performance Tuning for Cold Run Efficiency

With Microsoft Fabric garnering more and more popularity, I decided to create a comprehensive multi-part blog series on how to migrate to this powerful platform. In this first installment, we'll explore Data Warehouse Performance Tuning for Cold Run Efficiency. Cold run performance in Microsoft Fabric and Azure Synapse Analytics is crucial for minimising latency and ensuring efficient initial query execution, especially in data warehouse performance tuning scenarios. Azure Synapse combines big data and data warehousing capabilities, while Microsoft Fabric integrates data management, analytics, and business intelligence into a unified environment. Manual statistics creation plays a key role in optimising query performance in these platforms by providing the query optimizer with precise data distribution information.

Why Manual Statistics Creation?

1. Improved Query Plans: The query optimiser in Microsoft Fabric & Azure Synapse depends on precise statistics to craft effective execution plans, especially when dealing with extensive datasets.

2. Fine-Tuned Performance: Manual statistics provide greater control over optimisation in scenarios where automatic statistics updates may be delayed.

3. Consistency Across Environments: Ensures that critical statistics are always available, especially in dynamic cloud environments.

When to Use Manual Statistics?

• When the performance of the initial query execution is paramount.

• After loading large volumes of data or significant data modifications.

• When dealing with complex queries or large-scale data analytics.

• For data warehousing applications where efficient query performance is essential.

• In environments where frequent data model changes occur.

Prerequisites

• Permission to create and update statistics in Microsoft Fabric or Azure Synapse.

• Understanding of the data model and frequent query patterns.

• Familiarity with SQL.

Steps to Create Manual Statistics

1. Identify Key Tables and Columns

Identify tables and columns frequently involved in queries. Use system views to determine what tables and columns are available and where to focus.

SELECT
	o.name AS table_name,
	c.name AS column_name
FROM
	sys.objects o
	JOIN sys.columns c ON
		o.object_id = c.object_id
WHERE
	o.type = 'U' -- User tables
ORDER BY
	o.name;

2. Create Statistics Manually

Use the CREATE STATISTICS command to generate statistics.

CREATE STATISTICS stat_name ON table_name (column_name) WITH FULLSCAN;

Example:

CREATE STATISTICS stat_customer_age ON dbo.Customers (Age) WITH FULLSCAN;

3. Update Statistics Regularly

Schedule updates for manual statistics to keep them current. Use the UPDATE STATISTICS command:

UPDATE STATISTICS table_name (stat_name) WITH FULLSCAN;

Example:

UPDATE STATISTICS dbo.Customers (stat_customer_age);

4. Analyse and Optimise Query Plans

Utilise query execution insights provided by Microsoft Fabric & Azure Synapse to understand and optimise performance.

DBCC SHOW_STATISTICS ("dbo.Customers", "stat_customer_age");

To show only information about the histogram of the statistics object:

DBCC SHOW_STATISTICS ("dbo.Customers", "stat_customer_age") WITH HISTOGRAM;

5. Monitor and Review Performance

Consistently track query performance using the built-in monitoring features of Microsoft Fabric & Azure Synapse. Refine statistics and optimisation strategies in response to performance metrics.

You can utilise the following T-SQL objects to inspect statistics, whether they're manually created or automatically generated:

• sys.stats

• sys.stats_columns

• STATS_DATE

Example:

SELECT
	s.name AS statistics_name,
	c.name AS column_name,
	sc.stats_column_id,
	STATS_DATE(s.object_id, s.stats_id)
FROM
	sys.stats s
	JOIN sys.stats_columns sc ON
		s.object_id = sc.object_id
		AND s.stats_id = sc.stats_id
	JOIN sys.columns c ON
		sc.object_id = c.object_id
		AND c.column_id = sc.column_id
WHERE
	s.object_id = OBJECT_ID('dbo.Customers');

Should they not meet expectations, you can manually drop the statistics object.

DROP STATISTICS table_name.stat_name;

Example:

DROP STATISTICS dbo.Customers.stat_customer_age;

Best Practices, & Considerations for Data Warehouse Performance Tuning

• Targeted Statistics: Focus on critical columns and query patterns to minimise overhead.

• Regular Updates: Keep statistics current with scheduled updates, especially after data load operations.

• Development Testing: Test manual statistics impacts in a development environment before applying to production.

• Dedicated SQL Pools: Use manual statistics to enhance performance for large, complex queries.

• Serverless SQL Pools: While they rely on automatic statistics, manual intervention can be beneficial for recurring queries with known patterns.

Conclusion: Empower Your Data Warehouse Performance

Enhancing cold run performance through manual statistics creation in Microsoft Fabric & Azure Synapse Analytics is just one step towards achieving optimal data warehouse performance. By leveraging the power of precise statistics and query optimisation techniques, you can unlock the full potential of your data environment.

But the journey doesn't end here. I invite you to share your experiences and insights in the comments below. Have you encountered specific challenges or success stories in data warehouse performance tuning? What strategies have worked best for you in improving cold run performance?

Let's continue the conversation and learn from each other's perspectives. Together, we can navigate the complexities of data management and drive innovation in the world of analytics.

Stay connected, stay curious, and let's propel our data warehouse performance to new heights!