If you use ASP.NET's App_Data directory, chances are good you're using SQL Server Express and the User Instances feature. When you try to profile the default instance (usually ".\SQLEXPRESS"), you'll discover that your user instance databases don't show up in the database list and cannot be profiled. The reason is because user instance databases get their own instance of SQL Server.
To profile a user instance, you need to discover what the named pipe of the connection is. You can find it by running a query against the default instance:
SELECT owning_principal_name,instance_pipe_name FROM sys.dm_os_child_instances
The result will be something like this:
owning_principal_name instance_pipe_name --------------------- ------------------------------------ REDMOND\bradwils \\.\pipe\FACC73E6-E724-44\tsql\query (1 row(s) affected)
Locate the user in question (if you're running in the Web Server built into Visual Studio, it will be your local user account; if you're running inside of IIS, it will be whatever user you have the app pool configured to run as). Copy the instance_pipe_name value and use that as your server name for SQL Profiler:
Once you're connected, you will be able to trace the SQL for your user instance databases.
Note: You can also use that same "server name" with SQL Server Management Studio if you need to perform ad-hoc SQL queries against the user instance.
