Raise your hand if you've never heard the following:

• My application runs perfectly
• None of our other customers have a performance problem
• It's the SQL Server/DBA's fault because it isn't tuned correctly
• SQL Server just can't scale like xxx
• You don't have enough hardware to run the application
• Application x is functioning as efficiently as possible
• <pick a component> is running perfectly and isn't a bottleneck

I'd imagine that not one person has raised their hand yet.  Let me let everyone in on a little secret.  Everyone is to blame and everyone can fix something to increase performance.  Given unlimited resources, you can fix everything.  Unfortunately, unlimited doesn't describe any budget.  Where do you focus your resources?  The application and what it is issuing against the SQL Server. 

Why?  Performance gains break down roughly as follows:

• 5% SQL Server configuration
• 5% - 10% hardware
• 80% - 90% application

Now that you know that the biggest bang for your buck is in tuning the SQL sent from the application.  Before walking over to break this news to your developers, or worse yet a 3rd party vendor, you'd better either do your homework.  If you don't do your homework and don't have good reflexes, you stand a very good chance of coming back missing a few parts of your anatomy. 

3rd party vendors or packaged apps that sit on SQL Server are the worst bunch to deal with.  Why?  You have to face one cold, hard fact.  Irregardless of how much business you do with them, you are only one customer and they are probably not going to change anything even after you completely embarrass them.  They are supporting many customers running on many DBMSes.  When any vendor says their application is cross DBMS, it is highly unlikely that you will EVER achieve even bad performance.  These apps are characterized by lots of cursor processing and multiple single row selects to get a batch of data.  There are 2 ways a company can deal with having to support multiple DBMSes.  The first is to maintain source code tuned for each DBMS they choose to support.  The second is to use a single source and do all of the work in the application.  Unfortunately, the second way is how just about every vendor does things which turns your SQL Server into a simple storage heap.

You still have a problem on your hands.  You still have to implement or support that ERP, CRM, SFA, call center, etc. app that was purchased or is being purchased from some vendor.  You still have to support the apps your developers or external consultants write.

You know that 80% - 90% of the performance problem with any of these is going to be in the app.  What you need is the documentation to "hit someone over the head with" when they point the finger at you as the DBA for the performance of the app.

Gathering this information and documenting it isn't difficult. It just takes time and some effort.  Set up an isolated system with a copy of your production database or a production sized database. Pick out the top 10 longest running processes. (Not transactions, processes from an application standpoint.) This will most likely be 10 specific reports that the application does or a day/week/month/year end process that it runs.  You also want to pick out the top 10 delays in processing in the app. (The users clicked OK and had to wait x number of seconds before control returned to them.)

You then setup Profiler to trace all SQL statements into your test server (include performance info and send it to a file) and pause the trace. Fire up the app and navigate to the beginning of the first process you want to document. Start the trace and kick off the process. Once the process is complete, wait for a short period to allow Profiler to finish flushing and then stop the trace. Clear the buffer and point it at another file. Repeat the process with the next process you want to monitor.

Note: This process works the same for a 6.5 environment.  You simply use SQL Trace instead.

You now have the base to work with. You know how many SQL statements it took to perform process x and how long. Clicking a button and having several million SQL statements get fired through your server is NOT abnormal. You may laugh at this.  The worst app I ever worked with came from a little company that produced software for the legal industry.   We had one report that generated approximately 1MB of data.  The "process" that generated the report issued approximately 4.7 million SQL statements.  The trace file was over 750MB in size for this one process.  It took almost 2 hours to run.  As a comparison, the size of the database itself was only 240MB and it only output about 1MB of data.  That means that it issued 3 times as much SQL as there was physical data in the database and over 750 as much SQL as there was physical output from the process.  You might not see anything this terrible at your site, but you will find things that are similar.  (As 2 side notes, this was a Microsoft Solution Provider producing this thing and the entire process boiled down to 1 SQL statement that executed in less than 10 seconds after I was done with it.)

You then extract the SQL batch and BCP it into a table with a single varchar(8000) column. (This is where access to a 7.0 server is very handy.)  You then run a series of deletes on the table to remove all of the sp_cursorfetch, sp_cursorclose, set commands, and other extraneous stuff. Make sure you leave all of the sp_cursoropen statement as these are the select statements you want.  You should be left with sp_cursoropen and regular DML statements. You then BCP this out to a file and open it into your favorite SQL development tool. Run a series of search and replaces to remove all of the junk you don't want such as extraneous spaces, sp_cursoropen, etc. and you are now left with the raw statements that do the processing. Do not skip the removal or all extra blank spaces as this can cut your file size in half or more.  This is your first bench mark in documentation. You went from x number of SQL statements to y number of SQL statements and are still doing the same amount of work. (Your normal reduction will be between 80% and 95%.) It only stands to reason that if you have eliminated 80% - 95% of the commands, it will run faster. (At this point, it normally won't run faster due to some of the cursor processing that is occurring.) You then settle down and successively walk through the script file and combine the statements together into equivalents that return more than one row. When you are done, you should have anywhere from 1 - 10 statements that accomplish the same work, but in a set oriented manner. You will also have something in the neighborhood of a 99%+ reduction in time and a corresponding 90%+ reduction is resources needed.

It is this benchmarking that you need to document very carefully, double check, have someone else verify, and then present to your management. They should then take the document, which should be rather thick at this point, along with all of the supporting files, and beat the vendor over the head with it.

No vendor or application developer will admit to having performance problems. Every vendor or application developer when pushed will point the finger at your configuration, DBMS, data, or something else that is unique to your installation that must be the problem. By running through the process above, you have stripped all of those arguments out and proven that it is the way they are processing that is causing the problems. If it weren't the way they were processing, then there would be no way you could produce an equivalent process that ran faster.