April 24, 2012 at 8:00 am
Wait, partitioned?
Why not use the moving partition approach then? You don't delete anything. That's exactly what partitions are all about.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 24, 2012 at 8:13 am
Grant Fritchey (4/24/2012)
Wait, partitioned?Why not use the moving partition approach then? You don't delete anything. That's exactly what partitions are all about.
How come our bell didn't ring seeing the Table scan predicate ? :ermm:
( see attachment )
ref for Sliding Window technique: http://technet.microsoft.com/en-us/library/ms160743.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 25, 2012 at 1:17 am
Sorry - I did say in my first post about the partition - just reading about the sliding window now
April 25, 2012 at 2:35 am
JokerUK (4/25/2012)
Sorry - I did say in my first post about the partition - just reading about the sliding window now
Indeed. ( no offense intended here, nor in -any- of my replies ! )
Apparently we - still - didn't actually take that into account, not even when the sqlplan has been provided.
That's one of the reasons we often request the full table definition.
In this case partition function and partition schema might have our bells being hit more clearly and you might have been pointed to that alternative a bit sooner.
Make sure you test the sliding window technique so you really get it and know its strengths and caveats !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 25, 2012 at 7:54 am
Thanks Johan,
I couldn't possibly take offence where people are actually trying to help me.
I'll keep you posted.
Regards
Steve
April 25, 2012 at 7:56 am
If the table is partitioned on the Day column, wouldn't it need to be a not null column? and part of the primary key?
April 25, 2012 at 9:03 am
It seems that once again people went with partitioning without really understanding what it can (and cannot) do for them. If you statement is true that you are partitioned by day on this table your data removal AND addition should be simple metadata partition activity. This is actually the PRIMARY reason for partitioning in SQL Server.
If there is something incorrect or a hitch with that activity, then a batched index-seek delete operation will efficiently remove unwanted data without unnecessary blocking. That type of activity has been used for decades.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 25, 2012 at 12:37 pm
Is there a partition for each day? Could it possibly be made as simple as truncating that day's table?
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply