Bits of .NET

⚡ Query Hints = Performance

SQL needs optimization. Query hints help the optimizer choose better execution plans.

📝 Query Hints

# INDEX Hint
SELECT * FROM users WITH (INDEX(idx_users_name))
WHERE name = 'John';

# FORCESEEK Hint
SELECT * FROM orders WITH (FORCESEEK)
WHERE order_date > '2024-01-01';

# NOLOCK (Read Uncommitted)
SELECT * FROM orders WITH (NOLOCK)
WHERE status = 'pending';

# OPTIMIZE FOR
SELECT * FROM users
WHERE status = @status
OPTION (OPTIMIZE FOR (@status = 'active'));

# RECOMPILE
SELECT * FROM orders
WHERE user_id = @userId
OPTION (RECOMPILE);

# MAXDOP
SELECT * FROM large_table
OPTION (MAXDOP 2);

🎯 Advanced Hints

# Force Order
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
OPTION (FORCE ORDER);

# Merge Join Hint
SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id
OPTION (MERGE JOIN);

# Hash Join Hint
SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id
OPTION (HASH JOIN);

# Loop Join Hint
SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id
OPTION (LOOP JOIN);

# Fast Forward Cursor
DECLARE cursor CURSOR FAST_FORWARD FOR
SELECT * FROM users;

# Query Governor
SET QUERY_GOVERNOR_COST_LIMIT 100;

# Plan Guides
EXEC sp_create_plan_guide
    @name = N'Guide1',
    @stmt = N'SELECT * FROM users WHERE id = @id',
    @type = N'OBJECT',
    @module_or_batch = N'[dbo].[GetUser]',
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@id = 1))';

💡 Query Hints Tips

• Use hints sparingly
• Test with real data
• Monitor performance
• Document hint usage
• Remove unused hints