خانه SQL Server یافتن UDFهایی که موجب کاهش کارایی SQL Server می شوند SQL Server افزایش سرعت SQL Server نوشته شده توسط: سید محمد حسینی تاریخ انتشار: ۰۵ دی ۱۳۹۴ آخرین بروزرسانی: ۱۹ بهمن ۱۴۰۰ زمان مطالعه: 15 دقیقه ۰ (۰) مقدمه چند وقت پیش، در حالی که مشغول کار بر روی بهینه سازی کارایی بانک اطلاعاتی یکی از سیستم های شرکت بودم، با یک SP مواجه شدم که در آن عملیاتی نظیر عملیات زیر انجام می شد: یک SELECT که نتیجه آن در یک جدول موقت قرار می گرفت سپس یک INSERT که داده ها را از جدول موقت می خواند برخی دستکاری داده ها و در انتها درج داده ها در یک جدول دیگر جهت اهداف گزارش گیری پس از بررسی بیشتر و تجزیه و تحلیل این فرآیند، دریافتم که مشکل واقعی، استفاده گسترده از توابع-اسکالر- تعریف شده کاربر (UDF) می باشد. با شناسایی این مشکل و بازسازی SP مربوطه، توانستیم مدت زمان اجرا را از ۹۰ دقیقه تا کمتر از ۵ دقیقه کاهش دهیم. این مثال می تواند نشان از اهمیت شناسایی و رفع مشکلات ناشی از کارایی UDFها باشد. در ادامه برخی از مسائل ناشی از UDFها و برخی از پرسوجوهایی که می توان توسط آنها محل استفاده از UDFها و همچنین تأثیرات آنها بر کارایی را بدست آورد، بررسی می شود. بنابراین در انتها شما می دانید که در کجای کد باید بهینه سازی را جهت بهبود کارایی انجام دهید. چرا توابع اسکالر SQL Server بد هستند؟ توابع SQL Server به دو دسته تقسیم می شوند: توابع اسکالر(Scalar Functions) توابعی که خروجی آنها جدول است(Table Valued Functions-TVFs) اگرچه توابع برای کپسوله سازی و ساده سازی کد بسیار مفید هستند، ولی چندین مشکل در رابطه با کارایی UDFها، به خصوص توابع اسکالر وجود دارد. برخی از این مشکلات عبارتند از: توابع اسکالر مانند کرسر(cursor) عمل می کنند، به ازای هر سطر از پرسوجو که پردازش می شود، اجرا می شوند. این موضوع با توجه به رشد پرسوجو، به صورت نمایی موجب کاهش کارایی می شود. توابع اسکالر اجازه نمی دهند پرسوجو از منافع پردازش موازی(Parallelism) بهره مند شود، بنابراین اجرای پرسوجو را محدود به یک پردازنده می کنند. توابع اسکالر منجر به تخمین نادرست و کمتر از حد مطلوب هزینه ها در پلان اجرایی می شود. توابع اسکالر به عنوان گلوگاه های پنهان(Hidden Bottlenecks) هستند، زیرا درک تاثیر آنها بر کارایی، با نگاه کردن به یک پلان اجرایی که در آن UDFها مشکل ساز هستند، بسیار دشوار است. در اینجا فقط چند تکنیک جهت یافتن توابع اسکالر و میزان تاثیر آنها بر کارایی بررسی شده است. مطمئنا با بررسی های بیشتر در اینترنت می توان راهکارهای بهتر و بیشتری را جستجو نمود. چگونه میزان تاثیر توابع اسکالر SQL Server بر کارایی را اندازه گیری کنیم؟ هنگامی که قصد ارزیابی کارایی تاثیر توابع اسکالر را داریم، به عنوان یک DBA باید برخی موارد را بدانیم: لیست اسامی توابع مورد استفاده و میزان/تعداد دفعات فراخوانی این توابع چقدر است؟ تأثیر این توابع بر کارایی چیست – به عبارت دیگر، زمان و منابع مورد نیاز برای اجرای این توابع چقدر است؟ همانطور که قبلا نیز گفته شد، پلان اجرایی SQL برای تخمین کارایی توابع اسکالر ابزار مناسبی نیست، زیرا در هنگام استفاده از پرسوجوهای بزرگ میزان اصلی تاثیرات را منعکس نمی کند. در ادامه چندین راه جهت بررسی و ارزیابی کارایی UDFها ارائه شده است که دارای قابلیت ها و محدودیت هایی می باشند. استفاده از DMVها جهت به دست آوردن پلان اجرایی UDF به جای پلان SQL Server این کوئری آمارهای مربوط به کارایی را برای توابعی که اخیرا فراخوانی شده اند نشان می دهد(نظیر تعداد اجراها، متوسط زمان سپری شده، متوسط خواندن های منطقی/فیزیکی، و غیره). توابعی که دارای تعداد دفعات اجرای بیشتری هستند، همچنین آنهایی که تعداد عملیات خواندن آنها بیشتر است بهترین کاندید برای بهینه سازی هستند. از نظر من این بهترین روش برای ارزیابی کارایی UDFها می باشد. البته این روش دارای محدودیت هاییی هم هست- تنها توابعی را که به داده ها دسترسی داشته باشند به دست خواهد آورد. این کوئری تمام انواع UDFها، از جمله UDFهای اسکالر را باز می گرداند. USE [TempStockAsset4] SELECT TOP 100 DB_NAME() as [database], QS.total_worker_time /1000000 As TotalWorkerTime, QS.total_elapsed_time/1000000 As TotalElapsedTime_Sec, QS.total_elapsed_time/(1000000*qs.execution_count) AS [avg_elapsed_time_Sec], QS.execution_count, QS.total_logical_reads/QS.execution_count As Avg_logical_reads, QS.max_logical_writes, ST.text AS ParentQueryText, SUBSTRING(ST.[text],QS.statement_start_offset/2+1, (CASE WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), ST.[text])) * 2 ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) AS [Query Text] , QP.query_plan , O.type_desc FROM sys.dm_exec_query_stats QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) QP LEFT JOIN Sys.objects O ON O.object_id =St.objectid Where O.type_desc LIKE '%Function%' ORDER by qs.total_worker_time DESC; استفاده از DMVها جهت به دست آوردن پلان اجرایی کوئری با ارجاع به UDF به جای پلان SQL Server کوئری زیر، پلان های اجرایی SQL را جهت یافتن عبارت “ComputeScalar” که نشان دهنده عملیات رخ داده توسط توابع اسکالر هستند را به همراه آمار مربوط به کارایی این کوئری های را نمایش می دهد. البته این کوئری میزان منابع مورد استفاده توسط توابع را نمایش نمی دهد. ;WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan' ) , GetQueriesWithUDF AS ( SELECT DISTINCT udf.value('(@FunctionName)[1]','varchar(100)') As UDF_Name, RIGHT(udf.value('(@FunctionName)[1]','varchar(100)'), LEN(udf.value('(@FunctionName)[1]','varchar(100)'))- CHARINDEX('.',udf.value('(@FunctionName)[1]','varchar(100)'), CHARINDEX('.',udf.value('(@FunctionName)[1]', 'varchar(100)'),1)+1)) As Stripped_UDF_Name, QS.execution_count, CONVERT(float,cs.value('(.//RelOp/@EstimateRows)[1]', 'varchar(100)')) As EstimatedRows, QS.total_elapsed_time/1000000 As TotalElapsedTime_Sec, QS.max_elapsed_time/1000000 As max_elapsed_time_Sec, QS.total_elapsed_time/(1000000*qs.execution_count) AS [avg_elapsed_time_Sec], QS.total_logical_reads, QS.total_logical_reads/qs.execution_count As avg_logical_reads, SUBSTRING(ST.[text],QS.statement_start_offset/2+1, (CASE WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), ST.[text])) * 2 ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) AS [Query_Text], ST.text As Parent_Query FROM sys.dm_exec_query_stats QS CROSS APPLY sys.dm_exec_sql_text (QS.sql_handle ) ST CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp CROSS APPLY qp.query_plan.nodes('.//ComputeScalar') AS CompCsalar(cs) CROSS APPLY cs.nodes('.//UserDefinedFunction[@FunctionName]') AS fn(udf) WHERE DB_Name(qp.dbid) not in ('msdb','master','tempdb') ) SELECT * FROM GetQueriesWithUDF WHERE Query_text LIKE '%'+Stripped_UDF_Name+'%' ;ORDER BY EstimatedRows DESC OPTION(MAXDOP 1, RECOMPILE) استفاده از SQL Server Extended Events جهت به دست آوردن تعداد UDFها این کوئری یک سشن Extended Events جهت به دست آوردن رویدادهای module_end که بر اساس نوع شیء تابع فیلتر شده اند را ایجاد می کند. باید توجه داشت که پیش از اجرای این اسکریپت باید نام بانک اطلاعاتی مورد نظر شما با AdventureWorks2014 جایگزین گردد. جهت به حداقل رساندن تأثیر تریس بر روی سرور، در این مثال فقط تعداد کل اجراها به ازای هر UDF جمع آوری می شود. با این حال، توصیه می شود این اسکریپت را بر روی سرور اصلی اجرا نکنید. همچنین این روش با SQL 2012 و نسخه های بعد به خوبی کار می کند، اما ممکن است با نسخه های قدیمی تر به درستی کار نکند. CREATE EVENT SESSION [UDF_Trace] ON SERVER ADD EVENT sqlserver.module_end( ACTION(sqlserver.database_name,sqlserver.sql_text) WHERE ([sqlserver].[equal_i_sql_ansi_string]([object_type],'FN') AND [sqlserver].[database_name]=N'AdventureWorks2014')) ADD TARGET package0.histogram( SET filtering_event_name=N'sqlserver.module_end', source=N'object_name',source_type=(0)) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF) GO ALTER EVENT SESSION [UDF_Trace] ON SERVER STATE=START GO استفاده از تریس سمت سرور در SQL Server جهت به دست آوردن اطلاعاتی در مورد اجرای UDFها این کوئری یک تریس سمت سرور ایجاد می کند که رویداد SP:Completed را جمع آوری کرده و فیلتر آن بر اساس ObjectType=20038 می باشد. توجه داشته باشید که این تریس در صورت اجرا بر روی سرور اصلی و وجود تعداد زیادی رویداد، می تواند به طور جدی مشکلات کارایی زیادی برای سرور ایجاد نماید. در اینجا از تریس سمت سرور بجای تریس های برنامه Profiler استفاده شده است تا حجم بار کاری سرور کاهش داده شود. در صورتی که با تریس های سمت سرور آشنایی ندارید، این تریس ها در پس زمینه اجرا شده و می توان آنها را توسط دستورات TSQL اجرا/متوقف نمود. نتایج به دست آمده نیز توسط برنامه SQL Profiler یا دستورات TSQL قابل بررسی هستند. کوئری مورد نظر در زیر ارائه شده است. -- Create a Queue declare @rc int declare @TraceID int declare @maxfilesize bigint,@FilePath nvarchar(500)=N'C:\Data\UDFTrace' set @maxfilesize = 5 -- Please replace the text InsertFileNameHere, with an appropriate -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension -- will be appended to the filename automatically. If you are writing from -- remote server to local drive, please use UNC path and make sure server has -- write access to your network share exec @rc = sp_trace_create @TraceID output, 0, @FilePath, @maxfilesize, NULL if (@rc != 0) goto error -- Client side File and Table cannot be scripted -- Set the events declare @on bit set @on = 1 exec sp_trace_setevent @TraceID, 10, 12, @on exec sp_trace_setevent @TraceID, 10, 13, @on exec sp_trace_setevent @TraceID, 10, 14, @on exec sp_trace_setevent @TraceID, 10, 34, @on exec sp_trace_setevent @TraceID, 10, 35, @on exec sp_trace_setevent @TraceID, 45, 12, @on exec sp_trace_setevent @TraceID, 45, 13, @on exec sp_trace_setevent @TraceID, 45, 14, @on exec sp_trace_setevent @TraceID, 45, 28, @on exec sp_trace_setevent @TraceID, 45, 34, @on exec sp_trace_setevent @TraceID, 45, 35, @on -- Set the Filters declare @intfilter int declare @bigintfilter bigint set @intfilter = 20038 exec sp_trace_setfilter @TraceID, 28, 0, 0, @intfilter set @intfilter = NULL exec sp_trace_setfilter @TraceID, 28, 0, 1, @intfilter -- Set the trace status to start exec sp_trace_setstatus @TraceID, 1 -- display trace id for future references select TraceID=@TraceID goto finish error: select ErrorCode=@rc finish: go جهت اجرای این کوئری باید ابتدا مسیر دلخواهی را برای متغییر @FilePath مشخص کرده و سپس کوئری را اجرا نمود. پس از اجرای کوئری، ID تولید شده را باید جهت استفاده های بعدی یادداشت نمود. مثلا برای متوقف کردن تریس به این ID نیاز می باشد. پس از اتمام عملیات و جمع آوری اطلاعات مورد نظر، ID مربوطه را در کوئری ریز جایگزین @TraceID کرده و برای متوقف کردن تریس این کوئری را اجرا نمایید. exec sp_trace_setstatus @TraceId, 0 exec sp_trace_setstatus @TraceId, 2 همچنین می توان جهت بررسی اطلاعات جمع آوری شده توسط تریس، از کوئری زیر استفاده نمود(مسیر مشخص شده باید با مسیر مورد نظر شما جایگزین گردد) SELECT databasename,objectname,sum(duration) FROM fn_trace_gettable('c:\data\UDFTrace.trc',default) GROUP BY databasename,objectname j,h چه رتبه ای میدهید؟ میانگین ۰ / ۵. از مجموع ۰ اولین نفر باش معرفی نویسنده مقالات 11 مقاله توسط این نویسنده محصولات 0 دوره توسط این نویسنده سید محمد حسینی معرفی محصول مسعود طاهری آموزش ۳ در ۱ Performance Tuning در SQL Server 6.700.000 تومان 5.700.000 تومان مقالات مرتبط ۰۸ شهریور SQL Server اهمیت مانیتورینگ در SQL Server چیست؟ | تمام آنچه که باید از مانیتورینگ بدانید تیم فنی نیک آموز ۰۳ شهریور SQL Server اعمال گواهینامه SSL روی SQL Server تیم فنی نیک آموز ۳۱ مرداد SQL Server دستور Rank در SQL Server ؛ کلید حل معمای رتبه بندی تیم فنی نیک آموز ۲۵ مرداد SQL Server نرم افزار (Microsoft SQL Server Management Studio (SSMS چیست؟ تیم فنی نیک آموز دیدگاه کاربران لغو پاسخ دیدگاه نام و نام خانوادگی ایمیل ذخیره نام، ایمیل و وبسایت من در مرورگر برای زمانی که دوباره دیدگاهی مینویسم. موبایل برای اطلاع از پاسخ لطفاً مرا با خبر کن ثبت دیدگاه Δ محسن فرید ۰۳ / ۱۲ / ۹۴ - ۱۲:۲۳ سلام ببخشید من یه سوال داشتم یه کد نوشم اما معادلش رو تو ان تی تی نمیدونم ممنون میشم کمک کنید حتی با هزینهDECLARE @userTypeId BIGINT;SET @userTypeId = 1; WITH tblChild AS( SELECT * FROM jt_PrdGroup WHERE ParentId = @userTypeId UNION ALL SELECT jt_PrdGroup.* FROM jt_PrdGroup JOIN tblChild ON jt_PrdGroup.ParentId = tblChild.Id)SELECT * FROM jt_Product p INNER JOIN (SELECT * FROM tblChild UNION ALL SELECT jt_PrdGroup.* FROM jt_PrdGroup WHERE jt_PrdGroup.Id = @userTypeId) m1 ON m1.ID = p.Kind پاسخ به دیدگاه سید محمد حسینی ۰۵ / ۱۲ / ۹۴ - ۰۸:۳۲ سلام با اجازه استاد طاهری عزیز اگر منظورتون رو درست متوجه شده باشم و منظورتون از ان تی تی همون Entity Framework باشد، تا جایی که بنده میدونم و در لینک زیر هم این رو گفته که EF از پرسوجوهای Recursive و Hierarchical پشتیبانی نمی کنه https://code.msdn.microsoft.com/windowsdesktop/Recursive-or-hierarchical-bf43a96e ولی شما میتونی از طریق EF کد SQL تون رو اجرا کنید، لینک های زیر میتونه کمکتون کنه: http://www.entityframeworktutorial.net/EntityFramework4.3/raw-sql-query-in-entity-framework.aspx https://msdn.microsoft.com/en-us/data/jj592907.aspx http://stackoverflow.com/questions/915329/is-it-possible-to-run-native-sql-with-entity-framework پاسخ به دیدگاه ha_zarabi_vb6@outlook.com ۰۷ / ۱۱ / ۹۴ - ۰۸:۳۹ با سلام و خسته نباشید کوئری بالا خط زیر را ارور می دهد [sql] SUBSTRING(ST.1,QS.statement_start_offset/2+1, [/sql] منظور از st.1 هست که در جاهای دیگر هم تکرار شده من منبع آن را بدست آوردم و کدها را در زیر می گذارم البته کدهای زیر برای همه دیتابیس ها عمل می کند [sql] sp_msforeachdb ‘ IF ”?” NOT IN (”msdb”,”master”,”tempdb”) BEGIN USE [?] SELECT top 100 DB_NAME() as [database], qs.total_worker_time /1000000 As TotalWorkerTime, QS.total_elapsed_time/1000000 As TotalElapsedTime_Sec, QS.total_elapsed_time/(1000000*qs.execution_count) AS [avg_elapsed_time_Sec], QS.execution_count, QS.total_logical_reads/QS.execution_count As Avg_logical_reads, QS.max_logical_writes, ST.text AS ParentQueryText, SUBSTRING(ST.,QS.statement_start_offset/2+1, (CASE WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), ST.)) * 2 ELSE QS.statement_end_offset END – QS.statement_start_offset)/2) AS [Query Text] , QP.query_plan , O.type_desc FROM sys.dm_exec_query_stats QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) QP LEFT JOIN Sys.objects O ON O.object_id =St.objectid Where O.type_desc like ”%Function%” ORDER by qs.total_worker_time DESC ; END ‘ [/sql] پاسخ به دیدگاه سیدمحمد حسینی ۱۰ / ۱۱ / ۹۴ - ۱۱:۵۲ با سلام ممنون از همه دوستان و جناب طاهری عزیز جناب زرابی عزیر(اگر اشتباه نکرده باشم) بله شما درست میفرمایید و ممنون از اطلاع رسانی که فرمودید بنده در متن مقاله که نگاه کردم عبارت ST.[text][/text]نوشته شده ولی متوجه نشدم چرا در موقع نمایش به صورت ST.1 نمایش داده میشود. اگر جناب فرید طاهری عزیز راهنمایی کنند ممنون میشوم در حال حاظر، باید به جای عبارت ST.1 در دو خط عبارت ST.text جایگزین شود باز هم ممنون پاسخ به دیدگاه سیدمحمد حسینی ۱۰ / ۱۱ / ۹۴ - ۱۲:۱۷ من فکر کنم یه باگ در سایت هست چون باز هم درست نشون نمیده من در خطوط جدا مینویسم ST . [text][/text] درسته پاسخ به دیدگاه ha_zarabi_vb6@outlook.com ۰۷ / ۱۱ / ۹۴ - ۰۸:۳۹ با سلام و خسته نباشید کوئری بالا خط زیر را ارور می دهد [sql] SUBSTRING(ST.1,QS.statement_start_offset/2+1, [/sql] منظور از st.1 هست که در جاهای دیگر هم تکرار شده من منبع آن را بدست آوردم و کدها را در زیر می گذارم البته کدهای زیر برای همه دیتابیس ها عمل می کند [sql] sp_msforeachdb ‘ IF ”?” NOT IN (”msdb”,”master”,”tempdb”) BEGIN USE [?] SELECT top 100 DB_NAME() as [database], qs.total_worker_time /1000000 As TotalWorkerTime, QS.total_elapsed_time/1000000 As TotalElapsedTime_Sec, QS.total_elapsed_time/(1000000*qs.execution_count) AS [avg_elapsed_time_Sec], QS.execution_count, QS.total_logical_reads/QS.execution_count As Avg_logical_reads, QS.max_logical_writes, ST.text AS ParentQueryText, SUBSTRING(ST.,QS.statement_start_offset/2+1, (CASE WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), ST.)) * 2 ELSE QS.statement_end_offset END – QS.statement_start_offset)/2) AS [Query Text] , QP.query_plan , O.type_desc FROM sys.dm_exec_query_stats QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) QP LEFT JOIN Sys.objects O ON O.object_id =St.objectid Where O.type_desc like ”%Function%” ORDER by qs.total_worker_time DESC ; END ‘ [/sql] پاسخ به دیدگاه مهدی ربانی ذبیحی ۰۶ / ۱۰ / ۹۴ - ۰۵:۴۳ با سلام مقاله ای بسیار کاربردی ومفیدی بود با تشکر از شما دوست نیک اموزی عزیز پاسخ به دیدگاه فرشید علی اکبری ۰۶ / ۱۰ / ۹۴ - ۰۹:۳۶ سلام باید گفت این هم یکی دیگه از مقاله های کاربردی و خوبی بود که به نکات و ریزه کاریهای خوبی اشاره کرده بود. انشاءالله شاهد مقالات دیگه ای از شما دوست عزیز نیک آموزی باشیم. باتشکر و موفق باشید. پاسخ به دیدگاه حمیدقلیپور ۰۵ / ۱۰ / ۹۴ - ۰۲:۳۳ با سلامجناب حسینی مقاله شما حاصل تجربه و تخصص بود و بسیار کاربردی؛ عالی بود.با تشکر پاسخ به دیدگاه مسعود طاهری ۰۵ / ۱۰ / ۹۴ - ۱۱:۳۴ مقاله کاربردی و خوب بود متشکرم پاسخ به دیدگاه سید محمد حسینی ۰۵ / ۱۲ / ۹۴ - ۰۸:۳۲ سلام با اجازه استاد طاهری عزیز اگر منظورتون رو درست متوجه شده باشم و منظورتون از ان تی تی همون Entity Framework باشد، تا جایی که بنده میدونم و در لینک زیر هم این رو گفته که EF از پرسوجوهای Recursive و Hierarchical پشتیبانی نمی کنه https://code.msdn.microsoft.com/windowsdesktop/Recursive-or-hierarchical-bf43a96e ولی شما میتونی از طریق EF کد SQL تون رو اجرا کنید، لینک های زیر میتونه کمکتون کنه: http://www.entityframeworktutorial.net/EntityFramework4.3/raw-sql-query-in-entity-framework.aspx https://msdn.microsoft.com/en-us/data/jj592907.aspx http://stackoverflow.com/questions/915329/is-it-possible-to-run-native-sql-with-entity-framework پاسخ به دیدگاه