question

You have to capture metrics on the SQL Server instance to understand the behavior of that instance. You need to know the wait statistics, memory, cpu, I/O of the server, focusing on the waits and queues. The best tool for gathering these metrics is Windows Performance Monitor, a built-in tool for your OS. You also have to gather metrics about the queries run by the report to understand how it's behaving, how much cpu, I/O, and memory it uses, what it's wait statistics are. You do this using extended events in 2012, again, a free tool built into SQL Server. You'll also want to capture the execution plan for the query to understand, based on how it's currently written, how is the query optimizer choosing to define the execution of the query. With all this information, you can understand how SQL Server is behaving. If this is your first time doing performance tuning, might I suggest getting a copy of my book, [SQL Server Query Performance Tuning][1]. It goes over all of the above and then tells you how to figure out what's wrong with your query and fix it. You'll need to do the same type of work from the application side of things. I'm not an expert there, but I can suggest you look at the tool [ANTS Performance Profiler][2]. It will tell you exactly where your code is running slowly. Further, if your database is the bottleneck, it will help you identify which query is the problem and it will even capture the execution plan for you. DISCLAIMER: I work for Redgate. [1]: http://www.apress.com/9781430267430 [2]: http://www.red-gate.com/products/dotnet-development/ants-performance-profiler/