SQL Server Performance Forum – Threads Archive
Read Only Databases – Table Level?EM allows a database to be set to readonly, removing the need for most (all?) the locking semantics. My database runs on a single machine but I need to squeeze every last bit of performance out of it due to the complexity of queries im using. Am I able to set readonly at table level, leaving two or three tables free for read/write ?<br /><br />Failing that, are Key level locks mandatory when sql server is processing? Profiler shows thousands of key level locks being used, and as its single user id like to do away with this and give it free reign without respecting any locks. NOLOCK only inhibits shared locks it seems.<br /><br />Thanks for all your help here. I realise I ask way too many questions, but this site is a great resource <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />
If you are trying to squeeze every last ounce of performance, then for your read only tables you can pin the tables to memory so it never gets flushed out of the cache. You’ll have to gauge and see if there is a performance benefit, because when I tried it, it did not seem to help. It’s a DBCC command: DBCC PINTABLE
"How do you expect to beat me when I am forever?"
There is no real way to set a table to read-only, but you can in effect turn off locking to a table using a hint, which can help performance. (Note: You can also use security to make a table read-only from a permissions perspective by setting the table to SELECT only permission, but this won’t affect locking behavior.) But keep in mind if you do this, that if the data changes while it is being read, you won’t know that it happened. So assuming the tables in question are not modified, you should be safe using the hint. Here are two tips on my website that may be useful to you when deciding to turn off locking when a query runs against a table. One way to reduce locking, especially for queries or stored procedures that are used to create reports, is to force SQL Server to turn off shared locking when the query or stored procedure is run. By default, SQL Server will use shared locks on any rows that are queried by a query or stored procedure. The purpose of these shared locks is to prevent any user from changing any of the affected rows while the transaction is still running. Assuming you don’t care if any of the rows you are querying change during the query itself (which is common for many reports), you can turn off shared locking, which in turn increases concurrently in the database and can boost performance. Of course, if you require your data not to change while your transaction is still running, then you cannot take advantage of this technique. One of the easiest ways to turn of shared locks during a query or stored procedure is to use the NOLOCK hint. This hint, when used with the SELECT statement, tells SQL Server not to issue any shared locks on the table(s) being read. This hint is only good for the query or stored procedure you give it to. It does not affect any other queries or stored procedures. Before using this technique, be sure you test it thoroughly to ensure that turning off sharing locks does not present you with any unexpected problems.
Another way to reduce locking is to use the least restrictive transaction isolation level possible for your user connection, instead of always using the default READ COMMITTED. In order to do this without causing other problems, the nature of the transaction must be carefully analyzed as to what the effect of a different isolation will be.
Brad M. McGehee
Hi Brad, Thanks for the comments. I had tried the WITH NOLOCK hint, which didnt seem to make that much difference. A profiler session identified that sql server was issuing huge amounts of keys locks (I suppose this is only to be expected, and the time reqd for them is neglible. Its just because there was one for each row that I wondered if it would be possible to suppress locking altogether). Seems not.<br /><br />I then thought about the other end of the scale. If I was to issue an exclusive lock on each table (the current query is the only one accessing t, guaranteed, so no harm done), then perhaps sql server wouldnt need to issue all those key locks). This seemed to make no difference either . <br /><br />Im thinking I ought to look elsewhere for performance increases <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />
you can use either lock hints (NOLOCK, TABLOCK etc) for each table desired in the query or explicitly disable row or page locks for the entire table as follows EXEC sp_indexoption ‘Customers.City’, ‘disallowrowlocks’, TRUE
EXEC sp_indexoption ‘Customers.City’, ‘disallowpagelocks’, TRUE if your query involves a single table, you will find that lock hints have no observable benefit
if your query involves two tables and a loop join, the lock hint might have 20-30% improvement,
if you have a one-to-many merge join, NOLOCK might have ~2X benefit
hash joins are more complicated depending on whether it is an in-memory hash also, read-only is only meant to protect the data from modification, i am not sure that SQL automatically lowers the default locking level for a read-only db
When a SQL Server database is set to read-only, locking is effectively turned off. Some people will create a copy of their production database and run it on another server in read-only mode for reporting purposes. This not only gets much of the query load off the production server, but also speeds up queries in the read-only database. Joe, thanks for your contribution. I really like the detail you offer. To other forum members, if you have done do yet, look for Joe’s articles on this website. They are very good.
Brad M. McGehee
Is there an easy way to script the database on and off of READONLY? I would like to do this through code at the end of the batch update and then prior to loading the next night? Thanks in advance![?]
Bveara, In SQL 2K you can use ALTER DATABASE with READ_ONLY & READ_WRITE options, refer to books online for more information. HTH Satya SKJ