SQL Server Performance BugbearsPublished 11 July 2007 9:49 am
Whilst trying out Robyn and Phil’s excellent TSQL Newsfeed, some of the first articles that I saw was Linchi Shea’s fascinating Blog entries about the performance impact of not using owner-qualified stored procedure calls. I had no knowledge of this particular performance hit, which seemed to be coming out at around 15% and 25%. Prefixing with the owner will stop SQL Server from placing a COMPILE lock on the procedure, while it determines if all objects referenced in the code have the same owners as the objects in the current cached procedure plan.
More recently, I read Tony Rogerson’s explanation of the beneficial effect on performance of using parameterisation and stored procedures. It inspired me to do a brief survey of programming practices that could be slowing down code, and of ways of fixing that, or avoiding the problems altogether.
With a bit of exploration I came across a surprisingly good MSDN article on patterns and practices for Improving SQL Server performance. Often, when Books on line tackle issues like this, they seem to descend into fragmented paragraphs and strangulated prose, but this piece was excellent.
A short but very useful summary of performance tips, largely gleamed from sqlserverperformance.com, is provided by Simon Harriyott. On sql-server-perfomance itself, the article on Temporary Tables vs. Table Variables and their effect on SQL Server Performance caught my eye. Here, Dmitry Tsuranoff ran the tests between the two types of table on both SQL Server 2000 and 2005, and found, to his surprise, how poor SQL Server 2005 was with some operations involving large temporary tables.
Dennis Forbes, in his epic series of articles on SQL Server performance, has an interesting section on using Indexed Computed Columns. It is one of those techniques that is easily forgotten in the heat of “getting the job done”, but it is easy to see where it could be very handy.
Blocking is, of course, a common reason for poor performance. Our own Pop Rivett and the Case of the Rogue SPIDs provides an ingenious means of seeking out those occasional blocks. For some reason, it’s taken me a long time to discover the wonderful stored procedure that is used for monitoring blocking in the article, How to monitor blocking in SQL Server 2005 and in SQL Server 2000. The dbo.sp_blocker_pss80 stored procedure looks like an occasional ‘lifesaver’ that should be part of every DBA’s script library.
As far as books on SQL Server performance go, my favourite has always been Ken England’s recently-updated, Microsoft SQL Server Performance Optimization and Tuning Handbook. Of course, there is also Microsoft Press’ own SQL Server 2000 Performance Tuning Technical Reference by Steve Adrien DeLuca.
I suspect that one could easily be overwhelmed by the complexities of performance optimisation. However, I suspect that Phil Factor is right when he says that developers should always work with the slowest possible development server that one can find, so as to force developers to be conscious of slow code. It can be a novel experience for a developer to have to completely rewrite code several times until it runs efficiently, but it is the way of the world for a seasoned database developer.