More Memory

Question

More Memory

Viewing 15 posts - 16 through 30 (of 34 total)

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

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 1

It looks like you don't have the "ALTER SETTINGS" server-level permission on this instance. Can you get that or can you have someone grant that to you? If not, then have the sysadmin run the command for you.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

DaveK2014 Right there with Babe Points: 788 More actions · Answer 2

I am part of the Sysadmin group, your talking about under Security then servers roles we have a sysadmin group and I am part of that.

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 3

Well that is the error you get when you don't have the proper access, but let's try this a different way. Right click the server in Object Explorer and select Properties and then go to the Memory page (on the left hand side). Capture that screen and send it back.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 4

DaveK2014 (4/14/2014)
Just waiting for approval to buy that book Keith suggested as it looks like it would be great.

Download the free pdf while you wait for the hard copy 😀

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

DaveK2014 Right there with Babe Points: 788 More actions · Answer 5

here you go.

I actually did just download the free one 🙂 got a nice new production printer that we needed to test out so 400 pages should do nicely :).

Thanks for all your help.

We are just trying to give sql as much memory as it can possible have since we have all this extra it can have more.

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

DaveK2014 (4/14/2014)
I Tried running each one separate and all of them together and they give the same errors see attached. Maybe I have something wrong.

Looks like someone has tried to enable direct updates to the system tables.

Run this:

EXEC sp_configure 'allow updates', 0

RECONFIGURE

Then try running Keith's queries again.

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

DaveK2014 Right there with Babe Points: 788 More actions · Answer 7

after running the allow updates to zero I get this

Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install.

Msg 5845, Level 16, State 1, Line 2

Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.

I have added the lock pages in group policy to my account but I am thinking that might not be the right account to set it on. My Sql server services run on the same account as the lock pages was set to.

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

EXEC sp_configure 'AWE Enabled',0

RECONFIGURE

It shouldn't be on for a 64-bit process.

Looks like someone's been playing fast and loose with your config settings.

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

humbleDBA Hall of Fame Points: 3490 More actions · Answer 9

Keith Tate (4/14/2014)
...
Second if you want it to take 7GB of memory then set the "min server memory" and it will take that amount of memory.
...

Actually, the Min Memory setting is a 'floor' setting, in that SQL will only hold the minimum amount of memory once it has breached that minimum value - in your quote, Keith, 7GB. Until then, ie, until SQL actually takes up 7GB or more, SQL will only use the memory it needs, it does not initially grab all 7GB - see technet article.

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 10

That is correct, I guess I should have stated that if you want to have 7 GB, after SQL Server has acquired at least 7 GB for the buffer pool then it won't release below 7 GB of memory, but it seemed wordy 😀

But you are right it was misleading my bad.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

DaveK2014 Right there with Babe Points: 788 More actions · Answer 11

I am still looking for some more help on this if anyone has some more ideas.

So just to go over it again we have Server 2008R2 running a 32bit version of sql 2008.

We turned on AWE set the min to 0 and max to 6144Megs.

I checked the account running our sql server service and set Lock page file in memory and perform file maintenance task to the same account.

Reboot the sql server.

Now sql is using 200 Megs of data, the server has about 8 gigs available but the page file is using 8+ gigs.

Any thoughts?

Thanks again.

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

SQL doesn't use the page file.

How are you checking its size and how is the page file configured? (and why are you concerned about its size?)

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

DaveK2014 Right there with Babe Points: 788 More actions · Answer 13

Just using task manager to see the PF size.

our sql server was using upwards of 3.8 gigs now its down to 200 megs so that should be slightly concerning wouldn't you think?

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

What do you mean by '200MB of data' and where are you seeing that?

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

DaveK2014 Right there with Babe Points: 788 More actions · Answer 15

Sorry that's the memory usage showing for the Sqlserver.exe in Task manager. Since its 64bit its a bit more accurate then the 32bit os. I also checked in process monitor and it is showing 200 megs as well.

We want to make sure Sql can use as much memory as it wants up to the 6 gigs max we set.