{"id":834,"date":"2009-03-25T13:43:09","date_gmt":"2009-03-25T12:43:09","guid":{"rendered":"http:\/\/soci.hu\/blog\/?p=834"},"modified":"2009-03-25T13:43:09","modified_gmt":"2009-03-25T12:43:09","slug":"vigyazni-a-profilerrel","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2009\/03\/25\/vigyazni-a-profilerrel\/","title":{"rendered":"Vigy\u00e1zni a profilerrel!"},"content":{"rendered":"<p>Az egyik foly\u00f3 munk\u00e1mban profilerrel szerett\u00fcnk volna sz\u00e9tn\u00e9zni egy szerveren, hogy l\u00e1ssuk, kik a lass\u00fa lek\u00e9rdez\u00e9sek. Eddig ez minden c\u00e9gn\u00e9l z\u00f6kken\u0151mentesen ment, mondjuk egy min. 5000-es read filter mellett sz\u00e9pen j\u00f6tt a lista. Eset\u00fcnkben azonban a profiler bekapcsol\u00e1sa ut\u00e1n gyakorlatilag el\u00e9rhetetlenn\u00e9 v\u00e1lt az sql server, de olyannyira, hogy m\u00e9g be se tudtunk r\u00e1 l\u00e9pni, \u00fajra kellett ind\u00edtani. El\u0151sz\u00f6r azzal ideologiz\u00e1ltuk meg a dolgot, hogy lass\u00fa volt a kapcsolat a profilert futtat\u00f3 g\u00e9p \u00e9s az sql server k\u00f6z\u00f6tt, de ugyan\u00edgy lefagyott akkor is, ha egy azonos LAN-on lev\u0151 g\u00e9pr\u0151l futott a kliens.<br \/>\nEset\u00fcnkben nem egy sima terhel\u00e9si minta kellett, hanem kellettek az XML Planek is, gondolom ez fek\u00fcdte meg a gyomr\u00e1t, ezek el\u0151\u00e1ll\u00edt\u00e1sa.<br \/>\nKev\u00e9sb\u00e9 megterhel\u0151 m\u00f3dja a val\u00f3di planek kinyer\u00e9s\u00e9nek a management view-k haszn\u00e1lata. A k\u00f6vetkez\u0151 lek\u00e9rdez\u00e9s visszaadja a hangol\u00e1shoz sz\u00e1momra fontos inf\u00f3kat:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT top 500\r\nOBJECT_NAME(st.objectid) object_name, \r\n\r\nSUBSTRING(ST.text, (QS.statement_start_offset\/2) + 1,\r\n((CASE statement_end_offset \r\nWHEN -1 THEN DATALENGTH(st.text)\r\nELSE QS.statement_end_offset END \r\n- QS.statement_start_offset)\/2) + 1) statement_text,\r\n\r\nqs.last_execution_time, \r\nqs.execution_count, \r\n\r\nqs.total_worker_time, \r\nqs.total_worker_time \/ qs.execution_count agv_worker_time,\r\nqs.last_worker_time,\r\n\r\nqs.total_logical_reads,\r\nqs.total_logical_reads \/ qs.execution_count avg_logical_reads,\r\nqs.last_logical_reads,\r\nqp.query_plan as query_plan_text,\r\nxp.query_plan\r\n--into tempdb.dbo.Plans\r\nFROM sys.dm_exec_query_stats AS qs\r\nCROSS APPLY sys.dm_exec_sql_text(sql_handle) as st\r\nCROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, \r\nqs.statement_start_offset, qs.statement_end_offset) AS qp\r\nCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) xp\r\norder by total_worker_time desc\r\n<\/pre>\n<p>Az utols\u00f3 oszlop az xml plan, csak r\u00e1 kell kattintani, \u00e9s m\u00e1ris l\u00e1tszik grafikusan a terv. Szenz\u00e1ci\u00f3san k\u00e9nyelmes.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Az egyik foly\u00f3 munk\u00e1mban profilerrel szerett\u00fcnk volna sz\u00e9tn\u00e9zni egy szerveren, hogy l\u00e1ssuk, kik a lass\u00fa lek\u00e9rdez\u00e9sek. Eddig ez minden c\u00e9gn\u00e9l z\u00f6kken\u0151mentesen ment,&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,4,21,58],"tags":[],"class_list":["post-834","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-szakmai-elet","category-sql-server-2005","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/834","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=834"}],"version-history":[{"count":1,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/834\/revisions"}],"predecessor-version":[{"id":835,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/834\/revisions\/835"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=834"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=834"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=834"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}