When performance tuning queries it might be necessary to track down why a query might have been placed in the Waiter List of the SQL Server Execution Model. More specifically, what resource is the query waiting for to continue its execution? There are actually over 900 different wait types that…
Inside the Database Engine, the SQLOS will schedule tasks to worker threads to be executed by the CPU. This is called non-preemptive or cooperative thread scheduling in where a thread will yield access to the CPU if it has either reached its quantum limit (4 milliseconds) or is waiting on…
The SQL Server Database Engine is actually made up of three distinct parts that work together to process queries. These are the SQLOS, the Relational Engine, and the Storage Engine. When a query is processed, it is passed to the database engine or more specifically, the Relational Engine. This is…
In a previous blog, we discussed the different Table Structures in SQL Server. Another way of determining if a table is structured as a Heap or a Clustered Index is to look at the table operators from the Execution Plan. As always, there are outliers to how things work. But…
As mentioned in a previous post on How Data is Stored in SQL Server, Data rows from a table are stored in the data pages in either an unsorted order (Heap) or a sort order (Clustered Index). If the data has not been provided a clustering key, the data would…
When a database is created in SQL Server a Primary Data file with a .mdf extension and a Transaction Log file with a .ldf extension will be created. Optionally, Secondary Data files could also be created with a .ndf extension. For this post, we will focus on the data files…
This post will describe how a SQL Server will PROCESS a query. The first time through running an Ad Hoc query or Stored Procedure, SQL Server will go through each of the following steps. The first step is to Parse the statement into keywords, expressions, and operators. This is where…
When Transactions are processed they must pass the ACID test. The isolation part of the process is handled by Locks which ensure that Transactions do not interfere with each other. If we look at the example above if both Transaction 1 and Transaction 2 were to operate on the same…
In a previous post we discussed how to work with batches where we created a table named Accounting.BankAccounts and inserted two records. When the two records were inserted we received the following message. This happened because while the two statements were in the same batch they were two separate auto commit…
Transactions are used to ensure that a series of statements written to modify data will pass the ACID test that enforces the data integrity, accuracy, concurrency, and recoverability of the data in the database. The ACID properties of a transaction are… Atomicity – This ties several statements together to ensure…