Does SQL Profiler work with LocalDB?

Question

Is it possible to use SQL Profiler to observe queries being requested of a LocalDB instance?

Answer 1

It as simple as setting the server to (LocalDB)\v11.0

http://expressprofiler.codeplex.com/discussions/456518

Answer 2

From http://expressprofiler.codeplex.com/

ExpressProfiler (aka SqlExpress Profiler) is a simple but good enough replacement for SQL Server Profiler with basic GUI.

No requirements, no installation.

Can be used with both Express and non-Express editions of SQL Server 2005/2008/2008r2/2012 (including LocalDB)

Answer 3

This is what I used on SQL Server Express 2012 (note: not "LocalDB* - I have never used LocalDB so maybe that is different to a "regular" SQL Server Express).

Step 1: Setup the trace

This is basically the "hard work". You first need to find out where the default log directory of SQL Server is. You need this directory name to specfiy the trace file.

Then create a trace by doing something like this:

DECLARE @TraceID int
DECLARE @tracefile nvarchar(255)
DECLARE @endDate datetime
DECLARE @size bigint

-- no file extension!
SET @tracefile = 'C:\Data\sqlserver\MSSQL11.SQLEXPRESS\MSSQL\Log\mydb_trace' 

-- tracing stops when either the max size of the file is reached 
-- or the enddate (whichever occurs first)
-- size is in MB
SET @size = 250
SET @enddate = DateAdd(DAY, 15, GetDate())

EXEC @rc = sp_trace_create @TraceID output, 2, @tracefile, @size, @enddate

Now for every event that should be traced, you need to call sp_trace_setevent multiple times to define which column for that event should be returned:

For a full list of events and columns, see: http://msdn.microsoft.com/en-US/library/ms186265%28v=sql.90%29.aspx

-- Enable Event: 45 = SP:StmtCompleted
EXEC sp_trace_setevent @TraceID, 45, 27, @on -- 27: EventClass
EXEC sp_trace_setevent @TraceID, 45, 12, @on -- 12: SPID
EXEC sp_trace_setevent @TraceID, 45, 35, @on -- 35: DatabaseName
EXEC sp_trace_setevent @TraceID, 45, 11, @on -- 11: SQLSecurityLoginName
EXEC sp_trace_setevent @TraceID, 45,  6, @on --  6: NTUserName
EXEC sp_trace_setevent @TraceID, 45,  8, @on --  8: ClientHostName
EXEC sp_trace_setevent @TraceID, 45, 10, @on -- 10: ApplicationName
EXEC sp_trace_setevent @TraceID, 45,  1, @on --  1: TextData
EXEC sp_trace_setevent @TraceID, 45, 13, @on -- 13: Duration
EXEC sp_trace_setevent @TraceID, 45, 14, @on -- 14: StartTime
EXEC sp_trace_setevent @TraceID, 45, 15, @on -- 15: EndTime
EXEC sp_trace_setevent @TraceID, 45, 18, @on -- 18: CPU
EXEC sp_trace_setevent @TraceID, 45, 29, @on -- 29: Nesting Level

All of the above calls must be done for each and every event you want to trace!

I find the events 12 = SQL:BatchCompleted, 42 = SP:Starting, 43 = SP:Completed, 45 = SP:StmtCompleted, 50 = SQL Transaction the most interesting ones.

Optionally you can setup a filter, I usually filter out system events and only show events for a specific database:

-- Exclude system events (so only user events are shown)
-- 60: IsSystem Column
--  0: logical Operator: AND (only)
--  1: comparison operator: not equal
--  1: value
EXEC sp_trace_setfilter @TraceID, 60, 0, 1, 1

-- only mydb database
EXEC sp_trace_setfilter @TraceID, 35, 0, 6, N'mydb'

Once the trace is setup, it must be activated:

EXEC sp_trace_setstatus @TraceID, 1

(Note the above must be run as a single batch, because of the variable usage).

To see how the trace was defined, you can use the following statement:

select traceid,
       case property
         when 1 then 'Trace Options'
         when 2 then 'Trace file'
         when 3 then 'Max. file size'
         when 4 then 'Stop time'
         when 5 then 'Status'
       end as property_name,
       case
         when property = 5 then
            case convert(nvarchar(max), value)
               when '1' then 'Active'
               else 'Inactive'
            end
         else convert(nvarchar(max), value)
       end as value
from ::fn_trace_getinfo(null)
where property in (2,3,5)

Now run your application or whatever issues statements to the database that you want to trace.

Step 2: Retrieve trace information

For this you need to know the full path to the actual trace file (from Step 1). Note that for fn_trace_gettable you need to specify the file including the file extension.

SELECT ApplicationName,
       LoginName,
       HostName,
       SPID,
       Duration,
       StartTime,
       EndTime,
       DatabaseName,
       reads,
       writes,
       RowCounts,
       cpu,
       EventClass,
       case EventClass
        when 10 then 'RPC:Completed'
        when 11 then 'RPC:Starting'
        when 12 then 'SQL:BatchCompleted'
        when 13 then 'SQL:BatchStarting'
        when 40 then 'SQL:StmtStarting'
        when 41 then 'SQL:StmtCompleted'
        when 42 then 'SP:Starting'
        when 43 then 'SP:Completed'
        when 44 then 'SP:StmtStarting'
        when 45 then 'SP:StmtCompleted'
        when 50 then 'SQL Transaction'
        when 67 then 'Execution Warnings'
        when 71 then 'Prepare SQL'
        when 72 then 'Exec Prepared SQL'
        when 73 then 'Unprepare SQL'
       end as Event,
       LineNumber,
       TextData
FROM ::fn_trace_gettable('C:\Data\sqlserver\MSSQL11.SQLEXPRESS\MSSQL\Log\mydb_trace.log', default)
order by StartTime;

Adjust the above to return the information you are interested in.

Once you have the information you need, you have to turn off the trace:

Step 3: disable the trace

For this you need to know the Trace-ID (e.g. by running the "info statement from Step 1). The with this ID, you need to first stop the trace, then you can delete it:

-- stop the trace
EXEC sp_trace_setstatus @TraceID, 0

-- delete the trace
EXEC sp_trace_setstatus @TraceID, 2

Answer 4

Microsoft SQL Server 2012 Express LocalDB is an execution mode of SQL Server Express targeted to program developers.

Sql Profiler is not provided with SQL Server Express.

Thus, you can't use Sql profiler for your LocalDB.

However,you can go through alternative ways.

How to use SQL Profiler with SQL Server Express Edition

Answer 5

You can use SQL Profiler just as you do with all other SQL editions as long as you know the proper server name. You can find the server name using the SqlLocalDb utility.

To find it, use sqllocaldb info YourInstanceName to find the Instance Pipe Name. It has the form np:\\.\pipe\LOCALDB#12345\tsql\query

Use this as the server name to connect to the server and start profiling