Category Archives: SQL

Analiza performantei si stabilitatii unui server MySQL folosind utilitarul mysqltuner

MySQLTuner este un script scris in Perl care permite analiza rapida a unei instalari MySQL si propune ajustarea unor parametri pentru cresterea performantei si stabilitatii acesteia.

In ultima versiune (1.7.19) MySQLTuner suporta aprox. 300 de indicatori pentru MySQL/MariaDB/Percona Server.

Este foarte important sa intelegeti pe deplin fiecare modificare pe care o faceti in configurarea serverului MySQL. Testati intotdeauna fiecare modificare pe un server de test si tineti cont ca fiecare imbunatatire poate sa influenteze negativ server-ul in alta parte.

De asemenea, este important ca server-ul MySQL sa functioneze de cel putin o zi pentru ca rezultatul analizei sa fie relevant. Rularea utilizatului mysqltuner pe un server proaspat restartat este complet inutila.

Cea mai simpla metoda de descarcare a utilitarului:

wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv

Pentru a rula utilitarul folositi comanda:

perl mysqltuner.pl

Mai multe detalii despre utilizarea mysqltuner gasiti pe pagina oficiala:
https://github.com/major/MySQLTuner-perl

HeidiSQL – client gratuit pentru MySQL

HeidiSQL
HeidiSQL

HeidiSQL este un client gratuit si open-source pentru MySQL (MariaDB), MSSQL si PostgreSQL, cu o multime de functii utile programatorilor sau celor care administreaza baze de date MySQL. Personal folosesc acest program inca de pe vemea cand se numea MySQL-Front (2000-2001), iar astazi il gasesc indispensabil in activitatea mea zilnica.

Exista cel putin doua motive pentru care nu renunt la HeidiSQL: dezvoltatorul programului, Ansgar Becker, oferta suport tehnic prompt si mereu lanseaza versiuni noi ale programului. Iar incepand din luna februarie 2018 acest program poate fi descarcat si din Microsoft Store (pentru utilizatorii de Windows 10).

Din gama extinsa de facilitati amintesc:

  • manager de conexiuni la diverse baze de date
  • administrarea variabilelor si parametrilor de configurare a bazei de date
  • multiple sesiuni deschise intr-o singura fereastra
  • administrarea utilizatorilor si a drepturilor acestora
  • administrarea foarte comoda a bazelor de date si a tabelelor (creare, modificare, stergere)
  • interfata pentru crearea si editarea procedurilor si triggerelor
  • taburi separate pentru interogari, fiecare afisand rezultatele in subtaburi diferite
  • highlight si formatare (pretty formatting) sintaxa interogare SQL
  • highlight al celulelor cu aceeasi valoare ca si cea selectata
  • afisarea proceselor curente care ruleaza pe server, cu posibilitatea de administrare a lor
  • exportarea bazelor de date (sau doar a unor tabele) intr-un fisier sql sau direct in alta baza de date
  • exportarea rezultatelor unei interogari intr-un fisier extern in diferite formate (csv, xml, html, sql, LaTeX, PHP Array, JSON, etc…)
  • importarea datelor dintr-un fisier csv direct intr-o tabela
  • vizualizarea rapida si editarea continutului unei tabele
  • optimizarea in masa a tabelelor
  • posibilitatea de a filtra lista bazelor de date si a tabelelor utilizand sintaxa regex
  • posibilitatea de a salva unele interogari intr-o lista cu acces rapid (Snippets)
  • poate fi utilizat si ca soft portabil (nu necesita instalare)
  • exista variante pentru 32 si 64 bit

Evident ca pe masura ce veti utiliza HeidiSQL, pe care il consider ca fiind cel mai bun program in ramura sa, veti descoperi si multe alte facilitati.

Incepand cu versiunea 9.5.0.5318, pentru conectarea la un server Microsoft SQL este necesar sa fie instalat Microsoft OLE DB Driver for SQL Server.

Mai multe detalii: heidisql.com

Screenshot-uri: heidisql.com/screenshots.php

Diverse operatiuni: heidisql.com/help.php.

Mutarea fisierelor bazei de date tempdb in alta locatie

Pentru ca fisierele bazei de date tempdb  sunt recreate la fiecare pornire a serviciului SQL Server, acestea nu trebuie mutate in noua locatie. Fisirele vor fi create in noua locatie cand serviciul va fi repornit in pasul 3. Pana la restartarea serviciului, baza de date tempdb  va continua sa utilizeze fisierele din locatia curenta. Continue reading

Copiere parola utilizator SAP de la alt utilizator

Pentru copierea parolei utilizatorului GEORGE.S din clientul 300 peste acelasi utilizator din clientul 200 se poate utiliza urmatorul sql query:

UPDATE
    T200
SET
    T200.BCODE = T300.BCODE,
    T200.PASSCODE = T300.PASSCODE
FROM
    lid.USR02 AS T200
    INNER JOIN lid.USR02 AS T300 ON (T200.BNAME = T300.BNAME AND T300.MANDT = '300')
WHERE
    T200.MANDT = '200' AND T200.BNAME = 'GEORGE.S';

 

Mutare fisier transaction log pe alt disk

Din considerente care tin de performanta este recomandat ca fisierul Transaction log al unei baze de date MSSQL sa se afle pe alta partitie decat baza de date. Pentru mutarea acestui fisier pe alta partitie se va utiliza instrumentul SQL Server Management Studio (SSMS). In pricipiu aceiasi pasi pot fi urmati pentru mutarea oricarui alt fisier din componenta unei baze de date MSSQL.

  1. Autentificare in SSMS
  2. Click dreapta pe baza de date asupra careia trebuie intervenit > Tasks > Detach
  3. Mutarea fisierului Transaction log la locatia dorita
  4. Click dreapta pe Databases > Attach
  5. Se va cauta locatia fisierului principal al bazei de date
  6. In dreptul fisierului Transaction log (sau al oricarui fisier mutat in alta locatie) va apare eroarea ca fisierul nu a fost gasit. Pentru fiecare eroare se va naviga la locatia corecta si se va alege fisierul.
  7. Ok

Daca, dupa detach, apare eroare ca nu se mai poate conecta la serverul SQL (una din cauze poate fi ca la detach s-a bifat Drop connections, ceea ce a intrerupt inclusiv conexiunea din SSMS), atunci in fereastra de conectare > Options > Connection Properties > in campul Connect to database se scrie master (nu se cauta in lista), apoi Connect, dupa care se pot urma pasii de mai sus pentru Attach.

MSSQL Index Fragmentation

Identificarea indecsilor cu fragmentare mai mare de 80% (TOP 10 – primele 10 inregistrari):

SELECT TOP 10 OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName, indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
WHERE indexstats.avg_fragmentation_in_percent > 80 AND i.index_id = indexstats.index_id;

Continue reading

Selectarea celor mai recente/vechi inregistrari in SQL JOIN

Se considera tabela clienti, cu urmatoarea structura:

+-----------+-------------+
| client_id | client_name |
+-----------+-------------+
|         1 | Lorand      |
|         2 | Mihai       |
+-----------+-------------+

Si tabela adrese_clienti, cu urmatoarea structura:

+------------+-----------+----------------+---------------------+---------------------+
| address_id | client_id | client_address | date_from           | date_to             |
+------------+-----------+----------------+---------------------+---------------------+
|          1 |         1 | Adresa 1       | 2015-01-01 10:23:21 | 2015-01-17 11:24:31 |
|          2 |         1 | Adresa 2       | 2015-01-17 11:24:31 | 2015-02-21 13:28:57 |
|          3 |         1 | Adresa 3       | 2015-02-21 13:28:57 | 9999-01-01 00:00:00 |
|          4 |         2 | Adresa 4       | 2015-01-01 13:12:53 | 2015-01-31 09:19:22 |
|          5 |         2 | Adresa 5       | 2015-01-31 09:19:22 | 2015-02-11 08:23:19 |
|          6 |         2 | Adresa 6       | 2015-02-11 08:23:19 | 9999-01-01 00:00:00 |
+------------+-----------+----------------+---------------------+---------------------+

Pentru fiecare client se pastreaza istoricul modificarilor adreselor, in aceeasi tabela, fiecare inregistrare pastrand perioada de valabilitate in campurile date_from si date_to. Inregistrarea actuala pentru fiecare client este marcata de valoarea 9999-01-01 00:00:00 in campul date_to.

Doua probleme

1. Se doreste selectarea celei mai vechi adrese pentru fiecare client:

SELECT c.client_id, c.client_name, c2.client_address
FROM clienti AS c
INNER JOIN (
    SELECT client_id, MIN(address_id) AS address_id
    FROM adrese_clienti GROUP BY client_id
) AS c1 ON (c1.client_id = c.client_id)
INNER JOIN adrese_clienti AS c2 ON (c2.address_id = c1.address_id);

Rezultat:

+-----------+-------------+----------------+
| client_id | client_name | client_address |
+-----------+-------------+----------------+
|         1 | Lorand      | Adresa 1       |
|         2 | Mihai       | Adresa 4       |
+-----------+-------------+----------------+

2. Se doreste selectarea adresei actuale pentru fiecare client:

SELECT c.client_id, c.client_name, c2.client_address
FROM clienti AS c
INNER JOIN (
    SELECT client_id, MAX(address_id) AS address_id
    FROM adrese_clienti GROUP BY client_id
) AS c1 ON (c1.client_id = c.client_id)
INNER JOIN adrese_clienti AS c2 ON (c2.address_id = c1.address_id);

sau mai simplu (datorita campului date_to):

SELECT c.client_id, c.client_name, c1.client_address
FROM clienti AS c
INNER JOIN adrese_clienti AS ac
    ON (ac.client_id = c.client_id AND ac.date_to = '9999-01-01 00:00:00');

Rezultat:

+-----------+-------------+----------------+
| client_id | client_name | client_address |
+-----------+-------------+----------------+
|         1 | Lorand      | Adresa 3       |
|         2 | Mihai       | Adresa 6       |
+-----------+-------------+----------------+