Feb 09, 2010 06:57 PM|joeastral|LINK
In an effort to always improve my knowledge of performance tuning for SQL Server 2000/2005- I'm hoping others will be willing to share their top N lists of things you should not do in tsql sprocs and user defined functions. I'll submit some of my top no-no's
but I'm hoping others can share their knowledge as well that may be more advanced than mine.
1. Do not use build dynamic SQL statements that cannot be cached for optimal performance plans. So always use parameters in your dynamic sql in your stored procedures. If someone ran a query for the top N slowest queries on your box, none of them should
be something like OriginDate >= '1/5/2009' but instead OriginDate >= @OriginDate.
2. Do not use correlated subqueries on anything that needs to go fast. I only count the rare lazy scheduled report as a candidate for a correllated subquery. Otherwise the question to ask is how can I either put this in a join or restructure my database
so that this query can run more efficiently. (ex. select h.empid, (select i.name from emp i where i.empid = h.empid) as 'name' from hrdata h )
3. Do not be comforted if things run fast on the test database. If you can, always perform a SQL Profiler trace on your demo application before leaving in the lonely cycle of maintenance. I like using the Profiler because it gives you test parameters
to use for all of your stored procedures so you can then take that and get actual execution plans in sql server management studio. Then you can scan for any conspicuous table scans or clustered index scans that don't belong. You'll sleep better at night
knowing your application isn't going to crash in some fatal unoptimized slow down one day.
4. Don't put every function and your mom's functions all in one big query. You might find your tempdb or transaction log growing exponentially larger in any scheduled reports or extract load transform (ETL) tasks.
Please share anything you can as well. Thanks.
user defined function
top 10 things not to do