Troubleshooting with SQL Profiler

One of the Payroll Specialists called us today, wondering if her session in Darwin was hung up and needed to be killed. Darwin, as I may have mentioned before, is a Microsoft Great Plains Dynamics product customized for the Professional Employer Organization (PEO) industry. We have SQL Server as the back-end.

The IT Manager asked me to put a trace on her session in SQL Profiler so that we could ascertain whether her session was indeed hung or not. As I saw the actions scrolling on the screen, I called the Payroll Specialist and told her whatever she was doing was still processing.

I began examining the items in the trace to see what was going on. For those that are unfamiliar, GP Dynamics hasn't named their tables, views, and stored procedures in a very intuitive way. Nevertheless, I have been able to deduce from their stored procedure naming convention the base tables involved.


In addition, this exercise has helped me learn and understand SQL programming better. I usually open the procedure as a script in a Create New SQL window. Inside these procedures that were being executed in the trace, I saw nothing more than a serious of SELECT statements. It looks as though Darwin (or really the code behind it) is getting information from the table in groups of 25 at a time.

This particular recordset has close to 5000 rows. Getting 25 rows at a time means that it will execute this stored procedure 200 times. If the program takes 5 seconds to execute each batch, then it will take about 17 minutes to go through the entire recordset. I guess it's no wonder that the process seems hung to the user.

I'm guessing that something is going on behind the scenes as Darwin is preparing and and posting a payroll. Unfortunately, I don't have access to the code behind it. This may be something we'll want to bring up with Thinkware, the vendor who provides this software to us.



-----
Check out my other blogs:
Daniel Johnson, Jr.
Get That Job!
Journey Inside My Mind Blog
Journey Inside My Mind Podcast
QuotesBlog
Twitter.com/danieljohnsonjr
Connect on LinkedIn
Interesting Things I've Read

Related tags: daniel+johnson+jr microsoft+great+plains sql+server sql+profiler peo