best practice recommendations requested after data purge

Question

best practice recommendations requested after data purge

SQLJay

SSC Eights!

Points: 873
More actions
January 15, 2018 at 3:02 pm

#342372

All,
We are in the process of purging large amounts (20+ millions each) data from various tables used by our ERP system.
What would be the best practice tasks to complete to get the best performance from the database?
We are using SQL2008R2 x64, enterprise edition.
Thank you.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

SQLJay - Monday, January 15, 2018 3:02 PM
All,
We are in the process of purging large amounts (20+ millions each) data from various tables used by our ERP system.
What would be the best practice tasks to complete to get the best performance from the database?
We are using SQL2008R2 x64, enterprise edition.
Thank you.

Check fragmentation of your indexes on those tables, rebuild if necessary.
Check the statistics on the tables, update if needed.
And follow whatever practices are recommended by the vendor.

Sue

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Sue_H - Monday, January 15, 2018 4:03 PM
SQLJay - Monday, January 15, 2018 3:02 PM
All,
We are in the process of purging large amounts (20+ millions each) data from various tables used by our ERP system.
What would be the best practice tasks to complete to get the best performance from the database?
We are using SQL2008R2 x64, enterprise edition.
Thank you.
Check fragmentation of your indexes on those tables, rebuild if necessary.
Check the statistics on the tables, update if needed.
And follow whatever practices are recommended by the vendor.
Sue

I've been testing for the last 2+ years and the logical fragmentation depicted by the avg_fragmentation_in_percent column of sys.dm_db_index_physical_stats means little for performance especially with how today's SANs and databases actually operate. MUCH more important is the avg_page_space_used_in_percent especially if you want to avoid the flurry of page splits that occur when a previously defragmented index finally fills the pages and starts fragmenting again.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

SQLJay - Monday, January 15, 2018 3:02 PM
All,
We are in the process of purging large amounts (20+ millions each) data from various tables used by our ERP system.
What would be the best practice tasks to complete to get the best performance from the database?
We are using SQL2008R2 x64, enterprise edition.
Thank you.

As Sue_H said, defragment your indexes but do it based on avg_page_space_used_in_percent instead of avg_fragmentation_in_percent . The other thing to do is to take a performance baseline and find the top 10 worst queries (which are usually NOT the slowest running queries) and fix them. Then complete, do the next 10. Also, check your cache for the top 10 items that always seem to recompile and fix those, as well. They will only have minor differences between multiple singleton cache entries but you'll be able to recognize that they being generated from the same code, usually in the GUI. We had code that would run in 100ms, which I considered to be very slow for what it did, but that paled in comparison to the 2 to 22 seconds that it would take to compile and it would compile EVERY time it was called and it was called thousands of times per hour. Once we fixed it, CPU across 32 core dropped from an average of 22% to just 6-8%.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

SQLJay SSC Eights! Points: 873 More actions · Answer 4

Jeff and Sue,
thank you for the recommendations. We'll start looking into index defragmentation and update statistics tasks.

regards
SQLjay

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

SQLJay - Wednesday, January 17, 2018 9:34 AM
Jeff and Sue,
thank you for the recommendations. We'll start looking into index defragmentation and update statistics tasks.
regards
SQLjay

If you defrag indexes, one of the most important things to do is to determine a reasonable FILL FACTOR for each index depending on how the index is built (Ever increasing key or not? Mutable key or not? Update rate for the key columns? Insert rates for the key columns? Average row size is another serious consideration) or possibly suffer either massive blocking due to massive bad page splits as soon as the index is rebuilt or wasting a whole lot of space that doesn't need to be wasted, which will also affect how much memory is wasted or not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions