VYJR-COWI
commented
1 year ago 557058:622a62a3-9bf2-41ae-8c7b-ca3a891081a8:
Det er svært at svare på hvor ofte de 8 SP er blevet brugt, samt det enkelte View. Det afhænger af planer i Cachen. Hvis Plan Cache er ustabil, så er information om brug af SP også mangelfuld.
I Region Sjælland tyder det på at jar_HentMatrikelEjere_sp og jar_ngis_HentMatrikelEjerInfo_sp ikke bliver brugt.
View v_gisflader_hint bruges i minimum wms tema Alle_godkendte og Alle_godkendte_filter, som anvendes ofte.
/* Hvor ofte køres SP så
https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-procedure-stats-transact-sql?view=sql-server-ver16
*/
SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'SP',
d.cached_time, d.last_execution_time, d.total_elapsed_time,
d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_procedure_stats AS d
WHERE OBJECT_NAME(object_id, database_id) IN
(SELECT o.Name
FROM sys.sql_modules m INNER JOIN sys.objects o
ON o.object_id = m.object_id
WHERE m.definition LIKE '%WITH%(Index%' )
ORDER BY SP ASC;
GO
Cached planer som anvender View v_gisflader_hint (tager lang tid at køre):
declare @search_string nvarchar(255)
set @search_string = 'v_gisflader_hint'
SELECT DB_NAME(t.[dbid]) AS [Database Name],
t.text AS [Query Text],
qs.total_worker_time AS [Total Worker Time],
qs.total_worker_time/qs.execution_count AS [Avg Worker Time],
qs.max_worker_time AS [Max Worker Time],
qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time],
qs.max_elapsed_time AS [Max Elapsed Time],
qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
qs.max_logical_reads AS [Max Logical Reads],
qs.execution_count AS [Execution Count],
qs.creation_time AS [Creation Time],
qp.query_plan AS [Query Plan]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE ('%' + @search_string + '%')
AND t.[dbid] = DB_ID()
ORDER BY qs.total_worker_time DESC
OPTION (RECOMPILE);
GO
JAR-1693
Der er 9 objekter i JAR db som benytter Index Hints. Se vedhæftet sql.
Dette er skadeligt for SQL Server performance, da vi altid tvinger SQL Server til at bruge en bestemt Execution Plan, selvom SQL Server kunne komme op med en bedre plan for eksekvering af SQL Statement.
https://www.brentozar.com/blitz/query-hints-optimizer/, https://www.brentozar.com/archive/2010/03/2-features-of-sql-server-you-should-avoid/, https://www.brentozar.com/archive/2013/10/index-hints-helpful-or-harmful/.
Index Hints bør kun anvendes til at teste indexes og aldrig i produktionen.
Som eksempel, så anvender v_gisflader_hint Index Hint på IX_e_gis_spatial, selvom der ingen Spatial analyse sker i viewet. Dvs. vi har et View som performer ekstremt langsomt. Performance kan testes med:
WITH (INDEX(IX_e_gis_spatial),nolock) --Med Index Hint
WITH (nolock) --WITH (INDEX(IX_e_gis_spatial),nolock) --Uden Index Hint
Et andet eksempel er på jar_ngis_HentMatrikelInfo_sp hvor Index Hint anvendes på IX_e_gis_matrikel_spatial.
Her kan WITH(INDEX(IX_e_gis_matrikel_spatial)) udkomenteres og der testes om SQL Server selv vil bruge indekset. Det er præcis hvad der sker:
Cowi skal derfor fjerne Index Hints fra alle 9 objekter, samt teste at performance er mindst lige så god som tidligere. Hvis man forventer, at Spatial Index anvendes, så undersøges det om disse indexes så også bruges, uden Index Hints.
Da Syd har SQL Server 2014, så skal dette testes med Compatibility Mode 2014.
Cowi beskriver hvad de har gjort og hvordan, samt performance målinger før og efter.
Såfremt fjernelse af Index Hints ikke har en negativ Impact på performance, så implementeres disse ændringer i en release.
Originally reported by , imported from: Oprydning i Index Hints i JAR db