{"id":184,"date":"2009-08-05T09:56:42","date_gmt":"2009-08-05T08:56:42","guid":{"rendered":"https:\/\/www.alldb.fr\/blogs\/?p=184"},"modified":"2023-04-20T07:04:47","modified_gmt":"2023-04-20T06:04:47","slug":"sql-server-sessions-consommatrices-dans-tempdb","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/sql-server-sessions-consommatrices-dans-tempdb\/","title":{"rendered":"Sessions consommatrices dans tempdb"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

Je sais ce que vous allez dire: on l’a d\u00e9j\u00e0 vu partout, il y a d\u00e9j\u00e0 un million d’articles sur le sujet, quel int\u00e9r\u00eat pour une soci\u00e9t\u00e9 qui se d\u00e9fend justement de poster sur des sujets originaux, etc…<\/p>\n

Je vous r\u00e9pondrai que le probl\u00e8me n’est pas trivial. Tout le monde utilise les vues DMV pour r\u00e9cup\u00e9rer les infos de la session qui occupe l’espace dans tempdb, notamment le texte SQL de la requ\u00eate:
\n– sys.dm_db_task_space_usage<\/em>: pour voir l’espace utilis\u00e9 par session.
\n– sys.dm_exec_requests<\/em> ou sys.dm_exec_query_stats<\/em> + cross apply sys.dm_exec_sql_text(sql_handle)<\/em>: pour plus d’infos sur les sessions (stats + texte des requ\u00eates).<\/p>\n

Seulement voil\u00e0: les sessions dont le plan a \u00e9t\u00e9 purg\u00e9 du cache ne sont plus visibles dans les DMV sys.dm_exec… donc la jointure entre sys.dm_db_task_space_usage et sys.dm_exec… ne renverra pas d’infos pour des sessions qui utilisent pourtant bel et bien de l’espace dans tempdb.<\/p>\n

Donc il faut avoir recours au bon vieux dbcc inputbuffer<\/em><\/strong> pour r\u00e9cup\u00e9rer tout \u00e7a.<\/p>\n

1) D’abord, on va cr\u00e9er les tables de recueil.<\/p>\n

1.1 DBCCINPUTBUFFER va nous permettre de r\u00e9cup\u00e9rer le contenu du dbcc inputbuffer par session:<\/p>\n

CREATE TABLE tempdb.guest.DBCCINPUTBUFFER\r\n(\r\nEventType varchar(50),\r\nParameters int,\r\nSQLText varchar(8000)\r\n)<\/pre>\n
1.2 DBCCSHOWFILESTATS va nous permettre de r\u00e9cup\u00e9rer l’espace total et utilis\u00e9 dans tempdb:<\/p>\n
CREATE TABLE tempdb.guest.DBCCSHOWFILESTATS\r\n(\r\n[fileid] [int], [filegroup] [int],\r\n[totalextents] [int], [usedextents] [int],\r\n[name] [varchar] (255), [filename] [varchar] (255)\r\n)<\/pre>\n
1.3 monitorTempdb sera notre table finale d’historique.<\/p>\n
CREATE TABLE tempdb.guest.monitorTempdb\r\n(\r\ncurdate datetime,\r\nspaceintempdb bigint,\r\nspaceusedintempdb bigint,\r\nspid int,\r\nsqltext varchar(8000),\r\ninternal_objects_alloc_MB bigint,\r\ninternal_objects_dealloc_MB bigint,\r\nuser_objects_alloc_MB bigint,\r\nuser_objects_dealloc_MB bigint,\r\nprogram_name varchar(100),\r\nlogin_time datetime,\r\nhostname varchar(20),\r\nlogin_name varchar(100)\r\n)<\/pre>\n
2) Par session r\u00e9cup\u00e9rer les infos et le texte de la requ\u00eate: on va placer les sessions consommatrices ainsi que les espaces consomm\u00e9s dans un curseur, puis boucler pour chaque session_id et r\u00e9cup\u00e9rer plus d’infos (program_name, login, etc… depuis sys.dm_exec_sessions, la seule DMV qui affiche toutes les sessions actives ou non) et le dernier texte SQL g\u00e9n\u00e9r\u00e9:<\/p>\n
use tempdb\r\ngo\r\ndeclare\r\n\t@spid int,\r\n\t@sqltext varchar(8000),\r\n\t@internal_objects_alloc_page_count bigint,\r\n\t@internal_objects_dealloc_page_count bigint,\r\n\t@user_objects_alloc_page_count bigint,\r\n\t@user_objects_dealloc_page_count bigint,\r\n\t@program_name varchar(100),\r\n\t@login_time datetime,\r\n\t@hostname varchar(20),\r\n\t@login_name varchar(100),\r\n\t@SQLQUERY varchar(100),\r\n\t@spaceintempdb bigint,\r\n\t@spaceusedintempdb  bigint\r\n\r\ndeclare cr_sqltext CURSOR READ_ONLY for\r\nselect session_id,internal_objects_alloc_page_count,internal_objects_dealloc_page_count,\r\nuser_objects_alloc_page_count, user_objects_dealloc_page_count\r\nfrom sys.dm_db_task_space_usage where internal_objects_alloc_page_count > 12000 \r\n\r\nopen cr_sqltext\r\nfetch next from cr_sqltext into\r\n\t@spid,\r\n\t@internal_objects_alloc_page_count,\r\n\t@internal_objects_dealloc_page_count,\r\n\t@user_objects_alloc_page_count,\r\n\t@user_objects_dealloc_page_count\r\n\r\nwhile @@fetch_status = 0\r\nbegin\r\n\tselect\r\n\t\t@program_name = program_name,\r\n\t\t@login_time = login_time,\r\n\t\t@hostname = host_name,\r\n\t\t@login_name = login_name\r\n\tfrom sys.dm_exec_sessions where session_id = @spid\r\n\r\n\tselect @SQLQUERY = 'dbcc inputbuffer('+convert(char(3),@spid)+')'\r\n\r\n\tinsert into tempdb.guest.DBCCINPUTBUFFER exec (@SQLQUERY)\r\n\tselect @sqltext = SQLText from tempdb.guest.DBCCINPUTBUFFER \r\n\r\n\tinsert into tempdb.guest.DBCCSHOWFILESTATS exec ('DBCC showfilestats with NO_INFOMSGS')\r\n\tselect\r\n\t\t@spaceintempdb = sum(totalextents)\/16,\r\n\t\t@spaceusedintempdb = sum(usedextents)\/16\r\n\tfrom tempdb.guest.DBCCSHOWFILESTATS\r\n\r\n\tdelete from tempdb.guest.DBCCINPUTBUFFER\r\n\tdelete from tempdb.guest.DBCCSHOWFILESTATS\r\n\r\n\tinsert tempdb.guest.monitorTempdb values\r\n\t(getdate(),\r\n\t@spaceintempdb,\r\n\t@spaceusedintempdb ,\r\n\t@spid, \t@sqltext,\r\n\t@internal_objects_alloc_page_count*8192\/1048576 ,\r\n\t@internal_objects_dealloc_page_count*8192\/1048576 ,\r\n\t@user_objects_alloc_page_count*8192\/1048576 ,\r\n\t@user_objects_dealloc_page_count*8192\/1048576 ,\r\n\t@program_name ,\r\n\t@login_time ,\r\n\t@hostname ,\r\n\t@login_name)\r\n\r\nfetch next from cr_sqltext into\r\n\t@spid,\r\n\t@internal_objects_alloc_page_count,\r\n\t@internal_objects_dealloc_page_count,\r\n\t@user_objects_alloc_page_count,\r\n\t@user_objects_dealloc_page_count\r\n\r\nend\r\nclose cr_sqltext\r\ndeallocate cr_sqltext\r\ngo<\/pre>\n
Cette derni\u00e8re partie peut \u00eatre mise dans un job planifi\u00e9 toutes les minutes par exemple. C’est ainsi que j’ai d\u00e9couvert que sp_MSget_repl_commands et sp_MSdistribution_cleanup (procs de r\u00e9pli) peuvent \u00eatre extr\u00eamement consommatrices lorsque la file des requ\u00eates \u00e0 r\u00e9pliquer est tr\u00e8s importante.<\/p>\n
A+ [ David B. ]<\/p>\n