Ideally you want to be able to recover the db if either drive fails, or is completely destroyed even.

Currently only "A" gives you that.

In theory "B" might work, if every...

There is no such thing as an accurate, global freespace percentage. Freespace needs to be set index by index. Thus, a generic rebuild should never specify a fillfactor,...

Trim functions deal only with spaces. The easiest way to take care of all whitespace is to use REPLACEs, something like this:

LEFT OUTER JOIN LedgerGroups lg ON

...

Verify that the db does not have autoclose or autoshrink set to on.

I'd shrink the log file:

DBCC SHRINKFILE ( 2, 4096 )

Rick Todd (2/10/2015)

---

Nevyn (2/10/2015)

---

What is the cash_pickups table? Seems to be in the plan but not the query? Is one of those tables a view? Or do you maybe have...

Also, verify that you have enough free log space on that db already allocated to handle all the deletes.

Make sure you have nonclustered indexes on:

transaction_hdr ( float_no )

transaction_tenders ( float_no )

You want SQL to scan the floats table, not use an index, since it will delete > 70%...

You might want to consider putting each client schema in a different db, then using synonyms in the main db to point to those objects. It will look to...

;WITH cteCustRecentOrders AS (

SELECT

AA.[CustomerCode],

AA.[OrderDate],

AA.CustomerProductID,

AA.Rank_ID

FROM (

...

True, I...

In the log file?

Yeah, that should be plenty, so the statement should run as fast it can.

Are there triggers, replication or...

I'd cluster the table by:

Payroll_Year, PayPeriod, EmployeeID and, optionally, CompanyID

That way the clustering key is generally increasing, which will reduce fragmentation. That's not the main criterion for a clus...

That statement is going to have the scan the table regardless of what you do.

The critical thing for performance is to pre-allocate (and thus pre-format) enough log space for the...

Yes. Why risk not recompiling it?

Get the query plan id and remove all plans related to this proc from the cache using:

DBCC FREEPROCCACHE { plan_handle }