100 likes | 202 Vues
SQL Server performance tuning basics (for the developers). Dean Vitner. SQL Server UG Zagreb, 19.12.2013. Some like it hot. CHECKPOINT DBCC DROPCLEANBUFFERS DBCC FREESYSTEMCACHE Store ( sys.dm_os_memory_clerks ) Database Pool ALL DBCC FREEPROCCACHE Pool Plan handle
E N D
SQL Server performance tuning basics (for the developers) Dean Vitner SQL Server UG Zagreb, 19.12.2013.
Some like it hot CHECKPOINT DBCC DROPCLEANBUFFERS DBCC FREESYSTEMCACHE • Store (sys.dm_os_memory_clerks) • Database • Pool • ALL DBCC FREEPROCCACHE • Pool • Plan handle DBCC FLUSHPROCINDB • DB_ID
Testing code as you go Mock up or real data Baseline Multiple runs SSMS • SET STATISTICS IO ON • Logical vs physical reads • SET STATISTICS TIME ON • Graphical execution plan • Missing index hint DMVs • sys.dm_os_wait_stats • sys.dm_exec_query_stats • sys.dm_exec_procedure_stats
What to look for? Fat arrows Operators • Table scans • Lookups • Hash joins in OLTP workload • Joins on non-selective columns • Sort & sort warnings • Parallelism • Implicit conversions Estimates vs actual values • Stale statistics • Parameter sniffing • UDFs • Complicated predicates
Cursors and while loops Row-by-agonizing-row (RBAR) Could be useful: • Running totals • Calling SPs • Administrative tasks Default settings: • global, scrollable, writeable, dynamic Use LOCAL FAST_FORWARD sp_MSForEachDB is dangerous!
(NOT) IN and friends NULLS are neither equal nor different • NOT IN on columns with NULLS will return incorrect data Large IN lists are a performance problem EXCEPT eliminates dups sort Anti semi join Personally, I’d always use (NOT) EXISTS
Updating data Indexes • Large inserts – disable indexes first • Consider the whole workload, not just SELECTs Referential integrity Cascades Triggers and rollback from trigger Use CTEs to minimize human errors
Stored procedures SPs are not faster than ad hoc, but… Security is a plus Easier maintenance and tuning Parameterization • Greater chance of plan reuse • Parameter sniffing problem Recompilation SET NOCOUNT ON
Dynamic search procedures Freely combined search conditions Static code for every combination • Maintenance nightmare Static code with WHERE (col = @param or @param IS NULL) • Performance issues, recompilation Dynamic SQL with sp_executesql • Plan reuse • Strongly-typed parameters • Injection
OMGs (common missconceptions) Order of predicates matters Table variables are stored differently from #tables COALESCE is better than ISNULL UDF are good Adding hints makes it run faster