-- ❌ SLECHTE PRACTICE: Geen plan analyse
SELECT * FROM large_table WHERE column1 = 'value';

-- ✅ GOEDE PRACTICE: Plan analysis voor alle queries

-- SQL Server: Actual execution plan
SET STATISTICS PROFILE ON;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT 
    c.CustomerID,
    c.CompanyName,
    COUNT(o.OrderID) AS OrderCount,
    SUM(od.Quantity * od.UnitPrice) AS TotalSpent
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID
INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
WHERE o.OrderDate >= '1998-01-01'
GROUP BY c.CustomerID, c.CompanyName
HAVING COUNT(o.OrderID) > 10;

-- PostgreSQL: EXPLAIN ANALYZE met details
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON)
SELECT 
    c.customer_id,
    c.company_name,
    COUNT(o.order_id) AS order_count,
    SUM(od.quantity * od.unit_price) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_details od ON o.order_id = od.order_id
WHERE o.order_date >= '1998-01-01'
GROUP BY c.customer_id, c.company_name
HAVING COUNT(o.order_id) > 10;

-- ✅ GOEDE PRACTICE: Plan warning indicators check

-- SQL Server: Find queries with plan warnings
SELECT 
    qs.plan_handle,
    qs.query_hash,
    qs.query_plan_hash,
    st.text AS query_text,
    qp.query_plan,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; 
    count(//p:Warnings)', 'int') > 0
ORDER BY qs.total_worker_time DESC;

-- PostgreSQL: Analyze plan efficiency
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    100.0 * shared_blks_hit / 
        NULLIF(shared_blks_hit + shared_blks_read, 0) AS hit_percent,
    CASE 
        WHEN 100.0 * shared_blks_hit / 
             NULLIF(shared_blks_hit + shared_blks_read, 0) < 90 
        THEN 'POOR'
        WHEN 100.0 * shared_blks_hit / 
             NULLIF(shared_blks_hit + shared_blks_read, 0) < 95 
        THEN 'FAIR'
        ELSE 'GOOD'
    END AS cache_efficiency
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- ✅ GOEDE PRACTICE: Geavanceerde index strategieën

-- 1. Filtered indexes voor partiële data
CREATE INDEX idx_orders_active 
ON orders (customer_id, order_date)
WHERE status = 'active';

-- 2. Included columns voor covering indexes
CREATE INDEX idx_customers_search 
ON customers (last_name, first_name)
INCLUDE (email, phone, city);

-- 3. Columnstore indexes voor analytics
CREATE COLUMNSTORE INDEX idx_columnstore_orders 
ON order_history (order_id, product_id, quantity, price, order_date);

-- 4. Partitioned indexes voor grote tabellen
CREATE INDEX idx_orders_partitioned 
ON orders (order_date)
ON ps_order_date (order_date);  -- Partition scheme

-- 5. Index met computed columns
ALTER TABLE products 
ADD name_lower AS LOWER(product_name);

CREATE INDEX idx_products_name_lower 
ON products (name_lower);

-- 6. Geospatial indexes
CREATE SPATIAL INDEX idx_customers_location 
ON customers (location);

-- ✅ GOEDE PRACTICE: Geautomatiseerde index maintenance

-- Dynamische index reorganisatie/rebuild
DECLARE @threshold_fragmentation FLOAT = 30.0;  -- Rebuild boven 30%
DECLARE @threshold_reorganize FLOAT = 10.0;  -- Reorganize 10-30%

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql +
    CASE 
        WHEN avg_fragmentation_in_percent > @threshold_fragmentation 
        THEN 
            'ALTER INDEX ' + QUOTENAME(i.name) + 
            ' ON ' + QUOTENAME(OBJECT_NAME(ips.object_id)) + 
            ' REBUILD WITH (ONLINE = ON); '
        WHEN avg_fragmentation_in_percent > @threshold_reorganize 
        THEN 
            'ALTER INDEX ' + QUOTENAME(i.name) + 
            ' ON ' + QUOTENAME(OBJECT_NAME(ips.object_id)) + 
            ' REORGANIZE; '
    END
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id 
    AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > @threshold_reorganize
    AND ips.page_count > 1000  -- Alleen voor grote indexes
    AND i.name IS NOT NULL
    AND ips.alloc_unit_type_desc = 'IN_ROW_DATA';

-- Uitvoeren als er werk te doen is
IF LEN(@sql) > 0
BEGIN
    PRINT 'Executing index maintenance...';
    EXEC sp_executesql @sql;
END;

-- PostgreSQL: Index bloat monitoring
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched,
    CASE 
        WHEN idx_scan = 0 THEN 'UNUSED'
        WHEN idx_tup_read > idx_tup_fetch * 100 THEN 'INEFFICIENT'
        ELSE 'HEALTHY'
    END AS index_status
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexname::regclass) DESC;

