True, but I have changed only one thing: a unique index to a nonunique index. Now call me old fashioned, but I believe that there should be either no difference...

Jeff Moden (9/7/2009)

---

I'd love to see a performance test between HID's and the Nested Set Model for both up and downline queries as well as the addition and deletion of...

1) did you check to make sure that no agent jobs were running, like backups, checkdb, index maint, etc?

2) what else was happening on the server? on the IO...

to the OP: if you DO test your system out on SQL 2008 with HierarchyIDs, please post back (or even better write an article) about the experience, performance, etc....

do NOT just accept what the DTA gives you and move on....

>>There has to be a better way.

not sure why you think that. Actually there is a better way - a knowledgeable and experienced tuner doing his/her job. The...

I second the priority boost comment. I also note max memory is defaulted - not best practice, but likely not a culprit here.

>>but change this one index and it all goes down the gurgler for no apparent reason.

Maybe it simply isn't apparent to you. You have changed the information and options...

DBArtisan has been doing this for about a decade now, but it is the...

HAH!! 😎

select count(*) from #mytemptable 😀

Per this document (which is the BIBLE for performance tuning) "SQL Server 2005 Waits and Queues SQL Server Best Practices Article", LCK_M_S means:

"Occurs when a task is waiting to acquire...

1) it doesn't have to be a bug if the optimizer for 2000 derives a suboptimal query plan for a complex query involving 15 tables in a mishmash of views....

Dave Ballantyne (9/1/2009)

---

john.imossi (9/1/2009)

This runs but it returns no data, I'm surprised it will run at all to be hoenst. When I used to create something similar to this...

Try dynamic sql in the sproc. That will get you hard-coded values for every execution just like if you ran it in SSMS window with explicit values. You...