Stretch Database is a feature of SQL Server where data can be split between on-premises storage and cloud storage. With Stretch Database, cold, historical data is kept in the cloud and active data is kept on-premises for maximum performance. Stretch Database requires no changes to client applications or existing Transact-SQL queries, so you can implement … Continue reading What is Stretch Database?
Moving the msdb, model, and tempdb databases files
All system databases, except the resource database, can be moved to new locations to help balance I/O load. To move the msdb, model, and tempdb databases, perform the following steps: For each file to be moved, execute the ALTER DATABASE … MODIFY FILE statement. Stop the instance of SQL Server. Move the files to the … Continue reading Moving the msdb, model, and tempdb databases files
TempDB summary
Tempdb is a special database available as a resource to all users of a SQL Server instance, you use it to hold temporary objects that users, or the database engine, create. In many respects, tempdb files are identical to the files that make up other SQL Server databases. From the perspective of storage … Continue reading TempDB summary
Difference between Lock and Latch
Locks everywhere is a good start to understand how SQL Server provides logical consistency. Every operation has a lock and latch. What does Latch mean? Latch protects memory on Buffer Pool, is a method that provides physical consistency. SQL Server does operations in memory, that means, it read the page from disk and put that … Continue reading Difference between Lock and Latch
SQL Server threads architecture
SQL Server retrieves threads from Windows. The SQL Server configuration setting max worker threads (set at instance level) determines how many threads will be retrieved. SQL Server has its own internal scheduling system, independent of the scheduling performed by the operating system. Instead of using Windows threads directly, SQL Server creates a pool of worker … Continue reading SQL Server threads architecture
Wait Statistics
The first post about waits on SQL Server was regarding what SQL Server Wait means, a brief explanation and the concept might be difficult to catch at first. When the task needs to wait for a resource, it is placed on a list until the resource is available. SQL Server keeps detailed internal records of … Continue reading Wait Statistics
Locks everywhere
In this post I'm going to talk about locks on SQL Server. Locks are necessary, they are used in all operations in the database. Don't get confused about blocking, locking and blocking are totally different. When we talk about lock, doing something in the database, like an update and select though will cause a type … Continue reading Locks everywhere
