Finding historic queries for a SQL connection


  • kills Dumbledore

    In my inherited software, there is an ongoing issue with SQL connections not being closed (a mixture of ADO.net, ADODB and probably at least one example of every method of connecting to SQL Server from a VB6 app ported to .net). We get unpredictable spikes in usage where some users have 3000+ connections open, killing the database performance and ruining performance for all other users.

    Best guess is that somewhere in the code there's some sort of loop that opens a connection, tries a query and then when it fails or doesn't get the results it wants it tries again with a new connection and doesn't close the existing one.

    We've tried to investigate by getting a log of what the last command for each open connection, but most of them are SET NOBROWSETABLE OFF, which appears to be cleanup that runs after the main command when using an ADO connection. This effectively removes the actual command from the connection.

    Is there a way to see all commands that have been run in a particular connection, instead of just the most recent? My google skills haven't turned anything up


  • Notification Spam Recipient

    @Jaloopa My first thought would be to maybe start a trace on some very high session id that would most likely only get created when the loop triggers. You should then be able to see all queries for that session.

    I hope I make sense


  • Garbage Person

    Nope. Not really.


  • Discourse touched me in a no-no place

    Is it possible to identify which clients (in software terms) are having the problem? Or is it not something that can be narrowed down like that?


  • kills Dumbledore

    @Vault_Dweller said in Finding historic queries for a SQL connection:

    My first thought would be to maybe start a trace on some very high session id that would most likely only get created when the loop triggers. You should then be able to see all queries for that session.

    Interesting idea. We've been hoping to avoid putting a trace on because performance is already so dire, but tracing a single id might be lightweight enough.

    @Weng said in Finding historic queries for a SQL connection:

    Nope. Not really

    Bugger. I was hoping I was just searching the wrong terms.

    @dkf said in Finding historic queries for a SQL connection:

    Is it possible to identify which clients (in software terms) are having the problem? Or is it not something that can be narrowed down like that?

    Each spike, we can identify which users have crazy numbers of connections. There doesn't seem to be a pattern and when asked what they were doing at the time they don't remember doing anything unusual. The current workaround is to ask those users to restart the application when we start getting tickets about it being slow.

    Ideally, we'd be looking to make sure all connections are closed after being used, or even implementing a single data access layer, but the software is being retired in about 6 months, so the priority is keeping it limping along until the replacement is ready, so it's a matter of putting a sticking plaster over the worst bugs as we identify them



  • There is a new feature in the SQL 2016 pipe for doing that (the Query Store), but I doubt that's going to help you very much at this point.

    Assuming your database is SQL 2005 or newer, you might be able to munge about in the sys.dm_exec_query_stats and related DMVs to get the information you need: http://stackoverflow.com/questions/3579092/tsql-get-last-queries-ran -- you'll probably also need to use the sp_who2 stored procedure to get the SPIDs of the relevant connections...


Log in to reply