Detect Slow Queries in SQL Server with DMVs

Parameter Sniffing in SQL Server — Not a Bug, But a Double-Edged Sword I often see developers blame parameter sniffing for performance issues… But here’s the reality: It’s actually a feature designed for performance optimization. SQL Server sniffs the first parameter value passed to a query or stored procedure and creates an execution plan based on that value. That plan is then reused to avoid expensive recompilations. Sounds wonderful, right?  Until it isn’t… When Things Go Wrong Parameter sniffing becomes a problem when: • Data distribution is skewed • Queries are parameter sensitive. • One plan doesn’t fit all scenarios Example: One parameter returns 10 rows Another returns 1 million rows Same cached plan = drastically different performance Common Symptoms • The query is fast sometimes and slow other times • Sudden CPU spikes • High IO for the same query • Inconsistent execution times How to Fix It (Smartly) There’s no one-size-fits-all solution: OPTION (RECOMPILE) → Best plan every time (but CPU cost) OPTIMIZE FOR UNKNOWN → Stable, average plan OPTIMIZE FOR (@param = value) → Controlled behavior Local variables → Avoid sniffing (with trade-offs) Dynamic SQL → Fresh plans Query Store → Force stable plans in production Pro Tip (From Real Projects) Don’t blindly disable parameter sniffing. Instead: Identify parameter-sensitive queries Analyze execution plans Choose the right fix based on workload Key Takeaway Parameter sniffing is not the enemy. Misunderstanding it is. Master it, and you unlock next-level SQL Server performance tuning. Have you faced parameter sniffing issues in production? What solution worked best for you? #SQLServer #PerformanceTuning #DatabaseOptimization #DataEngineering #SQLTips #QueryOptimization

Writing the same SQL query again and again? Use 𝗩𝗶𝗲𝘄𝘀. A View is like a 𝘀𝗮𝘃𝗲𝗱 𝗦𝗤𝗟 𝗾𝘂𝗲𝗿𝘆 that you can treat like a table. Instead of rewriting complex queries, you just do: 𝗦𝗘𝗟𝗘𝗖𝗧 * 𝗙𝗥𝗢𝗠 𝗮𝗰𝘁𝗶𝘃𝗲_𝘂𝘀𝗲𝗿𝘀_𝘃𝗶𝗲𝘄; Clean. Simple. Reusable. Why Views are powerful in complex queries:  • Hide complicated joins and logic  • Reuse the same query across multiple places  • Provide a simplified “read-only” layer  • Restrict access to sensitive data (security layer) Real-world example: Instead of writing a big query joining users + orders + payments… Create a view 𝗼𝗻𝗰𝗲, and use it 𝗲𝘃𝗲𝗿𝘆𝘄𝗵𝗲𝗿𝗲. Now the important part What happens when you INSERT, UPDATE, DELETE? For simple views (single table, no aggregation) You can perform insert/update/delete For complex views (joins, group by, etc.) Mostly read-only Because the database can’t always figure out how to map changes back to original tables. Types of Views: 🔹 Simple View → Based on one table 🔹 Complex View → Multiple tables, joins, functions 🔹 Materialized View → Stores data physically (faster reads ⚡) But here’s the catch: Views don’t store data (except materialized ones) So performance depends on the underlying query. Real insight Views don’t just simplify queries… They simplify how you think about data. Next time your SQL looks messy, don’t rewrite it… 𝗪𝗿𝗮𝗽 𝗶𝘁. #Database #SQL #PostgreSQL #RelationalDatabase #QueryOptimization #BackendDevelopment #SoftwareEngineering #Developers #Programming #SpringFramework #SpringBoot #ScalableSystems #Microservices #aswintech 

