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.
| 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 |
 |
|
|
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? |
|
|
|
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 requiredJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
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 |
|
|
|
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 |
|
|
|
|
|
|
|
|
CPU and IO v/s Response Time - sql 2008R2