Menu ▾ ▴

Home

Fabien Sabinet

I recommend to use SQLProcexp from a remote connection as I tried to put most of the work load on the client side (you have to allow remote admin connection with EXEC sp_configure 'remote admin connections', 1)

You can use SQLProcexp with a DAC and in that case I recommend to use the 1434 port for not being dependent of SQL Server Browser service. (SQL Server Express does not listen on the DAC port unless started with a trace flag 7806.)

Some parameters can be found in the config file that should be generated with the executable. If a parameters pose a problem you can delete the suspected one,
reset all of them with the menu or just delete the file, it will be rebuilded with default value.

When the capture check box is checked, automatic capture are triggered each 'capture time' in the option menu (space bar shortcut to toggle it). You can manually trigger a capture with the refresh button near the capture check box (return key shortcut).

In the sysprocesses mode the spid column get green for some second when a new process have been created, and red when have been ended. You can right click on a process to get different information about query or kill the process.

In the osWaitStats tab the critical wait line appear in red, and the ignored one (when 'show ignored wait' is checked in the option) appear in gray. Ignored wait are never used to calculate the counter except for the UserWaits 1 and 2 if included in the list (see the tool web page for how to set that in the config file).

The dm_exec_query_stats mode is experimental. It's made of sum(total_<counter>) group by query_hash, therefore it doesn't work on instance prior to 2008. Lower value in totals sum are only updated when there is deleted line (from plan) detected in sys.dm_exec_query_stats (so in this case we loose the 'hidden' potentially included increasing... Should not be often.) In this mode high value of line to show can heavily affect performance, and 100 seem to be a good value. You can see in purple the query that was multi-processed based on elapsed time lower than worker time (not perfect technic).</counter>

If you want to know how counter are calculated, you can use SQLProcexp to find the queries that it execute each 'capture' tick, it should be easy and a good exercise on how to use the tool :)

The default stress calibration query (found under the 'S' query) does not take account of physical read, you'll have adjust the query with a select * from any huge table on your instance for example (or simply backup a DB)

All usage are per second. Histogram step are per interval (in the menu) with max usage (per second) during that interval. You can click on the histogram to pin the tooltip and copy information. (Then unpin button or ESC).

The scale, what I call the 'Zoom', of the historical data is sadly not 'absolute', it depend mainly on the power of the machine, so it's adjustable. You can set it to any value (on the left of each scale there is a number which is that zoom, from 1 to 99) and also you can set it to 'Auto zoom' the
check box just above all zoom values that will auto scale each historical data for the maximum value to be 'red'. But be careful with that option, there are sometime some kind of 'wrong value', especially coming from sysProcesses (equal to the max value of an integer apparently) that put the CPU zoom
value to the minimum, so 1 and then you cannot see anymore the normal activity. In those case, in the option menu, you can set all zoom value to 'Default value' or set value to maximum (so 99) & auto Zoom. I recommend for each instance to search for some good 'average max' values and
not touching them anymore.

In the main list double click on a row show the corresponding statement in the 'D' (Default) query view. The 'E' tab is an emergency sample query, read only. Do not hesitate to tell me what to add in it if you have any idea...

I'm using sys.sysprocesses table because it's hard & cpu intensive to get rid of it (to get the most_recent_sql_handle dbid for example) I'll update SQLProcexp when Microsoft will include in the dm_views the stuff that the original genius put in sysprocesses.

Project Members: