Here are a few good web resources on parameter sniffing.

http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html

http://nflanagan.blogspot.com/2006/07/parameter-sniffing.html

http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/c992528dc5c6d8e4?hl=en&lr=&ie=UTF-8&oe=UTF-8

http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

http://www.sqlmag.com/Article/ArticleID/94369/sql_server_94369.html

Basically there are a few ways to defeat it. This sounds to me like you're running into this issue.

Is there...

Can you post the query plan for the above so we can see what's happening?

Also, what's the sp_configure value for 'cost threshold for parallelism' ? Usually the default of...

Also, just so you know... if you choose to trace deadlocking in the log file, there's a new trace flag in 2005 that gives you a lot more information. ...

Please post an example of the queries and query plans from SQL Server 2000 and 2005. Until we see what exactly the query is doing, its difficult to determine...

Yep. Parallelism. Looks like SQL's having issues with selectivity and dropping back to "guess mode" and parallelizing the query. Either increase the sp_configure value for 'cost threshold...

1. Create an Event in SQL Server. Configure it to listen to the SQL Server namespace at \\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER. Make the query of the alert SELECT * FROM...

As indicated before, post the entire execution plan for each.

Also, just as something to check, make sure you're not getting nailed by the parameter sniffing issue. If your between...

Simply put, there's too much legacy code and legacy mindfame out there. Marketing disaster. I'd prefer some optimizer enhancements that detect cursor calls and decide on a set-based query plan.

Also, looking at the query plan you posted, looks like you're going into some massive parallelism on that query. Depending on what's going on, that could potentially kill your...

If you want to get technical, any time you see a spool operation in a query plan, its sorting in TempDb. It has nothing to do with the SORT_IN_TEMPDB...

Also the reason why Microsoft has improved the interface for managing a partition in SQL 2008's Management Studio.

Totally agree though. The T-SQL implementation for partition management is klunky. ...

Jeff has the right idea up there. Those non-clustered indexes are probably hurting your performance unless they are also used to find the records to delete in the first...

Oh yeah... Not to mention the fact that I find it entirely more reliable than MSCS. Show of hands,...

Compare as in how? Are you comparing the data or the table structures?

In either case, you can use tools from RedGate or use Microsoft Visual Studio Team Edition for...

Perry Whittle (8/8/2008)

the first question has to be

What virtualisation platform do you intend to use?

It is not uncommon to have a physical node (active) and a virtual node (passive).

If...