SQL Server Background Processes

Question

SQL Server Background Processes

p.modi

SSChasing Mays

Points: 648
More actions
October 13, 2010 at 1:36 am

#387995

Hi,
Can anybody help me in finding "SQL Server Background Processes".
i want to know what all process is carried of in SQL Server background.

Viewing 15 posts - 1 through 15 (of 17 total)

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

hallidayd SSCrazy Points: 2490 More actions · Answer 1

Um, that's a bit of an open question. You really could do with being more specific. As a start you look at the results of sp_who2 and investigate those process running on spid 50 or less.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

hallidayd (10/13/2010)
As a start you look at the results of sp_who2 and investigate those process running on spid 50 or less.

Since SQL 2005 the rule that spids under 50 are system processes and spids over 50 are user is not longer completely true. There are situations where there can be more than 50 system processes running.

Rather query sys.dm_exec_sessions and filter for is_user_process = 0

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hallidayd SSCrazy Points: 2490 More actions · Answer 3

Thanks Gail. I had a feeling there was some change like that but reckoned it was enough to get the guy started!

p.modi SSChasing Mays Points: 648 More actions · Answer 4

Thanks for both of you.

Actually i have to prepare an presentation on this topic, as i m not aware of this topic, so i want to know what all process SQL server Carries at backend and what are the major area that need to concentrate.

If you guys can suggest sume key area or you have any link which can help me than please share it with me.

Thanks,

Pankaj

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

p.modi (10/13/2010)
Actually i have to prepare an presentation on this topic, as i m not aware of this topic, so i want to know what all process SQL server Carries at backend and what are the major area that need to concentrate.

Odd presentation topic. Why this one? The background processes are not typically things that you need to know much about or worry about.

If you guys can suggest sume key area or you have any link which can help me than please share it with me.

I suggest you take a look at all the system processes as we've explained, and then hit a search engine for details on any that you are not familiar with.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hallidayd SSCrazy Points: 2490 More actions · Answer 6

Agreed. Assuming you are reasonably new to SQL Server this is an extremely odd topic - any sort of depth on this would be considered 300, probably 400 level. If I were in your shoes I would clarify exactly what your employers mean by background processes; something might be getting lost in translation here.

p.modi SSChasing Mays Points: 648 More actions · Answer 7

Thanks once again.

Gail i think its an good idea to start with sp_who2.

Rookie i will try to get more clear picture on same. thank you for your input as i was not knowing that this topic is so wide.

Thanks,

Pankaj

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

p.modi (10/13/2010)
thank you for your input as i was not knowing that this topic is so wide.

It's not wide, it's deep.

If you go beyond a list of the background processes you've getting into a very technical, very complex area. If you're investigating out of curiosity, that's one thing, but to be required to do a presentation is rather weird. There won't be all that much that's relevant to day-to-day admin or development

p.s. No one named 'rookie' here.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hallidayd SSCrazy Points: 2490 More actions · Answer 9

I suppose if it was me I would concentrate on the lazy writer. It does at least give some context to other more familiar SQL Server topics however without a rounded background knowledge it might not make much sense to the audience.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

hallidayd (10/13/2010)
I suppose if it was me I would concentrate on the lazy writer.

Lazy writer and checkpoint I think. They're about the most relevant of the system processes to day-to-day work. More relevant than the Signal Handler or Trace Queue Task. I'd be hard-pressed to tell you offhand what either of those do.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

hallidayd SSCrazy Points: 2490 More actions · Answer 11

Part of the reason I picked that one is it's the only I can think of off-hand I know anything about 😉

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 12

RichardDouglas

SSCarpal Tunnel

Points: 4029

October 14, 2010 at 7:13 am

#1236826

Talk about the SQLOS 😀

Hope this helps,
Rich

[p]
[/p]

smile_netz Ten Centuries Points: 1018 More actions · Answer 13

Hi,

Since the topic is about the background processes, is there a way to kill the connection for this using the SSMS?

Whenever I restore a backup, this causes me a problem because a background process is running somehow even if the applications are already shutdown.

Thanks.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

No. Only user processes can be killed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass