Doubled the size of my SQL Server and now getting bad performance

Question

Doubled the size of my SQL Server and now getting bad performance

Viewing 11 posts - 16 through 26 (of 26 total)

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

Alexander Suprun SSCertifiable Points: 6253 More actions · Answer 1

george sibbald (10/21/2011)
a max memory setting of 118GB is the buffer pool only, other SQL caches will add to that.

george, where did you get this?

Alex Suprun

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

alexander.suprun (10/21/2011)
george sibbald (10/21/2011)
a max memory setting of 118GB is the buffer pool only, other SQL caches will add to that.
george, where did you get this?

Should be in the docs, somewhere. Max server memory (and min server memory) control the buffer pool, the paged memory area (meaning divided into 8kb pages). There's also the multi-page allocations, non-paged memory. On 32-bit SQL that was called MemToLeave (not called that anymore on 64-bit)

Pretty sure I remember Jonathan covering it in his new book (shameless promotion) http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

If you google/bing SQL Server Max Memory Setting you'll find plenty of resources that talk about that setting being for the buffer pool. Here's just one, http://msdn.microsoft.com/en-us/library/ms180797.aspx

Edit: Changed the Link to a link instead of a code block:-P

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

george sibbald SSC Guru Points: 104210 More actions · Answer 4

alexander.suprun (10/21/2011)
george sibbald (10/21/2011)
a max memory setting of 118GB is the buffer pool only, other SQL caches will add to that.
george, where did you get this?

experience, lots of DBA work and reading stuff.

Take a quick look in task manager and at perfmon counters for memory allocation, you will often see the total amount of memory allocated to sqlservr.exe is greater than the max memory setting.

---------------------------------------------------------------------

Alexander Suprun SSCertifiable Points: 6253 More actions · Answer 5

Thanks! I've never got into details of that max memory setting before.

Alex Suprun

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

george sibbald (10/21/2011)
alexander.suprun (10/21/2011)
george sibbald (10/21/2011)
a max memory setting of 118GB is the buffer pool only, other SQL caches will add to that.
george, where did you get this?
experience, lots of DBA work and reading stuff.
Take a quick look in task manager and at perfmon counters for memory allocation, you will often see the total amount of memory allocated to sqlservr.exe is greater than the max memory setting.

Well, not task manager if you're using locked pages or AWE.

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

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 7

This might not be the problem but we recently ran into just such a problem. It was a bugger to figure out but the fix was simple... we had purchased a bad memory chip. As soon as we replaced it, performance came right back.

Several years ago, we had a similar problem and it was, again, a memory problem. It turned out that folks had installed a different type of memory and the two were incompatible.

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

george sibbald SSC Guru Points: 104210 More actions · Answer 8

GilaMonster (10/21/2011)
george sibbald (10/21/2011)
alexander.suprun (10/21/2011)
george sibbald (10/21/2011)
a max memory setting of 118GB is the buffer pool only, other SQL caches will add to that.
george, where did you get this?
experience, lots of DBA work and reading stuff.
Take a quick look in task manager and at perfmon counters for memory allocation, you will often see the total amount of memory allocated to sqlservr.exe is greater than the max memory setting.
Well, not task manager if you're using locked pages or AWE.

locked pages?. I decided not to complicate matters with those cans of worms as the OP states he is using 64bit.

---------------------------------------------------------------------

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

george sibbald (10/21/2011)
GilaMonster (10/21/2011)
Well, not task manager if you're using locked pages or AWE.
locked pages?. I decided not to complicate matters with those cans of worms as the OP states he is using 64bit.

If locked pages is enabled then task manager reports the wrong memory amount because of the API calls used to allocated the memory (even on 64 bit).

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

george sibbald SSC Guru Points: 104210 More actions · Answer 10

so it does. I told you not to open that can of worms. 🙂

---------------------------------------------------------------------

Eric M Russell SSC Guru Points: 125596 More actions · Answer 11

If possible, try running a schema comparison including indexes and statistics between the new database and the previous database instance. Perhaps in the process of migrating to the new server something didn't get copied over.

Also look into monitoring plan recompiles and how your cache is being used.

http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/23/4-0-useful-queries-on-dmv-s-to-understand-plan-cache-behavior.aspx

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho