SP3 Some Queries with User Functions Run Much Slower

[fluteplr]

Has anyone seen the following behaviour?

Sql 2000 SP3 and 2 processors.

Some queries that call user defined functons are running much slower than they did with SP1. By a factor of over 10 sometimes over 100.

It only seems to happen on mutiple processor boxes, you can run the same query on a one processor box and it runs in the same tine ut used to?

I saw one hotfix on the MSDN site regarding slow queries and SP3 but it did not seem to address this particular problem

http://support.microsoft.com/default.aspx?scid=kb;en-us;815114

Also before we all go off on other ways to acomplish the job, please remember the following:

1. They ran fine on SP1.
2. They run fine on machines with SP3 and one processor.

 

[tlbroadbent]

I know there was an issue with SQL 7 and slower query performance on multiprocessor servers. I've not seen any performance problems since we've upgraded to SQL 2000 SP3. However, several people in the SQL Server Newsgroups have complained about slower performance.

Some of the standard suggestions were:
1) Update statistics if that hasn't been done since the upgrade.
2) Recompile procedures. I suppose that would include functions.

Have you examined the Estimated Query Plans to see if the plans differ from server to server? I did read a comment in a newsgroup about parallel queries sometimes being slower than non-parallel queries due to increased wait times (whatever that means). Don't know if that is the problem but you could experiment by setting the "cost threshold for parallelism" option to an arbitrarily high value on the 2-processor server and running the queries.

Good luck on this one.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[fluteplr]

Thanks Terry,

That looks promising. I will let you know after I try it over the weekend. I have to borrow one of my clients computers to test it. Since my server only has one processor.