What I dont understand is why does it work on one database and not the other, is there a specific configuration setting that would effect this?

I've checked for open transactions and there are none

I saw the clustered insert in the plan but have no idea why its there, I dont know what tempdb.CWT_PrimaryKey and there's...

First off why is the statement wrapped in sp_cursorprepexec?

Second, the parameters supplied in both sql scripts are different so will affect query time.

Third, read uncommitted brings in a whole different...

Not sure what the problem is here, I can open the attachment fine, if you have a look it is saved as a .sqlplan file. It may be the version...

OK I just uploaded them again, whats the error when you try to open them?

Thanks for the reply Pooyan

To answer your questions, the writes/sec is quite low on the principal server, averages around 30 and the disk subsytem is the same as the principal...

OK I did some more investigations and found that the select query is acquiring an 'intent shared lock' on a certain page which is blocking the update statement. The interesting...

Hey Guys

Thanks for the solutions, they're all great ideas, I'm looking to change the query structure first and see how that goes.

two questions

Why...

Hey Thanks for the reply, I was actually looking to turn off table locking for the specific tables as there was some clear evidence in some traces that I ran...

Hey Guys apologies for the delay, I've uploaded the proper execution plan. Thanks for all the help so far

Here is the query and I have attached the plan. I assumed a like operator required a index scan? Anyway any help would be appreciated, I have created a non...

Thanks Vishal, worked a charm

Thanks for the reply Vishal,

I'm actually getting an error when I run the SQL:

/*------------------------

EXEC master..sp_MSForeachdb '

declare @db_type as varchar(10)

set @db_type = ''%_lts%''

USE [?]

IF ''?'' like '@db_type'

BEGIN

declare @sql as...

Is it possible to use a variable with the if statement when using the proc sp_MSforeachdb. I'm trying to do the following and getting errors

EXEC master..sp_MSForeachdb '

USE [?]

IF ''?'' like...

Hello

I have been monitoring the following counter SQLServer: General Statistics\User Connections through performance monitor but the user connection count is lower than the number of rows returned by sysprocesses, sp_who...