1 / 33

Code Camp NZ 2011

Code Camp NZ 2011. # CCNZ. www.mscommunities.co.nz. Minimal Logging & Data Manoeuvring On very Large Tables. What is going on here???. Steven Wang. Senior DBA/Senior BI Specialist at BNZ MCITP/MCTS: BI Developer, Database Developer and Database Administrator

fiorenza-gorman
Download Presentation

Code Camp NZ 2011

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Code Camp NZ 2011 #CCNZ www.mscommunities.co.nz

  2. Minimal Logging & Data Manoeuvring On very Large Tables What is going on here???

  3. Steven Wang • Senior DBA/Senior BI Specialist at BNZ • MCITP/MCTS: BI Developer, Database Developer and Database Administrator • Owner of TellYes Data Intelligence • Blogs: www.msbicoe.com • Email: stevenwang@msbicoe.com

  4. Agenda • What Is minimal logging and why does it matter? • What can be minimally logged? • Metadata-Only Operations • Things under the hood (Demo) • Data Manoeuvring technics on very large tables • Real world example • Q & A

  5. Small Quiz A minimally logged operation will always reduce the transaction log backup size? A minimally logged operation will always reduce the transaction log size? A minimally logged operation is always faster than fully logged operation ? True False True False True False

  6. Answer A minimally logged operation will always reduce the transaction log backup size? A minimally logged operation will always reduce the transaction log size? A minimally logged operation is always faster than fully logged operation? True False True False True False

  7. What is Minimal Logging and why does it matter? • SQL server uses ‘write-ahead logging’. Everything has to be written on to log file first • A minimally logged operation is one that does not always log every row; it only logs the extend allocations • Transaction log will fill rapidly under full recovery model, it is likely to be grown during a large quantity of data operation • “The transaction log is ALWAYSzero initialized when first created, manually grown, or auto-grown.” --Paul Randal

  8. What can be minimally logged? • SELECT INTO …. FROM Table • Bulk Import Operation: • BCP • BULK Insert • INSERT INTO … SELECT FROM OPENROWSET(BULK…) • Create/Rebuild Index • Using the .Write in the Update for new data • The Creation of new heap by Dropping Index

  9. Conditions • It has to be under Bulk-Logged or Simple Recovery Model • Table is not being replicated • Tablock hint needs to be used • Something talking about later

  10. Conditions (Continue) Heap + Tablock

  11. Conditions (Continue) Clustered + Tablock

  12. Conditions (Continue) • Use Trace Flag 610 • Can be used to get minimal logging for empty heap and clustered table • Can be used to get minimal logging in a non-empty B-Tree • Tablock hint is not needed for table without nonclustered index • The first page is always fully logged • Can be turned on instance-wide or session-wide • Fully tested in your environment before use

  13. Metadata-Only Operations • Truncate Table • Drop Table • Partition Switch • Partition Merge for 2 empty partitions • Partition Split for an empty partition

  14. Things Under the Hood (Demo)

  15. Data Manoeuvring technics on very large tables • Bulk Load Partitioned Table • Use parallelism, per bulk load per CPU core • Create staging tables having exactly same structure as target table in same filegroup with no indexes • Bulk load data into staging tables • Create indexes and constraints on staging tables. • Switch the staging tables into partitioned table partitions

  16. AlterdatabaseMyDBSetrecoveryBulk_Logged; Go AlterDatabaseMyDBModifyFilegroupFG1Default; GO SelectCol1,Col2,Col3,… IntoStage_1 FromTarget_TableWhere 0=1 Go ... Go AlterDatabaseMyDBModifyFilegroupFG4Default; GO SelectCol1,Col2,Col3,… IntoStage_4 FromTarget_TableWhere 0=1 Go

  17. Create Indexes; Apply other settings to comply with target; Create constraints;

  18. AlterTableStage_1SWITCHTOMyTargetTablePartition 1; Go ... Go AlterTableStage_4SWITCHTOMyTargetTablePartition 4;

  19. Data Manoeuvring technics on very large tables (Continues) • Delete a very large amount of rows in a table, for example, over 50% of the total • Don’t Delete! Think of using truncate or drop table instead. • How??

  20. Data Manoeuvring technics on very large tables (Continues) • Update a very large amount of rows in a table for majority of columns • Don’t Update! Think of using inserting data instead to achieve minimal logging. Really????

  21. Drop MyData • Truncate MyData_New • Rename MyData_Temp to MyData

  22. I believe that you have already got a fairly good idea how to apply the similar technical to perform the update on partitioned table.

  23. Real World Example • A Table has to be partitioned daily • The data has to be kept more than 7 years • Every first day of the month a partition merge operation will be performed to consolidate one month’s partitions which is 30 month old to one partition • How will you do it?

  24. Thank You • Email: stevenwang@msbicoe.com • Blogs: www.msbicoe.com • Linkedin: http://nz.linkedin.com/pub/steven-wang/1b/ab/b8b

  25. Thanks to our sponsorsand partners! Sponsor Premier Partners Associated Partners Supporting Partners

More Related