[minhbon]

Hiện mình có 1 reporting database bị đầy, nên cần xóa bớt dữ liệu, chỉ giữ lại data của 2 năm cuối. Cứ mỗi ngày lưu một lát cắt (có log number riêng cho từng ngày và date) vào table khoảng 500 ngàn dòng mà không để ý nó bị đầy, tính ra khoảng 1,5 tỉ dòng dữ liệu.

Hiện tại chức năng count đã không còn tác dụng do dữ liệu quá nhiều nên không biết cụ thể tổng số là bao nhiêu. Chạy delete theo từng ngày một thì khá là lâu. Nên chắc phải dùng tới truncate, nhưng mà chỉ biết tới nó mà chưa từng dùng đến.

Có cao thủ MS_SQL nào đã từng dùng đến truncate để xóa một phần dữ liệu cho mình xin ít kinh nghiệm được không ? Xóa toàn bộ dữ liệu trước 2018.

[dreamnight]

Originally Posted by minhbon

Hiện mình có 1 reporting database bị đầy, nên cần xóa bớt dữ liệu, chỉ giữ lại data của 2 năm cuối. Cứ mỗi ngày lưu một lát cắt (có log number riêng cho từng ngày và date) vào table khoảng 500 ngàn dòng mà không để ý nó bị đầy, tính ra khoảng 1,5 tỉ dòng dữ liệu.

Hiện tại chức năng count đã không còn tác dụng do dữ liệu quá nhiều nên không biết cụ thể tổng số là bao nhiêu. Chạy delete theo từng ngày một thì khá là lâu. Nên chắc phải dùng tới truncate, nhưng mà chỉ biết tới nó mà chưa từng dùng đến.

Có cao thủ MS_SQL nào đã từng dùng đến truncate để xóa một phần dữ liệu cho mình xin ít kinh nghiệm được không ? Xóa toàn bộ dữ liệu trước 2018.

Solution simple nhất là bạn dùng SELECT INTO dữ liệu cần giữ vào 1 table khác xong truncate cái table cũ đi rồi rename table mới . Tuy nhiên phải chia trường hợp ra phụ thuộc vào tình trạng table của bạn:
1) Có index cột nào ko? Vd CreatedDate desc, thì dùng SELECT INTO nhanh vì nó đã được index rồi.
2) Xui chả có index gì. Đây là trường hợp dễ dính nhất cho nhiều hệ thống mà dev ko phải DBA. Lúc này cũng là solution trên nhưng bạn phải viết store procedure để chạy batch theo từng tháng để ko bị out memory. Vd chạy 1/2018 rồi 2/2018.
3) Trường hợp xui nhất hệ mặt trời là có quá nhiều foreign keys (3 trở lên). Trường hợp này khiến việc truncate hay delete trở nên cực hình. Lời khuyên là remove key ra hết rồi chấp nhận hi sinh table đó mất reference keys. Rồi cũng SELECT INTO qua table mới.

Cả 3 trường hợp bạn phải check log size thì lúc này dễ ăn đạn vì ko kiểu soát được số dòng được kéo qua table mới. Tốt nhất hãy switch qua chế độ Simple Recovery rồi kéo qua.

Cách phức tạp hơn là dựa vào log file để recovery. Tuy nhiên cách này performance sẽ chậm, đổi lại an toàn cho hệ thống database server bạn ko bị out memory hay crash vì foreign keys. Keyword: Log shipping, cái này để bạn tự google để cho thêm kiến thức DBA nhé

[minhbon]

Originally Posted by dreamnight

Solution simple nhất là bạn dùng SELECT INTO dữ liệu cần giữ vào 1 table khác xong truncate cái table cũ đi rồi rename table mới . Tuy nhiên phải chia trường hợp ra phụ thuộc vào tình trạng table của bạn:
1) Có index cột nào ko? Vd CreatedDate desc, thì dùng SELECT INTO nhanh vì nó đã được index rồi.
2) Xui chả có index gì. Đây là trường hợp dễ dính nhất cho nhiều hệ thống mà dev ko phải DBA. Lúc này cũng là solution trên nhưng bạn phải viết store procedure để chạy batch theo từng tháng để ko bị out memory. Vd chạy 1/2018 rồi 2/2018.
3) Trường hợp xui nhất hệ mặt trời là có quá nhiều foreign keys (3 trở lên). Trường hợp này khiến việc truncate hay delete trở nên cực hình. Lời khuyên là remove key ra hết rồi chấp nhận hi sinh table đó mất reference keys. Rồi cũng SELECT INTO qua table mới.

Cả 3 trường hợp bạn phải check log size thì lúc này dễ ăn đạn vì ko kiểu soát được số dòng được kéo qua table mới. Tốt nhất hãy switch qua chế độ Simple Recovery rồi kéo qua.

Cách phức tạp hơn là dựa vào log file để recovery. Tuy nhiên cách này performance sẽ chậm, đổi lại an toàn cho hệ thống database server bạn ko bị out memory hay crash vì foreign keys. Keyword: Log shipping, cái này để bạn tự google để cho thêm kiến thức DBA nhé

Mình đang chạy cái solution đó, cơ mà select into cũng từng ngày nó mới chịu, vì hết thì chắc khoảng 360 mil dòng dữ liệu, quá nhiều nó cũng không chịu.

[dreamnight]

Originally Posted by minhbon

Mình đang chạy cái solution đó, cơ mà select into cũng từng ngày nó mới chịu, vì hết thì chắc khoảng 360 mil dòng dữ liệu, quá nhiều nó cũng không chịu.

Thế thì chấp nhận đi ko có cách nào nhanh hơn đâu. 360m records là con số nhiều cần tốn time để tính toán sự toàn vẹn dữ liệu mà. Muốn speed up thì scale up cái server lên thôi chứ ko cũng ko nhanh hơn được. Ngày trước mình phục hồi 500m cũng cả ngày trời, phải cách ly server production để nó chạy full load đó. Database ko nhanh được đâu vì nhanh nó sai là hỏng data ngay

[minhbon]

Originally Posted by dreamnight

Thế thì chấp nhận đi ko có cách nào nhanh hơn đâu. 360m records là con số nhiều cần tốn time để tính toán sự toàn vẹn dữ liệu mà. Muốn speed up thì scale up cái server lên thôi chứ ko cũng ko nhanh hơn được. Ngày trước mình phục hồi 500m cũng cả ngày trời, phải cách ly server production để nó chạy full load đó. Database ko nhanh được đâu vì nhanh nó sai là hỏng data ngay

Thanks thím, đang cách ly, cắt toàn bộ import, mai thứ 7 mà chắc cũng phải làm việc rồi, để thứ 2 hi vọng còn kịp đưa nó vào lại hoạt động.

[minhbon]

Mà truncate cả cái table 1,5 tỉ dòng dữ liệu - khoảng 1 Tb dữ liệu thì liệu nó có cho phép không thím ? vừa chạy vừa lo vì nếu nó không cho phép truncate là thành công cốc.

[dreamnight]

Originally Posted by minhbon

Mà truncate cả cái table 1,5 tỉ dòng dữ liệu - khoảng 1 Tb dữ liệu thì liệu nó có cho phép không thím ? vừa chạy vừa lo vì nếu nó không cho phép truncate là thành công cốc.

Truncate là delete force đó, bao đi hết, chưa bao giờ gặp trường hợp truncate ko được trong đời

[ez-aqua]

Originally Posted by minhbon

Hiện mình có 1 reporting database bị đầy, nên cần xóa bớt dữ liệu, chỉ giữ lại data của 2 năm cuối. Cứ mỗi ngày lưu một lát cắt (có log number riêng cho từng ngày và date) vào table khoảng 500 ngàn dòng mà không để ý nó bị đầy, tính ra khoảng 1,5 tỉ dòng dữ liệu.

Hiện tại chức năng count đã không còn tác dụng do dữ liệu quá nhiều nên không biết cụ thể tổng số là bao nhiêu. Chạy delete theo từng ngày một thì khá là lâu. Nên chắc phải dùng tới truncate, nhưng mà chỉ biết tới nó mà chưa từng dùng đến.

Có cao thủ MS_SQL nào đã từng dùng đến truncate để xóa một phần dữ liệu cho mình xin ít kinh nghiệm được không ? Xóa toàn bộ dữ liệu trước 2018.

Mình không chắc mình hiểu đúng không, nhưng mà nếu mình nghĩ giải pháp table partition có thể giúp bạn.

Với table partition, ví dụ bạn đánh partition theo năm. Rồi bạn có thể truncate từng mảnh của partition. (Ví dụ truncate các partion của năm <= 2018) Bạn xem thử xem.

B. Truncate Table Partitions
Applies to: SQL Server ( SQL Server 2016 (13.x) through current version)

The following example truncates specified partitions of a partitioned table. The WITH (PARTITIONS (2, 4, 6 TO 8)) syntax causes partition numbers 2, 4, 6, 7, and 8 to be truncated.

TRUNCATE TABLE PartitionTable1
WITH (PARTITIONS (2, 4, 6 TO 8));
GO

https://docs.microsoft.com/en-us/sql...l-server-ver15

Theo mình biết thì partition sinh ra là chủ yếu để cho mấy việc maintenance kiểu này. Chứ để tăng tốc query chỉ là phụ thôi (Partition làm chưa biết nhanh hơn ra sao nhưng tác dụng phụ tràn ngập, vd như Index Seek trên bảng có nhiều partition thì tăng query cost vl luôn). Bên mình dữ liệu không lớn như nên mình nghiên cứu với thử toàn để cho biết chứ ít không có cơ hội xài.

Mà mình nghĩ 1,5 tỉ dòng, bạn đang xài SQL phiên bản bao nhiêu, hay đang xài Azure SQL cloud. Bạn có nghĩ đến giải pháp dùng columnstore để tiết kiệm bộ nhớ + index scan độ thần thánh không (cực kì phù hợp với các hệ thống data warehouse nếu không có yêu cầu đặc biệt, như update liên miên chẳng hạn). Chứ trong mắt mình 1 tỉ rưỡi dòng mà xài columnstore thì không lớn lắm đâu. 1355308

[minhbon]

Originally Posted by ez-aqua

Mình không chắc mình hiểu đúng không, nhưng mà nếu mình nghĩ giải pháp table partition có thể giúp bạn.

Với table partition, ví dụ bạn đánh partition theo năm. Rồi bạn có thể truncate từng mảnh của partition. (Ví dụ truncate các partion của năm <= 2018) Bạn xem thử xem.



https://docs.microsoft.com/en-us/sql...l-server-ver15

Theo mình biết thì partition sinh ra là chủ yếu để cho mấy việc maintenance kiểu này. Chứ để tăng tốc query chỉ là phụ thôi (Partition làm chưa biết nhanh hơn ra sao nhưng tác dụng phụ tràn ngập, vd như Index Seek trên bảng có nhiều partition thì tăng query cost vl luôn). Bên mình dữ liệu không lớn như nên mình nghiên cứu với thử toàn để cho biết chứ ít không có cơ hội xài.

Mà mình nghĩ 1,5 tỉ dòng, bạn đang xài SQL phiên bản bao nhiêu, hay đang xài Azure SQL cloud. Bạn có nghĩ đến giải pháp dùng columnstore để tiết kiệm bộ nhớ + index scan độ thần thánh không (cực kì phù hợp với các hệ thống data warehouse nếu không có yêu cầu đặc biệt, như update liên miên chẳng hạn). Chứ trong mắt mình 1 tỉ rưỡi dòng mà xài columnstore thì không lớn lắm đâu. 1355308

Đúng rồi, trước đó mình tìm kiếm các giải pháp thì biết cái truncate có thể dùng được, và partition thì có thể xóa một phần, nhưng partition thế nào thì lại khá ít tư liệu.

Nó là MS_SQL và mới update lên bản 2014. Server được dùng cho việc reporting nên chỉ có import không có update. Được build từ hơn 10 năm trước nên việc chuyển đổi chắc không thể. Thực sự đến lúc mình kiểm tra thấy gần 1 Tbs cho dữ liệu SQL cũng giật cả mình, hiện free space còn có gần 100 Gb, bọn quản lý server làm full backup hàng ngày mà cũng đéo kêu ca gì cả.

[ez-aqua]

Originally Posted by minhbon

Đúng rồi, trước đó mình tìm kiếm các giải pháp thì biết cái truncate có thể dùng được, và partition thì có thể xóa một phần, nhưng partition thế nào thì lại khá ít tư liệu.

Nó là MS_SQL và mới update lên bản 2014. Server được dùng cho việc reporting nên chỉ có import không có update. Được build từ hơn 10 năm trước nên việc chuyển đổi chắc không thể. Thực sự đến lúc mình kiểm tra thấy gần 1 Tbs cho dữ liệu SQL cũng giật cả mình, hiện free space còn có gần 100 Gb, bọn quản lý server làm full backup hàng ngày mà cũng đéo kêu ca gì cả.

Mình làm với Azure SQL cloud, toàn version mới nhất nên ko biết nhiều với mấy version cũ, nhưng 2014 thì gay đấy, mình thấy cái hướng dẫn truncate partition trên nó ghi dành cho SQL Server 2016. Trên cloud thì vụ truncate partition mình từng test thử rồi, làm OK. Partition sinh ra để chuyên trị ba cái trò rebuild, reorganize 1 phần mà.

Cả columnstore mà xài tốt thì theo mình biết cũng phải từ 2016 trở lên. Nghe vậy thôi chứ mình chưa có trải nghiệm qua columnstore của SQL các version thấp.

Bảng 1.5 tỉ row thì chắc chắn phải là bảng fact rồi, mà nếu đã là bảng fact thì đâu ai link với nó đâu, sợ gì vụ foreign key nhỉ.

Cái vụ phải select ngày báo lỗi là chính xác báo lỗi gì vậy bác. Ko biết server mấy bạn ra sao chứ mình chơi trên cloud, plan cỡ trung thôi mà quất select into bảng tạm mấy chục triệu row còn được mà. Ít nhất cũng phải select được theo năm chứ nhỉ.

1355308