Non-Clustered Indexes / CXPACKET Wait

Question

Environment: SQL Server 2012 on a VM with 1 CPU and 4 cores.

Experience: As a DBA--> New
MAXDOP set to default of 0

I have a large table with over 100 million records and am trying to improve Query performance. I was running into large amount of CXPACKET wait time, as in as long as the query was running. So I was thinking to create some non-clustered indexes or a filtered index. The filtered index did not provide any help.

My question is whats the best approach to improve performance here? I was going to create 4 non-clustered indexes (Those that are commonly joined on).

As you can see I am new here, so I am probably leaving out information that may be needed.

I suggest you ask the question in DBA.SE with more data on the query you are using,execution plan any research done so far — TheGameiswar, Jul 13 '16 at 14:25

score 0 · Accepted Answer · answered Jul 13 '16 at 14:36

0

I would look closer at the execution plans and look specifically for the most heavily used SQL or the most expensive SQL. Make sure the SQL is doing what you expect it to do, and make sure bad plans are not being used.

answered Jul 13 '16 at 14:36

Jason B.

315
1
12

Thanks so much. Looking at the execution plan provided an Index that I can use. I will test that out as this query is a common query against this table. I feel like something so useful as the Execution Plan, I am not sure why I didn't think to look here. Thanks for the quick response and shedding light on this feature. – Brandon Jul 13 '16 at 14:50

Non-Clustered Indexes / CXPACKET Wait

1 Answers1