0

I need to retrieve all the database queries. I have asp.net running in IIS. I think we can do it using AOP but not sure how. I cannot make changes to asp.net code. I should retrieve all the queries that are sent to database. Also I need some way to retrieve all the queries independent of db type

Thank you in advance

user2967453
  • 31
  • 4
  • Hello, and welcome to Stack Overflow. Can you please clarify your question? Do you really want "all the database queries" - for a busy web application, that could be hundreds of thousands of queries. What do you want to do once you've retrieved them - write them to disk, view them in a log viewer? Is this on a live server? Have you looked at the SQL Server tools (https://learn.microsoft.com/en-us/sql/relational-databases/performance/performance-monitoring-and-tuning-tools?view=sql-server-2017)? – Neville Kuyt Aug 02 '19 at 08:26
  • You can use [SQL Server Profiler](https://learn.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-2017) tool to trace database quires. – Jalpa Panchal Aug 05 '19 at 08:17
  • @JalpaPanchal thank you for your response but in that case I would not be able to trace Oracle db or others. I need some way to trace any kind of database – user2967453 Aug 06 '19 at 00:32
  • @NevilleKuyt Thank you for your response. I will analyse and drop them based on user privileged and other reasons for security purpose – user2967453 Aug 06 '19 at 00:33
  • Please update your question with the additional information we're asking for in the comments - that way, people who want to help don't have to track the conversation. I still don't understand what "analyze and drop" means. – Neville Kuyt Aug 06 '19 at 09:00
  • @NevilleKuyt Thank you for your reply. I have updated the question. analyze and drop part is not that much important. The important part is how to retrieve all the queries sent to database. – user2967453 Aug 08 '19 at 00:25
  • could you please share what kind of framework you are using? if you want to achieve this requirement you could use network monitor and write some script. – Jalpa Panchal Aug 08 '19 at 06:19

1 Answers1

0

First things first: if you cannot modify ASP.Net code, you cannot realistically use AOP - it would be a huge security hole if you could inject functionality into an application in this way.

Secondly, even a moderately busy web application will be generating a lot of database queries. It's not uncommon for a single page request to require dozens of database queries (many hopefully cached!), and for a single server to handle dozens or hundreds of requests per second; 100s of database requests per second is not unusual.

Thirdly, anything you do that slows down those database requests will almost certainly slow down the web application, possibly to the point where it will seem broken.

So, what can you do?

If you can modify the ASP.Net code, you could follow the advice in this question. It's trivial to implement, and is by far the cleanest option.

Pretty much every RDBMS engine has a "tuning" solution, which allows you to look at (live) queries against a database. These tools are optimized for tuning, and therefore unlikely to affect performance of the web application.

You could sniff the network traffic between the database and the server. Wireshark supports several database formats.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • thank you very much for your detailed reply. In case of Java and Tomcat by using AOP I am eligible to get all the query and session details by simply connecting JDBC calls to AOP. So in that case I dont need to update any code there. I am looking for similar way for asp.net. – user2967453 Aug 09 '19 at 01:20