DAT309_ Microsoft SQL Server Data Compression

Document Sample
DAT309_ Microsoft SQL Server Data Compression Powered By Docstoc
					  Customer                Data Compression Space Savings        Notes
  Bank Itau               70%                                   PAGE. Data Warehouse application.

  BWIN.com                40%                                   PAGE. OLTP Web application.

  NASDAQ                  62%                                   PAGE. DW application.

  GE Healthcare           38%, 21%                              PAGE, ROW.

  Manhattan Associates    80%, 50%                              PAGE, ROW.

  First American Title    52%                                   PAGE.

  SAP ERP                 50%, 15%                              PAGE, ROW.

  MS Dynamics AX          81%                                   PAGE. ERP application.

  ServiceU                35%                                   PAGE.


Your mileage will vary.
                                                           Page 4
Customer               Performance impact   Notes
BWIN.com               5%                   PAGE compression. OLTP Web application.
                                            Large volume of transactions.
NASDAQ                 40%-60%              PAGE compression. Large sequential range
                                            queries . DW Application.
GE Healthcare          -1%                  PAGE compression. 500 users, 1500
                                            Transactions / sec. OLTP with some reporting
                                            queries.
Manhattan Associates   -11%                 PAGE compression. A lot of insert, update and
                                            delete activity.
First American Title   2% - 3%              PAGE compression. OLTP Application.

MS Dynamics AX         3%                   PAGE compression. ERP application – small
                                            transactions.
Comp
           Empty Space
 TB-2 TB-1 (16 GB) GB)
                            TB-2
                         Empty Space      Compressed
   Empty Space (16
             (14 GB)        4GB
 2GB                      ( 4 GB)          TB-1 (8GB)




                                       Comp
     Empty Space         Comp TB-1
                         Free Space
                              TB-2            Comp TB-1
     TB-1 (16 GB)                       TB-2
       (16 GB)             (( 4 GB
                              4 GB)
                                GB)             4 GB
                                       (2 GB)
               PAGE
Header         ROW
CI structure
   R1
   R2
   R3
   R4
   R5
                 36                                                                                       6




                                                                                                              Table Size after Load (GB)
Time (minutes)




                 24                                                                                       4



                 12                                                                                       2



                 0                                                                                        0
                      NONE                                ROW                                      PAGE
                                                   Compression Type

                             Time to BULK INSERT 50M rows (min)       Table Size after Load (GB)
Index
Index           Index



                        Uncompressed
        Table
        Table
                        PAGE Compressed
                                           Uncompressed
                                           PAGE Compressed
                                           ROW Compressed

Jan-Mar   Apr-June   July-Sept   Oct-Dec
Inputs: sp_estimate_data_compression_savings, dm_db_physical_index_usage_stats, SAP knowledge Computed: S=% scans; U=% updates



                                                      Table Compression Strategies
                Size   ROW     PAGE     1-row           >1-row                                                 %
        Table   (GB)  save %  save %     Read            Read           Update      Delete       Insert       Scan     % Update    Plan          Notes
COSP              398     80%     90%        2,797           58,735       886,187       15,747      584,017      3.80%    57.27%      ROW           Updates!

GLPCA            123     15%     89%            0            929,637           0       16,802        9,020     92.46%      0.0%     PAGE           Scan mostly
                                                     19,0read-mostly                                                                        Light use, but stay
COEP             185     30%     81%            0                 36        2,927           0       48,182     27.14%     4.17%     ROW                low risk
RESB             243     38%     83%        9,837          7,977,629      943,380       1,321       14,877     89.16%    10.54%     ROW               #updates
ACCTIT           210     21%     87%            0                  0            0           0       54,580      0.00%      0.0%     PAGE          Append only
MSEG             183     28%     87%    3,441,918         24,684,252           28           0       70,797     87.54%      0.0%     PAGE           Scan mostly
FAGLFLEXA         98     29%     88%            0                298            0           0       58,882      0.50%      0.0%     PAGE          Append only
BSIS             148     30%     90%            0              9,069           67       5,773       64,366     11.44%     0.08%     PAGE       Append mostly
COSB             150     84%     92%            0                 88            0           0            0    100.00%     0.00%     ROW          ROW ~=PAGE
GLFUNCA           40     15%     89%            0                  6            0           0            0    100.00%     0.00%     PAGE             Read Only

  ROW ~= PAGE => ROW                                                   Read-only => PAGE
  High Update, Low Scan => ROW
  High Scan => PAGE
  Append Only => PAGE
Unicode Compression in SQL2008R2
http://www.unicode.org/reports/tr6/tr6-4.html



   Comparison of UNICODE compression with SCSU and UTF-8
   Locale                     SCSU                         UTF-8
   English                    0.5                          0.5
   Japanese                   .85                          1.0
   Korean                     1.0                          1.0
   Turkish                    .52                          .53
   German                     .5                           .5
   Vietnamese                 0.61                         0.68
   Hindi                      0.5                          1.0
Unicode data is compressed. For most locales 50% saving
Supported types NVARCHAR and NCHAR but not NTEXT
                                 HEADER

0x53514C “SQL”
                                                          Col-prefix


0x53514C534552564552 “SQLSERVER”
0x03534552564552 “?SERVER”
0x00530051004C005300450052005600450052 “SQLSERVER”


0x53514C454E47494E45 “SQLENGINE”
0x 03454E47494E45 “?ENGINE”
0x00530051004C0045004E00470049004E0045 “SQLENGINE”


0x53514C4C4F414445525310 “SQLLOADERS”
0x00530051004C004C004F00410044004500520053 “SQLLOADERS”
0x034C4F414445525310 “?LOADERS”



                      ROW
                      PAGE COMPRESSION
                        ROW Compression Savings with UNICODE Compression
Application                     ROW Compression                   ROW with UNICODE
SAP ERP Benchmark DB            9%                                43%
Dynamics AX                     30%                               53.2%
****                            45%                               64%
****                            30%                               45%


                                      Savings on Hardware Cost
Customer                       Projected Storage Cost Reduction
Microsoft ( MSIT/SAP)          $500 K savings
****                           $500K
http://sqlcat.com/whitepapers/archive/2009/05/29/data-compression-strategy-capacity-planning-and-best-
practices.aspx
www.sqlcat.com
http://blogs.msdn.com/sqlserverstorageengine
http://blogs.msdn.com/sqlcat/
http://blogs.msdn.com/mssqlisv/
http://www.unisys.com/eprise/main/admin/corporate/doc/41371394.pdf
http://search.hp.com/redirect.html?type=REG&qt=sql+server+data+compression&url=http%3A//h71028.www7.hp
.com/ERC/downloads/4AA1-8766ENW.pdf%3Fjumpid%3Dreg_R1002_USEN&pos=1
http://www.netapp.com/us/library/technical-reports/tr-3719.html
www.microsoft.com/teched       www.microsoft.com/learning




http://microsoft.com/technet   http://microsoft.com/msdn
    Sign up for Tech·Ed 2011 and save $500
           starting June 8 – June 31st
http://northamerica.msteched.com/registration




             You can also register at the
    North America 2011 kiosk located at registration
             Join us in Atlanta next year

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:7
posted:2/22/2013
language:English
pages:32