Hot answers tagged

3

It sounds like your architecture has gone past partitioning and is sharded. If you have system software or an application framework in place which can handle sharding that is where this requirement should be implemented. In order to get the correct sort sequence you will have to have the results from all involved shards in the same place at the same time. ...


3

The use case of "purge after 30 days" is an excellent use of PARTITIONing. (It is one of only 4 use cases that I know of.) PRIMARY KEY(id, date) ... PARTITION BY RANGE (TO_DAYS(date)) (...) Then every day do ALTER TABLE .. DROP PARTITION ... ALTER TABLE .. REORGANIZE PARTITION future INTO PARTITION ..., -- preparing for tomorrow ...


2

The eager spool has to do with the size of data you're deleting. In this case, you're updating a large quantity of rows on a table with several indexes. You're also updating a field that's covered by a lot of nonclustered indexes, and SQL Server has to update all of those indexes as well. To avoid this operation, update less rows (as a percentage of the ...


2

@wBob got it in one. It's all about those NULLs. The following script creates the Destination table with a NOT NULL modified column and it alters the same column on the TableName_Partition2 to be NOT NULL after creation. This allows the SWITCH to run. CREATE TABLE [dbo].[SourceTable]( [Col1] [bigint] NULL, [Col2] [int] NULL, [Col3] [int] NULL, ...


1

Got an answer from Pivotal - I should have realised this, but parameterised queries are planned in ignorance of the parameter value, and partition elimination is part of the query plan, so we can't use variables for partition elimination. I am re-tooling our partition elimination to use views that are dynamically constructed with an IN list of dates. This ...



Only top voted, non community-wiki answers of a minimum length are eligible