shahgols (11/8/2013)
Hi everyone and many thanks for your responses.I had already checked on differences between table schemas, indexes, data, and also on wait/locks/etc. but didn't find any differences. I ran a update statistics statement in both environments with sample of 100%. I checked index fragmentation, which is about 5% on both servers. Any other checks I can do?
One more question, based on the xml data I posted, it looks like the staging environment assigns a lot more RAM for the same query, isn't that correct? Could that be the reason why?
Sure the memory grant can be important because it can lead to significantly more PHYSICAL IO as you get stuff spooled to disk for hashes, sorts, etc. What were the IOs for each query (and CPU)? I said you should check those. 🙂 NOTE though that you MUST use Profiler - the engine LIES to SSMS when UDFs are in play.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service