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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

poratips
Posting Yak Master

105 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
2179 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

105 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
2179 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

105 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
2179 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  
 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.09 seconds. Powered By: Snitz Forums 2000