0

I am working on an application with a huge database with lots of tables having billions of rows. I have distributed development team sitting round the globe. They need to access the production database for various purposes. They have been granted Read Only Permissions to DB. It works fine if you use queries with (nolock). But if you are not using nolock you can bring the whole application down by running some never ending query. It has happened a few times in last year so now I am hesitant to grant permission to new developers.

I know that creating a view with (nolock) for each table and granting permission to only those views is one solution but somehow I don't feel like creating 200 views for this purpose.

I am looking for a way to:

  1. Grant permission in a way that all the query run without lock for that user or
  2. Force user to use with (nolock)

We are using SQL Server 2008 R2.

PS: I have never faced this problem till date with another Oracle based application, which is 50 times larger than this application.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Pankaj
  • 27
  • 7
  • http://msdn.microsoft.com/en-ca/library/ms189040%28v=sql.100%29.aspx ? – Marc B Jul 10 '13 at 20:44
  • duplicate of http://stackoverflow.com/questions/64208/how-to-force-nolock-hint-for-sql-server-logins – Vasanth Jul 10 '13 at 20:48
  • @MarcB setting the time limit on remote queries will not help me. In this database queries are bound to take long time. All I want is even if developers are reading data it should not prevent application from writing to same table. This can be achieved by using nolock or setting Isoloation Level in query but that depends on the developer to use them. I want to force this. – Pankaj Jul 11 '13 at 18:06

1 Answers1

0

If you are using Enterprice edition, you can try using resource governor. Set maximum time for query to execute. That is not really what you asked for, but it would solve problem with never ending queries. You can have a look here (scenario 2).

And you can also look at direction of changing database properties (Using Snapshot Isolation)- and this is probably why in Oracle you have not experienced such problems- Oracle uses this by default, but for SQL Server it relatively new feature (>=2005). Should be careful though, because it affects tempdb.

Jānis
  • 2,216
  • 1
  • 17
  • 27
  • Set maximum time is already set but its value is so high that it does not help to resolve the problem. Snapshot Isolation will help if there is any way to force it on all the query of a specific user. Infact for that matter READ UNCOMMITTED mode is equally good for me. – Pankaj Jul 11 '13 at 19:36
  • @Pankaj * You can set Read Committed Snapshot database wide, so it is used instead of Read Committed- i would guess that it would be better for all users, but of course, you should know better. * I was not talking about "maximum time to execute"- it is used to limit Max CPU time query can use. With Resource Governor you can set that for specific group or users. – Jānis Jul 12 '13 at 05:54