Clogging up the sql

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

Clogging up the sql

Postby jonstahura » Wed Jul 17, 2013 1:12 am

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
jonstahura
 
Posts: 13
Joined: Fri Sep 28, 2012 2:28 pm

Postby priyasinha » Thu Jul 18, 2013 9:37 am

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
priyasinha
 
Posts: 532
Joined: Wed Jan 03, 2007 5:02 pm

Postby jonstahura » Mon Jul 29, 2013 6:43 pm

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)


--?>
jonstahura
 
Posts: 13
Joined: Fri Sep 28, 2012 2:28 pm


Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 0 guests