SQL Server 2019 : Administration SQL Server Part 2

Explore how a covering index improves performance by avoiding lookups, comparing covering and clustered indexes, and analyzing execution plans with SSN filters.

Explore how the include clause adds columns to the index leaf, saving space and boosting performance. Compare include and covering indexes by examining execution plans for performance gains.

Learn to consolidate redundant indexes by combining last name, first name, and middle initial with include columns, test with scripts, and compare execution plans to reduce storage.

Explore how a unique constraint creates a unique index by default, compare clustered and nonclustered indexes, and learn to avoid duplicate indexes while examining execution plans.

Explore how indexing and column order affect query performance in SQL Server 2019. Create clustered and nonclustered indexes, compare execution plans, and use statistics to optimize requests.

Identify how duplicate indexes degrade performance, inflating execution times and maintenance costs; avoid creating unnecessary indexes to save time and disk space.

See how over indexing hurts performance by slowing inserts, wasting maintenance time, and consuming disk space; learn to monitor and avoid excessive indexes in transactional SQL Server workloads.

Explore how to optimize data retrieval by choosing between cluster and noncluster indexes, forcing indexes, and creating covering indexes to improve reads and performance.

See how deactivating an index can improve insert performance, compare cluster indexes, reactivate as needed, and test multiple scenarios to choose the fastest approach.

Avoid clustering an index on a NEWID column due to fragmentation and poor page density, which degrades performance as shown by index physical stats.

Learn how filtered indexes index only selective values to shrink the index and speed queries, demonstrated by creating a filtered cluster index on a date field.

Demonstrates creating a filtered index, examining execution plans, and forcing index usage with hints to fix incorrect cache plans, while discussing memory implications.

Choosing a clustered index drives performance by the order of stored data. Schedule nightly maintenance to address fragmentation, and understand that unique constraints can create a clustered index by default.

Explore how the SQL Server 2019 columnstore index boosts performance for large data warehouses through compression and batch-mode execution, comparing it to traditional indexes.

Create an indexed view in SQL Server 2019, starting with a clustered index and adding a nonclustered index to speed up large, aggregated queries in data warehouses.

Fragmentation can slow performance by causing index scans and extra disk space use from page splits. Set per-index fill factors and apply reorganize to manage fragmentation.

Demonstrates how inserts split a data page, creating non-contiguous leaf pages within an index and revealing page fragmentation in sql server 2019 administration.

Track page splits in SQL Server 2019 using extended events; this lecture demonstrates enabling extended events, creating a custom event, starting the session, and querying results.

Understand how the fill factor reserves free space to prevent page splits, with 70 percent vs 100 percent defaults, and how to apply it during index creation, rebuilding, or reorganizing.

Compare reorganize and rebuild of indexes in SQL Server 2019 administration, explain online vs offline options, fragmentation thresholds, and when to perform reorganize, rebuild, or update statistics.

Learn how to rebuild an index using the abort after wait option to prioritize user transactions, manage blockers, and rollback when index maintenance encounters blocking.

Consolidate duplicates and indexes to save space and time, avoid over indexing, deploy a clustered index on large tables, and use covering indexes with include for critical queries.

Explore the configuration and modification of databases, including the various options when creating a database, how to activate those options, and the pitfalls to avoid.

Demonstrate how to define the database files when creating a SQL Server database, including multi-file design, data file locations, sizing, and growth settings to optimize performance.

Define and create a filegroup in a SQL Server 2019 database, distribute data and log files across disks for performance, and assign objects to specific filegroups.

Demonstrates activating a contained database in SQL Server 2019, isolating the user database from the master and configuring containment type to allow a single user to access one database.

demonstrates why and how to change a database's compatibility mode to match an application's needs, including considerations from backup restores across older SQL Server versions such as 2012 and 2016.

Learn how to take a SQL Server 2019 database offline, use emergency mode to handle corruption, bring it back online, and recover from missing files while preserving recovery options.

Demonstrates switching a SQL Server database to single_user, restricting access with restricted_user, and enabling read_only to control connections, highlighting security and connection management.

Learn how to create a database by attaching a file, detach and reattach databases, and use attachment syntax to ensure proper association of data and log files.

Deactivate auto close and auto shrink to avoid performance degradation from opening and closing the database, then enable auto create statistics and auto update statistics to keep stats current.

Learn how the target recovery time guides checkpoint frequency in SQL Server 2019, explaining how changing the checkpoint interval from zero to minutes affects recovery and online database behavior.

The checkpoint flushes modified memory pages to disk, keeping the memory pool and disk in sync, and runs automatically every 60 seconds.

Learn how to change the database owner in SQL Server, including default ownership, two methods to transfer ownership, and security cautions when granting rights.

Demonstrates how to enable and test the trustworthy option on a database, shows how runtime context and privileges affect access, and warns about security risks.

Explore the differences between full, simple, and bulk-logged recovery models, including how log backups and checkpoints affect disk space and point-in-time restoration.

Explore how MAXDOP controls CPU usage for queries in SQL Server 2019, showing default zero meaning use all processors, and how to adjust primary and secondary processors for large queries.

Learn how the checksum option protects page integrity by validating disk writes and reads, and understand why it is often left at default with no performance difference.

Demonstrates how to view and adjust SQL Server collation settings, including case sensitivity and accent sensitivity, and apply a chosen collation when creating or configuring databases.

Perform a quick demo of a broker service in SQL Server 2019 administration, including activating the broker, creating messages and queues, encrypting messages, and deactivating the service.

Demonstrates how set ansi_nulls and set quoted_identifier influence sql server 2019 behavior during table creation and data retrieval, with practical, step-by-step examples.

Explore database level configurations in SQL Server 2019 to adjust the query optimizer and cardinality estimation.

Demonstrates how to activate change tracking as an alternative to CDC, with a 24-hour retention period, and shows tracking of category table changes and versioned updates.

Learn why cross-database ownership chaining (db chaining) is off by default, the security risks it exposes, and how to test cross-database procedures safely.

Discover the Query Store feature in SQL Server 2019, activate it on a database, and explore options like max execution plans, max storage, and query capture mode for performance insights.

Explore accelerated recovery in SQL Server 2019, demonstrating dramatically shorter recovery times and smaller log data when crashes occur by comparing databases with and without the option.

Examine autogrow settings in sql server 2019 administration, with an initial 16 MB size, testing growth increments on large data loads and monitoring per-database performance.

Wrap up a course on modifying and creating the database by highlighting careful use of options such as auto close and auto shrink due to performance risks.