Nolock feature is by itself like any other tool – it can be a tool used for good or for evil. However in the data warehousing world where updates are highly controlled you will likely get a very different perspective. If you maintain OLTP systems, the response is naturally negative. It seems to me the response is largely dependent upon one’s point of view. What options do DBA-less, development focused shops have?īrent says: I’m always okay with NOLOCK because I just pretend it’s another one of SQL Server’s incorrect results bugs.Ĭoming from non-sqlserver environments, its always interesting to see what topics are hot buttons for DBAs on a specific database technology. And so I ask you, dear readerĪre you ever okay with NOLOCK? Under what circumstances? You can make all the best practice setup guides you want, but it doesn’t mean anyone’s going to follow them. Thinking about an application like kCura’s Relativity, where servers who have never met a DBA often end up with multiple terabytes of data, what would happen if a few cases got really lively? Picture tempdb with one file on a 100 GB C: drive. You could just ask them to re-run a report if they come to your desk with a hug full of dot matrix printer paper festooned with sticky note tabs, but if that happens enough times… ISVs have it especially toughĪnyone with a credit card can install their software. You might be able to get away with them just not noticing if things don’t line up. And that’s tough news to break to people who just may well want accurate reports. You can get partial data, double data, and if you catch an update that gets executed as an insert and a delete, both versions of the row. What it really does: Doesn’t respect other locks What everyone thinks: It doesn’t take out any locks Until you get there, you should know what NOLOCK actually does. Ultimately, the goal should be to get your server, and your skills, to a place where they can handle optimistic isolation levels. What other options might they have? READPAST? Informed choices That’s only moderately better than letting users query it at will.Įven with the right indexes in place for their workload, readers and writers will block each other, and long running reports can be painful. You could invalidate the cache every X minutes, but that doesn’t help because then you need to re-run that reporting query every X minutes. Caching the data when user activity is low and reporting against it when someone asks may raise some questions, like “why doesn’t my sales data show anything from today?”, or worse. They still need to run reports, either for users, or for higher ups at the company, and they can ask for them at any time. They may not be able to turn on RCSI or SI at the drop of a hat tempdb would keel over with the row versioning as part of a workload that already uses it pretty heavily. Picture a couple developers who started their app in the cloud, where they can’t get fancy with tempdb, fast disks aren’t in the budget yet, along with that beefier server with some extra RAM. What if it’s about all you can handle? Or your server can handle? Generally, outside of testing a query or grabbing some sample data, I’m going to avoid it. You can get incorrect data, and it can throw errors if your data is particularly volatile.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |