1 / 10

SQL Server performance tuning basics (for the developers)

SQL Server performance tuning basics (for the developers). Dean Vitner. SQL/ Dev UG Osijek Osijek, 30.01.2014. Some like it hot. CHECKPOINT DBCC DROPCLEANBUFFERS DBCC FREESYSTEMCACHE Store ( sys.dm_os_memory_clerks ) Database Pool ALL DBCC FREEPROCCACHE Pool Plan handle

harley
Download Presentation

SQL Server performance tuning basics (for the developers)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server performance tuning basics (for the developers) Dean Vitner SQL/Dev UG Osijek Osijek, 30.01.2014..

  2. 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

  3. 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

  4. 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

  5. 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!

  6. (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 prefer (NOT) EXISTS

  7. 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

  8. 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

  9. 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

  10. OMGs (common misconceptions) 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

More Related