January 11, 2011 at 9:33 am
When I run sp_who2 active I can see that there is an open process on tempdb for a CHECKPOINT. The CHECKPOINT started the night I did a Windows update and rebooted the server. The CPUTime is currently 297141 and the DiskIO is 31654. I am new to the admin side of SQL, but I don't think this is normal. How can safely kill the process and does anyone have any insight as to how this happens?
This is a fully updated version of SQL 2008 (Standard 64-bit Edition) on a Windows virtual box.
Thanks for the help!
Update - I can see that it is normal for a CHECKPOINT to be active for the master database, though it is in status SUSPENDED on my other servers, but on the server I am having the most performance issues with has the CHECKPOINT in the status of BACKGROUND in the tempdb database.
Any help would be greatly appreciated...even just saying it is nothing to worry about would be awesome.
January 11, 2011 at 10:15 am
You should really watch the Checkpoint Pages / Sec performance monitor counter and see if it drops off. If it is running all the time or for really long periods of time then your IO throughput to the disk is not sufficient to support the activity that you have. IF you were to shut down the instance right now you would have a really long recovery time as well.
Let me know what you see with the performance monitor counter.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 11, 2011 at 10:21 am
What is the best method to monitor that? Is there a way to monitor that by process or is it something that I just have to watch on perfmon for the whole server?
Thanks for the help,
Dane
-- I am slowing trying to build up my admin toolbox.
January 11, 2011 at 10:29 am
You should have Performance Monitor on your machine. You would open that and remove the counters that are currently running. Then in the drop down you will want to put your server name \\Servername. Then in the Performanc Object drop down you will select SQLServer:Buffer Manager and in the counter list that will be displayed select "Checkpoint pages/sec".
The hope would be that you would see that counter spike periodically and then drop down to 0. Based on what you were saying it might not be able to complete a checkpoint due to the activity or due to the performance of the IO subsystem. If there is not enough disk throughput that will be an issue. There could potentially be an issue with the virtual configuration to the disk subsystem as well.
Can you provide any information about the disk configuration?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 11, 2011 at 10:36 am
Thanks! I will keep an eye on it. I have given our server team a lot of information to check on our virtual setup and they feel confident that it is setup properly. Our organization has multiple SQL virtual servers and this is the one giving us the most grief. The others are performing well.
I don't have much information on our virtual setup, but I do know we are using VMware ESX 3.5.
I am starting to think that it was a red herring. Thanks for the Checkpoint pages/sec counter it has not been above 0 (saw a short spike to 401.986, but then it went back to 0 which as described by your comment is appropriate behavior) since I have been watching it. Is this counter specific to Checkpoint tasks (sometimes the obvious is misleading)?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply