MS SQL Server
  1. MS SQL Server
  3. Pourquoi cette requête est-elle lente la première fois que je démarre le service?
Intereting Posts
Meilleure optimization pour une grande table SQL Server (loggings de 100 à 200 mégaoctets) Comment passer efficacement un paramètre SSRS 2008 à choix multiple à une procédure stockée Implications des performances de l'utilisation de l'alias dans la clause HAVING Utilisez une variable au lieu de la requête UNION Y a-t-il un rlocation pour Transact-SQL Migrations automatiques plusieurs services une database Fusionner plusieurs lignes à un server SQL NHibernate: Configure () dans chaque request de page? Créer DateTimeOffset 00:01 / 23:59 à partir DateTimeOffset donné pour le jour même Comment lire la dernière ligne avec SQL Server Tables spécifiques MS SQL suspendues à des requêtes SQL Server – order by clause dépend de la paire (list de champs, type) Modifier le code XML avec la requête SQL SQL Server 2008 R2 Problème de connection à Apache nifi Combiner le Générateur de rapports de colonne et de graphique linéaire 3.0

Pourquoi cette requête est-elle lente la première fois que je démarre le service?

D'accord. Voici ce que j'essaie de faire: 

USE tempdb; SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number INTO Numbers FROM sys.objects s1 CROSS JOIN sys.objects s2 CROSS JOIN sys.objects s3 CROSS JOIN sys.objects s4;

C'est l'une de ces requêtes "faites moi une table de nombres".

Voici le problème. Si je l'exécute immédiatement après que le service SQL Server a été (re) démarré, cela prend une éternité. Pas pour toujours comme dans dix secondes et je le veux plus vite. Pour toujours, je l'ai laissé passer deux heures par hasard et je devais encore le tuer. Je pense que ça ne reviendra jamais . Et normalement, cela prend less de deux secondes sur ma machine pour l'exécuter.

Cependant , si je le fais à la place: 

 USE tempdb; SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number INTO Numbers FROM sys.objects s1 CROSS JOIN sys.objects s2 CROSS JOIN sys.objects s3; DROP TABLE Numbers; SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number INTO Numbers FROM sys.objects s1 CROSS JOIN sys.objects s2 CROSS JOIN sys.objects s3 CROSS JOIN sys.objects s4;

Ensuite, cela fonctionne comme prévu: le premier SELECT court en less de deux secondes, tout comme le second. Pourquoi est-ce que je n'utilise pas la version à trois tables? Parce qu'il n'y a pas assez d'inputs dans sys.objects pour ce nombre cubes pour égaler un million de lignes de résultat. Mais ce n'est même plus le point.

Quoi qu'il en soit, à partir de maintenant, je peux répéter cette seconde DROP / SELECT…INTO autant que je veux, pas de problème. D'une façon ou d'une autre, cette première version à trois tables a été parfaite pour toujours. Au less, jusqu'au prochain redémarrage du service et / ou redémarrage de la machine. À ce stade, l'exécution de ce dernier SELECT nouveau ne revient jamais. Encore.

Voici où il commence à devenir encore plus bizarre. Si je pare ce premier SELECT à une version à deux tables: 

 USE tempdb; SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number INTO Numbers FROM sys.objects s1 CROSS JOIN sys.objects s2; DROP TABLE Numbers; SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number INTO Numbers FROM sys.objects s1 CROSS JOIN sys.objects s2 CROSS JOIN sys.objects s3 CROSS JOIN sys.objects s4;

Cela rend également le second SELECT exécuté pour toujours. Comme le fait une version à un tableau. D'une certaine manière, cette version à trois tables est magique!

Qu'est-ce qui se passe ici? Pourquoi est-ce lent?

(Et avant que quelqu'un ne pointe que je crée une table permanente dans tempdb , oui, je sais.) Changer pour les tables temporaires ne fait aucune différence.)

Informations supplémentaires:

  • C'est SQL Server 2012 Developer Edition
  • Sortie d' EXEC sp_WhoIsActive @find_block_leaders = 1, @sort_order = '[blocked_session_count] DESC' (scripté en XML pour qu'il puisse être lu ici) est: 
 <? xml version = "1.0"?>
 <RÉSULTATS1>
     <RECORD>
         <jj hh: mm: ss.mss> 00 00: 10: 45.066 </ jj hh: mm: ss.mss>
         <session_id> 52 </ session_id>
         <sql_text> & lt;? query -
 SÉLECTIONNEZ TOP 1000000 IDENTITY (INT, 1, 1) Numéro
 INTO Numéros
 FROM sys.objects s1
 CROSS JOIN sys.objects s2
 CROSS JOIN sys.objects s3
 CROSS JOIN sys.objects s4;

 -? & gt; </ sql_text>
         <login_name> mon propre nom de connection expurgé </ login_name>
         <wait_info> (99ms) LCK_M_X </ wait_info>
         <CPU> 9 750 </ CPU>
         <tempdb_allocations> 713 </ tempdb_allocations>
         <tempdb_current> 702 </ tempdb_current>
         <blocking_session_id> NULL </ blocking_session_id>
         <blocked_session_count> 0 </ blocked_session_count>
         <lectures> 583,273 </ lectures>
         <écrit> 537 </ écrit>
         <physical_reads> 50 </ physical_reads>
         <used_memory> 3 </ used_memory>
         <status> suspendu </ status>
         <open_tran_count> 2 </ open_tran_count>
         <percent_complete> NULL </ percent_complete>
         <nom_hôte> mon propre nom d'ordinateur a été supprimé </ nom_hôte>
         <nom de la database> tempdb </ database_name>
         <nom_programme> Microsoft SQL Server Management Studio - Requête </ nom_programme>
         <start_time> 2013-11-23 23: 48: 19.473 </ start_time>
         <login_time> 2013-11-23 23: 47: 47.060 </ login_time>
         <request_id> 0 </ request_id>
         <collection_time> 2013-11-23 23: 59: 04.560 </ collection_time>
     </ RECORD>
 </ RESULTS1>

Plus d'infos ajoutées:

Pourquoi je mets ceci dans tempdb, c'est que cela fait partie d'un script destiné à être exécuté sur des installations vierges, et tempdb est garanti d'être là. Comme je l'ai dit, le passage aux arrays temporaires mondiaux ne diffère pas.

Je peux aussi reproduire cela 100% du time sur ma machine. (voir note à la fin)

L'essentiel du problème est que vous supprimez des verrous S sur les lignes de la table système dans tempdb qui peuvent entrer en conflit avec les verrous nécessaires pour les transactions internes de nettoyage tempdb .

Lorsque ce travail de nettoyage est alloué à la même session qui possède le verrou S , un blocage indéfini peut se produire.

Pour éviter ce problème, vous devez arrêter de referencer les objects system dans tempdb .

Il est possible de créer une table de nombres sans referencer aucune table externe. Ce qui suit doit lire aucune ligne de la table de base et ne prend donc aucun verrou. 

 WITH Ten(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ) SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number INTO Numbers FROM Ten T10, Ten T100, Ten T1000, Ten T10000, Ten T100000, Ten T1000000

Étapes à suivre pour reproduire

D'abord créer une procédure 

 CREATE PROC P AS SET NOCOUNT ON; DECLARE @T TABLE (X INT) GO

Puis redémarrez le service SQL et dans une connection, exécutez 

 WHILE NOT EXISTS(SELECT * FROM sys.dm_os_waiting_tasks WHERE session_id = blocking_session_id) BEGIN /*This will cause the problematic droptemp transactions*/ EXEC sp_recomstack 'P' EXEC P END; SELECT * FROM sys.dm_os_waiting_tasks WHERE session_id = blocking_session_id

Ensuite, dans une autre connection 

 USE tempdb; SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number INTO #T FROM sys.objects s1 CROSS JOIN sys.objects s2 CROSS JOIN sys.objects s3 CROSS JOIN sys.objects s4; DROP TABLE #T

La requête remplissant la table Numbers semble gérer une situation de locking en direct avec les transactions internes du système qui nettoient les objects temporaires tels que les variables de table.

J'ai réussi à bloquer l'identifiant de session 53 de cette manière. Il est bloqué indéfiniment. La sortie de sp_WhoIsActive montre que ce spid passe presque tout le time suspendu. Dans les exécutions consécutives, les nombres dans la colonne reads augmentent mais les valeurs dans les autres colonnes restnt largement les mêmes.

La durée d'attente n'indique pas de motif croissant, mais indique qu'elle doit être débloquée périodiquement avant d'être à nouveau bloquée. 

 SELECT * FROM sys.dm_os_waiting_tasks WHERE session_id = blocking_session_id

Résultats 

 +----------------------+------------+-----------------+------------------+-----------+--------------------+-----------------------+---------------------+--------------------------+--------------------------------------------------------------------------------------------------+ | waiting_task_address | session_id | exec_context_id | wait_duration_ms | wait_type | resource_address | blocking_task_address | blocking_session_id | blocking_exec_context_id | resource_description | +----------------------+------------+-----------------+------------------+-----------+--------------------+-----------------------+---------------------+--------------------------+--------------------------------------------------------------------------------------------------+ | 0x00000002F2C170C8 | 53 | 0 | 86 | LCK_M_X | 0x00000002F9B13040 | 0x00000002F2C170C8 | 53 | NULL | keylock hobtid=281474978938880 dbid=2 id=lock2f9ac8880 mode=U associatedObjectId=281474978938880 | +----------------------+------------+-----------------+------------------+-----------+--------------------+-----------------------+---------------------+--------------------------+--------------------------------------------------------------------------------------------------+

Utilisation de l'identifiant dans la description de la ressource 

 SELECT o.name FROM sys.allocation_units au WITH (NOLOCK) INNER JOIN sys.partitions p WITH (NOLOCK) ON au.container_id = p.partition_id INNER JOIN sys.all_objects o WITH (NOLOCK) ON o.object_id = p.object_id WHERE allocation_unit_id = 281474978938880

Résultats 

 +------------+ | name | +------------+ | sysschobjs | +------------+

Fonctionnement 

 SELECT resource_description,request_status FROM sys.dm_tran_locks WHERE request_session_id = 53 AND request_status <> 'GRANT'

Résultats 

 +----------------------+----------------+ | resource_description | request_status | +----------------------+----------------+ | (246708db8c1f) | CONVERT | +----------------------+----------------+

login via le CAD et en cours d'exécution 

 SELECT id,name FROM tempdb.sys.sysschobjs WITH (NOLOCK) WHERE %%LOCKRES%% = '(246708db8c1f)'

Résultats 

 +-------------+-----------+ | id | name | +-------------+-----------+ | -1578606288 | #A1E86130 | +-------------+-----------+

Curieux de savoir ce que c'est 

 SELECT name,user_type_id FROM tempdb.sys.columns WHERE object_id = -1578606288

Résultats 

 +------+--------------+ | name | user_type_id | +------+--------------+ | X | 56 | +------+--------------+

C'est le nom de la colonne dans la variable de table utilisée par le proc stocké.

Fonctionnement 

 SELECT request_mode, request_status, request_session_id, request_owner_id, lock_owner_address, t.transaction_id, t.name, t.transaction_begin_time FROM sys.dm_tran_locks l JOIN sys.dm_tran_active_transactions t ON l.request_owner_id = t.transaction_id WHERE resource_description = '(246708db8c1f)'

Résultats 

 +--------------+----------------+--------------------+------------------+--------------------+----------------+-------------+-------------------------+ | request_mode | request_status | request_session_id | request_owner_id | lock_owner_address | transaction_id | name | transaction_begin_time | +--------------+----------------+--------------------+------------------+--------------------+----------------+-------------+-------------------------+ | U | GRANT | 53 | 227647 | 0x00000002F1EF6800 | 227647 | droptemp | 2013-11-24 18:36:28.267 | | S | GRANT | 53 | 191790 | 0x00000002F9B16380 | 191790 | SELECT INTO | 2013-11-24 18:21:30.083 | | X | CONVERT | 53 | 227647 | 0x00000002F9B12FC0 | 227647 | droptemp | 2013-11-24 18:36:28.267 | +--------------+----------------+--------------------+------------------+--------------------+----------------+-------------+-------------------------+

