If the data is purely read-only once it arrives in the target database (and you have lots of disk space available), here's an alternative you might consider.
-- Two tables with the SAME structure
create table table1_a ( ...
create table table1_b ( ...
-- No Application permissions on these tables
create view table1_read as select * from table1_a ;
I can't think of a fully transparent way to achieve what you want with views without disabling row goals in general, which probably won't suit your purposes.
As the purpose of the view is to provide some business analytics to data analysts, while they are developing reports they use to check a sample of the view by doing a select (top N) query.
First, the plans you pasted were the estimated execution plans, we would need the actual execution plan. I'm guessing that you right-clicked in SSMS and selected, "show execution plan"... instead you need to select "Include Actual Execution Plan" prior to running the query. They are usually the same but the actual plan includes the actual number of rows ...
I’m assuming you have a Data Flow with 100,000 records and an OLEDB Command transform which executes that update statement. That results in 100,000 individual update statements running.
Instead use an OLEDB destination with fast load enabled to write to a stage table (some table which you have previously truncated in an Execute SQL task) to insert the 100,...
Rate Per Second = RPS
Suggestions for your my.cnf [mysqld] section,
innodb_buffer_pool_size=50G # from 52G for 80% of 62G
sort_buffer_size=4M # from 52M for this per connection RAM request
join_buffer_size=1M # from 52M to support row pointers for join per connection
read_rnd_buffer_size=192K # from 52M to reduce handler_read_rnd_next RPS
FROM tmp_VendorRate_stage_1 v
WHERE EXISTS ( SELECT NULL
FROM tmp_VendorRate_stage_1_orig vd
WHERE v.RowOriginationCodeID = 0
AND v.RowCodeID = vd.RowCodeID
AND v.TimezonesID = vd.TimezonesID )
Ensure the indices by (RowCodeID, TimezonesID, RowOriginationCodeID) exists (in _orig ...
First advice: you can speed up query by applying index to columns in where clause, i.e. ABC_Id.
Second advice: indexes slow down deletion and insertion of rows in a table, so if you abused indexes (and defined many on one table), deletion also may be slowed down by these.
Generally, there is no obvious answer: delete ... where can be thought as steps:
It sounds like you have encountered this bug with the SQL Server 2014 ("new") cardinality estimator:
Identifying SQL Server 2014 New Cardinality Estimator issues and Service Pack 1 improvement
The specific bug was fixed in SQL Server 2014 SP1, per this KB:
When the join predicate uses other conditions than equality or a combination of equalities, the ...
For the large tables, we require to update 20% of a row to auto-update the statistics. For example, a table with 1 million rows requires 200,000 rows updates. It might not be suitable for the query optimizer to generate an efficient execution plan.
SQL Server 2016 onwards, it uses dynamic statistics update threshold, and it adjusts automatically ...
The threshold for parallelism should be higher than 20 for very large database.
Memory pressure can be avoided by tuning query/index, rebuilding fragmented index, enabling TF 2371 at database level to fire auto update statistics frequently.
As a DBA you can add multiple file group to your very large database, each containing separate mdf file (located on ...