Skip links

WHAT IS SQL?

SQL is used in almost every application or on any website that interacts with databases. This is a programming language whose only function is to send queries to relational databases.

A request refers to a request to the database, which is necessary to manage information in it (deletion, addition or change), carried out using a database management system.

COMMON AND SPECIAL COPIES

If an application uses a large number of schemas/stored procedures, this can potentially degrade the performance of applications using the same instance. Available resources may be shared or locked. This can slow down other database applications on the shared instance of the SQL Server. Fixing performance issues can be a hassle because you need to figure out which instance is causing the problem. And it is often not so simple.

How companies answer this question is usually weighed against the cost of operating systems and SQL licenses. If application performance is a top priority, a dedicated instance is highly recommended.

Microsoft does not sell SQL Server licenses on a per-instance basis, but on a per-core basis. For cost reasons, administrators tend to install as many instances of SQL Server as the server can simply handle, which can lead to significant performance issues in the long run.

Therefore, you should choose dedicated SQL instances whenever possible.

SPLITTING SQL FILES ON DIFFERENT DATA MEDIA

SQL Server uses very different I/O patterns for reading data and log files. Data files are typically accessed randomly, and transaction log files are sequential. On spinning hard drives, the read head must be positioned for random access to I/O. Thus, sequential data access is more efficient than random access. If you partition files with different access patterns, you also minimize the number of read-head movements and thereby optimize storage performance.

Use RAID-10 systems for binaries, data, log files, and tempdb databases for best performance and availability.

TEMPDB DATABASE SIZE

Set the maximum size of the tempdb database file to avoid disk fragmentation. Conflicts can occur in GAM, SGAM, and PFS pages when SQL must write to specific system pages to allocate new objects. Latches protect (lock) these memory pages. On a busy SQL server, it may take a long time for the system file to be committed to the tempdb database, resulting in increased query times. This phenomenon is called “dispute”.

Rule of thumb for creating tempdb data files:

    • On 8 cores
    • 8 tempdb data files

Starting with SQL Server 2016, the number of CPU cores visible to the operating system is determined automatically during installation. Based on this, SQL Server calculates and adjusts the number of tempdb files needed for optimal performance.

Leave a comment

Explore
Drag