CREATE NONCLUSTERED INDEX IX_FactSales_OrderDate_Include ON Sales.FactSales (OrderDate) INCLUDE (CustomerID, ProductID, Revenue);
The dashboard query that had been timing out (45 seconds) dropped to . I sat back. The blue SSMS window – with its old-school toolbar icons and gray background – felt less like an IDE and more like a cockpit.
The graphical plan appeared: a thick arrow from a Clustered Index Scan, a Hash Match spilling to tempdb, and the dreaded yellow warning triangle: "Missing Join Predicate." sql studio 2014
The rest of the team had gone home hours ago. Only the hum of cooling fans and the faint blue glow of monitor screens kept me company. Tonight’s target: a legacy financial database running on SQL Server 2014. The company called it "Project Phoenix" – rebirthing old data into new dashboards. I called it a headache.
The Night the Database Spoke
Ten minutes later, it suggested three missing indexes and one statistic update. I scripted them out – because in 2014, you never let the advisor run automatically on production. You read every CREATE INDEX like a surgeon reading a consent form.
SET STATISTICS TIME, IO ON; EXEC Dashboard.GetMonthlyReport @Year = 2024; Output: Table 'FactSales'. Scan count 1, logical reads 342, physical reads 0 SQL Server Execution Times: CPU time = 312 ms, elapsed time = 407 ms. The graphical plan appeared: a thick arrow from
Suddenly, the query ran again – 40 seconds. What? I checked the execution plan. Same indexes. Then I remembered: parameter sniffing. SQL Server 2014 didn't have the automatic plan forcing of later versions. I added OPTION (RECOMPILE) to the stored procedure and added WITH RECOMPILE to a frequently called function.