وحید نصیری وحید نصیری
مطالب
معرفی OLTP درون حافظه‌ای در SQL Server 2014
OLTP درون حافظه‌ای، مهم‌ترین ویژگی جدید SQL Server 2014 است. موتور بانک اطلاعاتی disk based اس کیوال سرور، حدود 15 تا 20 سال قبل تهیه شد‌ه‌است و موتور جدید درون حافظه‌ای OLTP آن، بزرگترین بازنویسی این سیستم از زمان ارائه‌ی آن می‌باشد و شروع این پروژه به 5 سال قبل بر می‌گردد. علت تهیه‌ی آن نیز به نیازهای بالای پردازش‌های همزمان مصرف کنندگان این محصول در سال‌های اخیر، نسبت به 15 سال قبل مرتبط است. با استفاده از امکانات OLTP درون حافظه‌ای، امکان داشتن جداول معمولی disk based و جداول جدید memory optimized با هم در یک بانک اطلاعاتی میسر است؛ به همراه مهیا بودن تمام زیرساخت‌هایی مانند تهیه بک آپ، بازیابی آن‌ها، امنیت و غیره برای آن‌ها.



آیا جداول بهینه سازی شده‌ی برای حافظه، همان DBCC PINTABLE منسوخ شده هستند؟

در نگارش‌های قدیمی‌تر اس کیوال سرور، دستوری وجود داشت به نام DBCC PINTABLE که سبب ثابت نگه داشتن صفحات جداول مبتنی بر دیسک یک دیتابیس، در حافظه می‌شد. به این ترتیب تمام خواندن‌های مرتبط با آن جدول، از حافظه صورت می‌گرفت. مشکل این روش که سبب منسوخ شدن آن گردید، اثرات جانبی آن بود؛ مانند خوانده شدن صفحات جدیدتر (با توجه به اینکه ساختار پردازشی و موتور بانک اطلاعاتی تغییری نکرده بود) و نیاز به حافظه‌ی بیشتر تا حدی که کل کش بافر سیستم را پر می‌کرد و امکان انجام سایر امور آن مختل می‌شدند. همچنین اولین ارجاعی به یک جدول، سبب قرار گرفتن کل آن در حافظه می‌گشت. به علاوه ساختار این سیستم نیز همانند روش مبتنی بر دیسک، بر اساس همان روش‌های قفل گذاری، ذخیره سازی اطلاعات و تهیه ایندکس‌های متداول بود.
اما جداول بهینه سازی شده‌ی برای حافظه، از یک موتور کاملا جدید استفاده می‌کنند؛ با ساختار جدیدی برای ذخیره سازی اطلاعات و تهیه ایندکس‌ها. دسترسی به اطلاعات آن‌ها شامل قفل گذاری‌های متداول نیست و در آن حداقل زمان دسترسی به اطلاعات درنظر گرفته شده‌است. همچنین در آن‌ها data pages یا index pages و کش بافر نیز وجود ندارد.


نحوه‌ی ذخیره سازی و مدیریت اطلاعات جداول بهینه سازی شده برای حافظه

جداول بهینه سازی شده برای حافظه، فرمت ردیف‌های کاملا جدیدی را نیز به همراه دارند و جهت قرارگرفتن در حافظه ودسترسی سریع به آن‌ها بهینه سازی شده‌اند. برخلاف جداول مبتنی بر دیسک سخت که اطلاعات آن‌ها در یک سری صفحات خاص به نام‌های data or index pages ذخیره می‌شوند، اینگونه جداول، دارای ظروف مبتنی بر صفحه نیستند و از مفهوم چند نگارشی برای ذخیره سازی اطلاعات استفاده می‌کنند؛ به این معنا که ردیف‌ها به ازای هر تغییری، دارای یک نگارش جدید خواهند بود و بلافاصله در همان نگارش اصلی به روز رسانی نمی‌شوند.
در اینجا هر ردیف دارای یک timestamp شروع و یک timestamp پایان است. timestamp شروع بیانگر تراکنشی است که ردیف را ثبت کرده و timestamp پایان برای مشخص سازی تراکنشی بکار می‌رود که ردیف را حذف کرده است. اگر timestamp پایان، دارای مقدار بی‌نهایت باشد، به این معنا است که ردیف متناظر با آن هنوز حذف نشده‌است. به روز رسانی یک ردیف در اینجا، ترکیبی است از حذف یک ردیف موجود و ثبت ردیفی جدید. برای یک عملیات فقط خواندنی، تنها نگارش‌هایی که timestamp معتبری داشته باشند، قابل مشاهده خواهند بود و از مابقی صرفنظر می‌گردد.
در OLTP درون حافظه‌ای که از روش چندنگارشی همزمانی استفاده می‌کند، برای یک ردیف مشخص، ممکن است چندین نگارش وجود داشته باشند؛ بسته به تعداد باری که یک رکورد به روز رسانی شده‌است. در اینجا یک سیستم garbage collection همیشه فعال، نگارش‌هایی را که توسط هیچ تراکنشی مورد استفاده قرار نمی‌گیرند، به صورت خودکار حذف می‌کند؛ تا مشکل کمبود حافظه رخ ندهد.


آیا می‌توان به کارآیی جداول بهینه سازی شده برای حافظه با همان روش متداول مبتنی بر دیسک اما با بکارگیری حافظه‌ی بیشتر و استفاده از یک SSD RAID رسید؟
خیر! حتی اگر کل بانک اطلاعاتی مبتنی بر دیسک را در حافظه قرار دهید به کارآیی روش جداول بهینه سازی شده‌ی برای حافظه نخواهید رسید. زیرا در آن هنوز مفاهیمی مانند data pages و index pages به همراه یک buffer pool پیچیده وجود دارند. در روش‌های مبتنی بر دیسک، ردیف‌ها از طریق page id و row offset آن‌ها قابل دسترسی می‌شوند. اما در جداول بهینه سازی شده‌ی برای حافظه، ردیف‌های جداول با یک B-tree خاص به نام Bw-Tree در دسترس هستند.


میزان حافظه‌ی مورد نیاز برای جداول بهینه سازی شده‌ی برای حافظه

باید درنظر داشت که تمام جداول بهینه سازی شده‌ی برای حافظه، به صورت کامل در حافظه ذخیره خواهند شد. بنابراین بدیهی است که نیاز به مقدار کافی حافظه در اینجا ضروری است. توصیه صورت گرفته، داشتن حافظه‌ای به میزان دو برابر اندازه‌ی اطلاعات است. البته در اینجا چون با یک سیستم هیبرید سر و کار داریم، حافظه‌ی کافی جهت کار buffer pool مختص به جداول  مبتنی بر دیسک را نیز باید درنظر داشت.
همچنین اگر به اندازه‌ی کافی حافظه در سیستم تعبیه نشود، شاهد شکست مداوم تراکنش‌ها خواهید بود. به علاوه امکان بازیابی و restore جداول را نیز از دست خواهید داد.
البته لازم به ذکر است که اگر کل بانک اطلاعاتی شما چند ترابایت است، نیازی نیست به همین اندازه یا بیشتر حافظه تهیه کنید. فقط باید به اندازه‌ی جداولی که قرار است جهت قرار گرفتن در حافظه بهینه سازی شوند، حافظه تهیه کنید که حداکثر آن 256 گیگابایت است.


چه برنامه‌هایی بهتر است از امکانات OLTP درون حافظه‌ای SQL Server 2014 استفاده کنند؟

- برنامه‌هایی که در آن‌ها تعداد زیادی تراکنش کوتاه مدت وجود دارد به همراه درجه‌ی بالایی از تراکنش‌های همزمان توسط تعداد زیادی کاربر.
- اطلاعاتی که توسط برنامه زیاد مورد استفاده قرار می‌گیرند را نیز می‌توان در جداول بهینه سازی شده جهت حافظه قرار داد.
- زمانیکه نیاز به اعمال دارای write بسیار سریع و با تعداد زیاد است. چون در جداول بهینه سازی شده‌ی برای حافظه، صفحات داده‌ها و ایندکس‌ها وجود ندارند، نسبت به حالت مبتنی بر دیسک، بسیار سریعتر هستند. در روش‌های متداول، برای نوشتن اطلاعات در یک صفحه، مباحث همزمانی و قفل‌گذاری آن‌را باید در نظر داشت. در صورتیکه در روش بهینه سازی شده‌ی برای حافظه، به صورت پیش فرض از حالتی همانند snapshot isolation و همزمانی مبتنی بر نگارش‌های مختلف رکورد استفاده می‌شود.
- تنظیم و بهینه سازی جداولی با تعداد Read بالا. برای مثال، جداول پایه سیستم که اطلاعات تعاریف محصولات در آن قرار دارند. این نوع جداول عموما با تعداد Readهای بالا و تعداد Write کم شناخته می‌شوند. چون طراحی جداول مبتنی بر حافظه از hash tables و اشاره‌گرهایی برای دسترسی به رکوردهای موجود استفاده می‌کند، اعمال Read آن نیز بسیار سریعتر از حالت معمول هستند.
- مناسب جهت کارهای data warehouse و ETL Staging Table. در جداول مبتنی بر حافظه امکان عدم ذخیره سازی اطلاعات بر روی دیسک سخت نیز پیش بینی شده‌است. در این حالت فقط اطلاعات ساختار جدول، ذخیره‌ی نهایی می‌گردد و اگر سرور نیز ری استارت گردد، مجددا می‌تواند اطلاعات خود را از منابع اصلی data warehouse تامین کند.


محدودیت‌های جداول بهینه سازی شده‌ی برای حافظه در SQL Server 2014

- تغیر اسکیما و ساختار جداول بهینه سازی شده‌ی برای حافظه مجاز نیست. به بیان دیگر دستور ALTER TABLE برای اینگونه جداول کاربردی ندارد. این مورد جهت ایندکس‌ها نیز صادق است. همان زمانیکه جدول ایجاد می‌شود، باید ایندکس آن نیز تعریف گردد و پس از آن این امکان وجود ندارد.
تنها راه تغییر اسکیمای اینگونه جداول، Drop و سپس ایجاد مجدد آن‌ها است.
البته باید درنظر داشت که SQL Server 2014، اولین نگارش این فناوری را ارائه داده‌است و در نگارش‌های بعدی آن، بسیاری از این محدودیت‌ها قرار است که برطرف شوند.
- جداول بهینه سازی شده‌ی برای حافظه حتما باید دارای یک ایندکس باشند. البته اگر یک primary key را برای آن‌ها تعریف نمائید، کفایت می‌کند.
- از unique index‌ها پشتیبانی نمی‌کند، مگر اینکه از نوع primary key باشد.
- حداکثر 8 ایندکس را می‌توان بر روی اینگونه جداول تعریف کرد.
- امکان تعریف ستون identity در آن وجود ندارد. اما می‌توان از قابلیت sequence برای رسیدن به آن استفاده کرد.
- DML triggers را پشتیبانی نمی‌کند.
- کلیدهای خارجی و قیود را پشتیبانی نمی‌کند.
- حداکثر اندازه‌ی یک ردیف آن 8060 بایت است. بنابراین از نوع‌های داده‌‌ای max دار و XML پشتیبانی نمی‌کند.
این مورد در حین ایجاد جدول بررسی شده و اگر اندازه‌ی ردیف محاسبه‌ی شده‌ی آن توسط SQL Server 2014 بیش از 8060 بایت باشد، جدول را ایجاد نخواهد کرد.


اگر سرور را ری استارت کنیم، چه اتفاقی برای اطلاعات جداول بهینه سازی شده‌ی برای حافظه رخ می‌دهد؟

حالت DURABILTY انتخاب شده‌ی در حین ایجاد جدول بهینه سازی شده‌ی برای حافظه، تعیین کننده‌ای این مساله است. اگر SCHEMA_ONLY انتخاب شده باشد، کل اطلاعات شما با ری استارت سرور از دست خواهد رفت؛ البته اطلاعات ساختار جدول حفظ خواهد گردید. اگر حالت SCHEMA_AND_DATA انتخاب شود، اطلاعات شما پس از ری‌استارت سرور نیز در دسترس خواهد بود. این اطلاعات به صورت خودکار از لاگ تراکنش‌ها بازیابی شده و مجددا در حافظه قرار می‌گیرند.
حالت SCHEMA_ONLY برای مصارف برنامه‌های data warehouse بیشتر کاربرد دارد. جایی که اطلاعات قرار است از منابع داده‌ی مختلفی تامین شوند.



برای مطالعه بیشتر
SQL Server 2014: NoSQL Speeds with Relational Capabilities  
SQL Server 2014 In-Memory OLTP Architecture and Data Storage
Overview of Applications, Indexes and Limitations for SQL Server 2014 In-Memory OLTP Tables
Microsoft SQL Server 2014: In-Memory OLTP Overview
SQL Server in Memory OLTP for Database Developers
Exploring In-memory OLTP Engine (Hekaton) in SQL Server 2014 CTP1

‫۱۰ سال و ۵ ماه قبل، یکشنبه ۱۱ خرداد ۱۳۹۳، ساعت ۰۵:۲۵
وحید نصیری وحید نصیری
مطالب
به روز رسانی فیلدهای XML در SQL Server - قسمت دوم

قسمت اول را در این آدرس می‌توانید مطالعه نمائید.

در ادامه قسمت اول، اگر بخواهیم نود جدیدی را به فیلد XML موجود اضافه کنیم، روش انجام آن به صورت زیر است (یکی از روش‌ها البته):

DECLARE @tblTest AS TABLE (xmlField XML)

INSERT INTO @tblTest
(
 xmlField
)
VALUES
(
 '<Sample>
    <Node1>Value1</Node1>
    <Node2>Value2</Node2>
    <Node3>OldValue</Node3>
</Sample>'
)

DECLARE @Name NVARCHAR(50)
SELECT @Name = 'Vahid'

UPDATE @tblTest
SET    xmlField.modify(
        'insert element Node4 {sql:variable("@Name")} as last into
 (/Sample)[1]'
    )

SELECT tt.xmlField
FROM   @tblTest tt

که حاصل آن (افزوده شدن یک المان جدید به نام Node4 بر اساس مقدار متغیر Name در انتهای لیست) به صورت زیر می‌باشد:

<Sample>
<Node1>Value1</Node1>
<Node2>Value2</Node2>
<Node3>OldValue</Node3>
<Node4>Vahid</Node4>
</Sample>

سؤال 1 :
اگر بخواهیم نام Node4 نیز متغیر باشد به چه صورتی باید مساله را حل کرد؟
در این حالت باید از کوئری‌های داینامیک استفاده کرد. باید یک رشته را ایجاد (کل عبارت update باید یک رشته شود) و سپس از دستور exec کمک گرفت و البته باید دقت داشت در این حالت کار encoding کارکترهای غیرمجاز در XML را باید خودمان انجام دهیم.

سؤال 2:
اگر بخواهیم نام نودها و مقادیر آن‌ها را به صورت یک جدول معمولی بازگشت دهیم به چه صورتی باید عمل کرد؟

DECLARE @XML AS XML

SELECT @XML = tt.xmlField
FROM   @tblTest tt

SELECT t.c.value('local-name(..)', 'varchar(max)') AS ParentNodeName,
    t.c.value('local-name(.)', 'varchar(max)') AS NodeName,
    t.c.value('text()[1]', 'varchar(max)') AS NodeText

FROM   @XML.nodes('/*/*') AS t(c)

که پس از اجرای آن خواهیم داشت:

ParentNodeName - NodeName - NodeText
Sample           Node1     Value1
Sample           Node2     Value2
Sample           Node3     OldValue
Sample           Node4     Vahid

‫۱۵ سال و ۱ ماه قبل، پنجشنبه ۳۰ مهر ۱۳۸۸، ساعت ۰۳:۳۶
وحید نصیری وحید نصیری
اشتراک‌ها
محاسبه‌ی میزان پیچیدگی کدهای قسمت‌های مختلف برنامه
// <Name>Aggregate Type Complexity</Name>
from t in Application.Types
 
let aggregateTypeCC = t.MethodsAndContructors.Sum(m => m.CyclomaticComplexity / 10)
let rawCC = t.MethodsAndContructors.Sum(m => m.CyclomaticComplexity)
 
// optional optimization if not comparing rawCC
// where aggregateCC >= 10
 
orderby aggregateTypeCC descending 
select new { t, aggregateTypeCC, rawCC }
محاسبه‌ی میزان پیچیدگی کدهای قسمت‌های مختلف برنامه
‫۶ سال و ۲ ماه قبل، سه‌شنبه ۳۰ مرداد ۱۳۹۷، ساعت ۲۰:۰۸
وحید نصیری وحید نصیری
نظرات مطالب
پردازش داده‌های جغرافیایی به کمک SQL Server و Entity framework
باید نزدیک‌ترین آدرس‌ها را یافت:
var searchLocation = DbGeography.FromText(String.Format("POINT({0} {1})", longitude, latitude));
var nearbyLocations = 
    (from location in _context.GeoLocations
     where  // (Additional filtering criteria here...)
     select new 
     {
         LocationID = location.ID,
         // ... 
         Distance = searchLocation.Distance(
             DbGeography.FromText("POINT(" + location.Longitude + " " + location.Latitude + ")"))
     })
    .OrderBy(location => location.Distance)
    .ToList();
‫۷ سال و ۴ ماه قبل، یکشنبه ۴ تیر ۱۳۹۶، ساعت ۱۷:۱۲
رضا منصوری رضا منصوری
نظرات مطالب
Url Routing در ASP.Net WebForms
سلام.
این روتینگ رو تعریف میکنید.
 RouteTable.Routes.MapPageRoute("Gallery", "Page/{PageName}", "~/Main.aspx");
در صفحه‌ی Main.aspx
string pi = Page.RouteData.Values["PageName"] + "" ;
Pages page = (from p in context.Pages where p.PageName == pi select p).FirstOrDefault();
if (page ==  null )
        Response.Redirect("/");
  else
    // نمایش اطلاعات
‫۱۰ سال و ۲ ماه قبل، سه‌شنبه ۲۸ مرداد ۱۳۹۳، ساعت ۱۵:۱۱
مهمان مهمان
نظرات مطالب
یافتن تداخلات Collations در SQL Server
آریان
میشه اینطوری هم انجام داد
SELECT sys.tables.name AS TableName , sys.columns.name AS ColumnName ,
sys.columns.is_nullable , sys.columns.collation_name
FROM sys.columns
INNER JOIN sys.tables ON sys.columns.object_id=sys.tables.object_id
WHERE sys.columns.collation_name<>(CAST(DATABASEPROPERTYEX(DB_NAME() , 'Collation') AS NVARCHAR(1000)))
‫۱۴ سال و ۱۲ ماه قبل، یکشنبه ۱ آذر ۱۳۸۸، ساعت ۲۰:۵۲
وحید نصیری وحید نصیری
نظرات مطالب
شروع به کار با EF Core 1.0 - قسمت 13 - بررسی سیستم ردیابی تغییرات
متد EF.Property در Selectها هم قابل استفاده‌است: 
var items = (from p in context.Categories
                select new  
                {  
                    Id = p.Id,  
                    Name = p.Name,  
                    DateAdded = EF.Property<DateTime>(p, "DateAdded")  
                }).ToList();

و یا خارج از کوئری اصلی توسط Change Tracking API:
var cList = context.Categories
        .OrderBy(b => EF.Property<DateTime>(b, "DateAdded")).ToList();
foreach (var cat in cList)
{
   Console.Write("Category Name: " + cat.CategoryName);
   Console.WriteLine(" Created: " + context.Entry(cat).Property("DateAdded").CurrentValue);
}
‫۵ سال و ۴ ماه قبل، یکشنبه ۲۹ اردیبهشت ۱۳۹۸، ساعت ۱۵:۵۴
مهمان مهمان
نظرات مطالب
آشنایی با Window Function ها در SQL Server بخش چهارم
ممنون از پاسختون، الان متوجه تفاوتشون شدم.
ستون older_method باید مقادیرش دقیقا مشابه با first_value شما باشد:
Select EmployeeId,Name,Salary,HireDate,
       First_VALUE(HireDate) OVER(ORDER BY Salary RANGE BETWEEN UNBOUNDED PRECEDING
                               AND UNBOUNDED FOLLOWING) AS First,   
   D.HireDate AS older_method
FROM Employees
CROSS APPLY (SELECT TOP 1 HireDate
   FROM Employees 
  --WHERE E1.EmployeeId = E2.EmployeeId
  ORDER BY Salary ASC) AS D
ORDER BY EmployeeId;


‫۱۱ سال و ۱۰ ماه قبل، جمعه ۲۹ دی ۱۳۹۱، ساعت ۱۶:۵۳
وحید نصیری وحید نصیری
نظرات مطالب
آموزش MDX Query - قسمت شانزدهم – استفاده از تابع Filter در MDX Query ها
با استفاده از ADO.NET نمی‌توان کوئری‌های MDX را مستقیما اجرا کرد. برای اینکار نیاز به Microsoft.AnalysisServices.AdomdClient.dll هست. برای دریافت آن به صفحه‌ی Microsoft® SQL Server® 2012 Feature Pack مراجعه و قسمت Microsoft® SQL Server® 2012 ADOMD.NET را در صفحه جستجو کنید. برای نگارش 2008 به صفحه‌ی Microsoft® SQL Server® 2008 R2 Feature Pack مراجعه و در آن Analysis Management Objects را دریافت کنید. برای SQL Server 2005 نیز در اینجا پروایدر ADOMD قابل دریافت است.
بعد برای استفاده از آن خواهید داشت:
using (AdomdConnection conn = 
             new AdomdConnection("Data Source=tfsDB;Initial Catalog=Tfs_Analysis; MDX Compatibility=1;")) 
    {  
        conn.Open();  
        using (AdomdCommand cmd = new AdomdCommand(" ....... mdxQuery here ....... ", conn))  
        {  
            DataSet ds = new DataSet();  
            ds.EnforceConstraints = false;  
            ds.Tables.Add();  
            DataTable dt = ds.Tables[0];  
            dt.Load(cmd.ExecuteReader());  
            return dt;  
        }  
    }
و یا پروژه MdxClient - ADO.NET data provider for MDX queries سعی کرده این روش را تبدیل به یک پروایدر جدید ADO.NET کند. نکته‌ی جالب این پروایدر امکان تعریف مقادیر دریافتی از کاربر به صورت پارامتر است در کوئری‌ها تعریف شده. فایل readme آن‌را برای مشاهده یک سری مثال در مورد نحوه‌ی تعریف پارامترها و دریافت داده‌های ارسالی از طریق کاربر، مطالعه کنید.
‫۱۰ سال و ۹ ماه قبل، یکشنبه ۲۹ دی ۱۳۹۲، ساعت ۱۳:۵۴
مهمان مهمان
بازخوردهای پروژه‌ها
خطای sql ؟
سلام آقای نصیری
ممنون بابت این کار با ارزشتون
من تازه با این کتابخونه آشنا شدم و یه مشکل دارم
StartIndex cannot be less than zero.
Parameter name: startIndex 
این مشکل وقتی ایجاد میشه که کد sql رو تغییر میدم
کد من به این صورته:
                        sql: @"SELECT id,name,family,mark
                               FROM Student",
 وقتی یه شرط الکی میذارم فایل بدون مشکل درست میشه  فقط قسمت دیتا یا متن اصلی وجود نداره.
ولی من نمیخوام شرطی بذارم
لطفا راهنماییم کنید!
‫۱۱ سال و ۸ ماه قبل، پنجشنبه ۳ اسفند ۱۳۹۱، ساعت ۰۴:۳۲