CPU Utilization is high - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CPU Utilization is high

Author

Topic

visa123
Yak Posting Veteran

54 Posts

Posted - 2012-04-17 : 06:01:19

Currently our SQL production server handling 809440 pages, which required 6.5GB memory.

Clean master 115
Dirty master 3

Clean testdb1 18723
Dirty testdb1 1018

Clean testdb2 13743
Dirty testdb2 1952

Our IT team Checked and send the above.They informed "CPU Utilization is high"

But some of the db we don't have any functionality [ Dotnetnuke Site]

Please guide us the above scenario.

Visa.G

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-17 : 07:49:32

What have you done to the Master database, if there are 3 dirty pages?

N 56°04'39.26"
E 12°55'05.63"

visa123
Yak Posting Veteran

54 Posts

Posted - 2012-04-17 : 08:32:23

Thanks for your reply.

Yes. We run wrongly Some sp's and Tables for other db in master db.

Now we removed.Same like we will check other db's also.

But please suggest any other scenario have to check for this

Visa.G

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-17 : 09:51:21

Well, to be honest with you; CPU utilization has almost nothing to do with the number of pages in the database.
There is no correlation.

CPU utilization occurs when calculations or scans are made over the table.

N 56°04'39.26"
E 12°55'05.63"

visa123
Yak Posting Veteran

54 Posts

Posted - 2012-04-18 : 02:37:50

Sorry I have mentioned CPU utilization instead of memory. we are having server with 7 GB memory, which one handling around 20 sites. Due to this SQL page handling, our server memory usage reach around 6.GB, which is 90% of total memory.

We need to control the memory utilization, it is handling 809440 pages, but most of sites are not having any functionality. can you kindly suggest me the solution to solve this issue.

8KB_Pages Pages_in_KB Pages_in_MB

-------------------- --------------------------------------- ---------------------------------------

809440 6475520.000000 6323.750000000

Visa.G

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-18 : 03:31:48

No. You're wrong. SQL Server does NOT put all pages in memory by default.
The pages that are stored in the Buffer Pool are the pages recently or heavily in use. And that may be only some 1000 pages.

Unless you have a badly written application that forces a scan over several tables. That would flush the Buffer Pool over and over again to keep the new pages.

To get you into perspective... I have a datawarehouse that is 200GB in size (~26,000,000 pages) and the query response time is averaging at 0.8 seconds. The CPU never raises above 11%.

N 56°04'39.26"
E 12°55'05.63"

visa123
Yak Posting Veteran

54 Posts

Posted - 2012-04-19 : 01:52:07

Thanks for your reply.

To make sure this am going to use the below Queries to check the badly written application tables in db's.

Please guide me is it a right path to do.

1. DB Wise :-

SELECT count(*)AS cached_pages_count
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC

2.DB + Table Wise :-

SELECT count(*)AS cached_pages_count
,name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id
ORDER BY cached_pages_count DESC

Is any other way to do this , Please let me know.

Visa.G

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-19 : 15:20:03

I have no clue what you are trying to do.

1. Pages are a measure of storage need for all the data in your database. It has nothing to do with performance.
2. CPU utilization is a measure of pressure on your system.
3. High CPU can be caused by
* Badly written queries
* Scans
* Massive aggregations
* Lot of sorting
* Missing indexes and presence of key lookups

N 56°04'39.26"
E 12°55'05.63"

visa123
Yak Posting Veteran

54 Posts

Posted - 2012-04-20 : 02:27:17

Thank you very much for your reply and inputs.

Visa.G

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources