Link to home
Start Free TrialLog in
Avatar of Richard Comito
Richard ComitoFlag for United States of America

asked on

Change the amount of the CPU my SQL 2008r2 use.

I have a SQL 2008r2 database that is maxing out the server that it is on windows 2008 server.  SQL is using 17.5 gigs of memory on a server with 18 gigs available.  Right now eveything is running slow and it looks like it is the amount of CPU that the SQL is using is the culprit.

How do I go about configuring SQL to use only 16 gigs, that way it will leave 2 gigs for the OS?

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Tony303
Tony303
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
CPU and memory tie n when there is a large amount of swapping.

How large is your database? Do the sql data files reside on a multi-disk RAID 5 or 10? Where are the sql transaction logs reside? How big are the LDF files?

There are tunning guides using perfmon to determine where your bottleneck might be and what causes the high CPU.  You should use the tuning agent to see whether additions of an additional index, statistic or execution plan may improve performance.
As for my experience, most of the CPU goes when executing non-optimized queries.
You may need to check if you are missing indexes by executing this query:
SELECT
    d.database_id,
    d.[object_id],
    d.index_handle,
	database_name = db_name(d.database_id),
    d.statement as fully_qualified_object,
    d.equality_columns,
    d.inequality_columns,
    d.included_columns,
	gs.user_seeks, 
	gs.avg_user_impact,
	gs.last_user_seek,
	gs.last_user_scan,
	total_columns_to_index = (select count(*) from sys.dm_db_missing_index_columns(d.index_handle)),
	command = 'CREATE INDEX IX_' + CAST(ABS(CHECKSUM(isnull(equality_columns, '') + isnull(inequality_columns, '') + isnull(included_columns, ''))) as varchar(100)) + ' ON ' + d.statement + '(' + isnull(equality_columns, '') + isnull(','+inequality_columns, '')  + ')' + isnull(' INCLUDE (' + included_columns + ')', '')
FROM
    sys.dm_db_missing_index_groups g 
LEFT OUTER JOIN 
    sys.dm_db_missing_index_group_stats gs on gs.group_handle = g.index_group_handle 
LEFT OUTER JOIN
    sys.dm_db_missing_index_details d on g.index_handle = d.index_handle

Open in new window



This is just to try... You may have other serious I/O issues, that are not connected to indexing problems.


Regards.
Best bang for the bucks to bring CPU down (and easiest in my opinion) is to maintain your databases by doing regular Re-index(you can do it on-line if you have Enterprise edition) and update Statistics.
Also I strongly recommend to install and use SQL own Performance Dashboards that has a pretty good Missing Indexes report and go through that, add missing expensive indexes and you'll see your CPU going down IF SQL Service is the one that consumes all or most of it:


http://blogs.technet.com/b/rob/archive/2009/02/18/performance-dashboard-reports-for-sql-server-2008.aspx
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/sql-server-performance-dashboard-reports-in-ssms-introduction-install-datediff-error-amp-modified-for-sql-2008.aspx
http://blogs.msdn.com/b/arvindsh/archive/2010/06/25/performance-dashboard-reports-in-sql-server-2008.aspx