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)

image

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:

image

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:

image

En l’activant:

image

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:

image

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

image

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/

Une réflexion sur “Comment minimiser les connexions à SQL Server lors de l’exécution d’un rapport SSRS

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s