-- ❌ ANTI-PATTERN: Correlated subquery in SELECT
SELECT 
    c.customer_id,
    c.company_name,
    (
        SELECT COUNT(*)
        FROM orders o
        WHERE o.customer_id = c.customer_id
    ) AS order_count
FROM customers c;

-- ✅ SOLUTION: Gebruik JOIN met GROUP BY
SELECT 
    c.customer_id,
    c.company_name,
    COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.company_name;

-- ❌ ANTI-PATTERN: OR conditions met verschillende columns
SELECT *
FROM products
WHERE category_id = 5 
    OR supplier_id = 10;

-- ✅ SOLUTION: Gebruik UNION voor betere index usage
SELECT *
FROM products
WHERE category_id = 5
UNION
SELECT *
FROM products
WHERE supplier_id = 10;

-- ❌ ANTI-PATTERN: Functions in WHERE clause
SELECT *
FROM orders
WHERE YEAR(order_date) = 2024
    AND MONTH(order_date) = 6;

-- ✅ SOLUTION: Range queries gebruiken
SELECT *
FROM orders
WHERE order_date >= '2024-06-01'
    AND order_date < '2024-07-01';

-- ❌ ANTI-PATTERN: NOT IN met NULL values
SELECT *
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id 
    FROM orders 
    WHERE status = 'cancelled'
);

-- ✅ SOLUTION: Gebruik NOT EXISTS of LEFT JOIN
SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
        AND o.status = 'cancelled'
);

-- Scenario: Complex multi-join query

-- ❌ SUBOPTIMAAL: Default join volgorde
SELECT 
    c.company_name,
    o.order_date,
    od.product_id,
    p.product_name,
    od.quantity,
    od.unit_price,
    s.company_name AS supplier_name
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_details od ON o.order_id = od.order_id
INNER JOIN products p ON od.product_id = p.product_id
INNER JOIN suppliers s ON p.supplier_id = s.supplier_id
WHERE o.order_date >= '2024-01-01'
    AND c.country = 'Netherlands'
ORDER BY o.order_date DESC;

-- ✅ OPTIMAAL: Strategic join volgorde
SELECT 
    c.company_name,
    o.order_date,
    od.product_id,
    p.product_name,
    od.quantity,
    od.unit_price,
    s.company_name AS supplier_name
FROM customers c  -- Start met filtered table
INNER JOIN orders o ON c.customer_id = o.customer_id
    AND o.order_date >= '2024-01-01'  -- Filter early
INNER JOIN order_details od ON o.order_id = od.order_id
INNER JOIN products p ON od.product_id = p.product_id
INNER JOIN suppliers s ON p.supplier_id = s.supplier_id
WHERE c.country = 'Netherlands'  -- Al gefilterd
ORDER BY o.order_date DESC;

-- ✅ OPTIMAAL: Gebruik van query hints (alleen als nodig)
SELECT 
    c.company_name,
    o.order_date,
    od.product_id,
    p.product_name,
    od.quantity,
    od.unit_price
FROM customers c WITH (INDEX(idx_customers_country))
INNER HASH JOIN orders o ON c.customer_id = o.customer_id
INNER LOOP JOIN order_details od ON o.order_id = od.order_id
WHERE c.country = 'Netherlands'
    AND o.order_date >= '2024-01-01'
OPTION (
    RECOMPILE,  -- Fresh plan voor parameters
    MAXDOP 4    -- Limit parallelism
);

-- Scenario: Complexe data aggregatie

-- OPTIE 1: CTE (Common Table Expression)
-- Goed voor leesbaarheid, maar kan meermaals geëvalueerd worden
WITH MonthlySales AS (
    SELECT 
        customer_id,
        DATE_TRUNC('month', order_date) AS sales_month,
        SUM(total_amount) AS monthly_total,
        COUNT(*) AS order_count
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY customer_id, DATE_TRUNC('month', order_date)
),
CustomerStats AS (
    SELECT 
        customer_id,
        AVG(monthly_total) AS avg_monthly_spend,
        SUM(monthly_total) AS total_spend,
        COUNT(DISTINCT sales_month) AS active_months
    FROM MonthlySales
    GROUP BY customer_id
)
SELECT 
    c.customer_id,
    c.company_name,
    cs.avg_monthly_spend,
    cs.total_spend,
    cs.active_months
FROM customers c
INNER JOIN CustomerStats cs ON c.customer_id = cs.customer_id
WHERE cs.active_months >= 3
ORDER BY cs.total_spend DESC;

-- OPTIE 2: Derived Table (Inline View)
-- Soms beter voor performance in complexe scenario's
SELECT 
    c.customer_id,
    c.company_name,
    dt.avg_monthly_spend,
    dt.total_spend,
    dt.active_months
FROM customers c
INNER JOIN (
    SELECT 
        o.customer_id,
        AVG(monthly.monthly_total) AS avg_monthly_spend,
        SUM(monthly.monthly_total) AS total_spend,
        COUNT(DISTINCT monthly.sales_month) AS active_months
    FROM orders o
    INNER JOIN (
        SELECT 
            customer_id,
            DATE_TRUNC('month', order_date) AS sales_month,
            SUM(total_amount) AS monthly_total
        FROM orders
        WHERE order_date >= '2024-01-01'
        GROUP BY customer_id, DATE_TRUNC('month', order_date)
    ) monthly ON o.customer_id = monthly.customer_id
    GROUP BY o.customer_id
) dt ON c.customer_id = dt.customer_id
WHERE dt.active_months >= 3
ORDER BY dt.total_spend DESC;

-- OPTIE 3: Lateral Join (PostgreSQL)
-- Voor complexe correlated subqueries
SELECT 
    c.customer_id,
    c.company_name,
    recent_orders.order_count,
    recent_orders.total_spent
FROM customers c
CROSS JOIN LATERAL (
    SELECT 
        COUNT(*) AS order_count,
        SUM(total_amount) AS total_spent
    FROM orders o
    WHERE o.customer_id = c.customer_id
        AND o.order_date >= CURRENT_DATE - '90 days'::INTERVAL
) recent_orders
WHERE recent_orders.order_count > 0;

-- Scenario: Complex data processing met tussentijdse resultaten

-- ✅ TEMP TABLE: Goed voor grote datasets, ondersteunt indexes en statistics
CREATE TABLE #OrderSummary (
    customer_id INT NOT NULL,
    order_year INT NOT NULL,
    order_month INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    order_count INT NOT NULL,
    PRIMARY KEY (customer_id, order_year, order_month)
);

-- Voeg index toe voor betere join performance
CREATE INDEX idx_temp_order_summary 
ON #OrderSummary (order_year, order_month);

-- Vul temp table met geaggregeerde data
INSERT INTO #OrderSummary
SELECT 
    customer_id,
    YEAR(order_date) AS order_year,
    MONTH(order_date) AS order_month,
    SUM(total_amount) AS total_amount,
    COUNT(*) AS order_count
FROM orders
WHERE order_date >= '2023-01-01'
GROUP BY 
    customer_id, 
    YEAR(order_date), 
    MONTH(order_date);

-- Gebruik temp table in complexe query
SELECT 
    c.customer_id,
    c.company_name,
    os.order_year,
    os.order_month,
    os.total_amount,
    os.order_count,
    LAG(os.total_amount) OVER (
        PARTITION BY c.customer_id 
        ORDER BY os.order_year, os.order_month
    ) AS prev_month_amount
FROM customers c
INNER JOIN #OrderSummary os ON c.customer_id = os.customer_id
ORDER BY c.customer_id, os.order_year, os.order_month;

-- Cleanup
DROP TABLE #OrderSummary;

-- ✅ TABLE VARIABLE: Goed voor kleine datasets (< 1000 rows)
DECLARE @RecentOrders TABLE (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL
);

-- Insert recent orders
INSERT INTO @RecentOrders
SELECT 
    order_id,
    customer_id,
    order_date,
    total_amount
FROM orders
WHERE order_date >= DATEADD(day, -30, GETDATE());

-- Gebruik in query (geen cleanup nodig)
SELECT 
    c.company_name,
    ro.order_date,
    ro.total_amount,
    SUM(ro.total_amount) OVER (
        PARTITION BY c.customer_id
    ) AS customer_total
FROM customers c
INNER JOIN @RecentOrders ro ON c.customer_id = ro.customer_id;

-- ✅ CTE met MATERIALIZED hint (PostgreSQL 12+)
-- Forceert materialization van CTE resultaat
WITH MATERIALIZED CustomerOrders AS (
    SELECT 
        customer_id,
        COUNT(*) AS order_count,
        SUM(total_amount) AS total_spent
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY customer_id
)
SELECT 
    c.customer_id,
    c.company_name,
    co.order_count,
    co.total_spent
FROM customers c
INNER JOIN CustomerOrders co ON c.customer_id = co.customer_id
WHERE co.order_count > 5;

-- ✅ GOEDE PRACTICE: Parallel query configuration

-- Check huidige parallelism settings
SELECT 
    name,
    value,
    value_in_use,
    description
FROM sys.configurations
WHERE name LIKE '%parallel%';

-- Find queries using parallelism
SELECT 
    qs.sql_handle,
    qs.query_hash,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2)+1) AS query_text,
    qp.query_plan,
    qs.execution_count,
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
    qs.total_physical_reads / qs.execution_count AS avg_physical_reads
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; 
    count(//p:RelOp[@Parallel = "1"])', 'int') > 0
ORDER BY qs.total_worker_time DESC;

-- Voorbeeld: Parallel query met cost threshold
SELECT 
    c.customer_id,
    c.company_name,
    o.order_id,
    o.order_date,
    od.product_id,
    od.quantity,
    od.unit_price,
    p.product_name
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_details od ON o.order_id = od.order_id
INNER JOIN products p ON od.product_id = p.product_id
WHERE o.order_date >= '2023-01-01'
AND c.country = 'Netherlands'
ORDER BY o.order_date DESC;

-- Gebruik MAXDOP hint om parallelism te controleren
SELECT 
    c.customer_id,
    c.company_name,
    COUNT(*) AS order_count,
    SUM(o.total_amount) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2023-01-01'
GROUP BY c.customer_id, c.company_name
HAVING COUNT(*) > 10
OPTION (
    MAXDOP 4,            -- Maximum degree of parallelism
    USE HINT ('ENABLE_PARALLEL_PLAN_PREFERENCE')
);

-- Disable parallelism voor kleine queries
SELECT 
    c.customer_id,
    c.company_name,
    o.order_date,
    o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id = 12345
OPTION (MAXDOP 1);  -- Forceer serial execution

-- PostgreSQL: Parallel query hints
SET max_parallel_workers_per_gather = 4;
SET parallel_setup_cost = 1000;
SET parallel_tuple_cost = 0.1;

SELECT /*+ Parallel(customers 4) */
    c.customer_id,
    c.company_name,
    COUNT(o.order_id) AS order_count,
    SUM(o.total_amount) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2023-01-01'
GROUP BY c.customer_id, c.company_name;

-- ✅ GOEDE PRACTICE: Statistics monitoring en update

-- Check verouderde statistics
SELECT 
    OBJECT_NAME(s.object_id) AS table_name,
    s.name AS statistics_name,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter,
    CASE 
        WHEN sp.modification_counter > sp.rows * 0.20 
            THEN 'UPDATE NEEDED'
        ELSE 'OK'
    END AS update_status
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE OBJECT_NAME(s.object_id) NOT LIKE 'sys%'
ORDER BY sp.modification_counter DESC;

-- Automatische statistics update voor specifieke tabel
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql = @sql + 
    'UPDATE STATISTICS ' + QUOTENAME(OBJECT_NAME(s.object_id)) + 
    ' ' + QUOTENAME(s.name) + 
    ' WITH FULLSCAN; '
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE OBJECT_NAME(s.object_id) = 'orders'
    AND sp.modification_counter > sp.rows * 0.20;

EXEC sp_executesql @sql;

-- Create filtered statistics voor partitioned data
CREATE STATISTICS stats_orders_2024 
ON orders (customer_id, order_date, total_amount)
WHERE order_date >= '2024-01-01';

-- Update statistics met sample rate
UPDATE STATISTICS orders 
WITH SAMPLE 50 PERCENT, 
    PERSIST_SAMPLE_PERCENT = ON;

-- PostgreSQL: Statistics management
-- Check statistics
SELECT 
    schemaname,
    tablename,
    attname,
    n_distinct,
    correlation,
    most_common_vals,
    most_common_freqs
FROM pg_stats
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
AND tablename = 'orders';

-- Manual statistics update
ANALYZE VERBOSE orders;

-- Extended statistics voor correlated columns
CREATE STATISTICS orders_customer_date (customer_id, order_date)
ON orders;

-- Statistics voor expressies
CREATE STATISTICS orders_year_month (EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date))
ON orders;

