When using ORM tools, it is a good practice to occasionally enable query logging in a development environment to verify the generated SQL statements.The tip entitled “Markus Winand teaches efficient SQL—inhouse and online.And would you really make a frequent task for that on every MS SQL Server without exception? And is there any recommendation how oft this task should run? As a good start, read these: What Squillman said, but also, you might want to consider using something like Ola Hallengren's scripts, which only do as much work as necessary.You can customize the amount of fragmentation in an index before it's rebuilt or reorganized, and this cuts down on the amount of work involved in reindexing. It depends on your environment, on the type of load (mostly select, mostly insert/update/delete), etc.With the release of SQL Server 2012, Microsoft introduced the columnstore index, a memory-optimized column-oriented index geared toward data warehouse workloads.Although columnstore indexes offered significant performance gains over their traditional counterparts, they were not without their limitations.Updating very large tables can be a time taking task and sometimes it might take hours to finish.
At the moment we had fragmentation of over 90% so i made a rebuild.
Most notably, in SQL Server 2012, there was no way to create clustered columnstore indexes, and the nonclustered columnstore indexes could not be updated.
To update the data, you had to drop and rebuild the index or swap out partitions.
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. When updating in batches, even if the update fails or it needs to be stopped, only rows from the current batch are rolled back. 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.