BeyondBGCM
asked on
SQL server taking high resource
on my production server , sql server.exe is taking lot of resources, 100% memory and CPU utilization.
that blocks everything else on that machine. can you help me to reduce the resource consumption , by some administrative configuration,
the operation it does is loading data using bcp or executing query to fetch data
that blocks everything else on that machine. can you help me to reduce the resource consumption , by some administrative configuration,
the operation it does is loading data using bcp or executing query to fetch data
Here is a general article on how to determine the Max Memory setting.
Link: http://blog.sqlauthority.com/2013/12/12/sql-server-optimal-memory-settings-for-sql-server-notes-from-the-field-006/
The author recommends 80% of system RAM in his example. Since I have no idea what other services are running on the server or how much RAM is in the box... I went with 50%.
After reading the article you may be able to make a better determination as to what the setting should be.
Dan
Link: http://blog.sqlauthority.com/2013/12/12/sql-server-optimal-memory-settings-for-sql-server-notes-from-the-field-006/
The author recommends 80% of system RAM in his example. Since I have no idea what other services are running on the server or how much RAM is in the box... I went with 50%.
After reading the article you may be able to make a better determination as to what the setting should be.
Dan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
1. open SQL Server Management Studio
2. connect to the SQL Server in question
3. Right click on the server name and select Properties
4. select the memory tab
5. update the "Maximum server memory (in MB)
6. click OK.
7. restart the SQL Server service
Depending on the amount of RAM in the server and the other applications running on the server, I would initially recommend setting the max memory in step to 50% of total RAM in the server.
That should leave enough RAM for the OS and an additional app or 2 room to operate.
Dan