SQL with the highest performance. Learn tuning with SQL SERVER
Master over 25 tuning tips and tricks with SQL Server 2017 to create highly efficient queries and code.
What you’ll learn
Create more efficient T-SQL code with reduced response time and low resource consumption.
Make adjustments to T-SQL code that present performance issues.
Monitor the environment, identify faulty commands and infrastructure resources.
Create the best indexes for better performance without making code adjustments.
Analyze the execution of the commands, identifying the points where they present the performance bottlenecks.
Create an optimized database and table structure to receive application data.
Improve the efficiency of applications and systems, giving the user the best browsing experience as well as streamlining their processes.
Tired of waiting for your query to take more than 1 minute to execute.
What if you learn that it can be performed in 1 second?
In this course you will learn the concepts and foundations for building your high-performance, highly efficient T-SQL code queries using SQL Server with over 25 simple and effective tips, tricks, and techniques.
The art of querying sql tuning is one of the top points for the Database Developer. Several business systems (SAP or Siebel, for example), eCommerce stores, web applications such as personal finance managers, among others always require a high performance database for their data repositories.
And the challenge for application developers is to ensure that information is retrieved in the shortest possible time. People need and want quick answers, and this can be a determining factor for your app or your company’s website to succeed.
Several factors in SQL code can contribute to slow or poor performance queries or reports. Among them we have:
Poorly written instructions or not respecting good practices;
Poorly defined columns and occupying unnecessary spaces;
The database allocated on a single disk competing with the operating system;
Tables without indexes, poorly scaled or obsolete index;
Unnecessary data conversions;
Other points may influence data access performance:
Badly sized hardware;
Operating system installation and configuration does not conform to good database practices;
Wrong sizing of disk arrays;
Database manager installation and configuration.
In addition to the list, you should also consider:
The gradual increase in the mass of data that is constantly increasing in the database;
Increased connections and users in applications and systems;
The number of databases that are included in the instance and increases the sharing of hardware resources.
But SQL tuning is not an exact science where a performance tuning will always have a positive effect. There are several procedures that must be followed in order to get the best result.
You must monitor the environment to identify potential problems. Tools such as Windows Performance Monitor and Resource Monitor help with monitoring. In SQL Server, we can use Profiler, Extended Events, and DMVs to monitor server commands and resources.
Based on the data you capture, you should report on system status, harvested metrics, low-performing, high-resource queries, underutilized or obsolete indexes, tables that are subjected to many sequential searches, and few spot searches, disks with high utilization rate are some examples.
Based on the data presented, you should start applying the techniques learned in this training and testing. The important and correct is to apply the procedures in a QA or Homologation environment and test to validate if what was applied yielded the expected result. Test long before putting into production environment.
Implement what has been validated in production environment testing. The tip here is not to implement all techniques at once. If something goes wrong, you will not know which of the implementations did not work. This process will take a long time and in some cases you will need to stop the database service.
And this is a constant cycle, which should be applied periodically as a good practice.
Our goal in this training is to introduce techniques and best practices in developing various SQL commands.
Some tips you will learn:
Configure SQL Server memory
Create the database on multiple disks;
Understand type and size of correct data;
Create tables on specific disks;
Create table to occupy the smallest space;
Learn how to create calculated columns
Understand how a data page works;
Read weather and read statistics
View the running commands;
Monitor your server and database;
Analyze the execution plans of commands;
How to define an indexed primary key;
How to define an indexed foreign key;
Creating composite index to cover all searches;
Identify tables who have more SCAN and few SEEK;
Uses the concept of coverage ratio;
How to identify obsolete and unused indexes;
How to avoid page splits;
Create high performance SARG surveys;
Transform research into SARG;
Eliminate explicit data conversions;
Best practices for using UDF;
The correct use for NOCOUNT in Store Procedures;
When to validate data for updates;
Set the correct name for store procedures;
When using SET or SELECT to assign value to variable.
Use UNION ALL instead of UNION;
These are some of the tips that will be in our training and others that will be presented together with the more than 50 classes we will have.
I hope you enjoy our training and make the most of your learning.