2. Functions can result in poor performance.

3. Functions can result in REALLY REALLY BAD performance. 🙂

4. Both can make it very difficult to debug/maintain code.

5. Functions (or...

1) Drop and add database activity can indeed lead to os file frag due to leaving holes in the file structure on disk.

2) Index maint can lead to os file...

I am betting you are down to simply having slow disk IO, or perhaps your query is being blocked sometimes. Did you try your query with NOLOCK hints? ...

Rem (5/7/2008)

---

One more thing. This table is truncated and repopulated on a weekly basis. No insert/delete or update are done to the table after the load. How...

Rem (5/7/2008)

---

Ok, I understand now.

How can I see if my table is fragmented and how I can defrag? what about the disk file fragmentation, how I can defrag it?

Thanks...

Grant Fritchey (5/7/2008)

---

Oooh! I may have to try that just to see what happens. I'll bet you looked great on that one. But here's a question, how long did it...

And thus you see why the optimizer chooses a table scan - almost 1/2 the cost of the bookmark lookups!

I would look at table fragmentation, disk file fragmentation,...

Grant Fritchey (5/6/2008)

---

The only thing I can think of would be to lower the cost threshold for parallelism. But then you're likely to see multiple threads in queries that will...

I will add that I hope you undid your affinity settings too and put them back to default. I have yet to see anyone less than a TRUE expert...

You sure didn't give us much information we could use to help you, but I would start by doing an index analysis to see if you are missing some helpful...

Here is the bible on deadlock troubleshooting: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

Rem (5/5/2008)

---

Hello GilaMonster,

Here is the information:

rowcount whole table:+/-36000000

expected rowcount return: 244918

CREATE TABLE [dbo].[REP_table] (

[ID] [int] NOT NULL ,

[Qty] [int] NOT NULL ,

[Price] [money] NOT NULL ,

[FPrice] [money] NOT NULL ,

[Fline]...

rbarryyoung (5/6/2008)

---

Really?!? Could you site a reference for this? The only general...

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx

Check out CXPACKET waits. Also the best practice on (properly tuned) OLTP systems is to set max dop = 1.

If your IO subsystem can't keep up with parallelism of...

When you are modifying the code in QA, are you using the guts of the stored procedure and executing it as a batch? I often do this. You...