نظارت بر عملکرد و کارایی پایگاه داده با استفاده از Query Store در SQL SERVER 2016

قابلیت  Query Store امکانی را برای مدیران پایگاه‌ داده‌ها فراهم می‌کند تا با بینشی عمیق برای کوئری های خود Execution Plan (طرح پرس و جو)ی مناسبی را در جهت بهبود کارایی و عملکرد پایگاه داده خود انتخاب کنند. این امکان شما را قادر می‌سازد تا با یک عیب‌یابی ساده عملکرد تاثیرات ناشی از تغییرات کوئری‌ها در طرح پرس و جو را ردیابی کرده و با مشاهده تفاوت عملکردها بهترین کوئری را (از لحاظ کارایی) انتخاب کنید؛ این امکان به طور خودکار تاریخچه کوئری ها، طرح کوئری ها و آمار های زمان اجرا را ذخیره و برای استفاده و بازبینی شما نمایش می دهد، داده ها را بر اساس ساعت سیستم (زمان ویندوز) جدا و دسته بندی می‌کند و به شما اجازه می‌دهد الگوهای استفاده از پایگاه داده را مشاهده و تغییرات Execution Plan ها را روی سرور مشاهده کنید.

فعال سازی Query Store:

توسط صفحه Query Store در Management Studio :

1- روی پایگاه داده راست کلیک کرده و گزینه Properties را انتخاب کنید

2- در Database Properties روی گزینه Query Store کلیک کنید

3- در جعبه Enable  گزینه True را انتخاب کنید

توسط دستور ALTER DATABASE :

ALTER DATABASE dataBase_Name SET QUERY_STORE = ON;

بعد از فعال سازی این امکان Object Explorer را Refresh کنید تا بخش Query Store در پایگاه داده شما اضافه شود. بعد از اضافه شدن یکسری توابع، وییو و روال و کوئری ها هستند که با استفاده از آن ها میتوانید Query Store را مدیریت کنید.

هفت وییو برای ارائه اطلاعات درباره Query Store وجود دارد:

sys.database_query_store_options (Transact-SQL)

sys.query_context_settings (Transact-SQL)

sys.query_store_plan (Transact-SQL)

sys.query_store_query (Transact-SQL)

sys.query_store_query_text (Transact-SQL)

sys.query_store_runtime_stats (Transact-SQL)

sys.query_store_runtime_stats_interval (Transact-SQL)

شش Stored Procedure برای پیکربندی Query Store وجود دارد:

sp_query_store_flush_db (Transact-SQL)

sp_query_store_reset_exec_stats (Transact-SQL)

sp_query_store_force_plan (Transact-SQL)

sp_query_store_unforce_plan (Transact-SQL)

sp_query_store_remove_plan (Transct-SQL)

sp_query_store_remove_query (Transact-SQL)

برای اطلاعات بیشتر و تکمیلی به MSDN مراجعه کنید.

امکان جدید بسط جداول در اس کیو ال سرور ۲۰۱۶

در بسیاری از جداول بانکهای اطلاعاتی، اغلب داده هایی که مورد جستجو و بازیابی قرار می گیرند، داده هایی هستند که اخیراً ذخیره شده اند و به ندرت به داده هایی مثلا با قدمت یکسال پیش نیاز داریم.

بنابراین بسیار عقلانی به نظر میرسد که داده های قدیمی را جدا از داده های جدید نگه داریم تا سرعت بازیابی و جستجو و درج اطلاعاتمان بالا رود. از طرفی با ایجاد یک مکانیزم بهینه، کاری کنیم که در صورت جستجوی داده های قدیمی تر، آنها نیز در نتایج ظاهر شوند و کاربر متوجه تفکیک این دو بخش (داده های قدیمی، داده های جدید) نشود.

مایکروسافت در نسخه جدید اس کیو ال سرور یعنی SQL Server 2016‌ این امکان را به مجموعه امکانات خود اضافه کرده است و از سرویس ابری و آنلاین آژور Azure  برای ذخیره داده های قدیمی و از سیستم محلی کاربر برای داده های جدید استفاده می کند. نکته جالب ماجرا اینجاست که با فعال کردن این قابلیت که StretchDB نام دارد و ساخت یک حساب کاربری در سرویس ابری آژور، عملیات انتقال داده ها به سرور آژور به صورت خودکار توسط خود اسکیو ال سرور انجام میگیرد و بازیابی اطلاعات هم به صورت خودکار صورت می پذیرد.

منبع: سایت مهندسی داده

دسته (Batch) در T-SQL

دسته (Batch):مجموعه ی از یک یا چند دستور T-SQL که همواره همراه با هم به سرور ارسال می شوند که هر دسته از دستورات با هم ترجمه و بدنبال هم اجرا میشوند. چنانچه یک دسته ،شامل چندین دستور باشد.

تمام مراحل بهینه سازی لازم روی مجموعه ی این دستورات انجام می شود و پلن اجرائی (Execution plan) بر مبنای مجموعه دستورات موجود در دسته ،تعیین می گردد.

چند نمونه از دسته ها
۱-  همه دستورات موجود در یک تریگر یک دسته محسوب می شود.
۲-رشته ای که توسط یک فرمان EXECUTE اجرا می شود، یک دسته محسوب می شود.
قواعد کار با دسته ها
دستور CREATE در هرجائی بجزء در اولین دستور یک دسته قرار می گیرد، همه دستوراتی که در یک دسته، پس از این دستور قرار می گیرند بعنوان بخشی از همان دستور در نظر گرفته میشوند.
اگر در یک دسته ، ساختار یک جدول تغییر داده شود ، نمی توان در همان دسته از ستونهای جدید استفاده کرد.
اگر دستور EXECUTE اولین دستور یک دسته باشد می توان از نوشتن کلمه کلیدی EXECUTE خود داری کرد.

 

 

چگونگی اجرای دسته ها 
در هر مرحله ارسال دسته ای از دستورات به سرور ،سروردر پنج مرحله  آنها را مورد بررسی و پردازش قرار می دهد که این مراحل به شرح زیر می باشد :

۱-تفکیک نگارشی: دستورات دریافتیاز نظر املا مورد بررسی قرار می گیرند.
۲- تفکیک ارجاعی: اشیاء مورد ارجاء بررسی می شوند تا از وجود آنها  و وجود مجوز دسترسی به آنها برای کاربر اطمینان حاصل شود.
۳- بهینه سازی : طرح اجرای مناسب برای رسیدن به سریعترین مسیر اجرای دستورات را پیدا می کند.
۴- ترجمه :طرح اجرایی ترجمه می شود.
۵- اجرا: طرح ترجمه شده توسط سرور اجرا می شود.

نکته پایانی
قرار دادن تعداد زیادتری دستور در یک دسته ،سرعت اجرای دستورات توسط سرور را افزایش می دهد . زیرا ضمن کاهش عملیات I/O امکان بهینه سازی یهتر نیز بوجود می آید.

منبع: nikamooz.com

امکانات منحصر به فرد SQL Server در نسخه Enterprise

نسخه SQL Server Enterprise چند امکانات داخلی منصر به فرد دارد که توانایی بالا برای بازدهی سریع در سیستم هایی با تراکنشهای بالا و پردازشهای سنگین را دارد. به عنوان مثال سیستمهای «خرید آنلاین» یا «پایانه های فروش» یا سیستمهای گزارشگیری و آنالیزی مانند Credit Reference Information System برای بانکها و بیمه ها که این امکانات و قابلیتهای پنهان در SQL Server Standard Edition موجود نمی باشد.

متاسفانه مسعولان فروش در شرکت مایکروسافت ( به عنوان مثال مایکروسافت مالزی و سنگاپور) تماما در مورد امکانات ظاهری و کلی صحبت می کنند به عنوان مثال در نسخه Enterprise چند پردازنده قابل استفاده است و یا حداکثر حافظه و قابلیتهایی مانند Business Intelligence, High Availability و البته هزینه خرید هر لایسنس برای یک پردازنده فیزیکی ( قیمت هر لایسنس برای Enterprise حدود ۲۶ هزار دلار آمریکا است) اما متاسفانه در یک سوی قضیه مشتری است که هیچ دانشی از امکانات این نرم افزار ندارد و فقط دنبال این است که هزینه لایسنس ها را تا حداکثر پایین بیاورد ولی به چه قیمت ؟ به قیمت اینکه در تراکنشهای بالا سرعت بازدهی پایین آید و امکان از دست دادن مشتریان فراهم شود؟ (البته تمامی این مثالهای در ایران قابل اجرا نیست به هزار و یک دلیل)

داستان:

یک شرکت که خدمات خرید اینترنتی و دستگاه های پایانه فروش ارایه می دهد در سرور اصلی خود به دلیل پایین آوردن هزینه ها از SQL Server Standard Edition استفاده می کند. این شرکت برای بالا بردن کیفیت خدمات خود باید دارای سرور پایگاه داده قوی و پر سرعتی باشد که بتواند تمام نیازهای گزارش گیری در یک زمان را فراهم آورد. بعد از یک سال ارایه خدمات این شرکت دچار مشکلات بزرگی از قبیل حجم بالای پایگاه داده – سرعت بازدهی پایین – نداشتن توانایی آرشیو کردن میلیونها رکرورد در چند ثانیه  بدون هزینه یا وقت اضافی. به طور کلی تمامی این مشکلات با داشتن SQL Server Enterprise Edition بر طرف می شود. پس در نتیجه این شرکت نیاز به مشاوره قبل از خرید لایسنس را داشته ولی چه افسوس که برای این موضوع بسیار دیر است.

اگر SQL Server Enterprise گران است پس در مورد SQL Server Standard چه فکر می کنید؟(برای جواب به ادامه مطلب مراجعه کنید

نحوه ایجاد Column Stored Index در SQL Server 2012

برای ایجاد Column Stored Index ابتدا باید فیلدهای شرکت کننده در ایندکس را تعیین و پس از آن ایندکس را ایجاد نمایید. مثال زیر نحوه انجام اینکار را نمایش می‌دهد.

۱- برای شروع کار یک جدول جدید ایجاد می‌کنیم. این جدول دارای یک Primary Key است که با استفاده از Clustered Index ایجاد شده است. بدلیل اینکه جداول شما از قبل وجود دارند ممکن است این مرحله جزء کار شما نباشد.

USE tempdb

GO

IF OBJECT_ID('Employees', 'U') IS NOT NULL

 DROP TABLE Employees

GO

CREATE TABLE Employees_ColumnBased

(

 Code INT IDENTITY CONSTRAINT PK_Code PRIMARY KEY,

 FirstName NVARCHAR(50),

 LastName NVARCHAR(80),

 HireDate SMALLDATETIME,

 City NVARCHAR(20)

)

GO

۲- در این مرحله تعدادی داده تستی در جدول درج می‌کنیم.

INSERT INTO Employees_ColumnBased (FirstName,LastName,HireDate,City) VALUES

 (N'مسعود',N'طاهری','۲۰۰۰-۰۱-۰۱',N'میانه'),

 (N'فرید',N'طاهری','۲۰۰۳-۰۱-۰۱',N'میانه'),

 (N'احمد',N'غفاری','۲۰۰۳-۰۱-۰۱',N'میانه'),

 (N'خدیجه',N'افروزنیا','۲۰۰۰-۰۱-۰۱',N'تهران'),

 (N'مجید',N'طاهری','۲۰۰۵-۰۱-۰۱',N'تهران')

GO

INSERT INTO Employees_ColumnBased (FirstName,LastName,HireDate,City)

 SELECT FirstName,LastName,HireDate,City FROM Employees_ColumnBased

GO 10

۳- در این مرحله یک Column Stored Index به ازای فیلدهای مورد نیاز ایجاد می‌کنیم. لازم به ذکر است در SQL Server 2012 این نوع ایندکس به شکل Non Clustered Column Stored Index می‌باشد.




CREATE NONCLUSTERED COLUMNSTORE INDEX IX_ColumnStore

 ON Employees_ColumnBased(FirstName,LastName,HireDate)

GO

نکته مهمی که باید در ایجاد این نوع از ایندکس‌ها در نظر گرفت این است که به دلیل افزایش Performance معمولاً در بیشتر مواقع این ایندکس‌ها با توجه به کوئری‌های شما ایجاد می‌گردند. بدین صورتکه

۱- لیست تمامی کوئری‌های پر استفاده به ازای جدول مورد نظر را استخراج نمایید.

۲- کلیه فیلدهای مربوط به مرحله اول را استخراج نمایید.

۳- در صورتیکه فیلدهای استخراج شده در مرحله دوم جزء فیلدهای قابل شرکت در Column Stored Index باشند در ایندکس مورد نظر شرکت نمایند.

۴- در صورتیکه بخواهید Column Stored Index را به صورت ویژوالی در Management Studio ایجاد کافی است مراحل زیر را انجام دهید.

Column Stored Index

Column Stored Index یکی از قابلیت‌های جالبی است که در SQL Server 2012 ارائه شده است. بوسیله این قابلیت می‌توان سرعت اجرایی کوئری‌های را تا چندین برابر بهبود بخشید.

مایکروسافت مهمترین هدف خود را از ارائه این قابلیت افزایش سرعت اجرای کوئری‌های بر روی Data Warehouseهای بزرگ ذکر کرده است.

 اما می‌توان از آن در شرایطی خاص بر روی OLTP نیز استفاده نمود.

قبل از اینکه به معرفی این قابلیت جدید SQL Server بپردازیم باید شما با مفاهیم اولیه درباره Performance Tuning آشنا شوید.

۱- آشنایی با مفهوم Data File و Log File : همانطور که می‌دانید یک بانک اطلاعاتی در SQL Server حداقل از دو فایل (Data File و Log File) تشکیل شده است. در Data File کلیه اشیائی (مانند فیلد، جدول، ایندکس و…) که شما ایجاد می‌کنید ذخیره می‌شود و به طور ساده در Log File هر چیزی که منجر به تغییر این داده‌ها شود ذخیره می‌شود. برای مثال چنانچه شما مقدار رکوردی را تغییر دهید Log عملیات مربوط به تغییر در Log File ثبت می‌گردد. این Log می‌تواند شامل تصویر داده قبل و بعد از تغییر داده و همچنین زمان انجام کار و… باشد.

۲- آشنایی با مفهوم Page : همانطور که قبلاً هم اشاره کردیم داده‌هایی که شما ایجاد کرده‌اید در درون Data File ثبت می‌شود. درون Data File بلوک‌های ۸KB قرار دارد که به این بلوک‌ها Page می‌گویند. در SQL Server بیش از ۱۰ نوع Page وجود دارد. که SQL Server بر حسب نیاز از آنها استفاده می‌کند.

۳- آشنایی با مفهوم Index در SQL Server: به طور خیلی ساده، ایندکس لیست مرتب شده‌ای از داده‌ها است که بر اساس منطقی خاص مانند عدد، رشته، تاریخ و … ایجاد شده است. هدف از ایجاد ایندکس افزایش سرعت جستجو برای بازیابی داده‌ها می‌باشد.

۴- آشنایی با انواع ایندکس‌ها:

در SQL Server  پنج نوع ایندکس به شرح زیر وجود دارد

• Clustered Index: این نوع ایندکس‌ها بر روی داده‌های عادی مانند عدد، رشته، تاریخ و… ایجاد می‌شوند. زمانیکه یک Clustered Index بر روی یک جدول ایجاد می‌کنید ترتیب و چینش فیزیکی رکوردها بر اساس کلید ایندکس خواهد بود. بنابراین یک جدول صرفاً می‌تواند یک ایندکس از نوع Clustered  داشته باشد.

 

• NonClustered Index: این نوع ایندکس‌ها مانند Clustered Indexها بر روی داده‌های عادی مانند عدد، رشته، تاریخ و… ایجاد می‌شوند. زمانیکه یک ایندکس از نوع NonClustered بر روی جدول ایجاد می‌کنید کلید ایندکس در فضایی دیگری جدای از فضای جدول مرتب شده و مورد استفاده قرار می‌گیرد. به طور کلی در SQL Server می‌توان تا ۹۹۹ ایندکس از نوع NonClustered را ایجاد کرد.

 

• XML Index : این نوع ایندکس‌ها بر روی داده‌هایی  که در قالب ساختار XML هستند ایجاد می‌شود.

 

• Spatial Index : این نوع ایندکس‌ها بر روی داده‌هایی که از نوع جغرافیایی هستند ایجاد می‌شود.

 

• Column Stored Index : نوع جدیدی از ایندکس‌ها هستند که در SQL Server 2012 معرفی شده‌اند که سرعت دسترسی به داده‌ها در آن بیش از سایر ایندکس‌ها می‌باشد.

۵- آشنایی با مفهوم Execution Plan : Plan اجرایی یا نقشه اجرایی کوئری ترتیب اجرای فیزیکی دستورات را مشخص می‌نماید. زمانیکه شما کوئری را اجرا می‌کنید SQL Server آن را به همان ترتیبی که نوشته شده است اجرا نمی‌کند. بلکه ترتیب دسترسی به داده‌های موجود در جداول با توجه به پارامترهای مختلف مانند وضعیت ایندکس‌ها، وضعیت منابع سرور و…  خواهد بود.

پس از مشخص شدن ترتیب اجرای فیزیکی SQL Server نقشه آن را با عنوان Execution Plan ذخیره می‌کند تا در آینده بتواند از آن استفاده کند. چنانچه بخواهید نقشه اجرایی با Execution Plan یک کوئری را مشاهده نمایید کافی است که کوئری مورد نظر خود را Highlight نموده و کلید Ctrl+L را فشاره دهید تا Estimate Plan یا نقشه اجرایی تخمینی برای شما نمایش داده شود.

در تصویر زیر نمونه‌ای از Execution Plan یک کوئری نمایش داده شده است.

SELECT * FROM Orders O

 INNER JOIN [Order Details] OD ON O.OrderID=OD.OrderID

 INNER JOIN Employees E ON O.EmployeeID=E.EmployeeI

نکته : Execution Plan از سمت راست به چپ و از بالا به پایین خوانده می‌شود.