Indexes: الفكرة قبل الـ Syntax
الـ Index مش زرار سحري بيخلّي كل Query أسرع. هو data structure إضافية بتساعد SQL Server يوصل للصفوف المطلوبة من غير ما يقرأ الجدول كله، لكن ليها تكلفة: مساحة تخزين، ووقت إضافي مع كل INSERT/UPDATE/DELETE، واحتمال إن الـ Optimizer يختار index غير مناسب. المطلوب إنك تفهم شكل الـ query وشكل البيانات، وبعدها تصمم index يخدم workload حقيقي.
Clustered Index: ترتيب الصفوف المنطقي على مستوى التخزين
في SQL Server، الـ clustered index leaf level هو البيانات نفسها؛ يعني الجدول بيتخزن كـ B+ tree مرتبة حسب مفتاح الـ clustered index. عشان كده الجدول يقدر يمتلك clustered index واحد فقط. ده لا يعني إن الصفوف على القرص دايمًا متجاورة physical بلا أي fragmentation، لكنه يعني إن بنية الوصول الأساسية مرتبة على المفتاح.
اختيار clustered key قرار مهم. غالبًا نفضّل key ضيق (narrow)، ثابت (immutable)، فريد (unique)، ومتزايد بشكل مناسب للـ insert pattern. المفتاح الواسع بيتكرر كـ row locator داخل nonclustered indexes، فبيكبر كل index تاني. والمفتاح الذي يتغير باستمرار قد يحرك الصفوف ويزيد تكلفة التحديث. الـ IDENTITY خيار شائع، لكنه مش قاعدة مطلقة؛ اختار حسب نمط القراءة والكتابة والـ partitioning.
CREATE TABLE dbo.Orders
(
OrderId bigint IDENTITY(1,1) NOT NULL,
CustomerId int NOT NULL,
CreatedAt datetime2(3) NOT NULL,
TotalAmount decimal(12,2) NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderId)
);الـ Primary Key والـ Clustered Index مفهومان مختلفان: الـ PK constraint تفرض uniqueness و NOT NULL، أما clustered/nonclustered فتحدد طريقة تنظيم الوصول. الافتراضي غالبًا إن PK تنشئ clustered index لو مفيش واحد موجود، لكن تقدر تختار PRIMARY KEY NONCLUSTERED وتبني clustered index على عمود آخر.
Nonclustered Index: شجرة منفصلة تشير للصف
الـ nonclustered index بنية مستقلة: مفاتيح مرتبة ومعاها row locator يوصل لصف الجدول. لو الجدول clustered، الـ locator غالبًا هو clustered key؛ ولو heap، يكون RID. لذلك استعلام يقرأ أعمدة مش موجودة في الـ index قد يحتاج Key Lookup للرجوع للصف الأصلي. كثرة الـ lookups مع عدد كبير من rows قد تجعل scan أرخص.
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON dbo.Orders (CustomerId);
SELECT OrderId, CustomerId, CreatedAt
FROM dbo.Orders
WHERE CustomerId = 42;وجود index لا يجبر الـ Optimizer على استخدامه. لو الجدول صغير، أو predicate يرجع نسبة ضخمة من الصفوف، scan قد يكون أقل تكلفة. القرار مبني على cardinality estimates وتكلفة I/O و CPU وخطة التنفيذ.
Composite Index: ترتيب الأعمدة مهم
الـ composite index يحتوي أكثر من key column. SQL Server يرتب المفتاح حسب العمود الأول، ثم الثاني داخل قيم الأول، ثم الثالث. لذلك index على (CustomerId, CreatedAt) يخدم عادةً equality على CustomerId مع range/order على CreatedAt. لكنه ليس مكافئًا لـ index على (CreatedAt, CustomerId). فكر في leftmost prefix: العمود الأول هو نقطة الدخول الأساسية للـ B-tree.
CREATE INDEX IX_Orders_Customer_Created
ON dbo.Orders (CustomerId, CreatedAt DESC)
INCLUDE (TotalAmount);
SELECT TOP (25)
OrderId, CreatedAt, TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId
ORDER BY CreatedAt DESC, OrderId DESC;الاستعلام فيه equality predicate على CustomerId، وبعده ترتيب حسب CreatedAt. أضفنا TotalAmount كـ included column لتغطية القراءة. لاحظ أن OrderId قد يكون موجودًا بالفعل كـ clustered key داخل nonclustered index كـ row locator، لكن لا تعتمد على افتراض غير واضح؛ افحص تعريف الـ index والخطة الفعلية. لو tie-breaker لازم يظهر في ترتيب محدد، تأكد أن ترتيب الـ index يدعم ذلك.
Included Columns و Covering Index
أعمدة INCLUDE موجودة في leaf level وليست جزءًا من ترتيب الـ key. استخدمها للأعمدة التي تُقرأ فقط، وليس للبحث أو الفرز. Covering index يعني أن الاستعلام يستطيع الحصول على كل الأعمدة المطلوبة من الـ index دون الرجوع للجدول/clustered index. الهدف تقليل lookups، وليس وضع كل أعمدة الجدول في index واحد.
CREATE INDEX IX_Products_Active_Category
ON dbo.Products (CategoryId, ProductId)
INCLUDE (ProductName, UnitPrice)
WHERE IsActive = 1;ده مثال على filtered index: index يحتوي subset من الصفوف فقط. مفيد عندما query pattern يتعامل دائمًا مع active products. لكن لازم الـ query predicate يكون متوافقًا مع filter definition، ولازم parameterization والـ plan يخلّوا الـ Optimizer قادر يثبت إن الاستعلام ينطبق عليه الـ filter.
Selectivity وقياس الاختيار
الـ selectivity بتوصف مدى تميّز القيم: عمود فيه مليون Email مختلف عالي التميّز، وعمود IsActive بقيمتين فقط منخفض التميّز غالبًا. لكن low-cardinality لا يعني إن index مستحيل يفيد؛ لو قيمة معينة نادرة أو filtered index مناسب، قد يكون مفيدًا. اسأل: كم صفًا يتوقع إرجاعه؟ هل الـ index يوفّر ترتيبًا؟ هل يغطي الأعمدة؟ وهل تكلفة الوصول أقل من scan؟
Seek مقابل Scan مقابل Key Lookup
Execution Plans: إزاي SQL Server نفّذ الاستعلام؟
الـ execution plan هو وصف لطريقة تنفيذ query. الـ Query Optimizer يقرأ query و schema و indexes و statistics، ويولّد بدائل ويختار خطة يقدّر أنها الأقل تكلفة. الخطة ليست ترجمة حرفية لترتيب كتابة SQL؛ الـ optimizer يقدر يعيد ترتيب joins ويدمج عمليات طالما النتيجة semantics تظل صحيحة.
Estimated vs Actual Execution Plan
Estimated plan يوضح ما توقعه الـ optimizer قبل التنفيذ. Actual plan يتضمن ما حدث فعلًا، ومنها actual row counts و runtime warnings في الأدوات التي تعرضها. الفرق الكبير بين Estimated Rows و Actual Rows إشارة إلى مشكلة cardinality estimation محتملة: statistics قديمة، correlation بين الأعمدة، parameter sensitivity، أو predicate غير سهل التقدير.
Operators لازم تتعرف عليها
| Operator | معناه العملي | ماذا تراجع؟ |
|---|---|---|
| Index Seek | الوصول لنطاق محدد من index keys | هل seek predicate انتقائي؟ كم row رجع؟ |
| Index Scan | قراءة جزء كبير أو كل index | هل scan منطقي بسبب query ترجع نسبة كبيرة؟ |
| Table/Clustered Scan | قراءة صفوف الجدول/clustered structure | هل table صغير أم مفيش access path مناسب؟ |
| Key Lookup | رجوع للصف الأصلي لجلب أعمدة ناقصة | هل lookups كثيرة؟ هل covering index يستحق؟ |
| Nested Loops | لكل row من input خارجي يبحث في input داخلي | مناسب غالبًا لمدخل صغير مع lookup سريع |
| Hash Match | يبني hash table ثم يطابق مجموعتين | راجع حجم البيانات و memory grant/spill |
| Merge Join | يمشي على inputين مرتبين | هل الترتيب متاح؟ هل sorting مكلف؟ |
| Sort | ترتيب rows | هل index يمكن أن يوفر الترتيب؟ هل يوجد spill؟ |
لا تحكم على الخطة من لون أو اسم operator وحده. Index Scan مش دائمًا سيئ، و Index Seek مش دائمًا ممتاز. Seek قد يرجع 90% من الجدول، و scan قد يكون الأفضل لجدول صغير. راقب عدد الصفوف الفعلي، logical reads، CPU، elapsed time، memory grants، spills، وعدد مرات التنفيذ.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT OrderId, CreatedAt, TotalAmount
FROM dbo.Orders
WHERE CustomerId = 42
AND CreatedAt >= '2026-01-01';
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;STATISTICS IO يوضح logical reads و physical reads وغيرها لكل object. Logical reads تعني صفحات تمت قراءتها من buffer pool، وليس بالضرورة قراءة من disk. STATISTICS TIME يعرض CPU time و elapsed time. قارن قبل/بعد على نفس dataset ونفس ظروف التشغيل، وكرر القياس لتقليل أثر cache والضوضاء.
طريقة قراءة الخطة خطوة بخطوة
SARGability: اكتب Predicate يسمح بالبحث
SARGable اختصار Search ARGument ABLE: predicate يمكن أن يساعد محرك قاعدة البيانات في تحديد range من index بدل قراءة rows كثيرة ثم اختبارها. أهم مبدأ: لا تجعل الـ column indexed محاطة بتحويل أو function في WHERE إذا كان ممكن كتابة الشرط كـ range مكافئ.
-- Often prevents a normal index seek on CreatedAt
SELECT OrderId, CreatedAt
FROM dbo.Orders
WHERE YEAR(CreatedAt) = 2026;
-- Range predicate keeps the column bare
SELECT OrderId, CreatedAt
FROM dbo.Orders
WHERE CreatedAt >= '20260101'
AND CreatedAt < '20270101';في المثال الثاني استخدمنا half-open interval: البداية inclusive والنهاية exclusive. ده يتجنب مشاكل آخر لحظة في اليوم ودقة datetime2. لو محتاج يوم معين: CreatedAt >= @StartOfDay AND CreatedAt < @NextDay. احسب الحدود في التطبيق أو في متغيرات منفصلة، ولا تعمل CAST على العمود في predicate.
Implicit conversions
لو نوع parameter مختلف عن نوع العمود، SQL Server قد يضطر يحول أحد الطرفين. التحويل على العمود قد يضر seekability أو cardinality estimation. خليك مطابقًا للـ SQL type: nvarchar column يستقبل nvarchar parameter، و int يستقبل int. في .NET استخدم parameter types الصحيحة، ولا تبني SQL string من input.
DECLARE @Email nvarchar(254) = N'user@example.com';
SELECT CustomerId, FullName
FROM dbo.Customers
WHERE Email = @Email;Leading wildcard و OR و NOT
LIKE N'%term%' غالبًا لا يستفيد من B-tree seek لأن بداية القيمة غير معروفة. Full-text search مناسب للبحث اللغوي/token-based، لكنه ليس بديلًا حرفيًا عن substring matching. كذلك WHERE ISNULL(Phone, '') = '' يلف العمود في function؛ الأفضل التعبير مباشرة: Phone IS NULL OR Phone = N'' مع فهم إن OR قد يؤثر على الخطة. لا توجد قاعدة إن كل OR سيئة؛ افحص التنفيذ.
-- Less friendly to a normal index
SELECT CustomerId
FROM dbo.Customers
WHERE ISNULL(Phone, N'') = N'';
-- Clear semantic predicate
SELECT CustomerId
FROM dbo.Customers
WHERE Phone IS NULL OR Phone = N'';مش كل function ممنوعة. الدوال على parameter أو constant غالبًا لا تضر seekability بنفس الطريقة. computed column persisted ومفهرس قد يكون حلًا لما business expression لازم يتكرر، لكن له تكلفة تخزين وصيانة ويحتاج expression deterministic ومناسبًا.
Statistics و Cardinality Estimation
Statistics تلخص توزيع القيم في عمود أو مجموعة أعمدة، وتساعد الـ optimizer يتوقع عدد الصفوف التي ستطابق predicate. أهم أجزاء histogram و density information. SQL Server ينشئ statistics تلقائيًا في حالات كثيرة، لكن auto-update لا يعني أن كل statistic دائمًا مثالية لكل workload، خصوصًا مع skew أو correlated columns أو تغيّر سريع في البيانات.
لو التقدير المتوقع 10 rows بينما الفعلي 500,000، قد يختار optimizer Nested Loops بدل Hash Join، أو يطلب memory grant صغيرًا فيحدث spill، أو العكس فيبالغ في memory grant. إذن performance issue قد يكون estimation issue قبل أن يكون missing-index issue.
-- List statistics on a table
SELECT
s.name AS StatisticsName,
s.auto_created,
s.user_created,
s.no_recompute
FROM sys.stats AS s
WHERE s.object_id = OBJECT_ID(N'dbo.Orders');
-- Inspect the histogram for one statistic
DBCC SHOW_STATISTICS
(N'dbo.Orders', N'IX_Orders_Customer_Created');في production لا تشغل update statistics عشوائيًا على كل الجداول في وقت الذروة. SQL Server لديه auto statistics maintenance، ويمكنك تحديث statistic محددة عند وجود دليل. FULLSCAN أدق لكنه أغلى، sampling أسرع وقد يكون كافيًا. قرر بناءً على حجم الجدول وتوزيع البيانات ووقت الصيانة.
UPDATE STATISTICS dbo.Orders
IX_Orders_Customer_Created
WITH RESAMPLE;Parameter sniffing / parameter sensitivity
عند compile أو recompile، قد يستخدم SQL Server parameter values لتقدير selectivity ويخزن plan لإعادة الاستخدام. لو أول قيمة نادرة جدًا واللاحقة شائعة جدًا (أو العكس)، plan واحد قد لا يناسب كل الحالات. لا تعالج المشكلة مباشرة بـ OPTION (RECOMPILE) لكل query؛ ده يضيف compile CPU. افحص Query Store، distribution، plan history، وإصدارات SQL Server، ثم اختر حلًا مناسبًا مثل إعادة كتابة query، فصل الحالات، statement-level recompile عند الحاجة، أو ميزات parameter-sensitive plan optimization حيث تنطبق.
EXEC dbo.GetOrdersByCustomer @CustomerId = 7; -- frequent customer
EXEC dbo.GetOrdersByCustomer @CustomerId = 98231; -- rare customerPagination: OFFSET/FETCH أم Keyset؟
الـ pagination لازم تعتمد على ترتيب deterministic. لو ORDER BY على CreatedAt فقط وهناك صفوف لها نفس timestamp، ترتيبها النسبي غير مضمون. أضف unique tie-breaker مثل OrderId. OFFSET/FETCH سهل للـ UI التي تطلب صفحة رقم 1 ثم 2، لكن كلما زاد OFFSET قد يحتاج المحرك يتجاوز عددًا كبيرًا من الصفوف قبل إرجاع الصفحة.
DECLARE @PageNumber int = 4;
DECLARE @PageSize int = 20;
SELECT OrderId, CreatedAt, TotalAmount
FROM dbo.Orders
ORDER BY CreatedAt DESC, OrderId DESC
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;لازم تتحقق من PageNumber و PageSize في API، وتضع حدًا أقصى للـ page size. OFFSET/FETCH يحتاج ORDER BY، لكن لا يضمن snapshot ثابتًا لو البيانات تتغير بين طلبات الصفحات. صف جديد قد يظهر أعلى القائمة فتتحرك الحدود، وقد تشاهد تكرارًا أو تخطيًا بين الصفحات.
Keyset / Seek pagination
بدل رقم الصفحة، العميل يرسل آخر مفتاح شافه (cursor). الاستعلام يطلب الصفوف التالية بعد هذا المفتاح. مع index مناسب، ده يقدر يبدأ من نقطة seek ويكون أداؤه أكثر ثباتًا في الصفحات العميقة. لكنه لا يتيح jump مباشر لأي صفحة رقم 100 بسهولة، و cursor لازم يعبّر عن ترتيب فريد.
DECLARE @LastCreatedAt datetime2(3) = '2026-10-01T10:30:00.000';
DECLARE @LastOrderId bigint = 5000;
DECLARE @PageSize int = 20;
SELECT TOP (@PageSize)
OrderId, CreatedAt, TotalAmount
FROM dbo.Orders
WHERE CreatedAt < @LastCreatedAt
OR (CreatedAt = @LastCreatedAt AND OrderId < @LastOrderId)
ORDER BY CreatedAt DESC, OrderId DESC;لـ descending order، الشرط يختار مفاتيح أصغر من آخر cursor. لازم يكون عندك index يبدأ بترتيب مناسب مثل (CreatedAt DESC, OrderId DESC)، أو تصميم index متوافق مع access pattern. احفظ القيمتين في cursor opaque وموقّع في API لو لا تريد العميل يعبث بهما. لو query متعددة filters، cursor يجب أن يشمل مفاتيح الترتيب ويظل متوافقًا مع نفس filter set.
Concurrency: Locks و Blocking و Deadlocks عمليًا
الـ concurrency تظهر عندما transactions متزامنة تقرأ وتعدل نفس البيانات أو موارد مرتبطة. SQL Server يستخدم locks و row versioning حسب isolation level والإعدادات. Blocking طبيعي أحيانًا: transaction تمسك lock و transaction أخرى تنتظر. المشكلة لما مدة الانتظار أو حجم التنافس يضر النظام.
Lock modes ومدة المعاملة
Shared locks تستخدم لحماية القراءة في مستويات تعتمد على locking؛ Exclusive locks تحمي التعديل. Intent locks توضح وجود locks أدنى في hierarchy. Locks قد تكون على key/row/page/table، وقد يحدث escalation. لا تفترض إن كل UPDATE يقفل row واحدة فقط أو إن SELECT دائمًا لا يأخذ locks. Isolation level و query plan وحجم العملية كلها تؤثر.
أقوى إجراء عملي لتقليل blocking هو تقصير transaction: لا تفتح transaction ثم تعمل network call أو تنتظر user input. اقرأ/تحقق، نفّذ database work بسرعة، ثم commit. اجعل ترتيب الوصول للموارد متسقًا عبر العمليات المختلفة.
BEGIN TRANSACTION;
UPDATE dbo.Products
SET StockQuantity = StockQuantity - @Quantity
WHERE ProductId = @ProductId
AND StockQuantity >= @Quantity;
IF @@ROWCOUNT = 0
BEGIN
ROLLBACK TRANSACTION;
THROW 50001, 'Product not found or insufficient stock.', 1;
END;
COMMIT TRANSACTION;العملية دي تجمع التحقق والتعديل في statement واحدة؛ فتمنع race condition الشائعة: requestان يقرآن stock=1 ثم كلاهما يوافق على شراء الوحدة. الـ WHERE guard مع UPDATE atomic يجعل واحدة فقط تنجح. في تطبيق حقيقي أضف handling واضحًا للـ error وضمن أن @Quantity موجب.
Isolation levels باختصار تطبيقي
| Level | الضمان/المخاطر |
|---|---|
| READ UNCOMMITTED | يسمح dirty reads؛ لا تستخدمه كحل سحري للـ blocking. |
| READ COMMITTED | الافتراضي غالبًا؛ يمنع dirty reads. قد يستخدم locks أو row versions إذا RCSI مفعّل. |
| REPEATABLE READ | يحافظ على rows المقروءة من التغيير حتى نهاية transaction، لكنه لا يمنع كل phantoms. |
| SERIALIZABLE | أقوى نطاق حماية، وقد يسبب range locks و blocking أكبر. |
| SNAPSHOT | قراءة نسخة متسقة على مستوى transaction باستخدام row versions، مع احتمال update conflict. |
RCSI (READ_COMMITTED_SNAPSHOT) يغير سلوك READ COMMITTED ليقرأ row versions بدل shared locks في كثير من الحالات. SNAPSHOT isolation يتطلب تفعيل ALLOW_SNAPSHOT_ISOLATION واستخدام SET TRANSACTION ISOLATION LEVEL SNAPSHOT. Row versioning يقلل reader-writer blocking لكنه يزيد استخدام version store ويحتاج مراقبة tempdb أو persistent version store حسب الإعداد والإصدار.
ALTER DATABASE MyShop
SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
-- Separate option for explicit SNAPSHOT isolation:
ALTER DATABASE MyShop
SET ALLOW_SNAPSHOT_ISOLATION ON;Deadlock: دائرة انتظار، وليست مجرد بطء
Deadlock يحصل لما transaction A تمسك resource وتنتظر resource عند B، و B تمسك resource وتنتظر resource عند A. SQL Server يكتشف cycle ويختار victim transaction ويلغيها برسالة deadlock victim. التطبيق يجب أن يتعامل مع deadlock كخطأ قابل لإعادة المحاولة بحذر، مع bounded retries و jitter، فقط لو العملية آمنة للتكرار أو محمية بـ idempotency.
-- Transaction pattern A and B should touch tables
-- in the same order: Customer first, then Orders.
BEGIN TRANSACTION;
UPDATE dbo.Customers
SET FullName = @Name
WHERE CustomerId = @CustomerId;
UPDATE dbo.Orders
SET Status = @Status
WHERE OrderId = @OrderId;
COMMIT TRANSACTION;اقرأ deadlock graph من Extended Events system_health أو event session مخصص. ابحث عن victim، process list، resource list، lock modes، statements، indexes، و execution plans. اسأل: هل ترتيب الوصول مختلف؟ هل transaction طويلة؟ هل index ناقص يجعل statement تمسح نطاقًا أكبر؟ هل query تمسك locks أكثر من اللازم؟ لا تكتفِ بزيادة timeout؛ ده لا يصلح cycle.
Database Security: أقل صلاحية وحدود ثقة واضحة
الـ database تحتوي بيانات حساسة، فلا تجعل connection string بصلاحيات db_owner للتطبيق لمجرد السهولة. استخدم least privilege: application principal يحصل على أقل permissions يحتاجها. افصل identities الخاصة بالتطبيق، migrations، reporting، و DBA. لا تضع credentials داخل source control أو HTML أو logs؛ استخدم secret store/managed identity حيث البيئة تدعم ذلك.
Parameterized SQL ضد SQL Injection
لا تدمج user input داخل SQL string. Parameterization تفصل command structure عن values. الـ parameters لا تستبدل أسماء الجداول أو الأعمدة أو اتجاه ORDER BY؛ identifiers تحتاج allow-list mapping، وليس concatenation حرًا.
await using var command = connection.CreateCommand();
command.CommandText = """
SELECT CustomerId, FullName
FROM dbo.Customers
WHERE Email = @Email;
""";
var emailParameter = command.CreateParameter();
emailParameter.ParameterName = "@Email";
emailParameter.DbType = DbType.String;
emailParameter.Size = 254;
emailParameter.Value = email;
command.Parameters.Add(emailParameter);
await using var reader = await command.ExecuteReaderAsync();var sortColumn = sortBy switch
{
"created" => "CreatedAt",
"total" => "TotalAmount",
_ => "OrderId"
};
// sortColumn comes only from this fixed allow-list.
var sql = $"SELECT OrderId, CreatedAt, TotalAmount " +
$"FROM dbo.Orders ORDER BY {sortColumn} DESC";الـ interpolation هنا آمن فقط لأن identifier مصدره switch ثابت، وليس قيمة المستخدم كما هي. في أغلب ORM مثل EF Core استخدم LINQ parameters، وتجنب FromSqlRaw مع concatenated input. FromSqlInterpolated parameterizes values، لكنه لا يجعل identifiers الديناميكية آمنة تلقائيًا.
Permissions و Encryption و Row-Level Security
GRANT/REVOKE/DENY تحدد الصلاحيات. استخدم database roles بدل grant لكل user. TDE يحمي ملفات database و log at rest، لكنه لا يخفي البيانات من مستخدم لديه صلاحية query. Always Encrypted يهدف لحماية قيم محددة حتى من بعض مسارات وصول DBA، لكنه له قيود على العمليات والـ indexing حسب نوع التشفير. TLS يحمي النقل. كل طبقة لها threat model مختلف.
Row-Level Security (RLS) يطبق predicate function على الصفوف حسب execution context، مثل TenantId أو user context. مفيد كطبقة defense-in-depth، لكنه لا يغني عن authorization في API، ويحتاج اختبارًا دقيقًا حتى لا يحصل tenant على بيانات غيره عبر connection pooling أو session context غير مضبوط.
CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_TenantPredicate(@TenantId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @TenantId = TRY_CONVERT(int, SESSION_CONTEXT(N'TenantId'));
GO
CREATE SECURITY POLICY Security.TenantPolicy
ADD FILTER PREDICATE Security.fn_TenantPredicate(TenantId)
ON dbo.Orders
WITH (STATE = ON);التطبيق يضبط SESSION_CONTEXT لكل connection قبل تنفيذ queries، ويجب أن يكون الإعداد آمنًا ومتسقًا مع pooling. اختبر missing context، wrong tenant، background jobs، admin workflows، و connection reuse. الـ RLS predicate لا ينبغي أن يعتمد على context يستطيع المستخدم تغييره بحرية من مسار غير موثوق.
Backup و Restore: النسخة الاحتياطية ليست خطة تعافٍ
Backup يحفظ نسخة يمكن استخدامها للاستعادة، لكن وجود ملف .bak لا يثبت أن الشركة تستطيع الرجوع للعمل. لازم تحدد RPO (أقصى فقد بيانات مقبول زمنيًا) و RTO (الوقت المقبول للعودة)، تختار recovery model، تعمل backup schedule، تحفظ النسخ في مكان منفصل/محمي، وتختبر restore دوريًا.
| Backup | ماذا يحتوي؟ | متى يُستخدم؟ |
|---|---|---|
| Full | قاعدة البيانات كاملة كنقطة أساس | بداية سلسلة النسخ، أو حسب سياسة التشغيل |
| Differential | التغييرات منذ آخر full backup | تقليل عدد الملفات المطلوبة في restore |
| Transaction log | سجل المعاملات منذ آخر log backup | Point-in-time recovery مع FULL/BULK_LOGGED |
في SIMPLE recovery لا تعمل log backups بنفس نموذج FULL. في FULL recovery لازم log backup منتظم وإلا log قد ينمو. Restore chain تعتمد على full ثم differential المناسب ثم log backups بالتتابع، مع مراعاة LSNs ووقت التوقف المطلوب. لا تحذف log backup file قبل التأكد أن سياسة retention تسمح بذلك.
BACKUP DATABASE MyShop
TO DISK = N'E:\SqlBackups\MyShop_full.bak'
WITH INIT, COMPRESSION, CHECKSUM;
BACKUP DATABASE MyShop
TO DISK = N'E:\SqlBackups\MyShop_diff.bak'
WITH DIFFERENTIAL, INIT, COMPRESSION, CHECKSUM;
BACKUP LOG MyShop
TO DISK = N'E:\SqlBackups\MyShop_log.trn'
WITH INIT, COMPRESSION, CHECKSUM;المسارات هنا مثال محلي فقط، ويجب أن تكون متاحة لحساب خدمة SQL Server. في بيئة حقيقية استخدم naming/retention policy ونسخًا خارج host/region حسب متطلباتك. BACKUP CHECKSUM يساعد في اكتشاف corruption أثناء النسخ والتحقق، لكنه ليس بديلًا عن restore test.
RESTORE VERIFYONLY
FROM DISK = N'E:\SqlBackups\MyShop_full.bak';
RESTORE DATABASE MyShop_RestoreTest
FROM DISK = N'E:\SqlBackups\MyShop_full.bak'
WITH MOVE N'MyShop' TO N'E:\SqlData\MyShop_RestoreTest.mdf',
MOVE N'MyShop_log' TO N'E:\SqlData\MyShop_RestoreTest_log.ldf',
RECOVERY, REPLACE;RESTORE VERIFYONLY فحص محدود؛ الاختبار الحقيقي هو restore إلى instance/test environment ثم DBCC CHECKDB، فحص application smoke tests، وقياس الوقت. احتفظ بمفاتيح التشفير والشهادات اللازمة لاستعادة TDE/backup encryption. النسخة المشفرة بلا مفاتيحها قد تصبح غير قابلة للاستخدام.
Soft Delete و Auditing و Change History
Hard delete يزيل الصف فعليًا (مع مراعاة foreign keys والـ logs والنسخ الاحتياطية). Soft delete يعلّم الصف بأنه محذوف منطقيًا مثل IsDeleted و DeletedAt، فيختفي من queries العادية لكنه يظل موجودًا. ده مناسب عندما تحتاج restore أو retention، لكنه يفرض discipline: كل read path لازم يفلتر، والـ uniqueness rules لازم تراعي deleted rows، والـ reports/admin workflows لازم تكون واضحة.
ALTER TABLE dbo.Customers
ADD IsDeleted bit NOT NULL
CONSTRAINT DF_Customers_IsDeleted DEFAULT (0),
DeletedAt datetime2(3) NULL;
CREATE UNIQUE INDEX UX_Customers_ActiveEmail
ON dbo.Customers(Email)
WHERE IsDeleted = 0;
SELECT CustomerId, FullName, Email
FROM dbo.Customers
WHERE IsDeleted = 0;قبل إضافة filtered unique index تأكد من وجود duplicates تاريخية، ومن semantics المطلوبة: هل يسمح بإعادة استخدام Email بعد soft delete؟ لو لا، لا تستخدم filter بهذه الطريقة. EF Core global query filters قد تخفي deleted rows تلقائيًا، لكن query filters ليست security boundary؛ يمكن تجاوزها بـ IgnoreQueryFilters، ويجب التعامل مع navigation/joins و admin screens بعناية.
Auditing يجيب: مين غيّر؟ إمتى؟ إيه القيمة القديمة والجديدة؟ ولماذا؟ الخيارات تشمل audit table داخل transaction، SQL Server Audit للأنشطة الأمنية، Change Data Capture للتغييرات لأغراض downstream، و Temporal Tables لتاريخ row versions. كل أداة لها هدف مختلف؛ لا تخلط audit trail business مع telemetry أو database-level security audit.
CREATE TABLE dbo.CustomerAudit
(
AuditId bigint IDENTITY PRIMARY KEY,
CustomerId int NOT NULL,
ChangedAt datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
ChangedBy nvarchar(128) NOT NULL,
ActionName varchar(20) NOT NULL,
OldEmail nvarchar(254) NULL,
NewEmail nvarchar(254) NULL
);لو audit row لازم يضمن atomicity مع business update، اكتب الاثنين داخل نفس transaction. احمِ audit table من التعديل/الحذف بواسطة app principal، وقرر retention/PII masking. Audit log قابل للتعديل بواسطة نفس identity التي تسجل التغييرات لا يقدم non-repudiation قويًا.
Temporal Tables: تاريخ الصفوف تلقائيًا
System-versioned temporal table تحتفظ بإصدارات سابقة من الصفوف في history table، باستخدام period columns مثل SysStartTime و SysEndTime. عند UPDATE/DELETE، SQL Server ينقل النسخة السابقة إلى history table تلقائيًا. ده ممتاز لسؤال: ما القيمة التي كانت موجودة في وقت سابق؟ لكنه لا يسجل وحده business actor أو سبب التغيير، ولا يحل كل متطلبات audit.
CREATE TABLE dbo.CustomerProfile
(
CustomerId int NOT NULL PRIMARY KEY,
DisplayName nvarchar(120) NOT NULL,
Email nvarchar(254) NOT NULL,
ValidFrom datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
ValidTo datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON
(HISTORY_TABLE = dbo.CustomerProfileHistory));SELECT CustomerId, DisplayName, Email
FROM dbo.CustomerProfile
FOR SYSTEM_TIME AS OF '2026-10-01T12:00:00';
SELECT *
FROM dbo.CustomerProfile
FOR SYSTEM_TIME ALL
WHERE CustomerId = 42
ORDER BY ValidFrom;History table قد تكبر بسرعة؛ صمم retention/cleanup و indexes بما يناسب الاستعلامات. التعديلات داخل transaction لها دلالات زمنية مرتبطة بوقت transaction، وليس بالضرورة وقت business event الفعلي. لو تحتاج actor/reason، أضف audit metadata منفصلة.
Query Store و Missing-Index DMVs
Query Store يحتفظ بتاريخ query texts و plans و runtime statistics داخل database، ويساعدك تعرف متى query كانت سريعة ثم أصبحت بطيئة بعد plan change أو deployment أو تغير بيانات. فعّله وفق سياسة المؤسسة، راقب storage/cleanup، واستخدمه لمقارنة plans على فترات زمنية.
ALTER DATABASE MyShop
SET QUERY_STORE = ON
(
OPERATION_MODE = READ_WRITE,
CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
DATA_FLUSH_INTERVAL_SECONDS = 900,
MAX_STORAGE_SIZE_MB = 1000
);واجهة SSMS تعرض Top Resource Consuming Queries و Regressed Queries و Plan history. تقدر تستخدم Query Store hints في الإصدارات المدعومة لتجربة hint دون تعديل query text، لكن أي hint يحتاج قياس ومراجعة، وممكن يصبح ضارًا مع تغير البيانات.
Missing-index DMVs تقترح indexes بناءً على workload التي مرّت على optimizer منذ آخر restart/clear. الاقتراحات لا تعرف دائمًا indexes الحالية بشكل كامل، وقد تقترح عدة indexes متداخلة أو index ضخم مع INCLUDE كثيرة. راجع equality/inequality/include columns، overlap، write cost، filtered-index possibility، ثم اختبر الفائدة الفعلية.
SELECT TOP (20)
migs.avg_user_impact,
migs.user_seeks,
migs.user_scans,
mid.statement AS TableName,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
JOIN sys.dm_db_missing_index_groups AS mig
ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details AS mid
ON mid.index_handle = mig.index_handle
ORDER BY migs.avg_user_impact *
(migs.user_seeks + migs.user_scans) DESC;Partitioning: إدارة نطاقات ضخمة
Partitioning تقسم table/index منطقيًا إلى partitions حسب partition function و scheme، غالبًا حسب تاريخ أو نطاق key. الاستعلام الذي يحدد partition key بشكل مناسب قد يستفيد من partition elimination. لكن partitioning لا يجعل كل query أسرع تلقائيًا، ولا يغني عن indexes أو تصميم predicates. قيمته الكبيرة قد تكون في manageability: switch partitions، archive/drop old ranges، rebuild partitions بشكل مستقل، أو توزيع storage.
CREATE PARTITION FUNCTION pf_OrderMonth(date)
AS RANGE RIGHT FOR VALUES
(
'2026-01-01',
'2026-02-01',
'2026-03-01'
);ده يوضح حدود التقسيم فقط؛ إنشاء partition scheme يتطلب filegroups أو destinations، ثم وضع table/index عليها مع partitioning column مناسب. RANGE RIGHT يحدد أي partition تستقبل قيمة الحد. راجع boundary semantics قبل switch أو archive. لو partition key ليس موجودًا في query predicate، قد يضطر المحرك يقرأ partitions كثيرة.
Multi-Tenancy Models
في SaaS متعدد العملاء، database design يحدد كيف تفصل بيانات tenants. لا يوجد نموذج واحد يناسب الجميع؛ القرار trade-off بين isolation، التكلفة، التخصيص، التشغيل، compliance، وسهولة analytics.
| Model | المميزات | التحديات |
|---|---|---|
| Shared database + shared schema | أقل تكلفة وتشغيل أسهل؛ كل tables تحمل TenantId | خطر cross-tenant bug؛ indexes و RLS و tenant-aware queries ضرورية |
| Shared database + schema per tenant | فصل schema وتخصيص نسبي | عدد schemas/migrations يكبر؛ tooling و cross-tenant reporting أصعب |
| Database per tenant | عزل أقوى، backup/restore و geo placement مستقل | تكلفة وتشغيل ومراقبة ومigrations على قواعد كثيرة |
في shared schema، اجعل TenantId جزءًا من unique constraints والـ indexes التي تحتاجه، ولا تعتمد على أن المطور يتذكر إضافته في كل query. RLS يمكن أن يكون defense-in-depth، لكنه يحتاج session context آمنًا. في database-per-tenant، تحتاج catalog يربط tenant بقاعدة بياناته، connection management، migration orchestration، ومراقبة fleet كاملة.
CREATE TABLE dbo.Documents
(
DocumentId bigint IDENTITY PRIMARY KEY,
TenantId int NOT NULL,
ExternalCode nvarchar(50) NOT NULL,
Title nvarchar(200) NOT NULL,
CONSTRAINT UQ_Documents_Tenant_Code
UNIQUE (TenantId, ExternalCode)
);
CREATE INDEX IX_Documents_Tenant_Created
ON dbo.Documents (TenantId, DocumentId DESC)
INCLUDE (ExternalCode, Title);الـ composite unique constraint يسمح لعميلين باستخدام نفس ExternalCode، لكنه يمنع التكرار داخل نفس tenant. ده مثال على تحويل قاعدة business إلى invariant داخل database. يجب أن تظل API authorization و tenant resolution موثوقين، مع اختبارات تمنع tenant A من الوصول إلى tenant B عبر ID مباشر أو background task.
Zero-Downtime Schema Changes
تغيير schema في production خطر لما التطبيق القديم والجديد يعملان في نفس الوقت أثناء rolling deployment. الحل الشائع expand-migrate-contract: أضف التغيير بطريقة backward-compatible، انشر code يفهم الشكلين أو يكتب الاثنين مؤقتًا، املأ البيانات تدريجيًا، ثم انقل القراءة، وبعد التأكد احذف القديم في release لاحق.
ALTER TABLE dbo.Customers
ADD NormalizedEmail nvarchar(254) NULL;
-- Backfill in controlled batches in production.
UPDATE TOP (1000) dbo.Customers
SET NormalizedEmail = LOWER(LTRIM(RTRIM(Email)))
WHERE NormalizedEmail IS NULL;الكود أعلاه يوضح الفكرة وليس batch loop جاهزًا لكل production. خطط للـ batching، throttling، restartability، error logging، و validation. لا تضف NOT NULL مع default ضخم دون فهم locking/version behavior وحجم الجدول وإصدار SQL Server. EF Core migrations تحتاج مراجعة SQL الناتج بدل تنفيذ migration blindly أثناء deployment.
Full-Text Search Basics
LIKE مع %word% يبحث عن substring، وغالبًا يحتاج scan. Full-Text Search يبني inverted index على tokens/words، ويدعم linguistic matching و prefix/phrase/inflectional forms حسب اللغة والـ configuration. هو مناسب للبحث في وصف ومقالات ومستندات، لكنه لا يعطي دائمًا تطابق substring حرفيًا.
-- Requires a full-text catalog/index configured for the table.
SELECT ProductId, ProductName
FROM dbo.Products
WHERE CONTAINS(ProductDescription, '"wireless" OR "bluetooth"');قبل استخدامه، حدد language/word breaker، stoplists، columns، unique key index، update population strategy، وسلوك ranking باستخدام CONTAINSTABLE/FREETEXTTABLE. اختبر العربي والإنجليزي حسب المحتوى الفعلي؛ stemming/tokenization والـ stop words تؤثر على النتائج. لا تعتبر Full-Text Search بديلًا عن search engine عندما تحتاج distributed relevance ranking أو faceting أو typo tolerance متقدمة.
Vector Data Type Awareness للـ AI Features
مع تطبيقات AI قد تحتاج تخزن embeddings: arrays من floating-point values تمثل النص أو الصورة في vector space. SQL Server أضاف vector capabilities في الإصدارات الحديثة/preview حسب الإصدار والبيئة؛ لذلك تحقق من availability والـ compatibility level والـ syntax الفعلي في نسختك قبل اعتمادها. لا تفترض أن كل SQL Server 2022 instance تدعم نفس ميزات vector الحديثة.
الـ vector ليس بديلًا عن relational columns. احتفظ بـ entity key، tenant، source text/reference، model name/version، embedding dimensions، created timestamp، و metadata. عند تغيير embedding model أو dimension قد تحتاج إعادة embedding؛ خزّن model/version حتى لا تقارن vectors غير متوافقة.
-- Syntax/features vary by SQL Server release and preview status.
CREATE TABLE dbo.KnowledgeChunks
(
ChunkId bigint IDENTITY PRIMARY KEY,
TenantId int NOT NULL,
Content nvarchar(max) NOT NULL,
EmbeddingModel nvarchar(100) NOT NULL,
Embedding vector(1536) NOT NULL
);Vector similarity search عادةً تقيس distance مثل cosine أو Euclidean أو dot product. اختيار metric يجب أن يطابق طريقة تدريب/تطبيع embedding model. Approximate nearest neighbor indexes trade perfect recall for speed/memory. قيّم recall@k و latency على corpus حقيقي، وطبّق tenant filtering قبل أو أثناء retrieval حسب guarantees التي توفرها التقنية. لا تعتمد على vector similarity كـ authorization: لازم تتحقق من صلاحية الوصول للـ source document بعد retrieval.
Production Checklist: من Query بطيئة إلى قرار صحيح
لما endpoint بقى بطيئًا، لا تبدأ بإنشاء index. امشِ على سلسلة أدلة قصيرة:
أخطاء شائعة لازم تتجنبها
- إنشاء index لكل عمود ثم تجاهل تكلفة الكتابة والتخزين.
- اعتبار Index Scan سيئًا دائمًا أو Index Seek ممتازًا دائمًا.
- استخدام SELECT * في endpoint يعرض 3 أعمدة فقط.
- استخدام OFFSET بمئات الآلاف من الصفوف في feed سريع التغير دون تقييم keyset.
- زيادة CommandTimeout بدل تشخيص blocking أو bad plan.
- إعادة محاولة deadlock بلا حد أو على عملية غير idempotent.
- اعتبار backup ناجحًا دون restore drill.
- الاعتماد على soft delete أو RLS كبديل عن API authorization.
عندك query ترجع 15 صفًا، لكن Actual Plan يظهر 300,000 logical reads. ما أول شيء تراجعه؟
هل أضيف كل الأعمدة في INCLUDE حتى ألغي Key Lookup؟
ما الفرق بين RCSI و SNAPSHOT؟
هل Query Store missing-index suggestion أمر أنفذه مباشرة؟
ما الفرق بين Temporal Table و Audit Log؟
هل Vector search يضمن أن النتيجة مسموح للمستخدم يقرأها؟
المراجع والقراءة الإضافية
الدرس بيشرح الفكرة نفسها؛ الروابط دي للتحقق والتعمق والرجوع لتفاصيل الـ API. (محتاجة إنترنت.)
التوثيق الرسمي — Official documentation
خريطة التغطية: 22 عنصر من الـ Roadmap ومكان شرحه في الدرس
خلصت الدرس وحليت الأسئلة؟ علّم عليه عشان يتحسب في تقدّمك.