Logging SQL queries in Orchard

It is often useful to see what database queries were made during a specific request in Orchard. There are quite a few ways to do this (you can trace right from SQL Server, or you can use Mini-Profiler for instance), but this morning Sébastien showed me a really easy one that I thought I’d share.

Find the log4net.config file in /src/Orchard.Web/Config and edit it to add the following tag:

<logger name="NHibernate.SQL">
  <priority value="DEBUG" />
</logger>

Restart the application, then hit the URL you want to test, and look at your logs in App_data/logs. You should see new entries looking like this:

2013-04-03 18:57:30,367 [17] NHibernate.SQL -
  SELECT warmupsett0_.Id as Id575_0_,
         warmupsett0_.Urls as Urls575_0_,
         warmupsett0_.Scheduled as Scheduled575_0_,
         warmupsett0_.Delay as Delay575_0_,
         warmupsett0_.OnPublish as OnPublish575_0_
  FROM VuLu_Orchard_Warmup_WarmupSettingsPartRecord warmupsett0_
  WHERE warmupsett0_.Id=@p0;

  @p0 = 1 [Type: Int32 (0)]

(only not as nicely formatted)

To disable the SQL trace, just edit log4net.config and set the value to ERROR instead of DEBUG.