كيفية تحسين أداء Power BI DirectQuery وإيقاف الضغط على خوادم SQL Server

يمثل تحسين أداء Power BI DirectQuery تحدياً كبيراً عندما تعاني لوحات المعلومات من البطء الشديد ويرتفع استهلاك المعالج في خوادم SQL Server دون سابق إنذار. إذا كانت قاعدة بياناتك تغرق في استعلامات مخصصة (Ad hoc queries) لا نهاية لها، فأنت بحاجة إلى تحديد استعلامات T-SQL المسببة للمشكلة وتحسين بنية الخادم الخاص بك على الفور. يعد استخدام DirectQuery خياراً معمارياً صالحاً تماماً، ولكنه ينقل عبء العمل التحليلي بالكامل إلى محرك قاعدة البيانات الخاص بك. وبدون الفهرسة المناسبة وإدارة الموارد، يمكن للوحة معلومات واحدة أن تتسبب في توقف الخادم تماماً.

عندما يستخدم تقرير في نظام Power BI وضع الاستيراد، تستقر البيانات مضغوطة في محرك VertiPaq داخل الذاكرة، ولا يتدخل خادم SQL Server إلا أثناء عمليات التحديث المجدولة. لكن وضع DirectQuery يعمل بشكل مختلف تماماً. ففي كل مرة يفتح فيها المستخدم تقريراً، أو يحرك مقسمات طرق العرض (Slicers)، أو ينقر على مخطط بياني، يترجم نظام Power BI هذا التفاعل إلى استعلام T-SQL ويرسله مباشرة إلى قاعدة بياناتك. ولا توجد ذاكرة تخزين مؤقت عمودية لامتصاص هذا التأثير.

التفصيل الذي يفاجئ معظم مسؤولي قواعد البيانات هو تأثير المضاعفة. حيث يُنشئ كل عنصر مرئي في صفحة التقرير استعلاماً مستقلاً خاصاً به. وتطلق لوحة المعلومات التي تحتوي على 12 عنصراً مرئياً 12 استعلاماً منفصلاً عند تحميل الصفحة. وعندما يقوم المستخدم بتعديل مقسم التاريخ، يقوم نظام Power BI بتحديث كل عنصر مرئي في الصفحة، مما يولد 12 استعلاماً إضافياً. وإذا تم تفعيل التصفية المتقاطعة (Cross-filtering)، فإن النقر على شريط واحد في المخطط يؤدي إلى تشغيل جولة أخرى من الاستعلامات لكل عنصر مرئي آخر.

هذا يعني أن تفاعل مدير تنفيذي واحد مع لوحة المعلومات يمكن أن يولد 36 استعلاماً تحليلياً معقداً في أقل من 30 ثانية. اضرب ذلك في 50 مستخدماً يسجلون الدخول في الساعة 9:00 صباحاً، وسيصبح الحمل على الخادم كارثياً. يفرض نظام Power BI أيضاً قيوداً صارمة على هذه الاستعلامات. يبلغ الحد الأقصى لمهلة الاستعلام المرئي 225 ثانية في كل من السعات المشتركة والمميزة، بينما تبلغ مهلة خدمة DirectQuery حوالي 4 دقائق. وإذا لم يتمكن الخادم من الاستجابة في الوقت المناسب، سيعرض العنصر المرئي رسالة خطأ ببساطة.

علاوة على ذلك، يفرض نظام Power BI حداً أقصى للتزامن يبلغ 10 اتصالات متزامنة لكل مصدر بيانات افتراضياً، ويضع أي شيء يتجاوز ذلك في قائمة الانتظار. كما يفرض حداً يبلغ 1,000,000 صف للنتائج الوسيطة، والذي يظهر كـ TOP (1000001) في كود SQL المُنشأ ككاشف لتجاوز السعة. وعلى عكس المتوقع، فإن زيادة حد التزامن إلى 30 غالباً ما يؤدي إلى تدهور الأداء، حيث تكافح قاعدة البيانات لمعالجة هذا العدد الكبير من الاستعلامات التحليلية المتزامنة، مما يتسبب في تضخم أوقات التنفيذ الفردية وتجاوزها لمهلة الاستجابة.

إذا قمت بالتقاط حركة مرور DirectQuery باستخدام أدوات Profiler أو Extended Events، ستلاحظ أن كود T-SQL المُنشأ مطول للغاية. يقوم نظام Power BI بتغليف كل مرجع جدول في جدول مشتق ويطبق عمليات CAST بشكل مكثف. على سبيل المثال، قد يكتب المطور البشري تجميعاً بسيطاً على النحو التالي:

SELECT p.ProductCategory, SUM(s.SalesAmount) AS TotalSales
FROM dbo.Sales s
INNER JOIN dbo.Product p ON s.ProductID = p.ProductID
WHERE s.SalesDate >= '2024-01-01' AND s.SalesDate < '2025-01-01'
GROUP BY p.ProductCategory
ORDER BY TotalSales DESC;

ومع ذلك، يُنشئ نظام Power BI بنية أكثر تعقيداً بكثير. يختلف الشكل الدقيق بناءً على نموذج البيانات الخاص بك، ولكن عبارة DirectQuery النموذجية تبدو هكذا:

SELECT TOP (1000001) [t2].[ProductCategory], SUM(CAST([t1].[SalesAmount] AS FLOAT)) AS [a0]
FROM (
    SELECT [Sales].[SalesAmount], [Sales].[Quantity], [Sales].[ProductID], [Sales].[SalesDate]
    FROM [dbo].[Sales] AS [Sales]
) AS [t1]
INNER JOIN (
    SELECT [Product].[ProductID], [Product].[ProductCategory]
    FROM [dbo].[Product] AS [Product]
) AS [t2] ON [t1].[ProductID] = [t2].[ProductID]
WHERE YEAR(CAST([t1].[SalesDate] AS DATE)) = 2024
GROUP BY [t2].[ProductCategory]
ORDER BY [a0] DESC

تظهر هنا عدة عوامل قاتلة للأداء. أولاً، تضع الاستعلامات الفرعية غير الضرورية ضغطاً هائلاً على ذاكرة التخزين المؤقت للخطط. ثانياً، الشرط WHERE YEAR(CAST([t1].[SalesDate] AS DATE)) = 2024 غير قابل للبحث (Non-sargable). إن تغليف العمود في دالة يمنع مُحسّن خادم SQL Server من استخدام الفهارس الموجودة على عمود SalesDate، مما يجبره على إجراء عمليات مسح ضخمة للبيانات. بالإضافة إلى ذلك، يُنشئ نظام Power BI بشكل متكرر روابط من نوع LEFT OUTER JOINs للتعامل مع الصفوف الفارغة في لغة DAX. إذا كانت المفاتيح الخارجية الخاصة بك نظيفة، فإن توجيه فريق ذكاء الأعمال لتفعيل خيار افتراض التكامل المرجعي (**Assume Referential Integrity**) سيحولها إلى روابط INNER JOINs، مما يفتح المجال لاستراتيجيات تحسين أفضل.

قبل تنفيذ الإصلاحات، يجب عليك تحديد ما يرسله نظام Power BI بالضبط إلى الخادم الخاص بك. اعتماداً على مدى إلحاح الموقف، يمكنك استخدام ثلاثة أساليب تشخيصية مختلفة. إذا كان الخادم مثقلاً حالياً، استخدم استعلام DMV هذا للعثور على الطلبات النشطة التي تتطابق مع توقيع DirectQuery:

SELECT r.session_id, r.total_elapsed_time AS elapsed_ms, r.status, r.wait_type, r.cpu_time AS cpu_ms, r.logical_reads,
SUBSTRING(st.text, (r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1) AS query_text
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE s.is_user_process = 1 AND (
SUBSTRING(st.text, 1, 1000) LIKE '%FROM (%SELECT%' OR SUBSTRING(st.text, 1, 1000) LIKE '%TOP (1000001)%'
)
ORDER BY r.total_elapsed_time DESC;

لإجراء تحليل تاريخي، يعد مخزن الاستعلامات (Query Store) الأداة الأكثر فعالية. فهو يسمح لك بالعثور على أعباء عمل DirectQuery الأكثر تكلفة على مدار الأيام السبعة الماضية. انتبه جيداً لمقياس plan_count؛ فإذا كان لاستعلام واحد خطط تنفيذ متعددة، فمن المحتمل أن يكون عدم استقرار الخطة هو السبب الجذري لانخفاض الأداء المفاجئ.

DECLARE @start_time DATETIME2 = DATEADD(DAY, -7, GETDATE());
SELECT TOP 20 q.query_id, SUBSTRING(qt.query_sql_text, 1, 150) AS query_preview, SUM(rs.count_executions) AS total_execs,
CAST(AVG(rs.avg_duration) / 1000.0 AS NUMERIC(10, 2)) AS avg_duration_ms,
CAST(MAX(rs.max_duration) / 1000.0 AS NUMERIC(10, 2)) AS max_duration_ms,
SUM(CAST(rs.avg_logical_io_reads * rs.count_executions AS BIGINT)) AS total_logical_reads,
COUNT(DISTINCT p.plan_id) AS plan_count
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
INNER JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id
WHERE (qt.query_sql_text LIKE '%FROM (%SELECT%' OR qt.query_sql_text LIKE '%TOP (1000001)%')
AND q.is_internal_query = 0 AND q.object_id = 0 AND rsi.start_time >= @start_time
GROUP BY q.query_id, qt.query_sql_text
HAVING SUM(rs.count_executions) > 10
ORDER BY SUM(rs.avg_duration * rs.count_executions) DESC;

للمراقبة المستمرة دون استهلاك كبير للموارد، قم بإعداد جلسة Extended Events لالتقاط العبارات التي تستغرق وقتاً طويلاً. تأكد من التقاط كل من rpc_completed و sql_batch_completed، حيث تصل حركة مرور DirectQuery عادةً في شكل مكالمات RPC.

CREATE EVENT SESSION [DirectQuery_Monitor] ON SERVER
ADD EVENT sqlserver.rpc_completed (
    ACTION (sqlserver.client_app_name, sqlserver.database_name, sqlserver.sql_text, sqlserver.session_id)
    WHERE sqlserver.database_name = N'YourDatabase' AND duration >= 5000000
),
ADD EVENT sqlserver.sql_batch_completed (
    ACTION (sqlserver.client_app_name, sqlserver.database_name, sqlserver.sql_text, sqlserver.session_id)
    WHERE sqlserver.database_name = N'YourDatabase' AND duration >= 5000000
)
ADD TARGET package0.event_file (
    SET filename = N'D:\XEvents\DirectQuery_Monitor.xel', max_file_size = 100, max_rollover_files = 5
)
WITH (MAX_DISPATCH_LATENCY = 30 SECONDS, STARTUP_STATE = OFF);
GO
ALTER EVENT SESSION [DirectQuery_Monitor] ON SERVER STATE = START;

بمجرد تحديد الاستعلامات المسببة للمشاكل، يجب عليك تنفيذ تحسينات هيكلية في قاعدة البيانات. تعتبر أعباء عمل DirectQuery تحليلية بطبيعتها، وتعتمد بشكل كبير على عمليات GROUP BY و SUM و COUNT وعمليات المسح الضخمة لجداول الحقائق. تعد إضافة فهارس تخزين الأعمدة (Columnstore Indexes) غير المجمعة إلى أكبر جداول الحقائق لديك التغيير الأكثر تأثيراً الذي يمكنك إجراؤه.

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Sales_Analytics
ON dbo.Sales (SalesDate, ProductID, RegionID, CustomerID, SalesAmount, Quantity, Cost);

يسمح استخدام فهارس تخزين الأعمدة غير المجمعة ببقاء فهارس B-tree الأساسية سليمة لعمليات الكتابة الخاصة بالمعاملات (OLTP)، بينما تستفيد استعلامات DirectQuery من معالجة وضع الدُفعات والتخزين العمودي. يمكن أن يؤدي ذلك إلى خفض أوقات التنفيذ من 12 ثانية إلى أقل من ثانية واحدة، وتقليل القراءات المنطقية من أكثر من 15,000 إلى 500 فقط. إذا كنت تستخدم إصدار SQL Server 2019 أو أحدث، فتأكد من ضبط مستوى التوافق على 150 أو أعلى للاستفادة من وضع الدُفعات على تخزين الصفوف.

إذا طلب نظام Power BI نفس التجميعات بشكل متكرر، يمكنك تجسيد النتائج باستخدام عرض مفهرس (Indexed View). سيقوم المُحسّن بتقديم البيانات مباشرة من العرض بدلاً من مسح الجداول الأساسية. لاحظ أن المطابقة التلقائية للعرض تتطلب إصدار Enterprise أو قاعدة بيانات Azure SQL؛ ففي إصدار Standard، لا يمكن لنظام Power BI حقن تلميح NOEXPAND المطلوب.

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
CREATE VIEW dbo.vw_MonthlySalesByCategory WITH SCHEMABINDING AS
SELECT YEAR(s.SalesDate) AS SalesYear, MONTH(s.SalesDate) AS SalesMonth, p.ProductCategory, SUM(s.SalesAmount) AS TotalSales, COUNT_BIG(*) AS RowCount
FROM dbo.Sales AS s
INNER JOIN dbo.Product AS p ON s.ProductID = p.ProductID
GROUP BY YEAR(s.SalesDate), MONTH(s.SalesDate), p.ProductCategory;
GO
CREATE UNIQUE CLUSTERED INDEX IX_vw_MonthlySalesByCategory ON dbo.vw_MonthlySalesByCategory (SalesYear, SalesMonth, ProductCategory);
GO

عندما يشارك وضع DirectQuery نفس الخادم مع أعباء عمل المعاملات، يصبح حاكم الموارد (Resource Governor) ضرورياً لحماية أداء OLTP الخاص بك. من خلال تحديد الحد الأقصى لدرجة التوازي (MAX_DOP) عند 4، فإنك تمنع نظام Power BI من استنفاد خيوط المعالجة أثناء فترات ذروة التقارير.

CREATE RESOURCE POOL Pool_PowerBI WITH (MAX_CPU_PERCENT = 40, MAX_MEMORY_PERCENT = 30);
GO
CREATE WORKLOAD GROUP Group_PowerBI WITH (MAX_DOP = 4) USING Pool_PowerBI;
GO
CREATE FUNCTION dbo.ClassifyPowerBI() RETURNS SYSNAME WITH SCHEMABINDING AS
BEGIN
    DECLARE @group SYSNAME = 'default';
    IF APP_NAME() LIKE N'ower BI' OR SUSER_SNAME() = N'PowerBIServiceAccount'
        SET @group = 'Group_PowerBI';
    RETURN @group;
END;
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.ClassifyPowerBI);
ALTER RESOURCE GOVERNOR RECONFIGURE;

أخيراً، يعد بناء طبقة تقارير باستخدام العروض (Views) تحسيناً مجانياً يحقق نتائج فورية. قم بإنشاء عروض تربط جداول الأبعاد بجداول الحقائق مسبقاً، ووجه نظام Power BI مباشرة إلى هذه العروض. يلغي هذا حاجة نظام Power BI لإنشاء روابط معقدة في كل استعلام مرئي، مما يحافظ على فعالية الفهارس ويبسط كود SQL المُنشأ.

CREATE VIEW dbo.vw_SalesReporting AS
SELECT s.SalesID, s.SalesDate, YEAR(s.SalesDate) AS SalesYear, MONTH(s.SalesDate) AS SalesMonth,
c.CustomerName, c.CustomerSegment, c.Region, p.ProductName, p.ProductCategory,
s.Quantity, s.SalesAmount, s.Cost, s.SalesAmount - s.Cost AS GrossProfit
FROM dbo.Sales s
INNER JOIN dbo.Customer c ON s.CustomerID = c.CustomerID
INNER JOIN dbo.Product p ON s.ProductID = p.ProductID;

لا يمكن لتحسين قاعدة البيانات أن يحل كل شيء؛ فغالباً ما تتطلب مكاسب الأداء الأكثر أهمية إجراء تغييرات داخل نموذج Power BI نفسه. لتقليل الحمل على خادم SQL Server بشكل كبير، وجه مطوري ذكاء الأعمال لتنفيذ التغييرات المعمارية الخمسة التالية:

• إبقاء العناصر المرئية أقل من 8 لكل صفحة: نظراً لأن كل عنصر مرئي يُنشئ استعلاماً مستقلاً، فإن تقليل العدد من 15 إلى 8 يقلل حجم الاستعلامات إلى النصف لكل تفاعل للمستخدم.
• تشغيل أزرار التطبيق لمقسمات طرق العرض: بدون أزرار التطبيق (**Apply**)، تؤدي كل نقرة وسيطة في مقسم طرق العرض إلى إطلاق جولة كاملة من الاستعلامات. يجبر هذا المستخدمين على تعيين جميع عوامل التصفية قبل إرسال طلب واحد.
• تعطيل التصفية المتقاطعة غير الضرورية: انتقل إلى التنسيق (**Format**) ثم تحرير التفاعلات (**Edit Interactions**) واضبط العناصر المرئية غير المرتبطة على "بلا" (**None**). يمنع هذا نقرة واحدة على المخطط من إطلاق سلسلة تحديثات عبر الصفحة بأكملها.
• استخدام النماذج المركبة: قم باستيراد جداول الأبعاد الصغيرة والبطيئة التغير (العميل، المنتج، التاريخ) إلى نموذج Power BI داخل الذاكرة، واقصر استخدام DirectQuery حصرياً على جداول الحقائق الكبيرة.
• تعيين افتراض التكامل المرجعي: إذا كانت المفاتيح الخارجية الخاصة بك نظيفة، فإن تفعيل هذا الإعداد (**Assume Referential Integrity**) على العلاقات يغير كود SQL المُنشأ من روابط LEFT OUTER JOINs إلى روابط INNER JOINs، مما يتيح خطط تنفيذ أسرع دون تعديلات على قاعدة البيانات.

إذا كنت تستضيف قاعدة بياناتك على منصة Azure SQL Database، فلديك إمكانية الوصول إلى ميزات محددة تبسط إدارة DirectQuery. في مستويات Premium و Business Critical و Hyperscale، يمكنك الاستفادة من النسخ المتماثلة للقراءة. من خلال إضافة ApplicationIntent=ReadOnly إلى سلسلة اتصال Power BI، يتم توجيه عبء العمل التحليلي بالكامل إلى نسخة متماثلة ثانوية، مما يحافظ على نظافة قاعدة البيانات الأساسية لعمليات الكتابة (OLTP).

بالإضافة إلى ذلك، تتميز قاعدة بيانات Azure SQL بالضبط التلقائي. إذا تراجعت خطة تنفيذ DirectQuery بعد تحديث الإحصائيات، يمكن لنظام Azure اكتشاف التدهور تلقائياً وفرض الخطة المثلى السابقة.

ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

عند توفير موارد Azure، أعط الأولوية لنموذج vCore على نموذج DTU. يتطلب وضع DirectQuery موارد معالجة كبيرة للتوازي وذاكرة لعمليات الربط والفرز. يسمح لك نموذج vCore بتوسيع نطاق الحوسبة والذاكرة بشكل مستقل، وهو أمر بالغ الأهمية لأعباء العمل التحليلية.

على الرغم من كل جهود التحسين، هناك سيناريوهات يكون فيها وضع DirectQuery الأداة الخاطئة بشكل أساسي. إذا كانت مجموعة بياناتك غير المضغوطة أقل من 100 جيجابايت، فيجب عليك استخدام وضع الاستيراد كخيار افتراضي. يعد ضغط محرك VertiPaq استثنائياً؛ فغالباً ما يتم ضغط جدول SQL بحجم 5 جيجابايت إلى 500 ميجابايت فقط في نظام Power BI. وسيتفوق الاستعلام المباشر من ذاكرة الوصول العشوائي (RAM) دائماً على الاستعلام من محرك علائقي عبر اتصال شبكة.

إذا كانت حداثة البيانات هي الشاغل الرئيسي، فغالباً ما يكون التحديث التزايدي حلاً أفضل من DirectQuery. من خلال تحديث الأقسام الحديثة فقط، يمكن أن تنخفض أوقات التحديث من ساعات إلى دقائق، مما يلبي معظم متطلبات "الوقت الفعلي" دون الضغط على قاعدة البيانات. بالنسبة لمجموعات البيانات التي تبلغ مساحتها تيرابايتات متعددة حيث يكون وضع DirectQuery إلزامياً، تعمل النماذج المركبة كحل وسط مثالي. من خلال استيراد الأبعاد واستخدام التجميعات المعرفة من قبل المستخدم، تصل الاستعلامات عالية المستوى إلى جدول مخزن مؤقتاً، ولا يتم الاستعلام من خادم SQL Server إلا لعمليات التعمق التفصيلية.

بالنظر إلى المستقبل، يجب على المؤسسات التي تنتقل إلى منصة Microsoft Fabric تقييم وضع Direct Lake. من خلال القراءة المباشرة من ملفات Delta و Parquet في OneLake، فإنه يقدم أداءً مشابهاً لوضع الاستيراد دون عبء التحديثات المجدولة. وحتى ذلك الحين، يتطلب موازنة أداء DirectQuery جهداً تعاونياً: فهارس تخزين الأعمدة وحاكم الموارد على جانب قاعدة البيانات، مقترنة بحدود مرئية صارمة ونماذج مركبة على جانب Power BI.