SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Frequent blocking occurences after SQL SP4
 Forum Locked
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 5

donpolix
Yak Posting Veteran

97 Posts

Posted - 07/11/2005 :  12:44:16  Show Profile  Click to see donpolix's MSN Messenger address  Send donpolix a Yahoo! Message
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 - 07/11/2005 :  12:47:34  Show Profile  Click to see donpolix's MSN Messenger address  Send donpolix a Yahoo! Message
Im also seeing process being blocked by itself.

Donn Policarpio
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/11/2005 :  12:50:01  Show Profile
use PROFILER to see what's blockling what?

Kristen
Go to Top of Page

donpolix
Yak Posting Veteran

97 Posts

Posted - 07/11/2005 :  13:03:44  Show Profile  Click to see donpolix's MSN Messenger address  Send donpolix a Yahoo! Message
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
Go to Top of Page

Thrasymachus
Constraint Violating Yak Guru

USA
483 Posts

Posted - 07/11/2005 :  13:05:23  Show Profile
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.
Go to Top of Page

donpolix
Yak Posting Veteran

97 Posts

Posted - 07/11/2005 :  15:03:18  Show Profile  Click to see donpolix's MSN Messenger address  Send donpolix a Yahoo! Message
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
Go to Top of Page

Thrasymachus
Constraint Violating Yak Guru

USA
483 Posts

Posted - 07/11/2005 :  15:29:25  Show Profile
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.
Go to Top of Page

hawk
Starting Member

25 Posts

Posted - 07/26/2005 :  17:56:33  Show Profile
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.
Go to Top of Page

donpolix
Yak Posting Veteran

97 Posts

Posted - 07/27/2005 :  11:12:22  Show Profile  Click to see donpolix's MSN Messenger address  Send donpolix a Yahoo! Message
would this require restarting the sql service? or rebooting the server?

Donn Policarpio
Go to Top of Page

hawk
Starting Member

25 Posts

Posted - 07/27/2005 :  11:28:42  Show Profile
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...
Go to Top of Page

hawk
Starting Member

25 Posts

Posted - 07/27/2005 :  11:31:15  Show Profile
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
Go to Top of Page

hawk
Starting Member

25 Posts

Posted - 07/27/2005 :  12:12:48  Show Profile
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.

Edited by - hawk on 07/27/2005 12:18:10
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 07/27/2005 :  12:49:13  Show Profile
how large of a performance problem are you seeing? Have you contacted microsoft and let them look at it?



-ec
Go to Top of Page

hawk
Starting Member

25 Posts

Posted - 07/27/2005 :  14:09:01  Show Profile
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.

Edited by - hawk on 07/27/2005 14:09:55
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 07/27/2005 :  14:45:20  Show Profile
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
Go to Top of Page

hawk
Starting Member

25 Posts

Posted - 07/28/2005 :  16:26:53  Show Profile
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.
Go to Top of Page

hawk
Starting Member

25 Posts

Posted - 08/01/2005 :  12:27:15  Show Profile
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.
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 08/02/2005 :  18:41:51  Show Profile  Visit graz's Homepage
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.
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 08/02/2005 :  18:54:16  Show Profile  Visit paulrandal's Homepage
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.)
Go to Top of Page

hawk
Starting Member

25 Posts

Posted - 08/03/2005 :  17:42:26  Show Profile
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.

Edited by - hawk on 08/03/2005 17:46:17
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 08/03/2005 :  17:47:31  Show Profile  Visit paulrandal's Homepage
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.)
Go to Top of Page
Page: of 5 Previous Topic Topic Next Topic  
Next Page
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000