Zine.net online

Witaj na Zine.net online Zaloguj się | Rejestracja | Pomoc
w Szukaj

dev2dev

Probelm z widokiem DMV

Widoki DMV sa bardzo dobrym narzędziem do analizowania bazy danych. Ale są sytuacje gdy zawodzą.

Na początek utworzymy testową baze danych, testową tabelę oraz testową procedurę:

   1: IF EXISTS (SELECT * FROM sys.databases WHERE name = N'Test')
   2:   DROP DATABASE Test
   3: GO
   4:  
   5: CREATE DATABASE Test
   6: GO
   7:  
   8: USE Test
   9: GO
  10:  
  11: CREATE TABLE test (col int)
  12: GO
  13:  
  14: CREATE PROCEDURE test_dmv
  15:     @par varchar(30)
  16: AS
  17: BEGIN
  18:     DECLARE @sql varchar(4000)
  19:  
  20:     SET @sql = 'SELECT * FROM '+QUOTENAME(@par)
  21:     EXEC (@sql)
  22:  
  23: end

Przy pomocy widoku DMV będziemy chcieli śledzić jak często procedura testowa jest wywoływana. Wywoływać będziemy tę procedurę z parametrem będącym nazwą tabeli testowej.

   1: EXEC test_dmv 'test'

Uruchamiamy klika razy ten kod po czym chcemy sprawdzić czy stosowny widok DMV pokaże nam ile razy procedura została wykonana. W tyl celu potrzebny będzie kod:

   1: SELECT execution_count, text
   2: FROM sys.dm_exec_query_stats
   3: cross apply sys.dm_exec_sql_text(sql_handle)
   4: WHERE text like '%PROCEDURE test_dmv%' 

Spodziewając się, że pokaże ilość wywołań procedury test_dmv. Ale nic z tych rzeczy. Pokaże mi tylko wywołanie tego zapytania z widokami DMV. Gdzie sie podziało wywołanie procedury? Dla pewności zrobiłem plan zapytania:

   1: <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" 
   2:              Version="1.1" Build="10.0.1600.22">
   3:   <BatchSequence>
   4:     <Batch>
   5:       <Statements>
   6:         <StmtSimple StatementText="&#xD;&#xA;EXEC test_dmv 'test'" 
   7:                     StatementId="1" 
   8:                     StatementCompId="1" StatementType="EXECUTE PROC">
   9:           <StoredProc ProcName="test_dmv">
  10:             <Statements>
  11:               <StmtSimple StatementText="CREATE PROCEDURE test_dmv&#xD;&#xA;
  12:                 @par varchar(30)&#xD;&#xA;AS&#xD;&#xA;
  13:                 BEGIN&#xD;&#xA;    
  14:                 DECLARE @sql nvarchar(4000)&#xD;&#xA;&#xD;&#xA;    
  15:                 SET @sql = N'SELECT * FROM '+QUOTENAME(@par)&#xD;&#xA;" 
  16:                           StatementId="2" 
  17:                           StatementCompId="3" 
  18:                           StatementType="ASSIGN" />
  19:               <StmtSimple StatementText="    exec sp_executesql @sql&#xD;&#xA;&#xD;" 
  20:                           StatementId="3" 
  21:                           StatementCompId="4" 
  22:                           StatementType="EXECUTE PROC" />
  23:             </Statements>
  24:           </StoredProc>
  25:         </StmtSimple>
  26:       </Statements>
  27:     </Batch>
  28:   </BatchSequence>
  29: </ShowPlanXML>

Plan zapytania wyrażnie pokazuje wywołanie procedury test_dmv. Jeżeli teraz dokonamy zmiany procedury zamieniając dynamic SQL na jawne zapytanie (funkcjonalnie dokładnie takie samo).

   1: ALTER PROCEDURE test_dmv
   2:     @par varchar(30)
   3: AS
   4: BEGIN
   5:  
   6:     SELECT * FROM [test]
   7:  
   8: end

To widok DMV będzie pokazywał prawidłowo ilość wywołań procedury. Nie pomaga również bezpieczny dynamic SQL przy pomocy sp_executesql:

   1: ALTER PROCEDURE test_dmv
   2:     @par varchar(30)
   3: AS
   4: BEGIN
   5:     DECLARE @sql nvarchar(4000)
   6:  
   7:     SET @sql = N'SELECT * FROM '+QUOTENAME(@par)
   8:     exec sp_executesql @sql
   9:  
  10: end

Nie pomaga również dodanie “sztucznych” instrukcji przypisania w kodzie procedury

   1: DECLARE @temp int = 0
   2:  
   3: SET @temp += 1

Dobrze to wiedzieć przystępując do korzystania z widoków DMV do oceny ilości wywołań procedur składowanych.
Opublikowane 11 listopada 2008 23:02 przez marekpow
Filed under: , , ,

Komentarze:

 

awake said:

czyli... oszukali nas :)

listopada 12, 2008 10:46
 

marekpow said:

Raczej ująłbym to w znamy Ci zdaniu: "programiści programistom zgotowali ten los" ;-)

listopada 15, 2008 13:55
Komentarze anonimowe wyłączone
W oparciu o Community Server (Personal Edition), Telligent Systems