Brain numbing page lock problem
-
This has been driving me up the wall for some days now.
I have a M$ SQL 2000 clustered box, 4 chips, loads of RAM and free space.
I have a central table containing market data for UK Companies (3.1 mill), linked to Companies House Data (1.6 mill) and Contacts (3.9 mill) + a few other big resources.
My problem is this, every update to the Company data table fails. It isn't blocked by any other transaction, but in the current activity window it is shown as waiting for resource PAGEIOLATCH_SH. This resource never becomes free (well I say never, 73 hours waiting for a single row update and you give up
).This also happens in any locking operation (ie any writable read). I have copied the entire table with (nolock) to a new table and the new table has the same symptoms.
I have detached the entire database (all 40 gig of it) and reattached so as only I could access it. Taken the cluster to pieces, rebooted or restarted everything that could be, ran every DBCC process and found no errors.
Any suggestions before I switch to oracle?
Edited by - KHENRY on 07/04/2002 02:52:10 AM
-
Dont know that these will help, but I'll ask anyway?
1) Table is indexed, you've checked the query plan to see that it makes sense?
2) Updated statistics?
3) Double checked what any triggers on the table are doing (and maybe disabled them?)?
4) Profiled to verify what update is being received on the server?
Andy
-
Thanks for the response, but:
1) a)Indexed on key I'm using to find row
b)Optimised query plan
2)Yup
3)No triggers
4)Yes
I'm tearing my hair out here...
-
What is the values for disk counters like PhysicalDisk()\Avg Disk Queue Length, PhysicalDisk()\Current Disk Queue Length. Did you run DBCC CHECKDB('DB_Name',REPAIR_REBUILD) WITH ALL_ERRORMSGS recently?
-
Is the database set to 65? sh_page errors are thing from SQL 6.5. Upon conversion it is easy to forget to change modes.
sp_dbcmptlevel 'yourDBname'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply