If you remeber from the previous post Speakng the Language of Managment, we needed a write-up to give to Senior Management to get our software approved. Here is a sample that I did for a small purchase for SQL Trace Analyzer that we wanted to better help manage, report and understand the profiler traces.

------------------------------ 

This is the software that we have been talking about and would like to purchase. 

http://www.sqlsolutions.com/tadownload/SQLOnlinePayment/Step4.aspx?sSelected=15,1;36&sProducts=1&sLicenses=15,1

This software allows me to take the trace information that I gather from SQL and from the Performance Monitor on the SQL Server and map them over each other to give us more insight into the performance and troubleshooting of our SQL Servers. 

Problem:

From time to time, the performance of the SQL Servers need to be investigated and, as such, I encounter three major problems:

1. Correctly, rapidly and accurately report/analyze SQL Profiler statistics for clients that are experiencing performance issues that are impacting our contracted SLA terms. I not only manage Product Y's systems but also the SQL Server systems for Product Z, Product X and Product B's ASP versions so I have to do this for various teams and clients with differing SLA's and often under a situation where the client is not happy.   (Impacts multiple clients and SLA's)
2. Correctly and accurately report/analyze SQL Profiler statistics for Performance Reports that will be given out to clients who ask about this in their RFP and RFI process (this would be used in conjunction with a report from the Web load tool). (Impacts future business)
   
3. Correctly and accurately report/analyze SQL Profiler statistics for the ongoing tuning of new development applications (Impacts Accuracy and Responsiveness). 
   

Current Process:

Currently, I have been using a free tool called ClearTrace that just aggregates the data.   As well, I have also loaded the data into my own structures but this is quite time intensive and probably not the best use of my time to develop a new tool.   Finally, I have even tried the Microsoft software called Database Tuning Advisor (DTA) which comes with SQL Server 2005.   

Problems with Current Process:

1. The ClearTrace tool does not allow graphing or historical data so I can't see a before the change and after the change. 
   
2. The ClearTrace tool has limitations in the number of profiler entries it can process.  It can process about 3 million records and it crashes. 
   
3. The ClearTrace tool is unsupported software.
   
4. The manual process is time consuming and not user friendly. 
   
5. The time it takes to develop a new tool internally is time taken aware from customer focused tasks and is time not spent on the customer service hierarchy. 
   
6. The DTA does not provide any statistics, rather it only provides recommendations.  So I am not able to see how many time a process is called or a larger global perspective of the system, you just have to trust Microsoft.  As well, the DTA does not work well for processes that use temp tables and when stored procedures call other stored procedures (nesting)—both of which are common with the Product Y's application.   

1. Provide internal teams with a timely list of prioritized tasks for optimization when a client is experiencing performance issues.
   
2. Provide internal teams with an easy to use step in the development process to be proactive and eliminate potential bottlenecks in future versions.
   
3. Create a performance capabilities document to justify sales claims and to provide to many large clients that our systems can handle large quantities of concurrent users without performance issues for their projects that have short timeframes.      
   
4. Collect performance data to help better understand and plan capacity for growth.  

Test Plan:

I downloaded a trial version of the software and ran the same profiler files through the software application.  I was able to run a smaller sample of Project X's profiler output in about 30 minutes as compared to the ClearTrace run which took about 2 hours to process and manually graph.

ROI/Savings:

Software will cost $495 + annual maintenance of $49.

For the past several months I have done this formally three times for Client A (because of contract requirements) and once for Client S, and numerous times for Product B's ASP clients (listed the various clients but removed for confidentiality) for client performance relate issues and will be doing it formally in the next few months for approximately 10 additional clients.  Finally, we will be adding a performance analysis step as part of the monthly Product Y's monthly software release. 

The formal tests for Client A took 9 hours for the first two test and 6 hours for the last test and and based on the test case, should take about 2 hours with the new software.  I would also estimate a time of about 5 hours for each of the upcoming formal tests.  So just focusing on Client A, we have 24 total hours of time versus and estimate of 6 with the new software.  Based on my hourly rate at $35 that would compute to a savings of around $630.00.

The one off client performance traces take anywhere from 3 to 6 hours depending on the severity and urgency and up to 2 hours on the high side with the new software.  While the variability of time estimate for this scenario is difficult, it would follow that a similar reduction in hours as experienced with the formal tests. 

The upcoming process change, I estimate it will take about 3-6 hours per run under the current process and this will be done monthly and about 1.5 hours each month with the new software.  So again, we should see a similar reduction in the number of hours as experienced with the formal tests.  Since we plan on doing monthly releases, the old process would take a total of 36 to 72 hours or cost about $1260 to $2520 and under the new process cost about $258 in time and generate a savings of $1000 to $2268. 

While the total savings may not appear great in the grand scheme of things it has an ROI ranging from 329% to 585% in the first year.  It is a savings and the more expensive resource can be spent on other more important tasks. 

Thanks and let me know if you need any additional information/help in getting this processed. 

--------------------

So there you have it.  I start off with a short request, explain the business problem, explain the current process and the problems with it,  set the goals that this purchase will accomplish, explain the test plan and finally show the savings from this.  

Good luck and hope this helps you get that software you need! 

Next up, I will show you a template that I have been using to do an indepth analysis for upgrading to SQL Server 2008.