I manage a SQL server database for a manufacturing site. We collect data on all the units that we produce at our facility. Because we have a high volume manufacturing business, the SQL server 2000 database we use to collect the data is quite large. Our database is over 36Gb and one of the tables has over 143 million records in it.
The issue I have is we use the same server for queries as we do for live production equipment. As a result when someone is using the website to search through the data, it sometimes affects the production lines, which is not a good thing!
We heavily index our tables to suit the queries we run, but sometimes we have users that need to run queries that cover production data that covers a long period of time. Sometimes when this happens, the query optimizer will lock the table it is searching while it performs the query. when this happens, all inserts from the production floor are blocked by the query and everything just stops!
I have tried running the queries with the nolock option, but it still locks the table!
I am using ADO and the recordset object from ASP on the website for the queries that are causing the problems.
Basically, the queries do not have to be critical. It is much more important to insert the data from the floor than it is to not miss a few records that the production line is processing.
Is there any way that we can turn off the table locking features during the queries? This is becoming a real issue and is causing significant downtime!
I know there used to be a lock escalation threashold that was configurable for SQL 7.0 but it appears that this has been removed.
We run a high volume database here, and have found that putting the (nolock) after each table listed in any from clause in a select statement fixed our problem.
Another problem we found was that if someone has Enterprise Manager open with the results from a table up, it can cause a table page or row level lock as well.
One may accomplish the same effect (as inserting NoLock hints for all tables in select statements) within a given transaction via:
Set Isolation level READ UNCOMMITTED
In either case, the effect is the same i.e.(values in returned data sets may change, and additional rows may 'appear' while others 'disappear' in the returned data set before the end of the transaction).
Here is the VBscript code I am using to perform the search. As you can see I am already using the nolock hint and setting the appropriate isolation level. For the life of me I cant figure out why it still insists on setting any locks at all for the search!
sqltext = "SELECT audio_tester.measured_left, audio_tester.measured_right " & _
" FROM serial_numbers WITH (NOLOCK) " & _
" RIGHT JOIN audio_tester ON serial_numbers.ID = audio_tester.serial_num " & _
" WHERE (serial_numbers.date_time > '" & request("month") & "/" & request("day") & "/" & request("year") & " " & request("hour") & "') and (serial_numbers.date_time < '" & request("month_end") & "/" & request("day_end") & "/" & request("year_end") & " " & request("hour_end") & "') " & _
" and (serial_numbers.test_type = 1) and audio_tester.test_name = '" & request("test") & "' " & _
" and (serial_numbers.serial_num like 'M" & request("model") & "%') " & _
" and (serial_numbers.node_name like '" & request("station") & "%') "
conntemp.IsolationLevel = 256
conntemp.CommandTimeout = 120
Server.ScriptTimeout = 600