همانطور که اطلاع دارید نسخه آزمایشی SQL Server 2016 قرار از تابستان امسال (2015) در دسترس باشد. قابلیت‌های جدیدی به این محصول اضافه شده است. تعدادی از آنها عبارتند از
1- امکان استفاده از Clustered Column Store Index در جداول Memory Optmized
2- َAlways Encrypted
3- پشتیبانی از JSON
4- پشتیبانی از زبان R در SQL Server
و ...
New In-Memory OLTP improvements in CTP3.1 include:
Syntax inputdate AT TIME ZONE timezone.
SQL Server Analysis Services (SSAS) updates allow upgrading your existing models to 1200 compatibility level and a JSON editor for SSDT;
Today we announced the general availability of SQL Server 2016, the world’s fastest and most price-performant database for HTAP (Hybrid Transactional and Analytical Processing) with updateable, in-memory columnstores and advanced analytics through deep integration with R Services. Software applications can now deploy sophisticated analytics and machine learning models in the database resulting in 100x or more speedup in time to insight, compared to deployments of such models outside of the database.
Column Store Index یکی از ویژگیهای جدید SQL Server 2012 می باشد، که کارایی Query های قایل اجرا روی دیتابیس‌های با حجم داده ای بسیار بالا را (که اصطلاحا به آنها Data Warehouse یا انبار داده گویند)، چندین برابر بهبود بخشیده است.
قبل از توضیح در مورد Column Store مختصری در مورد نحوه ذخیره سازی داده‌ها در SQL Server می پردازیم. می‌توان گفت در SQL Server دو روش ذخیره سازی وجود دارد،یکی بصورت ردیفی که اصطلاحا به آن Row Storeیا Row-Wise گویند، و دیگری بصورت ستونی که اصطلاحا به آن Column Store گویند.
در روش ذخیره سازی Row Store، مقادیر ستونها در یک سطر بصورت متوالی ذخیره می‌شوند، در این روش ذخیره سازی از ساختار B-Tree یا Heap استفاده می‌شود.
یادآوری: در ساختار B-Tree، یک گره Root وجود دارد، و گره بعد از Root گره ای است که آدرس گره راست بعدی و آدرس گره چپ بعدی را در خود نگه می‌دارد.
شکل زیر نمای یک درخت B-Tree می‌باشد:
جهت کسب اطلاعات بیشتر درمورد ساختار B-Tree
یادآوری: وقتی در یک جدول، ایندکسی از نوع Clustered ایجاد نماییم، SQL Server، در ابتدا یک کپی از جدول ایجاد و داده‌های جدول را از نو مرتب می‌نماید، و ساختار صفحه ریشه و دیگر صفحات را ایجاد می‌کند و سپس جدول اصلی را حذف می‌نماید. به جدولی که Clustered Index ندارد، اصطلاحا Heap گویند.
برخلاف ذخیره سازی Row Store، در ذخیره سازی Column Store، داده‌ها بصورت ستونی ذخیره می‌شوند،در این روش داده ها، فشرده سازی می‌شوند و اینکار باعث می‌شود،در زمان درخواست یک Query، نیاز به Disk I/o به حداقل برسد، در نتیجه، زمان و سرعت پاسخگویی به پرس و جو‌ها بسیار افزایش می‌یابد.
شکل زیر نحوه ذخیره سازی داده ها،بصورت Row Store را نمایش می‌دهد:
شکل بالا ذخیره سازی داده ها، در ساختار B-Tree یا Heap را نمایش می‌دهد، در شکل فوق یک جدول چهار ستونی با N سطر (Row) در نظر گرفته شده است.بطوریکه ستونهای هر Row بطور متوالی در یک صفحه (Page) یکسان ذخیره می‌شوند.
شکل زیر نحوه ذخیره سازی داده ها،بصورت Column Store را نمایش می‌دهد:
مطابق شکل،ستونهای مربوط به هر Row،همگی در یک صفحه (Page) یکسان ذخیره شده اند. به عنوان مثال ستون C1 که مربوط به سطر اول (Row1) می‌باشد، با ستون C1 که مربوط به سطر دوم (Row2) می‌باشد، در یک ستون و در یک صفحه (Page1) ذخیره شده اند، و الی آخر ...
سئوال: یکبار دیگر به هردو شکل با دقت نگاهی بیاندازید، عمده تفاوت آنها در چیست؟
جواب: درست حدس زدید، تفاوت بارز بین دو روش Column Store و Row Store در نحوه ذخیره سازی داده‌ها می‌باشد. بطور مثال، فرض کنید،در روش ذخیره سازی Row Store، به دنبال مقادیری از ستون C2 می‌باشید، SQL Server میبایست کل رکوردهای جدول (منظور همه Rowها در همه Page ها)را Scan نماید، تا مقادیر مربوط به ستون C2 را بدست آورد.درحالیکه در روش ذخیره سازی Column Store، جهت یافتن مقادیر ستون C2، نیازی به Scan نمودن کل جدول نیست،بلکه SQL Server فقط به Scan نمودن ستون دوم (C2) یا Page2 بسنده می‌نماید.همین امر باعث افزایش چندین برابری، زمان پاسخگویی به هر Query می‌شود.
سئوال: در روش ذخیره سازی Column Store، چگونه مصرف حافظه بهینه می‌شود؟
جواب: واضح است، که در روش SQL Server، Row Store مجبور است، برای بدست آوردن داده‌های مورد نظرتان،کل اطلاعات جدول را وارد حافظه نماید(اطلاعات اضافه ای که به هیچ وجه بدرد، نتیجه پرس و جوی شما نمی‌خورد)، و شروع به Scan داده‌های مد نظر شما می‌نماید.بطوریکه در روش SQL Server، Column Store، فقط ستون داده‌های مورد پرس و جو را در حافظه قرار می‌دهد.(در واقع فقط داده هایی را در حافظه قرار می‌دهد، که شما به آن نیاز دارید)،بنابراین،طبیعی است که در روش Column Store مقدار حافظه کمتری نسبت به روش Row Store در هنگام اجرای Query استفاده می‌شود. به عبارت دیگر می‌توان گفت که در روش Column Store به دلیل، به حداقل رساندن استفاده از Disk I/o سرعت و زمان پاسخگویی به پرس و جو‌ها چندین برابر می‌شود.
برای درک بیشتر Row Store و Column Store مثالی می‌زنیم:
فرض کنید،قصد بدست آوردن ستونهای C1 و C2 از جدول A را داریم، بنابراین خواهیم داشت:
Select C1, C2 from A
روش Row Store:
در این روش همه صفحات دیسک (مربوط به جدول A) درون حافظه قرار داده می‌شود، یعنی علاوه بر ستونهای C1 و C2، اطلاعات مربوط به ستونهای C3 و C4 نیز درون حافظه قرار می‌گیرد،بطوریکه مقادیر ستونهای C3 و C4 به هیچ وجه مورد قبول ما نیست، و در خروجی پرس و جوی ما تاثیری ندارد، و فقط بی جهت حافظه اشغال می‌نماید.
روش Column Store:
در این روش فقط صفحات مروبط به ستون C1 و C2 در حافظه قرار می‌گیرد.(منظور Page1 و Page2 می‌باشد) بنابراین فقط اطلاعات مورد نیاز در خروجی، در حافظه قرار می‌گیرد.
چه موقع می‌توانیم از Column Store استفاده نماییم:
در تعریف Column Store گفته بودم، روش فوق، جهت بهبود بخشیدن به زمان و سرعت پاسخگویی به Queryهای اجرا شده روی دیتابیس‌های با حجم داده ای بسیار بالا(Data Warehouse ) می‌باشد، به بیان ساده‌تر Column Store را روی دیتابیس‌های offline یا دیتابیسهایی که صرفا جهت گزارش گیری مورد استفاده قرار می‌گیرند، تنظیم می‌نمایند.در واقع با تنظیم Column Store Index روی Databaseهای بزرگ مانند Databaseهای بانک‌ها که حجم داده ای میلیونی در جداول آنها وجود دارد، سرعت پاسخگویی Query ها، چندین برابر افزایش می‌یابد.
CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name
ON <object> ( column [ ,...n ] )
[ WITH ( <column_index_option> [ ,...n ] ) ]
[ ON {
{ partition_scheme_name ( column_name ) }
| filegroup_name
| "default"
}
]
[ ; ]
<object> ::=
{
[database_name. [schema_name ] . | schema_name . ]
table_name
{
<column_index_option> ::=
{
DROP_EXISTING = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
}
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_MyFirstName_ColumnStore] ON [Test] (Firstname)
ALTER INDEX [IX_MyFirstName_ColumnStore] ON [Test] DISABLE ALTER INDEX [IX_MyFirstName_ColumnStore] ON [Test] Rebuild
USE [WideWorldImporters];
GO
DROP PROCEDURE IF EXISTS [Application].[usp_GetPersonInfo];
GO
CREATE PROCEDURE [Application].[usp_GetPersonInfo]
(@PersonID INT)
AS
SELECT
[p].[FullName],
[p].[EmailAddress],
[c].[FormalName]
FROM [Application].[People] [p]
LEFT OUTER JOIN [Application].[Countries] [c]
ON [p].[PersonID] = [c].[LastEditedBy]
WHERE [p].[PersonID] = @PersonID;
GO IF EXISTS (
SELECT *
FROM sys.server_event_sessions
WHERE [name] = 'QueryPerf')
BEGIN
DROP EVENT SESSION [QueryPerf] ON SERVER;
END
GO
CREATE EVENT SESSION [QueryPerf]
ON SERVER
ADD EVENT sqlserver.sp_statement_completed(
WHERE ([duration]>(1000))),
ADD EVENT sqlserver.sql_statement_completed(
WHERE ([duration]>(1000))),
ADD EVENT sqlserver.query_post_execution_showplan
ADD TARGET package0.event_file(
SET filename=N'C:\Temp\QueryPerf\test.xel',max_file_size=(256))
WITH (
MAX_MEMORY=16384 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF);
GO USE [master]; GO ALTER DATABASE [WideWorldImporters] SET QUERY_STORE = ON; GO ALTER DATABASE [WideWorldImporters] SET QUERY_STORE ( OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 60, INTERVAL_LENGTH_MINUTES = 5, MAX_STORAGE_SIZE_MB = 100, QUERY_CAPTURE_MODE = ALL, SIZE_BASED_CLEANUP_MODE = AUTO, MAX_PLANS_PER_QUERY = 200); GO ALTER DATABASE [WideWorldImporters] SET QUERY_STORE CLEAR; GO
DBCC FREEPROCCACHE; GO
ALTER EVENT SESSION [QueryPerf] ON SERVER STATE = START; GO
SET STATISTICS IO ON; GO SET STATISTICS TIME ON; GO SET STATISTICS XML ON; GO
USE [WideWorldImporters];
GO
EXECUTE [Application].[usp_GetPersonInfo] 1234;
GO
SELECT
[s].[StateProvinceName],
[s].[SalesTerritory],
[s].[LatestRecordedPopulation],
[s].[StateProvinceCode]
FROM [Application].[Countries] [c]
JOIN [Application].[StateProvinces] [s]
ON [s].[CountryID] = [c].[CountryID]
WHERE [c].[CountryName] = 'United States';
GO 
ALTER EVENT SESSION [QueryPerf] ON SERVER STATE = STOP; GO DROP EVENT SESSION [QueryPerf] ON SERVER; GO
SELECT
[qs].[last_execution_time],
[qs].[execution_count],
[qs].[total_elapsed_time],
[qs].[total_elapsed_time]/[qs].[execution_count] [AvgDuration],
[qs].[total_logical_reads],
[qs].[total_logical_reads]/[qs].[execution_count] [AvgLogicalReads],
[t].[text],
[p].[query_plan]
FROM sys.dm_exec_query_stats [qs]
CROSS APPLY sys.dm_exec_sql_text([qs].sql_handle) [t]
CROSS APPLY sys.dm_exec_query_plan([qs].[plan_handle]) [p]
WHERE [t].[text] LIKE '%Countries%';
GO 
USE [WideWorldImporters];
GO
SELECT
[qsq].[query_id],
[qst].[query_sql_text],
CASE
WHEN [qsq].[object_id] = 0 THEN N'Ad-hoc'
ELSE OBJECT_NAME([qsq].[object_id])
END AS [ObjectName],
[qsp].[plan_id],
[rs].[count_executions],
[rs].[avg_logical_io_reads],
[rs].[avg_duration],
TRY_CONVERT(XML, [qsp].[query_plan]),
[rs].[last_execution_time],
(DATEADD(MINUTE, -(DATEDIFF(MINUTE, GETDATE(), GETUTCDATE())),
[rs].[last_execution_time])) AS [LocalLastExecutionTime]
FROM [sys].[query_store_query] [qsq]
JOIN [sys].[query_store_query_text] [qst]
ON [qsq].[query_text_id] = [qst].[query_text_id]
JOIN [sys].[query_store_plan] [qsp]
ON [qsq].[query_id] = [qsp].[query_id]
JOIN [sys].[query_store_runtime_stats] [rs]
ON [qsp].[plan_id] = [rs].[plan_id]
WHERE [qst].[query_sql_text] LIKE '%Countries%';
GO 
بطور کلی داده کاوی به دو قسمت زیر تقسیم می‌شود:
1- اهداف توصیفی (Descriptive Goal): بدنبال یافتن الگوها و روابط بین داده‌ها هستیم، بدین ترتیب مدلی برای توصیف بهتر داده‌ها بدست خواهد آمد.
2- اهداف پیش بینانه (Predictive Goal): بدنبال انجام پیش بینی با استفاده از الگوها و مدل‌های فوق هستیم.
همچنین مراحل اجرای یک پروژه داده کاوی شامل مراحل زیر است:
1- تحلیل: مهمترین فعالیت در این فاز، فهم عمیق مسئله و شناخت درست مسئله و شناسائی مفاهیم کلیدی (Key Concept) در مسئله است.
2- طراحی: مهمترین فعالیت این فاز، فرموله کردن مسئله با استفاده از مفاهیم کلیدی است.
3- پیاده سازی/ نگهداری و بهبود
محصول
مشترک شرکت‌های SPSS, Teradata, NCR و دایملر- کرایسلر است و یک فرآیند
استاندارد Cross-Industry برای داده کاوی است که به طور گسترده ای استفاده
می‌شود. مراحل کاری در این مدل به شش فاز اصلی به شرح زیر تقسیم می‌شوند:
1. درک پروژه و فهم حوزه کاربرد (Business Understanding):
به طور صریح و آشکار اهداف و نیازمندی‌ها مشخص می‌شود. ترجمه اهداف و
محدودیت آن در قاعده سازی، تعریف مسئله داده کاوی و مهیا کردن استراتژی
اولیه برای نائل شدن به اهداف در این مرحله تعریف می شود.
2. انتخاب داده‌ها (Data Understanding):
این مرحله شامل جمع آوری داده‌ها برای استفاده از تحلیل اکتشافی و مشخص
کردن اطلاعات اولیه برای ارزیابی داده‌های با کیفیت و انتخاب داده‌های
مفید و مورد نیاز می‌باشد.
3. آماده سازی داده‌ها (Data Preparation):
آماده کردن داده‌های اولیه خام به داده‌های نهایی، این دادها در کلیه
مراحل بعدی استفاده می‌شود و از این نظر این مرحله تحلیل و تلاش بیشتری را
می‌طلبد. انتخاب عناصر و شناسههای تحلیل شده را برای کاوش داده‌ها
اختصاص می‌دهیم و با تمیز کردن داده‌های خام آن را برای ابزارهای مدل سازی
آماده می کنیم.
4. مدل سازی (Modeling):
با انتخاب و به کار بستن تکنیک‌های مدل سازی مناسب و روش داده کاوی معین
نتایج مدل سازی را بهینه می کنیم، که در صورت نیاز می‌توانیم با برگشت به
عقب تحلیل مدل سازی را بهینه‌تر نماییم.
5. ارزیابی (Evaluation):
مشخص کردن اینکه آیا مدل انتخابی، ما را به اهدافمان که در اولین مرحله
تعیین کردیم، می رساند. اتخاذ تصمیم راجع به استفاده از نتایج داده کاوی
برای اعتبارسنجی نیز در این مرحله انجام می شود.
6. استقرار (Deployment):
استفاده کردن از مدل ایجاد شده، برای مثال می‌تواند تولید یک گزارش ساده از
خروجی‌ها را نام برد، و برای یک مثال پیچیده تکمیل کردن پردازش داده کاوی
موازی در سایر حوزهها می‌باشد، که این الگوها به یک دانش مفید و قابل
استفاده تبدیل می‌شوند و پس از بهبود آنها، الگوهایی که کارا محسوب می
شوند در یک سیستم اجرایی به کار گرفته خواهند شد.
داده کاوی غالباً به عنوان فرآیند استخراج اطلاعات، الگوها و روندهای موجود در مجموعه ی عظیمی از داده‌ها یاد می شود. این الگوها و روندها را می توان به عنوان یک مدل کاوشی تعریف نمود. به بیانی دیگر ایجاد یک مدل کاوشی بخشی از فرآیند بزرگتری است که در برگیرنده ی همه مراحل؛ از تعریف مسئله که مدل حل خواهد نمود تا اجرای مدل در محیط‌‌های کاری است. می توان این فرآیند را با استفاده از 6 مرحله اساسی زیر تعریف نمود:
باید در نظر داشت که تهیه یک مدل داده کاوی، فرآیندی چرخشی، پویا و تکرار پذیر می باشد و ممکن است هر یک از این مراحل آن قدر تکرار شود، تا مدل مناسبی تهیه گردد.
تعریف روشنی از مشکل و مسئله کسب و کار است. این مرحله شامل تجزیه و تحلیل نیازمندی‌‌های کسب وکار، تعریف دامنه مشکل، تعریف معیارهایی که با آن مدلها ارزیابی خواهد شد و تعریف هدف نهایی پروژه ی داده کاوی است.
یکپارچه سازی و پالایش داده هایی است که در مرحله ی تعریف مسئله فرآیند معین شده است. SSIS حاوی تمامی ابزارهای ملزوم برای تکمیل این مرحله می‌باشد.
به منظور تصمیم گیری‌های مناسب در هنگام تهیه مدل، می بایست داده‌ها را درک نمود و پس از آن می توان تصمیم گیری در مورد وجود داده‌های مخدوش در مجموعه داده و در نهایت استراتژی مناسب برای رفع این مشکلات اتخاذ نمود. Data Source view Designer موجود در BIDS حاوی ابزارهای جامعی برای بررسی و شناخت داده‌ها شامل محاسبه ارقام حداقل و حداکثر، محاسبه میانگین و انحراف معیار و بررسی توزیع داده‌ها می باشد.
پیش از تهیه مدل باید، داده‌ها را به دو دسته ی داده‌های آموزشی و اعتبارسنجی (آزمایشی) تقسیم نمود. از داده‌های آموزشی برای تهیه مدل و از داده‌های اعتبارسنجی برای آزمایش صحت مدل با ایجاد سوالاتی در مورد صحت پیش بینی‌ها استفاده نمود. پس از تعریف ساختار کاوشی، می بایست به پردازش مدل پرداخته شود و ساختارهای خالی با الگوهایی که مدل را توصیف می نمایند، پُر شوند. این مرحله با عنوان آموزش مدل شناخته می شود.
این مرحله شامل بررسی مدل‌های ایجاد شده به منظور آزمودن کارایی آنهاست. می توان مدلها را با ابزارهای موجود در Designer از جمله نمودار صعود و یا ماتریس دسته بندی بررسی نمود.
این مرحله شامل اجرای مدل هایی است که بهترین کارائی را در یک محیط عملیاتی داشته اند. پس از استقرار مدل‌های کاوشی در یک محیط عملیاتی می توان از این مدلها برای پیش بینی هایی بهره گرفت.
SQL Server 2014 DML Triggers are often a point of contention between Developers and DBAs, between those who customize a database application and those who provides it. They are often the first database objects investigated when the performance degrades. They seem easy to write, but writing efficient Trigger, though complex have a very important characteristic: they allow solving problems that cannot be managed in any other application layer. Therefore, if you cannot work without them, in this article you will learn tricks and best practices for writing and managing them efficiently.
USE [WideWorldImporters];
GO
SELECT
[s].[StateProvinceName],
[s].[SalesTerritory],
[s].[LatestRecordedPopulation],
[s].[StateProvinceCode]
FROM [Application].[Countries] [c]
JOIN [Application].[StateProvinces] [s]
ON [s].[CountryID] = [c].[CountryID]
WHERE [c].[CountryName] = 'United States';
GO 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT [cp].[size_in_bytes],
[cp].[cacheobjtype],
[cp].[objtype],
[cp].[plan_handle],
[dest].[text],
[plan].[query_plan]
FROM [sys].[dm_exec_cached_plans] [cp]
CROSS APPLY [sys].[dm_exec_sql_text]([cp].[plan_handle]) [dest]
CROSS APPLY [sys].[dm_exec_query_plan]([cp].[plan_handle]) [plan]
WHERE [dest].[text] LIKE '%StateProvinces%'
OPTION(MAXDOP
1,
RECOMPILE); 
SELECT
[o].[OrderID],
[ol].[OrderLineID],
[o].[OrderDate],
[o].[CustomerID],
[ol].[Quantity],
[ol].[UnitPrice]
FROM [Sales].[Orders] [o]
JOIN [Sales].[OrderLines] [ol]
ON [o].[OrderID] = [ol].[OrderID];
GO 
USE [WideWorldImporters];
GO
SELECT
[CustomerID],
[TransactionAmount]
FROM [Sales].[CustomerTransactions]
WHERE [CustomerID] = 1056;
GO
SELECT
[o].[OrderID],
[ol].[OrderLineID],
[o].[OrderDate],
[o].[CustomerID],
[ol].[Quantity],
[ol].[UnitPrice]
FROM [Sales].[Orders] [o]
JOIN [Sales].[OrderLines] [ol]
ON [o].[OrderID] = [ol].[OrderID];
GO 