Updating sql server
In this tip, we will cover a scenario where we update statistics while the database is being used to see if blocking does occur during the statistics update.In order to analyze the behavior of updating statistics, first we need to create a sample database with the option AUTO_UPDATE_STATISTICS set to OFF. Customers( Customer Id INT NOT NULL IDENTITY(1,1), Customer Code NVARCHAR(10) NOT NULL, Customer Name NVARCHAR(50) NOT NULL, Customer Address NVARCHAR(50) NOT NULL, Last Modified DATETIME NOT NULL DEFAULT GETDATE() PRIMARY KEY CLUSTERED (Customer Id), INDEX IX_Customer Code NONCLUSTERED (Customer Code) ) GO You can use Tibor Nagy's tip Populating a SQL Server Test Database with Random Data to fill the previous table with data.This can be done easily by doing a bulk-insert in a new table and then rename the table to original one.The required indexes and constraint can be created on a new table as required.In this tip I show that doing a statistics update does not cause blocking.This is a hot topic in the SQL Server DBA community.If we look at the request_mode column we can see that the locks are schema stability locks (Sch-S) which is compatible with all lock modes except the schema modification (Sch-M) lock mode.
The owning session ID can change for distributed and bound transactions.A value of -2 indicates that the request belongs to an orphaned distributed transaction.A value of -3 indicates that the request belongs to a deferred recovery transaction, such as, a transaction for which a rollback has been deferred at recovery because the rollback could not be completed successfully.The index can always be created once the update completes. Executing the update in smaller batches The query can be further optimized by executing it in smaller batches. The code below updates the records in batches of 20000. Disabling Delete triggers Triggers with cursors can extremely slow down the performance of a delete query.Disabling After delete triggers will considerably increase the query performance. Replacing Update statement with a Bulk-Insert operation An update statement is a fully logged operation and thus it will certainly take considerable amount of time if millions of rows are to be updated.