August 21, 2011 at 6:41 am
sp_configure 'affinity I/O mask',0
RECONFIGURE WITH OVERRIDE
ı run this query but it dosen t change running value ????
August 21, 2011 at 9:07 am
From Books Online:
The affinity I/O mask option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change affinity I/O mask only when show advanced options is set to 1. In SQL Server, reconfiguring the affinity I/O mask option requires a restart of the SQL Server instance.
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
August 22, 2011 at 1:59 am
Thanks for reply i solve that problem but i have a problem i set i/o affinity and procesor affinity auto and i set server
memory max and min server memory 200000 and max server memory 200000 but now it is using 211 gb why is it any proble?
August 22, 2011 at 2:17 am
Can it be producure cache?
August 22, 2011 at 3:17 am
No, it's not the plan cache, that is part of the buffer pool and is constrained by max memory.
SQL will take memory outside of buffer pool for things like CLR, backup buffers, linked server drivers and a few others, but that should be a few hundred MB usually. Using a lot of CLR?
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
August 22, 2011 at 3:19 am
I don t have clr but temp db is very big now
August 22, 2011 at 3:23 am
p.s. Why set processor and IO affinity? Most of the time they're best left at default values.
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
August 22, 2011 at 3:53 am
I change it to default values the before dba set it but i change it to normal default.
August 22, 2011 at 7:59 am
Given your questions and that you are taking over for a previous DBA it sounds like you are in need of a system and performance review from a professional. No telling what is going on there! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 22, 2011 at 8:01 am
What a coincidence that both of the people helping out offer such a service... 😀 😉
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
August 22, 2011 at 8:45 am
Let's make it 3 :-D.
No seriously, I'd go with either of those 2 before me 😉
August 22, 2011 at 9:05 am
GilaMonster (8/22/2011)
that is part of the buffer pool and is constrained by max memory.
is that correct? Although part of the same overall memory pool that is constrained by the max memory, they are separately managed memory spaces. Take for example a 32 bit server, the procedure cache cannot exist in AWE allocated memory whereas the buffer pool can
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 22, 2011 at 9:10 am
Perry Whittle (8/22/2011)
GilaMonster (8/22/2011)
that is part of the buffer pool and is constrained by max memory.is that correct?
Yes.
Although part of the same overall memory pool that is constrained by the max memory, they are separately managed memory spaces. Take for example a 32 bit server, the procedure cache cannot exist in AWE allocated memory whereas the buffer pool can
Kinda. The buffer pool is the paged-memory area (meaning it's allocated in 8k chunks). Min and max server memory set the size of the buffer pool. The buffer pool is divided internally into data cache, plan cache and several other caches.
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
August 22, 2011 at 10:48 pm
Now it dosent change it is about 213 and 214 gb changing but i cant find where is it use 13 gb memory. because when i take memory consumption report MEMORYCLERCK_SQLBUFFERPOOL AWE Memory Allocated =200 gb
August 23, 2011 at 4:55 am
with 200GB of memory I can't believe this is an x32 system? Min and max memory only set the limits for the buffer cache, in Denali I beleive this will change, however there are several memory pools and such which also take memory. For example one of my servers is set to 54,000 MB max memory - however memory used by SQL Server is currently 57,552 MB ( as reported by Process Explorer ) current buffer cache is reported as 49, 145 MB , procedure cache reported at 1,757 MB. I have 358,000 free pages reported currently ( I've just cleared a mess of ad-hoc sql plans from the server - about 3 GB - but that's another story 🙂 )
I don't see why you're so concerned with a few GB of memory, a running SQL Server is really a living thing with hopefully memory pools and such changing to suit the current working environment. No different to the o/s which will report usually constantly changing values.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply