October 7, 2008 at 8:26 am
Hi
Because we sometimes suffer from sudden performance drops I started perfmon for a while and noticed that most of the Buffer Manager related counters show huge variations. For example: Buffer Manager Reserved pages shows 51418 and at the next polling (15secs later) it shows 278. Same for Cache Manager(Execution Contexts) Cache Pages. This one goes up from 10 to 4294967280 in 15 seconds. And so on...
When we feel the change in performance, disk I/O and queuing is also high.
When we run sp_updatestats things seem to stabalize almost immediately. Does anyone know what I need to look for (and how) to find the cause?
Thx
Thierry
October 7, 2008 at 9:39 am
Have you rebuilt your indexes lately? The swings appear to be a bit extreme but not sure what is going on in your environment. Additionally, it also sounds like there may be some improperly tuned queries hitting the box at the times that the IO goes up and queue is increasing. Running the updatestats is a band-aid. Not saying that keeping statistics up to date is not important, but having the indexes up to date is even more important.
I would certainly try to run some traces to see if you can't pin down the queries that are driving things pretty hard during those times.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
October 8, 2008 at 1:20 am
thx for the reply David
We have an optimization job every Sunday. Takes about 6h to complete.
This morning I found a msg in my mailbox from a co-worker in another office location saying that they launched some batch at that time. Most probably that's the cause. I ran profiler almost every day during a couple of hours but unfortunately I always missed the time they started the batch. We'll need to find out what exactly the batch is doing. I would have thought that by now they know running these special batches during day time is evil 🙂
Thx again
Thierry
October 9, 2008 at 2:47 am
I have seen this behaviour at my old place on a Data Warehouse.
We start with many thousand pages in the bufferpool, and not much space allocated to locks. A user runs a query that accesses half the universe to get a report, and needs many thousands of locks. SQL does its best to get the query completed successfuly, and takes space from the buffer pool to give to the lock pool. We end up with a few hundred pages of bufferpool and thousands of pages in the lock pool.
We ended up re-writing this query as a stored procedure. The monster query was broken into a number of queries that created #temp tables, summarising the data as appropriate. We then joined the #temp tables to get the data for the report. This significantly reduced the number of locks and allowed more space for the bufferpool.
Rewriting a query as a SP is not often the best solution to this type of problem, but it was the best solution to that particular problem.
On 32-bit SQL, you can safeguard some of the size of the bufferpool by using AWE and storage above the 4GB line - none of this can be stolen for use by locks.
If you are using 32-bit SQL Server and consistently run with a large amount of lock space and a small buffer pool and performance is not what you need, this could be an indication you may need to upgrade to 64-bit. Running 64-bit will not reduce the size of your lock pool, but will allow you to throw more memory at the buffer pool to regain performance.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 9, 2008 at 3:27 am
Thanks for the reply TC
info like this is always appreciated by a junior dba like myself!
We are running 32bit and we have AWE enabled. I removed the /3Gb switch from the boot.ini last weekend as I thought (and MS confirmed) that it was not necessary when you have more than 4Gb of physical memory. Still a bit confused though about the actual reason so if you have more info on that... 🙂
Regards
T.
October 9, 2008 at 4:02 am
There is a LOT of mis-information given about the /3GB switch, and from your response some is now coming from Microsoft!
What happens without the /3GB switch:
This information only applies to 32-bit Windows, as the /3GB switch is not relevant to 64-bit Windows. Windows normally divdes the memory below the 4GB line into 2 portions. The OS can take up to 2GB, and applications can take up to 2GB.
If you have less than 2GB memory on the box, bot the OS and applications have to share what is available memory, but the OS will take precedence if any memory is needed.
If you have more than 2GB memory on the box the OS cannot use more than 2GB memory. If you have 4GB on the box, applications cannot use more than 2GB.
What the /3GB switch does:
For servers with less than 16GB, Windows normally needs a maximum of 1GB to function. The /3GB switch forces Windows to allow applications to use up to 3GB of the memory below the 4GB line, while restricting the OS to a maximum of 1GB.
When it is useful:
If you have a box that has less than 16GB, and you run applications that use a lot of memory (e.g. SQL Server) then using the /3GB switch will give those applications an extra 1GB memory. This memory is below the 4GB line, and can be used for any purpose (memory above the 4GB line has restricted use).
My recommendation is that any server with less than 16GB (but see below) should have the /3GB switch enabled.
When it is harmful:
If you have more than 16GB memory on the server, then Windows needs more than 1GB to manage the additional memory. You must never use the /3GB switch on a server with more than 16GB memory.
It is possible you may find issues with the /3GB switch when you have between 12GB and 16GB on your server, depending on what OS components you have installed. If you have a lot of operating system components installed (e.g. Domain Controller, DNS server, etc) then Windows may need more than 1GB memory. If you use /3GB in this situation and Windows needs more memory it may become unstable or crash. If it operates normally, then the /3GB switch should be OK.
What about the /PAE switch:
This operates completely independantly to the /3GB switch. The /PAE switch allows 32-bit Windows to make available memory above the 4GB line to those applications that are specially coded to use it. The Page Adressing Extentions are also a component of hardware page protection, and /PAE enabled automatically if the /NOEXECUTE switch is processed by the CPU chip.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 9, 2008 at 4:28 am
Hi again TC
well duh...
When I started investigating and reading articles about the /3Gb switch my feeling (because all info IS INDEED confusing) was that things are exactly as you explain and that's why it was so confusing to me because I thought that taking the /3Gb switch away would (potentially) leave SQL Server with 1Gb less memory. But who am I to contradict MS??? So I removed it.
And now you're saying I was right all along. I feel 1) relieved that I was right 2) not at ease going back to my managment saying I (or MS) made a mistake and we need to add the switch again 😉
Still wondering and confused though as to why the 'could not allocate xMb of memory' warning during SQL startup went away when I removed the /3Gb switch. You would think it was the other way around because it now has even less memory, right?
Regards
Thierry
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply