I need help optimizing the following query (returning ~8k rows):

SELECT A.sys_id, 'AppSvrRels' = CAST(SUBSTRING((SELECT (', ' + T.name)
                                FROM (
                                SELECT A.sys_id, AppSvr.name
                                FROM GTS_DataStage.SN.cmdb_ci_appl A
                                LEFT JOIN GTS_DataStage.SN.cmdb_rel_ci X 
                                    ON A.sys_id = X.parent
                                LEFT JOIN GTS_DataStage.SN.cmdb_ci AppSvr 
                                    ON X.child = AppSvr.sys_id 
                                    AND AppSvr.dv_sys_class_name 
                                      IN ('UNIX Server', 'Windows Server', 
                                      'IBM Mainframe', 'Application Server')
                                WHERE A.dv_sys_class_name = 'Application' 
                                 AND A.dv_u_used_for = 'Production' 
                                 AND A.dv_operational_status = 'Deployed') T
                                WHERE A.sys_id = T.sys_id
                                FOR XML PATH('')), 3, 4000) AS NVARCHAR(4000))
FROM GTS_DataStage.SN.cmdb_ci_appl A
WHERE A.dv_sys_class_name = 'Application' AND A.dv_u_used_for = 'Production' 
    AND A.dv_operational_status = 'Deployed'

These tables are on the DataStage and I am not allowed to use indexes. Total rowcounts are: cmdb_ci_appl: ~20k; cmdb_rel_ci: ~1200k; cmdb_ci: ~800k

The following image shows a section of the execution plan (I can upload the sqlplan file if needed but there's not much else to see):


Any help is appreciated.

  • 3
    What does "I am not allowed to use indexes" mean? Did you consider adding the index that the plan recommends adding, right there on the third line? You should always be sure to test that this doesn't adversely affect the DML portions of your workload, but that's the optimization step that seems most obvious to try first. – Aaron Bertrand Oct 18 '12 at 18:11
  • 2
    The next suggestion would be to stop doing this concatenation in SQL Server. Return the data to the presentation tier and let that layer concatenate and change the format of the output. – Aaron Bertrand Oct 18 '12 at 18:13
  • 1
    Not sure why you wouldn't be able to use indexes. It seems to me if the recommended index promises a 90% increase you should probably add it. Also if you are not allowed to use proper indexing you're probably not going to be able to get much performance out of your queries. – Zane Oct 18 '12 at 18:14
  • @Zane just for clarity, that impact number is not in %. Not trying to take away from your point, of course. – Aaron Bertrand Oct 18 '12 at 18:15
  • @AaronBertrand you are absolutely correct sir. I find I type it that way often a habit I am trying to break. – Zane Oct 18 '12 at 18:17

Show the DBA those Eager Index Spools in the query plan. SQL Server is so desperate for supporting indexes here, it is creating its own temporary ones on every execution!

There is always more to see in the sqlplan file, so please do upload it. A statistics-only copy of the tables in question (or table and index definitions at the very least) would also be useful if you want more than general advice).

This answer was originally a comment on your question. I'll update it if a query plan or DDL is provided.

| improve this answer | |

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