Extractions gourmandes et performances

Version imprimable

10/07/2013, 11h35
Vonziz

Extractions gourmandes et performances

Bonjour à tous,

Je travaille sur une application ASP.net / SQL Server utilisée par quelques milliers de personnes à travers le monde.

Il s'avère que nous avons des problèmes de requêtes qui partent en timeout en cascade depuis quelques temps. De nombreuses sessions utilisateurs s'en trouvent impactées.

Après analyse, nous avons identifié le problème.
Des extractions Excel des objets principaux de la base semblent locker la plupart du schéma de données et durent plusieurs minutes.

Du coup, toutes les autres sessions utilisateurs attendent la libération du verrou sur ces tables et partent en timeout au bout d'un moment.

Ma questions : comment peut-on résoudre ces problèmes de timeout afin de faire cohabiter les requêtes "classiques" des grosses extractions Excel?

Modifier des paramètres au niveau SQL Server pour la gestion des transactions?
Créer des tables parallèles pour les extractions? Via des triggers? Via des jobs SSIS?

Bref, comment gérez-vous ce genre de problématiques sur vos projets... :-)

Merci d'avance!
10/07/2013, 12h01
darkelend

Dans l'idéal, les extractions devraient se faire depuis un datawarehouse et non pas depuis tes bases OLTP.
Mais bon, parfois on ne peut pas faire autrement en fonction du client. Les requêtes que tu as identifiées sont optimisées (traitement ensembliste + indexés) ? En effet, si ces transactions sont longues car trop de pages lues ou ligne à ligne, tu augmentes les chances de blocage des autres transactions.
Quel est le niveau d'isolation de tes requêtes depuis Excel ?
10/07/2013, 13h06
Kropernic

Je n'ai encore jamais fait d'extraction vers Excel directement depuis sql server donc je vais p-e dire de la merde mais en .NET, écrire dans Excel, c'est assez lent.

Alors je pose la question de savoir si c'est le volume des données exportées est important ou non (me semble avoir lu que oui). Car si la requête est optimisée, c'est p-e l'export en lui-même qui prend du temps.

Du coup, du haut du peu d'expérience que j'ai dans le domaine, pourquoi ne pas copier temporairement les données à exporter vers excel dans une table de travail avec une colonne identifiant l'utilisateur en plus et de faire l'export vers excel depuis cette table de travail pour les supprimer ensuite ?

Ainsi, les tables de production devraient être verrouillées moins longtemps et au revoir les time-out.

Non ?
10/07/2013, 13h19
Vonziz

Merci de vos retours. :-)

Alors pour le datawarehouse, notre architecture sur ce projet ne le prévoit pas pour le moment.

Pour répondre à vos questions :

Les requêtes que tu as identifiées sont optimisées (traitement ensembliste + indexés) ?
=> Nous avons déjà fait une passe d'optimisation mais certaines requêtes peuvent retournées jusqu'à 100 000 lignes de données et concernent plusieurs tables en bases donc difficile d'optimiser plus à partir d'un moment...

Quel est le niveau d'isolation de tes requêtes depuis Excel ?
=> On reste sur de l'isolation standard, pas de WITH NO LOCK ou autre, as-tu quelque chose à me proposer à ce niveau?

Je n'ai encore jamais fait d'extraction vers Excel directement depuis sql server donc je vais p-e dire de la merde mais en .NET, écrire dans Excel, c'est assez lent.
=> Ca ne pose de problème au niveau SGBD car on fait déjà la requête en base puis on génère le fichier Excel à partir du DataSet donc la charge de la génération du fichier est encaissée par le IIS et non par le SQL Server.

Du coup, du haut du peu d'expérience que j'ai dans le domaine, pourquoi ne pas copier temporairement les données à exporter vers excel dans une table de travail avec une colonne identifiant l'utilisateur en plus et de faire l'export vers excel depuis cette table de travail pour les supprimer ensuite ?
=> OK mais comment faire pour que la copie de ces données dans une autres tables ne soit pas couteuse en temps également...?
10/07/2013, 13h22
DotNetMatt

Citation:

Envoyé par darkelend

Dans l'idéal, les extractions devraient se faire depuis un datawarehouse et non pas depuis tes bases OLTP.
Mais bon, parfois on ne peut pas faire autrement en fonction du client.

Je te rejoins tout à fait sur ce point ! Il y a un moment où un simple système OLTP ne suffit plus, et il faut commencer à mettre en place de l'OLAP (quelle que soit la forme choisie).

A force de tirer sur la corde côté OLTP, on peut finir par se retrouver face à un goulet d'étranglement qui pénalise non seulement les applications opérationnelles, mais aussi les applications de reporting. Sans anticipation, c'est le bazar assuré, car la contention sera de plus en plus présente.

Une modification du niveau d'isolation serait une bonne idée, mais il faut insister sur le fait que c'est temporaire et que cela peut nuire à l'intégrité des données selon les scénarios.
10/07/2013, 14h12
SQLpro

Citation:

Envoyé par Vonziz

Quel est le niveau d'isolation de tes requêtes depuis Excel ?
=> On reste sur de l'isolation standard, pas de WITH NO LOCK ou autre, as-tu quelque chose à me proposer à ce niveau?

Utilisez le niveau d'isolation SNAPSHOT. Ceci enlève tout verrou de lecture et vos problèmes seront résolus.

Citation:

Je n'ai encore jamais fait d'extraction vers Excel directement depuis sql server donc je vais p-e dire de la merde mais en .NET, écrire dans Excel, c'est assez lent.
=> Ca ne pose de problème au niveau SGBD car on fait déjà la requête en base puis on génère le fichier Excel à partir du DataSet donc la charge de la génération du fichier est encaissée par le IIS et non par le SQL Server.

Un dataset ouvre un curseur en live sur les données. Donc vérrouille. Mettez les paramètres nécessaires pour que votre dataset soit ouvert en READ ONLY, FORWARD ONLY, STATIC...

Citation:

Du coup, du haut du peu d'expérience que j'ai dans le domaine, pourquoi ne pas copier temporairement les données à exporter vers excel dans une table de travail avec une colonne identifiant l'utilisateur en plus et de faire l'export vers excel depuis cette table de travail pour les supprimer ensuite ?
=> OK mais comment faire pour que la copie de ces données dans une autres tables ne soit pas couteuse en temps également...?

Non, cela pourrirait la RAM de 2 fois les données en cache ! Inutile.... Fausse bonne solution.

A +
10/07/2013, 14h16
Kropernic

Citation:

Envoyé par SQLpro

Non, cela pourrirait la RAM de 2 fois les données en cache ! Inutile.... Fausse bonne solution.

A ma décharge, j'avais prévenu que j'allais probablement dire une connerie :aie:
10/07/2013, 15h02
Vonziz

SQLPro, merci de votre retour.

J'ai exécuté l'instruction suivante sur ma base :

Code:

ALTER DATABASE <ma base> SET ALLOW_SNAPSHOT_ISOLATION ON;

J'ai fais des tests tout de suite après mais j'ai toujours des timeouts en local.

Les requêtes SQL s'empilent et se mettent en SUSPENDED tant que mon extraction n'a pas finie.

Faut-il redémarrer SQL Server pour la prise en compte de tel paramètre?
Peut-être faut-il vérifier d'autre paramétrage?

Kropernic, toute proposition est bonne à prendre, on en discute, j'apprécie le geste. ;)
10/07/2013, 15h47
SQLpro

Il ne suffit pas d'autoriser le niveau d'isolation SNAPSHOT... Il faut en sus le mettre au niveau de la session.
Commencer votre requête en plaçant votre session en SNAPSHOT :

Code:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

A +
10/07/2013, 21h20
Vonziz

Merci SQLPro.

J'essaie dès demain.

Deux questions :

- Faut-il positionner le niveau SNAPSHOT uniquement sur la transaction ou sur la transaction et la base de données?

- Quel(s) sont les effets de bord possibles sur cette transaction au niveau corruption de données ?

Merci de votre retour!
11/07/2013, 09h11
SQLpro

Citation:

Envoyé par Vonziz

Merci SQLPro.

J'essaie dès demain.

Deux questions :

- Faut-il positionner le niveau SNAPSHOT uniquement sur la transaction ou sur la transaction et la base de données?

Il faut :
1° autoriser la base à utiliser le niveau SNAPSHOT (donc ALTER DATABASE....)
2° lancer la commande SET TRANSACTION ISOLATION LEVEL SNAPSHOT; avant la requête mais dans la même session (donc connexion)

Citation:

- Quel(s) sont les effets de bord possibles sur cette transaction au niveau corruption de données ?

Merci de votre retour!

Aucun puisque vous ne faite que lire une copie à l'instant t des données de la table.

A +

Bonjour,

Alors j'ai fais des tests avec ces préconisations mais pas de changements.
Par contre, quand je regarde mes requêtes via SQL Profiler, je dénote deux choses.

- Les requêtes sont exécutées via la procédures stockées sp_executesql, ce qui donne quelque chose du genre :
Code:

1 2 3 4 5 exec sp_executesql N' SET TRANSACTION ISOLATION LEVEL SNAPSHOT; SELECT * FROM test',@timestamp='2013-07-17 17:11:47.107',@login='root'
- Après chaque requête, j'ai un reset de la connexion :

Code:

exec sp_reset_connection

et un repositionnement de la transaction :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 -- network protocol: LPC set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed
Est-ce que ces deux points peuvent empêcher le niveau SNAPSHOT de fonctionner correctement?

22/07/2013, 11h18
SQLpro

Non, le snapshot s'effectue bien. Ne vous attendez pas à de meilleurs perf. mais plus de blocage et pas de deadlock.

Pour les perf, il faut une base parfaitement modélisée, puis indexer ou encore dé normaliser intelligemment....

A +
27/08/2013, 10h33
Vonziz

Bien que les résultats n'aient pas été significatifs, je vous remercie de vos retours.

Nous nous orientons vers la mise en place de table adjacentes que l'on alimentera la nuit pour "déverser" la charge des extractions sur celles-ci.