European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

SQL Server Hosting - HostForLIFE :: Performance of SQL Server Databases is Improved by Automatic Tuning

clock May 20, 2024 10:50 by author Peter

What is Automatic Tuning?
Automatic tuning refers to a database management system's ability to dynamically optimize its performance without human intervention. It leverages advanced algorithms, machine learning techniques, and telemetry data to continuously monitor the database workload and automatically make adjustments to improve performance.

Why is Automatic Tuning Required?

  • Automatic tuning in SQL Server databases is crucial for several reasons:
  • Performance Optimization: SQL Server databases often deal with large volumes of data and complex queries. Automatic tuning helps optimize database performance by continuously monitoring query execution and adjusting indexes, statistics, and other database structures to ensure efficient query processing.
  • Adaptability: A database's workload can vary over time due to changes in data distribution, query patterns, or system resources. Automatic tuning enables the database to adapt dynamically to these changes without manual intervention, ensuring optimal performance under varying conditions.
  • Resource Management: In a production environment, database resources such as CPU, memory, and disk I/O are often shared among multiple applications and users. Automatic tuning helps manage these resources effectively by identifying and resolving performance bottlenecks, thereby improving overall system throughput and responsiveness.
  • Complexity Handling: Modern applications often rely on complex query patterns involving joins, aggregations, and other operations. Automatic tuning helps simplify the task of database optimization by automating the process of identifying and addressing performance issues, reducing the need for manual tuning by database administrators.
  • Time and Cost Savings: Manual database tuning can be time-consuming and labor-intensive, requiring significant expertise and effort. By automating tuning tasks, SQL Server reduces the administrative overhead associated with database maintenance, allowing administrators to focus on higher-level tasks such as application development and business intelligence.

How Does Automatic Tuning Work?
Automatic tuning relies on a combination of techniques to optimize database performance:

Automatic Tuning in SQL Server Database refers to a set of features introduced in SQL Server 2017 aimed at improving database performance by leveraging artificial intelligence and machine learning capabilities. Here's how it generally works:

  • Automatic Plan Correction: SQL Server automatically identifies and corrects performance problems caused by suboptimal query plans. It monitors query performance continuously and compares the actual execution metrics against the expected ones. If it detects a regression in performance, it can automatically force a better execution plan.
  • Automatic Index Management: SQL Server can automatically create, drop, or modify indexes based on the workload patterns. It analyzes the queries executed against the database and suggests index changes to improve performance. These suggestions are based on historical usage patterns and can be implemented automatically if the administrator chooses.
  • Automatic Database Tuning: This feature focuses on improving overall database performance by continuously monitoring and adjusting various database settings like indexing, statistics, and query execution parameters. It uses machine learning algorithms to analyze historical performance data and dynamically adjust configuration settings to optimize performance.
  • Query Store: The Query Store feature in SQL Server tracks query execution plans and runtime statistics over time. It provides insights into query performance trends and helps identify performance issues. Automatic Tuning leverages Query Store data to make intelligent decisions about query plan changes and index adjustments.
  • Adaptive Query Processing: SQL Server 2017 introduced adaptive query processing techniques that enable the database engine to adapt query execution plans based on runtime conditions. Automatic Tuning utilizes these capabilities to dynamically adjust query execution plans to improve performance.
  • Machine Learning Models: Behind the scenes, SQL Server uses machine learning models to analyze historical performance data and predict future workload patterns. These models help in making intelligent decisions about query optimization and index management.

Enabling Automatic Tuning in SQL Server Database
In SQL Server, automatic tuning capabilities are available through features like Automatic Plan Correction and Automatic Index Management. Here's how to enable them:

Automatic Plan Correction: This feature automatically identifies and corrects suboptimal query plans using machine learning algorithms. To enable it, you can use the following Transact-SQL command:
ALTER DATABASE <Database name> SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)

This command enables the database to automatically revert to the last known good query plan if a regression is detected.

Automatic Index Management: This feature automatically creates, drops, or alters indexes based on the workload patterns. To enable it, you can use the following Transact-SQL command:
ALTER DATABASE <Database name> SET AUTOMATIC_TUNING (AUTO_CREATE_STATISTICS = ON)
ALTER DATABASE <Database name> SET AUTOMATIC_TUNING (AUTO_CREATE_STATISTICS_ASYNC = ON)

These commands enable the database to automatically create and update statistics, which are crucial for query optimization.

Conclusion

Overall, Automatic Tuning in SQL Server combines various intelligent features and techniques to continuously monitor, analyze, and optimize database performance, ultimately leading to improved efficiency and reduced management overhead. By enabling these automatic tuning features, SQL Server can effectively optimize its performance without manual intervention, leading to improved efficiency, reliability, and scalability.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

 



SQL Server Hosting - HostForLIFE :: SQL Primary Key: Syntax, Uses, and Illustration

clock May 15, 2024 08:06 by author Peter

A primary key constraint is a key that has one or more columns to help identify each tuple or record in a table individually.

Qualities
Only distinct values should be assigned to the column designated as the primary key; duplicate values are forbidden. The primary key column contains no NULL values. As a result, there is a necessary value in the column holding the primary key. There is only one primary key per table, even if a primary key may contain more than one column.

Adding a new row using an existing primary key is not feasible.

categorized as,

  • A single-column, basic primary key.
  • Multiple columns make up the composite primary key.

As stated in the Make a Table or Modify a table statement.

A PRIMARY KEY constraint can be used to create the primary key in a table.

It can be created at a couple of levels.

  • Column
  • Table

SQL Primary Key at Column level
The primary key should be defined at the column level if it only has one column. The primary key "primary_field_name" is created in the table by the following code.

Syntax

Field_name data_type PRIMARY KEY

Example

CREATE TABLE Employee
(
  Id INT NOT NULL PRIMARY KEY,
  [Name] VARCHAR(50) NOT NULL,
  City VARCHAR(20) NOT NULL
)


Let’s verify the primary key with an example.
INSERT INTO Employee
VALUES (1, 'Peter', 'Scott');

Let’s try the execution of the same query again in SQL.

It will throw an exception.

Let’s try with the NULL inserted as the primary key value.
INSERT INTO Employee
VALUES (NULL , 'Peter', 'Scott');

It will throw an error due to null not being allowed in the primary key.

SQL Primary key at Table level

Whenever the primary key contains more than one column, it has to be specified at the table level.

Syntax
CREATE TABLE table_name
(
  field1 data_type,
  field2 data_type,
  ...
  PRIMARY KEY (field1)
)


Example
CREATE TABLE Employee
(
  Id INT NOT NULL,
  [Name] VARCHAR(50) NOT NULL,
  City VARCHAR(20) NOT NULL,
  PRIMARY KEY (Id)
)


Primary Key with Multiple Columns

If you want to add a primary key to more than one column, then that is not possible at the column level; that is possible at the table level.
CREATE TABLE Employee
(
  Id INT NOT NULL,
  [Name] VARCHAR(50) NOT NULL,
  City VARCHAR(20) NOT NULL,
  PRIMARY KEY (Id, [Name])
)

Primary Key with Alter Table
The primary key is typically defined at the time the table is created, but occasionally it might not be created in the table that already exists. However, we can use the Alter Statement to add the primary key.

For a Single Column

Syntax
ALTER TABLE Table_name
ADD PRIMARY KEY (column1);

Example
ALTER TABLE Employee
ADD PRIMARY KEY (Id);


For Multiple Columns
Syntax
ALTER TABLE Table_name
ADD CONSTRAINT primary_key_name PRIMARY KEY (column1, column2);


Example
ALTER TABLE Employee
ADD CONSTRAINT Employee_PK PRIMARY KEY (Id, [Name]);

Remove Primary Key
We can remove the primary key constraint from a table using the DROP statement.

Syntax
ALTER TABLE table_name
DROP CONSTRAINT primary_key_name;

Example
ALTER TABLE Employee
DROP CONSTRAINT Employee_PK;


We learned the new technique and evolved together.
Happy coding!

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.


 



SQL Server Hosting - HostForLIFE :: Run Multiple SQL Files in a Single SQL Server Execution

clock May 6, 2024 10:05 by author Peter

Do you make use of SQL Server? Do some teams or DBAs still manage deployments by hand? Are project or financial constraints preventing the incorporation of CI/CD tools? However, there's a chance to use more than one SQL script.

Overview
Running SQL scripts is a standard procedure in database management. But handling several SQL files might make the procedure tiresome and time-consuming. Batch scripts are one of the ways that SQL Server offers for executing SQL scripts. We will examine how to effectively use a batch script to run several SQL files in SQL Server in this article.

Understanding Batch Scripting
Batch scripting is a powerful tool for automating tasks in Windows environments. It allows users to execute a series of commands or scripts in a sequential manner. These scripts are saved with a .bat extension and can be run by simply double-clicking them or through the command line.

Pre-requisite for Batch File

    This script deploys all the .sql files present in a directory or sub-directories in an order.
    NO SPACE in .sql file name.
    NO SPACE in sub-directories.
    To execute .sql in order, add a numeric sequence as follows:
    createtable.sql
    insertdata.sql
    execstoredproc....etc.
    Specify the SERVERNAME, which is mandatory.
    DATABASENAME is optional (Commented-out). In case enabling add (-d%dbname%) after -E at line like (SQLCMD -S%SERVERNAME% -E -d%dbname% -b -i%%G >> :: :: %logfilepath%).
    Specify the SCRIPTLOCATION, which is mandatory.
    The execution log will be captured on the same SCRIPTLOCATION for successful execution and failure.

Steps to Follow

  • Copy the below batch script and save it as "ExecuteSQLFiles.bat".
  • Update SERVERNAME, SCRIPTLOCATION, and LOGLOCATION accordingly for your needs.


    :: Pre-requisites to follow

    :: This script deploys all the .sql files present in a directory or sub-directories in an order.
    :: NO SPACE in .sql file name.
    :: NO SPACE in sub-directories.
    :: To execute .sql in order add numeric sequence as follows: 1.createtable.sql,
    :: 2.insertdata.sql, 3.execstoredproc....etc.
    :: Specify the SERVERNAME which is mandatory
    :: DATABASENAME is optional (Commented-out). In case enabling add (-d%dbname%) after -E at line like
    :: (SQLCMD -S%SERVERNAME% -E -d%dbname% -b -i%%G >> :: :: %logfilepath%).
    :: Specify the SCRIPTLOCATION which is mandatory.
    :: The execution log will be captured on the same SCRIPTLOCATION for successful execution and failure.

    @echo off
    setlocal enabledelayedexpansion

    set SERVERNAME=LAPTOP-AOVLBDQ4
    ::set DATABASENAME=MyWork
    set SCRIPTLOCATION=C:\Peter\AutoDepSQLFiles\SQL\
    set LOGLOCATION=C:\Peter\AutoDepSQLFiles\Logs\
    set hr=%time:~0,2%

    if "%hr:~0,1%" equ " " set hr=0%hr:~1,1%
    set logfilepath= %LOGLOCATION%\ExecutionLog_%date:~-4,4%%date:~-10,2%%date:~-7,2%_%hr%%time:~3,2%%time:~6,2%.log
    set cmd='dir %SCRIPTLOCATION%\*.sql /b/s'

    FOR /f %%G IN (%cmd%) DO (
    echo ******PROCESSING %%G FILE******
    echo ******PROCESSING %%G FILE****** >> %logfilepath%
    SQLCMD -S%SERVERNAME% -E -b -i%%G >> %logfilepath%
    IF !ERRORLEVEL! NEQ 0 GOTO :OnError
    )
    GOTO :Success

    :OnError
    echo ERROR ERROR ERROR
    echo One\more script(s) failed to execute, terminating bath.
    echo Check output.log file for more details
    EXIT /b

    :Success
    echo ALL the scripts deployed successfully!!
    EXIT /b


Copy the below SQL scripts into separate files in respective folders.
--File Name 1.TableCreation.sql

USE MyWork
GO

CREATE TABLE dbo.Employee (Id INT IDENTITY(1,1), Emp_Name VARCHAR(100))

--File Name 2.InsertData.sql

USE MyWork
GO

INSERT dbo.Employee
SELECT 'Peter'
UNION
SELECT 'Scott'


--File Name 3.AlterTable.sql

USE MyWork
GO

ALTER TABLE dbo.Employee ADD Email VARCHAR(200)

--File Name 4.UpdateTable.sql

USE MyWork
GO

UPDATE dbo.Employee
SET Email = '[email protected]'
WHERE Emp_Name = 'Peter'

UPDATE dbo.Employee
SET Email = '[email protected]'
WHERE Emp_Name = 'Scott'


  • Execute the bat file, and upon successful completion, a log file will be generated in the Log directory.
  • All SQL scripts within the folder will have been executed.
  • If there are any failures, refer to the execution log for details

Folder Structure
Batch File

SQL Files

Verify The Deployment
As a part of this process, we initiated by creating an Employee table with Id and Emp_Name columns in the first file. Subsequently, we inserted two rows in the second file, followed by altering a table to include an Email column in the third script. Finally, in the fourth script, we updated the employee records to include email addresses.

In SQL Server, batch scripting offers a practical means of running numerous SQL files simultaneously. Batch scripts facilitate database management operations, increase productivity, and guarantee consistency in deployment procedures by automating the execution process. Users can efficiently handle their SQL execution demands by creating and running batch scripts by following the instructions provided in this article.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.


 



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in