public MyContext():base("MyConnectionString") { Database.Log = sql => Debug.Write(sql); }
برای این کار از دادههای موجود در پایگاه داده [AdventureWorksLT2008R2].[SalesLT].[Address] استفاده میکنم .
نمونه خروجی مانند زیر میباشد :
سپس برای تنظیمات Script Component به ترتیب زیر عمل میکنیم :
در قسمت Input column ستون هایی را که به عنوان پارامتر میتوانیم با آنها کار کنیم ، تعریف میکنیم : (دقت شود که تغییر نام متغیرها ، در کدها اعمال میشوند .)
حال میخواهم ستونهای تاریخ میلادی و شمسی را برای خروجی تعریف کنم :
همانطور که مشاهده میکنید ، نوع داده ای برای خروجی را رشته تعریف کردم.
سپس به قسمت script بر میگردیم (سمت چپ پنجره ) و روی Edit Script کلیک میکنیم : (در صورتی که تمایل به کد نویسی با VB را دارید در همین قسمت میتوانید آن را تنظیم کنید)
پنجره ای مانند زیر برای ویرایش کدها ، باز میشود
همانطور که مشاهده میکنید درون این کلاس 4 متد تبدیل تاریخ را پیاده کردم و از آنها در متد input0_processInputRow استفاده کردم . این کار نیازی به پیاده سازی حلقه ندارد و به راحتی میتوان آنها را روی سطرهای دلخواه تعریف کرد . خروجی نمایش داده شده در data viewerها مانند زیر میباشند :
قبل از اعمال تبدیلات :
بعد از اعمال تبدیلات :
اگر تعداد کاراکترهای ناخواسته محدود و مشخص هستند میتوانید با دستور REPLACE آنها را حذف کنید، مثلا میخواهیم هر سه کاراکتر ~!@ از رشته حذف شوند:
DECLARE @s VARCHAR(50) = '~~~~~~!@@@@@@@ salam'; SET @s = REPLACE(REPLACE(REPLACE(@s, '~', ''), '!', ''), '@', ''); SELECT @s AS new_string
ولی هنگامی که کاراکترها نامحدود بوده امکان نوشتن تابع REPLACE به کرات بی معنا است در این حالت باید دنبال روشی پویا و تعمیم پذیر بود.
با جستجویی که در اینترنت انجام دادم متوجه شدم تکنیک WHILE یا همون loop یکی از روشهای رایج برای انجام اینکار هست، که احتمالا به دلیل سهولت در بکارگیری و سادگی آن بوده که عمومیت پیدا کرده است.
مستقل از این صحبتها هدف معرفی یک روش مجموعه گرا (set-based) برای این مساله میباشد.
حذف کاراکترها ناخواسته با تکنیک Recursive CTE
راه حل بر اساس جدول زیر است:
CREATE TABLE test_string (id integer not null primary key, string_value varchar(500) not null); INSERT INTO test_string VALUES (1, '@@@@ #### salam 12345'), (2, 'good $$$$$ &&&&&& bye 00000');
حالا فرض کنید میخواهیم هر کاراکتری غیر از حروف الفبای انگلیسی و فاصله(space) از رشته حذف شود. پس دو داده فوق به صورت salam و good bye در انتها در خواهند آمد.
برای حذف کاراکترهای ناخواسته فوق query زیر را اجرا کنید.
WITH CTE (ID, MyString, Ix) AS ( SELECT id, string_value, PATINDEX('%[^a-z ]%', string_value) FROM test_string UNION ALL SELECT id, CAST(REPLACE(MyString, SUBSTRING(MyString,Ix , 1), '') AS VARCHAR(500)), PATINDEX('%[^a-z ]%', REPLACE(MyString, SUBSTRING(MyString,Ix , 1), '')) FROM CTE WHERE Ix > 0 ) SELECT * FROM cte --WHERE Ix = 0; ORDER BY id, CASE WHEN Ix = 0 THEN 1 ELSE 0 END, Ix;
توضیح query:
در قسمت anchor اندیس اولین کاراکتر ناخواسته (خارج از رنج حروف الفبا و فاصله) بدست میآید. سپس در قسمت recursive هر کاراکتری که برابر باشد با کاراکتر ناخواسته ای که در مرحله قبل بدست آمده از رشته حذف میشود این عملیات توسط تابع replace صورت میگیرد و اندیس کاراکتر ناخواسته بعدی بعد از حذف کاراکتر ناخواسته قبلی بدست میآید که به مرحله بعد منتقل میشود. این مراحل تا آنجایی پیش میرود که دیگر کاراکتر ناخواسته ای در رشته وجود نداشته باشد.
به جدول زیر توجه بفرمایید (خروجی query فوق)
نتیجه مطلوب ما آن دو سطری است که در کادر بنفش هستند. که اگر به ستون Ix اشان توجه کنید مقدارش برابر با 0 است.
لطفا به سطر اول جدول توجه بفرمایید مشاهده میشود که هر 4 کاراکتر @ یکباره از رشته حذف شدند که بدلیل استفاده از تابع REPLACE میباشد.
https://github.com/MehdiSaeedifar/IrisStore
همچنین نمونهی آنلاین آنرا میتوانید در فروشگاه آیریس مشاهده کنید.
در ادامه برخی از قابلیتهای این سیستم را مشاهده میکنید:
جست و جو با قابلیت دسته بندی نتایج
به هنگام جست و جو، لیستی از موارد پیشنهادی به صورت دسته بندی شده نمایش داده میشود.
جست و جوی پیشرفته کالاها
جست و جو بر اساس قیمت، گروه، کلمات کلیدی و مرتب سازی نتایج انجام میگیرد. همچنین نتایج جست و جو بدون رفرش شدن صفحه و به صورت AJAX ای به همراه تغییر URL صفحه صورت میگیرد.
نمایش نمودار تغییرات قیمت
امکان نمایش نمودار تغییرات قیمت کالا در بازهی زمانی نیز پیش بینی شده است.
ویرایش اطلاعات به صورت inline
امکان ویرایش قیمت و تاریخ به صورت inline وجود دارد.
مدیریت تصاویر کالا
در این قسمت امکان آپلود همزمان چندین فایل به همراه پیش نمایش آنها وجود دارد. همچنین امکان کشیدن و رها کردن برای تغییر ترتیب چیدمان عکسها نیز مهیا است.( تصویر اول به عنوان کاور کالا در نظر گرفته میشود.)
قابلیتهای دیگر:
- مدیریت تصاویر اسلایدشو و تغییر ترتیب آنها از طریق کشیدن و رها کردن (drag & drop)
- تعریف برگه و تغییر ترتیب نمایش آنها از طریق کشیدن و رها کردن
- امکان ارسال پست
- تعریف دسته بندی
- مدیریت کاربران
- تعریف تنظیمات سایت
- نمایش کالا و پستهای مشابه
کارهایی که باید انجام شود:
- پیاده سازی سبد خرید و خرید آنلاین
تصویر پنل مدیریت
تصویر صفحهی اصلی:
همچنین به راحتی میتوان با طراحی قالب جدیدی، از این سیستم برای کاری غیر از فروشگاه اینترنتی استفاده کرد؛ سایتهای زیر نمونههای آنلاین دیگری از این سیستم هستند:
- http://www.petrapars.ir
- http://www.ava-tarh.ir
در نهایت فهرستی از کتاب خانهها و فناوریهای استفاده شده و همچنین مقالات مرتبط با این پروژه را قرار دادهام.
کتابخانهها و فریم ورکهای سمت سرور:
فناوری یا کتابخانه | توضیحات | مقالات مرتبط |
Bootstrap 3.x | فریم ورک پایه ای css سایت | - Bootstrap 3 RTL Theme - Twitter Bootstrap -سازگارسازی کلاسهای اعتبارسنجی Twitter Bootstrap 3 با فرمهای ASP.NET MVC -ساخت قالبهای نمایشی و ادیتور دکمه سه وضعیتی سازگار با Twitter bootstrap در ASP.NET MVC -نمایش اخطارها و پیامهای بوت استرپ به کمک TempData در ASP.NET MVC |
AdminLTE | قالب مدیریت سایت | - نسخه راستچین شده AdminLTE 2.2.1 |
Animate.css | انیمیشنهای css3 سایت | |
Font Awesome | پک آیکونهای برداری | |
Awesome Bootstrap Checkbox | زیبا سازی چک باکس ها | |
فونت فارسی وزیر | قلم فارسی | |
لطفا برای طرح سؤالات و پیشنهادات خود و جهت مدیریت بهتر آنها، از قسمت اختصاصی این پروژه در سایت استفاده نمائید.
در انتهای قسمت قبل، نحوهی ایجاد یک جدول جدید با فیلدی از نوع فایل استریم بررسی شد، حال اگر جدولی از پیش وجود داشت، نحوهی افزودن فیلد ویژه مورد نظر به آن، به صورت زیر است:
alter table tbl_files set(filestream_on ='default')
go
alter table tbl_files
add
[systemfile] varbinary(max) filestream null ,
FileId uniqueidentifier not null rowguidcol unique default (newid())
go
در ادامه جدول tblFiles قسمت قبل را در نظر بگیرید:
CREATE TABLE [tblFiles](
[FileId] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[Title] [nvarchar](255) NOT NULL,
[SystemFile] [varbinary](max) FILESTREAM NULL,
UNIQUE NONCLUSTERED
(
[FileId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [fsg1]
ALTER TABLE [dbo].[tblFiles] ADD DEFAULT (newid()) FOR [FileId]
GO
نحوهی افزودن رکوردی جدید به جدول tblFiles :
INSERT INTO [tblFiles]
(
[Title],
[SystemFile]
)
VALUES
(
'file-1',
CAST('data data data' AS VARBINARY(MAX))
)
اگر کنجکاو باشید که این فایل اکنون کجا ذخیره شده و نحوهی مدیریت آن توسط اس کیوال سرور به چه صورتی است، فقط کافی است به مسیری که هنگام افزودن گروه فایلها و فایل مربوطه در تنظیمات خواص دیتابیس در قسمت قبل مشخص کردیم، مراجعه کرد (شکل زیر).
بدیهی است افزودن یک رشته به این صورت کاربرد عملی ندارد و صرفا جهت یک مثال ارائه شد. در ادامه، نحوهی ثبت محتویات یک فایل را در فیلدی از نوع فایل استریم و سپس خواندن اطلاعات آنرا از طریق برنامه نویسی بررسی خواهیم کرد:
using System;
using System.IO;
using System.Data.SqlClient;
using System.Data;
namespace FileStreamTest
{
class CFS
{
/// <summary>
/// افزودن رکورد به جدول حاوی ستونی از نوع فایل استریم
/// </summary>
/// <param name="filePath">مسیر فایل</param>
/// <param name="title">عنوانی دلخواه</param>
public static void AddNewRecord(string filePath, string title)
{
//آیا فایل وجود دارد؟
if (!File.Exists(filePath))
throw new FileNotFoundException(
"لطفا مسیر فایل معتبری را مشخص نمائید", filePath);
//خواندن اطلاعات فایل در آرایهای از بایتها
byte[] buffer = File.ReadAllBytes(filePath);
using (SqlConnection objSqlCon = new SqlConnection())
{
//todo: کانکشن استرینگ باید از یک فایل کانفیگ خوانده شود
objSqlCon.ConnectionString =
"Data Source=(local);Initial Catalog=testdb2009;Integrated Security = true";
objSqlCon.Open();
//شروع یک تراکنش
using (SqlTransaction objSqlTran = objSqlCon.BeginTransaction())
{
//ساخت عبارت افزودن پارامتری
using (SqlCommand objSqlCmd = new SqlCommand(
"INSERT INTO [tblFiles]([Title],[SystemFile]) VALUES(@title , @file)",
objSqlCon, objSqlTran))
{
objSqlCmd.CommandType = CommandType.Text;
//تعریف وضعیت پارامترها و مقدار دهی آنها
objSqlCmd.Parameters.AddWithValue("@title", title);
objSqlCmd.Parameters.AddWithValue("@file", buffer);
//اجرای فرامین
objSqlCmd.ExecuteNonQuery();
}
//پایان تراکنش
objSqlTran.Commit();
}
}
}
/// <summary>
/// دریافت اطلاعات فایل ذخیره شده به صورت آرایهای از بایتها
/// </summary>
/// <param name="fileId">کلید مورد استفاده</param>
/// <returns></returns>
public static byte[] GetDataFromDb(string fileId)
{
byte[] data = null;
using (SqlConnection objConn = new SqlConnection())
{
//کوئری اس کیوال پارامتری جهت دریافت محتویات فایل
string cmdText = "SELECT SystemFile FROM tblFiles WHERE FileId=@id";
using (SqlCommand objCmd = new SqlCommand(cmdText, objConn))
{
//todo: کانکشن استرینگ باید از یک فایل کانفیگ خوانده شود
objConn.ConnectionString =
"Data Source=(local);Initial Catalog=testdb2009;Integrated Security = true";
objConn.Open();
//تنظیم کردن وضعیت و مقدار پارامتر تعریف شده در کوئری
objCmd.Parameters.AddWithValue("@id", fileId);
//اجرای فرامین و دریافت فایل
using (SqlDataReader objread = objCmd.ExecuteReader())
{
if (objread != null)
if (objread.Read())
{
if (objread["SystemFile"] != DBNull.Value)
data = (byte[])objread["SystemFile"];
}
}
}
}
return data;
}
}
}
مثالی در مورد روش استفاده از کلاس فوق :
using System.IO;
namespace FileStreamTest
{
class Program
{
static void Main(string[] args)
{
CFS.AddNewRecord(@"C:\filest05.PNG", "test1");
//آی دی رکورد ذخیره شده در دیتابیس برای مثال
byte[] data = CFS.GetDataFromDb("BB848D45-382C-4D95-BF4E-52C3509407D4");
if (data != null)
{
File.WriteAllBytes(@"C:\tst.PNG", data);
}
}
}
}
در روش دیگری که در اکثر مقالات مرتبط مورد استفاده است، از شیء SqlFileStream کمک گرفته شده و نحوهی انجام آن نیز به صورت زیر میباشد.
در ابتدا دو رویه ذخیره شده زیر را ایجاد میکنیم:
CREATE PROCEDURE [AddFile](@Title NVARCHAR(255), @filepath VARCHAR(MAX) OUTPUT)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ID UNIQUEIDENTIFIER
SET @ID = NEWID()
INSERT INTO [tblFiles]
(
[FileId],
[title],
[SystemFile]
)
VALUES
(
@ID,
@Title,
CAST('' AS VARBINARY(MAX))
)
SELECT @filepath = SystemFile.PathName()
FROM tblFiles
WHERE FileId = @ID
END
GO
CREATE PROCEDURE [GetFilePath](@Id VARCHAR(50))
AS
BEGIN
SET NOCOUNT ON;
SELECT SystemFile.PathName()
FROM tblFiles
WHERE FileId = @ID
END
رویه ذخیره شده GetFilePath نیز تنها مسیر سیستمی فایل استریم ذخیره شده را بر میگرداند.
به این ترتیب کدهای برنامه به صورت زیر تغییر خواهند کرد:
using System.Data.SqlClient;
using System.Data;
using System.Data.SqlTypes;
using System.IO;
namespace FileStreamTest
{
class CFSqlFileStream
{
/// <summary>
/// افزودن رکورد به جدول حاوی ستونی از نوع فایل استریم
/// </summary>
/// <param name="filePath">مسیر فایل</param>
/// <param name="title">عنوانی دلخواه</param>
public static void AddNewRecord(string filePath, string title)
{
//آیا فایل وجود دارد؟
if (!File.Exists(filePath))
throw new FileNotFoundException(
"لطفا مسیر فایل معتبری را مشخص نمائید", filePath);
//خواندن اطلاعات فایل در آرایهای از بایتها
byte[] buffer = File.ReadAllBytes(filePath);
using (SqlConnection objSqlCon = new SqlConnection())
{
//todo: کانکشن استرینگ باید از یک فایل کانفیگ خوانده شود
objSqlCon.ConnectionString =
"Data Source=(local);Initial Catalog=testdb2009;Integrated Security = true";
objSqlCon.Open();
//شروع یک تراکنش
using (SqlTransaction objSqlTran = objSqlCon.BeginTransaction())
{
//استفاده از رویه ذخیره شده افزودن فایل
using (SqlCommand objSqlCmd = new SqlCommand(
"AddFile", objSqlCon, objSqlTran))
{
objSqlCmd.CommandType = CommandType.StoredProcedure;
//مشخص ساختن وضعیت و مقدار پارامتر عنوان
SqlParameter objSqlParam1 = new SqlParameter("@Title", SqlDbType.NVarChar, 255);
objSqlParam1.Value = title;
//مشخص ساختن پارامتر خروجی رویه ذخیره شده
SqlParameter objSqlParamOutput = new SqlParameter("@filepath", SqlDbType.VarChar, -1);
objSqlParamOutput.Direction = ParameterDirection.Output;
//افزودن پارامترها به شیء کامند
objSqlCmd.Parameters.Add(objSqlParam1);
objSqlCmd.Parameters.Add(objSqlParamOutput);
//اجرای رویه ذخیره شده
objSqlCmd.ExecuteNonQuery();
//و سپس دریافت خروجی آن
string Path = objSqlCmd.Parameters["@filepath"].Value.ToString();
//زمینه تراکنش فایل استریم موجود را دریافت کرده و از آن برای نوشتن محتویات فایل استفاده خواهیم کرد
//این مورد نیز یکی از تازههای اس کیوال سرور 2008 است
using (SqlCommand objCmd = new SqlCommand(
"SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", objSqlCon, objSqlTran))
{
byte[] objContext = (byte[])objCmd.ExecuteScalar();
using (SqlFileStream objSqlFileStream =
new SqlFileStream(Path, objContext, FileAccess.Write))
{
objSqlFileStream.Write(buffer, 0, buffer.Length);
}
}
}
objSqlTran.Commit();
}
}
}
/// <summary>
/// دریافت اطلاعات فایل ذخیره شده به صورت آرایهای از بایتها
/// </summary>
/// <param name="fileId">کلید مورد استفاده</param>
/// <returns></returns>
public static byte[] GetDataFromDb(string fileId)
{
byte[] buffer = null;
using (SqlConnection objSqlCon = new SqlConnection())
{
//todo: کانکشن استرینگ باید از یک فایل کانفیگ خوانده شود
objSqlCon.ConnectionString =
"Data Source=(local);Initial Catalog=testdb2009;Integrated Security = true";
objSqlCon.Open();
//شروع یک تراکنش
using (SqlTransaction objSqlTran = objSqlCon.BeginTransaction())
{
//استفاده از رویه ذخیره شده دریافت مسیر فایل
using (SqlCommand objSqlCmd =
new SqlCommand("GetFilePath", objSqlCon, objSqlTran))
{
objSqlCmd.CommandType = CommandType.StoredProcedure;
//مشخص ساختن پارامتر ورودی رویه ذخیره شده و مقدار دهی آن
SqlParameter objSqlParam1 = new SqlParameter("@ID", SqlDbType.VarChar, 50);
objSqlParam1.Value = fileId;
objSqlCmd.Parameters.Add(objSqlParam1);
//اجرای رویه ذخیره شده و دریافت مسیر سیستمی فایل استریم
string path = string.Empty;
using (SqlDataReader sdr = objSqlCmd.ExecuteReader())
{
sdr.Read();
path = sdr[0].ToString();
}
//زمینه تراکنش فایل استریم موجود را دریافت کرده و از آن برای خواندن محتویات فایل استفاده خواهیم کرد
//این مورد نیز یکی از تازههای اس کیوال سرور 2008 است
using (SqlCommand objCmd = new SqlCommand(
"SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", objSqlCon, objSqlTran))
{
byte[] objContext = (byte[])objCmd.ExecuteScalar();
using (SqlFileStream objSqlFileStream =
new SqlFileStream(path, objContext, FileAccess.Read))
{
buffer = new byte[(int)objSqlFileStream.Length];
objSqlFileStream.Read(buffer, 0, buffer.Length);
}
}
}
objSqlTran.Commit();
}
}
return buffer;
}
}
}
FILESTREAM Storage in SQL Server 2008
Resource Governor، اجازه میدهد تا انواع مختلف Session را بر روی Server طبقه بندی کنید که به نوبه خود چگونگی کنترل تخصیص منابع سرور به فعالیت داده شده را به شما اعطا میکند. این قابلیت کمک میکند که ادامه فرآیندهای OLTP تضمین شود و یک عملکرد قابل پیش بینی فراهم میکند تا توسط فرآیندهای غیر قابل پیش بینی، تحت تاثیر منفی قرار نگیرد. با استفاده از Resource Governor، قادر خواهید بود نحوه دستیابی به Session را به منظور محدود کردن منابع خاص برای SQL Server مشخص کنید. به عنوان مثال میتوانید مشخص کنید که بیش از 20 درصد از پردازنده یا منابع حافظه به گزارشهای در حال اجرا اختصاص داده نشود. هنگامیکه این ویژگی فعال باشد، مهم نیست چه تعداد گزارش در حال اجرا است، آنها هرگز نمیتوانند از تخصیص منابع تعیین شده تجاوز کنند. البته این موضوع عملکرد گزارش گیری را کاهش میدهد ولی عملکرد فرآیندهای OLTP حداقل توسط گزارش ها، دیگر تحت تاثیر منفی قرار نمیگیرد.
1- بررسی اجمالی Resource Governor:
Resource Governor، با کنترل تخصیص منابع بر حسب Workload کار میکند. هنگامی که یک درخواست اتصال به موتور بانک اطلاعاتی ارسال میشود درخواست براساس یک تابع رده بندی (Classification function) طبقه بندی میشود. تابع رده بندی یک تابع اسکالر است که از طریق T-SQL تعریف میشود. تابع رده بندی، اطلاعات را درباره یک اتصال (برای مثال login ID، application name، hostname، server role ) ارزیابی میکند، به منظور تشخیص اینکه چگونه آنها را دسته بندی کند. پس از دسته بندی درخواست اتصال، آنها به گروههای حجم کاری (Workload Group) که برای رده بندی تعریف شده اند، شکسته میشوند. هر Workload Group مرتبط با یک مخزن منابع (Resource Pool) است.
یک Resource Pool، منابع فیزیکی SQL Server را نمایش میدهد (در حال حاضر در SQL Server 2008، تنها منابع فیزیکی موجود برای پیکربندی پردازنده و حافظه است) و مقدار حداکثر پردازنده و یا منابع حافظه را که به نوع خاصی از Workload اختصاص داده میشود، تعیین میکند. هنگامی که یک اتصال طبقه بندی شده و در Workload Group صحیح خود قرار میگیرد به این اتصال، پردازنده و منابع حافظه به اندازه نسبت داده شده به آن تخصیص داده میشود و سپس Query به Query Optimizer برای اجرا داده میشود.
2- اجزای Resource Governor:
Resource Governor، از سه قسمت اصلی تشکیل شده است: Classification، Workload Groups و Resource Pools. درک این سه قسمت و چگونگی تعامل آنها به درک و استفاده از Resource Governor کمک میکند.
2-1- Classification:
Classification، فرآیند ارزیابی اتصالات ورودی کاربر و اختصاص آن به یک Workload Group است که توسط منطق موجود در یک تابع تعریف شده توسط کاربر (user-defined function) انجام میشود. تابع نام یک Workload Group را برمی گرداند که Resource Governor از آن برای مسیر دهی Session به Workload Group مناسب استفاده میکند.
هنگامی که Resource Governor پیکربندی میشود فرآیند ورود به سیستم برای یک Session شامل گامهای زیر است:
• Login authentication2-2- Workload Groups:
• LOGON trigger execution
• Classification
Workload Groups، ظروفی برای اتصالات مشابه هستند که با توجه به معیارهای طبقه بندی برای هر اتصال گروه بندی میشوند. Workload Groups همچنین مکانیسمی برای تجمیع نظارت بر روی منابع مصرفی فراهم میکند.
Resource Governor دو Workload Group از پیش تعریف شده دارد: یک گروه داخلی (internal group) و یک گروه پیش فرض (default group).
Internal Workload Group، تنها توسط فرآیندهای داخلی موتور بانک اطلاعاتی استفاده میشود. معیارهای طبقه بندی را برای گروههای داخلی نمیتوانید تغییر دهید و همچنین هیچ یک از درخواستهای کاربران را برای انتقال به گروه داخلی نمیتوانید رده بندی کنید، با این حال بر گروه داخلی میتوانید نظارت کنید.
درخواستهای اتصال به طور خودکار هنگامی که شرایط زیر وجود دارد، به Default Workload Group رده بندی میشوند:
• معیاری برای طبقه بندی درخواست وجود ندارد.Resource Governor، در مجموع 20 عدد Workload Group را پشتیبانی میکند. از آنجائی که دو عدد از آنها برای Workload Groupهای داخلی و پیش فرض ذخیره شده اند در مجموع 18 عدد Workload Group تعریف شده توسط کاربر (user-defined) میتوان تعریف نمود.
• کوششی برای رده بندی درخواستی به گروهی که وجود ندارد.
• خرابی کلی Classification
2-3- Resource pools:
Resource Pool (مخزن منابع)، نشان دهنده تخصیص منابع فیزیکی به SQL Server است. یک Resource Pool از دو بخش تشکیل شده است:
• در بخش نخستین حداقل رزرو منابع را مشخص میکنیم، این بخش از مخزن منابع با مخازن دیگر همپوشانی نمیکند.در 2008 SQL Server مخزن منابع با تعیین حداقل و حداکثر تخصیص CPU و حداقل و حداکثر تخصیص حافظه تنظیم میگردد. با تنظیم حداقل، در دسترس بودن منبع از مخزن تضمین میشود. از آنجائی که در هر رزرو حداقل منابع تداخلی نمیتواند وجود داشته باشد، مجموع مقادیر حداقل در تمام مخازن از 100% کل منابع Server نمیتواند تجاوز کند.
• در بخش دیگر حداکثر ممکن رزرو منابع را برای مخزن مشخص میکنیم، تخصیص منابع با مخازن دیگر مشترک است.
مقدار حداکثر در محدوده بین حداقل و شامل 100% مقدار میتواند تنظیم گردد. تنظیم حداکثر نشان دهنده مقدار حداکثری است که یک Session میتواند مصرف کند، مادامی که منابع در دسترس باشند و توسط مخزن دیگر که با حداقل مقدار غیر صفر پیکربندی شده، استفاده نشود. هنگامی که یک مخزن با حداقل مقدار غیر صفر تعریف شده، مقدار حداکثر موثر از مخزنهای دیگر دوباره تنظیم میشوند، در صورت لزوم حداکثر مقدار موجود از جمع کل حداقل منابع مخازن دیگر کسر میگردد.
برای مثال، دو مخزن تعریف شده توسط کاربر (user-defined) را در نظر بگیرید. مخزن اول Pool1 با مقدار حداقل 20% و مقدار حداکثر 100% تعریف شده، مخزن دیگری Pool2 با مقدار حداقل 50% و مقدار حداکثر 70% تعریف شده است. حداکثر مقدار موثر برایPool1 برابر 50% است (100% منهای مقدار حداقل 50% مخزن Pool2) و حداکثر مقدار موثر برای Pool2، 70% است زیرا حداکثر مقداری است که پیکربندی شده است، گر چه 80% باقی میماند.
بخش مشترکی از مخزن (مقدارش بین مقدار حداقل و مقدار حداکثر موثر است) که برای تعیین مقدار منابع مورد استفاده است، توسط مخزن میتواند مصرف شود اگر منابعی موجود باشد و توسط مخازن دیگر مصرف نشده باشد. هنگامی که منابعی توسط یک مخزن مصرف میشوند، آنها به یک مخزن مشخص نسبت داده میشوند، به بیان دیگر اشتراکی نیستند تا زمانی که فرآیند در آن مخزن به اتمام برسد.
برای توضیح بیشتر یک سناریو که در آن سه مخزن تعریف شده توسط کاربر (user-defined) وجود دارد، را در نظر بگیرید:
PoolA با حداقل مقدار 10% و حداکثر مقدار 100% تعریف میشود.
PoolB با حداقل مقدار 35% و حداکثر مقدار 90% تعریف میشود.
PoolC با حداقل مقدار 30% و حداکثر مقدار 80% تعریف میشود.
مقدار موثر PoolA و مجموع در صد منابع به اشتراک گذاشته PoolA به شرح زیر محاسبه خواهد شد:
( حداکثر مقدار PoolA ) - ( حداقل مقدار PoolB ) - ( حداقل مقدار PoolC ) = ( حداکثر مقدار موثر PoolA )
(حداکثر مقدار موثر PoolA ) – ( حداقل مقدار PoolA ) = ( اشتراک PoolA )
جدول زیر مقدار حداکثر موثر و اشتراکی را برای هر مخزن در این پیکربندی نمایش میدهد:
Internal Pool، منابع مصرف شده توسط فرآیندهای داخلی موتور بانک اطلاعاتی را نشان میدهد. این مخزن تنها شامل گروههای داخلی است و به هیچ وجه قابل تغییر نیست. مخزن داخلی مقدار ثابت حداقل صفر و حداکثر 100% را دارد و مصرف منابع توسط مخزن داخلی، از طریق تنظیمات در هر مخزن دیگر محدود یا کاسته نمیشود.
به عبارت دیگر حداکثر مقدار موثر مخزن داخلی همیشه 100% است. هر workloads در مخزن داخلی برای عملکرد Server حیاتی در نظر گرفته میشود و Resource Governor در صورت لزوم اجازه میدهد تا مخازن داخلی 100% منابع موجود را مصرف کند حتی اگر به معنی نقض نیازمندیهای منابع از سایر مخازن باشد.
Default Pool، اولین مخزن تعریف شده کاربر است. قبل از هرگونه پیکربندی، Default Pool تنها حاوی Default group است. Default Pool نمیتواند ایجاد یا حذف شود اما میتواند تغییر کند. Default Pool علاوه بر Default group میتواند شامل گروههای تعریف شده توسط کاربر (user-defined) نیز باشد.
3- پیکر بندی Resource Governor :
پیکربندی Resource Governor شامل مراحل زیر است:
- فعال کردن Resource Governor3-1- فعال کردن Resource Governor
- ایجاد مخازن منابع (Resource Pools) تعریف شده توسط کاربر (user-defined)
- تعریف Workload Groups و نسبت دادن آن به مخازن
- ایجاد Classification function
- ثبت Classification function به Resource Governor
پیش از اینکه بتوانید یک Resource Pool را ایجاد کنید، نیاز است تا نخست Resource Governor را فعال کنید.
3-2- تعریف Resource Pool
ویژگیهای موجود برای یک Resource Pool عبارتند از:
Name، Minimum CPU %، Maximum CPU%، Min Memory%، Max Memory%
3-3- تعریف Workload Group
پس از اینکه Resource Pool را تعریف کردید، گام بعدی ایجاد یک Workload Group و اختصاص آن به Resource Pool مناسب است. چندین workgroup را میتوان به مخزن (Pool) یکسان نسبت داد اما یک workgroup را به چندین Resource Pool نمیتوان نسبت داد. خواص انتخابی موجود برای Workload Groups به شما اجازه میدهد سطح بهتری از کنترل را روی اجرای دستورات یک Workload Group تنظیم کنید. انتخابهای موجود عبارتند از:
3-3-1- Importance :
اهمیت نسبی (کم، متوسط یا بالا) Workload Group درون Resource Pool را تعیین میکند. اگر چندین Workload Group را در یک Resource Pool تعریف کنید این تنظیمات تعیین میکند که درخواستها در عرض یک Workload Group در اولویت بالاتر یا پائینتری از Workload Groupهای دیگر درون همان Resource Pool اجرا شوند، مقدار متوسط تنظیم پیش فرض است. در حال حاضر فاکتورهای وزنی برای هر تنظیم کم برابر 1، متوسط برابر3 و زیاد برابر 9 است. به این معنی که زمانبند به اجرای Sessionهای درون workgroup هائی با اهمیت بالا، سه برابر بیشتر از workgroupهای با اهمیت متوسط و نه برابر بیشتر از workgroupهای کم اهمیت، مبادرت خواهد کرد.
3-3-2- Maximum Request :
حداکثر تعداد درخواستهای همزمان که اجازه دارند در یک Workload Group اجرا شوند را مشخص میکند. تنظیم پیش فرض، صفر، تعداد نامحدود دستور را اجازه میدهد.
3-3-3- CPU Time :
حداکثر مقدار زمان پردازنده در ثانیه را مشخص میکند که یک درخواست درون Workload Group میتواند استفاده کند. تنظیم پیش فرض، صفر، به معنی نامحدود است.
3-3-4- Memory Grant %:
به صورت در صد، حداکثر مقدار اعطا حافظه برای اجرا (Execution grant memory)، که یک تک دستور از Resource Pool میتواند اخذ کند را مشخص میکند. این درصد نسبی است از مقدار حافظه ای که به Resource Pool نسبت داده میشود. محدوده مجاز مقادیر از 0 تا 100 است. تنظیم پیش فرض 25 است.
Execution grant memory، مقدار حافظه ای است که برای اجرای query استفاده میشود (نه برای Buffer کردن یا cache کردن) که میتواند صرفه نظر از Resource Pool یا Workload Group توسط تعدادی از Sessionها به اشتراک گذاشته شود. توجه شود که تنظیم این مقدار به صفر از اجرای عملیات Hash Join و دستورات مرتب سازی در Workload Groupهای تعریف شده توسط کاربر (user-defined)جلوگیری میکند. همچنین این مقدار توصیه نمیشود بیشتر از 70 باشد زیرا ممکن است Server قادر نباشد، اگر Queryهای همزمان در حال اجرا باشند، حافظه آزاد کافی اختصاص دهد.
3-3-5- Grant Time-out :
حداکثر زمان، به ثانیه، که یک query برای یک منبع منتظر میماند تا در دسترس شود را مشخص میکند. اگر منبع در دسترس نباشد، فرآیند ممکن است با یک خطای time-out مواجه شود. تنظیم پیش فرض، صفر، به معنی این است که سرور time-out را با استفاده از محاسبات داخلی بر مبنای هزینه پرس و جو ( query cost ) با تعیین حداکثر زمان برآورد میکند.
3-3-6- Degree of Parallelism :
حداکثر درجه موازی سازی (DOP) را برای پرس و جوهای موازی تعیین میکند. محدوده مجاز مقادیر از 0 تا 64 است. تنظیم پیش فرض، صفر، به معنی این است که فرآیندها از تنظیمات عمومی استفاده میکنند.
3-4- ایجاد یک Classification function
پس از تعریف Resource Pool و Workload Group، به یک Classification function نیاز است که شامل منطق ارزیابی اتصالات و نسبت دادن آنها به Workload Group مناسب است. Classification function برای هر اتصال Session جدید به SQL Server بکار میرود. هر Session در Workload Group نسبت داده شده به آن باقی میماند تا زمانی که به پایان برسد، مگر اینکه صراحتاً به یک گروه متفاوت دوباره نسبت داده شود. فقط یک Classification function فعال در هر زمان میتواند وجود داشته باشد. در صورت عدم تعریف شدن یا عدم فعال بودن Classification function همه اتصالات به Workload Group Default نسبت داده میشوند. Classification function یک نام workgroup که نوع آن SYSNAME است (که یک نام مستعار برای دیتا تایپ nvarchar 128 است.) برمی گرداند. اگر تابع تعریف شده مقدار 'NULL ،'Default یا نام گروهی که وجود ندارد را برگرداند، Session به Workload Group Default نسبت داده میشود. همچنین اگر به هر دلیلی تابع با موفقیت خاتمه نیابد Session به Workload Group Default نسبت داده میشود.
منطق Classification function معمولاً مبتنی بر ویژگیهای اتصال است و اغلب از طریق مقدار بازگشتی توابع سیستمی از قبیل:
()SUSER_NAME() ،SUSER_SNAME() ،IS_MEMBER() ،IS_SERVERROLEMEMBER() ،HOST_NAME و یا ()APP_NAME، نام Workload Group اتصال مشخص میشود. علاوه بر این توابع میتوانید از ویژگیهای توابع دیگر برای ساخت منطق رده بندی استفاده کنید. تابع ()LOGINPROPERTY شامل دو ویژگی (DefaultDatabase و DefaultLanguage) میباشد که میتواند برای Classification function استفاده شود. بعلاوه تابع ()CONNECTIONPROPERTY پروتکلها و دسترسی به نقل و انتقالات در شبکه، همچنین جزئیات طرح احراز هویت، Local IP address و TCP Port و Client’s IP Address را برای استفاده اتصالات فراهم میکند. برای مثال میتوانید برای یک اتصال، یک Workload Group نسبت دهید، مبتنی بر اینکه subnet یک اتصال ازکجا میآید.
نکته: اگر قصد دارید از هر یک از توابع ()HOST_NAME و یا ()APP_NAME در تابع رده بندی تان استفاده کنید، توجه داشته باشید این امکان وجود دارد مقادیر بازگردانده شده توسط این توابع توسط کاربران تغییر داده شوند، گر چه به طور کلی گرایش به استفاده از تابع ()APP_NAME برای رده بندی اتصالات بیشتر است.
4- بررسی نمونه ای از پیکربندی Resource Governor
برای سادگی، در این قسمت مثالی ارائه میشود که از تابع ()SUSER_NAME استفاده میکند: در گام نخست، دو Resource Pool ایجاد میشود ( ReportPool و OLTPPool )
می توان تابع ()WorkgroupClassifier را در محیط SSMS با اجرای دستور زیر برای Loginهای متفاوت تست نمود:
در ادامه دستور زیر برای پیکربندی تابع رده بندی به Resource Governor استفاده میشود:
5- اصلاح پیکربندی Resource Governor:
میتوانید درمحیط SSMS تنظیمات Resource Pool و Workload Group را تغییر دهید ( برای مثال حداکثر استفاده CPU برای یک Resource Pool و یا درجه اهمیت یک Workload Group). متناوباً میتوان از دستورات T-SQL استفاده نمود.
نکته: پس از اجرای دستورات ALTER RESOURCE POOL یا ALTER WORKLOAD GROUP، برای اعمال کردن تغییرات اجرای دستور ALTER RESOURCE GOVERNOR RECONFIGURE نیاز میباشد.
5-1- حذف Workload Group :
یک Workload Group را اگر هر نوع Session فعال نسبت داده شده به آن وجود داشته باشد، نمیتوان حذف نمود. اگر یک Workload Group شامل Sessionهای فعال باشد، حذف Workload Groupو یا جابجائی آن به یک Resource Pool متفاوت، هنگامی که دستور ALTER RESOURCE GOVERNOR RECONFIGURE برای اعمال نمودن تغییرات فراخوانی میشود، با خطا مواجه خواهد شد.
5-2- حذف Resource Pools:
یک Resource Pool را اگر هر نوع Workload Group نسبت داده شده به آن وجود داشته باشد، نمیتوان حذف نمود. نخست نیاز دارید Workload Group حذف شود و یا به Resource Pool دیگری جابجا گردد.
5-3- اصلاح Classification function:
اگر نیاز دارید تغییراتی در تابع رده بندی ایجاد نمائید، مهم است توجه داشته باشید که تابع رده بندی تا زمانی که مشخص شده (marked) برای Resource Governor است، نمیتوان آنرا حذف و یا تغییر داد. پیش از اینکه بتوان تابع رده بندی را اصلاح و یا حذف نمود نخست نیاز دارید Resource Governor را غیر فعال نمائید. متناوباً میتوان تابع رده بندی را جایگزین کرد با اجرای دستور ALTER RESOURCE GOVERNOR و فرستادن (passing) یک اسم متفاوت برای CLASSIFIER_FUNCTION،همچنین میتوان با اجرای دستور زیر تابع رده بندی جاری را غیر فعال نمود:
تابع رده بندی میتوان تعریف کرد که نام Workload Group را از جداول یک بانک اطلاعاتی جستجو کند به جای اینکه نام Workload Group به صورت hard-coding و مطابق با ضوابط درون تابع باشد. عملکرد، در موقع دسترسی به جدول برای جستجو کردن نام Workload Group، نباید تا حد زیادی تحت تاثیر قرار گیرد.
6- نظارت بر Resource Governor
با استفاده از Performance Monitor، events و (Dynamic Management View (DMV میتوان Workload Group و Resource Pool را نظارت (Monitor) کرد. دو شی Performance برای این کار موجود است: SQL Server:Workload Group Stats و SQL Server:Resource Pool Stats
شکل زیر مربوط به پیکر بندی مثال مورد نظرمان میباشد:
7- نتیجه گیری
Resource Governor چندین مزیت بالقوه ارائه میدهد، در درجه اول قابلیت اولویت بندی منابع Server برای کاربران و برنامههای کاربردی (applications) بحرانی، جلوگیری از “runaway” یا درخواستهای غیر منتظره ای که به شدت و بطور قابل توجهی روی کارائی Server تاثیر منفی میگذارند.
ضمناً Resource Governor چندین مشکل بالقوه نیز عرضه میکند، برای مثال پیکربندی اشتباه Resource Governor تنها به عملکرد کلی Server آسیب نمیرساند بلکه به طور بالقوه روی سرور قفل (Lock) میتواند ایجاد کند و نیاز به استفاده از اتصال اختصاصی Administrator برای متصل شدن به SQL Server به منظور اشکال یابی و رفع مشکل میباشد. بنابراین توصیه شده است که تنها در صورتی که DBA با تجربه ای هستید و درک خوب و آشنائی خوبی با Workload هائی که روی بانک اطلاعاتی اجرا میشوند دارید، Resource Governor را بکار برید. حتی در این صورت، ضروری است که پیکربندی تان را روی یک Server تستی پیش از اینکه روی محیط تولیدی بگسترانید، تست نمائید.
Resource Governor به عنوان یک ویژگی با نام تجاری جدید در SQL Server 2008، با تعدادی محدودیت همراه است که احتمالاً در نسخههای بعدی SQL Server حذف خواهد شد، از محدودیت های بارز :
- محدودیت منابع (Resource)، که به CPU و حافظه محدود میشوند. I/O Disk و منابع شبکه را در SQL Server 2008 نمیتوان محدود کرد.
- استفاده از منابع برای Reporting Service، Analysis Service و Integeration Service را نمیتوان محدود کرد . در این نسخه محدودیتهای منابع تنها روی هسته موتور بانک اطلاعاتی بکار برده میشود.
- محدودیتهای Resource Governor روی یک SQL Instance تعریف و بکار برده میشود.
حال اگر بخواهیم یکی از پرکاربردترین این توابع که Row_Number میباشد را در LINQ استفاده کنیم باید به چه صورت عمل کنیم.
من برای پیاده سازی از برنامه نیمه رایگان LINQPad استفاده کردم که میتوانید از سایت اصلی این نرم افزار دانلود نمائید.
پس از دانلود و اجرای آن ، در قسمت بالایی زبان linqpad را به C# Statement(s) تغییر دهید.
سپس کد زیر را به بخش query انتقال دهید.
string[] mystring = new string[]{"a","b","c","d"}; int i=0; var s1 = from s in mystring.ToList() let e = i++ select new { Row_Number = i,StringName = s }; s1.Dump(); mystring.Count().Dump("mystring Count");
سپس با زدن کلید F5 یا دکمه اجرای query نتیجه را مشاهده نمائید.
تصویر بالا یک خط لوله را با چهار اکتور، نشان میدهد. میتوان اینطور فرض نمود که هر اکتور یک mailbox دارد و اگر پیامی برای آن اکتور بفرستید، آن را پردازش نموده و کار مخصوص به خود را بر روی آن پیام انجام میدهد و سپس آن پیام را برای اکتور بعدی خود ارسال میکند. اکتور دوم نیز به همان ترتیب کار خود را انجام داده و پیام را به اکتور مابعد خود ارسال میکند و به این ترتیب، یک پیام در خط لوله حرکت نموده و فرآیند مربوطه انجام میشود. اگر دقت کنید یک فرق دیگر هم بین خط تولید کارخانه و این خط لوله وجود دارد و آن این است که این خط لوله به صورت گراف میباشد. یعنی اکتورها میتوانند در ارتباط خود یک حلقه را تشکیل دهند و یا یک اکتور با چندین اکتور ارتباط مستقیم داشته باشد (مثل اکتور سمت چپ تصویر که با دو اکتور دیگر در ارتباط است).
خوب حالا که با مفاهیم خط لوله و اکتور آشنا شدیم، یک مسئلهی بسیار ساده را در نظر میگیریم و آن را با این روش حل میکنیم. فرض کنید یک رشته (string) داریم و میخواهیم عملیات زیر را بر روی آن به ترتیب انجام دهیم:
1- فاصلههای اضافی ابتدا و انتهای رشته حذف شود.
2- اگر رشته یک کلمهای است lowerCase شود.
3- اگر رشته بیش از یک کلمه است، تمام کلمات، به جز کلمهی اول، حذف شوند و سپس مرحلهی 2 بر روی آن انجام شود.
4- نتیجهی کار در خروجی نمایش داده شود.
حالا میخواهیم انجام هر یک از عملیات فوق را به یک اکتور سپرده و یک خط لوله را برای حل این مسئله طراحی کنیم. در قسمت بعدی به صورت عملی و با TPL DataFlow مایکروسافت این کار را انجام میدهیم.