Clogging up the sql

jonstahura

Does anyone know why this query is killing my instance.

INSERT INTO [#Cluster_SqlServer_Error_Keys]
SELECT [Id]
FROM [data].[Cluster_SqlServer_Error_Keys_Purgeable](@PurgeDate)


I have killed the spid and stopped the monitoring. It started up again.

My who is active script is giving me this info.

<?query --
INSERT INTO [#Cluster_SqlServer_Error_Keys]
SELECT [Id]
FROM [data].[Cluster_SqlServer_Error_Keys_Purgeable](@PurgeDate)

--?>

(551941ms)RESOURCE_SEMAPHORE

WARAPPSQLMON01 RedGateMonitor SQL Monitor - Repository

priyasinha

Hi,

The query is part of SQL Monitor purge process. It creates a large table in tempDB in the process. That it runs into problems is probably due to a different query in the SQL Server.

Here’s a useful article on msdn which might help with troubleshooting.

http://blogs.msdn.com/b/sqlqueryprocess ... grant.aspx

Thanks,
Priya

jonstahura

I resolved the last issue by deleting the repository and installing the latest update. I deleted the database and had it start fresh.

It worked since the 17th, but now it is back.

I am trying to figure out what is going on.

Right now the query running for 40 minutes is:

<?query --
DELETE [data].[Cluster_Keys]
FROM @Chunk c
INNER JOIN [data].[Cluster_Keys] d WITH (ROWLOCK) ON c.[Id] = d.[Id]
-- FORCE ORDER: The order above is carefully chosen, and this prevents pathological orders.
-- LOOP JOIN: Profiling shows that this tends to improve performance, as long as the number of rows to be deleted is small
-- compared to the number of rows in the table, by encouraging index seeks.
OPTION (FORCE ORDER, LOOP JOIN, MAXDOP 1)


--?>