Being a SQL database administrator means never settling for tempdb defaults. Why be average when you can be exceptional? #DBA #SQL #sqlserver #mssql #TechHumor

• TempDB configuration and contention • Memory pressure and buffer cache efficiency • Blocking and deadlock patterns • Maintenance plans and statistics updates • Backup integrity and restore validation

While TempDB improvements in #SQLServer2025 may not attract the same attention as new AI features, they deliver some of the most impactful operational changes. Find out more: sqlmct.com/tempdb-improvemen…

Is your EC2 instance connected to a database? If yes, read this before enabling Auto Scaling especially if your DB is RDS SQL Server. There is always a scaling limit, and it’s defined by your database, not EC2. Auto Scaling doesn’t remove that limit. It only helps you reach it faster. Here’s what breaks first: 1. Connection pools · Each EC2 instance has its own DB connection pool. · Scaling from 2 → 10 instances = 10 pools. · Result: login spikes, authentication overhead, CPU spikes on RDS. 2. CPU saturation · RDS CPU does not auto-scale. · More EC2 instances = more queries = more CPU pressure on the same DB. 3. Storage & transaction log pressure · Higher concurrency increases writes, tempdb usage, and log I/O. Before enabling Auto Scaling, know the maximum number of app instances your RDS can handle. Scaling past that point doesn’t improve performance. #CloudComputing #aws

SQL Interview Questions which can be asked in a Data Analyst Interview. 1️⃣ What is difference between Primary key and Unique key? ◼Primary key- A column or set of columns which uniquely identifies each record in a table. It can't contain null values and only one primary key can exist in a table. ◼Unique key-Similar to primary key it also uniquely identifies each record in a table and can contain null values.Multiple Unique key can exist in a table. 2️⃣ What is a Candidate key? ◼A key or set of keys that uniquely identifies each record in a table. It is a combination of Primary and Alternate key. 3️⃣ What is a Constraint? ◼Specific rule or limit that we define in our table. E.g - NOT NULL,AUTO INCREMENT 4️⃣ Can you differentiate between TRUNCATE and DELETE? ◼TRUNCATE is a DDL command. It deletes the entire data from a table but preserves the structure of table. It doesn't deletes the data row by row hence faster than DELETE command, while DELETE is a DML command and it deletes the entire data based on specified condition else deletes the entire data,also it deletes the data row by row hence slower than TRUNCATE command. 5️⃣ What is difference between 'View' and 'Stored Procedure'? ◼A View is a virtual table that gets data from the base table .It is basically a Select statement,while Stored Procedure is a sql statement or set of sql statement stored on database server. 6️⃣ What is difference between a Common Table Expression and temporary table? ◼CTE is a temporary result set that is defined within execution scope of a single SELECT ,DELETE,UPDATE statement while temporary table is stored in TempDB and gets deleted once the session expires. 7️⃣ Differentiate between a clustered index and a non-clustered index? ◼ A clustered index determines physical ordering of data in a table and a table can have only one clustered index while a non-clustered index is analogous to index of a book where index is stored at one place and data at other place and index will have pointers to storage location of the data,a table can have more than one non-clustered index. 8️⃣ Explain triggers ? ◼They are sql codes which are automatically executed in response to certain events on a table.They are used to maintain integrity of data.

Our favorite content for this week (0926)! More info: buff.ly/ID12ovK #ladataplatform #vscode #dbre #git #dba #failover #hadr #ssms #tempdb #numa #sqlserver #features

Connecting to SQL Server when TempDB Transaction Log is Full - curatedsql.com/2025/09/19/co… #CuratedSQL

SQLのクエリコストとメモリ(tempdb含む)が最悪になるように複数のテーブルからデータセット作ってみて、ってプロンプト書いてみたいけど、いつもエディタで見慣れたクエリが帰ってきそうで怖い　(´･ω･｀)

Client: Really? What’s the plan? Me: Over Zoom (no password sharing!), we’ll walk through it all: 🛠️ Server settings ⚙️ DB parameters 📚 Index health 📈 Query tuning 💽 I/O waits 🔧 TempDB config ✅ DBCC script toolkit

A query without tuning breathes just fine, But it's a song that lacks its rhyme. The stats are stale, the plan’s unclear— What’s a tune when no one hears? Indexes sleep while CPU cries, TempDB grows, the deadline dies. You call it running—I see a stall. Without the fix, it's not a call.

🚨 3 Performance Fixes I Made at a Client Site That Changed Everything 🚨 Sometimes ignorance is bliss—until your system crashes during peak load. I recently worked with a client who said, “We just have one slow report.” What we found was much bigger: a set of silent performance killers hiding in plain sight. Here are the three key fixes I made that had an immediate impact: ✅ 1. Replaced Scalar Functions with Inline Table-Valued Functions They had a scalar UDF used in a report that ran 2M times per execution. Issue: It forced serial execution and killed parallelism. Fix: Rewrote it as an inline TVF and used CROSS APPLY. Result: Report runtime dropped from 18 minutes to 34 seconds. 🔍 Root cause: Devs didn’t review execution plans or understand function impact on query optimizer. ✅ 2. Cleaned Up 38 Indexes on One Table (!) One critical table had 38 indexes—many unused or overlapping. Issue: Sluggish writes, bloated storage, poor batch job performance. Fix: Dropped 26 unused indexes, merged others, and added filtered indexes. Result: Write performance improved 3x; update jobs finished in ¼ the time. 🔍 Root cause: No indexing strategy. Indexes were added reactively and never reviewed. ✅ 3. Reconfigured TempDB Properly Yes, they still had one TempDB data file—on a 16-core machine. Issue: Severe allocation contention, frequent deadlocks, high CPU. Fix: Added 8 TempDB data files, enabled trace flag 1118, and balanced sizes. Result: Blocking disappeared, CPU usage dropped, parallelism improved. 🔍 Root cause: “Default settings” from day one were never reviewed or tuned. 💡 Final Thought: Each of these fixes helped—but they also revealed a bigger theme: the real bottleneck was lack of awareness. Performance tuning isn’t black magic—it’s knowing where to look and asking the right questions. 🛠️ If you’ve got reports dragging, batch jobs crawling, or unexplained deadlocks—it’s probably not "just slow code." It's often overlooked defaults or forgotten decisions. If you’re facing similar issues, let’s talk. Drop your toughest tuning story in the comments—what did you fix that changed everything? #SQLServer #PerformanceTuning #DatabaseOptimization #TempDB #Indexing #ExecutionPlans #Consulting #PinalDave

Feeling grateful to share that I am fully booked for the first week of May with multiple SQL Server Performance Tuning engagements! 🚀 April has been one of the busiest and most rewarding months in recent memory. A quick technical overview: 🔹 117 queries tuned and optimized — Common patterns included excessive nested loops, missing SARGability, and redundant implicit conversions. In several cases, query execution times dropped by 85–95%, directly improving application responsiveness and reducing overall CPU pressure. 🔹 32 major server-level and database-level configuration changes — including fine-tuning settings like MAXDOP, Cost Threshold for Parallelism, Memory Grant Threshold for Serial Queries, TempDB optimizations (multiple files, trace flags), and adjusting the Query Store capture mode for better performance insights. 🔹 Index optimization: 74 non-used or low-impact indexes dropped after a careful review using sys.dm_db_index_usage_stats. 18 highly targeted indexes created, focusing on key lookup eliminations, covering indexes for critical queries, and filtered indexes for selective access patterns. In most environments, reducing index overhead led to noticeable gains in DML operations and better buffer cache hit ratios. 🔹 Statistics updates (both full and sampled) were performed across mission-critical tables, ensuring better cardinality estimations and helping the optimizer generate better execution plans. 🔹 Significant work was done around parameter sniffing issues — including use of OPTIMIZE FOR UNKNOWN, query hints, and forced parameterization strategies where needed. 🔹 Blocking and Deadlock Resolution: Identified and resolved multiple blocking chains through deadlock graph analysis and redesign of transaction scopes. Implemented lock escalation prevention strategies and optimized isolation levels where appropriate. 🔹 Wait Statistics Analysis: Addressed high CXPACKET, PAGEIOLATCH_XX, and WRITELOG waits across several production environments. The beauty of SQL Server tuning lies in the details. Every environment is different — but the goal is always the same: faster queries, stable systems, and happy users. If you’re looking to schedule a SQL Server performance review or tuning session, mid-May is the perfect window to plan ahead — or we can also target the first week of June depending on your timeline. Thank you to all the wonderful clients who trusted me with their most critical systems. It’s a privilege to be part of your performance journey. 🧡 #SQLServer #PerformanceTuning #DatabaseOptimization #QueryTuning #IndexTuning #WaitStats #DeadlockResolution #SQLServerConsulting

You haven't lived until MSSQL's tempdb fills up in production and takes your app down with it 🤣

#sqlhelp does a parallel batch mode hash aggregate need a lot more memory than what SQL Server normally estimates? 44 billion row columnstore table. Query is simple: SELECT DISTINCT column_one, column_two. Data types are bigint and nvarchar(500). Optimizer guesses about 5 million unique combinations when in actuality there are about 2.5 million. Memory grant is 1.5 GB which seems like it ought to be enough to store the hash table based on the amount and size of the data. When it spills, it uses about 2TB of tempdb (it must be spilling the data pages of the table?) to avoid the spill, I actually need about 23GB of memory. Any explanation of this behavior would be helpful! When it spills, it gets to spill level 3 and reports about 263 million pages spilled.

Improving #Azure #SQL #Database reliability with accelerated database recovery in tempdb buff.ly/osW69Dv #Microsoft #SQLServer #AzureSQL #MadeiraData

TempDB Size Considerations to Ensure Successful SQL VM Deployments from Marketplace Images from Raghu Tanikella buff.ly/3PPJoKD #Azure

[New Video] Ever wondered how sorting large datasets can secretly slow down your SQL Server? In this episode of SQL in Sixty Seconds, we dive into tempdb spills caused by insufficient memory grants during sorting operations. Watch as we analyze an execution plan, uncover tempdb warnings, and demonstrate how spills impact performance. Learn practical tips like creating indexes, filtering data early, optimizing tempdb, and monitoring memory grants to reduce I/O overhead and improve query efficiency. youtube.com/watch?v=AFBusiFy…

In about 20 minutes, we're kicking off our final #virtual #dataMinds #EveningSession of 2024 We're happy to be joined by Haripriya Naidu, who will be talking about #TempDB contention in #SQLServer 2022 There is still a bit of time to register & join: dataminds.be/tempdb-contenti…

Oh good gosh. Someone had a question about tempdb and I proceeded to answer questions about log backups. Total brain fart.

Used to put MS Sql Server Tempdb on these back in the day. Worth every penny in query performance.