I have found the following two articles by Hugo Kornelios very informative with regards to cursor optimizations: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx and http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/28/poor-men-see-sharp-ndash-more-cursor-optimization.aspx.

The best help we can give you has already been given: reference Books Online (BOL). See here as a starting place for your studies: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/7d913c8d-b79b-4b1f-93b9-098dd33b07a8.htm.

That link goes...

Well, some manuals (especially these days) aren't very good. But the 'softies really did a nice job with SQL Server's BOL! Most people don't know that. I...

1) As someone else mentioned, partitioning IS and advanced topic. You really should get a a professional to assist you in this endeavour.

2) It is possible that...

I post this for the benefit of others who may search the forums: PLEASE check Books Online before posting questions! It is a WONDERFUL resource! I put...

Peso (12/17/2008)

---

It depends on what you are indexing, of course.

A primary key can have 100% since all records are unique.

An IDENTITY can also have 100% since the values are sequential.

An...

1) what else is running on the server?

2) what is maxdop setting at the sql server level and are you seeing a lot of parallel queries?

3) Have you run track_waitstats_2005?...

I had zoned out when I posted that this date table is more granular than day. :hehe:

CheeseMan316 (12/17/2008)

---

I guess I was incorrect that...

Jeff, I am very interested in your findings! Perhaps this is one of those "step outside the box" situations where we all come out with something very useful and...

Could be worth a try to run the query using the OPTION (RECOMPILE) directive on production to see if it changed the plan.

Are you 100% certain that all objects involved in the query are 100% identical?

Thanks for the repro Gail! Clearly either my memory is faulty (wouldn't be the first time and it seems to be happening more and more since the birth of...

GilaMonster (12/9/2008)

---

Depends...

The optimizer is cost-based. An index seek/bookmark lookup of a given depth takes X cost per row. A table (or clustered-index range) scan takes Y cost per page....