SQL Profiler to identify the relationships between tables from the view of the app?

N2IT

Has anyone used SQL Profiler to look at the log of when a screen is updated? our data architects use it all the time but I was wondering if you think SQL Profiler is a good tool to use when looking at an update when an application is used.

Thanks

NotHackingYou

I think I understand your question. You want to use SQL Server Profiler to capture the SQL issued by an application to understand what tables are read/changed during a specific operation in the application. Likely so you can mimic the SQL calls to automate an otherwise manual task.

I have used profiler before to get a grip on what an application is doing. It can be very effective in this capacity. However, you tend to have a lot of data to filter through in a busy OLTP system. To answer the question on if it is a good tool to use or not is a little trickier. It can have a pronounced effect on performance so I tend to stay away from it in a production environment.

For both questions, it's best if you can profile a database on a test system that isn't doing anything but the operation you intend to sniff for. If you have to do it on production, try for a quiet time if at all possible. Always be ready to kill Profiler if you see an unacceptable drop in performance.

You can also determine dependencies for a table in SSMS. Right click on it and chose 'View Dependencies'. This can be quite telling when trying to understand a schema.

N2IT

I think I understand your question. You want to use SQL Server Profiler to capture the SQL issued by an application to understand what tables are read/changed during a specific operation in the application. Likely so you can mimic the SQL calls to automate an otherwise manual task.

This right here.

Sorry for the jacked up grammar I have 10000 things going on just like the rest of us. But this is precisely why I want to use profiler.

N2IT

Carl - I waited for production hours to drop and then ran profiler and immediately started the application. There is A LOT of information logging, but it helped build my level of confidence that a two part bulk update is possible on these tables. I don't have access to the back end code or I would of used the LINQ to map to the tables the application is accessing.

I have the VB editor installed on my machine. Is there a way to restrict access to TFS and the other source code repositories but allows access to the back end code and the SQL relationships?

NotHackingYou

I'm glad you got the data you were looking for. Often in large applications, we'll use separate SQL Server users for separate parts of the application if possible. One advantage to this is you can scope profiler down to a specific user in a specific database for specific operations. This may or may not work for you depending on your configuration.

Edited to add:
I also find myself using wireshark if the application goes directly to the database or uses a non-http API. I'll use Fiddler if an application uses an HTTP API. I do this quite often to better understand how an application is communicating.


I'm not sure what you mean by VB Editor - Do you mean Visual Studio? To clarify, by backend code you mean the vendor's application? If yes, generally source access is either all of a project or none. A complete application may consist of many projects.

It's a bit taboo to discuss with the vendor but various utilities exist to take the vendor's compiled application out of MSIL and back into legible code like C# or VB.NET. You can read the IL directly with ILDASM. ILDASM is really useful for troubleshooting things like what a service does on startup but I wouldn't probably try it for what you are doing. If your application is written in VB.NET, C#, F#, etc these utilities should work. I'm not sure how to do this if it's VB6

If I understand correctly, by relationships you mean primary key / foreign key relationships? If yes, these should be available by examining the schema manually or with the dependency viewer. If not, I'll need you to clarify a bit for me as I must be misunderstanding.

N2IT

Visual Studio 2013. The application is developed using C#.

I spoke to my boss about opening up that black box, but it's best to drop that with our development team. I have access to all the tables and after that it gets handed off to the development team. To be honest I am glad.

I am already designing/layering dimensional data (both informational and transactional). Along with developing high detailed (very complexed) SQL for reporting. Last thing I need was to start getting into the application side.

Thanks again for your thoughts.