Object: – SQLServer:SQL Statistics
Counter: – Batch Requests/Sec
Preferred Value: – > 300
Description: – The higher this number, the better. What it generally means is that your SQL Server can scale when needed. If you see peaks hit over 2000, then you are on your way to an optimized box. I have worked with servers that never got over 150. Then after changing a critical process like removing a scalar UDF from a computed column, or rewriting a critical process to not use looping, see this number hit over 500 to 2000.
Object: – SQLServer:SQL Statistics
Counter: – SQL Compilations/sec
Preferred Value: – < 10% of the number of Batch Requests / sec
Description: – Not a major indicator. I would not worry too much about it, however be aware of it.
Object: – SQLServer:SQL Statistics
Counter: – SQL Re-Compilations/sec
Preferred Value: – < 10% of the number of SQL Compilations/sec
Description: – No you don’t want recompilations, and the higher the number here, probably the more temp tables you use. Not much you can do about this except change code.
These three SQL Statistics counters provide information about how frequently SQL Server is compiling or recompiling an execution plan, in relation to the number of batches being executed against the server. The higher the number of SQL Compilations/sec in relation to the Batch Requests/sec, the more likely the SQL Server is experiencing an ad hoc workload that is not making optimal using of plan caching. The higher the number of SQL Re-Compilations/sec in relation to the Batch Requests/sec, the more likely it is that there is an inefficiency in the code design that is forcing a recompile of the code being executed in the SQL Server. In either case, investigation of the Plan Cache, as detailed in the next section, should identify why the server has to consistently compile execution plans for the workload.
Recompilation:
Once the plan is cached, there are situations when the optimizer feels something has changed and it has to compile/generate the query plan again, this is called Recompilation. Recompilation is generally caused when there is a change in schema , statistics change and execute the statements with recompile options…
There are various ways by which recompile can be captured. Easiest way is to capture profiler trace. There are two events available in profiler called “SP:Recompile” and “SQL:StmtRecompile”
Below is the query which can identify various possible reasons which is stored in profiler related catalog views. Note that both the above events have exactly same reasons:
SELECT sv.subclass_value,sv.subclass_name
FROM sys.trace_events AS e, sys.trace_subclass_values AS sv
WHERE e.trace_event_id = sv.trace_event_id
AND e.name = ‘SP:Recompile’
AND sv.subclass_value < 1000
ORDER BY sv.subclass_value;
Here is the output from this query:
subclass_value subclass_name
1 Schema changed
2 Statistics changed
3 Deferred compile
4 Set option change
5 Temp table changed
6 Remote rowset changed
7 For browse permissions changed
8 Query notification environment changed
9 PartitionView changed
10 Cursor options changed
11 Option (recompile) requested
12 Parameterized plan flushed
13 Test plan linearization
14 Plan affecting database version changed
These can be seen in the Profiler, under the “Event Subclass” column, whenever there is a recompile event.
selectstarfromyogi 