SQL Server Administration – Tips From the Trenches

1. SQL Server Administration – Tips From the Trenches Troubleshooting Reports of Sudden Slowdowns

2. Agenda • A Typical Call for Help • Perfmon (Performance Monitor) • Blocking • Deadlocking • Long running queries • Currently executing queries • Info on queries just completed • Why not Activity Monitor? • Why not 3rd party tools? • Next Steps

3. A Typical Call for Help • Your phone rings. • On the other end is a manager who is asking what is going on with their application. “It’s slow. Everyone says it has been slow for a while now. What is causing this and how do we stop it?” • No matter how much you monitor and are proactive, you will get these types of calls. You have virtually no access to details and the panicky manager wants answers NOW. • Here is how I find answers….

4. Step 1 - Perfmon • Check your Vital Signs! • Processors • Utilization >60%, >85%, >95% • Memory • SQL Server Page Life Expectancy < 300sec • Available Bytes > 100,000,000 (1GB) • Disks • Reads or Writes >20ms, >40ms, >80ms

5. Perfmon • Processors • Processor (% Processor Time)(for each processor) - The total amount of time a specific processor is busy. • Warning Threshold > 60 • Danger Threshold > 85 • Extreme Danger Threshold > 95

6. Perfmon • Memory (OS & SQL Server) • Memory (Available Bytes)- The amount of physical memory available to processes. This must be 100 MB or more or you risk Windows being dedicated to memory management activities. • Warning Threshold < 100,000,000 (1GB) • Danger Threshold < 20,000,000 (200MB) • Extreme Danger Threshold < 5,000,000 (5MB) • SQLServer:Buffer Manager (Page life expectancy)- The average number of seconds that information remains in memory when it is not actively being used. Less than 300 seconds indicates memory pressure. • Warning Threshold < 300 • Danger Threshold < 200 • Extreme Danger Threshold < 90

7. Perfmon • Disks • PhysicalDisk (Avg. Disk sec/Read) • PhysicalDisk (Avg. Disk sec/Write) • PhysicalDisk (Avg. Disk sec/Transfer) (All counters measure each disk) - Measures the response time of the disk subsystem. Best performance is 10 ms or less. • Warning Threshold > 0.020 • Danger Threshold > 0.040 • Extreme Danger Threshold > 0.080

8. Perfmon

9. Blocking Blocking CTE script available on request.

11. Deadlocking Event notifications - Sends info about a database or server event to a service broker service. Can be logged to a table and either reported on or alerted on using triggers. Does not need a job to capture deadlock info. ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]) Script to set up event notifications available on request. Alerts – Can only tell you that a deadlock occurred and cannot give you info on what was causing the deadlock. You can make the SQL Agent receive WMI events to capture deadlock graph info. Requires a job and an alert to be added to the instance. See: http://msdn.microsoft.com/en-us/library/ms186385(v=sql.110).aspx

13. Long running queries Gather long running queries periodically and load them into a table. You can use SQL Profiler or a server side trace as shown. Long Running Queries Trace script available on request.

14. microseconds ms

15. What queries are running right now and how are they performing? Currently Running Queries script available on request.

16. Dynamic Management Views (DMV’s) sys.dm_exec_requests - Returns information about each request that is executing within SQL Server See: http://msdn.microsoft.com/en-us/library/ms177648.aspx SELECT * FROM sys.dm_exec_requests** sql_handle is found here. sys.dm_exec_sql_text - Returns the text of the SQL batch that is identified by the specified sql_handle See: http://msdn.microsoft.com/en-us/library/ms181929.aspx SELECT * FROM sys.dm_exec_sql_text(sql_handle)

18. Find info on queries that have recently completed. Cached Queries script available on request.

20. Dynamic Management Views (DMV’s) sys.dm_exec_query_stats- Returns aggregate performance statistics for cached query plans in SQL Server. The view contains one row per query statement. See: http://msdn.microsoft.com/en-us/library/ms189741.aspx SELECT * FROM sys.dm_exec_query_stats** sql_handle is found here. sys.dm_exec_sql_text - Returns the text of the SQL batch that is identified by the specified sql_handle See: http://msdn.microsoft.com/en-us/library/ms181929.aspx SELECT * FROM sys.dm_exec_sql_text(sql_handle)

21. Why not use Activity Monitor? Notoriously slow and can negatively impact an already busy server. While Activity Monitor uses DMV’s to provide information, there is a cost associated with querying those DMV’s. Activity Monitor does a LOT of work behind the scenes and can add workload to an already overloaded server. Activity Monitor should not be used for long term monitoring of a SQL instance. Challenge: Capture Activity Monitor statements with SQL Profiler I found timeout errors as well as statements that took over 6 seconds to complete. And this was on my laptop with nothing else running!!

22. Why not use a 3rd party tool? Constraints by design 3rd party tools have to be specially configured to partially capture the data that we know to look at during a time of stress. If the 3rd party tools were not constrained they would overwhelm the environments they are trying to monitor. Clients who currently use these tools still come to us to find problems!

23. Contact Us Next Steps & Resources… • SQL Health Check (free). • RemoteDBA Subscription – 1st month Free w/ min 3 month subscription. • Contact us for the scripts shown in our presentation. For more information: dhooper@isi85.comDan Hooper Principal, Vice President Sales & Marketing Integrated Services, Inc. 214.526.7680 x101 www.isi85.com lorib@isi85.comLori Brown Senior ConsultantIntegrated Services, Inc. 214.526.7680 x113 www.isi85.com