Critical issues detected by SQL Server BPA

Question

Critical issues detected by SQL Server BPA

madhu-686862

SSChampion

Points: 10707
More actions
November 29, 2008 at 2:45 am

#125241

hi,
1.Error SQL Server: [...] - Known issue with queries encountering performance degradation on systems with large amount of memory before build 9.00.3171.00 1 When you execute queries under the context of a login which is not a member of the sysadmins group, then you could encounter issues that relate to a large and growing Security Token cache. This could include reduced performance of queries, higher CPU usage for SQL Server process, and sudden increase in worker threads and SQL user connections.
Recommandation: Apply the latest cumulative update package or cumulative update package #3
2.Error Host: [...] - Outdated system driver that can cause paging detected There are known issues that exist in Windows and various Device Drivers which can trigger conditions that result in working set of all processes currently running on the machine to be trimmed. This will cause excessive paging and result in drastic reduction in performance of all applications on the machine (specifically SQL Server).We found that TCPIP.SYS present with version [5.2.3790.4318] and BXVBDA.SYS is also present. So we recommend you to disable TCP Chimney
plz advice me to resolve above issues...

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The Best practice advisor has given you recommendations. Are you looking for a second opinion, independent confirmation, or something else?

Recommandation: Apply the latest cumulative update package or cumulative update package #3
So we recommend you to disable TCP Chimney

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

madhu-686862 SSChampion Points: 10707 More actions · Answer 2

thanks,

I would like to know how to disable TCP chimny and there upto 9 cumulative updates, so Iam thinking to apply the recent one.plz advice me

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

TCP Chimney I don't know. May I suggest a google search?

As for CU9, providing you're on SP2 (which I assume you are), you can request the cumulative update from microsoft. Test it out on a test system before you put it in production. I haven't heard of any issues with CU9, but that doesn't mean there aren't any.

http://support.microsoft.com/kb/953752

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

Do this:

From: http://support.microsoft.com/kb/942861

To work around this problem, disable the TCP Chimney Offload feature.

To do this, follow these steps:

Click Start, click Run, type cmd, and then press ENTER.

At the command prompt, type the following command, and then press ENTER:

Netsh int ip set chimney DISABLED

Note You do not have to restart the server after you run this command.

If the performance of Windows Server 2003 decreases after you disable the TCP Chimney Offload feature, follow these additional steps:

Click Start, click Run, type Regedit, and then click OK.

Locate the following registry subkey:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

Double-click the EnableTCPChimney registry entry.

In the Edit DWORD Value dialog box, type 0 in the Value data box, and then click OK.

Double-click the EnableRSS registry entry.

In the Edit DWORD Value dialog box, type 0 in the Value data box, and then click OK.

Double-click the EnableTCPA registry entry.

In the Edit DWORD Value dialog box, type 0 in the Value data box, and then click OK.

Restart the server.

MJ

SafeDistance Valued Member Points: 50 More actions · Answer 5

Manu-J... thanks for your straightforward, helpful response. Some others on the site appear to be more interested in providing condescending, pompous replies that do nothing to help and only show that the poster doesn't really have an answer and has probably never been responsible for handling critical issues with enterprise database Instances.

Editor's Note: An apology was issued here:http://www.sqlservercentral.com/Forums/Topic610662-146-2.aspx#bm987034

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

Mark Enloe (9/15/2010)
Manu-J... thanks for your straightforward, helpful response.

Mark...surely just the above would have sufficed if you have found your answer?

Your second sentence is far from polite, unprofessional and uncalled for.

One day, you may find that you have another issue you would like help with.....

gah

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Mark Enloe (9/15/2010)
Some others on the site appear to be more interested in providing condescending, pompous replies that do nothing to help and only show that the poster doesn't really have an answer and has probably never been responsible for handling critical issues with enterprise database Instances.

Wanna put some names to that accusation? Or do you just want throw around random accusations from a safe distance?

Oh, and what help have you provided recently?

p.s. 2 year old thread.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SafeDistance Valued Member Points: 50 More actions · Answer 8

gah... Yes, the first sentence would have been a nice reply to a nice guy, but if people who are generally courteous and respectful are so much so that they let those with a little less respect for their fellow man be hurtful to those who would be intimidated, then I believe it's appropriate to try to help someone see their mistake. I'm sorry you feel the type of comments I was referring to are appropriate.

And Gail, thanks for confirming what I suspected. And I'm not sure about the 2 year old post comment. Do you mean that you're not as rude anymore? I hope so.

Thanks,

Mark

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Mark Enloe (9/15/2010)
And Gail, thanks for confirming what I suspected. And I'm not sure about the 2 year old post comment. Do you mean that you're not as rude anymore? I hope so.

My pleasure, though I suspect your mind was made up already.

Got an example of the 'rudeness'? If you're referring to this thread, I don't personally believe telling someone essentially 'I can't help you on this point, but have you tried a search engine' is rude. Rude would be 'You're a #$#$^% idiot for posting such a stupid question. Why the #@$$#%$ didn't you try google instead of bothering us'. I don't know anyone here that would do the latter.

By the way, if this kind of accusation is all you intend to contribute to the SSC community, may I please request that you think twice.

The '2 year old post' comment was just a note that you're replying on a post from 2 years ago and it's likely that most of the people involved don't follow it any longer

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150467 More actions · Answer 10

Mark Enloe (9/15/2010)
Manu-J... thanks for your straightforward, helpful response. Some others on the site appear to be more interested in providing condescending, pompous replies that do nothing to help and only show that the poster doesn't really have an answer and has probably never been responsible for handling critical issues with enterprise database Instances.

I think you are reading things into the previous responses that aren't really there :pinch:

I can't see anything wrong with what Gail posted, but I'm glad you got the answer you wanted in the end.

Please try to be more tolerant and understanding in future - it makes everybody's lives easier.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 11

Mark Enloe (9/15/2010)
Manu-J... thanks for your straightforward, helpful response. Some others on the site appear to be more interested in providing condescending, pompous replies that do nothing to help and only show that the poster doesn't really have an answer and has probably never been responsible for handling critical issues with enterprise database Instances.

I agree with Paul. Tolerance goes a long way in this business. So does the occasional reminder that Google is a pretty good tool when you're in a pinch. I know someone with 2 posts like you might not know this but Gail is the one that normally knows this type of stuff. In this case, Gail very appropriately suggested Google because she didn't know about TCP Chimney and the OP appeared to need an immediate answer. 😉

TCP Chimney I don't know. May I suggest a google search?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SafeDistance Valued Member Points: 50 More actions · Answer 12

Gail, Will you accept my apology? Sometimes in a hurry, in a stressful situation, you do things you shouldn't do. I should have not inferred what I did from your post. You've made a great contribution to many people who needed assistance. I actually was referring to the post saying something like 'do you need another opinion, a confirmation or something else.' The person obviously didn't quite understand what the BPA does and may have been overwhelmed by yet another bunch of messages that they didn't know how to interpret. I misinterpreted your response.

I sincerely would like to withdraw that reply, thank you and all the members who help for their help and move on! 🙂

Thanks, Mark

Paul White SSC Guru Points: 150467 More actions · Answer 13

Well played, Mark 😎

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

SafeDistance (9/16/2010)
Gail, Will you accept my apology?

Certainly.

I actually was referring to the post saying something like 'do you need another opinion, a confirmation or something else.'

We get a lot of posts here where someone posts a statement or describes a scenario or posts a bunch of messages without saying clearly what it is that they want/need help with. Hence you'll see people replying with something along the lines of 'What is the question?' or 'What is it that you want from us?' It's really hard to give a useful answer when you don't know what the question is.

I've seen cases where people post output like the above from BPA and what they want is a second opinion on whether it's a good thing to do and what effect it will have while other people who post similar outputs want to know how to implement the recommendations. Without clarifying which it is (or maybe if it's something else entirely) it's easy to waste an immense amount of time.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass