I owe you and your corporation a long overdue thank you for Method R.
A couple of months ago, I was helping a client get through a serious performance problem on a project that was already late and getting later, we were finally seeing the light at the end of the tunnel until we got to the last part of the application. We were facing a query that was part of a customer facing module that had to run in under 2 seconds per execution, and the current tests showed it running in 40 minutes. There was much consternation and many meetings were called, management was getting anxious and lots of memos were being sent.
I finally managed to convince the local DBA over there to send me a trace file of the application while the problem was occurring, then turned the Profiler on it. Out of 40 minutes of response time, over 39 minutes and 59 seconds were being spent one index call. Said call stood out in big bright red in the Profiler file….
One ‘ALTER INDEX ...’ call later to make the index unusable for the test…and the query completed in ½ second. We dropped the index, no recoding, release date met, …customer happy.
Chief Technology Officer, Orion Data Management, Hopkinton, Massachusetts