SQL Server 2008 has now introduced minimally logged inserts into tables that already contain data and a clustered index. What happens is the initial inserts may be fully logged if the data pages they are filling already contain data. However any new data pages added to the table will be minimally logged if all the requirements below are met. Trace flag 610 must be on Database recovery model must be bulk-logged or Simple Inserted data must be ordered by the clustered index To turn on the trace flag for your current session: DBCC TRACEON (610) INSERT INTO dbo.MyTable SELECT * FROM ORDER BY 1 DBCC TRACEOFF (610) This new change differs dramatically from the previous requirements for minimal logging. Previously there could be no clustered index and a table lock had to be acquired on the target table. For more information, visit: Minimal Logging Changes – MSDN BlogContinue reading ...
- SQL Server Database Optimization Guide In the troubleshooting guide we went over the different physical bottlenecks that can
- Yet Another Temp Tables Vs Table Variables Article The debate whether to use temp tables or table variables is an old
- Using Union Instead of OR Sometimes slow queries can be rectified by changing the query around a bit.
- User Defined Functions and Performance There is definitely a lack of awareness in the SQL world regarding the