You are simply counting the number of rows..He needs 3 cliams in a year.

Why everything is defined as nvarchar(50) like fiscalyear could be a smallint similarly others. nvarchar takes double space the corresponding varchar. You might need nvarchar for some columns for others...

Gullimeel (7/2/2012)

--------------------------------------------------------------------------------

This was not so show you, how to reproduce the eager spool. It was similar situation as OP has but at smaller scale like in 100 thousands rather than...

I've updated all the statistics on the indexes with no help. Another strange thing is that in the query plan the eager spool estimates about 84k rows for the eager...

This was not so show you, how to reproduce the eager spool. It was similar situation as OP has but at smaller scale like in 100 thousands rather than millions..

Gail, You can reproduce the same by using the following sql...

drop table a

drop table nums

go

select cnt

into nums

from

(

select ROW_NUMBER() over (order by (select 0)) cnt

from sys.all_columns ac inner join sys.all_columns...

I asked for deadlock graph 😉

But all of above can be validated based...

That too...

Try below...

Regarding the performance it depends on the indexes on the columns.Can you post which column has the clustered index and which one have nonclustered index. If you have covering...

total_worker_time is sum of time taken by all CPU's ,for all the execution of that particular query... see execution_count...If you divide toatl_worker_time by execution_count it will give you average worker...

Run one batch at a time. After first batch run the subquery in if exists to check whether the constraint is dropped or not.

Why do you need the 3rd batch?When...

SQL server stores the query plan for all the select,delete,insert and update statements unless recompile options were used.Sometime you might not see the plan in query if the statement was...

It depends on the usage.Some of cross apply can be written as inner join but if the cross apply is used for a function then it can not be rewritten...

There is some issue with my mahcine so I can not open plans.Hopefully, someone will reply based on your plan

Is...

Still no luck with plan opening.I am opening it in text pad and data is kidn of scrambled.

G...

Looks like the indexes are issues here.

The delete statement is using clustered...