0

We have a bunch of users, AD groups as well as SQL IDs accessing the DBs from time to time. During an important Data load (SSIS package) getting Data from Oracle to MSSQL, this load sometimes gets delayed due to other queries getting hold of the same tables, locked tables, long execution, etc.

What I would like to see..

  • before the start of the load, turn off or block all users except a few collections of users.
  • Do the data load
  • after data load, revert back to original state (turn on and unblock all users)

How is this done? or any other suggestions.

Thanks.

Improve this question
6
  • 1
    It's unlikely this is going to be the right solution for the problem you're having. "this load sometimes gets delayed due to other queries getting hold of the same tables" - it would be helpful to see what these queries are, what the Tables are, and what the query for the data load is. Probably some tuning (query / index / process) and / or proper isolation levels may be the right route to head down.
    – J.D.
    Sep 2, 2022 at 20:00
  • thanks @J.D. the usual process is 1. the load takes data from Oracle to SQL Sever Database tables....... more than 20 million rows.) 2. after the load, series of data transformation using Store procedures on this table to several other tables. if we use only the process to work on the DB then it takes a total of one hour and 20 mins.... if someone comes in and execute select queries.... the load takes longer... so I am looking for ways to solve the issue.
    – simpleorchid
    Sep 2, 2022 at 20:19
  • 1
    Have you tried the Resource Governor?
    – mustaccio
    Sep 2, 2022 at 20:45
  • 1
    @mustaccio If it's purely a blocking / locking issue, then Resource Governor isn't really going to help him. Perhaps it can even make the problem worse, since it'll potentially slowdown the existing problematic queries. simpleorchid, if it is purely a blocking / locking issue, then you may want to look into Read Committed Snapshot Isolation which will allow you to write to the Tables concurrently while they're being SELECTed from. You also should try to optimize...
    – J.D.
    Sep 2, 2022 at 21:05
  • ...your load process. It sounds like it probably takes longer than it really should, if it was well tuned. 20 million rows is not that much, but couldn't comment on the transformations you're applying without seeing them.
    – J.D.
    Sep 2, 2022 at 21:07

1 Answer 1

Reset to default
0

A few suggestions:

  • ensure your extract queries are performing as well as they can (indexes, partitions, etc)
  • run your extract at a time when the DB is not being accessed by online users
  • ensure read-only users have READ privilege, as opposed to SELECT which allows locking via SELECT ... FOR UPDATE
  • set up a read-only standby DB for reporting and extract, Active Data Guard is good but does cost
  • use materialised views to create your extract data set
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.