SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 CPU and IO v/s Response Time - sql 2008R2
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

poratips
Posting Yak Master

104 Posts

Posted - 09/12/2013 :  15:15:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2062 Posts

Posted - 09/12/2013 :  15:48:43  Show Profile  Visit jackv's Homepage  Reply with Quote
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

104 Posts

Posted - 09/12/2013 :  16:16:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2062 Posts

Posted - 09/14/2013 :  05:44:57  Show Profile  Visit jackv's Homepage  Reply with Quote
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

104 Posts

Posted - 09/16/2013 :  14:42:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2062 Posts

Posted - 09/23/2013 :  10:42:25  Show Profile  Visit jackv's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000