sqlschool.gr logo

articles

Articles of SQLschool.gr Team

Sizing SQL Server instance

Antonios Chatzipavlis
Saturday 15 August 2020

Overview

"Έχω ένα σύστημα με Χ Cores, Y RAM, Z storage είναι καλό για να βάλω SQL Server;"

Αυτή είναι η συνηθέστερη ερώτηση που πρέπει συχνά να απαντήσω και αφορά το sizing ενός SQL Server instance.

Αν κάποιος κάνει μια αναζήτηση στον ιστο θα βρει άπειρες καταχωρήσεις σχετικά με το θέμα αυτό.

Καταχωρήσεις πολλές αλλά απαντήσεις ξεκάθαρες δεν θα βρει και αυτό γιατί δεν υπάρχει deterministic μέθοδος που να υπολογίζει αυτό.

Ο βασικός λόγος είναι ότι υπάρχουν πάρα πολλές μεταβλητές που εμπλέκονται, που καθιστά αδύνατο να βγει ένα ασφαλές αποτέλεσμα.

Ενδεικτικά θα αναφέρω μερικές μεταβλητές που επηρεάζουν το τελικό αποτέλεσμα και είναι

  • Πόσοι είναι οι χρήστες που θα χρησιμοποιούν τον SQL Server;
  • Πόσες databases θα υπάρχουν στον SQL Server;
  • Τι μέγεθος ή πόσο μεγάλες θα είναι αυτές (μεμονωμένα και συνολικά);
  • Πόσο καλό είναι το database schema;
  • Πόσο καλοί είναι indexes;
  • Πόσο αυτές θα μεγαλώνουν και με ποιο ρυθμό;
  • Πόσα transaction/sec θα υπάρχουν;
  • Τι είδους queries (operational or analytics) θα εκτελούνται.
  • Τι είδους storage θα χρησιμοιηθεί και πως αυτό θα είναι συνδεδεμένο με το SQL Server;
  • Τι Disk IO θα έχω;
  • Τι Νetwork needs υπάρχουν;
  • Τα φορτία που θα έχω σε αυτόν τον SQL Server θα είναι CPU bound;
  • Τι services θα υπάρχουν σε αυτό (Database Engine, DW, OLAP, … )

Η λίστα αυτή είναι μακριά…

Μπορώ να σας δείξω μερικά από τα συστήματα που κατά καιρούς έχω χρησιμοποιήσει, είναι καλά;
System Image A
image

System Image B
image

Η απάντηση είναι it depends όσο παράδοξο και αν αυτό είναι με αυτά που δείχνω καθώς θέλουμε να κάνουμε οικονομία κλίμακος δηλαδή να πληρώσουμε για αυτό που πραγματικά χρειαζόμαστε. Το ιδανικό είναι να μπορείς να δοκιμάσεις πριν αγοράσεις αλλά αυτό δεν το μπορούν να το κάνουν όλοι, άρα κάπως πρέπει να ξεκινήσεις.

ATTENTION
Πριν συνεχίσω παρακάτω στο άρθρο αυτό θα πρέπει να κάνω σαφές ότι όλα τα παρακάτω σε καμία περίπτωση δεν δίνουν το τελικό αποτέλεσμα καθώς οι μεταβλητές όλες δεν περιλαμβάνονται. Είναι μια αρχή συζητήσεων και αναζήτησης, απλά έχεις κάπου να στηριχτείς.

Estimate CPU Requirements

Οι περισσότεροι ξεκινάμε με το να υπολογίζουμε τα CPU cores. Αυτό γίνεται για δύο ευνόητους λόγους ο πρώτος είναι το performance και ο δεύτερος είναι το licensing. Φυσικά το performance είναι ο κυρίαρχος λόγος για να έχουμε ένα σύστημα που να ανταποκρίνεται στις ανάγκες μας.

Maximum Consumption Rate (MCR)

Για αυτό το λόγο και υπολογίζουμε το MCR για να βρούμε το ποσό των δεδομένων που μπορεί να επεξεργαστεί ένα core σε ένα δευτερόλεπτο.

Φυσικά αυτό είναι για το συγκεκριμένο τύπο CPU και σύστημα (motherboard) στο οποίο θα εκτελέσουμε την διαδικασία που περιγράφεται παρακάτω. Παρόλα αυτά όμως οι διαφορές είναι ελάχιστες αν μιλάμε για παρεμφερείς συστήματα.

Αν δεν μπορούμε να κάνουμε τον υπολογισμό του MCR στο σύστημα που έχουμε επιλέξει ότι θα προμηθευτούμε θα πρέπει να κάνουμε μια υπόθεση σχετικά με την τιμή του. Σύμφωνα με τις μετρήσεις που έχουν γίνει αλλά και τους σημερινούς επεξεργαστές η τιμή αυτή είναι 300 ΜΒps.

Calculate MCR

Για τον υπολογισμό του χρειαζόμαστε μια database όπως για παράδειγμα στο script που ακολουθεί

SQL Script

USE master;

-- Create a database for benchmark queries
CREATE DATABASE BenchMarkDB;
GO
USE BenchMarkDB;

-- Include a heap and a table with a clustered index
CREATE TABLE heap_table
(col1 integer identity,
 col2 integer,
 col3 varchar(50));

 CREATE TABLE clust_table
(col1 integer identity PRIMARY KEY CLUSTERED,
 col2 integer,
 col3 varchar(50));

-- Insert 100 rows to start with
DECLARE @i integer = 0;
WHILE @i < 101
BEGIN
   SET @i = @i + 1
   INSERT INTO heap_table VALUES (@i, CAST(@i%5 AS varchar))
   INSERT INTO clust_table VALUES (@i, CAST(@i%5 AS varchar))
END;

-- Now keep reinserting exponentially until the tables each contain 2 million rows
WHILE (SELECT COUNT(*) FROM clust_table) < 2000000
BEGIN
 INSERT INTO heap_table
 SELECT col2, col3 FROM clust_table;
 INSERT INTO clust_table
 SELECT col2, col3 FROM clust_table;
END;

Έπειτα χρειαζόμαστε το παρακάτω script στο οποίο θα δείτε ότι αρχικά "ζεσταίνουμε" την buffer cache ώστε να διασφαλίσουμε ότι δεν θα έχουμε disk IO.

Στην συνέχεια εκτελούμε τα queries με MAXDOP=1 ώστε να εξασφαλίσουμε ότι θα χρησιμοποιηθεί ένα core και αφού φυσικά έχουμε ενεργοποιημένα τα statistics io & time.

SQL Script

USE BenchmarkDB
GO

SELECT SUM(Col2) FROM heap_table WHERE col1 % 3 = 1
GROUP BY col3;

SELECT SUM(Col2) FROM clust_table WHERE col1 % 3 = 1
GROUP BY col3;


SET STATISTICS IO ON;
SET STATISTICS TIME ON;


-- run these muliple times and take an average of the logical reads and CPU time
SELECT SUM(Col2) FROM heap_table WHERE col1 % 3 = 1
GROUP BY col3
OPTION (MAXDOP 1);


SELECT SUM(Col2) FROM clust_table WHERE col1 % 3 = 1
GROUP BY col3
OPTION (MAXDOP 1);

Το αποτέλεσμα δεν μας ενδιαφέρει αλλά μας ενδιαφέρει το αποτέλεσμα στo Messages tab στο οποίο θα έχουμε κάτι αντίστοιχο με το παρακάτω image και στο οποίο φαίνονται τα σημεία που μας ενδιαφέρουν.


MCR Results
image

MCR Formula

Το MCR μπορούμε να το βρούμε με την εξής φόρμουλα στρογγυλοποιώντας το αποτέλεσμα σε επόμενη δεκάδα.

MCR = ( ( Logical Reads Average ÷ CPU time average in sec ) X 8 ) ÷ 1024

Αυτό σημαίνει ότι έχουμε

MCR = ( ( ( (A1+A2) ÷ 2 ) ÷ ( ( (B1+B2) ÷ 2 ) ÷ 1000 ) ) X 8 ) ÷ 1024 = 205.23 -> 210

Estimate the number of cores

Αφού έχουμε το MCR για να υπολογίσουμε τον αριθμό των cores αρκεί να χρησιμοποιήσουμε την εξής φόρμουλα

((Average query size in MB ÷ MCR) x Concurrent users) ÷ Target response time

Αυτό σημαίνει ότι θα πρέπει να ξέρουμε το Average query size in MB, το αριθμό των concurrent users που θέλουμε να υποστηρίξουμε κάθε στιγμή και φυσικά το target response time για τα περισσότερα queries που θα εκτελούνται.

Αν έχουμε ήδη ένα σύστημα που λειτουργεί και θέλουμε να κάνουμε αναβάθμιση αυτά τα ξέρουμε ή μπορούμε να τα βρούμε με τα εργαλεία που έχουμε στο SQL Server όπως DMVs, Query Store κλπ.

Αν όχι θα πρέπει να κάνουμε υποθέσεις εργασίας που να ανταποκρίνονται στην πραγματικότητα, όπως για παράδειγμα κανείς δεν θέλει σαν target response time μια τιμή μεγαλύτερη από 60 sec. Η δυσκολία είναι στο Average query size in MB αν δεν υπάρχει προγενέστερη εικόνα. Σε αυτή την περίπτωση είναι δύσκολη η υπόθεση και εξαρτάται από το αν μιλάμε για OLTP, DW ή OLAP καθώς θα πρέπει να γνωρίζουμε πολλές μεταβλητές.

Ο αριθμός των concurrent users είναι κάτι που μπορούμε να το υποθέσουμε σχετικά εύκολα αλλά και να το προβλέψουμε από την φύση της database και το ποια θα είναι η χρήση της στο οργανισμό.

Σε κάθε περίπτωση το τελικό αποτέλεσμα θα πρέπει να είναι ζυγός αριθμός. Αν για παράδειγμα είναι 15 θα πρέπει να το κάνουμε 16 και φυσικά θα πρέπει να το λάβουμε υπόψη στο τι CPUs θα πάρουμε και πόσες.


Αν για παράδειγμα έχουμε

MCR = 300 MBps

Average query size in MB = 20000 ΜΒ

Concurrent users = 20

Target response time = 60 sec

CPU cores = 22

Estimate RAM Requirements

To να υπολογίσουμε την RAM είναι ακόμα ένα δύσκολο σημείο.

Υπάρχει μια φόρμουλα που λέει ότι για κάθε core υπολογίζουμε το ελάχιστο 4 GB RAM (προσωπικά επιλέγω 8 GB / core).

Μια άλλη φόρμουλα λέει ότι πρέπει να έχουμε 64-128 GB / socket.

Τέλος άλλη μία προσέγγιση είναι να τα βάζουμε το 30% του μεγέθους των databases που θα έχουμε στο συγκεκριμένο instance αλλά και εδώ πρέπει να λάβουμε υπόψη το ρυθμό που αυτές μεγαλώνουν.

Σε κάθε περίπτωση πρέπει να λάβουμε υπόψη αν είναι OLTP, DW, OLAP, αν χρησιμοποιούνται in-memory objects αν έχω columnstore indexes κλπ.

Επίσης προσθέτουμε ακόμα 8 GB για το OS.

Με βάση το παραπάνω αποτέλεσμα των cores μιλάμε για 96GB RAM.

Storage Requirements

Δεν έχω να πω πολλά σε αυτό το σημείο καθώς θεωρώ ότι είναι εύκολο να το υπολογίσει κανείς με βάση τα δεδομένα που έχει για τα μεγέθη των databases.

Αυτό που θα επισημάνω είναι ότι χρειαζόμαστε ξεχωριστά storage volumes για τα data, logs, tempdb, backups, filestreams, files και πιθανότατα ιδιαίτερα όταν έχουμε πολλές βάσεις στο instance που είναι απαιτητικές σε Disk IO αυτές να είναι σε ξεχωριστά volumes.

Conclusion

Θα τονίζω για ακόμα μια φορά ότι όλα όσα έχω αναφέρει στο άρθρο αυτό δεν αποτελούν θέσφατα αλλά μια ένδειξη που χωράει αρκετές αλλαγές και συζητήσεις.



Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios Chatzipavlis is a highly experienced Data Solutions Consultant and Trainer. He has been working in the IT industry since 1988, holding various roles such as senior developer, IT Manager, Data & AI Solutions Architect and Consultant.

Since 1995, Antonios has focused on modern technologies and software development tools, primarily by Microsoft. He has specialized in Data & AI since 2000, with expertise in Microsoft Data Platform (SQL Server, Azure SQL Databases, Azure Synapse Analytics, Microsoft Fabric, Power BI, AI) and Databricks.

Antonios is also a Microsoft Certified Trainer (MCT) for over 25 years, has been recognized as a Microsoft Most Valuable Professional (MVP) in Data Platform since 2010 and he is in the Data Expert 40 Powerlist 2024 by Boussias. He is the co-founder and visionary behind XLYTiCA, a company dedicated to Data & AI solutions.

Episode

Task Flows in Microsoft Fabric

image

More Episodes...

Tip

What's New in SQL Server 2022 - Episodes

More Tips...

Become a member

If you want to receive updates from us become a member to our community.

Connect

Explore

Learn


sqlschool.gr © 2010-2025 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.