We had to restart the SQL service.

Thanks Sue.
That link lead me to more troubleshooting tips.
Unfortunately, we still haven't found the culprit(s).

Here are some of the links we have come across.

Leave enough memory over for OS and applications.
Configure "min server memory (MB)" & "max server memory (MB)". Min server memory (MB) should be around 1/3 Max server memory (MB)
EXEC...

Joie Andrew (6/29/2016)

---

You could also modify the previous query to use sys.master_files. That will prevent you from having to loop through each database and you may not need a temp...

We haven't had any performance problems for a while now, though we still use the UDFs.

Some of the UDFs were rewritten and perform much better than their previous versions.

Following up...

You could create a SQL account, give it the necessary permissions and use it in your connectionstring.

Alternatively, create a stored procedure with EXECUTE AS. Give execute permissions on the SP.

Create a point with your latitude and longitude, use STBuffer with the radius value.

What is your question exactly?

How long the query is supposed to take? It depends and I don't know 😛

Feels like homework......

IF OBJECT_ID('TempDB..#DBSizes') IS NOT NULL

DROP TABLE #DBSizes

CREATE TABLE #DBSizes

(

DBNameVarchar(128),

FileTypeVarchar(5),

CurrentSizeMBDec(9,1),

SpacedUsedMBDec(9,1),

FreeSpaceMBDec(9,1),

SpacedUsedGBDec(9,3),

FreeSpaceGBDec(9,3),

SamppleDTSmallDatetime

)

DECLARE @SQLVarchar(1000)

SET @SQL = 'USE [?]

SELECTDBName= DB_NAME(),

FileType= DF.type_desc,

CurrentSizeMB= SUM(CAST(DF.size / 128.0 AS Dec(9,1))),

SpacedUsedMB= SUM(CAST(FILEPROPERTY(DF.name, ''SpaceUsed'') / 128.0 AS Dec(9,1))),

FreeSpaceMB= SUM(CAST((DF.size...

@jacob Wilkins,

No unfortunately I don't, but in the attached plans are the same, but execution times differ greatly.

@GSquared,

Our hardware has been checked and all is good....

Hugo Kornelis does a good write up of the overhead involved when calling an UDF and the bad decision taken by the query optimizer. A few big SQL names in...

Thanks for joining in GSquared.

The view is just one of many objects that use UDFs. You are right about static values being much better, but alas, that's not how everything...

We found more UDFs (No tables or objects involved in of them) that are also all of a sudden very slow.

I still have not found the reason why these UDFs...

Thanks for your insights guys!

I had tried OPTION (RECOMPILE) to no avail.

We removed the UDF altogether and replaced it with a simplified DATEADD. This also didn't help.

The problem with just...