shahgols (11/7/2013)
Hi all,I have a query that calls on a UDF. In staging, has the same number of records and schema, it runs in 25 seconds. On production it used to run in seconds, but for some reason today it is very slow, to the point that if I let it run, it could run for a day or two. The execution plans are the same, the same indexes are being called, etc. The only difference I see is that the call to the UDF on production executes much slower (verified with Profiler) and the CPU usage on production is way, way high, compared to staging. Has anyone ever seen this sort of behavior??
Thanks.
Are you absolutely, 100% positive that a) the code is exactly the same, b) the server builds are the same, c) the execution plans are the same (be sure to compare the XML and look for SET differences, etc)?
I wrote a chapter for the SQL Server MVP Deep Dives 2 book entitled "Death by UDF". It is very aptly named and I STRONGLY encourage you to factor out the UDF and watch your queries run orders of magnitude faster!!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service