Unfortunately our DBA is on a pilgramage back to China, so I am looking for an answer myself.
We use Spotlight and OpManager alerts to monitor SQL Server 2005 performance. We usually show about 150 users and 2000 transactions per second. Is there some way with Profiler or some other tool to determine what is originating those transactions? We use a single account for all applications with the app name included in the connection string, and the applications are on various application servers. Even being able to identify the originating app name or server or IP address would be very helpful.
The transactions are not causing any problems. BUT my manager put me in a position of tracking down any performance issues no matter the origin. Since there is an alert in OpManager that sends him an email when the main DB exceeds certain levels, he wanted me to find out if this warning about excessive transactions per second is something to worry about. As such, I began by trying to find out where those transactions originated.
Also, we have several third party monitoring tools for our webservers, communications between servers, and network traffic and the engineers for one of them commented that our number of transactions was ridiculously high for the work that our apps perform. So, that was the second indications to my manager that this was an excessive number of transactions and may indicate a problem. I will note that nothing else seems problematic, e.g. cpu average 20%, no locking problems, etc.
I am a developer and would normally ask the DBA to help with this, but she is away for 2 months.
Edit to add:
In Profiler to capture Transactions per second, which event should I pick to apply the app name filter to? I see lots of events under the TRANSACTIONS (i.e., SQLTransaction, Begin Tran Starting, etc.) node, but which one corresponds to the Transactions per second reported in OpManager?
Personally, I would be overjoyed, if our eCommerce databases were getting over 2000 transactions a second. The guys in the factories wouldn't care for it, but hey.
I have seen some applications do some very odd things. It could be that someone has coded a loop that re-queries the same value from the database on each pass through. If you run Profiler catching all 'Stored Procedure:RPC Completed" and "T-SQLQL Batch Completed", you can look for queries that are run seemingly too many times. Your server is not under such a load that this would be in any way risky, it seems. Get a trace of 10 or 20 minutes activity, then save that trace to a table on a dev server somewhere out of the way. Then you can do all sorts of analysis on it.