Frequent blocking occurences after SQL SP4

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Frequent blocking occurences after SQL SP4

Author

Topic

donpolix
Yak Posting Veteran

97 Posts

Posted - 2005-07-11 : 12:44:16

After applying sql sp4, we often experience locking problems, unlike before that we had sp3a. I can't afford to revert it back at this point.

Any suggestions? Thanks!

Donn Policarpio

donpolix
Yak Posting Veteran

97 Posts

Posted - 2005-07-11 : 12:47:34

Im also seeing process being blocked by itself.

Donn Policarpio

Kristen
Test

22859 Posts

Posted - 2005-07-11 : 12:50:01

use PROFILER to see what's blockling what?

Kristen

donpolix
Yak Posting Veteran

97 Posts

Posted - 2005-07-11 : 13:03:44

i just saw it in Enterprise Manager.
Like:

spid 212 (Blocked by 212)
spid 212 (Blocking)

From there, I can also see what is being executed (sql being run)..
Self blocking processes being displayed isnt normal from this point.

Donn Policarpio

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-07-11 : 13:05:23

Read this. A bunch of folks are having this problem with sp4. I have not heard anything further.

http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic1883.aspx

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

I am available for consulting work. Just email me through the forum.

donpolix
Yak Posting Veteran

97 Posts

Posted - 2005-07-11 : 15:03:18

thanks thrasy..
i guess this page exclusive latch request is giving me headache instead of improvements, it's just that that my sp3a queries runs w/o problems, and relatively faster than sp4,... simple basis i had here.

im worried about rolling back to sp3a as it would again affect our operation..
is there any hotfix that you heard coming out to address performance degradation on sp4?

Donn Policarpio

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-07-11 : 15:29:25

I have not heard of anything but Mr. Paul Randal of The SQL Development Team is prowling this site at the moment. He is the only one I know who may have an answer. Bait....

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

I am available for consulting work. Just email me through the forum.

hawk
Starting Member

25 Posts

Posted - 2005-07-26 : 17:56:33

After installing SP4 we are seeing processes locking themselves as has been mentioned here. It is not happening on all our servers but on some of them. For the ones having the problem I have changed the "Specify the number of processors to use for parallel execution of queries:" setting to 1.

So far this seems to have resolved the issue. Although we make a sacrifice in terms of parallel processing.

donpolix
Yak Posting Veteran

97 Posts

Posted - 2005-07-27 : 11:12:22

would this require restarting the sql service? or rebooting the server?

Donn Policarpio

hawk
Starting Member

25 Posts

Posted - 2005-07-27 : 11:28:42

quote:
Originally posted by donpolix

would this require restarting the sql service? or rebooting the server?

No.

In Enterprise Manager just right click on the Instance and select "Properties" then the "Processor" tab then in the "Parallelism" section of that tab click the radio button next to "Use" and select 1 from the drop down.

It seems that something in SP4 is causing some queries to select TERRIBLE query plans. Removing the parallelism factor by setting it to 1 seems to force the query optimizer to stick with a better plan.

I'd recommend everyone put a call into MS support if you have an MSDN call you can use. Because they REALLY need to resolve this.

Today I can confirm that for us changing the parallelism setting has resolved the issue. (Without my "so far" hedge .) Today we have our normal load of usage and still no problems and test queries that were having the problem yesterday are running fine now.

Please resolve this MS, I want my parallelism back

. I've got a call in to MS Tech support, but that has been going slow...

hawk
Starting Member

25 Posts

Posted - 2005-07-27 : 11:31:15

Also the following T-SQL will change the parallelism setting if you don't want to use Enterprise Manager:

USE master
EXEC sp_configure 'max degree of parallelism', '1'
RECONFIGURE WITH OVERRIDE
GO

hawk
Starting Member

25 Posts

Posted - 2005-07-27 : 12:12:48

We are now seeing some short instances of self locking. Guess I should have kept my my "so far" hedge

.

The instances of self locking are much shorter than before. But still slow things down more than before installing SP4. So things are better, but still worse than before SP4.

Our test queries that used to self lock are not doing it, but some queries running in our system still cause it.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-07-27 : 12:49:13

how large of a performance problem are you seeing? Have you contacted microsoft and let them look at it?

-ec

hawk
Starting Member

25 Posts

Posted - 2005-07-27 : 14:09:01

quote:
Originally posted by eyechart

how large of a performance problem are you seeing? Have you contacted microsoft and let them look at it?

-ec

We are working with MS support on this issue. Been gathering information for MS since yesterday and still in process.

This is a MAJOR performance problem for us. If we can't resolve it with MS Support by Friday we will go back to SP3a this weekend.

With parallelism set to use all processors we were seeing queries take 24 times longer than usual. With it set to 1 the queries that take longer than usual are taking a few times longer than normal, but it is happening with far less queries than before.

As many people as are having this issue should put a call into MS Support.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-07-27 : 14:45:20

quote:
Originally posted by hawk
We are working with MS support on this issue. Been gathering information for MS since yesterday and still in process.

This is a MAJOR performance problem for us. If we can't resolve it with MS Support by Friday we will go back to SP3a this weekend.

With parallelism set to use all processors we were seeing queries take 24 times longer than usual. With it set to 1 the queries that take longer than usual are taking a few times longer than normal, but it is happening with far less queries than before.

As many people as are having this issue should put a call into MS Support.

I have yet to upgrade to SP4, but I had heard that there were problems with it. Keep us in the loop if you can. thanks.

-ec

hawk
Starting Member

25 Posts

Posted - 2005-07-28 : 16:26:53

We are going to have to go back to SP3a!

After sending information to MS Tech support all I'm getting back is talk about optimizing queries. When some of the queries we have problems with are simple inserts of data from one table to another. And I already know we have our indexes well optimized.

So bottom line is that I'm not comfortable with the feedback I'm getting from MS Tech Support.

We'll keep SP4 on our machines that have not had issues, but for the ones now having issues we are going back to SP3a and I'll wait till MS can resolve the issues this patch has created.

I recommend more people with SP4 installed put calls in regarding this issue. Until more folks complain they will keep giving the same standard response and not look into what has really caused this problem.

And for folks that have not installed SP4, I recommend you wait. I thought waiting till a couple months after SP4's release would be enough time for the bugs to be flushed out. But in the futre I'll wait 3 months or more instead.

hawk
Starting Member

25 Posts

Posted - 2005-08-01 : 12:27:15

After going back to SP3a on Instances with issues running SP4 all those previous issues are gone.

This proves to me beyond all doubt that SP4 causes issues in some cases that MS needs to resolve.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-08-02 : 18:41:51

FYI - Here's a discussion of the self-locking issue: http://groups-beta.google.com/group/microsoft.public.sqlserver.server/msg/b86e343e513ab281?hl=en

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-02 : 18:54:16

quote:
Originally posted by hawk

We are going to have to go back to SP3a!

After sending information to MS Tech support all I'm getting back is talk about optimizing queries. When some of the queries we have problems with are simple inserts of data from one table to another. And I already know we have our indexes well optimized.

So bottom line is that I'm not comfortable with the feedback I'm getting from MS Tech Support.

We'll keep SP4 on our machines that have not had issues, but for the ones now having issues we are going back to SP3a and I'll wait till MS can resolve the issues this patch has created.

I recommend more people with SP4 installed put calls in regarding this issue. Until more folks complain they will keep giving the same standard response and not look into what has really caused this problem.

And for folks that have not installed SP4, I recommend you wait. I thought waiting till a couple months after SP4's release would be enough time for the bugs to be flushed out. But in the futre I'll wait 3 months or more instead.

What was the case number please (SR.....)? I'll make sure they're aware of Santtu's explanation.

Sorry Thrasy' - didn't see the earlier bait.

Thanks

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

hawk
Starting Member

25 Posts

Posted - 2005-08-03 : 17:42:26

quote:
Originally posted by paulrandal

What was the case number please (SR.....)? I'll make sure they're aware of Santtu's explanation.

Sorry Thrasy' - didn't see the earlier bait.

Thanks

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

SRX050726602487

I must say I don't find Santtu's explaination to be adequate. Because even as people asked in that thread after his explaination and he never could resolve it does not explain the difference in performance.

Glad someone from MS is reading this, but there is a real problem here that has yet to be resolved.

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2005-08-03 : 17:47:31

I've asked Santtu to followup the newsgroup thread.

Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources