3

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 

enter image description here

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?

  • Could you please post relevant table structures? – George.Palacios Nov 29 '18 at 9:13
  • Hi, does that Image help? – Frank P. Nov 29 '18 at 9:17
  • Not without the data types of the columns Frank. and any PK / FK relationships. – George.Palacios Nov 29 '18 at 9:18
  • Give me a second I will try to figure out how to provide that. Manually .. don't know how to export this from SSMS or SentryOne – Frank P. Nov 29 '18 at 9:21
  • 1
    If you right click the table in management studio you can select "script table as > create > to clipboard" and it will copy the table definition to your clipboard. Do the same for the indexes of the tables and you should be able to get full table and index definitions. – Tom V - try topanswers.xyz Nov 29 '18 at 9:40
1

Your query spends a lot of time waiting. It executes at MAXDOP 8 and 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_stats offers 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:

enter image description here

Object5 has 949385 rows yet a total of 35906640 rows were read from it. Object3 has 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:

SELECT Column14 
FROM Object3 
JOIN Object5 ON Object5.Column15 = Object3.Column16
AND Object5.Column18 = Object3.Column18  
WHERE Object5.Column61 LIKE ?

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 for LIKE filters, so it may not be practical to fix that.

| improve this answer | |
  • Ok, I got extended events running and find my query in it. Still unsure what I am looking for exactly. It seems to me that there is plenty of information out there how to set up extended events. But analyze and assess the data gained from extended events.... not so much. Still would love to know why I wait more then spend CPU time... – Frank P. Dec 1 '18 at 21:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.