Thursday, September 26, 2013

Dynamics GP SQL Server Composite Primary Key Trivia

By Steve Endow

Coincidentally, after working on a really interesting Dynamics GP SQL Server performance issue earlier this week, I will be having a call tomorrow with another GP partner and GP customer to discuss a different Dynamics GP SQL performance issue.

This time, it involves an eCommerce web site that is trying to stay synchronized with Dynamics GP.  I don't have much information on the integration at the moment, but I do have a list of requests from the web developers.  They are asking that several indexes be added to Dynamics GP.

When I reviewed their request for new indexes, I looked at the GP tables to see what indexes already existed on the tables.

One example is that they requested an index for CUSTNMBR on the SOP10107 table.  The SOP10107 table has a composite primary key (a key consisting of multiple fields), but does not have any additional indexes (at least not on my GP 2010 TWO database).

So the developers are apparently asking for an index on CUSTNMBR, the first field in SOP10107.

But CUSTNMBR is one of the fields in the primary key.  Doesn't SQL Server use the primary as an index?  I didn't actually know and had to do some research.

The answer is one of my favorites:  It depends.

Apparently, if you query the first field in a composite primary key, or all fields in the composite primary key, the primary key can be used as an index.  But if you query the second or subsequent fields in the key, then the primary key cannot be used as an index.

I need to track down the MSDN or TechNet documentation to back this up, but I initially found it explained in two threads on StackOverflow:

I haven't researched the specific mechanics of the primary key in SQL Server to understand exactly why this limitation exists, but I have speculated about the reason and can understand why it might be the case.

So there you go.  Now you can talk fancy about SQL Server composite primary keys at your next social gathering!  You will be the life of the party.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

Wednesday, September 25, 2013

A most EPIC tale of troubleshooting a Dynamics GP SQL performance issue

By Steve Endow

Today was a very good day.  After hours of effort by a team of three consultants, we solved a vexing Dynamics GP SQL performance issue for a client.

I'll start with the conclusion and explain why this was such a great achievement.

1. We actually identified the cause of the performance problem.  For those that have tried to identify the cause of GP running slowly, locking up, or not responding, you know how difficult this can be.  Working together, our team of 3 was able to conclusively track down the specific SQL stored procedure that was causing the problem.  This was quite an accomplishment.

2. It took the combined effort of three consultants with three different areas of expertise and a lot of combined Dynamics GP, networking, and SQL Server experience to identify the cause.  In my experience, it is very, very, rare to have the opportunity to assemble such a team to work on this type of issue, and it was a great experience for all of us.

3. The client was willing to invest in consultants and made the commitment to identify and resolve the issue.  So often I've had situations where a client was plagued with performance issues, but there wan't a strong commitment to resolve them.  The client will likely spend several thousand dollars resolving this particular issue, but I believe it was a worthwhile investment--or I will once we finally resolve the issue.

And with that, some background...

The Dynamics GP 2010 client contacted their partner in the San Francisco Bay Area a few months ago complaining about performance problems with Dynamics GP.  GP is sporadically slow, and sometimes hangs or stops responding completely.  The "lock up" can last from 5 minutes to 20 minutes and affects multiple users at different times.

The GP partner looks into it, but because the issue is so unpredictable and can't be reproduced on demand, no cause is identified in Dynamics GP or SQL.  Apparently the problem continues and gets even worse.  At this point, the client requests a network expert to try and determine if a network issue is causing the problem.  The GP partner identifies a networking data communications consultant in Denver and flies him out to San Francisco this week.

After two days of network traffic monitoring and traces, the network consultant, working with the GP consultant, is able to capture the actual network packets that are occurring in the seconds leading up to the performance issues in Dynamics GP.  Like a countdown, he is able to list the users, the actions they were performing in different applications, and the data in the network packets that were being issued by those users at the exact time that the problem occurred.

This data indicated that a revenue recognition application used by the client was issuing queries against the Dynamics GP database.  He assembled the contents of the network packets, and saw that the "rev rec" application was querying the Extender "EXT" tables in Dynamics GP.  When implementing Dynamics GP and the revenue recognition system, the client chose to store additional sales order invoice field values in Extender windows that could be used to perform rev rec calculations.  So this all makes sense.

Since the network consultant had uncovered a pile of SQL, the Dynamics GP consultant called me to decipher the SQL activity and determine how it might relate to the performance issue.  Coincidentally, my blog-mate Christina Phillips and I just presented last week at the Tech Airlift 2013 conference in Fargo, and the topic of our presentation was "SQL Skills for Consultants".  While I am by no means a super-SQL-guru, I regularly get calls like this to solve some tricky problems, so I have had a fair amount of experience in this area.

So today I had a few GoToMeeting sessions with the GP consultant and network consultant where we pored over the SQL activity and dug into the Dynamics GP company database to understand what was going on.

We saw that the rev rec application issued a select statement against the EXT00100 table, and then called a custom stored procedure.  Immediately after that stored procedure is called, Dynamics GP locks up for one user.  A few seconds later, GP locks up for another user, and then another.  So the network consultant had identified our culprit:  a custom stored procedure.

After reviewing the network trace data, the GP consultant knew the specific actions that were being performed in the rev rec system and in Dynamics GP, so she was able to get the users to reproduce the issue.  Suspecting that we had a locking issue on our hands, I sent her a slightly modified version of the "dynamic management view" query from this Stack Overflow thread that identifies blocked and blocking processes in SQL Server.

t2.blocking_session_id, 'object name',
o1.type_desc 'object descr',
p1.partition_id 'partition id',
p1.rows 'partition/page rows',
a1.type_desc 'index descr',
a1.container_id 'index/page container_id'
FROM sys.dm_tran_locks as t1
LEFT OUTER JOIN sys.dm_os_waiting_tasks as t2
      ON t1.lock_owner_address = t2.resource_address
LEFT OUTER JOIN sys.objects o1 on o1.object_id = t1.resource_associated_entity_id
LEFT OUTER JOIN sys.partitions p1 on p1.hobt_id = t1.resource_associated_entity_id
LEFT OUTER JOIN sys.allocation_units a1 on a1.allocation_unit_id = t1.resource_associated_entity_id
LEFT OUTER JOIN sys.dm_exec_sessions s1 ON s1.session_id = t1.request_session_id

When she sent me the results of the query, it painted a great picture of the problem.  Here is a screen shot of the query results--I know it looks busy, but stay with me, it's pretty simple to interpret.

Notice that the highlighted row is the only one with a value in the "blocking" column.  That means that an application is trying to perform an operation on the EXT00100 table, but is being blocked by another process.  Notice that the "request_mode" column on that row says "IX".  This means that the row in yellow is waiting for an "exclusive" lock on the table, in this case because it is trying to update the EXT00100 table.

When a SQL process is blocked, it typically causes the calling application to wait--it will often just sit there and be completely unresponsive as it waits for the SQL command to finish.  The SQL operation usually finishes in a fraction of a second, but if the process is blocked, it may wait for a minute, or perhaps indefinitely, until the operation completes.

The Dynamics GP consultant knew that particular yellow row was a Dynamics GP user--the user reported that GP was locked up.  So this told us that something was locking the EXT00100 table, and that is why Dynamics GP was locking up--it was waiting indefinitely for the table to become available for an update.

Which leads us to the records for process 178.  178 is the process that is blocking the update to EXT00100.  But why is it blocking?  Well, if you look at the "request_mode" values, most have a value of "S", which means "shared lock".  Shared locks allow other processes to read the locked data, but the data cannot be changed or updated while the shared lock is present.  So process 178 appears to have issued SELECT queries against the EXT tables, with the default shared lock, that are preventing Dynamics GP from updating those tables.

Fundamentally, this isn't a problem--a query can retrieve data from the EXT tables, complete in a fraction of a second, and then the shared lock is released.  But for some reason, the shared lock of process 178 persisted for up to 20 minutes at a time.  During that time, Dynamics GP would become unresponsive if a user tried to update any Extender records.

So, now that we know why GP is locking up, the next question is: Why is a mysterious query against the EXT tables locking them for 20 minutes?  For that, we go back to the network packet trace, and find packet 33823 documented by the network consultant:

Packet 33823 – hq-app1 issues a SQL command to run a Stored Procedure on the SQL Server 

Exec CORP..spREVREC_EXTENDER 'CORP\username', '2013-09-24 03:35:30:35', 'EXT_REVREC'

Aha, now we are getting to the root of the problem.  There is a custom stored procedure called "spREVREC_EXTENDER" that is being called--this procedure is what is locking the tables under process 178.  We deduce that this custom stored procedure was developed by the revenue recognition software vendor to extract data from Dynamics GP for analysis.

When then jump back to SQL Server Management Studio and pull up that custom stored procedure so that we can view its contents. is encrypted.  D'oh!

Never fear, as I have a tool that can decrypt stored procedures with a single click.  After some file transfer acrobatics, we finally get a copy of the customer database onto my development machine so that I can use the decryption tool.  And voilĂ , we can now see the contents of the stored procedure.  And it is a doozie.  It is 160 lines with 2 derived tables and 12 joins.  (Attendees of our SQL Skills for Consultants presentation at the Tech Airlift would know that a good substitute for a derived table is a CTE, and that CTEs can be helpful in improving query performance!)

So now it is crystal clear what is happening.  The stored procedure, with its 2 derived tables and 12 joins, is taking forever to run.  It was developed against a brand new Dynamics GP company database that only had a few records--of course it ran fine back then.  Now that the customer has 100,000 records in the SOP tables and each of the Extender tables, it takes anywhere from 5 to 20 minutes to run.  It's a classic example of how it is often impossible to predict future performance of a complex query, and why SQL query optimization is important.

But that's not all.  It isn't just that it has 12 joins--it's the particular table that it is joining and the fields it is using in the join.  This is just a small snippet of the query--to anyone familiar with Extender tables, it should be fairly obvious that this isn't going to end well.

from EXT00100 e
INNER JOIN EXT00102 d1 ON d1.PT_Window_ID = e.PT_Window_ID and d1.PT_UD_Key = e.PT_UD_Key and d1.PT_UD_Number = 1 
INNER JOIN EXT00102 d2 ON d2.PT_Window_ID = e.PT_Window_ID and d2.PT_UD_Key = e.PT_UD_Key and d2.PT_UD_Number = 2
INNER JOIN EXT00103 t1 ON t1.PT_Window_ID = e.PT_Window_ID and t1.PT_UD_Key = e.PT_UD_Key and t1.PT_UD_Number = 3
INNER JOIN EXT00101 s1 ON s1.PT_Window_ID = e.PT_Window_ID and s1.PT_UD_Key = e.PT_UD_Key and s1.PT_UD_Number = 6
INNER JOIN EXT00101 s2 ON s2.PT_Window_ID = e.PT_Window_ID and s2.PT_UD_Key = e.PT_UD_Key and s2.PT_UD_Number = 7
INNER JOIN EXT00103 t2 ON t2.PT_Window_ID = e.PT_Window_ID and t2.PT_UD_Key = e.PT_UD_Key and t2.PT_UD_Number = 4
ON m.PT_Window_ID = t2.PT_Window_ID 
and m.Field_Number = t2.PT_UD_Number 
INNER JOIN EXT00103 t3 ON t1.PT_Window_ID = e.PT_Window_ID and t3.PT_UD_Key = e.PT_UD_Key and t3.PT_UD_Number = 5
ON h.SOPNUMBE = rtrim(e.Key_Strings_2) 
-- and h.SOPTYPE = convert(INT,e.Key_Strings_3)-1
AND a.LNITMSEQ = convert(INT,e.Key_Strings_1)
and b.LNITMSEQ = convert(INT,e.Key_Strings_1)
and b.DELETE_FL = 0
and b.VOID_FL = 0

I haven't yet dug into the query to determine the specific causes of the performance issues, but one leading candidate is the join with "rtrim(e.Key_Strings_2)".  That is a join using a function on a non-indexed field.  I sense trouble ahead.

At this point, we have identified the cause of the GP lockups and know that we need to have the rev rec vendor modify the custom stored procedure.

I'm guessing that a quick fix would be to simply add the "WITH (NOLOCK)" table hint to the query.  In theory, this should eliminate the shared lock and allow GP to update the EXT tables without issue.  But that would just be covering up the underlying problem--the stored procedure would still take 5 to 20 minutes to run.  And using NOLOCK on a query that runs that long is probably a bad idea.

So we will likely need to work with the rev rec vendor to completely restructure the query so that it runs much faster.  Preferably in just a few seconds.

Despite the work ahead, we felt pretty good by 5pm today, as we were confident that we had found the cause of the client's performance issue, and knew that it could be fixed.

It was a good day.

Postscript:  Some of you may know that with GP 2013, eOne completely redesigned the table structures for Extender.  The tables have different names, and work differently than the prior Extender tables.  So, when this client upgrades to GP 2013, the revenue recognition stored procedure will have to be redesigned yet again to query data from the new 2013 Extender tables.

For more information on different types of SQL locks, here is a good TechNet article on SQL locking:

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

How NOT to develop an eConnect integration: Lesson #29

By Steve Endow

I've come across some really bad Dynamics GP integrations.  I had one where the developer made a really pretty user interface with their own company logo and fancy controls and dynamic layout, but the import simply didn't work and the developer didn't know the difference between a debit and credit.

Then I saw another one where the developer wrote their own massive stored procedures to import GL and AP transactions into GP tables (I'm hoping that was done before eConnect existed).  Scary stuff.

I got a call last week about an old integration that stopped working when a customer tried to set it up on a new Windows 7 x64 machine.  The integration fails with two errors.  The first appears to indicate that it encountered an error when it tried to read the source data file.  And the second error says that the Crystal Reports runtime is not installed correctly.

Say what?

I got a copy of the .NET executable and decompiled it to try and find some clues.  I found the section of code that is failing when reading the data file, but it isn't obvious why it is failing--the developer didn't bother to output any error messages in their error handling code, just a useless "There were errors loading data" message.

So then I move on and look for the Crystal Reports reference.  Sure enough, the application relies on Crystal Reports version 10.2.3600.  For those of you with long memories, Crystal Reports 10 is from around 2004.  I have a copy of it, but my version isn't 10.2.3600, so I'm guessing it won't help.  So now we're stuck digging around an old machine to try and find the right Crystal DLLs and see if we can get the import working again, and worrying that there may be some compatibility issue with Windows.

So why does a GP integration use Crystal Reports?  Apparently the developer thought it would be super cool if he used Crystal Reports to display errors.  No joke.  So if the import has an error, the application launches a Crystal Reports runtime window to display the a Crystal Report.

So in a nice paradox, the application is encountering an error, and it then tries to display the Crystal Report to present the error, but Crystal Reports isn't working, so we get another error, and we are unable to see what the first error is.

They could have gone with a dialog box. They could have used a text box.  They could have written to a text log file.  They could have sent an email.  All of those options would have been much simpler, easier, and worked just fine.  But no, they had to get all fancy and bill the client a few more hours to build a Crystal Report just to display errors.  Face palm.  And now that 2004 version of Crystal Reports is ancient and is preventing the app from working on a newer version of Windows.

If you are developing an eConnect integration, or any simple business application for that matter, please just keep it simple.  There is no need to Bedazzle a basic application like a data import.  And when the application lasts for years and years and needs to be upgraded as the client upgrades GP, the simple application will be easier to upgrade, maintain, and support.

The developer who wrote this integration with Crystal Reports did not impress anyone, yet left a legacy of hassle and frustration due to a poor design choice.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

Sunday, September 22, 2013

Want to use cloud computing? Make sure and have an exit strategy!

By Steve Endow

I had lunch with a friend today who manages internal IT systems for a large film and television media company.  He said that he was swamped at work because of an unexpected business crisis:  one of his company's cloud storage service providers is going out of business and he has less than 30 days to try and transfer all of the critical data stored with the provider.  He was not involved in the selection or setup with this provider, but is now having to figure out how to save all of the data.

The fun part:  His company has over 160 TERABYTES of data stored online with this provider.  (It is believed that other customers have many PETABYTES of data stored with this provider, so relatively, 160 TB isn't nearly as bad.)

The provider uses "object storage", so there is no simple way to request all of the files and have them transferred to physical drives.  And the data is encrypted, so even if he is able to move all of the data somewhere else, he will likely have to purchase the same file encryption appliance to ensure that the file encryption and decryption processes work the same.

Despite having a gigabit network connection to the cloud storage service, he's finding that it is taking too long to download the data.  And his company doesn't exactly have an empty 160 TB enterprise grade storage device laying around.  Given the crisis, he's having to simultaneously explore three options to try and save the data.  He explained they are trying to download the data to their corporate network, but it is too slow, so that doesn't look too promising.  So they are also simultaneously building a new storage colocation setup in the same data center as the cloud storage service to try and utilize the internal 10GB connectivity in the building.  But to do that, they are having to borrow a storage device and encryption appliance from EMC--since they can't purchase one quickly enough.  And the third option is to get setup with an alternate storage provider, such as Amazon S3.  If you have a ton of data on a storage device, apparently you can physically ship the storage devices to an Amazon data center and they can import the data directly to their systems rather than trying to upload it via the Internet.

Given his limited time window, he may have to use all three approaches simultaneously.  Oh, and don't forget that his business still needs to function--they still need a place to store the gigabytes of new data that are constantly being generated while this crisis unfolds.

After learning more about the "cloud" industry and attending several conferences, he shared a key lesson that he picked up from a Gartner Research study:  If you plan on adopting cloud computing, make sure to have an exit strategy.

If you backup or store data to the cloud, how can you retrieve ALL of that data if necessary?  If you have hosted ERP or CRM, how would you handle a complete shutdown of that service provider?  Do you have a plan?  Do you have multiple options?  How long will it take to execute the plan?

If you use hosted Dynamics GP, it could be as simple as getting a copy of your SQL Server database backups and a backup of your hosted Dynamics GP application directory.  Assuming the hosting provider allows you to access or receive copies of such backups regularly.  And assuming you know the process and are ready to turn those database backups into a fully functioning Dynamics GP environment using either internal servers or a new hosting provider.  How long would it take to implement such a plan?

What about hosted CRM?  What about hosted email?  How about your web site?

"Cloud computing" has come a long way in the last 10 years, but it still has some challenges that require prudence and diligence.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

Friday, September 20, 2013

Another Great Dynamics GP Technical Airlift, in Scenic Fargo!

By Steve Endow

This week Christina and I attended the Technical Airlift 2013 in lovely Fargo.  The weather mostly behaved, but varied from 80 degrees and foggy to 45 degrees with some rain.

The conference was great, with reviews of the features and enhancements added with GP 2013 SP2, some overviews of upcoming functionality, and even a few sneak peaks of technology improvements that are potentially a few years away.

Christina and I had a very successful presentation, "SQL Skills for Consultants".  We had a full room, a great audience, lots of nifty T-SQL tips and tricks, and some very good questions throughout the presentation.  Several Starbucks gifts cards were awarded to the audience for their tips and questions, and there were even a few hecklers who found a few typos in our SQL examples.  ;-)   The room full of partners and consultants kept us on our toes!

I'll try and write a few follow-up post to answer some questions that we had during the presentation.

It was great to meet several new people, catch up with old friends, and learn about the impressive new features and enhancements that are being added to Dynamics GP.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

Friday, September 6, 2013

Technical Airlift Here We Come! SQL Tips and Tricks

Hopefully we will see some of our ardent followers at the upcoming Microsoft Technical Airlift for Dynamics GP in Fargo, ND later this month. Steve and I are presenting a session on SQL tips and tricks for consultants. Definitely not a developer-oriented session, we hope to help front line consultants and support folks build our their SQL toolboxes to up their game in terms of queries, reporting, and data fixing.

 It has been fun working on the presentation, and I thought I would share some of the fun things we have come across in a few posts over the next couple of weeks. Here are a couple things from the Cool SQL Keywords section of the presentation...

Much like a middle child, BETWEEN is often forgotten although it saves us precious keystrokes and is so simple to understand. It can be used in place of using ">=" and "<=" in a WHERE clause.

For example,
 "select * from RM00103 where CUSTBLNC >=10000 and CUSTBLNC <=100000"
"select * from RM00103 where CUSTBLNC BETWEEN 10000 and 100000"

So clear cut, right?

Now, the second on we chatted about a bit was UNION vs UNION ALL. Now, the little know fact is that UNION actually causes duplicates to be eliminated. In reality, we use UNION a lot to marry up open, history, and maybe work tables. So we wouldn't expect to have any duplicates, right? But it is worthwhile to know this, as it may cause an issue if you are data fixing and resolving duplicate issues...or don't realize you have some.

So in that case, you may want to err on the conservative side and use UNION ALL which does not eliminate duplicates. Simple enough...


We will share more as it gets closer, and if you are coming to Fargo stop by our session and say Hi!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.