March 24, 2004 at 5:32 pm
Hello,
I am in a situation today where I have a lot of users who are using a Microsoft Access 2003 ADP (an Elementary school report card application) to input and print out report cards, and our one database server is having a difficult time with the load. The ADP uses a Windows authenticated through an OLE DB connection to a database on SQL Server 2000 Standard. At the moment, I have 236 connections to the database.
I spend much more time with development than I do with perfomance issues, and I'm not sure which counters I can use from performance monitor to give me at least a preliminary indication as to where the bottle necks are.
The one counter I have checked, %Processor time shows the processor running at 100%. Task Manager shows that the SQL Server Service is using between 98% and 100% processor time.
The machine runs Windows 2K Advanced Server SP 4, and has two 347 MHZ processors (yes, I know, the processors just might be the problem). The box has 1GB of RAM, and the main database file (just one Primary mdf file, no secondary files) sits on 34GB three SCSI disk RAID (I'm not sure which RAID level, but I'm pretty sure its not RAID 5).
The single Log file for the database has plenty of space, and the database file is less than half full.
When I look at locks/objects, one thing I notice is that there is a particular table referenced in 'Index' for all of the objects that are listed. The table is called 'tblGradeCodes2'. Here is a sample of what I am seeing in SQL Server EM under locks/objects for a particular process:
ProcessID: 104
Lock Type: TAB
Mode: Sch-S
Status: Grant
Owner: Xact
Index: tblGradeCodes2
I will check BOL for more information on this lock, but I wonder if this could be a problem.
From what I've given here, what can I look at to give me a preliminary indication as to where my most serious preformance problems might be. What other counters can I check to see what is going on with the disk subsystem, the processor, memory, etc? What would these counters tell me, and how would I interpret the results?
If you require more information, please let me know.
Thanks for your help!
CSDunn
March 24, 2004 at 6:20 pm
In your first paragraph,
I have a lot of users who are using a Microsoft Access 2003 ADP (an Elementary school report card application) to input and print out report cards, and our one database server is having a difficult time with the load.
Just what exactly does this process consist of? Is it a stored procedure? Or some adhoc code that you execute?
My guess is that the problem lies within this and that you have a concurrent process running at the same time as the above procedure you have mentioned.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply