|
Many people seem to see select as an innocent command. It should not hurt anybody or anything. It is granted to anybody on production database for technical people or non-technical people. For people who don't know, don't see the danger, they don't know how it could hurt. This is the same as electricity, fire, flood or any natural disaster. What you don't know won't hurt you? I often got question as below: I don't understand... is there an issue with this query? It's just selecting (read-only).... By default SQL Server uses read committed isolation and places a shared lock on select. That means it blocks update, insert and delete because it ensures you get the right records under concurrent transaction processing. SQL Server snapshot isolation uses row versioning to provide isolation without locking resources. You need a fast IO drive for tempdb. To compare how Oracle read committed does, please read Thomas Kyte's book "Expert Oracle " the chapter about concurrency and isolation. Tom uses the word "the other database" to reference the direct competitor product SQL Server.
The danger of sql server select query mostly lies on long running query by mistake on syntax, forgotten whereclause, select overly large number of joined tables without filters, poor indexes on large tables etc. I list a couple real-world scenarios below : Danger 1 ) a huge select with (lock escalation) that runs a long time, so the core tables insert, update, delete are blocked for a long time. DBA intervention is needed. I have verified it is still a problem on SQL2005 SP2 9.04266 and on 8.0.2282, but I cannot reproduce on SQL2008SP1 10.0.3789 except using table-lock (See my SQLSaturday#57 presentation). It seems SQL Server lock-escalation has changed. If you use snapshot isolation, this particular problem won't happen, but making sure you have set up adequate version-store resource (tempdb again) on your server to cover the workload. If you are using SQLServer2008, you should get a fast drive for tempdb, like SSD FusionIO PCIe(2) at 5 Gbps, then switch to snapshot isolation. Danger 2) a large select .... into #temp with improper whereclause filters until the server runs out of tempdb space. DBA intervention is needed. You can restrict tempdb space to a hard-coded size. The default is unrestricted. Unless you reserve tremendous of amount of server space to cover accidental wrong query, you will enter this emergency zone. Danger 3) a unreasonably large select .... also consumes server resources, CPU cycle, RAM logical read, IO physical read etc. What you don't know won't hurt you? huh?
|
