Slideshare uses cookies to improve functionality and performance, and to provide you with relevant advertising. If you continue browsing the site, you agree to the use of cookies on this website. See our User Agreement and Privacy Policy.

Slideshare uses cookies to improve functionality and performance, and to provide you with relevant advertising. If you continue browsing the site, you agree to the use of cookies on this website. See our Privacy Policy and User Agreement for details.

17.
Scalar User Defined Functions• The Good – Re-Usable code• The Bad – Runs once per record in record set – They don’t take advantage of parallelism – They use Nested Loop joins regardless• The Ugly – So ugly it’s hidden from IO statistics17 Natural Born Killers

18.
Scalar UDF Solutions and Alternatives• If you have to use them: – View the actual IO usage in Profiler• If you can replace them: – Look at Table Value Functions – Look at CLR18 Natural Born Killers

24.
Table Variables Vs. Temp TablesTable Variables• Fast when used with small data sets.• Have a limited scope• Use less locking and logging resources than temp tables – http://sql.richarddouglas.co.uk/archive/2011/06/rollback-gotchas-part-2-2.html• Estimated statistics always show 1 record• Cannot be altered after they are declared• Mythbuster - MAY be memory only, this isn’t guaranteed• Generally faster with smaller data sets27 Natural Born Killers

26.
Parameter Sniffing• What is it? – It’s all a matter of statistics• How do I know when it will affect me? – Monitor the IO and CPU – This means baselining your environment• What are my options? – Rewrite dynamic queries – Query Hints: – WITH RECOMPILE – OPTIMIZE FOR – Plan Guides29 Natural Born Killers