pirmdiena, 2012. gada 6. augusts

Resursu prasīgākie vaicājumi, procedūras, trigeri

Ātrdarbības problēmu cēloņi var būt dažādi. Risinājumi arī (šo tēmu cītīgi cenšos izvērst sadaļā "Optimizācija"). Šajā rakstā plašāk izvērsta viena no problēmas konstatēšanas pieejām- noskaidrot, kuri no vaicājumiem/procedūrām ir visvairāk resursu prasīgi.

Mazliet pafilozofējot par tēmu
Ātrdarbības problēmas var rasties ļoti dažādu iemeslu dēļ un arī to risinājumi ir ļoti dažādi. Vadoties no pieredzes, tiek veidotas pieejas problēmu konstatēšanai. Nav viena ideālā veida kā to darīt. Piemēram, datu bāzes programmētājs visdrīzāk sāks skatīties potenciālos vaicājumus, kas grauj ātrdarbību. Administrators ķersies klāt pie Perfomance monitor un skatīsies kas trūkst- IO, RAM, CPU, tīkls- un pēc tam jau dziļāk, kas tam par iemeslu.

Konstatēt, ka kāds no resursiem trūkst, ir samērā vienkārši. Un ja ir iespējams šo resursu vienkārši papildināt (piemēram, piešķirt papildus RAM), tad tas ir gana labs un ātrs risinājums. Praksē gan resursi ir ierobežoti un papildus dzelžus piešķir tikai tad, ja ātrdarbības problēmas nevar vai ir pārāk dārgi risināt optimizējot vaicājumus. Bet kurus vaicājumus vajag optimizēt? Par šo jautājumu domājot arī ir tapis šis raksts.

Tas ko mēģinu paveikt- izveidot priekš sevis lietojumu modeli, ar kā palīdzību var ātri atrast problēmu cēloņus un kuru izmantojot var pēc iespējas skaidrāk saprast konkrētus veicamos soļus, lai datu bāzes serveris sāktu "lidot".

Protams, šī ideja nemaz nav unikāla. Šādā virzienā domā daudzi un daudziem ir jau gatavi ļoti labi risinājumi. Piemēram, Glenn Berry's SQL Server Performance emuārā regulāri tiek publicēti diagnostikas vaicājumi. Arī Brent Ozar PLF emuārā ir lejupielādējama procedūra, kas palīdz atrast daudzas potenciālās problēmas un papildus tam dod norādes kur iegūt vairāk informācijas. 

Abas lietas es mēdzu izmantot (sevišķi Glenn Berry vaicājumus) un arī jums iesaku vismaz pārskriet pāri. Šajā rakstā vaicājumi, kurus esmu izveidojis sev. Daudz kas aizkomentēts. Atkomentējot daļas, rezultātus var skatīt pilnīgi citā griezumā.

Vaicājumi
Ņemot vērā to, ka pirmie divi vaicājumi izmanto jaunus DMV, tie nebūs izmantojami, ja strādā ar vecāku versiju par SQL Server 2008! Nepieciešamas VIEW SERVER STATE tiesības.
/**************** Procedūras **********************/
-- Strādā sākot no SQL Server 2008, DB ietvaros
SELECT
    OBJECT_SCHEMA_NAME(OBJECT_ID) + '.' + OBJECT_NAME(object_id) AS object_name,
    execution_count,
    Cast(total_elapsed_time * 0.001 * 0.001 as bigint) total_elapsed_time_sec,
    -- cik ilgi procedūra izpildās (CPU laiks + gaidīšanas laiks):
    Cast(total_elapsed_time/execution_count * 0.001 as decimal(18,2)) AS avg_elapsed_time_ms,
        --Cast(last_elapsed_time * 0.001 as decimal(18,2)) last_elapsed_time,
        --Cast(min_elapsed_time * 0.001 as decimal(18,2)) min_elapsed_time_ms,
        --Cast(max_elapsed_time * 0.001 as decimal(18,2)) max_elapsed_time_ms,
    -- cik ilgi strādā:
    Cast(total_worker_time/execution_count * 0.001 as decimal(18,2)) avg_worker_time_ms,
        --Cast(last_worker_time * 0.001 as decimal(18,2)) last_worker_time,
        --Cast(min_worker_time * 0.001 as decimal(18,2)) min_worker_time_ms,
        --Cast(max_worker_time * 0.001 as decimal(18,2)) max_worker_time_ms,
    -- cik ilgi gaida (jāņem vērā, ka paralēliem plāniem te var būt brīnumi, jo laiks ir pa visiem kodoliem!):
    Cast((total_elapsed_time-total_worker_time)/execution_count * 0.001 as decimal(18,2)) avg_wait_time_ms,
        --Cast((last_elapsed_time-last_worker_time)/execution_count * 0.001 as decimal(18,2)) last_wait_time_ms,
    --Lasīšana (loģiskā- no operatīvās)
    total_logical_reads/execution_count avg_logical_reads,
        --last_logical_reads,
        --min_logical_reads,
        --max_logical_reads,
    -- Rakstīšana (loģiskā- fiziskā notiek backgroundā)
    total_logical_writes/execution_count avg_logical_writes,
        --last_logical_writes,
        --min_logical_writes,
        --max_logical_writes,
    --Lasīšana (fiziskā- no diska)
    total_physical_reads/execution_count AS avg_physical_reads,
    cached_time, last_execution_time,
    -- sql teksts:
    Cast('<?SQL ' + st.text + ' ?>' as xml) sql_text,
    -- izpildes plāns (klikšķinam virsū, lai grafiski parādītos)
    pl.query_plan
FROM sys.dm_exec_procedure_stats ps with (NOLOCK)
    Cross Apply sys.dm_exec_sql_text(ps.sql_handle) st
    Cross Apply sys.dm_exec_query_plan(ps.plan_handle) pl
WHERE
    database_id = DB_ID()
    -- ja vēlamies pievērst uzmanību pēdējā laika izmaiņām, atkomentējam abas rindas:
    --AND DATEADD(HOUR, 8, last_execution_time) > getdate()
-- laista pēdējās 8H
    --AND DATEDIFF(HOUR, cached_time, GETDATE()) < 8 -- izpildes plāns mainījies pēdējo 8H laikā
ORDER BY
    -- Atkomentē vajadzīgo, aizkomentē lieko:
    --total_elapsed_time DESC -- kopā visvairāk pildijies (gaidīšana + CPU)
    total_worker_time DESC -- kopā visvairāk pildijies (CPU)
    --avg_logical_reads DESC -- vidējais izpildes laiks vislielākais
    --avg_elapsed_time_ms DESC -- vidējais CPU laiks vislielākais
    --ps.execution_count DESC -- izpildīts reizes
    --avg_wait_time_ms DESC -- vidējais gaidīšanas laiks
OPTION (RECOMPILE);


/**************** TRIGERI **********************/
-- Strādā sākot no SQL Server 2008, DB ietvaros
SELECT
    OBJECT_SCHEMA_NAME(OBJECT_ID) + '.' + OBJECT_NAME(object_id) AS object_name,
    execution_count,
    Cast(total_elapsed_time * 0.001 * 0.001 as bigint) total_elapsed_time_sec,
    -- cik ilgi procedūra izpildās (CPU laiks + gaidīšanas laiks):
    Cast(total_elapsed_time/execution_count * 0.001 as decimal(18,2)) AS avg_elapsed_time_ms,
        --Cast(last_elapsed_time * 0.001 as decimal(18,2)) last_elapsed_time,
        --Cast(min_elapsed_time * 0.001 as decimal(18,2)) min_elapsed_time_ms,
        --Cast(max_elapsed_time * 0.001 as decimal(18,2)) max_elapsed_time_ms,
    -- cik ilgi strādā:
    Cast(total_worker_time/execution_count * 0.001 as decimal(18,2)) avg_worker_time_ms,
        --Cast(last_worker_time * 0.001 as decimal(18,2)) last_worker_time,
        --Cast(min_worker_time * 0.001 as decimal(18,2)) min_worker_time_ms,
        --Cast(max_worker_time * 0.001 as decimal(18,2)) max_worker_time_ms,
    -- cik ilgi gaida (jāņem vērā, ka paralēliem plāniem te var būt brīnumi, jo laiks ir pa visiem kodoliem!):
    Cast((total_elapsed_time-total_worker_time)/execution_count * 0.001 as decimal(18,2)) avg_wait_time_ms,
        --Cast((last_elapsed_time-last_worker_time)/execution_count * 0.001 as decimal(18,2)) last_wait_time_ms,
    --Lasīšana (loģiskā- no operatīvās)
    total_logical_reads/execution_count avg_logical_reads,
        --last_logical_reads,
        --min_logical_reads,
        --max_logical_reads,
    -- Rakstīšana (loģiskā- fiziskā notiek backgroundā)
    total_logical_writes/execution_count avg_logical_writes,
        --last_logical_writes,
        --min_logical_writes,
        --max_logical_writes,
    --Lasīšana (fiziskā- no diska)
    total_physical_reads/execution_count AS avg_physical_reads,
    cached_time, last_execution_time,
    -- sql teksts:
    Cast('<?SQL ' + st.text + ' ?>' as xml) sql_text,
    -- izpildes plāns (klikšķinam virsū, lai grafiski parādītos)
    pl.query_plan
FROM sys.dm_exec_trigger_stats ps with (NOLOCK)
    Cross Apply sys.dm_exec_sql_text(ps.sql_handle) st
    Cross Apply sys.dm_exec_query_plan(ps.plan_handle) pl
WHERE
    database_id = DB_ID()
    -- ja vēlamies pievērst uzmanību pēdējā laika izmaiņām, atkomentējam abas rindas:
    --AND DATEADD(HOUR, 8, last_execution_time) > getdate() -- laista pēdējās 8H
    --AND DATEDIFF(HOUR, cached_time, GETDATE()) < 8 -- izpildes plāns mainījies pēdējo 8H laikā
ORDER BY
    -- Atkomentē vajadzīgo, aizkomentē lieko:
    --total_elapsed_time DESC -- kopā visvairāk pildijies (gaidīšana + CPU)
    total_worker_time DESC -- kopā visvairāk pildijies (CPU)
    --avg_logical_reads DESC -- vidējais izpildes laiks vislielākais
    --avg_elapsed_time_ms DESC -- vidējais CPU laiks vislielākais
    --ps.execution_count DESC -- izpildīts reizes
    --avg_wait_time_ms DESC -- vidējais gaidīšanas laiks
OPTION (RECOMPILE);


/**************** VAICĀJUMI **********************/
-- Strādā sākot no SQL Server 2005, SERVERA ietvaros
SELECT-- top (300)
    OBJECT_NAME(st.objectid) AS object_name, -- šeit neliku shēmas, jo 2005 nav attiecīgās f-jas (lai īsāk)
    execution_count,
    Cast(total_elapsed_time * 0.001 * 0.001 as bigint) total_elapsed_time_sec,
    -- cik ilgi procedūra izpildās (CPU laiks + gaidīšanas laiks):
    Cast(total_elapsed_time/execution_count * 0.001 as decimal(18,2)) AS avg_elapsed_time_ms,
        --Cast(last_elapsed_time * 0.001 as decimal(18,2)) last_elapsed_time,
        --Cast(min_elapsed_time * 0.001 as decimal(18,2)) min_elapsed_time_ms,
        --Cast(max_elapsed_time * 0.001 as decimal(18,2)) max_elapsed_time_ms,
    -- cik ilgi strādā:
    Cast(total_worker_time/execution_count * 0.001 as decimal(18,2)) avg_worker_time_ms,
        --Cast(last_worker_time * 0.001 as decimal(18,2)) last_worker_time,
        --Cast(min_worker_time * 0.001 as decimal(18,2)) min_worker_time_ms,
        --Cast(max_worker_time * 0.001 as decimal(18,2)) max_worker_time_ms,
    -- cik ilgi gaida (jāņem vērā, ka paralēliem plāniem te var būt brīnumi, jo laiks ir pa visiem kodoliem!):
    Cast((total_elapsed_time-total_worker_time)/execution_count * 0.001 as decimal(18,2)) avg_wait_time_ms,
        --Cast((last_elapsed_time-last_worker_time)/execution_count * 0.001 as decimal(18,2)) last_wait_time_ms,
    --Lasīšana (loģiskā- no operatīvās)
    total_logical_reads/execution_count avg_logical_reads,
        --last_logical_reads,
        --min_logical_reads,
        --max_logical_reads,
    total_logical_writes/execution_count avg_logical_writes,
        --last_logical_writes,
        --min_logical_writes,
        --max_logical_writes,
    --Lasīšana (fiziskā- no diska)
    total_physical_reads/execution_count AS avg_physical_reads,
    -- Atgriezto rindu skaits (TIKAI no SQL 2008).
    --total_rows/execution_count AS avg_total_rows,
        --last_rows,
        --min_rows,
        --max_rows,
    -- CLR laiks:
    total_clr_time,
        --last_clr_time,
        --min_clr_time,
        --max_clr_time,
    creation_time, last_execution_time,
    -- sql teksts:
    Cast('<?SQL ' +
     SUBSTRING(st.text, (ps.statement_start_offset/2) + 1,
    ((CASE ps.statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE ps.statement_end_offset END
            - ps.statement_start_offset)/2) + 1) + ' ?>' as xml) query_text,
     Cast('<?SQL ' + st.text + ' ?>' as xml) full_text,
    -- izpildes plāns (klikšķinam virsū, lai grafiski parādītos)
    pl.query_plan
FROM sys.dm_exec_query_stats ps with (NOLOCK)
    Cross Apply sys.dm_exec_sql_text(ps.sql_handle) st
    Cross Apply sys.dm_exec_query_plan(ps.plan_handle) pl
--WHERE --st.dbid = DB_ID()
    -- ja vēlamies pievērst uzmanību pēdējā laika izmaiņām, atkomentējam abas rindas:
    --AND DATEADD(HOUR, 8, last_execution_time) > getdate() -- laista pēdējās 8H
    --AND DATEDIFF(HOUR, creation_time, GETDATE()) < 8 -- izpildes plāns mainījies pēdējo 8H laikā
ORDER BY
    -- Atkomentē vajadzīgo, aizkomentē lieko:
    --total_elapsed_time DESC -- kopā visvairāk pildijies (gaidīšana + CPU)
    total_worker_time DESC -- kopā visvairāk pildijies (CPU)
    --avg_logical_reads DESC -- vidējais izpildes laiks vislielākais
    --avg_elapsed_time_ms DESC -- vidējais CPU laiks vislielākais
    --ps.execution_count DESC -- izpildīts reizes
    --avg_wait_time_ms DESC -- vidējais gaidīšanas laiks
OPTION (RECOMPILE);

Vaicājumu pielāgošana (idejas)
Sākot no SQL Server Management Studio 2005 ir pieejami datu bāzes pārskati (vienu tādu arī uztaisīju pats- SSMS: Datu bāzes tabulu pārskats). SSMS 2005 ir pārskats, kas attēlo procedūru un trigeru izpildes laiku.