La transaction SELECT INTO tempdb.sys.sysschobjs un verrou S sur la ligne dans tempdb.sys.sysschobjs appartenant à la variable de table #A1E86130 . La transaction droptemp ne peut pas get un verrou X sur cette ligne à cause de ce verrou S conflit.

L'exécution répétée de cette requête révèle que le transaction_id de la transaction droptemp change à plusieurs resockets.

Je spécule que SQL Server doit allouer ces transactions internes sur les spids user et les prioriser avant de faire le travail de l'user. Ainsi, l'identifiant de session 53 est bloqué dans un cycle constant où il démarre une transaction droptemp , est bloqué par la transaction de l'user s'exécutant sur le même spid. Annule la transaction interne puis répète le process indéfiniment.

Cela est corroboré par le suivi des divers events de locking et de transaction dans SQL Server Profiler une fois que le spid est bloqué.

Profiler

J'ai également retracé les events de locking avant cela.

Bloquer le blocage des events

LockAquisitionPatternBlockingTransaction

La plupart des verrous de key partagés supprimés par la transaction SELECT INTO sur les keys de sysschobjs sont immédiatement libérés. L'exception est le premier verrou (246708db8c1f) .

Cela a du sens car le plan montre des parsings de loops nestedes de [sys].[sysschobjs].[clst] [o] et parce que les objects temporaires reçoivent des objectids négatifs, ils seront les premières lignes rencontrées dans l'ordre de balayage.

J'ai également rencontré la situation décrite dans l'OP où l'exécution d'une jointure croisée à trois voies semble d'abord permettre à la voie à quatre de réussir.

Les premiers events de la trace de la transaction SELECT INTO présentent un model entièrement différent.

LockAquisitionPatternNonBlockingTransaction

C'était après un redémarrage du service, de sorte que les valeurs des ressources de locking dans la colonne de données de text ne sont pas directement comparables.

Au lieu de conserver le verrou sur la première touche, puis un schéma d'acquisition et de libération des keys suivantes, il semble acquérir beaucoup plus de verrous sans les libérer au départ.

Je présume qu'il doit y avoir une certaine variance dans la stratégie d'exécution qui évite le problème.

Mettre à jour

L'élément de connection que j'ai soulevé à ce sujet n'a pas été marqué comme étant corrigé, mais je suis maintenant sur SQL Server 2012 SP2 et je ne peux maintenant reproduire que l'auto-blocage temporaire plutôt que permanent. Je reçois toujours le blocage automatique, mais après un certain nombre de tentatives infructueuses pour exécuter la transaction droptemp avec succès, il semble revenir au traitement de la transaction user. Après que la transaction du système est validée, elle est exécutée avec succès. Toujours sur le même spid. (huit tentatives dans un exemple, je ne suis pas sûr si cela sera répété de manière cohérente)

Plutôt que de chasser ce problème, pourquoi ne pas simplement créer la table une fois dans la database du model , alors elle sera créée automatiquement dans tempdb ?

Pour le problème réel, nous ne soaps pas. Ma première supposition serait que votre taille initiale pour votre file tempdb est très petite (comme, 1 Mo). Ainsi, lorsque vous créez la table, elle doit développer le (s) file (s) pour l'adapter. Cela peut être assez coûteux, surtout si vous n'avez pas activé l' initialisation de file instantanée , et il peut aussi être très coûteux de développer le journal pour s'adapter à l'activité requirejse là aussi.

Autre que cela, nous pourrions continuer à deviner, mais vous serez mieux à même d'enquêter sur ce qui se passe réellement. Questions que vous voudrez requestr:

  1. Pour le spid qui essaie de créer la table, que dit sys.dm_exec_requests pour wait_type ?
  2. At-il un blocking_session_id ?
  3. Si oui, que fait cette session?