How to define SPID of default trace

Question

How to define SPID of default trace

SQL Guy 1

SSCoach

Points: 15974
More actions
May 8, 2013 at 9:37 am

#394736

We have our default background trace enabled. I am trying to find its SPID number. Does it have spid at all, and how to define it ?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323507 More actions · Answer 1

when i run sp_who2, i see a few rows that might be the "right" server side spid(14,29 and in my case), with the command "TRACE QUEUE TASK"

why do you need the spid of a server side trace?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQL Guy 1 SSCoach Points: 15974 More actions · Answer 2

Thanks Lowel, I found. It's #15 in my case. But sp_who2 is too large for me, it returns close to 1000 recs. So I actually ran

select * from sys.sysprocesses

where lastwaittype like '%trace%'

order by spid

Now, the reason why I need it. I am not happy with the info provided with default trace. It has almost nothing for performance, what it has is mostly connections info. I created my own trace, aimed particularly for performance. I run it from time to time in production. But before placing it to run it constantly in production, I want to compare their consumption metrics.

I can probably foresee your next advice - to use extended events. Yes, that's my next step, I need to learn it first.

Lowell SSC Guru Points: 323507 More actions · Answer 3

we are really on the same page;

i use traces a LOt, especially becasue i'm very familiar with it, and it's easy to create a trace template with profiler, and make views to hit the results.

While i'm aware that extended events supposedly have a smaller impact, except for a few samples i've fiddled with, I'm not yet familiar enough with extended events to keep it at the top of the toolkit yet.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!