I'm running sql server 2000 enterprise edition on windows 2003 enterprise edition. The server is a dual 3Ghz cpu with 3.75Gb of Ram. The DB runs alone on this machine. No IIS or other client-server apps run on it
We are a medium sized nonprofit serving at-risk families and their kids.
We have a small intranet that we've run for about 2 years for our +-300 employees. One table, our largest so far at 142,904 rows and 16 columns, has suddenly slowed considerably in the last few days. It is our Timesheet details table and both employees and their supervisors run queries against. All queries are in Stored Procedures. It's slow enough that supervisors call in and I've noticed timeouts occurring.
The table has one Primary key and an index on a foreign key to the main timesheet table....am i saying that right?
It seems pretty odd to me that such a small table would be timing out but I'm not that knowledgable about dbs (yet).
I first noticed the issue because I make it a habit of checking the event viewer on our web server. Yesterday there was about 10 occurrences and today its doubled...already. We only have 4 pages that query the Timesheet and Timesheet Details tables and provide different functionalities and all of the pages have slowed down considerably.
We reindexed our tables, a job that runs weekly, and that sped things up considerably and stopped our errors, so far, for today.
I am hoping that some folks on dbForum who are familiar with troubleshooting and optimization could help me gain some insight into why this would be happening? Is some setting in our db not right that might be causing this kind of fragmentation? Could our indexes be insufficient?
I'm happy to share info on how these 2 tables are setup, I just don't know how to make much sense of it myself at the moment.
I would check if there are any concurrent updates that are blocking the select on that table. SQL Server 2000 will not allow concurrent read and write access to the same row (might even be on page level, but I'm not sure about that)
I have also seen locks being held on system objects e.g. by doing a CREATE VIEW that would block SELECTs on other tables just because the system tables are blocked.
If you are subject to blocking can easily be seen with sp_who2 which will list if a session is blocked by another.
You can also use the SQL Server Profiler to pinpoint the queries that are running slow and then go from there to analyze why they are slow.
TIs SQL Profiler something that can be run on a production database during the working day or would it be too big of a performance hit?
It simply logs the statements that are currently sent to the database. You can filter based on various conditions such as database affected, user and (which is interesting for you) the execution time of the query.