Profile your SQL Express 'User Instance'

In the free SQL Server 2005 Express Edition , you get a new connection string parameter 'User instance=true'.

"data source=.\SQLEXPRESS;Integrated Security=SSPI;
AttachDBFilename=|DataDirectory|people.mdf;User Instance=true"

When this parameter is specified, SQL Express will create another instance of the sqlservr.exe process on the client. This process is running in the security context of the currently logged on user. The connection object passed back to the client is connected to this new user child instance, running as a client process in the user's security context.

More details about User Instances? Read this excellent article on msdn: Working with User Instances

SQL Profiler

For monitoring purposes (what's going on under the hood, troubleshooting and more), the SQL Server Profiler acts as a big time safer. The Profiler is able to read and display all the traffic between the client (e.g. a custom-application, SQL Management Studio etc.) and the SQL Server. Just attach the Profiler to a running SQL Server instance.

But how to attach to a 'user instance', where you don't have a well known instance name?

First, query the master database from SQL Express as follows:



There you get the named pipes per instance. You get the heart-beat as well: alive or dead (if the Lifetime of a User Instance Connection has timed out; default is 60 minutes).

Second, connect with the pipe (just alive instances can be connected):



Third, get the traffic, sniff, profile...



You get the Profiler together with Workgroup-, Standard- and Enterprise edition.

SQL Server Management Studio / SQL Server Management Express

This solution will work for SQL Server Management Studio as well:



The Management Studio does display all attached Databases(in my case AspNetDB.MDF and People.MDF). - There is the free Version SQL Server Management Studio Express.