Memory for SQL Server Machine
-
Hi,
We have a SQL Server 2008 R2 server on a Windows Server 2008 R2 which was put in production as a data warehouse server in January this year. It is a 2 processor 24 core machine. ( 12 cores per processor). It has latest 15k drives in raid configurations and 32 gig memory.
For the past 2 weeks we have seen the performance of the machine degrade rapidly. Our SSIS processes and queries have started taking more time for execution. In many cases it is almost twice of what it was before. Even some of the basic operations like connecting to the server via ssms or looking at job history are taking more time.
Since the time the server went into production, we have never seen consistent results for Query execution and I always had a feeling the server is not performing upto its full potential.
My question is:
Is the low memory responsible for the poor performance? Being a 24 core machine is the 32 gig memory insufficient. I have set the Max Degree of Parallelism to 8.
The other day I rebooted the machine and run couple of ssis jobs in parallel and they took half their normal execution time.
Any advice on this will be appreciated.
Thanks.
Apurva
-
Perhaps SQL Server is consuming all of the server's memory. Can you verify that SQL Server has a max/min memory limit? Also, there are some things you may want to change if your SQL Server is 32-bit.
-
It sounds like you may have hyper-threading enabled on those cores. If so, try disabling the hyper-threading and see if that helps performance.
The reason I say this is that I am not aware of any 12-core processors (8 core is the largest I know about). This sounds like 2 6-core processors with hyper-threading to get a count of 12.
With hyper-threading enabled, you could be running into issues with parallelism and high IO workloads.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I have set the min memory to 8 gig and max to 26 gig. It is a 64 bit machine.
Also this machine is 24 core. It was one of the latest from HP.
Our old data warehouse production box was converted into a test machine for with all its drive arrays. It is a 2 processor quad core machine with 8 gig of memory. Even today
when I run queries on production backups I get very good and consistent results. This is unfortunately not true for our new machine.
A while back I had read that a processor core usually requires around 2 gig of memory for optimal performance. I am not sure how true it is. I would appreciate if someone can clarify on this.
-
1-Define MAX Memory parameter at least 24 GB
2-You have define Max Degree of Parallelism to 8,This will define as 2 because its depend on physical processor not on logical
3-Set MAX worker Thread 704 if this OS is 64bit othwisewise
http://technet.microsoft.com/en-us/library/ms187024.aspx
4-Whats the RAID Level ?
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBAMy Blog
www.aureus-salah.com -
Syed Jahanzaib Bin hassan (4/22/2011)
2-You have define Max Degree of Parallelism to 8,This will define as 2 because its depend on physical processor not on logicalMax degree of parallelism affects the number of logical processor that a query operator can parallel over, not physical. The only place where the number of sockets means anything is in licensing.
3-Set MAX worker Thread 704 if this OS is 64bit othwisewise
http://technet.microsoft.com/en-us/library/ms187024.aspx%5B/quote%5D
As per the page that you referenced, 704 is the default configuration for a 24-core, 64-bit instance, so why change the setting explicitly to that? It's not going to make any difference.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Default for the Operating System but SQL server didnt set this as default you have to set this parameter as well
Max degree of parallelism affects the number of physical processor
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBAMy Blog
www.aureus-salah.com -
Syed Jahanzaib Bin hassan (4/24/2011)
Default for the Operating System but SQL server didnt set this as default you have to set this parameter as wellWindows Operating system doesn't have a concept of 'worker threads', and from the very article you referenced.
Calculating max worker threads
The default value for max worker threads, 0, allows SQL Server to automatically configure the number of worker threads at startup. This setting is best for most systems; however, depending on your system configuration, setting max worker threads to a specific value sometimes improves performance.
The following table shows the automatically configured number of max worker threads for various combinations of CPUs and versions of SQL Server.
The only time the worker threads needs changing is when you want to make it something other than the automatically configured value (as listed in the above table), and in general that should only be done under guidance from Customer Support or when you absolutely know exactly what you're doing and why.
Max degree of parallelism affects the number of physical processor
The only thing in SQL that depends on number of physical processors is licensing. Other than that, SQL considers cores to be processors, and if there's hyperthreading considers those virtual cores to be full processors as well.
I have a desktop with a single quad-core processor (one physical, 4 cores), my maxdop is set to 2 and when queries parallel they do indeed run over 2 of the cores.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Default value is 0 when sql server start
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBAMy Blog
www.aureus-salah.com -
Syed Jahanzaib Bin hassan (4/24/2011)
Default value is 0 when sql server startYes, and if you would read the article you referenced and I quoted from..
The default value for max worker threads, 0, allows SQL Server to automatically configure the number of worker threads at startup.
Hence 0 means 'SQL, please configure the value that you think best for this server'. If it is 0 (meaning automatically configure), the server has between 16 and 32 processors and is 64 bit, SQL configures 704 worker threads.
There is no way whatsoever that 0 could possibly mean 0 worker threads, as if it did SQL could never run.
There are many settings (eg max dop, worker threads) where 0 means 'used automatic configuration based on this server'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Just to get the ball rolling, I would run Perfmon to find out where the bottlenecks are. It doesn't look like it's memory, but perhaps it could be IO? I, personally, have never encountered a HT performance degradation so I won't be a good person to talk to about that, but I have read a lot of people who did run into problems.
Some other considerations that I can think of are:
Are there any other applications that run on this server? If so, you may need to adjust the memory settings so the other applications won't be starving.
Perhaps you can look at the SQL settings on the previous server to see if there are any settings that was missed.
Hope that helps a little.
-
Friends Thank you for the suggestions.
When I set the max memory to 26 gig, I did take into consideration the memory required by the threads ( I assumed the number of threads to be 750), the startup memory etc.
I did not explicitly set the max number of threads. I am not sure if it will make a difference but I will surely give it a try.
The raid level for our data drives is raid 10 and for non data drives is raid 1. All these are 6g 15k drives probably the best in magnetic drives. I do not see any I/O pressure. the disk and cpu both seem to be doing well.
What has puzzled me is the erratic behavior of our server, with no consistent results.
After some consideration we have ordered more memory for the server. I will update everyone once the memory goes in.
-
Do not change the max worker threads. That's an advanced setting, and only helps in very few and rare circumstances. The recommendation is to leave this at the default unless you specifically know this is a problem. Most times it is not.
The advice to change that is poor advice. Leave it at 0.
-
Another thing I just thought about is if your cache hit ratio is low. Perhaps the performance dashboard/Perfmon can assist in diagnosing the server. Sorry, I'm just thinking about anything that can help you resolve this issue. Please let us know if you find the issue and the resolution.
-
While looking through the SQL logs I found that there is message "Cannot use large page extensions. lock memory privilege was not granted".
I thought for 64 bit systems this is not important. Is there a benefit in allowing the page locking option for a 64 bit system?
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply