خانه SQL Server توابع COMPRESS و DECOMPRESS در SQL Server 2016 SQL Server افزایش سرعت SQL Server نوشته شده توسط: سید محمد حسینی تاریخ انتشار: ۰۹ اسفند ۱۳۹۴ آخرین بروزرسانی: ۱۱ اردیبهشت ۱۴۰۱ زمان مطالعه: 6 دقیقه ۰ (۰) مقدمه فشرده سازی داده ها تا نسخه SQL Server 2014 برای فشرده سازی در سطح سطر و ستون در دسترس می باشد، ولی این قابلیت فقط با فعال کردن فشرده سازی در سطح صفحه(page) یا سطر(row) قابل استفاده است. در SQL Server 2016 CTP3.1، توابعی جهت فشرده سازی داده هایی که به طور مشخص باید فشرده شوند معرفی شده است. در این مقاله بررسی می کنیم که این توابع چگونه می توانند برای ما سودمند باشند. در SQL Server 2016 CTP به طور درونی(builtin) توابعی جهت Compress و Decompress کردن داده ها ارائه شده است. COMPRESS: این تابع داده های ورودی را با استفاده از الگوریتم GZIP فشرده کرده و نتیجه باینری شده را در قالب نوع داده VARBINARY(MAX) باز می گرداند. DECOMPRESS: این تابع داده های باینری و فشرده ورودی را با استفاده از الگوریتم GZIP از حالت فشرده خارج کرده و نتیجه باینری شده را در قالب نوع داده (VARBINARY(MAX باز می گرداند. شکل کلی استفاده از این توابع در زیر ارائه شده است: COMPRESS ( expression ) DECOMPRESS ( expression ) عبارت expression می تواند یکی از نوع های داده ای زیر باشد: nvarchar(n), nvarchar(max), varchar(n), varchar(max), varbinary(n), varbinary(max), char(n), nchar(n), or binary(n) این دو تابع را می توان به صورت زیر مورد استفاده قرار داد: SELECT COMPRESS ('Compress and Decompress Function SQL Server') و در صورتی که خروجی فوق را از حالت فشرده خارج کنیم نتیجه زیر حاصل خواهد شد: SELECT DECOMPRESS (0x1F8B080000000000040073CECF2D284A2D2E5648CC4B5170494D8671DD4AF3924B32F3F31482037D1482538BCA528B004E16B8732B000000) با توجه به اینکه خروجی تابع DECOMPRESS از نوع باینری می باشد، برای دیدن داده اصلی که قبلا فشرده شده است نیاز به استفاده از تابع CAST-جهت تبدیل داده خروجی باینری به نوع داده مورد نیاز- به صورت زیر داریم: مثال هایی دیگر از توابع COMPRESS و DECOMPRESS حال که با این توابع آشنا شدیم، اجازه دهید یک جدول ایجاد کرده و تعدادی رکورد را با استفاده از تابع COMPRESS در آن درج کنیم. CREATE TABLE dbo.product ( Id INT IDENTITY(1,1), Name NVARCHAR(max), Description VARBINARY(MAX) ) GO INSERT INTO dbo.product (Name, Description) VALUES('TestDemo', COMPRESS(N'This Demo is to show how we can use the new Compress and decompress function in sql server 2016 CTP 3.1 onwards')) حال اگر از دستور SELECT به صورت عادی استفاده شود نتیجه ای مشابه زیر به دست خواهد آمد: همانطور که قبلا گفته شد، برای دیدن متن واقعی باید از تابع CAST استفاده شود. برای اینکار تابع CAST به همراه تابع DECOMPRESS را می توان به صورت زیر مورد استفاده قرار داد: SELECT Id, Name, description, CAST( DECOMPRESS(description) AS NVARCHAR(MAX)) AS DecomressedTest FROM dbo.product میزان فضای ذخیره سازی داده های فشرده شده اجازه دهید بررسی کنیم که این فشرده سازی در کجا می تواند مفید باشد. برای بررسی این موضوع ما سه رشته با طول های مختلف را ایجاد کرده و حجم آنها را پیش/پس از فشرده سازی با هم مقایسه می کنیم. DECLARE @TextToCompress1 VARCHAR(MAX) DECLARE @TextToCompress2 VARCHAR(MAX) DECLARE @TextToCompress3 VARCHAR(MAX) SELECT @TextToCompress1 = 'Data Compression SQL SERVER' SELECT @TextToCompress2 = 'The compression technique which were available with Prior Version of SQL server are Row level and Column Level compression. SQL Server 2016 CTP 3.1 Onwards we have newly introduced functions for Compression. In this Tip we are going to explore and see how this can be beneficial.' SELECT @TextToCompress3='The compression technique which were available with Prior Version of SQL server are Row level and Column Level compression. SQL Server 2016 CTP 3.1 Onwards we have newly introduced functions for Compression. In this Tip we are going to explore and see how this can be beneficial. The COMPRESS function compresses the data provided as the input expression and must be invoked for each section of data to be compressed. This is demo purposes only.' SELECT DATALENGTH(@TextToCompress1) AS 'Before_compression-1', DATALENGTH(COMPRESS(@TextToCompress1)) AS 'After_compression-1', DATALENGTH(@TextToCompress2) AS 'Before_compression-2', DATALENGTH(COMPRESS(@TextToCompress2)) AS 'After_compression-2', DATALENGTH(@TextToCompress3) AS 'Before_compression-3', DATALENGTH(COMPRESS(@TextToCompress3)) AS 'After_compression-3' در اینجا می توان به سادگی دریافت که فشرده سازی برای داده های با طول کوتاه اصلا مناسب نیست، ولی برای داده های با طول زیاد می تواند تا حد زیادی مناسب باشد، بنابراین ما نیاز به بررسی بیشتری جهت فشرده سازی داده ها داریم، زیرا این فشرده سازی در صورتی که به درستی برنامه ریزی نشده باشد می تواند سربار زیادی را تحمیل کند. استفاده از حالت های مختلف CAST به همراه DECOMPRESS همانگونه که گفته شد تابع DECOMPRESS بدون استفاده از تابع CAST قادر نیست داده های اصلی را بازگرداند، به همین علت در صورتی که از نوع داده صحیحی برای تابع CAST استفاده نشود، ممکن است نتیجه درستی بازگردانده نشود. DECLARE @varcharValue NVARCHAR(MAX) = 'SQL Server 2016 Compress and Decompress function' DECLARE @compressedValue VARBINARY(MAX) SET @compressedValue = COMPRESS(@varcharValue) SELECT @varcharValue OriginalValue, CAST(DECOMPRESS(@compressedValue) AS VARCHAR(MAX)) AS Decompress1, CAST(DECOMPRESS(@compressedValue) AS NVARCHAR(10)) AS Decompress2, CAST(DECOMPRESS(@compressedValue) AS NVARCHAR(20)) AS Decompress3, CAST(DECOMPRESS(@compressedValue) AS NVARCHAR(MAX)) AS Decompress4 فضای ذخیره سازی صرفه جویی شده و میزان کارایی به دست آمده با تابع COMPRESS حال اجازه دهید یکی دیگر از جاهایی که فشرده سازی می تواند مناسب باشد را بررسی کنیم. برای این کار، دو جدول ایجاد کرده و مقداری اطلاعات را به صورت عادی و فشرده شده در این دو جدول ذخیره می کنیم و سپس میزان فضای ذخیره سازی مورد نیاز آنها را مقایسه می کنیم: CREATE TABLE Test_uncompress (test varchar(max)) --table to hold uncompressed data CREATE TABLE Test_compressed (test Varbinary(max)) -- table to hold compressed data GO -- Now insert test data into uncompressed table INSERT INTO Test_uncompress values (replicate('DemoCompress and uncompress function',5000)) GO 200000 --Insert data into compressed table by doing compress of values stored in uncompressed table INSERT INTO Test_compressed SELECT compress(test) FROM Test_uncompress اکنون اگر با استفاده از sp_spaceused دو جدول را با هم مقایسه کنیم، نتیجه زیر حاصل می شود: همانطور که مشاهده می کنید حجم جدولی که داده های آن فشرده شده است ۲۰۸۰۸KB و حجم جدولی که داده های آن فشرده نشده است ۱۶۰۰۲۰۰KB می باشد-که تفاوت چشم گیری است. همچنین، اگر از دستور SELECT برای هر دو جدول به صورت ساده استفاده گردد، می توانیم تفاوت کارایی این دو جدول را نیز مشاهده کینم، البته در این مثال از تابع CAST استفاده نشده است که این خود می تواند کمی زمان پرسوجو را افزایش دهد. SET STATISTICS TIME ON SET STATISTICS IO ON GO SELECT TOP 1000 * FROM dbo.Test_uncompress GO SELECT TOP 1000 * FROM Test_compressed GO با توجه به تصویر فوق، از میزان زمان مورد نیاز و همچنین تعداد IO استفاده شده برای خواندن اطلاعات از این دو جدول ، می توان به میزان کارایی فشرده سازی در جداول پی برد. نکته: در انتها باید به این نکته مهم اشاره کنم که داده های فشرده شده نمی توانند در ایندکس استفاده شوند. چه رتبه ای میدهید؟ میانگین ۰ / ۵. از مجموع ۰ اولین نفر باش معرفی نویسنده مقالات 11 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده سید محمد حسینی معرفی محصول مسعود طاهری آموزش ۳ در ۱ Performance Tuning در SQL Server 6.700.000 تومان 5.700.000 تومان مقالات مرتبط ۰۸ شهریور SQL Server اهمیت مانیتورینگ در SQL Server چیست؟ | تمام آنچه که باید از مانیتورینگ بدانید تیم فنی نیک آموز ۰۳ شهریور SQL Server اعمال گواهینامه SSL روی SQL Server تیم فنی نیک آموز ۳۱ مرداد SQL Server دستور Rank در SQL Server ؛ کلید حل معمای رتبه بندی تیم فنی نیک آموز ۲۵ مرداد SQL Server نرم افزار (Microsoft SQL Server Management Studio (SSMS چیست؟ تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ شایان ۱۷ / ۰۲ / ۹۸ - ۰۲:۲۴ سلام آیا تنطیم کردن datacompression page در ایندکس های پرکاربرد باعث خرابی میشن؟ من اجرا کردم و دیدم که logical read خیلی کم شدو سرعت کوئری م بالاتر رفته ولی جای دیگه خوندم که نباید روی جداول یا ایندکس هایی که read ,write بالا دارن اجرا کرد ممنون میشم جواب بدید پاسخ به دیدگاه آرزو محمدزاده ۱۹ / ۰۲ / ۹۸ - ۱۱:۳۹ درود وقت بخیر پاسخ شما در لینک زیر موجود است. http://dl4.nikamooz.com/Ticket/audio2019-05-4b.ogg پاسخ به دیدگاه شایان ۱۷ / ۰۲ / ۹۸ - ۰۲:۲۴ سلام آیا تنطیم کردن datacompression page در ایندکس های پرکاربرد باعث خرابی میشن؟ من اجرا کردم و دیدم که logical read خیلی کم شدو سرعت کوئری م بالاتر رفته ولی جای دیگه خوندم که نباید روی جداول یا ایندکس هایی که read ,write بالا دارن اجرا کرد ممنون میشم جواب بدید پاسخ به دیدگاه آرزو محمدزاده ۱۹ / ۰۲ / ۹۸ - ۱۱:۳۹ درود وقت بخیر پاسخ شما در لینک زیر موجود است. http://dl4.nikamooz.com/Ticket/audio2019-05-4b.ogg پاسخ به دیدگاه Mehdi ۲۳ / ۰۷ / ۹۶ - ۰۶:۴۵ با تشکر از شما Missing indexes Outdated statistics Non-SARGable predicates Implicit conversions Parameter sniffing. Ad hoc non-parameterized queries Inappropriate parallelism و…. در افزایش CPU Usage تاثیر مستقیم دارند. تاملی بودن سایت نیک آموز واقعا یک نقطه ی عطف محسوب می شود. پاسخ به دیدگاه مسعود طاهری ۲۳ / ۰۷ / ۹۶ - ۰۹:۰۸ بسیار عالی مهندس این دو تا لینک می تواند به شما کمک کند یک دوره رایگان https://nikamooz.com/sql-server-download-tutorial-videos-shetab و دوره جامع Perofrmance & Tuning در SQL Server : (شامل این مواردی که شما اشاره کردید می باشد) https://nikamooz.com/product/course-performance-tuning-sql-server/ پاسخ به دیدگاه Mehdi ۲۳ / ۰۷ / ۹۶ - ۰۶:۴۵ با تشکر از شما Missing indexes Outdated statistics Non-SARGable predicates Implicit conversions Parameter sniffing. Ad hoc non-parameterized queries Inappropriate parallelism و…. در افزایش CPU Usage تاثیر مستقیم دارند. تاملی بودن سایت نیک آموز واقعا یک نقطه ی عطف محسوب می شود. پاسخ به دیدگاه مسعود طاهری ۲۳ / ۰۷ / ۹۶ - ۰۹:۰۸ بسیار عالی مهندس این دو تا لینک می تواند به شما کمک کند یک دوره رایگان https://nikamooz.com/sql-server-download-tutorial-videos-shetab و دوره جامع Perofrmance & Tuning در SQL Server : (شامل این مواردی که شما اشاره کردید می باشد) https://nikamooz.com/product/course-performance-tuning-sql-server/ پاسخ به دیدگاه Mehdi ۲۲ / ۰۷ / ۹۶ - ۰۵:۴۵ با سپاس فراوان از شما : در این لینک : https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/12/08/built-in-functions-for-compressiondecompression-in-sql-server-2016/ این موضوع اومده که : These functions use standard GZip algorithm so you can compress data in application layer/client side and send compressed data to SQL Server, or you can compress data in SELECT query and DECOMPRESS it in client side. هدف از عنوان این موضوع اینه که ما یه سرور داریم که CPU Usage آن به هنگام اجرای دستورات select بالاست (Decompress). از Compression بروی جدول و ایندکس استفاده میشه. افزایش CPU Usage در هنگام Fetch Data چه علتی به غیر Compression میتواند داشته باشد ؟؟ خیلی خیلی ممنون از سایت خوبتون. پاسخ به دیدگاه خانم محمدزاده ۲۳ / ۰۷ / ۹۶ - ۰۵:۱۶ با سلام و عرض ادب به نقل از مهندس مسعود طاهری این مسئله به دلیل استفاده نادرست از ایندکسها و Tune نیودن کوئریهاست. پاسخ به دیدگاه Mehdi ۲۲ / ۰۷ / ۹۶ - ۰۵:۴۵ با سپاس فراوان از شما : در این لینک : https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/12/08/built-in-functions-for-compressiondecompression-in-sql-server-2016/ این موضوع اومده که : These functions use standard GZip algorithm so you can compress data in application layer/client side and send compressed data to SQL Server, or you can compress data in SELECT query and DECOMPRESS it in client side. هدف از عنوان این موضوع اینه که ما یه سرور داریم که CPU Usage آن به هنگام اجرای دستورات select بالاست (Decompress). از Compression بروی جدول و ایندکس استفاده میشه. افزایش CPU Usage در هنگام Fetch Data چه علتی به غیر Compression میتواند داشته باشد ؟؟ خیلی خیلی ممنون از سایت خوبتون. پاسخ به دیدگاه خانم محمدزاده ۲۳ / ۰۷ / ۹۶ - ۰۵:۱۶ با سلام و عرض ادب به نقل از مهندس مسعود طاهری این مسئله به دلیل استفاده نادرست از ایندکسها و Tune نیودن کوئریهاست. پاسخ به دیدگاه Mehdi ۱۹ / ۰۷ / ۹۶ - ۰۴:۳۴ با عرض سلام و خسته نباشید این فانکشن ها بار فشرده سازی را به سمت کلاینت و لایه برنامه می برند (مثلا افزایش CPU Usage)، یا اینکه این بار همچنان سمت سرور SQL است ؟؟ ممنون پاسخ به دیدگاه خانم محمدزاده ۲۰ / ۰۷ / ۹۶ - ۰۶:۰۵ سلام و عرض ادب به نقل از مهندس مسعود طاهری دوست عزیز فشرده سازی سمت سرور انجام میشود. پاسخ به دیدگاه Mehdi ۱۹ / ۰۷ / ۹۶ - ۰۴:۳۴ با عرض سلام و خسته نباشید این فانکشن ها بار فشرده سازی را به سمت کلاینت و لایه برنامه می برند (مثلا افزایش CPU Usage)، یا اینکه این بار همچنان سمت سرور SQL است ؟؟ ممنون پاسخ به دیدگاه خانم محمدزاده ۲۰ / ۰۷ / ۹۶ - ۰۶:۰۵ سلام و عرض ادب به نقل از مهندس مسعود طاهری دوست عزیز فشرده سازی سمت سرور انجام میشود. پاسخ به دیدگاه فرشید علی اکبری ۱۳ / ۱۲ / ۹۴ - ۱۰:۲۷ سلام البته با توجه به تستی که من قبلاً انجام دادم متوجه شدم که اینمورد فقط برای جداول خاصی با حجم دیتاهای کم می تونه کاربرد موثری برای برنامه نویس و یا DBA داشته باشه (به عنوان مثال جدول کاربران و یا از این دست …) در جداولی با تعداد رکوردهای بالا، اینمورد واقعاً Performance بانک اطلاعاتی را تحت تاثیر منفی خودش قرار میده وشاید هم در مواردی منجربه Down شدن سرور در شرکتها و سازمانهای بزرگ با تعداد کاربران زیاد بشه. بنابراین از این تکنیک خوبی هم که مایکروسافت در اختیار ما قرار داده باید مثل سایر تکنیک های دیگه، ازش بجا و درست استفاده کرد. پاسخ به دیدگاه سیدمحمد حسینی ۱۳ / ۱۲ / ۹۴ - ۱۰:۳۵ سلام درست فرمودید اگر از این روش زیاد استفاده شود مطمئنا نتیجه منفی در بر خواهد داشت به عنوان مثال در یک جدول (حتی بزرگ) که فرضا در یک یا دو ستون آن متن طولانی ذخیره میشود مناسب است، چون حجم جدول و در نتیجه IO را کم میکند و بار پردازشی CPU هم زیاد نخواهد بود ولی اگر برای تعداد زیادی از ستون های یک جدول استفاده شود قطعا مناسب نخواهد بود هم از نظر حجم کاهش داده شده که ممکن است جواب برعکس بدهد(که در مقاله به آن اشاره شده است) هم از نظر بار پردازشی تحمیل شده به سیستم پاسخ به دیدگاه مهدی شیشه بری ۱۱ / ۱۲ / ۹۴ - ۰۹:۴۸ مقاله خوبی بود. من به تازگی با این سایت آشنا شدم. فقط یک پیشنهاد:” ای کاش نسخه قابل چاپ مقالات هم وجود داشته باشد.”با سپاس پاسخ به دیدگاه فرشید علی اکبری ۱۳ / ۱۲ / ۹۴ - ۱۰:۱۱ دوست عزیر اگه از کروم استفاده میکنید با استفاده از Ctrl+P میتونید خروجی PDF از مقالات سایتها تهیه کنید و اگه از فایرفاکس استفاده میکنید باید افزونه مورد نظر رو به اون اضافه کنید تا این امکان بهتون داده بشه. وگرنه پیشنهاد داشتن خروجی PDF از این سایت خیلی وقته قدیمی شده و متاسفانه با توجه به درخواست های مکرر کاربران، هنوز هم هیچ اقدامی در اینمورد صورت نگرفته. موفق باشید. پاسخ به دیدگاه فرید طاهری ۱۳ / ۱۲ / ۹۴ - ۱۰:۳۶ سلام دوستان. متاسفانه بحث کانورت به PDF مثل آب خوردن نیست مشکلاتی وجود دارد. با چند برنامه نویس وب حرفه ای کار کردیم که نتوانستن اوکی کنند. اگر از دوستان کسی هست که برنامه نویس حرفه ای وردپردس هست لطفا با ما تماس بگیرد. در دنیای وب هم پلاگینی وجود ندارد برای اینکار البته هست ولی اصلا جوابگوی کار ما نیست. با تشکر پاسخ به دیدگاه 1 2