Why is my database application running so slowly?

There are many reasons that a database application can be slow.  In large ERP systems like Dynamics GP, there are often complex operations happening behind the scenes when batches are being generated or posted.  All too often, we throw more hardware at a performance problem., when the issue could be as simple as a poor join or missing index.

So how do we figure out what to do about a performance problem?

First, I would make sure the SQL server is set up in a manner that follows best practices.  Here are some links to posts on setup:
Why you should not store user data on a sql server.
SQL Server setup and maintenance tips
Dream SQL Install
Creating a good set of SQL maintenance plans

If SQL is installed in a virtual environment, start here:
Keys to SQL on VMWare
Essential Guide to SQL Server Virtualization

Let's say I have done all that and I still have performance issues with posting a large batch.  The users normally use the application on a terminal server, so I am not sure if this is a network, terminal server, or application issue.

If you have a good tool, such as LiteSpeed, turn it on and post the batch.  In other words, use your tool to figure out why it is slow and make changes to fix it.  If you still have issues, or if you only have the tools that install with SQL server, here is what I would do:
1. Get a base line
- Restore the production database to a test database on the same server
- Post the batch from the terminal server while running a SQL Profiler trace
   * Time the process
   * Save the trace
- Restore prod to test (so we keep using same data set)
2. To see if terminal server is the issue, run the process from a fat client while running a trace

   * Time the process
   * Save the trace
- Restore prod to test (so we keep using same data set
3. To rule out network issues, run the process from the SQL server while running a trace

    * Time the process

   * Save the trace
- Restore prod to test (so we keep using same data set

At this point you will know whether it is a network issue, terminal server issue, or SQL issue, based on the difference in run times.
. 

If it is a SQL issue:

- Use trace results to identify the SQL objects involved and manually generate a list of those objects.

   * Tables: Check row count and explore options for purging or archiving records on large tables. Check index fragmentation.

   * Views: Check execution plan for missing indexes.  Eliminate poor joins.  Eliminate function calls that can be easily handled with inline statements.  Ensure that schema (eg. dbo) is referenced on every table operation to allow caching of the execution plan.

   * Stored Procedures: Check execution plan for missing indexes.  Eliminate poor joins.  Eliminate function calls that can be easily handled with inline statements.  Ensure that schema (eg. dbo) is referenced on every table operation to allow caching of the execution plan.  Try switching between #temp and @temp variables where used.  Look for opportunities to reduce the size of local or temp table record sets early in the processing.  Eliminate cursors where possible.

After all that, now it is time to look deeper in SQL.  
Check the performance counters for PLE and RAM in use to help determine if more RAM is needed. A good rule of thumb is that if PLE regularly falls below (75*(GB of RAM)), more RAM is needed.

Check the performance counters for disk reads and writes per second to help determine if a faster hard drive is needed.

A

 This should get you started.  I will add to this post as I find tricks that help.