SQL Server CPU Scheduler basics

UMS Basics

https://blogs.msdn.microsoft.com/psssql/2013/08/13/how-it-works-sql-server-2012-database-engine-task-scheduling/

https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-updated-scheduling-algorithms/

Advertisements

Tip for database restore: Don’t get into issues with log file initialization

Whenever we want to restore a database/transaction log backup to a different server from primary server as part of requirements like setting up AG Secondary,  preparing Test/Dev Server databases, we may get into issues of log file initialization taking longer time and longer waits on PAGEIOLATCH_EX.

This can happen particularly when log file grows to large size. Ex: 100 or 200 GB

Tip in this case is shrink the original transaction log file in primary database to much lesser size (Ex: 20 GB) and then take a DIFF/FULL backup depending on the situation and then use that backup for subsequent restore in AG secondary, Dev/Test servers.

Also, if transaction log file could not be shrinked physically despite having free space within the file, please follow the tip in below article. Sometimes, multiple transaction log backups are required on primary server to be able to shrink the log file.

                                                  Physically shrinking SQL Transaction log files

 

 

 

AlwaysOn Availability Groups concepts

https://blogs.msdn.microsoft.com/psssql/2013/04/22/how-it-works-always-onwhen-is-my-secondary-failover-ready/

https://www.brentozar.com/archive/2015/09/synchronous-alwayson-availability-groups-is-not-zero-data-loss/

https://www.brentozar.com/sql/sql-server-alwayson-availability-groups/

https://www.brentozar.com/archive/2016/09/asynchronous-database-mirroring-vs-asynchronous-availability-groups/

https://blogs.msdn.microsoft.com/saponsqlserver/2012/02/07/sql-server-2012-alwayson-what-is-it/

 

Transaction log I/O concepts: Optimizing log block I/O size

http://www.sqlskills.com/blogs/paul/benchmarking-1-tb-table-population-part-2-optimizing-log-block-io-size-and-how-log-io-works/

https://blogs.msdn.microsoft.com/sql_pfe_blog/2013/03/28/observing-sql-server-transaction-log-flush-sizes-using-extended-events-and-process-monitor/

http://www.sqlshack.com/sql-server-transaction-log-part-1-log-structure-write-ahead-logging-wal-algorithm/

https://www.sqlskills.com/help/waits/logbuffer/

https://blogs.msdn.microsoft.com/sqlcat/2013/09/10/diagnosing-transaction-log-performance-issues-and-limits-of-the-log-manager/

https://www.brentozar.com/archive/2012/05/how-big-your-log-writes-spying-on-sql-server-transaction-log/