If you could somehow link all the SQL Servers in the world together and then capture a Profiler trace on it, it would be extremely interesting to analyse the results. I bet table scans and therefore missing indexes would be quite high on this list. I wonder if recompilation of stored procedures called sp_ would be in the top ten culprits, eating up precious CPU cycles and causing wait time? I've been using SQL Server since 1999 as a Developer/DBA and it's easy to be cynical about developers and the way they code queries. I have lost count of the number of times I've seen developers create user stored procedures called sp_ not knowing that this forces recompilation ever time the query's run. I started thinking about why they did this, I mean, it happens a lot.
1. Microsoft call their own system stored procedures sp_, so that they recompile when they're executed. Developers see this and think it's a standard and trying to be good developers, replicate this Microsoft standard.
2. Developers like to adhere to naming conventions to make coding easier. I expect they think that sp_ means stored procedure but I wonder if it actually stands for system procedure? Surely though they should be called SSP (System Stored Procedures?)
3. They just don't know, and no-one has ever told them. It's eays to criticise other people's lack of knowledge, but I've seen this countless times. How can they all be uninformed? Maybe they don't have a DBA or SQL developer on site.
Thinking about this, I think Microsoft are slightly at fault here because of the reasons above. People who develop in SQL Server may not have the time to pick up a decent programming book and start learning the nuances of the product, they have to get a solution from SQL within the whole project they're working on and also to a deadline. Is it any surprise they don't pick up a DBA or SQL Server programming book? Perhaps if they did then I would be out of a job. Microsoft have done a very good job at creating a self-tuning RDBMS, however, I can't help but think the easiest solution to this problem is when someone creates a stored procedure called sp_, the informational message should be 'Stored Procedures called sp_ will be recompiled every time they are run and this may affect performance'. Surely this would force even the most reluctant developer to investigate this?
I'm off to the SQL Server Connect website and logging this recommendation. If Microsoft added this in a future Service Pack, I wonder if that World-Wide Trace would get any smaller?