0

The websites (intranet sites or extranet sites - sometimes web portals) at my company return certain results (which is obtained via SQL queries/commands in the back-end systems). I"m trying to find out which queries are being run in the background and how I could track back the query results onto the tables where they come from. How can I achieve that? I tried looking at the "source" but found no queries there. Back-end uses SQL Server if that matters.

LearnByReading
  • 1,813
  • 4
  • 21
  • 43

2 Answers2

1

If you have an isolated environment (i.e. no one else can access the pages at the same time) set up and you have appropriate access to the SQL Server you could query the sys.dm_exec_query_stats table after opening each page to figure out what queries were run to get data for that page.

potatopeelings
  • 40,709
  • 7
  • 95
  • 119
  • 1
    Thank you for the answer, I'm looking into this now. Not sure why I got a negative vote.. this is a legit question... sigh – LearnByReading Aug 04 '15 at 15:35
  • 1
    You should be able to see the close flags - its actually a bit too broad (i.e. there are lots of different ways this can / cannot be done based on the specifics of the environment). As for the downvote, I wouldn't worry too much about it as long as you get an answer that helps you frame a more narrower question. – potatopeelings Aug 04 '15 at 15:38
  • 1
    Hmm.. I'm really not good with web technologies, all I see is an internal intranet that opens with IE. I'm surprised how I can't find out which query the back-end system is running in an easy manner . The method you had suggested did not work unfortunately because (as you had mentioned) so many people use the site and so many queries are run every minute.. – LearnByReading Aug 04 '15 at 15:43
  • try offhours maybe? Unfortunately unless you have access to the code and can navigate through that, it's a tad difficult. – potatopeelings Aug 04 '15 at 15:49
0

You could use SQL Server profiler. It will collect you all the statements that have been executed. It's a quite good tool for test environments or something with relatively little usage. For a heavily used production environment you might get too much results, and there is also a slight performance overhead from using it.

James Z
  • 12,209
  • 10
  • 24
  • 44
  • 1
    Hi James, thanks a lot for your answer. I had heard SQL Profiler can be used, but I cannot set it up... it doesn't seem it is possible to set it up unless you know the SQL commands for it... – LearnByReading Aug 11 '15 at 13:36
  • @LearnByReading It can look quite intimidating since there's so many options, but starting it is easy, it's in the tools menu in SSMS if I remember correctly. I would assume there's videos in youtube available how to use it. – James Z Aug 11 '15 at 13:52