The full and developer versions of Microsoft SQL Server include SQL Profiler, a tool that captures SQL Server events from the server and saves those events in a trace file. You can use this utility to:
- Monitor the performance of an instance of the SQL Server Database Engine, Analysis Server, or Integration Services (after they have occurred).
- Debug Transact-SQL statements and stored procedures.
- Analyze performance by identifying slowly executing queries.
- Test Transact-SQL statements and stored procedures in the development phase of a project by single-stepping through statements to confirm that the code works as expected.
However, SQL Profiler isn’t included with either LocalDB or SQL Server 2012 Express.
An awesome, but pricey, option is Entity Framework Profiler from Hibernating Rhinos. If your daily work involves Entity Framework, this tool is a must have.
My focus here, though, is on a free no frills alternative: Express Profiler.
By default, Visual Studio 2012 Lightswitch projects use LocalDB while debugging, so, unless your project uses an external datasource rather than the intrinsic one… or unless you are trying to profile a deployed application, you’ll need to enter:
in the “Server” textbox of Express Profiler.
I encourage other Lightswitch users to experiment with this useful resource. It can be invaluable for understanding how your application is actually using the database, since so much of that is hidden behind the scenes, taken care of automagically by Lightswitch and Entity Framework. It can help you identify cases where adding an index may dramatically improve query performance. It can help you verify that you’ve implemented your RIA service properly, and it isn’t issuing redundant queries or disregarding proper paging.
Also, if you’ve got your own favorite tool for SQL Server profiling, share in the comments!