Optimize SQL Queries with Execution Plan Cheat Sheet

The query that taught me the most about SQL performance: A query running 4 minutes on a 50M row table. The fix took 30 seconds. Here's what I learned: The query had a WHERE clause filtering on YEAR(transaction_date) = 2023. This function-wrapped column prevented partition pruning. The database scanned all 50M rows. Fix: WHERE transaction_date >= '2023-01-01' AND transaction_date < '2024-01-01' Result: 9 seconds. The partition pruning now skipped 11 of 12 monthly partitions. The lesson: any function applied to a filtered column in a WHERE clause breaks partition pruning and index usage. Common culprits: → DATE(), YEAR(), MONTH() on date columns → CAST() or CONVERT() on join columns → LOWER() or UPPER() on string filter columns Write predicates that let the engine use its optimization structures. #SQL #QueryOptimization #DataEngineering #Performance #Snowflake

“CTEs don’t store data… and that matters more than people think.” I still see this misunderstood in a lot of SQL Server queries. Common Table Expressions (CTEs) are great for readability. They help break complex logic into steps. But they don’t behave like temp tables. Every time a CTE is referenced, SQL Server can re-evaluate it as part of the execution plan. On small datasets? No big deal. On large datasets? • Repeated scans of the same data • More work for the optimizer • Slower overall performance Especially when a CTE is referenced multiple times in the same query. In those cases, I’ll often switch to: 👉 Temp tables Because they: • Materialize the data once • Can be indexed • Reduce repeated work in complex queries CTEs are great for clarity. Temp tables are often better for performance. 👉 The key is knowing when each one actually makes sense. Because in SQL Server, how you structure the query can matter just as much as the logic itself. #SQLServer #PerformanceTuning #DataEngineering #HealthcareIT #QueryOptimization

A slow SQL query can quietly damage system performance. Recently, I worked on a query in a live environment that was taking several seconds to execute. After analyzing the execution plan, I noticed: A full table scan instead of an index seek Unnecessary data being selected Missing index on a frequently filtered column By applying a few changes: Added a proper non-clustered index Reduced selected columns Adjusted query structure The execution time dropped significantly. This is a common issue I see in real systems — small inefficiencies that grow into serious performance problems. If your SQL Server feels slow, the issue is often easier to fix than expected. #SQLServer #DatabasePerformance #TSQL #QueryOptimization

⚡ SQL Days 27 & 28: Subquery Mastery These two days focused on moving from basic syntax to advanced logic building, learning to break complex problems into nested steps. 🧠 Logic Flow Inner Query: Runs first to find a specific value (e.g., the average price). Outer Query: Uses that value to filter the final results. 🛠️ The 3 Essential Types Single-Row: Returns one value. Use with =, >, <. Multi-Row: Returns a list. Use with IN, ANY, or ALL. Correlated: Runs once for every row in the outer query. Powerful for row-by-row comparisons but can be slower. 💡 Key Takeaways Performance: If a subquery is too slow, consider using a JOIN. Inside-Out: Always test the inner query first to ensure the data types match. Day 29 Loading... #SQLJourney #Subqueries #DataLogic #SQLTips

Today I came across an interesting pitfall while working with the LAST_VALUE() function in SQL Server. At first glance, it seems straightforward to use LAST_VALUE() with an ORDER BY ASC clause to fetch the last value in a partition. However, the default window frame only considers rows up to the current row, not the entire partition. This can lead to unexpected results. To get the actual last value across the full dataset, you need to explicitly define the window frame: 👉 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING Alternatively, a cleaner workaround is to use FIRST_VALUE() with ORDER BY DESC, which often achieves the same result more intuitively. Small detail, but a great reminder that understanding window frame behavior is crucial when working with SQL window functions. #SQLServer #SQLTips #DataEngineering #LearnSQL #WindowFunctions

Day 26/90 — SQL Series | Week 4 "My WHERE clause is not filtering dates correctly." "My SUM is returning NULL instead of a number." 9 times out of 10 — it's a data type mismatch. CAST and CONVERT fix it. CAST('250' AS INT) → turns text into number so you can do math CAST('2024-01-15' AS DATE) → turns text into date so filters work CAST(order_id AS VARCHAR) → turns number into text for concatenation Rule: use CAST (works everywhere). Use CONVERT only when you need date formatting in SQL Server. #SQL #CastConvert #DataAnalytics #LearnSQL #DataAnalyst

🚀 SQL Basics – Day 9: Stored Procedures (Super Simple) Today let’s learn how to save SQL logic and reuse it anytime 💡 👇 🔍 What is a Stored Procedure? 👉 A saved SQL code 👉 You can run it anytime 🧠 “Like a function in SQL” --- 📌 Create Procedure 💡 "CREATE PROCEDURE GetEmployees() BEGIN SELECT * FROM employees; END;" 👉 Save your query --- ▶️ Run Procedure 💡 "CALL GetEmployees();" 👉 Execute anytime --- ✏️ Procedure with Parameter 💡 "CREATE PROCEDURE GetByDept(IN dept_name VARCHAR(50)) BEGIN SELECT * FROM employees WHERE department = dept_name; END;" 👉 Pass value while running --- 😄 Easy way to remember: Procedure = Saved code CREATE = Save CALL = Run Parameter = Input value --- ✨ Conclusion: Stored procedures save time and reduce repeated work 💪 They make your SQL more powerful and reusable 🚀 📌 Work smart by writing once and using many times! #SQL #DataAnalytics #SQLBasics #StoredProcedure #LearningSQL #Day9 #DataAnalyst

𝗪𝗮𝗶𝘁 𝘀𝘁𝗮𝘁𝘀 are usually one of the first things I look at in a SQL Server performance analysis. Not because they explain the problem immediately, but because they give the discussion a more concrete starting point. Before that, you often only have descriptions like “it is slow”, “users are waiting”, or “something changed since yesterday”. The tricky part starts when the waits are sorted and one value clearly stands out.  I have seen this often enough: the wait type is familiar, the number is high, and the analysis starts to move in that direction almost automatically. It feels reasonable. It feels technical. It also feels much more certain than it actually is. A high wait type does not prove the 𝗿𝗼𝗼𝘁 𝗰𝗮𝘂𝘀𝗲  It tells you where SQL Server spent time waiting under the workload and conditions you captured. That is useful, but it still needs to be connected to what was happening at the time. Which workload was active?   Which queries were involved?   Was the waiting concentrated in a specific time window?   Was there blocking?   Did an execution plan change?   Did the application or client behavior contribute to the waits? That is usually where the useful part of the analysis begins. Wait stats are a strong signal. I rely on them a lot. But I do not trust them as a standalone explanation. The highest wait type is a place to start looking more carefully, not a place to stop thinking. #SQLServer #PerformanceTuning #DatabasePerformance #WaitStats #Troubleshooting #DataEngineering #SQLTips

CTE vs Temp Table – Which Performs Better? 🤔 I often see this question come up in SQL discussions, so here’s a simple, practical take from a performance perspective. A CTE is great for readability and quick logic. It’s lightweight and perfect when the result set is small and used only once. But it’s important to remember that a CTE has usually not materialized, SQL Server can re-evaluate it each time it’s referenced. A temp table, on the other hand, is physically stored in tempdb. That means: ✅ It can be indexed ✅ It has statistics ✅ It can be reused across multiple statements Because of this, temp tables often perform much better for large datasets, complex joins, or reporting scenarios (SSRS, BI queries, heavy aggregations). 👉 Rule of thumb: Use CTEs for clarity and single-use logic Use temp tables when performance matters and data is reused There’s no universal winner, execution plans and data size always decide #SqlServer #SSMS #SQLDatabaseAdiministration #SQL

Ever wondered why your T-SQL queries work… but don’t scale? 🤔 Here’s a quick reminder that changed how I write SQL: 👉 “If you don’t understand the execution plan, you don’t understand your query.” A simple example: Many developers write: SELECT * FROM Orders WHERE YEAR(OrderDate) = 2024; Looks fine, right? But this forces SQL Server to scan every row because the function blocks index usage. ✅ A better approach: SELECT * FROM Orders WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'; 💡 Why this matters: Enables index seeks instead of scans Reduces IO and improves performance Scales much better with large datasets 📌 Small changes in T-SQL can have massive impact. What’s one query optimization trick you wish you learned earlier? #SQLServer #TSQL #DatabasePerformance #DataEngineering #TechTips