Sql-server – Dealing with large log files

2GB of RAM is barely acceptable to run Windows, let alone SQL Server on top.

Obviously your 15GB of data doesn't fit into 2GB of RAM. So, any and every query you run that requires rows that aren't in RAM will require reads from disk.

From there, you can do the math on how much data you're requesting versus how fast your disks can deliver it. Given that the server is so woefully underspecified for memory, one can assume you've got just a single spinner delivering IOPS at miserably slow rate, perhaps 50MB/sec for a sequential read but as low as 1MB/sec with several queries fighting for their share.

Have 5 users each run a query that needs 1GB of data that isn't currently in RAM and you'll be waiting 15+ minutes for the data to crunch its way through the system.

RAM is comically cheap, especially compared to the cost of you wasting your time trying to optimise 15GB into 2GB. Fill the server, or buy a new one.

Also: the Pentium E6300 seems to be a desktop-class processor. You want a computer designed to be a server. Just adding a couple of disks and more RAM may not be satisfactory.

The problem is the use of scalar functions. These are executed once per reference per row, and the current internal implementation is such that this is very nearly as expensive as running a separate query per invocation (18 million times, for 6 million rows with three function references per row).

A quick solution is to convert the function to an in-line table-valued function. These are in-lined into the query text, much in the same way views are expanded before query optimization. So the first step is to translate the function to:

CREATE FUNCTION dbo.dFK_InLine
    (@dt as sql_variant)
RETURNS table
AS
RETURN
    SELECT
        ReturnValue =
        CASE 
            WHEN CA.datatype = 'int' 
                AND @dt >= 19000101 AND @dt <= 20451231
                THEN CONVERT(integer, @dt)
            WHEN (CA.datatype = 'int' AND @dt < 19000101) 
                OR (CA.datatype = 'int' AND @dt > 20451231)
                THEN 1
            WHEN @dt IS NULL 
            THEN 1
            WHEN (@dt < CAST('1900-01-01 00:00:00.000' AS DATETIME) 
                OR @dt > CAST('2045-12-31 11:59:59.000' AS DATETIME)) 
                AND CA.datatype = 'datetime' 
            THEN 1
            WHEN (@dt < CAST('1900-01-01' AS DATE) 
                OR @dt > CAST('2045-12-31' AS DATE)) 
                AND CA.datatype = 'date' 
            THEN 1
            ELSE FORMAT(CAST(@dt AS DATETIME2), 'yyyyMMdd')
        END
    FROM
    (
        VALUES
        (
            CONVERT(varchar(128), SQL_VARIANT_PROPERTY(@dt, 'BaseType'))
        )
    ) AS CA (datatype);

Then modify the source query to use it:

SELECT 
    SD.ReturnValue AS StartDate, -- INTEGER DATE!
    ED.ReturnValue AS EndDate,
    EFD.ReturnValue AS EffectiveDate
FROM OpenEnrollmentWindow oew
CROSS APPLY dbo.dFK_InLine(oew.StartDate) AS SD
CROSS APPLY dbo.dFK_InLine(oew.EndDate) AS ED
CROSS APPLY dbo.dFK_InLine(oew.EffectiveDate) AS EFD
INNER JOIN ProductYear py 
    ON oew.OrganizationProductYearID = py.ID
INNER JOIN Marketplace m 
    ON py.MarketplaceID = m.ID
INNER JOIN Organization o 
    ON m.OrganizationID = o.ID
INNER JOIN Consumer c 
    ON c.OrganizationID = o.ID
LEFT JOIN OpenEnrollmentWindowProduct oewp 
    ON oew.ID = oewp.OrganizationOpenEnrollmentWindowID
LEFT JOIN OpenEnrollmentWindowProductType oewpt 
    ON oew.ID = oewpt.OrganizationOpenEnrollmentWindowID;

All that said, this is still quite an...unusual strategy, particularly the use of sql_variant and the CASE logic. You might get better value from refactoring the design to use a strong types, and a more traditional model.