Disclaimer: I'm a senior sysadmin who's trying to help our development team speed up the production database. Our users are hurting, bad. Please don't shoot me for not being great with SQL

The database server is a Windows Server 2008 R2 with the latest updates applied, running SQL Server 2008 R2 Enterprise Edition with SP2+CU6. We also have a test server with the identical OS/SQL server installed. The production server has 96GB of RAM, where 76GB is free at this moment. The test server has 16GB of RAM, where 8GB is free at the moment. The production server is set to maximum use 90GB of RAM, while the test server is set to use maximum 12GB of RAM.

Edit: I forgot to mention an important part.. the production database is mirrored offsite (async), while the test DB is not.

We have the following functions.

pl.get_overtime_from_pl (Main function)

USE [Customapp]

ALTER FUNCTION [pl].[get_overtime_from_pl]
      -- Add the parameters for the function here
      @officeId INT = 9999 ,
      @candId INT = 0 ,
      @onlyOverTime BIT = 0
      -- Add the column definitions for the TABLE variable here
      CandId INT ,
      CandName VARCHAR(100) ,
      OfficeName VARCHAR(60) ,
      OfficeId INT ,
      DateFrom CHAR(8) ,
      DateTo CHAR(8) ,
      ConsName VARCHAR(100) ,
      ConsId INT ,
      NormalTime DECIMAL(9, 2) ,
      OverTime DECIMAL(9, 2) ,
      TwoWeekTendency DECIMAL(9, 2) ,
      MonthTendency DECIMAL(9, 2) ,
      PeriodId VARCHAR(10) ,
      CompanyId INT
        IF @candId > 0 
            SET @officeId = 9999
        IF @officeId = 0 
            SET @officeId = 9999

            @dEnd SMALLDATETIME ,
            @dStart2weekTendency SMALLDATETIME ,
            @dStartMonthTendency SMALLDATETIME
        SELECT  @dStart = dStart ,
                @dEnd = dEnd
        FROM    pl.get_overtime_periods_dates(GETDATE())

    --Find Start Date for this 2-week period
        SET @dStart2weekTendency = DATEADD(dd, -( DAY(@dEnd) - 1 ), @dEnd)
        IF DAY(@dEnd) >= 16 
            SET @dStart2weekTendency = DATEADD(dd, 15, @dStart2weekTendency)

    --Find start date for the current monthperiod
        SET @dStartMonthTendency = DATEADD(m, 11, @dStart) 

    -- 1 = OverTime
    -- 2 = Normal Time
    -- 3 = 1 or 2 - both
        DECLARE @iWageType INT
        IF @onlyOverTime = 1 
            SET @iWageType = 1
            SET @iWageType = 3

        DECLARE @2weekPeriodFrom INT ,
            @monthPeriodFrom INT

        SELECT  @2weekPeriodFrom = MIN(yearperiod)
        FROM    pl.get_periods(@dStart2weekTendency, @dEnd) d

        SELECT  @monthPeriodFrom = MIN(yearperiod)
        FROM    pl.get_periods(@dStartMonthTendency, @dEnd) d

        INSERT  INTO @OvertimeRows
                SELECT  h.employeenumber CandId ,
                        ka.FNAME + ' ' + ka.SNAME CandName ,
                        a.NAME OfficeName ,
                        a.ID OfficeId ,
                        p.toUkeLonnFDT DateFrom ,
                        p.toUkeLonnTDT DateTo ,
                        ko.firstname + ' ' + ko.lastname ConsName ,
                        ko.id ConsId ,
                        SUM(h.NormalTime) NormalTime ,
                        SUM(OverTime) OverTime ,
                        SUM(CASE WHEN h.yearperiod >= @2weekPeriodFrom
                                 THEN OverTime
                                 ELSE 0
                            END) TwoWeekTendency ,
                        SUM(CASE WHEN h.yearperiod >= @monthPeriodFrom
                                 THEN OverTime
                                 ELSE 0
                            END) MonthTendency ,
                        h.yearperiod ,
                FROM    pl.get_worktime_from_pl(@dStart, @dEnd, @iWageType) h
                        INNER JOIN SalaryDatabase.dbo.Period p ON p.yearperiod = h.yearperiod
                                                              AND h.companyid = p.companyid
                        INNER JOIN ( SELECT employeenumber ,
                                            companyid ,
                                            CAST(homedept AS SMALLINT) homedept_int ,
                                     FROM   SalaryDatabase.dbo.personal WITH ( NOLOCK )
                                     WHERE  ISNUMERIC(homedept) = 1
                                   ) k ON k.employeenumber = h.employeenumber
                                          AND k.companyid = h.companyid
                        INNER JOIN AVDELING a ON a.ID = k.homedept_int
                        INNER JOIN KANDIDAT ka ON h.employeenumber = ka.ID
                        INNER JOIN KONSULENT ko ON ko.ID = ka.KONSULENT_ID
                        INNER JOIN dbo.fn_get_groupoffices(@officeid) offices ON offices.id = a.id
                WHERE   ( @candId = 0
                          OR @candId = h.employeenumber
                GROUP BY h.employeenumber ,
                        ka.FNAME + ' ' + ka.SNAME ,
                        h.yearperiod ,
                        p.toUkeLonnFDT ,
                        p.toUkeLonnTDT ,
                        a.ID ,
                        a.NAME ,
                        ko.ID ,
                        ko.fornavn + ' ' + ko.etternavn ,
                ORDER BY h.employeenumber


pl.get_overtime_periods_dates (The previous function uses this)

USE [Customapp]
ALTER FUNCTION [pl].[get_overtime_periods_dates] 
    -- Add the parameters for the function here
    @dt smalldatetime
@dates TABLE 
    -- Add the column definitions for the TABLE variable here
    dStart smalldatetime, 
    dEnd smalldatetime
    -- Fill the table variable with the rows for your result set
    declare @dStart smalldatetime, @dEnd smalldatetime
        --Find Start date for 12 month period
    if day(@dt)>=23 
        set @dEnd=dateadd(d,14,DATEADD(mm, DATEDIFF(mm, 0, @dt ), 0))
    else if day(@dt)<9
        set @dEnd=dateadd(mm,-1, dateadd(d,14,DATEADD(mm, DATEDIFF(mm, 0, @dt ), 0)))
        set @dEnd=dateadd(d,-1,DATEADD(mm, DATEDIFF(mm, 0, @dt  ), 0))
    set @dStart=dateadd(d,1,dateadd(yyyy,-1,@dEnd))

    insert into @dates
    values(@dStart, @dEnd)


If I run this function directly as a query I get results within one second. At the same time I'm monitoring I/O on the server, and the tempdb is not touched.

If I call this function from a query, like this:

USE [Customapp]
DECLARE @date DATETIME = '20131104', @office INT = 9999, @candidate INT = 0, @onlyovertime BIT = 1;
SELECT * FROM pl.get_overtime_from_pl (@date,@office,@candidate,@onlyovertime)

Then it suddenly takes 18 seconds to give me an result. I can see that the tempdb (or DB's, I've split them up into four files - one per core) are running through the roof with every IOPS we have available.

The very weird part here is that the function AND the query above runs within one second on our test server. I've looked at the server options, and they look identical. The only major difference is that we do nightly maintenance plans on the production server (reorganize index, update statistics).

I've been sitting in the SQL profiler trying to make sense out of this. I can see that SSMS is using slightly different connection settings than our custom application, namely "SET ARITHABORT OFF" while SSMS defaults to ARITHABORT ON. A few days ago I tried switching that back and forth, and suddenly both queries were fast (under 1 second). When I'm trying the same thing today, ARITHABORT doesn't seem to have any effect whatsoever, the second query is always slow on our production server.

From my non-DBA perspective it seems like the function can use cached in-memory results, while the query cannot? I can't grasp why the second query is using the tempdb, while the function isn't..

What should I do next, other than begging our CTO to hire a DBA?

  • 1
    My first thought - parameter sniffing gone bad. Can you get the plans? In test only (don't do this in prod) - make sure you're the only one connecting to SQL Server, run CHECKPOINT then DBCC FREEPROCCACHE, then run the query in both fast and slow modes. Then SELECT * FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle); should return two rows. – Simon Righarts Nov 4 '13 at 21:25
  • What does pl.get_overtime_periods_dates look like? – Zane Nov 4 '13 at 21:28
  • @SimonRigharts Interesting, been reading about parameter sniffing and I'm pretty sure you're spot on - the values fed to this function changes nearly every time. I'll try the steps you provided. – pauska Nov 4 '13 at 21:36
  • @Zane Sorry, forgot that one. It's up in the question now. – pauska Nov 4 '13 at 21:36
  • Have you tried putting OPTION (RECOMPILE) on the queries? Have you tried changing from multi-statement table-valued functions to inline table-valued functions? – Aaron Bertrand Nov 4 '13 at 21:51

You could create a tiny table:

CREATE TABLE OvertimePeriodsDates

...then call get_overtime_periods_dates() once a day to populate this table. Then just replace the call to get_overtime_periods_dates() with OvertimePeriodsDates in the start of get_overtime_from_pl. In principal, this shouldn't change much of anything, but it may be enough to inspire a new query plan that doesn't take 18 seconds.

| improve this answer | |
  • Not a bad idea, there's really no need to compute a 12-month period every single time the function is used. Do you think a SQL agent running the update should be enough? – pauska Nov 5 '13 at 22:28
  • Yep. Or add a LastModified field to the OvertimePeriodsDates table, and have whatever process runs the sproc also update this table beforehand, if the LastModified field is not set to the current date. – Jon of All Trades Nov 5 '13 at 22:45

Not the answer you're looking for? Browse other questions tagged or ask your own question.