Microsoft SQL Server Query Tuning

MicrosoftMicr SQL ServervQuery y TuningT Speaker: Dean RichardsSenior DBA, Confio SoftwareSilicon Valley SQL Server User GroupNovember 2010Mark Ginnebaugh, User Group Leader, mark@designmind.comQuery TuningGet it Right the First TimeDean Richards Senior DBA, Confio Software2Who Am I? Dean Richards, of course!, 20+ Years in SQL Server & Oracle• DBA and Developerp Senior DBA for Confio Software• DeanRichards@confio.com• Makers of Ignite8 Response Time Analysis Tools• http://www.ignitefree.com – only free RTA Tool Specialize in Performance Tuning Presented at 24 Hours of PASS 13 SQL Saturdays and counting…3Agenda Introduction Which Query Should I Tune Query Plans  SQL Diagramming•Who registered gyesterdayy for Tuninyg Classg•Check order status4Why Focus on Queries Most Applications• Read and WriteWrite data to/from d atabasedatabase• Simple manipulation and smaller amounts of data• Inefficiencies would notnot be noticed  Most Queries• Examine largerlarger amountsamounts o fof d ata,data, return a little • Inefficiencies quickly become bottleneck Why Tune Queries?Q• “Gives the most bang for your buck”• Changes to SQL are usually safer• ~85% of performance issues are SQL related5Who Should Tune Developers?p• Developing applications is very difficult• Typically focused on functionality• Not much time left to tune SQL• Do not get enough practice• SQLd runs iffdifferentltly in Productithon Dan ev/T/Test DBA?• Dto no k now ththde colike e develdopers o• Focus on “Keep the Lights On”• Very complex e nvironmentenvironment Need a team approach6Which SQL User / Batch Job Complaints Queries Performing Most I/O ( LIO(LIO P, IO)PIO) Queries Consuming CPU Queries Doing T ableTable o ror IndexIndex Scans Known Poorly Performing SQL Server Side Tracing Highest Response Times (Ignite8)SELECT sql_handle, statement_start_offset,statement_end offset,_plan _handle,handle, e xecutionexecution_count,count, total_logical_reads, total_physical_reads, total_elapsed_time, st.textFROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS stORDER BY total_elapsed_time DESC7Why is SQL Slow – Wait StatesFocus on Response Time Understand the total time a Query spends in Database Measure time while Query executes SQL Server helps by providing Wait Types8Wait Time Tables (SQL 2005/8)http://msdn.microsoft.com/en-us/library/ms188754.aspxdm_exec_query_statsdm_exec_requestsexecution_countstart_timetotal_logical_writesstatustotal_physical_readssql_handletotal logical readsplan handletotal_logical__handletotal_elapsed_timestart/stop offsetdatabase_iduser_idblocking_sessiondm_exec_query_planwait t_ ypeypquery planwait_timedm_exec_text_query_planquery plandm_exec_sessionslogin_timelogin_namehost_namedm_exec_sql_textprogram name_ttexsession_id9Base Monitoring QueryINSERT INTO SessionWaitInfoSELECT r.sessiion_ didl, r.sq _handldle, r.statement_start_off tse ,r.statement_end_offset, r.plan_handle, r.database_id,r.blocking_session_id, r.wait_type, r.query_hash,s.host_name, s.program_name, s.host_process_id,s.login_name, CURRENT_TIMESTAMP cdtFROM s yssys dm.exec_requests_r INNER JOIN sys.dm_exec_sessions s ON s.session_id = r.session_idWHERE r.status <> 'background'ANDd r.comman <> 'AWAITING COMMAND'COMMAND'AND s.session_id > 50AND s.session_id <> @@SPID10