SQL Express Profiler

After installed SQL Server 2008 you will find it also gives you a performance tool called SQL Server Profiler. It is a very useful tool which can provide you a lot of information about your database and the SQL statements executed on it. One of the common use in daily development is tracking the sql statements hit database come from your application, especially when the sql statements are generated by ORM framework such as nHibernate.

Unfortunately it doesn't come with SQL Server Express. Good news is that there are some open source tools which provide similar functionality, such as the one I normally use: sqlexpressprofiler. It's under GNU GPL, which means you can use it on commercial production as long as it is not distributed.

Today this little tool helped me to tackle a production issue. In the morning the first level support team called me that a store could not do one of the operation. From the log it looked like it was timeout from the WCF service. So I TeamViewered to the store server which has a SQL Server Express database installed. Launched the little tool sqlexpressprofiler, logged into the terminal then hit that operation button. Failed, as expected. Went back to the profiler, got the relevant sql it just executed and went through them. turned out one of the sub queries used 18 seconds. Great! I got the problem.

The rest of that was pretty routine then. Restored the database locally. Checked the where clause in the query. Checked the index of the table (it was a view in the case). It only had clustered index of the primary key. Added a nonclustered index based the where condition. Ran the query again. Less than 1 second. Done!