September 7, 2010 at 10:53 am
Hi, we were investigating a matter today with our BI Application Server being really slow and I was looking at my activity monitor for our SQL Server and I noticed something with our Background processes that looked "odd" to me.
All of the background processes have a "wait type" and the wait times are very large. I see that the processes were logged in on 9/5/2010 and the last batch was also executed on 9/5/2010.
The Log Writer process is also suspended.
Is this something I should be alarmed about? From what I know about background processes (specifically the Lazy Writer) it should be running things constantly and freeing up threads. So, when I see that the lazy writer is in a wait status and hasn't done anything for 3 days... it seems like there is something wrong.
And if so, is the only solution to reboot the server? Or should I kill these processes... would they be reinitalized if I killed them?
September 7, 2010 at 11:09 am
Firstly you can't kill the system processes, except by restarting SQL.
They should be waiting most of the time. The system processes should be idle the majority of the time on a well-running system. The lazy writer is certainly one you don't want running a lot. It's job is to write dirty pages to disk when there's memory pressure. If the lazy writer isn't idle, it's a sign of memory pressure and not something that you really want. (it's called the lazy writer for a reason)
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
September 7, 2010 at 11:13 am
Ok thanks, I hadn't noticed it really before and we're having a lot of network contention and I/O issues with a slow server connection so I wanted to be sure it was supposed to be doing that.
And hmm... I thought the lazy writer was in charge of managing threads and processes... perhaps I need to go read up on that again. 🙂
September 7, 2010 at 11:18 am
amy26 (9/7/2010)
Ok thanks, I hadn't noticed it really before and we're having a lot of network contention and I/O issues with a slow server connection so I wanted to be sure it was supposed to be doing that.
Then check the user processes. The system processes should not be running often and, if they are running a lot and taking up resources better spent on user's queries, that would definitely be a problem.
And hmm... I thought the lazy writer was in charge of managing threads and processes... perhaps I need to go read up on that again. 🙂
No, that it is not. It's one of the three processes responsible for writing dirty pages to disk. (the other two being the checkpoint and the eager writer)
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
September 7, 2010 at 11:21 am
GilaMonster (9/7/2010)
Then check the user processes. The system processes should not be running often and, if they are running a lot and taking up resources better spent on user's queries, that would definitely be a problem.
Yup yup... as I said I just noticed these when we were investigating the other processes and I wanted to make sure they were supposed to be like that. 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply