While comparing query performance should i go for CPU values or Page Reads. On live server (with better hardware) a query is showing different CPU when i test it on my local machine. But Page Reads remains same.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Most systems are still I/O-bound, not CPU-bound. If you have a SQL Server system that is not running SSDs and CPU is trumping memory and/or I/O, there is probably a very serious problem. So I would focus on steering queries toward plans that optimize I/O.
You are not comparing two diff plans but you are comparing two diff servers and thus you can expect that time will surely vary.Even if you run the query on same server you wont get the same CPU time. Thus if you want to compare the time on two servers or two queries on same server try to execute the query say 100's of time and then use the avg CPU time.
Page reads are not based on the hardware but depends on your data and thus this counter will be same provided you have same data and indexes. I would suggest that you compare all 3 before deciding which is best query.But in most of cases reducing IO's will reduce others excpet few scenarios.
1.Page reads(both logical and disk reads). This is most important as reading a page is work done by CPU so if you reduce the IO then ultimately you will reduce workd done by CPU which in turn will reduce the CPU time as well as wait time.Also, you will be improving overall system perf as well e.g. Your query is doing say 10 IO's then it will use just 10 pages in memory or buffer but if it will use 1000 IO's then it will use 1000 pages in memory and thus will take extra 990 pages space in memory which could cause other useful pages to be thrown out of the memory and thus might cause physical IO's to read those pages.I assumed above that 10 and 1000 IOs were diff pages and not same pages.This comparison should be based on diff pages rather than number of IO's.
2.CPU Time.This could be quite high if you have CPU intensive tasks like calculations or sorts operations. (for parallel query it could be much more high than serial one and in that case direct comparison doesnt make much sense).
3.Wait Time.This could be high when you have too much IO's to do and some of them are coming from say disk (again this could be quite high in case of parallel and it doesnt reflect true wait times thus while comparing try to compare other waits).