Statistics needs to be captured for query analysis What statistics are required to theoretically prove that X query work better than Y query. As per my knowledge I need to look for:

Query elapsed time
Logical read
Logical write
Physical read
Physical write

Do I need to have other statistics also to measure? Do I also need to measure

Memory pressure

TempDb space usage

Buffer usage

Query plan (Scan, Seek)
to get perfect analysis.


Ex scenario:
I have two queries one is using index and one without index. I need to show which query is performing well.
I assume you are talking about the same query, executing with different indexes.  You can't just arbitrarily compare 2 totally different queries that do 2 different things.  As Aaron stated in the comments, I can compare my 2001 VW to a 2015 Camaro and there will be no correlation, the Camaro may go faster but that's because it has a bigger engine.  Conversely, my VW will get better gas mileage because the engine is smaller.

That being said, I use the SET options: SET STATISTICS TIME, IO ON

Once those are on, the query will kick out all the CPU usage and reads that have been performed.  See below:



I then use www.statisticsparser.com to consolidate the data into an easy to read format.  That takes care of CPU/IO.



Execution plan can be pulled from the "Get Actual Execution plan" option in SSMS .  The execution plan will also have the memory grant (how much memory is allocated to the query).