Confusion about SQL Server snapshot isolation and how to use it -
i newbie ms sql server. few months experience maintaining sql server sps. reading transaction isolation levels optimising sp , quite confused. please me below questions:
if run dbcc useroptions in clientdb, default value isolation level 'read committed'. mean db set isolation level set read_committed_snapshot on ?
is set transaction isolation level (at transaction level) same set read_committed_snapshot on (at db level)? mean if db has snapshot enabled, can set isolation level in sp , process data accordingly?
is allow_snapshot_isolation similar above?
i have sp starts off long running select statement dumps it's contents temp table. uses temp table update/insert base table. there 8 mil records being selected , dumped temp table, similar number of total rows updated/inserted. problem facing sp takes disk space. client db , not have permissions check disk space/log size etc in db. not know if tempdb/tempdb-log taking disk space or clientdb/clientdb-log is. disk space can reduce as 10gb @ 1 go! causes transaction log run out of disk space (as disk full) , sp errors out. if use snapshot isolation level, disk space more affected? uses tempdb versionize data?
what want this: set transaction isolation level snapshot. select temp table. begin transaction , update/insert base table ... 1 mil records. in loop until records processed. end transaction. think idea? should initial select kept out of transaction? in reducing load on transaction logs?
an isolation level of "read committed" isn't same thing setting read_committed_snapshot on. setting read_committed_snapshot on sets default isolation level all queries. query or procedure uses "read committed" isolation level does use snapshot isolation. see isolation levels in database engine , set transaction isolation level in books online.
when read_committed_snapshot database option set on, read committed isolation uses row versioning provide statement-level read consistency. read operations require sch-s table level locks , no page or row locks. when read_committed_snapshot database option set off, default setting, read committed isolation behaves did in earlier versions of sql server. both implementations meet ansi definition of read committed isolation.
allow_snapshot_isolation doesn't change default isolation level. lets each query or procedure use snapshot isolation if want to. each query want use snapshot isolation needs set transaction isolation level snapshot. on big systems, if want use snapshot isolation, probably want rather changing default isolation level read_committed_snapshot.
a database configured use snapshot isolation take more disk space.
think moving log files bigger disk.
Comments
Post a Comment