Database Name: Buzz_Data
Database Size: 12.3 GB
Data Files: 1 (Primary)
Transaction Log File: 1
Database Tables: 500+
At Present my user's are facing problem to retrive data in time inspite of having Server's config like Intel XEON (IBM) , 2 GB Ram, 80 GB HDD.
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.
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:Memory Manager\Total Server Memory (KB)
System\Processor Queue Length