Podcast
Questions and Answers
When tables or indexes are subsequently accessed, what optimization can SQL Server perform?
When tables or indexes are subsequently accessed, what optimization can SQL Server perform?
- Query rewriting
- Partition elimination (correct)
- Partition reorganization
- Index rebuilding
Which data types can the partitioning key include?
Which data types can the partitioning key include?
- VARCHAR(MAX), NVARCHAR(MAX)
- XML, TIMESTAMP, NTEXT
- TEXT, NTEXT, NVARCHAR(MAX)
- INT, VARCHAR, DATETIME (correct)
During partitioning, what is the purpose of boundary points?
During partitioning, what is the purpose of boundary points?
- To merge small partitions into a larger partition
- To determine the size of each partition
- To determine the order of data sorting in the table
- To set upper and lower limits for each partition (correct)
What is the purpose of creating a partition scheme?
What is the purpose of creating a partition scheme?
In SSMS, what can be used to view the distribution of rows in partitions?
In SSMS, what can be used to view the distribution of rows in partitions?
How can you determine the number of rows in each partition of a table?
How can you determine the number of rows in each partition of a table?
What type of argument does the $PARTITION function accept for the partitioning column name?
What type of argument does the $PARTITION function accept for the partitioning column name?
Which format is used to store table versions on disk with FILESTREAM technology?
Which format is used to store table versions on disk with FILESTREAM technology?
What transaction log size threshold triggers an automatic checkpoint?
What transaction log size threshold triggers an automatic checkpoint?
What happens when SCHEMA_AND_DATA is selected in the context of storing data for a table?
What happens when SCHEMA_AND_DATA is selected in the context of storing data for a table?
Flashcards
Partition Elimination
Partition Elimination
This SQL Server optimization technique avoids unnecessary scanning of partitions by excluding irrelevant ones.
Valid Partition Key Data Types
Valid Partition Key Data Types
INT, VARCHAR, and DATETIME are valid data types that can be used as partitioning keys in SQL Server.
Boundary Points in Partitioning
Boundary Points in Partitioning
Boundary points define the upper and lower limits for each partition in the partitioning process.
Partition Scheme
Partition Scheme
Signup and view all the flashcards
Inspecting Row Distribution in SSMS
Inspecting Row Distribution in SSMS
Signup and view all the flashcards
Using $PARTITION to Find Row Count
Using $PARTITION to Find Row Count
Signup and view all the flashcards
$PARTITION Function Parameter
$PARTITION Function Parameter
Signup and view all the flashcards
FILESTREAM Storage Format
FILESTREAM Storage Format
Signup and view all the flashcards
Automatic Checkpoint Threshold
Automatic Checkpoint Threshold
Signup and view all the flashcards
Using SCHEMA_AND_DATA
Using SCHEMA_AND_DATA
Signup and view all the flashcards
Study Notes
- After tables are accessed, SQL Server may perform partition elimination, partition reorganization, index rebuilding, or query rewriting for optimization.
- Partition keys can include data types such as INT, VARCHAR, and DATETIME.
- During partitioning, boundary points define upper and lower limits for each partition.
- A partition scheme is created to specify the filegroup where each partition is stored.
- You can view the distribution of rows in partitions using "The Disk Usage by Partition" report in SSMS.
- The number of rows in each partition of a table can be determined using $PARTITION.
- The $PARTITION function accepts the column name of the partition key as an argument.
- FILESTREAM technology utilizes an unstructured format for storing table versions on disk.
- The transaction log size threshold that activates an automatic checkpoint is 512MB.
- Selecting SCHEMA_AND_DATA when storing a table's data saves all table data to disk and records transactions.
- Memory-optimized tables, also known as Hekaton or In-Memory OLTP tables.
- The @object_name parameter is utilized to specify a table's name.
- Indexes on memory-optimized tables must be created within a CREATE TABLE.
- Nonclustered indexes perform better than nonclustered hash indexes for queries using inequality predicates.
- Newly inserted rows into a table are temporarily stored in a table known as deltastore.
- Leaf nodes contain the actual data pages of a table.
- Approximately 99% of data is accessed through the primary key in OLTP applications.
- FILLFACTOR is used to specify the amount of free space to leave on each page at the leaf level of an index.
- GUID (Globally Unique Identifier) is 16 bytes long.
- When multiple values share the same bucket, this is referred to as a hash collision.
- Every memory-optimized table must have at least one Index.
- A Columnstore index divides table rows into chunks of 102,400 to 1,048,576 rows each.
- 4 million data rows at 100% page fullness fit within 20,000 data pages.
- A table expected to grow to K million rows should have 2K hash buckets.
- When SQL Server scans all table pages to retrieve necessary rows, this is called a clustered heap scan.
- If a table is partitioned, an operation requires more than 5000 locks on a table or partition.
- Bulk Update lock type allows multiple threads to load data concurrently into a table while blocking other processes.
- SQL Server detects deadlocks through an internal process called a deadlock monitor.
- An Explicit transaction begins with BEGIN TRANSACTION.
- Snapshot isolation utilizes optimistic concurrency for Write and Read operations.
Studying That Suits You
Use AI to generate personalized quizzes and flashcards to suit your learning preferences.
