Actual Execution Plan here:
https://www.brentozar.com/pastetheplan/?id=H1fgOQTRX
Non Clustered Indices did not seem to change much.
SELECT DISTINCT TOP ?
Object1.Column1 AS Column2, Object1.Column3 AS Column4,
Object2.Column1 AS Column5, Object2.Column3 AS Column6,
Object2.Column7 AS Column8, Object2.Column9 AS Column10,
Object2.Column11 AS Column12,
(
SELECT TOP ? Column13
FROM Object3
WHERE Object3.Column14 = Object4.Column15
AND Object3.Column16 = ?
) AS Column13,
Object4.Column15 AS Column17, Object4.Column18 AS Column19,
Object4.Column16 AS Column20, Object4.Column21 AS Column22,
Object4.Column23 AS Column24, Object4.Column25 AS Column26,
Object4.Column27 AS Column28, Object4.Column29 AS Column30,
Object4.Column31 AS Column32, Object4.Column33 AS Column34,
Object4.Column35 AS Column36, Object4.Column37 AS Column38,
Object4.Column39 AS Column40, Object4.Column41 AS Column42,
Object4.Column43 AS Column44, Object4.Column45 AS Column46,
Object4.Column47 AS Column48, Object4.Column49 AS Column50,
Object4.Column51 AS Column52, Object4.Column53 AS Column54,
Object4.Column55 AS Column56, Object4.Column57 AS Column58
FROM Object4
LEFT JOIN Object5 AS Object1 ON Object4.Column16 = Object1.Column15
AND Object4.Column18 = Object1.Column18
AND Object1.Column59 = ?
LEFT JOIN Object5 AS Object2 ON Object4.Column43 = Object2.Column15
AND Object4.Column18 = Object2.Column18
AND Object2.Column59 = ?
LEFT JOIN Object3 AS Object6 ON Object4.Column15 = Object6.Column14
AND Object4.Column18 = Object6.Column18
AND Object6.Column59 = ?
WHERE Object4.Column18 = ?
and (
Object4.Column16 = ? or Object1.Column60 >= ?
OR Object6.Column16 = ?
OR Object4.Column15 IN (
SELECT Column14
FROM Object3
JOIN Object5 ON Object5.Column15 = Object3.Column16
AND Object5.Column18 = Object3.Column18
WHERE Object5.Column60 >= ?
)
)
and (
Object1.Column61 LIKE ?
OR Object4.Column15 IN (
SELECT Column14
FROM Object3
JOIN Object5 ON Object5.Column15 = Object3.Column16
AND Object5.Column18 = Object3.Column18
WHERE Object5.Column61 LIKE ?
)
)
and Object4.Column23 = ?
and Object4.Column25 NOT IN (?)
and Object4.Column29 >= ?
and Object4.Column29 <= ?
ORDER BY Object4.Column29 asc , Object4.Column25 , Object4.Column18
Table: Object4
Column15=PK (INT)
Column43= FK (PK on Object5 As Object2) INT
Column16= FK (PK on Object5 As Object1) INT
Table: Object3
Column14=FK (PK on Object4) INT
Column16=FK (PK on Object5) INT
Column18=PK (INT)
Table: Object5
Column15=PK INT
Column18= PK INT
CREATE TABLE [dbo].[Object4](
[15] [int] NOT NULL,
[MANDANT_NR] [int] NOT NULL,
[16] [int] NULL,
[IdTerminMaster] [int] NULL,
[IdStatus] [int] NULL,
[IdTerminTyp] [int] NULL,
[IdTerminArt] [int] NULL,
[UhrzeitVon] [datetime] NULL,
[UhrzeitBis] [datetime] NULL,
[IdGanztaegig] [int] NULL,
[IdErinnerung] [int] NULL,
[IdSerientyp] [int] NULL,
[SerienEnde] [datetime] NULL,
[Betreff] [varchar](100) NULL,
[Notiz] [varchar](2000) NULL,
[43] [int] NULL,
[IdAdresse] [int] NULL,
[Ort] [varchar](200) NULL,
[EmpfehlungKunde] [int] NULL,
[EmpfehlungMA] [int] NULL,
[IdGrundVerschoben] [int] NULL,
[IdGrundAbgesagt] [int] NULL,
[USER_ID_NEW] [varchar](50) NULL,
[USER_NR_NEW] [int] NULL,
[TS_NEW] [datetime] NULL,
[USER_ID_CHANGE] [varchar](50) NULL,
[USER_NR_CHANGE] [int] NULL,
[TS_CHANGE] [datetime] NULL,
[DELETED] [tinyint] NULL,
PRIMARY KEY CLUSTERED
(
[15] ASC,
[MANDANT_NR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
________________
CREATE TABLE [dbo].[Object3](
[ID] [int] NOT NULL,
[18] [int] NOT NULL,
[16] [int] NULL,
[14] [int] NULL,
[IdZusage] [int] NULL,
[USER_ID_NEW] [varchar](50) NULL,
[USER_NR_NEW] [int] NULL,
[TS_NEW] [datetime] NULL,
[USER_ID_CHANGE] [varchar](50) NULL,
[USER_NR_CHANGE] [int] NULL,
[TS_CHANGE] [datetime] NULL,
[DELETED] [tinyint] NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC,
[18] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
__________
CREATE TABLE [dbo].[5](
[15] [int] NOT NULL,
[18] [int] NOT NULL,
[IdVerbundtyp] [int] NULL,
[VerbundName] [varchar](100) NULL,
..a lot of stuff [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[15] ASC,
[18] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
______________
Non Clustered:
CREATE NONCLUSTERED INDEX [Index3] ON [dbo].[Object4]
(
[15] ASC,
[MANDANT_NR] ASC,
[16] ASC,
[IdTerminMaster] ASC,
[IdStatus] ASC,
[IdTerminTyp] ASC,
[IdTerminArt] ASC,
[UhrzeitVon] ASC,
[IdGanztaegig] ASC,
[IdSerientyp] ASC,
[IdErinnerung] ASC,
[43] ASC,
[EmpfehlungKunde] ASC,
[EmpfehlungMA] ASC,
[IdAdresse] ASC,
[UhrzeitBis] ASC
)
INCLUDE ( [Betreff],
[IdGrundAbgesagt],
[IdGrundVerschoben],
[Ort],
[SerienEnde],
[TS_CHANGE]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
_______________
CREATE NONCLUSTERED INDEX [Index2] ON [dbo].[Object5]
(
[15] ASC,
[18] ASC,
[DELETED] ASC,
[Mobiltelefon] ASC,
[Name] ASC,
[Vorname] ASC,
[Telefon] ASC,
[KeyVHierarchie] ASC,
[Geburtsdatum] ASC,
[KeyKHierarchie] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
_____________
CREATE UNIQUE NONCLUSTERED INDEX [Index 5] ON [dbo].[Object3]
(
[14] ASC,
[16] ASC,
[18] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
_____
CREATE NONCLUSTERED INDEX [Index4] ON [dbo].[Object5]
(
[KeyVHierarchie] ASC,
[18] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Thank you! As I expect latency from the I/O Subsystem, as you so kindly hinted me to the strange ratio, I created an extended event like so:
CREATE EVENT SESSION [DG] ON SERVER
ADD EVENT sqlos.wait_info(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.sp_statement_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text)),
ADD EVENT sqlserver.sql_statement_completed(SET collect_parameterized_plan_handle=(1),collect_statement=(1)
ACTION(sqlos.cpu_id,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'C:\DG_FPS.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO
What happens when I run my query with that extended Event on the query never finishes. And after 90 seconds or so the MSSQLSERVER Services crashes. …. I really would like to take a closer look, but I do not know why it crashes with that extended event.
Analyzing CXPACKET with dm_os_waiting_tasks gave me:
60 0 2 4062 CXPACKET 60 exchangeEvent id=Port18028ee0400
exchangeEvent id=Pipe1740ad6d900 WaitType=e_waitPipeGetRow nodeId=16
In't that normal?
Best Answer
Your query spends a lot of time waiting. It executes at
MAXDOP 8and work is relatively even distributed between threads, yet the query's elapsed time is over 5X CPU time:<QueryTimeStats ElapsedTime="506530" CpuTime="99188" />On a healthy server I would expect the ratio to be reversed: CPU time should be more like 5X elapsed time. One way to make this query faster is to figure on what resources it is waiting on. You can do that using extended events or taking snapshots of
sys.dm_os_wait_stats.sys.dm_os_wait_statsoffers a global view of the server so it will probably include waits that aren't related to your query. To give a simple example, if you have a lot of IO waits and query performance is unacceptable then you may need to tune your queries to reduce overall IO demands or you may need to improve IO bandwidth available to the server.If you must tune the query, I would start around node 31:
Object5has 949385 rows yet a total of 35906640 rows were read from it.Object3has 65874 rows yet 35906640 index seeks were performed on it. That's just not an efficient plan. It would have been more efficient for the query optimizer to pick a plan that reads all relevant rows once from both tables. Even worse, all of that work amounted to 0 rows being returned. For this particular query you could comment out that part and you'd see the same overall results.It is hard to give precise tuning advice for anonymized plans. For a relatively foolproof method, I would put the results of this subquery into a temp table with a clustered index:
If you join to the temp table you will almost certainly get a more efficient plan. At most, 1 million rows should be read to create the temp table, which is a significant savings over reading 35 million rows and doing 35 million seeks. If you need an alternative fix, there's a cardinality estimate problem for
Object5.Column61 LIKE ?. It is difficult to get perfect cardinality estimates forLIKEfilters, so it may not be practical to fix that.