Aaron,

In the simulations I have run, there are a number of small changes which need to be made to get good performance - the most important of which is a...

Jeff Moden (6/13/2009)

---

Ok folks... NOW you can test for performance because NOW you can generate substantial amounts of test data quite easily. The following code creates a million rows...

This is a daft QOD.

The correct answer is clearly SQL_VARIANT - and that isn't even listed as an option!!!

Paul

P.S. Hugo: you may find that your sarcas-ometer just exploded 😉

rob.patterson (6/13/2009)

---

I guess the only thing left is to determine whether or not there is any reason to even run the alter index rebuild/reorganzie on objects where the page_count value...

You could, of course, also populate a table with all the valid values for the flag combinations and directly foreign key on that.

Sounds like a challenge 🙂

I can be done with FOREIGN KEYS as shown below. (I'm not saying it isn't without its own problems however).

As a side-benefit, this approach also...

distill (6/10/2009)

---

Using min/max setting would work my question is; When you set a min/max setting for the passive instance (when running in normal operation) does this instance always use the...

You could also put tempdb on a RAM disk.

Aaron,

Thanks for providing the example code. I had tried to create one myself, but was struggling with the datatype for the PurchaseDateTime column on the CurrentPurchases table, as it...

Jeff Moden (6/12/2009)

---

It causes the whole thing to act like a cursor with a recomplile for every row if you don't include the target table in the FROM clause with...

I would usually write it like this:

--(FOR AFTER UPDATE trigger ON dbo.CurrentPurchases)

UPDATECur

SETdbo.CurrentPurchases.RunningTotalCost = Old.RunningTotalCost + Cur.Cost,

dbo.CurrentPurchases.PurchaseDateTime = DATEDIFF(ms, Old.PurchaseDateTime, Cur.PurchaseDateTime)

OUTPUT

Old.ProductTypeID,

Old.Cost,

Old.PurchaseDateTime

INTOdbo.Purchases

FROMdbo.CurrentPurchases Cur

JOINinserted ON inserted.ProductTypeID = Cur. ProductTypeID

JOINdeleted Old ON Old.ProductTypeID =...

The first eight pages for a structure are allocated from mixed extents (unless trace...

Fish.

KB,

Your script contains an error - there is an unwanted comma before each UNION ALL.

Having fixed that and run it, the query runs sub-second on my SQL 2K5 SP3 Dev...

Hey Rob,

Can you post the results that show you that fragmentation is increasing together with the query that generated the results please?

I want to make sure that you are comparing...