- SQL Server Database Engine از کلمه کلیدی SPARSE برای تعریف یک ستون که مقادیر آن میبایست بهینه شود استفاده مینماید.
- نمای Catalog جداول با ستون sparse شبیه جداول معمولی میباشد.
- مقدار برگشتی از تابع COLUMNS_UPDATED با ستون sparce متفاوت از ستون معمولی است.
geography | text |
geometry | timestamp |
image | user-defined data types |
ntext |
نوع داده | بایت بدون sparse | بایت sparse | درصد null |
bit | 0.125 | 5 | 98% |
tinyint | 1 | 5 | 86% |
smallint | 2 | 6 | 76% |
int | 4 | 8 | 64% |
bigint | 8 | 12 | 52% |
real | 4 | 8 | 64% |
float | 8 | 12 | 52% |
smallmoney | 4 | 8 | 64% |
money | 8 | 12 | 52% |
smalldatetime | 4 | 8 | 64% |
datetime | 8 | 12 | 52% |
uniqueidentifier | 16 | 20 | 43% |
date | 3 | 7 | 69% |
نوع داده | بایت بدون sparse | یابت sparse | درصد null |
(datetime(2 | 6 | 10 | 57% |
(datetime(2 | 8 | 12 | 52% |
(time(0 | 3 | 7 | 69% |
(time(7 | 5 | 9 | 60% |
(datetimetoffset(0 | 8 | 12 | 52% |
(datetimetoffset (7 | 10 | 14 | 49% |
(decimal/numeric(1,s | 5 | 9 | 60% |
(decimal/numeric(38,s | 17 | 21 | 42% |
(vardecimal(p,s |
نوع داده |
بایت بدون sparse | یابت sparse | درصد null |
sql_variant | 2* | 2* | 60% |
varchar or char | 2* | 4*+ | 60% |
nvarchar or nchar | 2* | 4* | 60% |
varbinary or binary | 2* | 4* | 60% |
xml | 2* | 4* | 60% |
hierarchyid | 2* | 4* | 60% |
- sparse column می بایست nullable باشد و نمیتواند ROWGUIDCOL یا IDENTITY باشد.
- sparse column مقدار پیش فرض نمیتواند داشته باشد
- ستون محاسبه ای نمیتواند sparse باشد
- sparse column نمیتواند بخشی از clustered index یا unique primary key index باشد
- sparse column نمی تواند بخشی از user-defined table باشد
CREATE TABLE Employees_sparse ( EMP_ID INT IDENTITY(5001,1) PRIMARY KEY, SSN CHAR(9) NOT NULL, TITLE CHAR(10) SPARSE NULL, FIRSTNAME VARCHAR(50) NOT NULL, MIDDLEINIT CHAR(1) SPARSE NULL, LASTNAME VARCHAR(50) NOT NULL, EMAIL CHAR(50) SPARSE NULL) GO
CREATE TABLE Employees ( EMP_ID INT IDENTITY(5001,1) PRIMARY KEY, SSN CHAR(9) NOT NULL, TITLE CHAR(10) NULL, FIRSTNAME VARCHAR(50) NOT NULL, MIDDLEINIT CHAR(1) NULL, LASTNAME VARCHAR(50) NOT NULL, EMAIL CHAR(50) NULL) GO
sp_spaceused 'Employees' GO sp_spaceused 'Employees_sparse'
برخی مواقع شما نیاز دارید تا یک Query را بر روی یک سرور اجرا نمایید و این Query برخی اطلاعات خود را از سرور دیگری دریافت مینماید. در این صورت باید یک پل ارتباطی بین سرور جاری و سرور دیگر وجود داشته باشد تا بتوانید در یک Query به سرور دیگری متصل شوید و اطلاعاتی را دریافت نمایید. در حالت عادی یک Query فقط میتواند بر روی سرور جاری اجرا شده و اطلاعاتی را بازیابی نماید. اما اگر همین Query بخواهد به سرور دیگری متصل شود، آن سرور باید در سرور جاری بصورت Linked Server تعریف شده باشد.
به عنوان مثال:
من سروری با آدرس 192.168.0.1 دارم که دارای پایگاه دادهای با نام Salary می باشد. نام این سرور را A میگذارم.
همچنین من سرور دیگری با آدرس 192.168.1.100 دارم که دارای پایگاه داده ای با نام Accounting است. نام این سرور را B میگذارم.
حالا میخواهم در سرور A یک Query بنویسم که جدول Payment را با اتصال به سرور B به جدول Document متصل نموده و نتیجه ی JOIN این دو جدول را نمایش دهد. به عنوان مثال:
SELECT * FROM Payment AS pay JOIN Document AS doc ON pay.DocumentId = doc.Id
نحوهی ایجاد یک Linked Server
بر روی سیستم من دو نسخه از SQL نصب شده است. یکی Standard Edition و دیگری Express Edition. من میخواهم در نسخه Standard یک Linked Server به نسخهی Express ایجاد کنم. بنابراین با اتصال به نسخه Standard مراحل زیر را طی میکنم:
1. یک New query ایجاد میکنم.
2. دستورات زیر را در Query ایجاد شده مینویسم:
sp_addlinkedserver 'MyServer', '', 'SQLNCLI', '.\sqlexpress'
sp_addlinkedserver نام رویه ای است که یک Linked Server را ایجاد مینماید.
پارامتر اول نام Linked Server را مشخص مینماید که جهت دسترسی به سرور دیگر مورد استفاده قرار میگیرد.
پارامتر دوم Product Name میباشد که من خالی گذاشتم.
پارامتر سوم Provider Name یا نام فراهم کننده دادهای است. چون من میخواهم به یک سرور SQL متصل شوم SQLNCLI (SQL Native Client) را انتخاب کردم. اگر به منبع دادهای دیگری مثل Access،Oracle، MySql و ... متصل میشوید باید Provider Name دیگری را نتخاب کنید.
پارامتر چهارم نام یا IP سروری است که میخواهیم به آن لینک شویم.
3. با فشردن F5 یا منوی Execute این Query را اجرا کنید.
با اجرای موفقیت آمیز مراحل فوق باید عنوان MyServer را در مسیر Server Objects > Linked Server مشاهده کنید. در نسخه Express پایگاه دادهای با نام test دارم که شامل جدولی به نام tbl می باشد. با نوشتن Query زیر میتوانم محتویات این جدول را مشاهده کنم:
SELECT * FROM MyServer.test.dbo.tbl
sp_addlinkedsrvlogin 'MyServer',@rmtuser='user1', @rmtpassword='abc123'
sp_addlinkedsrvlogin نام رویه ای است که نام کاربری و رمز عبور را به یک Linked Server اضافه میکند.
پارامتر اول نام Linked Server می باشد.
پارامتر دوم نام کاربری جهت اتصال به سرور لینک شده میباشد.
پارامتر سوم رمز عبور جهت اتصال به سرور لینک شده میباشد.
شاخصهای مهم بررسی کارآیی کوئریها
در ابتدای بررسی هر کوئری، باید 4 شاخص بسیار مهم، مدنظر باشند:
- مدت زمان اجرای کوئری: هرچند بررسی مدت زمان اجرای کوئری، شاخص مهمیاست، اما الزاما حاوی اطلاعات مفیدی در مورد آن کوئری نیست. برای مثال اگر یک کوئری زیاد طول میکشد، حتما به معنای وجود مشکلی با آن نیست؛ ممکن است اطلاعات زیادی را واکشی میکند یا ممکن است توسط عاملی سد شدهاست. در این موارد هرچند مشکلاتی وجود دارند، اما مستقیما مرتبط با آن کوئری نیستند.
- میزان مصرف CPU: میزان کاری که باید توسط CPU انجام شود تا کوئری به نتیجه برسد.
- I/O: در SQL Server میتوان هم physical I/O و هم logical I/O را بررسی کرد. برای مثال اگر اطلاعات مورد درخواست توسط کوئری هم اکنون در حافظه موجود باشند، نیازی به physical I/O پرهزینه نخواهد بود و در مقابل آن logical I/O کم هزینهتر است.
- میزان مصرف حافظه
در کل هر کدام از این شاخصها اگر دارای مقدار بالایی باشند، بیانگر وجود مشکلی است.
مروری بر ابزارهای مختلف اندازهگیری شاخصهای کارآیی
Management studio
درون Management studio میتوان اطلاعات مرتبط با یک کوئری را به صورت زنده مشاهده کرد. البته این اطلاعات صرفا مرتبط با یک کوئری و یا تعدادی مشخص هستند؛ چون باید کوئری را به صورت دستی درون این برنامه اجرا کرد و سپس اطلاعات اجرای کوئریها را دریافت نمود. اطلاعات آماری که توسط آن نیز ارائه میشود محدودیتهایی دارد. برای مثال مدت زمان اجرای کوئری و یا تعداد رکوردهای تحت تاثیر قرار گرفته شده را میتوان مشاهده کرد. اما به اندازهی اطلاعات ارائه شدهی در یک execution plan کامل نیست. به علاوه بازگشت اطلاعات حاصل از اجرای کوئریها درون این برنامه، سربار خودش را داشته و سبب کند شدن برنامه میشود. در آخر اطلاعات ارائه شدهی توسط آنرا نیز باید از قسمتهای مختلفی جمع آوری و به صورت دستی ذخیره کرد.
Extended Events
توسط Extended Events نیز میتوان همانند Management studio، اطلاعات آماری یک تک کوئری و یا یک batch را جمع آوری کرد؛ اما پس از ایجاد و تنظیم آن، به صورت خودکار اجرا میشود. در حین تعریف یک سشن Extended Events میتوان شاخصهای خاصی را انتخاب کرد و یا شرطهای دقیقی را اعمال کرد. خروجی آن نیز به صورت خودکار در یک فایل ذخیره میشود.
Dynamic management objects
با استفاده از DMO's از نتایج آماری مرتبط با تک کوئریها، به نتایج تجمعی حاصل از اجرای آنها میرسیم. این نتایج نیز در plan cache ذخیره میشوند. به این معنا که اگر کش، تخلیه (با اجرای دستور DBCC FREEPROCCACHE) و یا سرور ریاستارت شود، این اطلاعات از دست خواهند رفت. هدف آن بیشتر رفع اشکال کوئریهایی است که هم اکنون در حال اجرا هستند. اگر نیاز به اطلاعات دورهای را داشته باشید، نیاز خواهید داشت تا با تهیهی snapshotهایی از بانک اطلاعاتی، این تاریخچه را تکمیل کنید. به همین جهت Query Store ارائه شدهاست تا نیازی به اینکار نباشد.
Query Store
Query Store کار ذخیره سازی متن plan و آمار تجمعی مرتبط با آنرا به صورت خودکار انجام میدهد و آنرا درون بانک اطلاعاتی کاربر ذخیره میکند. به همین جهت با خالی شدن کش، برخلاف DMO's، اطلاعات آن حذف نمیشود.
مثالی از روشهای مختلف جمع آوری اطلاعات آماری حاصل از اجرای کوئریها در SQL Server
در ادامه قصد داریم با مثالی، خلاصهای را از سه قسمتی که تاکنون بررسی کردیم، ارائه دهیم. برای این منظور ابتدا رویهی ذخیره شدهی زیر را ایجاد میکنیم:
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
سپس یک سشن Extended event را با نام QueryPerf ایجاد میکنیم:
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
در ادامه Query Store را نیز بر روی بانک اطلاعاتی WideWorldImporters فعال کرده و همچنین اگر اطلاعاتی از پیش در آن وجود دارند، پاک میشود.
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
سپس هر آنچه را که در plan cache نیز وجود دارد، حذف میکنیم:
DBCC FREEPROCCACHE; GO
اکنون سشن QueryPerf را که پیشتر ایجاد کردیم، آغاز میکنیم:
ALTER EVENT SESSION [QueryPerf] ON SERVER STATE = START; GO
در ادامه چون میخواهیم نتایج آماری را در management studio نیز مشاهده کنیم، ابتدا جمع آوری شاخصهای آماری را در یک پنجرهی جدید new query، فعال میکنیم:
SET STATISTICS IO ON; GO SET STATISTICS TIME ON; GO SET STATISTICS XML ON; GO
همچنین در منوی Query، گزینهی Include client statistics را نیز انتخاب میکنیم تا مشخص شود که آیا عملیات insert/update/delete انجام شدهاست. چه تعداد ردیف تحت تاثیر اجرای این کوئری قرار گرفتهاند. چه تعداد تراکنش انجام شدهاست. همچنین اطلاعات آماری شبکه و زمان نیز ارائه شوند.
پس از این تنظیمات، اکنون نوبت به اجرای کوئریهای زیر رسیدهاست که یکی پارامتری است و دیگری AdHoc:
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
سپس سشن QueryPerf را متوقف و حذف میکنیم:
ALTER EVENT SESSION [QueryPerf] ON SERVER STATE = STOP; GO DROP EVENT SESSION [QueryPerf] ON SERVER; GO
اگر بخواهیم از عملیات صورت گرفته توسط DMO's کوئری بگیریم:
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
همانطور که مشاهده میکنید، شاخصهای چهارگانهای که در ابتدای بحث معرفی شدند، در مورد کوئری پارامتری نوشته شده، وضعیت بسیار بهتری نسبت به کوئری AdHoc دوم دارند.
از Query Store هم میتوان به صورت زیر کوئری گرفت (علاوه بر قسمت رابط کاربری Query Store که ذیل اشیاء مرتبط با بانک اطلاعاتی WideWorldImporters در management studio قابل مشاهدهاست):
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
بررسی اجمالی Redis
Redis | RDBMS |
Redis همه چیز را در حافظه اصلی ذخیره میکند. | RDBMS همه چیز را در حافظه ثانویه ذخیره میکند. |
در Redis بخاطر ذخیره سازی دادهها در حافظه اصلی، خواندن و نوشتن عملیات به شدت سریع میباشد. | در RDBMS بخاطر ذخیره سازی دادهها در حافظه ثانویه، خواندن و نوشتن عملیات کند است. |
حافظه اصلی از نظر size کوچکتر و از لحاظ قیمت نسبت به حافظه ثانویه گرانتر میباشد. Redis نمیتواند دادههای بزرگ یا binary data را ذخیره کند. | حافظه ثانویه از نظر size بزرگتر و از لحاظ قیمت نسبت به حافظه اصلی ارزانتر میباشد. RDBMS به آسانی میتواند با انواع فایلها کار کند. |
- Redis : Exceptionally fast خیلی سریع است و میتواند حدود 110000 ، SET و 81000 ، GET را به ازای هر ثانیه انجام دهد.
- Redis : Supports rich data type بیشتر دیتا تایپها را که توسعه دهندگان قبلا آنها را شناختهاند، پشتیبانی میکند؛ از قبیل string ، list ، set ، sorted set یا hash .
- Operations are atomic : تمام عملیات Redis اتمیک میباشند که این اطمینان خاطر را میدهد اگر دو کلاینت به صورت همزمان به آن دسترسی داشته باشند، Redis server مقدار update شده را دریافت خواهد کرد.
- Redis : Multi-utility tool یک ابزار چند منظوره است که میتواند در برخی از سناریوها استفاده شود از قبیل: Redis ) messaging-queues , caching به صورت بومی از Publish/Subscribe پشتیبانی میکند ) , هر داده ای با طول عمر کوتاه در Application مانند web application sessions , ... .
افزونه جملات قصار jQuery
LocalDB FAQ
در تنظیمات Data Source جهت اتصال به LocalDB در Rider این مراحل باید طی شوند:
- پیش از هر کاری دو دستور زیر را اجرا کنید:
C:\>"C:\Program Files\Microsoft SQL Server\140\Tools\Binn\SqlLocalDB.exe" i MSSQLLocalDB ProjectsV13 v12.0 C:\>"C:\Program Files\Microsoft SQL Server\140\Tools\Binn\SqlLocalDB.exe" s MSSQLLocalDB LocalDB instance "MSSQLLocalDB" started.
خود Rider آغازگر این وهلهها نخواهد بود. به همین جهت نیاز است دستی آغاز شوند.
- سپس در صفحه تنظیمات Data Source، نوع Driver را بر روی SQL Server (jTds) قرار دهید.
- پایین صفحه، لینک download missing driver files ظاهر میشود. بر روی آن کلیک کنید تا به سرعت کار نصب و راه اندازی درایور کم حجم آن انجام شود.
- اکنون میتوانید در قسمت URL، گزینهی LocalDB و سپس وهلهی MSSQLLocalDB را از لیست Instance انتخاب کنید.
- در آخر بر روی دکمهی Test Connection کلیک کنید. اگر درایور را نصب نکرده باشید، این دکمه قابل انتخاب نخواهد بود.
EF Code First #2
سؤال شما هم بحث کلاینت سروری است و نه بحث کلاینت تنها که EF روی آن مشغول به کار است.
- میشود در متد Seed ایی که در بالا توضیح دادم در SQL Server تریگر درست کرد. (که مثلا اگر کاربر دیگری به شرط اینکه این کاربر جزو کاربران تعریف شده در خود SQL Server باشد نه در برنامه شما، اتفاق خاصی رخ دهد. برنامه شما هم بدیهی است باید سرور را مدام چک کند تا از این مساله مطلع شود)- SQL Server مبحثی دارد به نام Service Broker : (^). توسط آن میتوان از طریق سرور به کلاینت اطلاع رسانی کرد. بازهم خارج است از بحث یک ORM. یا تمام ORMهای موجود. - EF مبحثی دارد به نام Concurrency check که اگر شخصی در شبکه بر روی رکوردی که همین الان شما مشغول به کار هستید، تغییری را ایجاد کرد، به شما اطلاع رسانی کند. (در قسمتهای بعدی بحث خواهد شد). البته این هم خودکار نیست. لازم است یک رفت و برگشت به سرور انجام شود.- entity framework auditing هم میسر است. خودکار نیست. در همان کلاس Context فوق که از DbContext مشتق میشود میتوان متد تحریف شده public override int SaveChanges را تعریف کرد. در اینجا میتوان به تمام تغییراتی که قرار است اعمال شوند دسترسی داشت. مثلا آنها را در یک جدول مجزا ثبت کرد. بدیهی است برنامه بعدا نیاز خواهد داشت از این جدول گزارشگیری کند.
رفع اشکال خطای an error was encountered in the transport layer در هنگام وصل شدن از کلاینتی که در یک کامپیوتر دیگر نصب شده است به Sql Server Analysis Services
در هنگام برخورد با این مشکل، پس از بررسیها و تستهای مختلف و پیاده کردن روشهای متفاوتی که در وب مطرح شده بود، به فکرم رسید که شاید از طریق درج مستقیم پورت بتوان مشکل را حل کرد که مراحل آن به شرح ذیل میباشد:
برای بدست آوردن پورتی ( Port ) که Analysis Services به آن گوش میدهد و با آن کار میکند، باید کارهای ذیل انجام شوند:
از طریق Sql Server Configuration Manager، همانطور که در تصویر آمده است، PID یا Process Id را که مربوط به Sql Server Analysis Services میشود، برای هر نمونهای ( Instance ) که میخواهیم به آن وصل شویم، بدست میآوریم:
سپس از طریق Command Prompt دستور ذیل را اجرا میکنیم:
netstat /abo >>c:\output.txt
پس از آن کافی است که در رشتهی اتصال به Analysis Services از آن Port استفاده کنیم:
و یا