affinity mask question

Hello:
I took the MCDBA exam last week. I almost got over the hump, but one
question on the test (related to the Subject left completely
stumped. If you can provide any help, I would be most appreciative.
The gist of the question is:
You manage a server with 4-processors and 1GB of RAM. There are
performance problems, so you get some perfmon statistics. The values
you get are 90% Processor Time, 95% User time and 20% Privilege
Time. How should you approach this problem:
A. go to 2GB RAM
B. go to 4GB RAM
C. change the affinity mask to only use processors 0,1,2
C. change the affinity mask to only use processors 1,2,3
The only part of this problem (I think) I understand would be: if
the Privilege time is high, then the system is spending too much
time doing "cluster" work rather than "user" (i.e. SQLS2k) work.
But that is not the case in this problem.
Rather than just the correct answer, I need to know how to approach
a problem like this. So, any BOL aricles or links about this would
be most helpful.
TIA.
Regards..
I'm not a DBA, however I am a SysAdmin /Lead Tech for a datacenter and part
of my routine is dealing with DBs that arent running as well as they should
be.
The question seems perfectly useless to me in typical MS test style. There
is not enough information to even begin to look at solving the problem. I
have never had cause to set affinity other than testing. Sounds like a bad
work around to me.
My real world approach would be
1.) Ram is cheep and 1gig of ram is weak in any DB. SQL std uses 2gig, so as
a minimum I would start with 3gigs regardless of performance. If I knew it
was SQL Ent I would use 4gig + depending on the version of os.
2.) A modern system tends to bottleneck on disk before cpu. I would run
perfmon on cpu, disk, memory in use, pagefile, and connections for starters.
There are several ways to address disk performance if that is actually the
problem.
3.) Most of the time a box is at or near 100% cpu its loopy code or a
runaway process. Rarely does a well maintained system hit 100% cpu for any
length of time unless there is something known to be cpu intensive being
run, and that should always be done during low use time.
4.) Throwing more hardware at a problem is usually everyones first choice,
and rarely solves the performance issue.
5.) In my opinion the best way to add more hardware is to add a whole new
system and move some of the databases to that system as most DB servers I
see are made of multiple smaller databases.
6.) Bug the DBA/developer until he fixes his broken code.
7.) Bug the DBA/developer until he fixes his broken code.
8.) Bug the DBA/developer until he fixes his broken code.
9.) Bug the DBA/developer until he fixes his broken code.
10.) Bug the DBA/developer until he fixes his broken code.
YMMV,
thanks,
DaveV
"Relishguy" <dbsearch04@.yahoo.com> wrote in message
news:84e6fe3d.0406261305.6b371af4@.posting.google.c om...
> Hello:
> I took the MCDBA exam last week. I almost got over the hump, but one
> question on the test (related to the Subject left completely
> stumped. If you can provide any help, I would be most appreciative.
> The gist of the question is:
> You manage a server with 4-processors and 1GB of RAM. There are
> performance problems, so you get some perfmon statistics. The values
> you get are 90% Processor Time, 95% User time and 20% Privilege
> Time. How should you approach this problem:
> A. go to 2GB RAM
> B. go to 4GB RAM
> C. change the affinity mask to only use processors 0,1,2
> C. change the affinity mask to only use processors 1,2,3
> The only part of this problem (I think) I understand would be: if
> the Privilege time is high, then the system is spending too much
> time doing "cluster" work rather than "user" (i.e. SQLS2k) work.
> But that is not the case in this problem.
> Rather than just the correct answer, I need to know how to approach
> a problem like this. So, any BOL aricles or links about this would
> be most helpful.
> TIA.
> Regards..
|||I'm not a DBA, however I am a SysAdmin /Lead Tech for a datacenter and part
of my routine is dealing with DBs that arent running as well as they should
be.
The question seems perfectly useless to me in typical MS test style. There
is not enough information to even begin to look at solving the problem. I
have never had cause to set affinity other than testing. Sounds like a bad
work around to me.
My real world approach would be
1.) Ram is cheep and 1gig of ram is weak in any DB. SQL std uses 2gig, so as
a minimum I would start with 3gigs regardless of performance. If I knew it
was SQL Ent I would use 4gig + depending on the version of os.
2.) A modern system tends to bottleneck on disk before cpu. I would run
perfmon on cpu, disk, memory in use, pagefile, and connections for starters.
There are several ways to address disk performance if that is actually the
problem.
3.) Most of the time a box is at or near 100% cpu its loopy code or a
runaway process. Rarely does a well maintained system hit 100% cpu for any
length of time unless there is something known to be cpu intensive being
run, and that should always be done during low use time.
4.) Throwing more hardware at a problem is usually everyones first choice,
and rarely solves the performance issue.
5.) In my opinion the best way to add more hardware is to add a whole new
system and move some of the databases to that system as most DB servers I
see are made of multiple smaller databases.
6.) Bug the DBA/developer until he fixes his broken code.
7.) Bug the DBA/developer until he fixes his broken code.
8.) Bug the DBA/developer until he fixes his broken code.
9.) Bug the DBA/developer until he fixes his broken code.
10.) Bug the DBA/developer until he fixes his broken code.
YMMV,
thanks,
DaveV
"Relishguy" <dbsearch04@.yahoo.com> wrote in message
news:84e6fe3d.0406261305.6b371af4@.posting.google.c om...
> Hello:
> I took the MCDBA exam last week. I almost got over the hump, but one
> question on the test (related to the Subject left completely
> stumped. If you can provide any help, I would be most appreciative.
> The gist of the question is:
> You manage a server with 4-processors and 1GB of RAM. There are
> performance problems, so you get some perfmon statistics. The values
> you get are 90% Processor Time, 95% User time and 20% Privilege
> Time. How should you approach this problem:
> A. go to 2GB RAM
> B. go to 4GB RAM
> C. change the affinity mask to only use processors 0,1,2
> C. change the affinity mask to only use processors 1,2,3
> The only part of this problem (I think) I understand would be: if
> the Privilege time is high, then the system is spending too much
> time doing "cluster" work rather than "user" (i.e. SQLS2k) work.
> But that is not the case in this problem.
> Rather than just the correct answer, I need to know how to approach
> a problem like this. So, any BOL aricles or links about this would
> be most helpful.
> TIA.
> Regards..