SQL Server by Harsh Kumar

In SQL Server, there are several database states that can help you understand the health and status of a database. Here are some of the different database states in SQL Server: Online : This is the normal state of a database when it's available and ready for use. When a database is online, users can connect to it, run queries and perform transactions. Offline : This state indicates that a database is not available for use. When a database is offline, it can't be accessed or modified. You can take a database offline using the SQL Server Management Studio or T-SQL scripts. Restoring : This state indicates that a database is currently undergoing a restore operation. When a database is being restored, it's not available for use until the restore process is complete. Recovering : This state indicates that a database is currently recovering from a failure, such as a crash or unexpected shutdown. During the recovery process, SQL Server is bringing the database to a consistent stat...

RUNNING STATE - The running state in SQL Server refers to a task or request that is currently being executed. When a user executes a query or an application initiates a transaction, SQL Server assigns resources to that request and starts executing it. The request will remain in the running state until it completes or is terminated. To see which queries are currently running in SQL Server Management Studio, you can open the "Activity Monitor" and look for the "Processes" section. This section shows a list of all currently executing requests, along with information about their resource usage, status, and other details. Queries that are currently executing will have a "Running" status. For example , if you run the following query in SQL Server Management Studio: SELECT * FROM Orders The query will be in the running state while it's executing, and you'll see it in the Activity Monitor with a "Running" status. RUNNABLE STATE - The runnable s...

In SQL Server, a sleeping or awaiting command session is a database connection that is currently connected to the server but is not actively executing any commands. This state can occur for various reasons, such as: When a user connects to the database, a session is created even if the user is not performing any actions. This session will remain open until the user disconnects, and it will be in the sleeping state. When a user executes a command that takes some time to complete, the session will remain in the awaiting command state until the command finishes executing. When a user uses connection pooling, the session may remain open even when the user disconnects, and it will be in the sleeping state until a new connection is made. When a session is in a sleeping or awaiting command state, it is still connected to the database and holding resources. These resources can include locks on tables or memory, depending on what the session was doing before entering the sleeping state. While s...

STATISTICS IO and STATISTICS TIME are used to measure the performance of a query in SQL Server. STATISTICS IO shows the amount of I/O performed by SQL Server during the execution of a query. It can help identify which tables and indexes are being read the most and whether the query is performing optimally. STATISTICS TIME displays the amount of time taken by the CPU to execute the query and the total time taken for the query to complete. It can help identify queries that are taking a long time to execute. To use STATISTICS IO and STATISTICS TIME, you can turn on the options using the following command: SET STATISTICS IO ON SET STATISTICS TIME ON Then, run your query as usual: SELECT * FROM MyTable WHERE Column1 = 'Value1' After the query has finished executing, the query execution plan will be displayed in the results pane. Along with the execution plan, you will also see the statistics for I/O and time. Here's an example output of STATISTICS IO : Table 'MyTable...

The SQL Server transaction log is a fundamental component of the database architecture that records all the modifications to the database. The transaction log ensures that the database can be restored to a consistent state after a system failure or user error. The transaction log is composed of a sequence of log records, each of which describes a single modification to the database. Each log record contains a Log Sequence Number (LSN) that uniquely identifies the record and its position in the log sequence. The transaction log is divided into Virtual Log Files (VLFs), which are fixed-size contiguous blocks of disk space that contain the log records. The size of the VLFs depends on the initial size of the log file and the autogrowth settings. One of the enhancements to the SQL Server transaction log is the Accelerated Database Recovery (ADR) feature, introduced in SQL Server 2019. ADR uses a more efficient and faster recovery mechanism that eliminates the need to roll back uncommitted ...

In SQL Server 2022, the Ledger feature is a new addition that provides a built-in way to track and audit changes to data in a database. The Ledger feature maintains a complete history of all changes made to specified tables, including inserts, updates, and deletes, and allows you to easily view and analyze this history to track changes and identify issues. To use the Ledger feature, you first need to enable it on a database by setting the "IS_LEADGER_ENABLED" database configuration option to "ON". Once this is done, you can then specify which tables you want to track using the "ALTER TABLE" command with the "WITH (SYSTEM_VERSIONING = ON)" option. For example , the following command enables the Ledger feature for a table named "Customer" in a database named "Sales": ALTER TABLE [Sales].[dbo].[Customer] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[CustomerHistory])); This will enable the Ledger feature for the "Custo...

In SQL Server 2022, the Query Store feature includes a new feature called optimized plan forcing , which allows you to force a specific query plan for a given query, even if the Query Optimizer would choose a different plan. This can be useful in cases where you know that a specific query plan performs better than other plans that the Query Optimizer might choose. To use optimized plan forcing with Query Store, you first need to enable the feature by setting the "QUERY_STORE_OPTIMIZED_PLAN_FORCING" database configuration option to "ON". Once this is done, you can then use the "ALTER DATABASE" command to specify a forced plan for a specific query. For example , the following command forces a specific plan for a query with the query ID of 1234: ALTER DATABASE [DatabaseName] SET QUERY_STORE FORCED_PLAN (QUERY_ID = 1234, PLAN_ID = 5678); This will force the specified plan with ID 5678 to be used for any future executions of the query with ID 1234, even if the ...

In SQL Server 2022, the degree of parallelism (DOP) and the max degree of parallelism (MDOP) continue to play an important role in optimizing query performance, but there are some changes and enhancements to these features that are worth noting. The DOP determines the number of processors that are used to execute a single query or index operation in parallel. The MDOP, on the other hand, specifies the maximum number of processors that can be used for parallel execution of a single query across the entire SQL Server instance. By default, the MDOP is set to 0, which means that SQL Server can use all available processors for parallel execution. One important change in SQL Server 2022 is the introduction of "Intelligent DOP", which is a new DOP control mechanism that automatically adjusts DOP based on system performance and resource utilization. This feature can help optimize query performance in highly dynamic and variable workloads by more efficiently using system resources. ...

Parameter sensitivity optimization is an important technique for improving the performance of SQL Server queries by taking into account the variability in query parameter values. In SQL Server 2022, there are several methods available for optimizing query performance based on parameter sensitivity. One such method is the use of query store , a feature that allows for the capturing of query execution plans and runtime statistics, as well as the comparison of these plans across different parameter values. This can help identify cases where the same query executed with different parameter values has different performance characteristics and can be used to optimize the query plan accordingly. Another method is the use of query hints , such as OPTION(RECOMPILE), which causes the query to be recompiled each time it is executed, taking into account the current parameter values. This can help avoid parameter sniffing issues, where a query plan optimized for one set of parameter values may not...

SQL Server Agent Jobs are automated tasks that can be scheduled to run at specified intervals, such as daily, weekly, or monthly. They can be used to perform a variety of operations, such as database backups, data transfers, and maintenance tasks. Via GUI -  To create an SQL Server Agent Job using the GUI, follow these steps: Open SQL Server Management Studio (SSMS) and connect to the SQL Server instance. Expand the SQL Server Agent node and right-click on Jobs. Select New Job from the context menu to open the New Job window. In the General tab, enter a name and description for the job. In the Steps tab, add one or more steps to the job. Each step defines a specific task to be performed. In the Schedules tab, define the schedule for the job. You can choose from a variety of options, such as daily, weekly, or monthly. In the Notifications tab, configure the job to send an email notification when it completes or fails. Here's an example of an SQL Server Agent Job that performs a da...

Maintenance Plans are a feature in SQL Server that allows you to automate common maintenance tasks, such as backups, index maintenance, and database integrity checks. By creating Maintenance Plans, you can save time and ensure that critical tasks are performed on a regular basis. Here's how to create and use Maintenance Plans in SQL Server: Create a new Maintenance Plan : To create a new Maintenance Plan, open SQL Server Management Studio and go to the "Management" node. Right-click on "Maintenance Plans" and select "New Maintenance Plan". This will open the Maintenance Plan Designer, which allows you to create and configure your plan. Add Maintenance Tasks : Once you've created a new Maintenance Plan, you can add maintenance tasks to it. This includes tasks such as backing up databases, rebuilding indexes, updating statistics, and checking database integrity. To add a task, drag it from the "Toolbox" pane and drop it onto the "Desig...

Database Mail is a feature in SQL Server that allows you to send email messages from the database engine. This can be useful for sending alerts and notifications, as well as reports and other information to users. Here's how to set up and use Database Mail in SQL Server: Enable Database Mail : Before you can use Database Mail, you need to enable it in SQL Server. This can be done using SQL Server Management Studio or by running a T-SQL script. To enable Database Mail, go to the "Management" node in SSMS, right-click on "Database Mail" and select "Configure Database Mail". Configure SMTP Server : To send email messages, you need to configure an SMTP server. This can be done in the "Configure Database Mail" wizard or by running a T-SQL script. You'll need to enter the server name, port number, and login credentials for the SMTP server. Create a Profile : In SQL Server, a profile is a collection of settings that define how email messages sho...

In SQL Server, a synonym is an alternative name for an object in the database. Synonyms are often used to simplify database schema management, improve query readability, or create a level of abstraction between the application and the database. Here are some of the key features and benefits of synonyms in SQL Server : Simplify database schema management : Synonyms allow you to create a logical name for an object that can be used instead of its physical name. This can simplify database schema management and make it easier to maintain the database over time. Improve query readability : Synonyms can make queries easier to read and understand, especially when working with complex database schemas that have long and complex object names. Create a level of abstraction : Synonyms can create a level of abstraction between the application and the database, making it easier to change the database schema without impacting the application code. Cross-database access : Synonyms can be used to acces...

A linked server is a SQL Server feature that enables you to connect to other database servers or data sources from within a SQL Server instance. Once you have created a linked server, you can execute distributed queries and transactions against the remote data source as if it were a local table in your database.  Here are the steps to create a linked server: Open SQL Server Management Studio and connect to the SQL Server instance where you want to create the linked server. In Object Explorer, expand the "Server Objects" node, right-click on the "Linked Servers" node, and select "New Linked Server". In the "New Linked Server" dialog box, enter a name for the linked server and select the data source type, such as SQL Server or Oracle. Enter the connection properties for the remote data source, such as the server name, database name, and authentication method. Configure the security options for the linked server, such as the login mappings and perm...

In SQL Server, an execution plan is a graphical representation of the query execution process that shows how the SQL Server query optimizer will execute a particular query. The execution plan can help database administrators and developers to understand how a query is being executed and to identify any performance issues. There are several types of execution plans in SQL Server, including: Estimated Execution Plan : An estimated execution plan is generated by the query optimizer without actually executing the query. It provides an estimate of the cost of the query execution and can help to identify potential performance issues before running the query. When generating an estimated execution plan, the query optimizer analyzes the query and creates a plan that is based on statistics about the data in the database. The estimated execution plan shows the operators that will be used to execute the query, the order in which they will be executed, and the estimated number of rows that will be...

SQL Server Profiler is a powerful tool for monitoring and analyzing SQL Server activity. Here are some additional details about its features and some recommendations for using it effectively: Event Capture : SQL Server Profiler can capture a wide variety of events related to SQL Server activity, including T-SQL statements, stored procedure calls, and database backups. However, capturing too many events can impact the performance of the SQL Server instance, so it's important to be selective about which events are captured. Recommendation : Start by capturing a small set of events that are relevant to the issue at hand. If necessary, add more events to the trace session as needed. Trace Filters : SQL Server Profiler allows users to filter events based on a variety of criteria, such as application name, database name, and user name. This can help to reduce the amount of data captured in the trace and focus on the events that are most relevant to the analysis. Recommendation : Use tra...

Microsoft Database Engine Tuning Advisor (DTA) is a tool for optimizing the performance of Microsoft SQL Server databases. It works by analyzing a workload, which is a set of queries that the database receives over a given period of time. The DTA examines the workload and provides recommendations for improving database performance, such as creating new indexes or modifying existing indexes. The DTA has two main components: a graphical user interface (GUI) and a command-line interface (CLI). The GUI is integrated with SQL Server Management Studio and provides a visual representation of the DTA's analysis results. The CLI is a command-line tool that can be used to automate the analysis process. To use the DTA, a database administrator (DBA) needs to provide the workload to be analyzed, and specify the database or databases to be optimized. The workload can be provided in a variety of formats, such as SQL Server Profiler traces or Transact-SQL scripts. The DTA then analyzes the worklo...

Parameter sensitivity is a common challenge in SQL Server query optimization, where a single query can have multiple execution plans depending on the specific parameter values used. In SQL Server 2022, there are several improvements to the Parameter Sensitive Plan optimization feature that help to address this issue. Here are some of the key improvements and examples: Batch Mode on Rowstore : SQL Server 2022 introduces the ability to execute queries in batch mode on rowstore data, which can help to improve performance for parameter sensitive queries. Batch mode processing is a high-performance data processing mode that can take advantage of vectorized operations and SIMD instructions, which can result in significant performance improvements. For example , consider a query that retrieves all orders for a specific customer: SELECT * FROM Sales.Orders WHERE CustomerID = @CustomerID With batch mode on rowstore, SQL Server 2022 can use vectorized operations to process the query more efficie...

Cardinality estimation is a critical component of query optimization in SQL Server that estimates the number of rows that a query will return, and uses this information to select an optimal query execution plan. SQL Server 2022 introduces several improvements to its cardinality estimation engine, which is based on a machine learning model, to provide more accurate cardinality estimates. Here are some of the improvements in SQL Server 2022's cardinality estimation engine, along with examples: Selectivity Estimation for Complex Predicates : SQL Server 2022 can now more accurately estimate the selectivity of complex predicates, such as those involving multiple AND and OR operators, and nested subqueries. It uses a combination of heuristics and machine learning to estimate the selectivity of such predicates more accurately. For example , consider the following query: SELECT * FROM Sales.Orders WHERE (CustomerID = 123 OR CustomerID = 456) AND (OrderDate BETWEEN '2021-01-01' AND...

Automatic tuning is a new feature introduced in SQL Server 2017 and enhanced in SQL Server 2022 that uses machine learning algorithms to automatically optimize database performance. It provides insights into query performance, indexes, and statistics, and suggests corrective actions to improve database performance. Here are some of the ways Automatic Tuning can help optimize database performance in SQL Server 2022, along with examples: Automatic Plan Correction : Automatic Plan Correction uses machine learning algorithms to identify and correct performance problems caused by query plan regressions. It monitors query performance over time and uses machine learning to identify changes in the execution plan. If a plan regression is detected, Automatic Plan Correction can automatically roll back to a previous, known-good plan, or force the use of a better plan. For example, consider the following query: SELECT COUNT(*) FROM Sales.Orders WHERE OrderDate BETWEEN '2021-01-01' AND ...

Intelligent Query Processing (IQP) is a set of features introduced in SQL Server 2017 to optimize and improve the performance of queries in SQL databases. These features use machine learning algorithms to adapt and optimize query execution plans based on data statistics, query patterns, and other factors. SQL Server 2022 builds upon these features and introduces several enhancements to further improve query performance. Here are some of the enhancements to Intelligent Query Processing in SQL Server 2022 , along with examples: Scalar UDF Inlining : User-Defined Functions (UDFs) can be used to encapsulate complex logic and make queries more readable. However, they can also negatively impact query performance. With Scalar UDF Inlining, SQL Server 2022 can automatically inline scalar UDFs into queries, reducing the overhead of calling the UDF multiple times. For example , consider the following query with a scalar UDF: SELECT dbo.CalculatePrice(OrderDate, OrderQty, UnitPrice) FROM Sales.Or...

TempDB is a system database in SQL Server that is used to store temporary data, including temporary tables, table variables, and stored procedure output. TempDB is also used for sorting, grouping, and other operations that require temporary storage. When multiple transactions or sessions need to access TempDB at the same time, it can cause contention, leading to performance degradation. To address this issue, SQL Server 2022 introduces several improvements for TempDB: Multi-File Instant File Initialization (IFI) : This feature allows instant initialization of all TempDB files during startup or when adding new files. When SQL Server needs to create a new TempDB file, it needs to zero out the file to avoid exposing sensitive data from the previous use of the file. With Multi-File IFI, SQL Server can initialize all files at once, which reduces the time needed to allocate space, speeding up the process of creating a new TempDB file. For example , suppose SQL Server 2022 needs to create a n...

Azure Active Directory (AAD) authentication is a new security feature in SQL Server 2022 that allows administrators to authenticate users using their AAD credentials. This provides a centralized and secure way to manage access to SQL Server databases and helps to ensure that only authorized users have access to sensitive data. AAD authentication leverages the security and management capabilities of Azure Active Directory, providing a single sign-on (SSO) experience for users and simplifying the administration of database access. By using AAD, administrators can enforce multi-factor authentication, conditional access policies, and other security policies to ensure that only authorized users have access to sensitive data. In addition, AAD authentication supports integration with other Microsoft services, such as Microsoft Defender for Cloud and Microsoft Purview, to provide a comprehensive security solution for SQL Server databases. This helps to protect against data breaches, hacking at...

System page latch concurrency enhancements is a new feature in SQL Server 2022 that helps to improve scalability and performance. It addresses the scalability limitations of traditional SQL Server architectures by allowing multiple threads to access the same page at the same time, reducing the contention for system resources and improving performance. In traditional SQL Server architectures, access to a page is protected by a latch, which acts as a lock. Only one thread can access the page at a time, and any other threads trying to access the page must wait until the latch is released. This can result in contention for system resources, reducing performance and scalability. With system page latch concurrency enhancements, multiple threads can now access the same page at the same time, reducing the contention for system resources and improving performance. This feature helps to reduce the latency of query execution and improves the overall performance of SQL Server, particularly in larg...

Buffer pool parallel scan is a feature in SQL Server 2022 that improves the scalability and performance of database operations that require scanning a large amount of data. In this feature, the buffer pool is divided into smaller buffer pools, which are then assigned to individual threads for parallel scanning. In order to understand how buffer pool parallel scan improves scalability, it's important to first understand how SQL Server stores and retrieves data. SQL Server stores data in pages, which are 8 KB units of storage. When a query requests data, SQL Server reads the pages containing the requested data from disk and stores them in the buffer pool in memory. Subsequent requests for the same data can then be satisfied from the buffer pool instead of having to read from disk again. However, when a query needs to scan a large amount of data, it can be bottlenecked by the speed at which data can be read from disk and loaded into the buffer pool. This is where buffer pool parallel...

Tools in SQL Server 2022 refer to the various software programs and utilities that help to manage, maintain, and develop SQL Server databases. Some of the new tools in SQL Server 2022 include: Azure Data Studio - a cross-platform database management tool for SQL Server, designed for database administrators, developers, and data professionals. Distributed Replay - a tool that helps to simulate the production workload and measure the performance of SQL Server databases in a lab environment. SQL Server Management Studio (SSMS) - a graphical user interface tool that provides a comprehensive and integrated environment for managing and developing SQL Server databases. SqlPackage.exe - a command-line tool that helps automate database deployment, schema comparison, and data migration. Visual Studio Code - a popular code editor that includes an integrated SQL Server database management extension. These tools provide a range of functionalities to help administrators and developers manage the...

SQL Server 2022 includes several language improvements aimed at making it easier for developers to write and maintain their applications. Some of these language improvements include: Resumable add table constraints : SQL Server 2022 includes a resumable add table constraints feature, allowing for the creation of constraints to be resumed if the operation is interrupted due to an error or other unforeseen circumstances. This feature helps to improve the reliability and robustness of database schema changes. CREATE INDEX : SQL Server 2022 includes enhancements to the CREATE INDEX statement, making it easier to manage and optimize indexes in your database. This feature includes improvements to index creation and maintenance, providing better performance and reduced overhead. Transactional replication : SQL Server 2022 provides improved support for transactional replication, allowing for more efficient and robust replication of data between multiple databases. This feature provides better ...

SQL Server 2022 includes several platform improvements aimed at making it more compatible and performant on a wide range of hardware and software platforms. Some of these platform improvements include: SQL Server Native Client (SNAC) has been removed : The SQL Server Native Client (SNAC) has been removed in SQL Server 2022, and is no longer supported. This change is part of Microsoft's ongoing effort to modernize and simplify its technology stack. Hybrid buffer pool with direct write : The buffer pool in SQL Server 2022 includes a hybrid buffer pool with direct write, providing improved performance and scalability. This feature enables the buffer pool to store data in both DRAM and persistent storage, reducing I/O overhead and improving performance for write-intensive workloads. Integrated acceleration & offloading : SQL Server 2022 provides integrated acceleration and offloading capabilities, allowing for improved performance and resource utilization. This feature provides bet...

SQL Server 2022 includes several management features aimed at making it easier for database administrators and developers to manage their databases. Some of these management features include: Integrated setup experience for the Azure extension for SQL Server : This feature provides a streamlined setup experience for the Azure extension for SQL Server, making it easier to deploy and manage SQL Server in the cloud. Manage Azure extension for SQL Server : The management of the Azure extension for SQL Server has been improved in SQL Server 2022, providing a more seamless experience for administrators. Max server memory calculations : SQL Server 2022 includes improved max server memory calculations, making it easier to configure memory usage and avoid memory pressure. Accelerated Database Recovery (ADR) improvements : SQL Server 2022 includes improvements to ADR, making it faster to recover from database failures. Improved snapshot backup support : This feature provides improved support for...