Microsoft SQL Server Query Tuning

Microsoft SQL Server Query Tuning Query Tuning Speaker: Dean Richards Senior DBA, Confio Software Silicon Valley SQL Server User Group November 2010 Mark Ginnebaugh, User Group Leader, mark@designmind.com

Query Tuning Get it Right the First Time Dean Richards Senior DBA, Confio Software 2

Who Am I?  Dean Richards, of course! ,  20+ Years in SQL Server & Oracle • DBA and Developer p  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… 3

Agenda  Introduction  Which Query Should I Tune  Query Plans  SQL Diagramming • Who registered yesterday for Tuning Class g y y g • Check order status 4

Why Focus on Queries  Most Applications • Read and Write data to/from database • Simple manipulation and smaller amounts of data • Inefficiencies would not be noticed  Most Queries • Examine larger amounts of data, return a little • Inefficiencies quickly become bottleneck  Why Tune Q y Queries? • “Gives the most bang for your buck” • Changes to SQL are usually safer • ~85% of performance issues are SQL related 5

Who 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 • SQL runs differently in Production than Dev/Test diff tl i P d ti th D /T t  DBA? • D not k Do t know th code lik developers do the d like d l d • Focus on “Keep the Lights On” • Very complex environment 6  Need a team approach

Which SQL  User / Batch Job Complaints  Queries Performing Most I/O (LIO PIO) (LIO,  Queries Consuming CPU  Queries Doing Table or Index Scans  Known Poorly Performing SQL  Server Side Tracing  Highest Response Times (Ignite8) SELECT sql_handle, statement_start_offset, statement_end_offset, plan_handle, execution_count, statement end offset, plan handle, execution count, total_logical_reads, total_physical_reads, total_elapsed_time, st.text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY total_elapsed_time DESC 7

Why is SQL Slow – Wait States Focus on Response Time  Understand the total time a Query spends in Database  Measure time while Query executes  SQL Server helps by providing Wait Types 8

Wait Time Tables (SQL 2005/8) http://msdn.microsoft.com/en-us/library/ms188754.aspx dm_exec_requests dm_exec_query_stats start_time execution_count status total_logical_writes sql_handle total_physical_reads plan_handle plan handle total_logical_reads total logical reads start/stop offset total_elapsed_time database_id user_id blocking_session dm_exec_query_plan wait_type yp query plan wait_time dm_exec_text_query_plan query plan dm_exec_sessions login_time login_name host_name dm_exec_sql_text program_name program name text t t session_id 9

Base Monitoring Query INSERT INTO SessionWaitInfo SELECT r.session_id, r.sql_handle, r.statement_start_offset, i id l h dl t t t t t ff t 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 cdt FROM sys dm exec requests r sys.dm_exec_requests INNER JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id WHERE r.status <> 'background' AND r.command <> 'AWAITING COMMAND' d AND s.session_id > 50 AND s.session_id <> @@SPID 10

Sample Wait Types  WRITELOG • Waiting for a log flush to complete  LCK_M_S, LCK_M_U, LCK_M_X… • Waiting to acquire locks  NETWORKIO, ASYNC_NETWORK_IO • Waiting on the network  PAGEIOLATCH_SH, PAGEIOLATCH_EX… • Physical disk reads  WAITFOR (idle event) • W i i during a WAITFOR command Waiting d i d 14

Tracing  Tracing with waits gathers very good data  C be High Overhead via Profiler Can b Hi h O h d i P fil  Use Server-Side Tracing • sp_trace_create – create the trace definition p_ _ • sp_trace_setevent – add events to trace • sp_trace_setfilter – apply filters to trace • sp_t ace_setstatus sta t/stop the trace sp trace setstatus – start/stop t e t ace  Use Profiler to Create Initial Trace • Use File > Script Trace to Get Script  Cumbersome to review data  Set trace file sizes appropriately 15

Summary of Response Time  Using Response Time Analysis (RTA) Ensures you Work on the Correct Problem  Shows Exactly Why Performance is Suffering  Helps Prioritize Problems l bl  Do Not Rely Exclusively on Health Stats (CPU Utilization, Utilization Disk IO Cache Hit Ratio) IO,  Data Collection • DMVs – build it yourself • Tracing – know how to process trace data • Tools – Ensure they use Wait Time and Health 16

Why is SQL Slow - Plans  SQL Server Management Studio • Estimated Execution Plan - can be wrong • Actual Execution Plan – must execute query, can be dangerous in production and also wrong in test  SQL Server Profiler Tracing • Event to collect: Performance : Showplan All • Works when you know a problem will occur  DM EXEC QUERY PLAN DM_EXEC_QUERY_PLAN, DM_EXEC_TEXT_QUERY_PLAN(@handle,@s,@e) • Real execution plan of executed query p q y 17

Case Studies  SQL Diagramming Q g g • Who registered yesterday for Tuning Class • Check order status 19

SQL Statement 1  Who registered yesterday for SQL Tuning SELECT s.fname, s.lname, r.signup_date FROM student s INNER JOIN registration r ON s.student_id = r.student_id i t ti t d t id t d t id INNER JOIN class c ON r.class_id = c.class_id WHERE c.name = 'SQL TUNING' AND r.signup_date BETWEEN @BeginDate AND @EndDate AND r.cancelled = 'N'  Execution Stats – 9,634 Logical Reads 20

Execution Plan Recommendation from SSMS R d ti f CREATE NONCLUSTERED INDEX [<Name of Missing Index>] ON [dbo].[registration] ([cancelled],[signup_date]) INCLUDE ([student_id],[class_id]) ([student id] [class id]) 22

SQL Diagramming  Great Book “SQL Tuning” by Dan Tow • Great book that teaches SQL Diagramming • http://www.singingsql.com registration g .03 37 1293 1 1 student class .001 select count(1) from registration where cancelled = 'N' and signup_date between '2010-04-23 00:00' and '2010-04-24 00:00' 54,554 / 1,639,186 = 0.03 select count(1) from class where name = 'SQL TUNING SQL TUNING' 2 / 1,267 = .001 23

New Execution Plan CREATE INDEX cl_name ON class(name)  Execution Stats – 9,139 Logical Reads  Why would an Index Scan still occur on REGISTRATION? 24

New Execution Plan CREATE INDEX reg_alt ON registration(class_id)  Execution Stats – 621 Logical Reads 26

Better Execution Plan CREATE INDEX reg_alt ON registration(class_id) INCLUDE (signup_date, cancelled) (signup date  Execution Stats – 20 Logicall Reads d 27

Alternative from SSMS CREATE INDEX reg_can ON registration(cancelled, signup_date) INCLUDE (class_id, student_id)  Execution Stats – 595 Logical Reads CREATE NONCLUSTERED INDEX [<Name of Missing Index>] ON [dbo].[registration] ([class_id],[cancelled],[signup_date]) 28 INCLUDE ([student_id])

SQL Statement 2  Paychecks for specific employees SELECT e.first_name, e.last_name, l.description FROM emp e INNER JOIN loc l ON e.loc_id = l.loc_id WHERE (e.first_name = @fname OR e.last_name = @lname) AND EXISTS ( SELECT 1 FROM wage_pmt w t WHERE w.emp_id = e.emp_id AND w.pay_date>= DATEADD(day,-31,CURRENT_TIMESTAMP) )  Execution Stats – 64,206 Logical Reads 29

SQL Diagramming wage_pmt .02 90 1 emp .0005 .0009 1000 1 loc select count(1) from wage_pmt where pay_date >= DATEADD(day,-31,CURRENT_TIMESTAMP) 40,760 / 1,915,088 = .02 select top 5 first_name, count(1) from emp group by first_name order by 2 desc 12 / 23,798 = .0005 – first_name 22 / 23,789 = .0009 – last_name 31

New Execution Plan CREATE INDEX ix2_fname ON emp(first_name) 33

Which Index? SSMS Recommendation CREATE INDEX wp pay date ON wage pmt(pay date) wp_pay_date wage_pmt(pay_date) INCLUDE (emp_id) 50,000 L i l R d 50 000 Logical Reads or… Better Option CREATE INDEX wp emp pd ON wage pmt(emp id, pay_date) wp_emp_pd wage_pmt(emp_id, pay date) 46 Logical Reads 34

New Execution Plan CREATE INDEX wp_emp_pd ON wage_pmt(emp_id, pay_date) 35

SQL Statement 2  Lookup order status for caller SELECT o.OrderID, c.LastName, p.ProductID, p.Description, sd.ActualShipDate, sd.ShipStatus, sd.ExpectedShipDate FROM [Order] o INNER JOIN Item i ON i.OrderID = o.OrderID It i O d ID O d ID INNER JOIN Customer c ON c.CustomerID = o.CustomerID INNER JOIN ShipmentDetails sd ON sd.ShipmentID = i.ShipmentID LEFT OUTER JOIN Product p ON p.ProductID = i.ProductID LEFT OUTER JOIN Address a ON a.AddressID = sd.AddressID WHERE c.LastName LIKE ISNULL(@LastName,'') + '%' --AND c.FirstName LIKE ISNULL(@FirstName,'') + '%' AND o.OrderDate >= DATEADD(day, -30, CURRENT_TIMESTAMP) AND sd.ShipStatus <> 'C'  Execution Stats – 10 159 Logical Reads 10,159 36

SQL Diagramming o .08 .005 i c .03 sd p a SELECT COUNT(1)*1.0/(SELECT COUNT(1) FROM Customer) FROM Customer WHERE LastName LIKE 'SMI%' .03 SELECT COUNT(1)*1.0/(SELECT COUNT(1) FROM [Order]) FROM [Order] WHERE OrderDate >= DATEADD(day, -30, CURRENT_TIMESTAMP) .08 SELECT COUNT(1)*1.0/(SELECT COUNT(1) FROM [Order]) FROM [Order] WHERE OrderStatus <> 'C' .005 -- Combined .005 39

Data Skew Problems SELECT OrderStatus, COUNT(1) FROM [Order] GROUP BY OrderStatus  Only 0.5% of rows are <> ‘C’  How about changing the query? g g q y • AND o.OrderStatus = 'I'  Add an Index on ShipStatus 40

New Execution Plan CREATE INDEX IX2_OrderStatus ON [Order] (OrderStatus) INCLUDE (OrderID,CustomerID) ( , ) Execution Stats – 3,052 Logical Reads 41

Takeaway Points  Tuning Queries gives more “bang for the buck”  M k sure you are tuning the correct query Make t i th t  Use Wait Types and Response Time Analysis • L ki problems may not be a Query Tuning issue Locking bl b Q T i i • Wait types tell you where to start  Use “Real Execution Plans” • Get plan_handle from DM_EXEC_REQUESTS • Pass plan handle to DM_EXEC_QUERY_PLAN() plan_handle DM EXEC QUERY PLAN()  SQL Diagramming - “Get it right the First Time” • Query Tuner Tools can mislead you

Confio Software  Wait-Based Performance Tools  Igniter Suite • Ignite for SQL Server, Oracle, DB2, Sybase g Q , , , y • Ignite for Databases on VMWare (Beta)  Helps show which SQL to tune  Provides visibility into entire stack  Based in Colorado, worldwide customers ,  Free trial at www.confio.com 43

To learn more or inquire about speaking opportunities, please contact: Mark Ginnebaugh, User Group Leader mark@designmind.com http://www.meetup.com/The‐SiliconValley‐SQL‐Server‐User‐Group/

Microsoft SQL Server Query Tuning

More Related Content

What's hot

Similar to Microsoft SQL Server Query Tuning

More from Mark Ginnebaugh

Recently uploaded

In this document

Microsoft SQL Server Query Tuning