I am developing a web application, which consists of a .NET application, using Entity Framework, and MS SQL 2012 back end.
Every now and then we experience a slowdown in the system, which causes certain pages to become extremely slow at loading (10+ seconds). I cannot identify a pattern in the pages - other than they are all "edit" pages that contain dropdown select lists, however other pages with lists more extensive than these load just fine.
I have noticed that the CPU usage for MS SQL Server is sitting at around 10% consistently, which while not high, it is higher than the 0% it normally sits at when there are no requests. It seems there must be some kind of background process running.
I have tried to investigate using SQL Server Management Server Activity Monitor, however it does not show any active queries being performed (other than required to render the page requested).
Is there another way to identify or log all queries being performed on SQL Server?