An application was passing customer_id as VARCHAR from the ORM. The column was INT. SQL Server converted 12 million INT rows to VARCHAR before every comparison. 22 seconds per query. Changed the ORM to pass INT. 0.04 seconds. Same query. Same index. Just the correct data type. Day 25 of 60 — SQL Server from Scratch to Master. Today: Execution Plans — the single most powerful skill for diagnosing slow queries. The 5-step process I follow every time a query is slow: SET STATISTICS IO ON → enable Actual Plan → find the most expensive operator → compare Estimated vs Actual row counts → fix the root cause. Most slow queries have one or two root causes. This process finds them every time. The logical reads number in STATISTICS IO output tells you more than query duration. Duration varies with server load. Logical reads is consistent — it measures how many 8KB pages SQL Server read. Before adding an index: note the reads. After: compare. If they drop from 50,000 to 8 — your index works. Have you ever fixed a slow query using the execution plan? What was the red flag you found? ♻ Repost — reading execution plans is what separates senior SQL developers from the rest. #SQLServer #TSQL #SQLDeveloper #LearnSQL #SQLServerFromScratch #SQLServer2019 #DatabaseDeveloper #OpenToWork #TechIndia #ITJobsIndia #HiringIndia #IndianDeveloper #LinkedInIndia #ImmediateJoiner #SQLJobs #100DaysOfCode #SQLTips #ExecutionPlans #QueryOptimization #BackendDeveloper #MicrosoftSQL #DataEngineering #Performance #SQLTutorial

Most SQL is anonymous. It should not be. ✅ In EF Core, Query Tags close a real observability gap. They let you attach intent directly to your LINQ queries using TagWith, and that context is emitted into the generated SQL as comments. Now when you open a profiler or query logs, you are not reverse engineering queries. You see exactly which feature, service, or call path produced them. This matters more than people think: In large systems, dozens of queries can look identical at the SQL level. Without context, debugging turns into guesswork. Where this pays off most: 1. Debugging incorrect results or unexpected data paths 2. Tracing slow queries back to a specific feature or service 3. Understanding production traffic without digging through multiple layers of code 4. Correlating logs, metrics, and database activity with real intent A few things to keep in mind: 1. Tags are static strings and do not support runtime interpolation 2. Compiled queries require tags to be defined at compile time 3. Keep tags short, descriptive, and diagnostic It is a tiny feature. But it upgrades how you observe, debug, and reason about your system in production. — ♻️ Share this with your network ➕ Follow me [Elliot One] 🔔 Enable Notifications

One SQL Transaction Can Save Your Data… or Destroy It ⚠ SQL transactions look simple… until you face real scenarios 😅 While working with SQL Server, I came across some situations that completely changed how I write queries: 💡 BEGIN → UPDATE → ROLLBACK ✔ Changes are safely undone 💡 UPDATE without BEGIN ❌ Auto-committed → No rollback possible 💡 BEGIN → UPDATE → COMMIT → ROLLBACK ❌ Once committed, rollback won’t work 💡 BEGIN → UPDATE → BEGIN again ⚠ Previous transaction gets committed automatically 💡 ROLLBACK ✔ Only affects changes after BEGIN ❌ Old data remains unchanged 📌 Key Learning: Transactions are not just commands — they are your safety net. One wrong query without control = permanent data loss ⚠ Now I always follow: 👉 BEGIN → VERIFY → EXECUTE → VERIFY → COMMIT / ROLLBACK Small habit… big impact 🚀 #SQL #SQLServer #Database #BackendDevelopment #TechLearning #Developers #CodingLife

💡 Understanding SQL Triggers: AFTER vs INSTEAD OF While working with databases, I recently revisited SQL Triggers and realized how powerful (and sometimes misunderstood) they can be. Here’s a quick breakdown 👇 🔹 AFTER Trigger Runs after an INSERT, UPDATE, or DELETE operation. 📌 Common use cases: ✔️ Auditing changes ✔️ Logging activity ✔️ Enforcing business rules 👉 Example: Logging every time a user record is updated 🔹 INSTEAD OF Trigger Runs instead of the actual operation and overrides default behavior. 📌 Common use cases: ✔️ Handling complex updates ✔️ Working with views ✔️ Applying custom validation logic 👉 Example: Preventing deletion or customizing insert logic on a view ⚖️ Key Difference ➡️ AFTER → Executes after the action completes ➡️ INSTEAD OF → Replaces the action entirely 🚀 My Takeaway Triggers are powerful—but should be used carefully. Overusing them can: ⚠️ Make debugging harder ⚠️ Hide business logic in the database In many cases, handling logic in the application layer (e.g., .NET services) is cleaner and more maintainable. 💬 What’s your approach? Do you prefer handling business logic in the database or in application code? #SQL #Database #DotNet #BackendDevelopment #SoftwareEngineering #Learning #CleanCode

The Advanced SQL Course - A Production Support Perspective 📊🔍 Recently completed The Advanced SQL Course, and it reinforced how critical strong SQL skills are - especially in production support environments. In real-world systems, data issues are often at the heart of incidents, and advanced SQL knowledge can make a huge difference in response time and accuracy. Here’s how it connects to production support: 1. Faster incident resolution by writing efficient queries to trace data issues 2. Ability to analyze large datasets and identify anomalies quickly 3. Improved performance tuning to handle slow queries in live systems 4. Better understanding of joins, indexing, and query optimization for critical fixes At the same time, it highlights a few realities: 1. A small query mistake in production can have big consequences 2. Understanding the data model is just as important as writing queries 3. Precision and validation are key when working with live data Strong SQL skills aren’t just for developers — they’re essential for anyone involved in keeping systems stable and reliable. Grateful for the deeper insights, and looking forward to applying them in real-world scenarios. #SQL #ProductionSupport #DataAnalysis #DatabaseManagement #PerformanceTuning #ContinuousLearning

Most people use SQL every day — but do you know the 12 rules that define a TRUE relational database? 📋 CODD'S 12 RULES — SIMPLIFIED ━━━━━━━━━━━━━━━━━━━━ ✅ Rule 1 — Information Rule All data is stored in tables (rows & columns). Period. ✅ Rule 2 — Guaranteed Access Reach any value using: Table Name + Primary Key + Column Name ✅ Rule 3 — NULL Handling NULL ≠ zero or blank. It means UNKNOWN. Treat it consistently. ✅ Rule 4 — Active Online Catalog DB metadata (schema, tables) is stored AS data — queryable like any table. ✅ Rule 5 — Powerful Language One language (SQL) covers DDL + DML + transactions. No exceptions. ✅ Rule 6 — View Update Rule Views must be updatable wherever logically possible. ✅ Rule 7 — Relational Operations INSERT / UPDATE / DELETE + set operations (UNION, INTERSECT) all supported. ✅ Rule 8 — Physical Independence Change your storage engine or file structure? Your app shouldn't care. ✅ Rule 9 — Logical Independence Add or rename columns? Existing user views stay unaffected. ✅ Rule 10 — Integrity Independence Constraints belong in the DATABASE — not scattered across your application code. ✅ Rule 11 — Distribution Independence Data can live across multiple servers — users see one unified system. ✅ Rule 12 — Non-Subversion Rule No backdoor bypass of integrity rules. Low-level access cannot override constraints.  

🚀 Indexing in SQL Server: What I Got Wrong Early in My Career Early on, whenever a query was slow, my instinct was simple: “Add an index.” And it worked… initially. But things changed once I saw it in production. On a write-heavy table (10k+ inserts/day), reads got faster — but insert latency started increasing as indexes grew. That’s when it clicked: indexing isn’t just optimization, it’s a trade-off. What I started noticing • Read performance improved, but writes became slower • Extra indexes added overhead during INSERT/UPDATE • Some indexes were never even used • What worked in dev didn’t behave the same at scale Where things usually go wrong • Adding indexes without understanding query patterns • Creating multiple single-column indexes instead of effective composite indexes • Ignoring the cost of maintaining indexes during writes What changed for me • Designing indexes based on actual queries (WHERE, JOIN, ORDER BY) • Using composite indexes where they truly add value • Reviewing execution plans instead of guessing • Periodically identifying and removing unused indexes One insight that stuck with me, Sometimes, removing an index improves overall performance more than adding one. Final Thoughts Indexes are not just about faster queries. They are part of your data design decisions. The goal isn’t maximum speed for one query, it’s balanced performance across the system.