We have an .net enterprise application which backs onto an SQL Server backend.
With some of our bigger customers now we are coming across some SQL based performance bottlenecks. We've applied as much global indexing that we can see which would increase performance but we are constantly getting customers that push the boundaries past what we would have ever passed as our max use cases and there is nothing to say this is wrong.
What this means though is being able to provide the ability to look at how the bigger customers and provide indexes for their databases. At this point I can only see this achievable in a case by case basis (because all customers will use the system differently).
My thought is to somehow run the SQL Profiler on the customers database for a certain period of time and than pump this data into a table in the database and after that period analyze the data and apply indexes where needed to improve performance.
The enterpise app is asp.net mvc. Does anyone know if we can add .net code that will fire up the SQL profiler from the asp server via a connection string to the database and server and than pump the results into a table?
Or any advice on other approaches we could take?
SQL Server Profiler is only a graphical interface to show all the events handled by SQL Trace API. It is possible to start and capture trace data through SQL stored procedures and .Net code. Here is two examples on how to do it. It isa bit old, but I have managed to start sql trace throm .net code following the instructions.
http://blogs.msdn.com/b/sqlprogrammability/archive/2006/05/26/receiving-profiler-events.aspx
Here is a list of stored procedures that are executed by the profiler to start the trace. You can allways run them directly. http://technet.microsoft.com/en-us/library/ms187346.aspx