Tempdb inaccessible browsing et verrous!

[vbman75]

Bonjour,

nous avons migré de sql 2000 en sql 2008. sous 2008 les bases sont restées pour le moment en compatibilité 2000.

aparrement la tempdb se lock au bout de quelques minutes d'utilisations et lié à la charge utilisateurs sur le serveur SQL. impossible de browser les tables ou les SP par la suite sur cette tempdb. les autres sont OK.
idem pour une application qui se trouve en face d'une base utilisateur qui sort dorénavant avec des timed out, qui n'avais pas lieu en sql 2000?

Code :

1
2
3
SELECT * FROM sys.dm_tran_locks
SELECT * FROM sys.dm_os_waiting_tasks
sp_who2

me donnent des résultats mais difficilement exploitables. notamment pour os_waiting_task qui me renvoi une adress de job et non le nom du job par exemple.


si vous avez des pistes concernant mon problème je suis preneur.

merci!

[SQLpro]

Probablement des transactions non correctement validées ou annulée utilisant des tables temporaires.

Voyez si le trace flag 5302, vous permet de sortir de l'impasse :
"Alters default behavior of select…INTO (and other processes) that lock system tables for the duration of the transaction. This trace flag disables such locking during an implicit transaction.
"
Le mettre dans le service en -T

A +

[vbman75]

Bonjour

merci pour la réponse.
pouvez vous m'aiguillez comment faire l'ajout du flag avec -t dans le service; je ne comprend pars vraiment le fonctionnement? (désolé)

je constate au lancement de procédures stockées qui peuvent durer assez longtemps que des lock sont posés de façon illimité il me semble. voila l'analyse
si je constate au niveau moniteur d'activité :
-> temp d'attente 85859554 (ms) en état running pour le spid 44
-> un lock posé de 8000 ms qui ne baisse jamais
-> si je regarde par select * from sys.dm_tran_locks je constate plusieurs
DATABASE BULKOP_BACKUP_LOG sur id database = 2 = tempdb
-> si je lance un sp_who2 je vois spid 44 et 45 suspended blocked by 44 ce ceux plusieurs fois.

ps : pour info, nous avons migré de sql 2000 a sql 2008 r2 64bits et ces pb font suite à cette migration.

si une personne pourrai me dire poruquoi je me retrouve avec des bulkop_backup_log alors que je n'ai aucun travaux de backuop qui est lancé a ce moment ni même dans les procédures stockées! encore moins en sql 200 ou cela fonctionnait parfaitement

Merci de votre aide.

[elsuket]

Bonjour,

Quelle est la taille de votre base de données TempDB, et l'espace libre sur les disques qui la supportent (EXEC xp_fixeddrives) ?
ça sent le rollback ou timeout sur croissance du fichier du journal des transactions ...

@++

[vbman75]

le serveur a 10gb de memoire et 4 vpcu
j'ai donc crée 4 fichiers tempdb
tempdev -> mdf
tempdev1 a tempdev3 -> ndf
chacune de ses bases est de taille équivalente de 150 Mo avec 100 de log ctroissance par 10% , illimité

sinon la proc stock crée un tables , envoi un mail, delete la table, etc X8.. cela dans un BEGIN TRAN.

merci a+

[mikedavem]

Il faudrait avoir plus de détails concernant vos processus qui bloquent :

Il faudrait récupérer la requête qui bloque. Pouvez vous poster le résultat de cette requête :

++

[elsuket]

Je crois que vous confondez fichier et base de données ... ce n'est pas la même chose !
Utilisez plutôt la requête que je donne dans mon billet :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
USE TempDB
GO
 
;WITH  
  CTE AS  
  (  
  SELECT name AS nomLogique,  
  physical_name AS nomPhysique,  
  CASE is_percent_growth 
  WHEN 0 THEN CAST(growth * 8 / 1024 AS VARCHAR(8)) + ' MB' 
  ELSE CAST(growth AS VARCHAR(3)) + ' %' 
  END AS accroissement,  
  CAST((size * 8) / CAST(1024 AS NUMERIC(14, 2)) AS NUMERIC(14, 2)) AS tailleFichier_MB,  
  CAST((FILEPROPERTY(name, 'SpaceUsed') * 8) / CAST(1024 AS NUMERIC(14, 2)) AS NUMERIC(14, 2)) AS espaceOccupe  
  FROM sys.database_files  
  )  
SELECT nomLogique,  
  nomPhysique,  
  tailleFichier_MB,  
  accroissement,  
  espaceOccupe AS espaceOccupe_MB,  
  tailleFichier_MB - espaceOccupe AS espaceLibre_MB,  
  CAST((CAST(espaceOccupe AS NUMERIC(14, 2)) / tailleFichier_MB) * 100 AS NUMERIC(14, 2)) AS [%occupe],  
  CAST((CAST(tailleFichier_MB - espaceOccupe AS NUMERIC(14, 2)) / tailleFichier_MB) * 100 AS NUMERIC(14, 2)) AS [%libre]  
FROM CTE

@++

[vbman75]

Bonsoir,

j'ai lancé le script
pour la tempdb je suis dorénavant a 95% libre sur l'ensemble de fichiers (j'ai tout de meme augmenté la taille de la tempdb comme c'était le cas en SQL2000)

pour la base utilisateur sur laquelle sont lancées les procédures stockées, voici le résultat

userdb_Data F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\userdb.MDF 3800.00 200 MB 3076.69 723.31 80.97 19.03
userdb_Log G:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\userdb.LDF 2238.00 250 MB 43.52 2194.48 1.94 98.06

[elsuket]

OK. Pourriez-vous nous donner les valeurs que vous obtenez pour les disques F et G après l'exécution de :

Code :

EXEC xp_fixeddrives

Cette procédure stockée étendue non documentée retourne l'espace disque libre pour tous les disques accessibles à SQL Server.

@++

[vbman75]

bonjour,
voici

C 17106
F 131798
G 61959

Ce matin j'ai remarqué aucun temps en attente dans la partie lock du moniteur d'activité. cepandant si je souhaite browser les tables de la tempdb ou autres éléments ca m'est une nouvelle fois impossible : Error 12222 : délai de requête de verrou dépassé.

et toujours 4 lignes BULKOP_BACKUP_LOG depuis dm_tran_locks'

il y a actuellement un cinquantaine d'utilisateurs accédant au serveur SQL.

concernant la procédure stockée, elle est passé ce matin .

[SQLpro]

Auand vous dites 4 vcpu c'est 4 coeur ?, 4 cpu physique ??, une virtualisation ???

Soyez précis !!! sinon on ne peut pas vous aider...

A +

[vbman75]

je vais essayé d'être le plus précis
en terme d'architecture :

- SQL 2008R2 64bits STD
- Windows 2008R2 64bits
- les partitions F et G ou se situent les bases; ont été formatées en bloc de 64k
- Tous les disques ont suffisamment d'espace libre

--> c'est une plateforme virtuelle, le serveur est hébergé sur un ESX et je lui ai affecté 4 cpu, vu par windows 4 cpu distincts. cet ESX n'est pas chargé, loin de la.
--> le comportement des verrous tempdb n'est présent qu'à partir du moment ou le serveur est sollicité par des utilisateurs. en gros tôt le matin et le soir j'ai de nouveau accès à la base.
j'ai des tests plus précis que je pourrai fournir dés que possible si vous jugez util .

que puis vous fournir comme information supplémentaires?

Merci

[SQLpro]

Commencez par dé-virtualiser votre serveur... Aucun SGBDR n'est franchement compatible avec une couche de virtualisation si vous voulez obtenir des performances.
C'est une hérésie et une erreur de débutant, lié à l'incompétence notoire des administrateurs systèmes qui veulent à tout prix succomber à la mode de la virtualisation sans discernement !
Bref, si la mode consistait à mettre son slip sur la tête, je crois aujourd'hui que les informaticiens le ferait sans se poser aucune question...
Bref, ce que j'appelle les techno victimes.

A lire : http://blog.developpez.com/sqlpro/p8...irtualisation/

A +

[mikedavem]

--> c'est une plateforme virtuelle, le serveur est hébergé sur un ESX et je lui ai affecté 4 cpu, vu par windows 4 cpu distincts. cet ESX n'est pas chargé, loin de la.
--> le comportement des verrous tempdb n'est présent qu'à partir du moment ou le serveur est sollicité par des utilisateurs. en gros tôt le matin et le soir j'ai de nouveau accès à la base.
j'ai des tests plus précis que je pourrai fournir dés que possible si vous jugez util .

que puis vous fournir comme information supplémentaires?

- Quelles requêtes sont lancées ?
- Quelles requêtes bloquent ?
- Quel architecture du sous système disque pour les fichiers tempdb ? Raid physique, logique, Datastore partagé ? etc ...
- Problème survenu après virtualisation ?

++

[vbman75]

OK je conçoit votre idée.
Ceci dit je dois faire avec ce que j'ai; ce qui est en place avant mon arrivée et ce qui a été budgté en terme de matériel sur une année, qui diffère de vos préférences surement..je ne suis pas seul au monde et n'ai pas le pouvoir de décision

Merci tout de même pour vos remarques et conseils

[mikedavem]

Bis

Quelles requêtes sont lancées ?
- Quelles requêtes bloquent ?
- Quel architecture du sous système disque pour les fichiers tempdb ? Raid physique, logique, Datastore partagé ? etc ...
- Problème survenu après virtualisation ?

Sans plus d'informations de votre part ca va etre dur de vous aider ..

++

[SQLpro]

Les américains ont une expression pour cela :
garbage in, garbage out
Qui peut se traduire, par si j'ai de la merde en entrée, j'ai de la merde en sortie.

Qaund à cette remarque :

qui diffère de vos préférences surement.

Permettez moi de vous faire remarquez que ces arguments (et la copie de slide qui est dans cet article), ne vient pas directement de moi, mais d'une conférence de Microsoft lors de Tech Days 2010 auquel j'étais convié pour parler du futur du métier de DBA !

Si les gens qui vous dirigent sont des incapables, il y a 3 solutions :
1) mettre le nez dans leur caca
2) foutre le camp de la boîte pour éviter d'autres emmerdes
3) courber l'échine comme un cerf et passer à autre chose, mais ne pas venir demander l'impossible...

A +

[vbman75]

je ne peux pas vous répondre sur qu'est ce qui bloque, qu'est ce qui est lancé, c'est en production avec des applications derrières, donc il y a un tas de requête qui passent qui d'ailleurs fonctionnent. les utilisateurs travaillent en somme..

je partais, dans mon premier post, d'un constat :
celui de l'accès a tempdb, d'un verrou sur cette base, de l'impossibilité à la browser, des backup log et backup DB toute la journée dans l'historique des transactions (dm_tran_locks); je n'ai pas toutes les billes pour vous dire tout ce que vous attendez surement et j'en suis désolé.

- Quel architecture du sous système disque pour les fichiers tempdb ? Raid physique, logique, Datastore partagé ? etc ...
VMFS , d'1to, une dizaine de VM connectés

- Problème survenu après virtualisation ?
Non, le sql2000 étaient déjà virtualisé. aucun problème avant.

[SQLpro]

Bien entendu la VM ne provoque pas tous les problèmes. Mais déjà l'architecture de VM que vous nous avez donné est fortement en cause !
10 VM 1 seul bloc disque avec des agrégats taillés n'importe comment c'est le plus sur moyen de pourrir rapidement la partie persistance de la base à cause du JT qui doit écrire en temps réel.
Malheureusement avec une base de merde (je n'ai pas dit que c'était votre cas... mais y a t-il au moins des clefs à toutes les tables et suffisamment d'index ???, y a t-il eut une normalisation correcte du modèle ???) et une config à chier, la dégradation n'est pas du tout linéaire... Dans un tel cas, dès que la BD dépasse la taille de la RAM, il y a pagination du serveur SQL (qui ne peut exécuter ses requêtes que si les données sont en RAM) et là les temps explosent rapidement (fois 1 000 au minimum !!!!), d'où des verrous maintenu plus longtemps, d'où des blocages, d'où statistiquement l'augmentation fracassante du risque d'interblocage !!!

Lisez les articles que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/optimisation/

Questions

• Quel est le niveau de RAID ?
• Comment sont taillés les agrégats ?
• On t-ils été alignés sur des disques physiques ?
• Avez-vous fait du pass-through au niveau des disque dans la VM ?

A +

[Philippe Robert]

Envoyé par SQLpro

Commencez par dé-virtualiser votre serveur... Aucun SGBDR n'est franchement compatible avec une couche de virtualisation si vous voulez obtenir des performances.
C'est une hérésie et une erreur de débutant, lié à l'incompétence notoire des administrateurs systèmes qui veulent à tout prix succomber à la mode de la virtualisation sans discernement !
Bref, si la mode consistait à mettre son slip sur la tête, je crois aujourd'hui que les informaticiens le ferait sans se poser aucune question...
Bref, ce que j'appelle les techno victimes.

A lire : http://blog.developpez.com/sqlpro/p8...irtualisation/

A +

Ouf, j'ai pour l'instant échappé à la virtualisation...

Pour ce qui est du problème relatif à Tempb, nous l'avons placé sur un disque SSD. Les autres databases étant sur un SAN. Et les performances sont extraordinaires.