I need some help knowing what to look for in Profiler to troubleshoot an issue.
I've got an application that accesses a SQL Server database that has suddenly started timing out when users launch and attempt to log in, and I'm trying to find out where and why the application might be timing out (whether it's a server issue, a stored procedure or SQL query from the application that could be optimized, a table that could be truncated or archived, etc.). All I have to work with from troubleshooting the database side are a series of trace files from Profiler that were run for a total of about 5 minutes while the application was launched and then timed out. Of course, there are a whole lot of statements being issued, hundreds of tables being accessed, lots of stored procedures and even more ad-hoc queries coming straight from application source code.
So my question is, what do I need to look for in these trace files that might be a red flag to an issue? I'm no DBA, but I know that really long durations might be a tip-off. I'm only seeing these on the occasional Event:Audit Logout (which I read in another thread could potentially be very normal). Anything else that I might want to filter for?
Are the users actually able to launch the application (i.e. connect to SQL Server) and then the app times out when trying to do something in particular or are users not even able to log in?
You say that you have a lot of trace data from the 5 minute period, so my guess is that they are able to connect....although if you have not filtered by DatabaseName of DatabaseID, you are running Profiler traces against all the databases on that server.
I would do the following in a new trace:
Filter by the name or id of the database you are interested in
Select all events in the 'Errors and Warnings' category
Select 'Deadlocks and Lock Timeouts' in the Locks category
Save the trace to a table instead of a file - that allows you to run SQL queries against it later
Yes, the users are able to connect to SQL Server. The application actually runs from a Citrix Server, so all of the users are logging into SQL Server using a single log-in credential.
Thanks for your help; it was definitely much easier to search through the trace when I filtered the results down a lot more. I've found a few things that I'm going to investigate a little further. Thanks!