כוונון שאילתות SQL Server: תוכניות, אינדקסים, סטטיסטיקות

אופטימיזציה של שאילתות MS SQL Server: תוכניות ביצוע, אינדקסים, סטטיסטיקות

שאילתה רצה ב-200 אלפיות השנייה, ואז פתאום קופצת ל-45 שניות. כמות הנתונים לא השתנתה, האינדקסים זהים, הקוד לא נגעו בו. זהו רגרסיית תוכנית—האופטימייזר בוחר תוכנית ביצוע שונה עקב סטטיסטיקות מיושנות או sniffing של פרמטרים. אנו נתקלים בזה ב-80% מהפרויקטים הקשורים לביצועים. אנו מתמודדים עם מקרים כאלה מדי יום ויודעים כיצד להחזיר את הביצועים תוך ימים. התרחיש הטיפוסי: שאילתה עבדה בצורה מושלמת עד שעדכון סטטיסטיקות או שינוי בנתונים הפכו אותה לאיטית. ללא ניתוח מעמיק של תוכנית הביצוע, אתה מסתכן בשעות של כיוונונים חסרי תועלת. בקש ביקורת—נעריך את הפרויקט שלך ונציע צעדים קונקרטיים.

כיצד לקרוא תוכנית ביצוע

תוכניות ביצוע הן עצי אופרטורים הנקראים מימין לשמאל. עובי החצים מצביע על כמות הנתונים. פער בין Estimated Rows ל-Actual Rows הוא הסימן הראשון לבעיה. אנו רואים פערים כאלה ב-80% מהשאילתות האיטיות.

אופרטורים מרכזיים:

-- Получить actual execution plan программно
SET STATISTICS XML ON;
SELECT o.OrderID, c.CustomerName, SUM(od.Quantity * od.UnitPrice) AS Total
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN OrderDetails od ON o.OrderID = od.OrderID
WHERE o.OrderDate >= '2022-01-01'
GROUP BY o.OrderID, c.CustomerName;
SET STATISTICS XML OFF;
-- Или через Query Store
SELECT TOP 5 qp.query_plan, qt.query_sql_text, rs.avg_duration / 1000.0 AS avg_ms, rs.avg_logical_io_reads
FROM sys.query_store_plan qp
JOIN sys.query_store_query q ON qp.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_runtime_stats rs ON qp.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;

כיצד לאבחן Parameter Sniffing?

Parameter sniffing הוא הגורם העיקרי לביצועים לא יציבים. SQL Server שומר במטמון את התוכנית מהביצוע הראשון ומשתמש בה לכל הקריאות הבאות. מקרה טיפוסי: פרוצדורה מאוחסנת עבור לקוח גדול מייצרת תוכנית עם Table Scan, שאינה יעילה עבור לקוח קטן יותר.

-- Диагностика parameter sniffing
SELECT TOP 10
    qs.execution_count,
    qs.plan_generation_num,
    qs.total_worker_time / qs.execution_count AS avg_cpu,
    qs.min_worker_time,
    qs.max_worker_time,
    qt.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qt.text LIKE '%GetCustomerOrders%'
ORDER BY qs.max_worker_time DESC;

פתרונות בהתאם למצב:

-- 1. OPTIMIZE FOR UNKNOWN — использовать усреднённую статистику
CREATE PROCEDURE GetOrdersByCustomer
    @CustomerID INT
AS
SELECT * FROM Orders
WHERE CustomerID = @CustomerID
OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN));

-- 2. Recompile для нестабильных запросов
CREATE PROCEDURE GetOrdersByDateRange
    @StartDate DATE,
    @EndDate DATE
AS
SELECT * FROM Orders
WHERE OrderDate BETWEEN @StartDate AND @EndDate
OPTION (RECOMPILE);

-- 3. Очистка кэша конкретного плана при деградации
DECLARE @plan_handle VARBINARY(64);
SELECT @plan_handle = plan_handle
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE text LIKE '%GetCustomerOrders%';
DBCC FREEPROCCACHE (@plan_handle);

כיצד Query Store עוזר לזהות רגרסיית תוכנית

Query Store מתעד אוטומטית את היסטוריית התוכניות ומדדי הביצועים. זה עוזר לזהות תוכנית שהחמירה משמעותית את זמן הביצוע. לדוגמה, אם -- Получить actual execution plan программно SET STATISTICS XML ON; SELECT o.OrderID, c.CustomerName, SUM(od.Quantity * od.UnitPrice) AS Total FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID JOIN OrderDetails od ON o.OrderID = od.OrderID WHERE o.OrderDate >= '2022-01-01' GROUP BY o.OrderID, c.CustomerName; SET STATISTICS XML OFF; -- Или через Query Store SELECT TOP 5 qp.query_plan, qt.query_sql_text, rs.avg_duration / 1000.0 AS avg_ms, rs.avg_logical_io_reads FROM sys.query_store_plan qp JOIN sys.query_store_query q ON qp.query_id = q.query_id JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id JOIN sys.query_store_runtime_stats rs ON qp.plan_id = rs.plan_id ORDER BY rs.avg_duration DESC; קופץ מ-50 אלפיות השנייה ל-5 שניות, Query Store לוכד את רגע שינוי התוכנית. לאחר מכן ניתן לאלץ את התוכנית הקודמת באמצעות -- Диагностика parameter sniffing SELECT TOP 10 qs.execution_count, qs.plan_generation_num, qs.total_worker_time / qs.execution_count AS avg_cpu, qs.min_worker_time, qs.max_worker_time, qt.text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt WHERE qt.text LIKE '%GetCustomerOrders%' ORDER BY qs.max_worker_time DESC; . זה מחזיר את הביצועים מיד ללא שינויי קוד. אנו משתמשים ב-Query Store ככלי ניטור בסיסי בכל הפרויקטים.

אילו אינדקסים דרושים לשאילתה ספציפית?

הצעות אינדקס חסרות מתוכניות ביצוע הן רמזים שימושיים, לא חוקים. SQL Server רואה שאילתה אחת ומציע אינדקס מבלי להתחשב בעומס הכתיבה. אנו משתמשים ב-DMV -- 1. OPTIMIZE FOR UNKNOWN — использовать усреднённую статистику CREATE PROCEDURE GetOrdersByCustomer @CustomerID INT AS SELECT * FROM Orders WHERE CustomerID = @CustomerID OPTION (OPTIMIZE FOR (@CustomerID UNKNOWN)); -- 2. Recompile для нестабильных запросов CREATE PROCEDURE GetOrdersByDateRange @StartDate DATE, @EndDate DATE AS SELECT * FROM Orders WHERE OrderDate BETWEEN @StartDate AND @EndDate OPTION (RECOMPILE); -- 3. Очистка кэша конкретного плана при деградации DECLARE @plan_handle VARBINARY(64); SELECT @plan_handle = plan_handle FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle) WHERE text LIKE '%GetCustomerOrders%'; DBCC FREEPROCCACHE (@plan_handle); ותמיד בודקים את ההשפעה על פעולות שינוי.

-- Топ missing indexes по потенциальному benefit
SELECT TOP 20
    ROUND(migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans), 0) AS estimated_improvement,
    migs.user_seeks,
    migs.user_scans,
    migs.last_user_seek,
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID('AppDB')
ORDER BY estimated_improvement DESC;

-- Неиспользуемые индексы (кандидаты на удаление)
SELECT
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    i.type_desc,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    ius.last_user_seek,
    ius.last_user_scan
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = DB_ID('AppDB')
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND i.type > 0
    AND (ius.user_seeks IS NULL OR ius.user_seeks = 0)
    AND (ius.user_scans IS NULL OR ius.user_scans = 0)
    AND (ius.user_lookups IS NULL OR ius.user_lookups = 0)
ORDER BY ius.user_updates DESC;

-- Фрагментация индексов
SELECT
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID('AppDB'), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10 AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

כלל הפרגמנטציה: 10-30% → REORGANIZE (מקוון), >30% → REBUILD. עבור טבלאות גדולות, השתמש ב-avg_duration. שימוש באינדקס מכסה יכול להפחית קריאות לוגיות פי 10 בהשוואה ל-Key Lookup. פרגמנטציה של >30% בטבלה עם 100 מיליון שורות יכולה להוסיף 20% לזמן הביצוע. REBUILD במקרים כאלה לוקח 2-3 שעות אך משתלם תוך שבוע.

מדוע חשוב לעדכן סטטיסטיקות?

האופטימייזר מסתמך על סטטיסטיקות. סטטיסטיקות מיושנות מובילות להערכות שגויות ולתוכניות גרועות.

-- Статистики, которые давно не обновлялись
SELECT OBJECT_NAME(s.object_id) AS table_name, s.name AS stat_name, sp.last_updated, sp.rows, sp.rows_sampled, sp.modification_counter, CAST(100.0 * sp.rows_sampled / NULLIF(sp.rows, 0) AS DECIMAL(5,2)) AS sample_pct
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
AND (sp.last_updated < DATEADD(DAY, -7, GETDATE()) OR sp.last_updated IS NULL)
ORDER BY sp.modification_counter DESC;

-- Обновление с полным сканированием
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

-- Для больших таблиц — сэмплирование
UPDATE STATISTICS dbo.OrderDetails WITH SAMPLE 30 PERCENT;

עדכון סטטיסטיקות אוטומטי מופעל כברירת מחדל, אך סף השינוי של 20% גבוה מדי עבור טבלאות גדולות. עבור טבלאות עם מעל 100 מיליון שורות, אנו מגדירים עבודות ידניות עם FULLSCAN פעם בשבוע. זה לוקח 2-3 שעות אך מונע הידרדרות שאילתות. שגיאת הערכת cardinality של 10% יכולה להוביל לתוכנית עם הפרש של פי 50 בזמן הביצוע.

תהליך האופטימיזציה

1. זיהוי שאילתות בעייתיות באמצעות Query Store או DMV.
2. קבלת תוכנית ביצוע בפועל; ניתוח אופרטורים בעלי עלות גבוהה.
3. אבחון: parameter sniffing, סטטיסטיקות מיושנות, אינדקס חסר, JOIN לא אופטימלי.
4. יישום שינויים: עדכון סטטיסטיקות, יצירה/שינוי אינדקסים, רמזי שאילתה.
5. מדידת תוצאות: השוואת תוכנית לפני/אחרי, מדדים.
6. בדיקת עומס בסביבת staging.

מה כלול בעבודת האופטימיזציה

• ביקורת ביצועים מפורטת עם דוח על כל שאילתה בעייתית.
• המלצות אינדקס תוך התחשבות בעומס הכתיבה.
• הגדרת עדכון סטטיסטיקות אוטומטי וניהול תוכניות.
• תיעוד שינויים והדרכה לצוות שלך.
• ניטור לאחר אופטימיזציה למשך שבוע.

המומחיות שלנו: 10+ שנים באופטימיזציה של SQL Server, 500+ פרויקטים שהושלמו. אנו מבטיחים לפחות 30% הפחתה בזמן ביצוע השאילתות. אופטימיזציה יכולה לחסוך לחברה שלך עד $6,000 בשנה בעלויות שרת ורישוי. צור קשר כדי לדון בפרויקט שלך ולקבל ייעוץ.

ציר זמן: הביקורת אורכת 3-5 ימים; אופטימיזציה מלאה בין שבוע ל-3 שבועות בהתאם להיקף. העלות נקבעת באופן אישי. קבל ייעוץ—ננתח את המקרה שלך ונציע את הפתרון האופטימלי.