Over the years of building and maintaining business user facing databases for adHoc and reporting analysis, I’ve had numerous situations where I would need to truncate/reload a table or perform an update on it. However, if one of the business users was querying the table, I would have to wait for them to finish their data pull before I could do the update. This would lead to a lot of cat and mouse games and trying to contact the user if the query ran too long to suspend it so I could make changes. To overcome this, I came up with this strategy that has been working great. The proc to maintain this is attached, and any suggestions are welcome:
Strategy to Maintain a Robust SQL Database and avoid blocking
- Run a script hourly that takes each table and creates a corresponding view with the same columns and uses the NOLOCK hint
- The view name is the same as the table but prefixed with a “v_”.
- The view has a watermark applied at the top of the definition in the comments so that we can detect later if the view was created from this script in case it needs to be dropped.
- Example: You have a table with the following structure:
CREATE TABLE dbo.dim_departments
dpt_id int primary key
,last_upd_ts smalldatetime not null
The corresponding view that the script creates will look like this:
*** AUTO VIEW GENERATION PROCESS ***
VIEW WAS AUTO GENERATED FROM proc usp_update_bus_user_permissions on 2017-07-05 12:00:16.2866
DO NOT ALTER THIS VIEW. IT WILL BE OVERWRITTEN HOURLY.
IF YOU NEED TO ADD COLUMNS, CREATE ANOTHER VIEW prefixed with "vt_"
CREATE VIEW dbo.v_dim_departments as
FROM dbo.dim_departments WITH (NOLOCK)
- The script then grants SELECT and VIEW DEFINITION to the business user account on the view, but not the base table
- After all views have been created/updated, the script checks for views from the auto-generation process (prefixed with “v_” and the watermark) that are unresolvable due to syntax errors or the original base table getting dropped. When these views are detected, they get dropped from the database. This helps maintain a database that is self-cleaning and provides users with views that will fire on SELECT.
A few remarks
- When I create databases, I always use a database role to assign permissions to. I don’t ever assign directly to an LDAP or SQL login incase the user leaves or no longer needs access. By maintaining permission on the database role, it makes the maintenance a lot easier
- The script watermark suggests that if you need to create a custom view, to prefix it with “vt_” so that it does not get picked up in this auto-generation process. You can still name a view with a “v_” and as long as the watermark doesn’t appear in the definition, it won’t get overwritten. However, I’ve found it a lot easier to request that developers just use the “vt_” prefix for custom views so we can identify quickly if a view was auto-generated or custom.
Thanks for reading and any feedback is greatly appreciated.