Clogging up the sql

jonstahurajonstahura Posts: 18
edited July 29, 2013 1:43PM in SQL Monitor Previous Versions
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

Comments

  • 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
    Priya Sinha
    Project Manager
    Red Gate Software
  • 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)


    --?>
Sign In or Register to comment.