Mastering Application Performance Symantec Application Performance Management (APM)

1. Mastering Application Performance Symantec Application Performance Management (APM) Name Title

2. Databases Middleware Applications Symantec Data Center Foundation Veritas NetBackup Veritas Storage Foundation VeritasServer Foundation Veritasi3—APM Network Storage Servers Virtual Machines Symantec Data Center Foundation

3. The Need For Application Performance Management

4. Risks of Bad Application Performance • Eroding Customer Loyalty • Impact on Revenue • Loss of Employee Productivity • Mistrust of IT by Business • Increased Spend on Hardware Good Application Performance is not a Luxury, it’s a Requirement

5. AvailabilityMetric 100 99.99 99.96 99.80 % % % % Database Server Storage Area Network Application Server Web Server Different Perspectives Different Perspectives The reality of what your users may experience... What the IT executive sees

6. Why the Discrepancy? • IT organizations are typically divided into “silos” – each silo focusing on its specialty • Your end users experience the ENTIRE system not just a single component or tier • Traditional approaches to application performance management do not provide end-to-end visibility 98.70 99.99 100 99.96 % % % %

7. The Symantec Application Performance Management Solution

8. SQL Statement Index Stored Procedure Programs Full Table Scan Table Space Column Table Locking Users Instances Servers OS Metrics I/O Understanding Customer Experience J2EE Application Server Storage Area Network (SAN) WebBrowser WebServer Database Server Java ServerPage Logical Volume HTTP Enterprise Java Bean I/O Channel URL Physical Device Jpg OS Metrics OS Metrics Invocations HTTP Servlet SQL Statement JSP Java DB Connectivity

9. Key Questions for Application Performance Management • Can We View Performance Information in a Single Pane of Glass? • Is the Application Available? • Is the Application Working as Designed? • What's the Cause of Application Slowdown? • Where is the Application Bottleneck? • How do We Improve Application Performance?

10. Insight Inquire Web Monitoring Insight Inquire Web Monitoring Updated Updated Application Service Dashboard (ASD) Integrated view of application performance and availability New Integrated application performance management via a customizable, easy-to-use dashboard interface Symantec APM Portfolio i3 End-to-end application performance monitoring i3 End-to-end application performance monitoring

13. Insight Inquire Web Monitoring Application Service Dashboard (ASD) Integrated view of application performance and availability New Updated Application Service Dashboard (ASD) Integrated view of application performance and availability New Agentless, real-time, 24x7 availability and performance monitoring of business-critical web systems Symantec APM Portfolio New i3 End-to-end application performance monitoring

14. This application has Faulted Shows all monitored Applications and SLA Status—Availability and Performance—At a glance

15. Drill down into the Transaction—Identifies the problem web page component

16. Insight Inquire Web Monitoring Application Service Dashboard (ASD) Integrated view of application performance and availability New Insight Performance Data Correlation Updated Inform Performance Warehouse, Alerting, Reporting Indepth for Web Servers Indepth for Applications Indepth for Middleware Indepth for Databases Proactive monitoring, analyzing, and tuning of critical business applications Symantec APM Portfolio i3 End-to-end application performance monitoring

17. ! Symantec i3 Key Capabilities Where Is The Problem? What Is The Problem? What ActionsDo I Take? • Correlated end-to-end view • Impact to end-user response • Slow tier isolation • Performance data collection • Root cause identification • Proactive Alerting • Historical reporting • Trend analysis • Expert tuning advice RESPONSE TIME DB Tier Client Network Storage Web App DB Where toLook? • RECOMMENDED ACTIONS • Add Index • Revise SQL statement • Add memory PerformanceWarehouse

21. Guest Speaker

22. Does This Happen at Your Company? SQL Server Slow response leads to Finger-Pointing Storage Application Windows

23. When the “Finger-Pointing” hits the fan • This is when I get called in • All database examples in this presentation are from real-life consulting engagements over the last 10-12 months

24. Presentation Outline • When you know the problem is the database server • Where to look to identify actual (as opposed to perceived) bottlenecks • Drilling down into root causes • How to demonstrate that it’s not the database • Proving it to the unbelievers / Quantifying your findings • Moving from reactive to proactive tuning • Moving from reactive to proactive administration

25. How do you define performance? • Query response time? • End-to-end user interaction time? • Batch window completion? • Preventive maintenance completion? • Monthly process time? • Daily reports? • Utilization rates within 60-70% of capacity on CPU, Disk, Network, Web Server, etc.? • Frequently, this is the first question that gets answered when I get called in

26. Performance and Tuning • Performance and tuning is a balancing act • You weigh the complaints of the CEO, whose on-demand reporting is taking 5 seconds instead of 1 second, against the complaints about report generation time of the accountant who has been holding up your expense check because you lost a lunch receipt for $4.22 • The squeaky wheel gets the grease, but sometimes the wheel is squeaking not because of a problem with the wheel, but with a problem with design, coding, or even a SQL Server bug • Solving the underlying problems make you life easier in the long run

27. When you think it’s the database • Case Study • Client has a 2-CPU system, 120 users, rolled out a newly developed software application • CPU (per Microsoft Performance Monitor) was pegging the box at 100% • Added 2 CPUs, performance was “acceptable,” but CPU was still at 95% • Since this the entire user community had not been rolled in, panic had set in

28. The old approach to tuning • Check OS-level stats to see where the CPU, IO, and network are bottlenecking • Interview users to identify which application components are slow • Squeaky wheel tuning • Run profiler to identify queries • Fix queries (or add indexes) • Repeat as needed

29. Where to look when things go bad Latch Waits

30. 11am resource utilization day 1 versus day 2 Before After

31. Solving the problem • The first step to solving any problem is identifying it (for the sake of this discussion, throwing hardware at a problem will be considered masking, not resolving, the problem). Once the problem has been identified, we can devise a solution, test a solution and roll out the successful versions • In the case of the database, we’re going to tune at the macro scale or the micro scale • At the macro scale, we’re going to look at server bottlenecks, and try to fix things at a global level (Faster io? More CPU? Network bottleneck?) • At the micro scale, we’re going to look at individual queries (Bad SQL? Bad query plans? Poor index selection? No matching index?) • How do we know to look at the micro scale or the macro?

32. To drill down, click here

33. Drilling down into root causes

34. From this list, you see: • A hash representation of the top 50 resource-intensive queries in descending order of “time in MS SQL Server” (the default sort) • A color-coded graphic representing the type of utilization of the queries • Percentage of SQL Server resources these queries cost (in relation to the rest of the queries) • The number of times the queries were executed • Average execution time of the queries • Text of the queries

35. Sometimes you find • … that a single query is running thousands of times, and using 33% of server resources, and taking a 1.5 seconds • Tuning that particular query to .2 seconds will have a dramatic positive effect on overall performance • … that the top few queries have over half their time spent in lock wait states • Knowing what tables are being locked, what the lock types are, what queries are locking them, and the types of locks, zeros in on the root cause of the problem • … that 4 or 5 queries are using the same tables, the same wrong indexes, or blocking each other incessantly • Wouldn’t it be nice to get some index selection recommendations? • Or any of a hundred other things, since you’ll have visual data and the tools you need to track it down

36. Further drill down Click Here

37. Statement-level information Significant io wait

38. Query plans • It’s also useful to be able to look directly at the query plans that were used for the query under investigation • If the query is run once, on Saturdays, you want to be able to address the issue on Monday, when you hear about the problem, not stick around the following Saturday to observe the issue

39. Viewing query plans

40. i3 traps, tracks, and stores all query plans…

41. … And will tell you when & why the plans change

42. Drilling Down into Stored Procedures • A fun sidebar – ever wonder how to identify resource utilization within your stored procedure? REPLACE WITH MORE RECENT PICTURE

43. The little things matter • Sometimes performance isn’t just the box. Performance issues involve not queries or system bottlenecks, but: • Batch windows • Preventive maintenance • DBCC • Index rebuilds

44. For ASPs, database utilization can be important

45. Which indexes are in use? • Index maintenance drives everybody nuts • When you have a performance problem, one of the first things developers try is adding indexes to the database • Which ones may be safely removed? • I3 captures all query plans. It’s easy for it to look at the live data, compare historical indexes, and show you which ones haven’t been used

46. Ever wonder which indexes may be safely removed?

47. When you don’t think it’s the DBMS • Experience gives you the perspective to say, “The DBMS is performing fine. Your problem is…” • Unfortunately, that feels a bit like participating in the “Finger-Pointing” game. What you really want to do is identify the specific cause of the problem, demonstrate it definitively, and find the right person to fix it

48. Case Study • 4-processor SQL Server in an Application Service Provider (ASP) environment • Key queries (stored procedures) were rewritten to run in under a half second, from 5-20 seconds. • Further follow-up found that application response time was still in the 5-second + category • We knew the issue was not in the database; but where was it?

49. How to demonstrate that it’s not the database

50. Where is the application spending its time? • When the vast majority of elapsed time is not in the database, you have evidence that it’s not your fault. • In this case, I had been brought in to tune the database, knocked all the queries down from 5-10 second range to significantly subsecond. Performance was still in the 3-6 second range for much of the application. I knew it was not a database issue, but what was it? • In this case, it was the result of significant requests to an SSL layer; it turned out that some of the screens were taking 5-6 seconds to encrypt due to the quantity of dynamic data displayed.