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
 CPU and IO v/s Response Time - sql 2008R2

Author  Topic 

poratips
Posting Yak Master

105 Posts

Posted - 2013-09-12 : 15:15:52
Hi,

I have a question regarding Query tuning that if it shows high CPU and IO intesive query but resposne time is ok so still nedds to be tuned or optimized?


Example:

While Runing tining script, if it shows Total_CPU_Time = 2094320785, MAx_CPU_Time = 684039, Execution counts = 94903 and total Logical Reads = 602159535 but query response time is ok then what will be the effect if i don't create the Index on filtered criteria or suggested index from the Execution plan or Tuning Advisor?

I have Index also ReBuilded and updated the stats.

I would liek to know any performance impact within a IO and COU intensive query but resposne time is ok.


Thanks.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-09-12 : 15:48:43
Taking a step back - look at the performance overall - think about the right questions to ask - such as http://www.sqlserver-dba.com/2013/02/sql-performance-tuning-asking-the-right-question.html . Is it a problem currently?

It is good to apply to "best practises" , but optimizing prematurely , can also cause problems later on.

The situation you've described may not be a problem currently , but with increased workload , it may be a bottleneck. Are you testing the queries under a normal workload?


Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

poratips
Posting Yak Master

105 Posts

Posted - 2013-09-12 : 16:16:49
Thanks Jack.
I m agreed and running query in a different time and work load but you are right that it might cause issue suring heavily peak time.

I am just trying to make sure that if query having high CPU and IO but resposne time is ok then i should avoit it?


Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-09-14 : 05:44:57
It is a good exercise to run the query and check the execution plan. Check for such things as:
1) Scans 2)to many rows returning

In other words, tune the query and see if you can make it be more efficient - while returning the recordset required

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

poratips
Posting Yak Master

105 Posts

Posted - 2013-09-16 : 14:42:30
ThanksJAck.

Row count is 5 - 10 records

This kind of query is executing frequently so i wanted to be pro-active before any bottleneck.

Query Cost: 100%


Index Seek on NonClustered Index on Idx_1with cost = 0%

Index Scan on NonClustered Index on IDX_2with cost = 55%

Clustered Index Scan on PK_FeeInv with Cost = 5%

Query:
SELECT "Col_A","Ex1"
FROM (SELECT "Col_A",MAX("ColB") "Ex1"
FROM (SELECT "T1"."InNumr" "Col_A","T1"."TxN" "ColB","T1"."Clnt" "C3"
FROM "FITx" "T1"
WHERE "T1"."Clnt"='MT') Q1,
(SELECT "T7"."TxN" "C6","T7"."TXcd" "C27","T7"."SDt" "ColB","T7"."Clnt" "C32","T7"."CANum" "C8","T7"."TxCd" "C4","T7"."Status" "C0",
"T7"."CFlg" "C31","T7"."EffDt" "Col1233"
FROM "CT" "T7"
WHERE "T7"."Clnt"='MT' AND ("T7"."TXcd"='FPINV' OR "T7"."TXcd"='FWROFF')) Qry1235
WHERE "C6"="ColB"
GROUP BY "Col_A") Qry36
ORDER BY "Col_A" ASC
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-09-23 : 10:42:25
Is the IDX_2 a multiple key non clustered index ? could you post the index definition ? with the keys in the correct order?
Are the statistics up to date on the index?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -