Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2005 Resources Management Expand / Collapse
Author
Message
Posted Wednesday, July 21, 2010 4:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 13, 2013 7:06 PM
Points: 3, Visits: 78
Can I limit some jobs or users in SQL Server 2005 using resources (CPU or Memory)?
If SQL Server running on 2 CPUs host, Can I limit SQL Using CPU#1 or CPU#2 or using max 50%?
Post #956213
Posted Wednesday, July 21, 2010 7:11 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 2:26 PM
Points: 386, Visits: 626
SQL 2005 doesn't have resource Pool management like SQL 2008, so no for doing anything per user.

as far as limiting to a processor you can use sp_configure and affinity mask to limit to a particular processor. I would read up on this topic and practice it as this is not something to do on a production system without a lot of testing.

http://msdn.microsoft.com/en-us/library/ms187104.aspx

As far as memory consumption you can limit the memory under the SQL instanced Server Properties | Memory | and configure the maximum amount of memory you would like to allow SQL to use.

Once again this should be tested in a dev environment as limiting the amount of SQL memory will impact SQL in almost every way, more is normally better not less. that being said I understand everyone has thier limitations they must opperate under, but be very careful adjusting any of these things as you could critically limit your server.


Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Post #956329
Posted Wednesday, July 21, 2010 9:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 13, 2013 7:06 PM
Points: 3, Visits: 78
Thanks for ur help!
Post #956902
Posted Wednesday, July 21, 2010 9:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 8:51 PM
Points: 33,266, Visits: 15,431
Keep in mind this is for the whole server instance, not by user or job. So if you limit memory, you are limiting it for the entire instance and a job can still use most of it. Same for CPU, if you limit this to one CPU, one user/job can use all the CPU resources for that one CPU in SQL Server.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #956906
Posted Friday, July 23, 2010 12:07 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 1, 2014 8:37 AM
Points: 4,769, Visits: 208
Your absolutely right Steve!
Post #958153
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse