Sql-server – How to keep a schema separate from the rest of the database, so it doesn’t lock?

lockingschemasql server

We are using a propriety SQL Server 2012 database, from a company that has given it's tables, views, objects, etc a schema name – lets call it COMP, for "the company's" schema.

We, as the client, are allowed to have full access to the company's database structure via SSMS, and have full control of all aspects of it. However, in order for us to customise the database to our needs, we've created a separate schema called INT, for "our internal" schema. Our new tables, views, etc are all under this schema and everyone is happy.

However, our "INT" schema has an hour job we run that seems (have not confirmed yet), to lock the entire database including the company's schema and structure. This in effect locks the application, at the users' end for at least 3 to 5 minutes. Some users have already noticed this and have started to raise complaints.

Some of our views in our INT schema, do refer to COMP schema tables and objects. Could that be why it's locking everything? Is there a way to separate our schema from the COMP schema of objects so it doesn't lock or hog the server to all users? Is there best practice on how to do the above please?

Best Answer

Picking up from the comment thread, attempting to summarize and provide some suggestions:

Schema is just a logical construct - there is nothing physical about it. So there is no difference from a locking perspective between having your stuff in the same or different schema. If you have, say, views that refer to tables in prod and, say, modify through the views, then locks will be applied to base tables. There are several options here, but schema isn't one of them.

Having said that, if you determine to create objects in that database, having a separate schema can be good for other reasons than blocking. Easier to see by the schema which objects were created by you, for instance.

Which option to relieve blocking would be best is hard for us to say, since we have close to none knowledge of what you are doing.

I like the KISS principle, so if you are only reading data, and can accept data being one day old, then consider backup and restore (into a new "reporting" database).

Or, copy only the stuff you need into new tables (possibly into a new database). This would probably be an Agent job. You can investigate functionality such as Change Data Capture (CDC) and Change Tracking (CT), if you want to work with the delta. The more fancy you get, the more time you will spend developing this, typically.

If you don't want to work with a copy of the production data, then we would really need to know what you are doing. Investigate whether allowing the snapshot isolation level (a database setting) would be an option. Your code would use the SNAPSHOT isolation level and likelihood for blocking is reduced. But since old versions of rows now have to be stored in tempdb (to allow getting the previous version of the row for a reader), you will have an increased resource usage in the database.