Does Dynamics GP use the tempdb database? No? Yes? Prove it!

By Steve Endow

I received an interesting email today with this statement:

"TempDB has no performance impact on Dynamics GP, as it does not use TempDB."

I had to read that a few times to make sure I wasn't mis-reading it.

So, let's have a show of hands:

How many people believe that Dynamics GP does not use the SQL Server "tempdb" database?

How many people believe that GP does use tempdb?

Perhaps a better starting question:  Who knows what the SQL Server tempdb is?

Let's start with the last question:  What is tempdb?  I suspect that many people don't know since tempdb typically operates in the background where most users, and even some DBAs may not even be aware of its existence.  Folks that are fairly technical with SQL Server and software developers that write SQL should be at least familiar with tempdb.

According to this MSDN article, tempdb is a global system database that holds certain temporary objects, such as local temp tables, temp stored procedures, variables, or cursors.  It is also used for "work tables to store intermediate results for spools or sorting", and several other SQL Server operations.  These features are critical for almost any application that uses SQL Server--not just Dynamics GP.

A few points to note about the physical characteristics of tempdb.

From that MSDN article:

tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database.

I didn't know this until today and never really thought about it before.  I think it's interesting and informative in case you ever have to diagnose or troubleshoot an issue related to tempdb.  However, while testing this with Dynamics GP, I believe that this statement is technically incorrect--from what I can tell, all temporary objects are disposed from tempdb, but non-temporary or "permanent" objects can persist in tempdb even when SQL Server is restarted (examples to follow).  It is not as if all tables in tempdb are dropped when SQL Server is restarted.

However, for "permanent" tables that are stored in tempdb, it appears that the contents of those tables will be wiped when the SQL Service is restarted.  This is a pretty interesting "feature" that I'll get back to below.

Another point:

Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another.

So when you close Dynamics GP and close out your SQL Session, any temp tables or temp stored procedures should be removed. (GP consultants will know that this isn't always the case)

So after that brief review of tempdb, what is your answer now?  Does Dynamics GP use the tempdb database?

A politician might say:  It depends on your definition of "use"!  Well, let's settle that definition.

If you view the contents of the tempdb database on a SQL Server being used for Dynamics GP, you will quickly see two physical tables:  DEX_LOCK and DEX_SESSION.

Both of those tables are used by Dynamics GP.  I'd say that qualifies for "use".

Next, check out the contents of the tempdb database on an active Dynamics GP SQL Server.  This list of temporary objects is just from one user logging into Dynamics GP, with no other activity.  That's 7 temp tables and 37 temp stored procedures, JUST FOR LOGGING IN!

Additional temp tables and temp stored procedures will be created as you perform additional processes in GP, and old temp objects will be removed.

These are just the application level objects that we can easily see in SQL Management Studio, but I would say this alone clearly shows that Dynamics GP relies heavily on tempdb.

So, now that we know that GP uses tempdb, so what?  Who cares?  Why does it matter?

Well, normally it shouldn't matter, and normally we shouldn't really care too much.  But, there are times when you are troubleshooting Dynamics GP problems, and in some instances, understanding how Dynamics GP uses tempdb may be valuable.

For instance, many consultants will be familiar with the topics discussed in these KB articles:

Remove all the inactive sessions from the DEX_LOCK

Error message when you post a batch in the general ledger

In both of these cases, you may need to clear the contents of one or both of the DEX tables in the tempdb database.

And what if Dynamics GP takes 12 minutes to login?  Perhaps tempdb could be involved in that performance problem?

Hopefully that answers some questions you may have had, or didn't know you had, about Dynamics GP and tempdb.  But I have one final question:

Why does Dynamics GP use the tempdb for the DEX_LOCK and DEX_SESSION tables?  Well, as I mentioned earlier, "permanent" tables in tempdb will automatically have their contents cleared when SQL Server is restarted.

So while it is somewhat unconventional to create application-specific tables in the tempdb database, such tables do have the interesting benefit of being cleared whenever SQL is restarted or the server is rebooted.  That is a handy feature for the DEX_LOCK and DEX_SESSION tables, as I can see wanting those tables to always be empty after a SQL restart.

So now you hopefully know more than you ever wanted to know about Dynamics GP and tempdb.  Go forth and be the life of the party!


Steve Endow is a Microsoft MVP for Dynamics GP and a 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

http://www.precipioservices.com