The doggedness with which certain SQL Server "myths" cling to the coat of the SQL Server community is really quite astonishing. They are the proverbial "sheep ticks"; very annoying and incredibly difficult to dislodge. And even when you think you've managed it, their embedded jaws remain to provoke further irritation.
A classic case in point is the "SELECT…INTO" myth, which states that its use should be avoided as it will cause serious blocking issues in SQL Server. Way back in the days of version 6.5, i.e. circa 1996, SQL Server had a serious problem with SELECT…INTO. Due to limitations in the way page allocation worked, these statements caused massive contention in tembdb. In effect, tempdb was "locked" for the duration of the statement, and so any other process that required access to tempdb was blocked. Many a time, a long-running query would pour its results into a new table via a SELECT…INTO, and effectively lock an entire production application for maybe an hour until KILLed by the DBA. It was an unforgettable experience whenever it happened, remembered vividly both by the users and the harassed DBAs.
The problem was essentially fixed in version 7.0, more than 10 years ago, and yet the memory of the problem lives on so vividly in the minds of many DBAs that, to this day, the myth persists, and SELECT…INTO is effectively banned from the SQL Servers they manage. Of course, other DBAs have accepted that SELECT…INTO has been rendered harmless, and have cautiously released it into the wild. And yet, it seems that the jaws of this particular tick remain resolutely embedded in our skin.
In SQL Server 2000, the allocation system for tempdb was improved to greatly reduce contention issues, but the fact remained that SELECT…INTO, and other processes that made heavy use of tempdb, could still cause significant contention. In response, with SQL 2000 SP3, Microsoft introduced a new trace flag, -T1118. When activated, it forced "uniform extent allocations instead of mixed page allocations" and helped with tempdb scalability. Although still available in SQL Server 2005 and 2008, further architectural improvement has rendered this flag redundant…or has it? Speculation persists to the effect that if you are performing SELECT…INTOs then you should set the -T1118 flag in order to deal with the resulting tempdb contention. And yet others in the community have reported not being able to reproduce a single case where the flag has helped improve throughput in SQL Server 2005 or 2008.
It's not the fact such myths arise that surprises me, but the way they manage to persist for so long, and morph into new forms. Why is this? Is it a lack of clear information? Or difficulty in finding the information that is out there? What other "ticks" do we need to kill off once and for all, and how can we do it? Could a resource such as asksqlservercentral help de-flea the community?
Full article...
