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

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

VLF (Virtual Log Files)

To see how many VLFs you have solely look at the number of rows returned by DBCC LOGINFO. The size and number of VLFs you'll have depends largely on the size that the chunk is when it's added to you transaction log. There is no general rule how to determine the best values for the … Continue reading VLF (Virtual Log Files)

There is insufficient system memory in resource pool

Doing crash and recovery tests on my local machine I got the SQL Server instance not going online. After trying the third time to bring my instance online thinking was something else problem I saw the SQL Server errolog file and I could see the problem. Not enough memory, but wasn't on my machine, was … Continue reading There is insufficient system memory in resource pool

Wait wait wait…

Let's talk about why we have to wait and how to understand the wait types. Paul Randal in his post Wait statistics, or please tell me where it hurts said: A thread is using the CPU (called RUNNING) until it needs to wait for a resource. It then moves to an unordered list of threads … Continue reading Wait wait wait…