July 22, 2014 at 9:04 am
I have an table with half a million records. A simple SELECT of 500 rows takes very long to finish. I rebuilt the clustered index. fragmentation is close to nil. I see CXPACKET and wait resource = ExchangeEvent. How to tune this?
July 22, 2014 at 9:26 am
Start by ignoring the CX packet wait. CX packet tells you queries are running in parallel. That is all.
Query please, plus table definitions, index definitions and execution plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2014 at 11:42 am
You have to look at the execution plan to understand how the query is being resolved. It may not be using your index at all, or it might just be scanning it. There's nothing else to tell you without the information that Gail has suggested.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 25, 2014 at 5:44 am
SELECT
TABLE1.field1,
TABLE1.field2,
TABLE2.field1,
count(distinct(field4)) as Counts,
(count(distinct(TABLE2.field2)))
FROM TABLE1 LEFT OUTER JOIN TABLE2
on TABLE1.field1 = TABLE2.field1
WHERE
TABLE1.field4 >= 'dd/mm/yyyy'
and TABLE1.field4 <= 'dd/mm/yyyy'
and TABLE1.field1 like 'CloudSA%'
group by TABLE1.field1,
TABLE1.field2,
TABLE2.field1
order by TABLE1.field1 asc
Field1nvarchar100
Field2varchar5
Field3datetime8
Field4nvarchar100
Field5nvarchar100
Field6nvarchar100
Field7datetime8
Field8datetime8
Field9nvarchar100
Field10nvarchar100
Field11nvarchar200
Field12nvarchar100
Field13datetime8
Field14nvarchar200
Table2
Field1varchar50
Field2varchar50
Field3varchar50
Field4varchar50
Field5varchar50
Field6varchar150
Field7varchar50
Field8varchar50
Field9varchar50
Field10nvarchar300
Field11varchar50
Field12varchar50
Field13varchar50
Field14varchar50
Field15varchar50
Field16varchar50
Field17varchar500
Field18varchar50
Field19varchar100
Field20varchar100
Field21varchar100
Field22varchar100
Field23varchar100
Field24varchar100
Field25varchar100
Field26varchar100
Field27varchar100
Field28varchar100
Field29varchar100
Field30varchar100
Field31varchar100
Field32varchar100
Field33varchar100
Field34varchar100
Field35varchar100
Field36varchar100
Field37varchar100
Field38varchar100
Field39varchar50
Field40varchar50
Field41varchar50
Field42varchar50
Field43varchar50
Field44varchar50
Field45varchar100
Estimated plan shows the clustered index on table2 is 93% and is scanning. Clustered index is set on a column. that column is not used in the select query.
July 25, 2014 at 8:44 am
Could you tell us which columns are the clustered indexes on for each table?
Any nonclustered indexes on the table? You'll almost certainly get scans without them.
Is Field4 on Table1 really nvarchar(100)? If so, this in the where clause:
TABLE1.field4 >= 'dd/mm/yyyy'
and TABLE1.field4 <= 'dd/mm/yyyy'
is not going to do what you hope it will.
Cheers
July 25, 2014 at 9:36 am
okay. i will get the index information. meanwhile, running DTA, shows that i need to create another 2 NC index on table2. I also ranmissing index dmc. It also showed same. Should I create the indexes?
July 25, 2014 at 9:49 am
Thanks.
Probably create the indexes, it's really your call though.
A good indicator is if the user_seeks, unique_compiles, avg_user_impact, and avg_total_user_cost are high in the dmv.
Beware just blindly creating them though, especially if they have loads of included columns!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply