database settings

Question

database settings

Viewing 7 posts - 16 through 21 (of 21 total)

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

Oracle_91 SSChampion Points: 14548 More actions · Answer 1

Hi Gila,

As you said, i understand that since the machine is dedicated as database server and no more app's are running then there is no need to change the default settings of CPU's. Let sql server use all CPU's!!! This is fair enough.

But if i have question for myself?

If am using all the CPUs then does it effect the overall performance of the sql server??

using more CPU's means using more threads, more context switches and even CPU's are running and processing faster but ultimately its only the one SQL Server has to wait for each CPU's output and combine it and then show it back to the end user.

Does this degrade the overall performance of the SQL Server?

Also, what if the number of processors it is odd numbered.? Is there anything like, if number of CPU's is odd, then we need use the default all cpu's?? is there anything like that?

Correct if am wrong!!!

Or else before taking the machine to production, do we need to set the DOP and test all the critical job operations and check the performance and basing on that set the DOP ???

Thanks in Advance Gila

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

Oracle_91 (6/26/2010)
If am using all the CPUs then does it effect the overall performance of the sql server??

Yes, it can run queries faster because it has more processing power available to it.

If you limit SQL to a certain number of CPUs only, the rest will sit idle, doing just about nothing. That's a fair waste of hardware.

using more CPU's means using more threads, more context switches and even CPU's are running and processing faster but ultimately its only the one SQL Server has to wait for each CPU's output and combine it and then show it back to the end user.

Um....

Do you KNOW that context switches are a problem in your environment? There are places they are, but it's far from common.

SQL's perfectly capable of running multiple queries at the same time, it's only when it parallels queries that it has to wait for the CPUs to finish (and parallel doesn't immediately mean that it will use all CPUs for a parallel query.) So more CPUs means more queries running concurrently and a greater throughput.

If you're having problems with parallelism (and you need to test and see, not assume), then you can limit the maxdop setting for SQL. Not the CPU affinity, the degree to which SQL is allowed to parallel queries. Test, check, verify and then decide if you need to change the setting. Often too much parallelism is actually a result of badly tuned queries.

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

Oracle_91 SSChampion Points: 14548 More actions · Answer 3

Oracle_91

SSChampion

Points: 14548

June 26, 2010 at 3:46 am

#1185338

Super!!

Makes sense...

Thanks so much Gila!:-)

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 4

I apologize for the off-topic questions, but your environment is "interesting":

- You seem to have a lot of iron for a dedicated SQL box (definitely a good thing!). Why go with SQL 2000?

- If you are sticking with SQL 2000, why SP 3?, Does SP 4 break an application you are supporting?

- Did you ever mention if you were running 32 or 64-bit Windows? SQL?

Joie Andrew
"Since 1982"

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 5

Contrary to what seems to be the popular opinion and depending on who has access to the server, I'll usually set the max number of CPU's to be used for a given thread to somewhere between two and four especially if "common users" have access. Think of it as a poor-man's resource governor... it keeps one really bad query from taking over the whole server.

--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)

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

Joie Andrew (6/27/2010)
- Did you ever mention if you were running 32 or 64-bit Windows? SQL?

The only 64-bit version of SQL 2000 is the Itanium. There's no x64 version of SQL 2000.

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

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 7

The only 64-bit version of SQL 2000 is the Itanium. There's no x64 version of SQL 2000

Wow, I guess I did not really think about the 64-bit version came out before x64 was widely accepted. I guess I am so used to seeing versions available in different releases that thinking of that skipped my mind altogether.

Thanks for keeping me in check Gail!

Joie Andrew
"Since 1982"