Production (20GB+) Database running slowly and timing out I know the title thread is a bit ambiguous (and my first post here!) but I am having a very severe production database problem. I am not a DBA, though I know how to work with queries and SQL Manager. The database is constantly under load, as there's a constant input and output. The problems started about a week ago and I've done everything I know to get it working... from Rebuilding Indexes to Updating Statistics to Shrinking DB. As an example of the recent problem I used to run a daily backup at 5PM and it took 20-25 minutes, now its taking over an hour. Some days the thing will work just fine, for example yesterday we didn't have an issue and today its all over the place. Its a 20+GB Database running in a VM with Windwos Server 2008R2, dual Xeon @ 2.4GHZ and 12GB of RAM plus a partition for OS, one for DB and one for backups. As one of the measures I am taking I created a separate Log partition to move the log from the DB HardDisk to its own separate HDD. Haven't yet done it as I need to detach and take everything offline. I checked the fragmentation for the main table (5+ million entries) and it was over 99%... this was AFTER rebuilding the index. 

At this point, I am just stuck. The application users use is timing out, transactions not going in or out (financial institution) sometimes. I am constantly monitoring the performance, and heck even the SQL Server Activity Monitor timed out for me a few moments ago. Any suggestions and what else should I do (aside from separating the log and DB) would be greatly appreciated.

EDIT: Server RAM Usage just in case:
[1]

EDIT2: Code used for rebuilding Index

  ALTER INDEX ALL ON DailyTransactions
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
          STATISTICS_NORECOMPUTE = On);
First you need to figure out what the actual problem is. The sys.dm_os_wait_stats DMV can help with that. Most values in there are accumulative, so you need to capture it a few times  over an extended period to see what is actually going on. 

One of the things that this DMV can tell you is if you have RAM pressure. The graphic you posted is basically useless as SQL Server uses all memory it can get (if it needs to). So this is only showing that that mechanism is working.

Once you figured out what your biggest problem is, come back here to get more help. 



There are a few wait_types that can be considered noise. You can filter many of those out with this query:

SELECT wait_type,
       wait_time_ms / 1000.0 AS wait_in_sec,
       (wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_sec,
       signal_wait_time_ms / 1000.0 AS signal_sec,
       waiting_tasks_count AS wait_count,
       100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS percentage
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
        'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
        'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
        'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
        'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
        'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
        'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP')
     AND waiting_tasks_count &gt;0
     AND wait_time_ms &gt;0
  ORDER BY wait_time_ms DESC;


(based on http://www.sqlskills.com/blogs/paul/survey-what-is-the-highest-wait-on-your-system/)