I am using Embarcadero DBArtisan to check the queries that users are running so that I can avoid blocks between them, or to investigate the exact query that caused the block. I know that the data about processes is gathered from master..sysprocesses but where can I get the actual query that a process is running without messing with the mda tables?
Actually I am trying to figure out the way that DBArtisan is doing, so that I can replicate it in a stored procedure for statistical use (gather with queries cause most blocks or take the longest so that they can be optimized).
I set up the mda tables because I actually need the output captured and inserted in a table for logging purposes.
My question now is weather it is safe to create an insert trigger for monProcessSQLText or if that will seriously affect the overall server performance (I want to log daily all the sql commands send by all users).