by Oliver
17. August 2011 18:28
Lately, we encountered a problem with the speed of our search on www.camping.info for a certain set of search criteria. It sometimes used to take over a few seconds before the updated results were shown. This most likely seemed to be a problem with the database so I went to investigate the offending queries using the wonderful NHibernate Profiler. I found a very slow query:
So I went and copied the long running query into a new query window in SSMS (SQL Server Management Studio). Since we use MS SQL Express on our production servers we don’t get the advanced database tuning advisor features of the full edition. Still, when you right click on any query window you’ll see the option “Include Actual Execution Plan” …
[FULL] 
[EXPRESS]
… which already offers a lot of detail, as you can see in the following screenshot:
When you right click on the execution plan you’ll see the option “Missing Index Details…” - there you get a CREATE INDEX statement that is ready to use once you give a name to the new index. I did this for three indexes and now we have this for the same query:
115 ms instead of 1993 ms – that’s an improvement of 94%! Even if DB queries lasting longer than 50-60 ms are not really fast anymore, we’ve still got quite an improvement here.
Well, that’s it. Using a well-priced tool like NHibernate Profiler to identify slow queries and the Execution Plan feature of SSMS, we’re able to get quite a performance improvement in a short time.
Happy Coding and Optimizing,
Oliver