SQL Server 2012 - High Memory Usage
-
I'm still trying to disable McAfee in this machine at least it did not scan those folder with mdf,ldf. But at the moment, still contact help desk on how to disable it as I can't disable myself due to it the setting is grey out.
-
I have had several clients have significant problems with McAfee on production servers, especially SQL Servers. I have never had a client use McAfee on a SQL Server with success.
Has anyone mentioned updating statistics with full scan yet? Sorry, but I don't have time to reread everything this morning. I have absolutely seen failing to do that cause massive performance issues when upgrading database platform from SQL Server 2000.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
TheSQLGuru (5/21/2015)
I have had several clients have significant problems with McAfee on production servers, especially SQL Servers. I have never had a client use McAfee on a SQL Server with success.Has anyone mentioned updating statistics with full scan yet? Sorry, but I don't have time to reread everything this morning. I have absolutely seen failing to do that cause massive performance issues when upgrading database platform from SQL Server 2000.
previous sql server 2000 is using McAfee v8.5 enterprise but we never encounter any issue. While this new one is using v8.8
I had run execute sp_updatestats for all user databases
-
audiocool (5/21/2015)
1) Min and Max (Default)
2) Min (0) and Max (28GB)
3) Min (4096) and Max (24GB)
4) Min (5120) and Max (20GB) => Latest
I still have the performance issue until now.
Before that, from time to time I did the following switching the max memory from
24GB to 4096GB, wait a while then switch it back to 24GB, I saw memory usage from task manager actually went down.
But this is only effective for around 2 times u can do.. After this no impact after performing following statement. The only thing to do is restart service.
EXEC sp_configure 'max server memory (MB)', 4096;
RECONFIGURE WITH OVERRIDE;
GO
Please, stop fiddling with the memory. From the symptoms, stats and details you've posted, the memory is NOT the problem. At most it's a symptom of the problem, a secondary effect, and by doing things like changing max server memory to a very low number you are probably causing further problems with the database and application.
And, again, don't use Task Manager to look at SQL Server's memory usage. Use perfmon counters or, if you need a live tool, download Process Explorer
Current recommendations:
Apply the MS recommended antivirus config
Increase cost threshold for parallelism
Tune the 3-5 highest CPU using queries, as identified in the query stats report you did for Grant the other day.
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 -
GilaMonster (5/22/2015)
Please, stop fiddling with the memory. From the symptoms, stats and details you've posted, the memory is NOT the problem. At most it's a symptom of the problem, a secondary effect, and by doing things like changing max server memory to a very low number you are probably causing further problems with the database and application.Current recommendations:
Apply the MS recommended antivirus config
Increase cost threshold for parallelism
Tune the 3-5 highest CPU using queries, as identified in the query stats report you did for Grant the other day.
Whilst I agree on this recommendations, my thought is that the OS is under considerable memory pressure. The latest Max Memory Configuration of 20Gb should be fine in most circumstances IF and ONLY IF the unnecessary activities on that server are stopped such as RDP, Live Virus Scans etc.. The last screen-shot posted by the OP clearly indicated that this server is quite busy paging the memory and that will seriously affect the performance.
😎
-
GilaMonster (5/22/2015)
audiocool (5/21/2015)
1) Min and Max (Default)
2) Min (0) and Max (28GB)
3) Min (4096) and Max (24GB)
4) Min (5120) and Max (20GB) => Latest
I still have the performance issue until now.
Before that, from time to time I did the following switching the max memory from
24GB to 4096GB, wait a while then switch it back to 24GB, I saw memory usage from task manager actually went down.
But this is only effective for around 2 times u can do.. After this no impact after performing following statement. The only thing to do is restart service.
EXEC sp_configure 'max server memory (MB)', 4096;
RECONFIGURE WITH OVERRIDE;
GO
Please, stop fiddling with the memory. From the symptoms, stats and details you've posted, the memory is NOT the problem. At most it's a symptom of the problem, a secondary effect, and by doing things like changing max server memory to a very low number you are probably causing further problems with the database and application.
And, again, don't use Task Manager to look at SQL Server's memory usage. Use perfmon counters or, if you need a live tool, download Process Explorer
Current recommendations:
Apply the MS recommended antivirus config
Increase cost threshold for parallelism
Tune the 3-5 highest CPU using queries, as identified in the query stats report you did for Grant the other day.
What had done so far:
1) cost of threshold has been increased from 5 to 40 as recommended by Granthttp://qa.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif
2) change power mode from Balanced to High Performance
3) Change page file is Windows Server 2012 from system managed to manual mode
Min: 16GB
Max: 32GB
What is pending:
1) McAfee (Can't make any changes as setting is been locked by corporate team. Local IT also can't do anything. Will proceed after received feedback)
-
audiocool (5/22/2015)
3) Change page file is Windows Server 2012 from system managed to manual modeMin: 16GB
Max: 32GB
Quick note, if manually setting the page file size then set both the upper and lower limits to the same size, otherwise the page file will get fragmented.
😎
What is pending:
1) McAfee (Can't make any changes as setting is been locked by corporate team. Local IT also can't do anything. Will proceed after received feedback)
I have the suspicion that the virus scan is part of the user policy hence logging on to the server (Desktop/RDP/Console) will always include it, yet another reason for not doing so.
😎
-
Eirikur Eiriksson (5/22/2015)
audiocool (5/22/2015)
3) Change page file is Windows Server 2012 from system managed to manual modeMin: 16GB
Max: 32GB
Quick note, if manually setting the page file size then set both the upper and lower limits to the same size, otherwise the page file will get fragmented.
😎
What is pending:
1) McAfee (Can't make any changes as setting is been locked by corporate team. Local IT also can't do anything. Will proceed after received feedback)
I have the suspicion that the virus scan is part of the user policy hence logging on to the server (Desktop/RDP/Console) will always include it, yet another reason for not doing so.
😎
What is the recommended size for upper and lower limit since we have 32GB of RAM and space available in Drive C is around 60GB.
-
audiocool (5/22/2015)
What is the recommended size for upper and lower limit since we have 32GB of RAM and space available in Drive C is around 60GB.Tiny. SQL Server does not use the page file, so if this is a dedicated SQL Server, you can leave the page file at the minimum size that it allows.
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 -
audiocool (5/22/2015)
What is pending:1) McAfee (Can't make any changes as setting is been locked by corporate team. Local IT also can't do anything. Will proceed after received feedback)
And the query tuning, which is most likely where you're going to get the most gains.
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 -
Finally got support from corporate team help to disable the HIP and according to him,
file type like mdf, ldf, ndf already in the exclusion list in on-access scan
-
Please ask him to make sure that configurations are per the Microsoft KB articles
https://support.microsoft.com/en-us/kb/2033238 and https://support.microsoft.com/en-us/kb/309422
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 -
is the following service require to turn on
SQL Server Analysis Service ?
-
audiocool (5/22/2015)
is the following service require to turn onSQL Server Analysis Service ?
Are you planning to use it's features? Are you planning to build cubes, do data mining and the like on this server?
If so, then yes (though you probably would be better off using a different server). If not, then no.
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 -
GilaMonster (5/22/2015)
audiocool (5/22/2015)
is the following service require to turn onSQL Server Analysis Service ?
Are you planning to use it's features? Are you planning to build cubes, do data mining and the like on this server?
If so, then yes (though you probably would be better off using a different server). If not, then no.
I'm not sure what is the build cubes and data mining..
Our SQL server basically used for data exchange purposes (application <-> database)
Viewing 15 posts - 76 through 90 (of 107 total)
You must be logged in to reply to this topic. Login to reply