|
| Featured Contents |
| Question of the Day |
| The Voice of the DBA |
Never is Not the Policy Years ago I worked with a few developers and DBAs that were temp-table happy. As in they defaulted to using temp tables everywhere. This was in SQL Server 6.5, and tempdb was an issue with contention, sizing, and performance. I rewrote so many queries to remove temp tables for our clients that I banned them. I told other developers they could never use temp tables in their SQL code. They, of course, would try to submit code with temp tables in our VCS (Visual SourceSafe at the time), but an early, pre-automated CI/CD would notify me and I'd have the developer rewrite their code. There were situations that didn't perform well with a single query, and we did allow some temp tables. The point wasn't the ban them entirely, but stop them from being a crutch for developers or a first choice. I wanted them to think about the problem first and try to solve it with SQL. If performance was an issue, then we'd look at a temp table. Recently, I saw a post from Jeff Iannucci that noted there are settings you should never change. These were database-level or instance-level settings that he suggests you leave the defaults, or leave a specific setting chosen. An example of the latter is Page_verify set to checksum. The default in older databases, upgraded through different versions, can be none or torn page detection enabled. Fix that if you see it. Examples of other settings are things like Auto Shrink and Auto Close, which shouldn't be enabled. These types of settings should be left off, well, with a caveat from me. There are narrow places where you might want certain things turned on. For example, Auto Close isn't the worst thing in the world, and there places where it makes sense. Dev servers with a large number of databases that aren't often used might get help here in conserving resources. Same for production instances where a lot of older databases are kept around for archival, but we don't want them open and consuming any resources without an explicit connection. I don't mind DBAs or developers having "never" as a policy for certain things, with the caveat that this isn't a forever rule, but one that strongly sets a guideline that we don't create exceptions for without a very good reason. Staffs change, internal knowledge is lost or narrowly shared, and often inexperience can creep into your environment. These strongly worded guidelines, even with "never" in them, help to keep everyone on the same path. Strong opinions, loosely held. We change our minds and grant exceptions when there is evidence to do so. Steve Jones - SSC Editor Join the debate, and respond to today's editorial on the forums |
| Featured Contents |
I decide to rebuild all indexes with this command:
