September 7, 2017 at 7:33 am
Guys,
Any thoughts on a best practice approach to preventing users from blocking my SQL Datawarehouse load while the job is running? it runs nightly for about 2 hours, and occasionally a user will kick off a report which will block the job from finishing....
I thought about adding a step in the beginning disabling the reporting login for the time it's running on that server, any ideas if this is a good practice or not?
Other info:
--- the only DB's on that server are the DWH DB's.
--- all reports are run by the same id.
Thanks!
September 7, 2017 at 9:11 am
Disabling reportlogin and killing reportsessions sounds feasible.
Another option is read committed snapshot isolation ( depends on what kind of ETL is running)
September 7, 2017 at 10:02 am
There are a couple of options you can try that will let things place nice together.
I'm imagining that what happens is the report ends up taking out a table lock, and takes long enough to finish that the ETL times out trying to get data into the tables that the report job has locked.
For something like this, the first thing I would look at is trying to tune the report query. Or, since this is a a DW environment, I wouldn't be concerned about adding any extra indexes to support this report and get it in and out before the locks take too long. In a DW environment, I'm more lenient about adding indexes. The load is done by automation, who cares if it takes a little extra time loading data. You want data access to be fast, because that's what the humans are waiting on.
RCSI can be an option, but it's going to put increased load on TEMPDB, and you may not want to sacrifice TEMPDB performance when you're doing an ETL that probably is already hitting TEMPDB.
One other thing you can do is trying to break up the load into smaller batches. If it's possible, batch up 4 or 5 thousand records and try to insert them in their own transactions. Don't try to stuff everything in one giant transaction. If you need to, you can do an initial load to a staging table and then batch that to the permanent table.
And of course, you can kill your user sessions and disable them from logging in for the duration of the ETL. Just make sure that if anything fails during the ETL, the user sessions still get re-enabled at the end of the process. Otherwise, you have a failed ETL and users that can't log in when you walk in the door first thing in the morning.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply