• 1. What are the MINIMUM events and fields I need to capture to prove that the heavy I/O usage is because of the requests coming from the application server itself.

    I would say if you are going to filter by hostname only: Database ID/Name, LoginName, Reads, Writes, Duration, SPID, Start Time, End Time

    I would use Clear Trace for analyzing this type of data. You can check their site here that includes a sample trace to use. Once you run the trace file through their tool it will give you a nice run down of top queries for CPU, IO, etc. If you are going to only capture for the period of time the heavy IO traffic is being seen you can catch which ones might be the culprit. If the file is fairly small in size you can also process it through their online system as well, if you don't have an instance to use for the local executable (requires an instance of SQL Server to process the trace file).

    2. Do we need to enable any performance counters to show nothing wrong at SQL Server end ?

    I am generally going to look at wait statistics for an instance to see what it is having to wait on. If there is anything hurting you will get your first look at it there most every time. From that information you can determine what areas might need more focus than anything.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton