SQL Perfromance - 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 Administration

SQL Perfromance

Author

Topic

syam_pydimarri86
Starting Member

1 Post

Posted - 2012-05-04 : 03:13:41

Hi,

I am using SQL server 2008R2 STD edition and my server configuration is win 2008R2 STD Edition 64bit and Dual core Processor with 8 GB of RAM, but recently i noticed that my CPU utilization is almost 70% and Physical memory utilization is also around 6 GB, so can any one suggest how to do minimize both CPU and Physical memory utilization and do performance tuning

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-05-04 : 07:49:26

It depends on the kind of performance tuning you have done before, there are tons of things you can do. The one with the by far biggest potential is to do performance tuning on the queries that are running.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-05-05 : 05:18:45

It is not unusual for CPU to be 70% usage or 6GB memory. What settings have you on the SQL Server memory config? Are you seeing memory pressure ? Are you seeing any query timeouts from application?
Identify queries with large resource usage and tune.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Sachin.Nand

2937 Posts

Posted - 2012-05-05 : 09:38:21

CPU with 70% usage not unusual ??? My heart starts throbbing if the average CPU usage goes beyond 30%.

Is 70% usage same across all the CPU cores or for a particular core ?

After Monday and Tuesday even the calendar says W T F ....

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-05-05 : 14:43:40

Sachin - the initial call did not mention "average" CPU.
Is the high CPU sporadic or continious?
A good way to find queries with high CPU usage is the sys.dm_exec_query_stats DMV, capture the top few and analyse the execution plan

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Sachin.Nand

2937 Posts

Posted - 2012-05-05 : 15:05:10

Irrespective of whether its average or not. 70% CPU usage can never be "not unusual".

After Monday and Tuesday even the calendar says W T F ....

m.esteghamat
Starting Member

47 Posts

Posted - 2012-05-09 : 08:03:28

Hi
If you can, when you have 70% busy, (If u can !!!) Stop services from down to top .
for example : First stop agent!
and last stop sqlserver service and check cpu usage. and start from top to down . and check again.
check after start services, when cpu usage, go up !!!!
if immediate After start go to 70%, dont go to check query , ... . you should check configuration.
bye

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-05-09 : 09:24:42

quote:
Originally posted by m.esteghamat

Hi
If you can, when you have 70% busy, (If u can !!!) Stop services from down to top .
for example : First stop agent!
and last stop sqlserver service and check cpu usage. and start from top to down . and check again.
check after start services, when cpu usage, go up !!!!
if immediate After start go to 70%, dont go to check query , ... . you should check configuration.
bye

Are you insane?? This is by far the worst advice I have read in a very long time.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-05-09 : 14:51:32

As Lumbag - said in the first post - first look at queries - and see which ones are creating CPU pressure

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

m.esteghamat
Starting Member

47 Posts

Posted - 2012-05-10 : 04:36:44

hey syam
if you like a practical solution, and have a good apportionity, take my solution.
and lumbago , you are insane. beacase a solution should match the condition. this is worst for you not for another.

Sachin.Nand

2937 Posts

Posted - 2012-05-10 : 13:30:31

quote:
Originally posted by m.esteghamat

....and lumbago , you are insane. beacase a solution should match the condition. this is worst for you not for another.

Add me also in your list of insane as your solution is way to worst for me to..

After Monday and Tuesday even the calendar says W T F ....

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-05-12 : 04:50:59

if this is a Production site - and the users response is acceptable- start with a method where the instance does not have to be restarted.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

ovc
Starting Member

35 Posts

Posted - 2012-05-19 : 11:38:01

Hi all,

first of all it is not 100% sure that this counters are coming from SQL Server, or is it? If the OS is Windows Server 2008 R2 be sure that you have first SP1 installed. I had a lot of issues which were solved by some issues fixed in Win SP1.

Is there of the high CPU Percentage kernel times? If yes the bottleneck might be the discs.

From your symptoms it point ti high IO, memory usage and CPU might caused by a lot of index scans.

For monitoring exact how much SQL Server is running use DBCC MEMORYSTATUS.

You can also use the following scripts to find out top 10 CPU consuming queries:
################################
SELECT TOP 10 query_plan_hash, query_hash,
COUNT (distinct query_plan_hash) as 'distinct query_plan_hash count',
sum(execution_count) as 'execution_count',
sum(total_worker_time) as 'total_worker_time',
SUM(total_elapsed_time) as 'total_elapsed_time',
SUM (total_logical_reads) as 'total_logical_reads',
max(REPLACE (REPLACE (SUBSTRING (st.[text], qs.statement_start_offset/2 + 1,
CASE WHEN qs.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), st.[text]))
ELSE qs.statement_end_offset/2 - qs.statement_start_offset/2 + 1
END), CHAR(13), ' '), CHAR(10), ' ')) AS sample_statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
group by query_plan_hash, query_hash
ORDER BY sum(total_worker_time) DESC;
go

############################################
-and top queries who produce a lot of reads.

#############################################
print '-- top 10 logical reads by query_plan_hash and query_hash --'
SELECT TOP 10 query_plan_hash, query_hash, sum(execution_count) as 'execution_count',
sum(total_worker_time) as 'total_worker_time',
SUM(total_elapsed_time) as 'total_elapsed_time',
SUM (total_logical_reads) as 'total_logical_reads',
max(REPLACE (REPLACE (SUBSTRING (st.[text], qs.statement_start_offset/2 + 1,
CASE WHEN qs.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), st.[text]))
ELSE qs.statement_end_offset/2 - qs.statement_start_offset/2 + 1
END), CHAR(13), ' '), CHAR(10), ' ')) AS sample_statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
group by query_plan_hash, query_hash
ORDER BY sum(total_logical_reads) DESC;

go

################################
If you find in the last output a lot of queries which provide a lot of scans, creating indexes might solve your issues.

I hope this helps.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-05-19 : 12:22:23

syam_pydimarri86, do you have high CPU wait time. In other words - is your CPU spending high percentage waiting for a response?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Ibid
Starting Member

4 Posts

Posted - 2012-05-20 : 04:58:00

I agree that you should start with understanding what's running on your SQL Server, not killing off processes.

If you want to see a breakdown of which queries are consuming the most resources (I/O, CPU, etc.) there's a great free tool for analyzing trace files that you might find useful [url]http://www.dbsophic.com/qure-analyzer.html[/url].

m.esteghamat
Starting Member

47 Posts

Posted - 2012-05-21 : 08:26:06

Hi ovc
I Run your script.
I get 2 error :
Msg 207, Level 16, State 1, Line 13
Invalid column name 'query_plan_hash'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'query_hash'.
please help me.
thank you.

ovc
Starting Member

35 Posts

Posted - 2012-05-21 : 11:27:11

Hi m.esteghamat

those columns exists from SQL 2008 upwards. I assume you tried it with an older SQL Server Version...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources