Indianrock (12/1/2010)

---

Thanks Kevin. I believe we have the auto update to stats happening asynchronously ( that feature turned on ). When I say cache flush, I'm referring to...

The only thing that matters here is the OP's request: "please help me to decide on using UDF?"

I will help you with that: NEVER, EVER, EVER USE SCALAR...

1) have you looked into asynchronous stats updates?

2) 60GB is WAY to high for max memory on 64GB box. Have you monitored for paging? I would try maybe...

1) did you run update statistics on EVERYTHING with FULLSCAN?

2) have you done a waitstats analysis while the queries are running for hours?

3) have you done a fileIO stall analysis...

I looked around and couldn't find anything either Jeff. Perhaps if it does exist it is buried in the list of Edition features?

george sibbald (11/27/2010)

---

Hmmm, interesting Jeff, a poor mans partitioned table. It looks as if it would take a fair amount of effort to get to this set up though if...

Review this link, and the two associated posts:

http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

Also try here if you are getting the intraquery parallelism deadlock:

http://blogs.msdn.com/b/bartd/archive/2008/09/24/today-s-annoyingly-unwieldy-term-intra-query-parallel-thread-deadlocks.aspx

1) the easy answer is to drop a FusionIO card into the server and use that for tempdb.

2) I wonder if a plan guide can be created that will somehow...

Refactoring this stuff is WAY beyond a forum - it simply cannot be effectively done. I agree with the other poster who said it is time to call in...

The obvious thing is to review all scheduled activity that occurs around the times of concern. I recommend sqljobvis to view job activity as a gantt chart - amazing...

My vote is for 5.5GB max memory for this machine, assuming the only thing running on it is the SQL Server relational engine. Monitor for paging to ensure this...

1) ALWAYS use aliases for your tables AND columns (quite a few columns are not aliased)! Try this:

select a.8,b.aasdfddd

from aaaa a, bbbbb b

where a.srno = b.srno and isnull(status,'Y')='Y'...

I would like to put in a plug for SQLJobVis - a wonderful FREE utility that helps with agent job analysis: http://www.sqlsoft.co.uk/sqljobvis.php

1) if you check the code for sp_updatestats I think you will find that it isn't so selective as you imagine. It updates anything with 1 or more rows...

The latest version of Ola's index maintenance sproc now has the ability to specify indexes, so you can set up rolling jobs to cycle through them and not crush your...