| Author |
Topic |
|
cjp
Yak Posting Veteran
69 Posts |
 Posted - 2010-12-03 : 05:12:23
|
| I have been reading some online articles about the performance benefits that could accrue from preventing table locking. My installation of SQLS 05 is standalone (and the computer is not even on the internet), so there is no question of someone from somewhere else issuing a transaction against a table while I am working on it.The articles suggest that speed improvements of 15-20% could be expected if the nolock hint were used; I experimented with this yesterday on a testbed database on my laptop and found a noticeable speed improvement. In principle, would it be safe to use this hint routinely in a standalone environment?Thanks.Chris |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-03 : 05:20:17
|
| If you are sure no updates are being issued then it should be fairly safe.An issue might be if a system task kicks off to reorg. Don't think a statistics update should be an issue but it might.I never use this if i want reliable results.I'm a bit surprised you found that much improvement on a stand alone system. What happens if you take table locks?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-03 : 07:15:51
|
| "I never use this if i want reliable results"Me too, but we go further than that. We only ever use it on high-insert tables where DBAs want to query something for diagnosis. It does not exist anywhere in our application that users connect to.I'm surprised that any difference is noticed in standalone sue. True there must be some "effort" in creating a read-lock. Perhaps there is a caching effect? Clear SQL's cache (or restart the SQL Service) before making each benchmark test perhaps? |
|
|
|
cjp
Yak Posting Veteran
69 Posts |
Posted - 2010-12-03 : 07:29:27
|
| Thanks for your advice - which I will follow.Chris |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2010-12-03 : 07:58:54
|
This was posted by Kristen to the wrong thread...I think it belongs here:quote:
Originally posted by Kristen
"WITH (NOLOCK)"Only use if you know how dangerous the side effects are, and are handling them in your application and/or the data is not being used for line-of-business decisions, or users are aware of the ramificationsWhat most people who use NOLOCK actually want is READ_COMMITTED_SNAPSHOT [introduced in SQL2005] (its a database setting so you don't need anything in your code per se, after setting it, but you will need to remove all NOLOCK from your code unless you are comfortable with the side effects).
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-03 : 09:18:05
|
| Well ... it was meant for that thread (too) The OP in that thread posted some code with a simple syntax error (which someone kindly pointed out). I was pointing out additionally, in that thread, that NOLOCK is dangerous - which I do whenever I see people posting code snippets with NOLOCK hints in them because I think there is a huge amount of code where NOLOCK was used back in the days of SQL 2000, and earlier, because that was the only fix, and it perpetuates and developers are (probably) not aware of the risks / side effects.READ_COMMITTED_SNAPSHOT (since SQL2005) is usually the solution that developers were looking for (i.e. when they have READs blocking WRITEs). However, I don't think that will help this O/P as I expect that READ_COMMITTED_SNAPSHOT will ADD some CPU etc, and if O/P is genuinely single-user then (hopefully!) O/P won't be blocking him/herself! |
|
|
|
cjp
Yak Posting Veteran
69 Posts |
Posted - 2010-12-06 : 04:37:02
|
| I suspect that I have been labouring under a misapprehension: my setup is genuinely standalone (to address Kristen's observation) but I was under the impression that SQLS would apply and release a lock for every row of data that it examined. For this reason, I was wondering whether abandoning locks would improve speed - as advised in an article I read recently but which I probably misunderstood.The balance of response is that using a nolocks hint is bad practice - so I will abandon the idea and concentrate on other ways to improve performance - eg improving my indexes.Thank you.Chris |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-06 : 05:45:54
|
"release a lock for every row of data that it examined."Yeah, that's pretty much it - but "it depends"  SQL locks things to say "I am reading this" - which stops it changing underfoot, and if you are the only user then it may be a moot point.Might be worth looking at ISOLATION LEVEL instead. If you put NOLOCK hints in your code and you ever need to change to "Multi user" or have some other reason for change, it will be a lot of code to change. You should be able to set ISOLATION LEVEL at a higher level.I wonder how READ_COMITTED_SNAPSHOT works (you set this at the database level, so would work for all your code )It MIGHT be that WRITES are stored separately and READs get a free ride (but it might also be that READs force a SnapShot of the database - which would be more CPU effort). I don't know the answer, our databases are multi-user and we use READ_COMITTED_SNAPSHOT so that users get a consistent view without Writes Changing things and locking/blocking |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-12-06 : 12:15:48
|
quote:
Originally posted by cjp
I suspect that I have been labouring under a misapprehension: my setup is genuinely standalone (to address Kristen's observation) but I was under the impression that SQLS would apply and release a lock for every row of data that it examined. For this reason, I was wondering whether abandoning locks would improve speed - as advised in an article I read recently but which I probably misunderstood.The balance of response is that using a nolocks hint is bad practice - so I will abandon the idea and concentrate on other ways to improve performance - eg improving my indexes.Thank you.Chris
Is this "application" just for you to run on your own computer and that's it?If so, then NOLOCK is propbably fine. If you don't understand the implications of using different isolation levels, then you should read up on them. The only two isolation levels that prevent Dirty Reads, Nonrepeatable Reads and Phatom Reads are Snapshot and Serializable. But, maybe, some of those side affects are not of paramount importance if you are just doing something on your local machine.Not that it is a huge deal, but Snapshot isolation levels has an affect on TempDB usage. So, depending on how your application is written (does it use tempdb a lot) or how the database is configured (does tempdb have it's own drive(s)) can affect performance too. |
|
|
|
cjp
Yak Posting Veteran
69 Posts |
Posted - 2010-12-07 : 03:22:13
|
| Well - amazing how an innocent question suddenly opens out into unimagined complexity! I will do the reading that Lamprey suggests but am happy to approach this conservatively and leave well alone. The idea of a snapshot reminds me of Access, from a long time ago, which offers this facility - producing a non-editable result set.In response to the question about my setup, the tempdb is on a separate physical disk: I use separate physical disks for mdfs, ldfs, tempdb and system files. The reason everything is on a standalone setup is that our data supplier will not license a networked installation of potentially sensitive medical information: I have to build, query, manage the data on one computer and then output tables in text format for statistical analysis.Chris |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-07 : 03:51:10
|
| "Snapshot isolation levels has an affect on TempDB usage"Is that actually true in the real world? (edit: I mean: I have read that often, but not seen a significant increase in TEMPDB in practice)Maybe our database isn't that busy, but I think our TEMPDB is very small (and we run with READ_COMMITTED_SNAPSHOP). |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-07 : 03:54:38
|
| cjp: Could your database be set to read-only? (or could you split it into one database that is read-only and another that has any intermediate results etc)That might enable SQL to process data more quickly |
|
|
|
cjp
Yak Posting Veteran
69 Posts |
Posted - 2010-12-07 : 04:25:11
|
| Thank you, Kristen - I will think about that. Off the top of my head, I suspect that, at some point, I could make it read only. I have to dump and rebuild whole databases 2-4 times each year (depending on study requirements) but, having done so, the installed datasets rarely change - no inserts, no deletions. However, I do have to manipulate the raw build to make it usable epidemiologically - I need, for example, to change the demographic table (one row per patient) - but this involves adding extra fields to take calculated outcomes and practice metadata - no inserts or deletions.From time to time, the data supplier notifies errors so I would then need to be able to add or delete records but this happens seldom so, in principle, after the preliminary work, read only becomes a viable proposition.Chris |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-07 : 07:24:52
|
| You can change the database to READ-WRITE when you need to do the occasional updates. I think you will that queries are faster when the DB is READ-ONLY - as SQL will know it is not changing ... but I've not tried it! |
|
|
|
|
With(nolock) query hint