-- ✅ GOEDE PRACTICE: Comprehensive monitoring toolkit

-- 1. Top 10 langst lopende queries
SELECT TOP 10
    qs.total_elapsed_time / 1000000.0 AS total_elapsed_time_sec,
    qs.total_worker_time / 1000000.0 AS total_worker_time_sec,
    qs.total_logical_reads,
    qs.total_physical_reads,
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS avg_elapsed_time_sec,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2)+1) AS query_text,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.execution_count > 0
ORDER BY qs.total_elapsed_time DESC;

-- 2. Wait statistics analysis
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms / waiting_tasks_count AS avg_wait_time_ms,
    100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS pct_total_waits
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',
    'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'WAITFOR',
    'BROKER_EVENTHANDLER', 'BROKER_RECEIVE_WAITFOR',
    'CHECKPOINT_QUEUE', 'CHKPT', 'XE_TIMER_EVENT'
)
AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

-- 3. Missing indexes analysis
SELECT 
    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    DB_NAME(mid.database_id) AS database_name,
    OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.user_seeks,
    migs.user_scans,
    migs.last_user_seek,
    migs.avg_total_user_cost,
    migs.avg_user_impact
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.dm_db_missing_index_groups mig 
    ON mid.index_handle = mig.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats migs 
    ON mig.index_group_handle = migs.group_handle
WHERE mid.database_id = DB_ID()
ORDER BY improvement_measure DESC;

-- 4. Blocking chain analysis
SELECT 
    blocking.session_id AS blocking_session_id,
    blocked.session_id AS blocked_session_id,
    blocking_text.text AS blocking_text,
    blocked_text.text AS blocked_text,
    blocking.wait_type AS blocking_wait_type,
    blocked.wait_type AS blocked_wait_type,
    blocking.wait_time AS blocking_wait_time,
    blocked.wait_time AS blocked_wait_time
FROM sys.dm_exec_requests blocked
INNER JOIN sys.dm_exec_requests blocking 
    ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_text
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) blocking_text
WHERE blocked.blocking_session_id > 0;

-- 5. Memory usage monitoring
SELECT 
    type,
    name,
    pages_kb / 1024.0 AS pages_mb,
    virtual_memory_reserved_kb / 1024.0 AS vm_reserved_mb,
    virtual_memory_committed_kb / 1024.0 AS vm_committed_mb,
    awe_allocated_kb / 1024.0 AS awe_mb
FROM sys.dm_os_memory_clerks
WHERE pages_kb > 0
ORDER BY pages_kb DESC;