Comment minimiser les connexions à SQL Server lors de l’exécution d’un rapport SSRS

Avez-vous déjà eu un rapport qui lance un très grand nombre de requêtes SQL / MDX et dont la performance semble affectée par l’ouverture / fermeture d’un trop grand nombre de connexions? Nous oui.

Dans notre situation, l’exécution des requêtes en soi n’était pas si longue mais le fait qu’une connexion soit créée pour chacune venait multiplier le temps total par un facteur significatif. Ce comportement était amplifié significativement lorsque le serveur SQL utilisé était à bout de ressources.

SQL Server Profiler peut nous aider à comprendre ce qui se passe. Il faut toutefois savoir ceci:

A SPID in SQL Server is a Server Process ID. These process ID’s are essentially sessions in SQL Server. Everytime an application connects to SQL Server, a new connection (or SPID) is created. This connection has a defined scope and memory space and cannot interact with other SPIDs. The term SPID is synonymous with Connection, or Session.

Un seul rapport, une connexion et 2 datasets

Lorsqu’un rapport SSRS a plusieurs datasets utilisant la même connexion vers SQL Server, le comportement par défaut est:

1. D’exécuter sp_reset_connection avant l’exécution des requêtes
2. D’utiliser une connexion différente pour chaque requête. La colonne SPID dans SQL Server Profiler changera pour chaque requête.

Pour réutiliser la même connexion, il suffit de cocher « Use single transaction when processing the queries » sur la connexion SSRS (en francais: Utiliser une transaction unique lors du traitement des requêtes)

Un rapport utilisant un sous-rapport partageant la même connexion

Si les deux rapports utilisent l’option « Use single transaction when processing the queries », la même connexion sera utilisée pour toutes les requêtes des deux rapports.

Cependant, une exécution de sp_reset_connection sera effecutée entre les requêtes du rapport principal et les requêtes du sous-rapport.

Afin d’éviter ceci, il suffit de mettre True pour « Merge Transactions » sur les propriétés du sous-rapport dans le rapport parent:

Dans ce cas, il n’y aura qu’une exécution de sp_reset_connection au démarrage du rapport.

Et pour les connexions Analysis Services (SSAAS)?

Sql Server Profiler affiche une colonne ConnectionID (en plus du SPID) et on voit que le comportement est similaire que pour l’engin relationnel:

Sans l’option de transaction unique, il y a deux connexions:

En l’activant:

De la même manière, si on n’utilise pas « Merge Transactions », il y aura une connexion pour un rapport parent, et une autre pour le sous-rapport:

En activant l’option, on vient de sauver une connexion:

Testez dans votre environnement

Comme pour toutes les optimisations de performance, il est important de tester dans votre environnement. Il se peut que ça ne change rien dans votre environnement, ou pour certains rapports. Mais dans d’autres circonstances, vous pourriez économiner beaucoup de temps d’exécution.

Références

SQL Server SPID – What is it?
http://sqlserverplanet.com/dba/spid-what-is-it

SQL Server: Understanding and Controlling Connection
http://www.pythian.com/blog/sql-server-understanding-and-controlling-connection/