Pārskatu var iegūt, SSMS nospiežot labo pogu uz datu bāzes, izvēloties Reports -> Standart Reports -> Object Execution Statistics. Rezultātā tiek iegūts visai glīts, bet grūti lietojams pārskats. Tajā parādītās "smagākās" procedūras un trigeri (SQL Server 2008 ir pieejams jaunie DMV skati sys.dm_exec_procedure_stats un sys.dm_exec_trigger_stats, bet SQL Server 2005 tādu vēl nav).

Pielāgojot trešo vaicājumu (vienīgajā, kas iet uz SQL Server 2005) var iegūt tos pašus datus, kas redzami pārskatā:
SELECT
    OBJECT_NAME(st.objectid) AS object_name, -- šeit neliku shēmas, jo 2005 nav attiecīgās f-jas (lai īsāk)
    execution_count,
    Cast(total_elapsed_time * 0.001 * 0.001 as bigint) total_elapsed_time_sec,
    -- cik ilgi procedūra izpildās (CPU laiks + gaidīšanas laiks):
    Cast(total_elapsed_time/execution_count * 0.001 as decimal(18,2)) AS avg_elapsed_time_ms,
    Cast(total_worker_time/execution_count * 0.001 as decimal(18,2)) avg_worker_time_ms,
    Cast((total_elapsed_time-total_worker_time)/execution_count * 0.001 as decimal(18,2)) avg_wait_time_ms,
    total_logical_reads/execution_count avg_logical_reads,
    total_logical_writes/execution_count avg_logical_writes,
    total_physical_reads/execution_count AS avg_physical_reads,
    total_clr_time,
    creation_time, last_execution_time,
    -- sql teksts:
    Cast('<?SQL ' +
     SUBSTRING(st.text, (ps.statement_start_offset/2) + 1,
    ((CASE ps.statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE ps.statement_end_offset END
            - ps.statement_start_offset)/2) + 1) + ' ?>' as xml) query_text,
     Cast('<?SQL ' + st.text + ' ?>' as xml) full_text,
    -- izpildes plāns (klikšķinam virsū, lai grafiski parādītos)
    pl.query_plan
FROM sys.dm_exec_query_stats ps with (NOLOCK)
    Cross Apply sys.dm_exec_sql_text(ps.sql_handle) st
    Cross Apply sys.dm_exec_query_plan(ps.plan_handle) pl
WHERE st.dbid = DB_ID() AND Not OBJECT_NAME(st.objectid) Is Null
ORDER BY OBJECT_NAME(st.objectid)
OPTION (RECOMPILE);
Nākamajā vaicājumā vidējie procedūru izpildīšanās laiki (SQL Server 2008 un jaunākām versijām) sadalot tos pēc shēmas (ja izmanto vienu shēmu, šis nebūs diezko interesants):
-- Strādā sākot no SQL Server 2008, DB ietvaros
SELECT
    OBJECT_SCHEMA_NAME(OBJECT_ID) schema_name,
    SUM(execution_count) execution_count,
    Cast(SUM(total_elapsed_time) * 0.001 * 0.001 as bigint) total_elapsed_time_sec,
    Cast(SUM(total_elapsed_time)/SUM(execution_count) * 0.001 as decimal(18,2)) AS avg_elapsed_time_ms,
    Cast(SUM(total_worker_time)/SUM(execution_count) * 0.001 as decimal(18,2)) avg_worker_time_ms,
    SUM(total_logical_reads)/SUM(execution_count) avg_logical_reads,
    SUM(total_logical_writes)/SUM(execution_count) avg_logical_writes,
    SUM(total_physical_reads)/SUM(execution_count) AS avg_physical_reads
FROM sys.dm_exec_procedure_stats ps with (NOLOCK)
WHERE
    database_id = DB_ID()
GROUP BY OBJECT_SCHEMA_NAME(OBJECT_ID)
ORDER BY schema_name
OPTION (RECOMPILE);

Nav komentāru:

Ierakstīt komentāru