I realise that this is the SQL 2008 forum, this may not work out; the code below requires SQL 2012 or later. Then again, the sad story is that the...

The DELETE statement is deleting multiple rows with the same (nConn, HashId). I would guess the other process is trying to insert rows with the same key values. It seems...

I didn't study this in detail, but one thing I have noticed that people miss in deadlock analysis is that the processes may be involved in a multi-statement transaction. Look...

This question was also posted to microsoft.public.sqlserver.programming. For convenience I repeat my answer here:

All sorts of reasons:

1) Long-running query plan.

2) Infinite loop.

3) Blocking.

There may be more, but those are the...

And so,Is there any way that I can fetch both at the same time(so that it can be completed in 6 mins only).

So did you look at my suggestion for...

To be honest: I don't think it is with Bind's realm to optimize the view - but it is an advice (s)he can forward to the manager.

Yes, attempting to optimize the view is the way to go. When I first got the notification for this thread, I though that there is something fishy here. If it...

The old system tables are now compatibility views for those that have old scripts running. There is a particular reason the old views may not work well here: in those...

That query is so... SQL 2000. Here is a mordernised version:

SELECT 'ALTER TABLE ' + quotename(s.name) + '.' + quotename(o.name) + 
     ...

ALTER DATABASE COLLATE only changes the default collation and the collation of the tables in the system catalog. I got the impression from your post that all the user-table columns...

You are probably hit by the fact that SSMS opens a second connection for Intellisense. Do this:

ALTER DATABASE DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE DB COLLATE...

Going back to the original question, and the origin of the query. What  you can track in SQL Server, with Trace or whatever is the application name, which is something...

Does it happen with a specific stored procedure, or just in random places in the application?

Is the SQL Server instance local to your machine, or is on a different server?

To...

What edition of SQL Server do you have? The optimizer will only consider indexed views in Enterprise Edition. The only way an indexed view can be used in Standard Edition...

We can't tell if you should add more columns to that view - we know nothing about your system.

And I am not sure that an indexed view will actually work...