Have you looked at my response?

I won't enter the debate about stored procedures or not.

You have some problems in your SQL code. First, why are you using DISTINCT for all the queries? This is...

there's not really a good way and, frankly, you shouldn't. TSQL is a great language for querying databases and manipulating data. It's quite horrible at all other tasks. I'd suggest...

Nuts. Thanks. I wasn't sure when it was introduced.

It...

Nope. There is no way to control the cache in that manner. The one thing you can do, and I think this is available in 2005, is set the system...

And for a slightly different view of the same material:

http://scarydba.wordpress.com/2010/07/23/sql-university-introduction-to-indexes-part-the-third/%5B/url%5D

No, I don't recognize that wait state. Sorry.

Have you run this DMV, sys.dm_os_wait_stats, and collected stats from it? Before, during & after your peak times will tell you quite a...

GilaMonster (8/10/2010)

---

Agreed, but since the rebuild recreates the entire index regardless of what state it's in, it shouldn't be affected by how fragmented the index is, whereas the reorganise likely...

Red Gate SQL Compare Pro comes with a command line utility. It can be completely automated.

GilaMonster (8/10/2010)

---

Grant Fritchey (8/10/2010)

---

It's possible that rebuilding an index (but not defragging) could be longer due to page splits at the top of the key, but not a single page...

Well, that "solution" is pretty dangerous. You just forced every query to go through a recompile, which can be pretty costly, depending on the query. It's likely that you're experiencing...

GilaMonster (8/10/2010)

---

Megistal (8/10/2010)

---

Yes but from my understanding, time to perform a page split from a non clustered index <= time spent for a page split from a clustered index.

If that...

I think you're assuming that SQL Server would move all the pages in the clustered index if a split occurs. It wouldn't. It would only move the information on the...

What business need is being satisfied by pulling all the data? Users do not look at anything past a few hundred rows in general. If you're looking at data migration,...

You'll still possibly end up with page splits in the nonclustered index AND you'll be dealing with the fact that your structure isn't taking advantage of a good clustered index....