November 11, 2011 at 7:55 am
Yesterday, a normally hostile client was threatening nuclear attack. Hawaiian's can be surprisingly volatile when their server is slow:-P "What's going on, it's slow as death, keeps locking and SQL Server is taking up 1.8GB of memory and 100% of CPU!" They had even tried rebooting the server, to no avail.
Today, everything seems normal. Of course they haven't woke up there yet. CPU utilization runs around 0% and SQL server has 1.5GB ram in use.
The db in question is a subscriber to a merge replication. I last set this up one week before it all started. (For maintenance, I drop the publication do any maint, build new db's for the subscribers and set the publication and subscriptions up again.
Question: Could the snapshot agent have run and the subscriber spent all day applying the new snapshot whilst the users were trying to use it? How could I tell? How can I prevent/reschedule or something. A Hawaiian invasion of America would be ugly indeed.
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
November 11, 2011 at 8:22 am
100% CPU consumption could be result of any activity on Server, not necessarily SQL Server.
November 11, 2011 at 8:23 am
But the task mangler said SQL server was using all the cpu
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
November 11, 2011 at 8:24 am
Download this, read chapter 3. (and chapter 4 for that matter)
and maybe these
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
November 11, 2011 at 8:26 am
Half Bubble (11/11/2011)
Question: Could the snapshot agent have run and the subscriber spent all day applying the new snapshot whilst the users were trying to use it?
Maybe, though shouldn't unless something or someone marked the subscription for reinit. Did you check the snapshot agent's history and the replication history?
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
November 11, 2011 at 8:27 am
Oh, thank you.
read read read
(I will undoubtedly get myself confused before sorted.)
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
November 11, 2011 at 8:28 am
GilaMonster (11/11/2011)
Download this, read chapter 3. (and chapter 4 for that matter)and maybe these
Ok, scratch more sleep from schedule! :hehe:
November 11, 2011 at 8:44 am
By "Locking" the client seems to mean all the programs on all the users computers (that access the db) pause for tens of seconds at the same time. Their fairly astute IT guy thought to look at what the server is doing.
I've done this by accident by making a stored procedure that inserts rows into a table that takes a long time to run, and once preforming tens of thousands of needless inserts and deletes on a frequently used table. (code bug) Took all day for replication to get done deleting these rows one by one and reinserting them one by one over and over.
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply