Hmm… Performance problems retrieving data - that could be a lot of things. However, here is some ideas how you might trouble shoot the issues you are experiencing.

1.SQL Server is most likely taking up most of your 2 GB of memory. Make sure there are no other applications competing for this memory. One method is to use task manager, click “show processes from all users” and sort by CPU and memory.

2.Setup a SQL Profiler trace on a single user. When that user starts having performance problems, you will have all the commands sent to the database. Look for the commands with long durations.

3.Run SP_WHO2 active when you start having performance issues determine if there is a process that is taking up a lot of memory or cpu time.

4.Setup a Server Side Script. Capture the statements with long durations.

5.Setup Performance counters. Create a baseline, when you are not having issues. When you do have issues, compare the current values to the baseline values. I pull perfmon file into excel and create charts. Below are the counters I use. This will determine if there are any bottlenecks.

Memory\Available MBytes

Memory\Pages/sec

Network Interface(Broadcom NetXtreme Gigabit Ethernet _2)\Output Queue Length

Network Interface(Broadcom NetXtreme Gigabit Ethernet)\Output Queue Length

Paging File(_Total)\% Usage

PhysicalDisk(*)\% Disk Time

PhysicalDisk(*)\Avg. Disk Queue Length

Processor(_Total)\% Processor Time

SQLServer:Buffer Manager\Buffer cache hit ratio

SQLServer:General Statistics\User Connections

SQLServer:Latches\Latch Waits/sec

SQLServer:Locks(_Total)\Lock Requests/sec

SQLServer:Memory Manager\Total Server Memory (KB)

System\Processor Queue Length

Bill Richards