Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MichaelP
Jedi Yak
2489 Posts |
 Posted - 2003-01-28 : 16:26:46
|
| I've read on this site and others that making your database read only makes things faster. Is it possible to mark some tables in a database as read only in a way to increase performance on those tables? I'd want other tables in the same database to be readable.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-28 : 16:59:02
|
| You can always revoke INSERT, UPDATE and DELETE permissions on those tables, and write your SELECT statements using the NOLOCK hint. That will cut down on some overhead by not requiring locks, but you have to specify NOLOCK each time. |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-01-28 : 18:36:37
|
| Ok, sounds like NOLOCK is what I'm looking for. Is there any way to see what / how many locks a certain query causes, or do I have to use sp_who2 / sp_lock to figure it out?The subtree cost on this query is 0.0128 currently, I'm not sure that I need to improve it that much, but it IS called quite a bit.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-28 : 18:51:17
|
| I don't think NOLOCK will alter the execution plan. All it does is shave off the cycles that locking would otherwise take up. Truth is, unless the table being accessed had a lot of lock contention, deadlocking or blocking (which a read-only table wouldn't have) NOLOCK may not improve performance all that much. The only way to really speed things up would be to index the hell out of the table, and if it really doesn't get updated that much then you don't have to worry about indexes slowing down performance. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-28 : 23:03:56
|
| Using nolock you can get incorrect results from a query so it should be avoided.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-01-29 : 00:01:36
|
| NR,The query in question hits tables that hardly ever change (think customer options and their default values). That's why I'm thinking I can get away with nolock, since I pretty much only SELECT from the tables in question.Will I run into issues with NOLOCk in this case? Should I just avoid it to avoid any possible issues I might have?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-29 : 00:14:30
|
| Well, since you're talking about wanting to have the table read-only I don't forsee any problems with NOLOCK, for exactly that reason: the data will rarely change. If you were to make the database read-only you would effectively get NOLOCK by default; it certainly wouldn't make the data any less valid. |
|
|
|
|
|
|
|
|
Read only Table?