Discussion :

[al1_24]

Bonjour,

Je suis arrivé il y a quelques temps sur une application décisionnelle avec un entrepôt de données sous SQL-Server.
Les utilisateurs se plaignent que le système est complètement bloqué de leur côté lorsque l'un d'eux exécute une requête qui retourne plusieurs millions de lignes.

Je ne connais pas SQL-Server et j'ai été rechercher des informations sur la configuration du serveur.

La vue dm_os_schedulers me donne les résultats suivants :

 parent_node_id  scheduler_id  cpu_id  status                is_online is_idle
  0               0             0       VISIBLE ONLINE        1         1
  0               1             1       VISIBLE ONLINE        1         0
  64              1048576       0       VISIBLE ONLINE (DAC)  1         1
  0               1048578       0       HIDDEN ONLINE         1         0
  0               1048579       1       HIDDEN ONLINE         1         1
  0               1048580       0       HIDDEN ONLINE         1         1
  0               1048581       1       HIDDEN ONLINE         1         1
  0               1048582       0       HIDDEN ONLINE         1         1
  0               1048583       1       HIDDEN ONLINE         1         1
  0               1048584       0       HIDDEN ONLINE         1         1

Et voilà ce que je trouve dans dm_os_sys_info :

 cpu_count                       2          
  hyperthread_ratio               1          
  physical_memory_kb              8388088    
  stack_size_in_bytes             2093056    
  os_quantum                      4          
  os_error_mode                   5          
  os_priority_class               32         
  max_workers_count               512        
  scheduler_count                 2          
  scheduler_total_count           10         
  virtual_machine_type            1          
  virtual_machine_type_desc       HYPERVISOR

Je suis étonné que seuls deux coeurs aient été mis à disposition du SGBD sur les 10 disponibles.
Y a-t-il une raison à cela ? Je ne trouve pas de documentation à ce sujet (ou je ne sais pas où la chercher).

[bvesan]

Bonjour,
Dans l'absolu, il est possible de cloisonner l'instance SQL Server à un petit nombre de CPUs d'une machine (via le mécanisme d'affinité processeurs). Mais dans ce cas, les schedulers existent pour tous les CPUs de la machine, et ceux qui sont positionnés sur les CPUs non utilisés ont le statut 'VISIBLE OFFLINE'.
De plus, comme sys.dm_os_sys_info renvoie le nombre total de CPUs de la machine et pas seulement ceux utilisés par l'instance, il semblerait que votre machine (ou au moins votre OS) ne mette à disposition que 2 processeurs logiques.

Ou peut-être s'agit-il d'une limite de l'édition de l'instance SQL Server déployée (SQL Server Express) ?

[SQLpro]

lancez

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

EXEC sp_configure

et donnez nous les résultats.

Quel est l'architecture machine ? Combien de SOCKETS, de core par CPU ? Combien de noeuds NUMA ?

A +

[al1_24]

Voici le résultat de sp_configure :

name                               minimum         maximum         config_value    run_value
access check cache bucket count    0               65536           0               0
access check cache quota           0               2147483647      0               0
Ad Hoc Distributed Queries         0               1               0               0
affinity I/O mask                  -2147483648     2147483647      0               0
affinity mask                      -2147483648     2147483647      0               0
affinity64 I/O mask                -2147483648     2147483647      0               0
affinity64 mask                    -2147483648     2147483647      0               0
Agent XPs                          0               1               1               1
allow updates                      0               1               0               0
backup compression default         0               1               0               0
blocked process threshold (s)      0               86400           0               0
c2 audit mode                      0               1               0               0
clr enabled                        0               1               0               0
common criteria compliance enabled 0               1               0               0
contained database authentication  0               1               0               0
cost threshold for parallelism     0               32767           5               5
cross db ownership chaining        0               1               0               0
cursor threshold                   -1              2147483647      -1              -1
Database Mail XPs                  0               1               1               1
default full-text language         0               2147483647      1033            1033
default language                   0               9999            0               0
default trace enabled              0               1               0               0
disallow results from triggers     0               1               0               0
EKM provider enabled               0               1               0               0
filestream access level            0               2               0               0
fill factor (%)                    0               100             0               0
ft crawl bandwidth (max)           0               32767           100             100
ft crawl bandwidth (min)           0               32767           0               0
ft notify bandwidth (max)          0               32767           100             100
ft notify bandwidth (min)          0               32767           0               0
index create memory (KB)           704             2147483647      0               0
in-doubt xact resolution           0               2               0               0
lightweight pooling                0               1               0               0
locks                              5000            2147483647      0               0
max degree of parallelism          0               32767           0               0
max full-text crawl range          0               256             4               4
max server memory (MB)             128             2147483647      5120            5120
max text repl size (B)             -1              2147483647      65536           65536
max worker threads                 128             65535           0               0
media retention                    0               365             0               0
min memory per query (KB)          512             2147483647      1024            1024
min server memory (MB)             0               2147483647      8               16
nested triggers                    0               1               1               1
network packet size (B)            512             32767           4096            4096
Ole Automation Procedures          0               1               0               0
open objects                       0               2147483647      0               0
optimize for ad hoc workloads      0               1               0               0
PH timeout (s)                     1               3600            60              60
precompute rank                    0               1               0               0
priority boost                     0               1               0               0
query governor cost limit          0               2147483647      0               0
query wait (s)                     -1              2147483647      -1              -1
recovery interval (min)            0               32767           0               0
remote access                      0               1               1               1
remote admin connections           0               1               0               0
remote login timeout (s)           0               2147483647      10              10
remote proc trans                  0               1               0               0
remote query timeout (s)           0               2147483647      600             600
Replication XPs                    0               1               0               0
scan for startup procs             0               1               0               0
server trigger recursion           0               1               1               1
set working set size               0               1               0               0
show advanced options              0               1               1               1
SMO and DMO XPs                    0               1               1               1
transform noise words              0               1               0               0
two digit year cutoff              1753            9999            2049            2049
user connections                   0               32767           0               0
user options                       0               32767           0               0
xp_cmdshell                        0               1               1               1

De ce que j'ai pu glaner comme informations, il y aurait 2 processeurs physiques avec 10 coeurs chacun (xeon E7-4850 @ 2.0 GHz) mais je n'ai pas pu savoir si les machines étaient dédiées ou partagées en environnement virtualisé

[SQLpro]

A priori rien n'indique une limitation du parallélisme au niveau serveur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
affinity I/O mask               0               0
affinity mask                   0               0
affinity64 I/O mask             0               0
affinity64 mask                 0               0
cost threshold for parallelism  5               5
max degree of parallelism       0               0

Il faut voir maintenant si cela n'est pas le fait du Resources Governor....

Avez-vous une limitation du RG ? (voir dans SSMS arbo : gestion / Gouverneur de ressources)

Sinon, cela peut être le fait dans la requête. Voyez avec le profiler par exemple s'il n'y a pas un OPTION (MAXDOP 1)

Cependant vous avez :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

max server memory (MB)          5120            5120

donc liimitation de la RAM de SQL Server à 5 Go... Quelle quantité de RAM possède votre serveur ?

Autre chose : pourquoi ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

server trigger recursion           0               1               1               1

Vous utilisez réellement des déclencheurs récursifs ???

Enfin pour savoir si vous êtes en physique ou virtuel, lancez :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
EXEC xp_msver;
 
SELECT * FROM sys.dm_os_sys_info;

A +

[al1_24]

Merci pour votre aide.
En fait, le problème de blocage ne se trouvait pas du côté de la base de données mais de la connexion réseau (<50Mb/s) entre le serveur de données et l'outil de restitution (BO).

Compte-tenu des informations partielles, déformées ou tronquées communiquées par les DBA et responsables système , il était bien difficile de trouver où se trouvait le goulot d'étranglement.
... et de faire comprendre à un DBA qui n'a fait que du transactionnel depuis plus de 20 ans que, en décisionnel, les outils mis à disposition des utilisateurs ne permettent pas de prévoir toutes les requêtes qu'ils pourraient construire ni de limiter la taille des éléments retournés à quelques centaines de lignes.

[darkelend]

et de faire comprendre à un DBA qui n'a fait que du transactionnel depuis plus de 20 ans que, en décisionnel, les outils mis à disposition des utilisateurs ne permettent pas de prévoir toutes les requêtes qu'ils pourraient construire ni de limiter la taille des éléments retournés à quelques centaines de lignes.

Les outils BI traitent de gros volumes de données certes mais ils ne sont pas là non plus pour restituer des listings de million de lignes