Hi All, We have bulk load jobs configured using SSIS packages. All the load jobs were working perfectly fine until 2 days back. Suddenly, our load jobs are taking way longer than the normal time. The issue is identified to be the use of UDF as shown below, Note: These jobs were working fine since 3 years without any issues. We load around 16 GB of data each day. No changes to the server and\or SQL Server itself. Any help would be greatly appreciated. Thanks, -Mohsin ALTER FUNCTION [dbo].[convertdate_null] (@input varchar(50)) RETURNS VARCHAR(12) AS BEGIN DECLARE @output DATETIME; SELECT @output = CASE WHEN (ISDATE ( ( CASE WHEN Left(replace(ltrim(rtrim(@input)),'+',''), 1) =1 THEN '20' ELSE '19' END + Right(ltrim(rtrim(@input)), 6)) ) = 1) THEN CONVERT(VARCHAR(12),CONVERT (DATETIME, CASE WHEN Left(replace(ltrim(rtrim(@input)),'+',''),1) = 1 THEN '20' ELSE '19' END + Right(ltrim(rtrim(@input)), 6) ) ) ELSE null END RETURN @output END
First Welcome to Forums, Ultimately speaking about UDFs , they may participate in performance slowness cases either they are scalar function or TVF (Table Valued Function) if they aren’t used properly within your T-SQL query so you can learn more about these cases at my blog : http://www.sqlserver-performance-tu.../12926989-t-sql-performance-optimization-4-6- Kindly bear in your mind that such issues may appear after 3 years due to the increase of stress on DB due to either growth of DB or growth of concurrent users hitting the same T-SQL query at the same time or perhaps some other T-SQL Query is running on the same DB server and consuming massive IO /CPU cost Therefore, kindly post your T-SQL Query that use this UDF with the schema design of relevant tables to help you for determine which case is relevant to your T-SQL Query then we can come out with the appropriate T-SQL/Index enhancements and let us see where we can get then..