Red Gate forums :: View topic - Clogging up the sql
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Monitor 3
SQL Monitor 3 forum

Clogging up the sql

Search in SQL Monitor 3 forum
Post new topic   Reply to topic
Jump to:  
Author Message
jonstahura



Joined: 28 Sep 2012
Posts: 13

PostPosted: Wed Jul 17, 2013 1:12 am    Post subject: Clogging up the sql Reply with quote

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
Back to top
View user's profile Send private message
priyasinha



Joined: 03 Jan 2007
Posts: 531

PostPosted: Thu Jul 18, 2013 9:37 am    Post subject: Reply with quote

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/sqlqueryprocessing/archive/2010/02/16/understanding-sql-server-memory-grant.aspx

Thanks,
Priya
Back to top
View user's profile Send private message
jonstahura



Joined: 28 Sep 2012
Posts: 13

PostPosted: Mon Jul 29, 2013 6:43 pm    Post subject: Reply with quote

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)


--?>
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group