یافتن UDFهایی که موجب کاهش کارایی SQL Server می شوند

یافتن UDFهایی که موجب کاهش کارایی 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

چه رتبه ای می‌دهید؟

میانگین ۰ / ۵. از مجموع ۰

اولین نفر باش

title sign
معرفی نویسنده
سید محمد حسینی
مقالات
11 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
سید محمد حسینی
پروفایل نویسنده
title sign
معرفی محصول
title sign
دیدگاه کاربران

    • سلام ببخشید من یه سوال داشتم یه کد نوشم اما معادلش رو تو ان تی تی نمیدونم
      ممنون میشم کمک کنید حتی با هزینه
      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  

    • با سلام و خسته نباشید

      کوئری بالا خط زیر را ارور می دهد

      [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]
          درسته
    • با سلام و خسته نباشید

      کوئری بالا خط زیر را ارور می دهد

      [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 پشتیبانی نمی کنه
      ولی شما میتونی از طریق EF کد SQL تون رو اجرا کنید، لینک های زیر میتونه کمکتون کنه: