Saturday, August 26, 2006

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.

7 comments:

Anonymous said...

I'm running Sql Server 2005 Workgroup Edition, v9.1.2047.00
Using Mngm Studio I add to add Sql Profile in via an option.
However that is unable (dimmed)

so I can't c how I can run Sql Profile.

if u could reply via email - thelal@iname.com that would be great thanks
Brendan

Anonymous said...

Take a look at http://sqlprofiler.googlepages.com/

Anonymous said...

cheap wedding gowns,
discount bridal gowns,
China wedding dresses,
discount designer wedding dresses,
China wedding online store,
plus size wedding dresses,
cheap informal wedding dresses,
junior bridesmaid dresses,
cheap bridesmaid dresses,
maternity bridesmaid dresses,
discount flower girl gowns,
cheap prom dresses,
party dresses,
evening dresses,
mother of the bride dresses,
special occasion dresses,
cheap quinceanera dresses,
hot red wedding dresses

products said...

China Wholesalers has been described as the world’s factory. buy products wholesaleThis phenomenom is typified by the rise ofbusiness. Incredible range of products available with China Wholesale “Low Price and High Quality” not only reaches directly to their target clients worldwide but also ensures that wholesale from china from China means margins you cannot find elsewhere and China Wholesale will skyroket your profits.china wholesale productsbuy china wholesalewholesale chinawholesale productsbuy products

products said...

We offer designer , custom wedding dress, cheap cell phonescheap wedding dressesbridal gowns, wedding gowns, Flower Girl Dresses andwholesale cell phonesso on. Buy tailor-made anddesigner wedding dresses designer to be a special brides now!

products said...

Wholesale Gemstone cheap Gemstone Jewelry from China, discount wholesale jewelry Beads, wholesale Gemstone jewelry cheapest Wholesale Naruto anime and manga cheap cosplay costumes, cheap Naruto cosplay costumes on sale, buy Naruto cosplay naruto online shop.Wholesale anime and manga wholesale crystal jewelry costumes, cheap cosplay costumes on sale, buy anime cosplay costumes online shop

www.mueblescebreros.com said...

So, I don't actually believe this will have effect.