The code looks to me like it will be likely to use that index. Have you checked the execution plan to see what it is doing?

What changed on the machine? It's unlikely that the server instance just suddenly decided to stop working. Surely something lead to the initial problem. I'd look there to figure out...

If the process you're currently using has a command line, try using PowerShell. You can fire a powershell command right from SQL Agent.

Not a problem.

Just so you know, I cheated in figuring out what was up. I used Red Gate SQL Prompt. It's a software my company makes. It's invaluable when working...

You were missing a parenthesis after the first date operation. You don't need all those parentheses. You can have it just like this:

SELECT [displaydate],

...

The first query is an old style ANSI 89 syntax join with the join criteria in the WHERE clause. The second is a modern style join with the join criteria...

Since you know the specific query, your job got easier. You want to look at the code to ensure it's not using functions on columns in the WHERE clause or...

The single, dead-last thing I would do is utterly preempt the optimizer in that fashion, especially with an undocumented DBCC command. Code, indexes, statistics, statistics, statistics, would all get more...

Yep, if you're capturing both start and stop, you could span files.

You need to purchase a license from Microsoft. You may then need to reinstall that software after you get the fully licensed copy.

I was thinking it could be parallel processes. That would show why you couldn't see the statement and plan on all of them.

SELECT queries alone will not cause deadlocks. There has to be data modification queries built in there somewhere.

Do you notice if there are other entrees for the same session_id?

The deadlock is probably being caused by multiple slow points, not just the select statement. I'd suggest getting the execution plans for the two queries involved in the deadlock and...

ramana3327 (1/3/2014)

Thanks for the replies.

I am able to create a user defined data collector from a template or create a custom collection but I am not able define this...