MAXDOP and cost threshold work together to shape parallel plans; inspect current values, test a change, and watch real workload effects.
SQL SERVER – Reducing CXPACKET Wait Stats for High Transactional Database
Reducing CXPACKET wait stats on a high transactional database: what CXPACKET means, and when lowering the degree of parallelism really helps.
SQL SERVER – Visiting Alma Mater – Delivering Session on Database Performance and Career – Nirma Institute of Technology
I went back to my alma mater, Nirma Institute of Technology in Ahmedabad, to deliver a session on database performance and career. It felt wonderful.
SQL SERVER – Quickest Way to Identify Blocking Query and Resolution – Dirty Solution
A friend could not delete, truncate or drop a table at 11 PM because of open transactions. Here is my quick and dirty way to identify blocking query.
NO_PERFORMANCE_SPOOL: Compare the Work Before Keeping the Hint
NO_PERFORMANCE_SPOOL changes one query optimization decision. A guarded private fixture compares baseline work, the optional hint and a supporting inner index. The article explains how to preserve results and assess actual child work without promising a plan shape.
Wait Statistics: Read the Snapshot Before Naming the Bottleneck
Wait statistics provide a starting point for investigation. Separate cumulative completed waits, background activity, signal time and currently waiting requests before treating the largest total as the present bottleneck.
Finding the Nearest Location With a Spatial Index
A nearest location query in SQL Server needs TOP, STDistance in WHERE and ORDER BY, matching SRIDs, and a checked spatial plan.