Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 130
  • Last Modified:

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
0
BeyondBGCM
Asked:
BeyondBGCM
  • 2
1 Solution
 
Dan McFaddenSystems EngineerCommented:
You can use the SQL Server Management Studio to change the amount of memory SQL server is allowed to consume.

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
0
 
Dan McFaddenSystems EngineerCommented:
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
0
 
Chris LuttrellSenior Database ArchitectCommented:
Here is Brent Ozar's explanation about resource use which I found most enlightening.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now