Click here to monitor SSC

Tony Davis is an Editor with Redgate Software, based in Cambridge (UK), specializing in databases, and especially SQL Server. He edits articles and writes editorials for both the and websites and newsletters, with a combined audience of over 1.5 million subscribers. You can sample his short-form writing at either his blog or his author page. As the editor behind most of the SQL Server books published by Red Gate, he spends much of his time helping others express what they know about SQL Server. He is also the lead author of the book, SQL Server Transaction Log Management. In his spare time, he enjoys running, football, contemporary fiction and real ale.

SQL Server Performance Bugbears

Published 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, 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.




3 Responses to “SQL Server Performance Bugbears”

  1. acbups says:

    I recently ran into a real performance issue that brought our production site to its knees several times, and knocked it flat on its face twice inside a week.

    The web application connects to the [app] database for all the application work it does, as one would expect. However, we also maintain users’ session information in a separate [usersession] database to keep their info straight no matter which web-server their request if fulfilled by through the load balancer.

    It turns out all those calls (about 35/second) were in the form of FROM [usersessions]..[usersessions_table]. No dedicated connection, no owner qualification.

    Now the app has dedicated connections to [usersessions] for that work, and I’m not hyperventilating any more.

  2. GilaMonster says:

    My favorite lurking performance problem is one I’ve run into several times with a vendor app I support. Implicit conversions.

    SELECT field1 FROM tbl WHERE field2 = N’abc’ may look innocent enough, but if the data type of vield2 is varchar, SQL will first implicitly convert the column to nvarchar before doing the filter. The implicit conversion prevents index seeks that may exist on the column.

    Much fun on a several million row table.

  3. randyvol says:

    Recently we converted to a new ERP system. Once we got up on our legs and running, we quickly hit a performance problem.
    Several people immediately suspected SQL Server 2K5 (I knew we should not have moved from SQL Server 2K!)
    We used the ‘onboard’ performance report to show us the Performance – Object execution statistics. The offender leaped off the chart. When we tracked it down, it was a stored procedure that is run frequently to show us what shipments to post.

    So we opened the stored procedure and found that the first step in the procedure was to do a full table scan of all inventory, then join that to order lines in sales orders pending shipment with with a keen little comment from the developer that pretty much said – “if you have a lot of items, this is going to get ugly, but hey what do I know?”. Well, he apparently knew a lot more than whoever spec’d out the code. We had a change made to instead select order lines pending shipment and match to the inventory table – presto! Performance problem solved.

    Yeah – SQL Server may have some bugbears, but I still find that some of the biggest performance problems manifest themselves as ‘queries that work, but are malformed’.

Leave a Reply

Blog archive