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
BeyondBGCMAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.