My job as a .NET consultant takes me to a lot of different companies and sometimes I'm asked to fix an existing performance problem. 99% of the time, the performance problem is related to a poorly written stored procedure, trigger or sql statement.
I'm amazed how often I see stored procedures and triggers that are > 1000 lines, which in and of itself is not the worst practice, but definitely a flag that maybe there's too much business logic in the stored procedure/trigger.
The other thing I see quite often are excessive use of cursors and temporary tables. I can understand this trend because it's really easy to create and use cursors and temporary tables in SQL Server.
Whenever I have to fix a problem at a client and I find it's related to poorly written SQL Server stored procs/triggers, I make it a priority to educate the developers who wrote the original code and show them alternative options.
For example, most cursors can be re-written with a while loop and temporary tables can (most of the time) be replaced with Table variables instead. Sometimes, its just a matter of re-writing the SQL statements to use a sub-select.
I don't understand why more developers don't know these basic best practices for SQL Server:
Bottom Line:Avoid cursors when you can:
they cause locking and server resources are consumedAvoid temporary tables:
they cause more locking and more stored procedure recompiles