Performance Issue when executing my query

Question

Post reply

Performance Issue when executing my query

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

syed.hajamohideen SSC Journeyman Points: 79 More actions · Answer 1

syed.hajamohideen

SSC Journeyman

Points: 79

July 2, 2012 at 3:52 am

#1507665

Development also giving the same issue.

Thanks,

Syed.

Gullimeel Hall of Fame Points: 3871 More actions · Answer 2

There is some issue with my mahcine so I can not open plans.Hopefully, someone will reply based on your plan

I have used NON Clustered index on Col_name1 and date_time columns.

Is it a single nonclustered index on these columns or do you have two diff indexes one on each column?

GulliMeel

Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]

syed.hajamohideen SSC Journeyman Points: 79 More actions · Answer 3

syed.hajamohideen

SSC Journeyman

Points: 79

July 2, 2012 at 4:50 am

#1507675

Two different indexes.

Thanks,

Syed

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Please post the plan as a .sqlplan file, not excel. Excel's fine for SQL 2000 where there was no other way to save plans.

Please post the definitions of all indexes on that table (CREATE INDEX statements, not screenshots that only show the index name)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

People, there is no need to look any further than the fact that the OP is trying to return 35 MILLION RECORDS to the calling entity (presumably SSMS).

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Gullimeel Hall of Fame Points: 3871 More actions · Answer 6

People, there is no need to look any further than the fact that the OP is trying to return 35 MILLION RECORDS to the calling entity (presumably SSMS).

That too on a machine in India ( most probably, we indian uses CRORE for 10 million). Thus 9 minutes might be just transfer and displaying time in SSMS.

GulliMeel

Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]

syed.hajamohideen SSC Journeyman Points: 79 More actions · Answer 7

syed.hajamohideen

SSC Journeyman

Points: 79

July 3, 2012 at 4:06 am

#1508117

Please find index definitions.

Thanks,

Syed.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 8

TheSQLGuru (7/2/2012)
People, there is no need to look any further than the fact that the OP is trying to return 35 MILLION RECORDS to the calling entity (presumably SSMS).

+1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Execution plan as a .sqlplan file. Please.

Also why are you returning 35 million rows to the client? No one will ever read that volume of data

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

syed.hajamohideen SSC Journeyman Points: 79 More actions · Answer 10

Yes I too agree, client cannot view that much record at a slot.

But the requirement which i got is like that.

Thanks,

Syed.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Then honestly you should take that requirement back to the person who gave it to you and get some clarity on why exactly they find it essential to send 35 million rows across the network. Retrieving and sending 35 million rows will never be fast.

p.s. I still don't see an exec plan in XML format (.sqlplan file).

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

syed.hajamohideen SSC Journeyman Points: 79 More actions · Answer 12

Please find the attached Execution Plan in excel.

Thanks,

Syed

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

I give up.

p.s. You've got missing indexes. Don't know how much one would help because I'm really not in the mood for figuring out the text version of a 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gullimeel Hall of Fame Points: 3871 More actions · Answer 14

You mentioned that you are getting 35 million rows.It is just returning 60K rows as i mentioned before your table has around 4K pages ( i could see the fragment count etc) did not see the page count so it could be differeent .You can not store 35 million rows in these many pages.

Your table is heap table.You still did not post statistics IO,time which might give better idea on how big is heap because of forwarded rows and deleted rows. If the heap table has just 180K rows then it should not take 9 minutes even though you do not have proper index.

As Gail mentioned, always post the .sqlplan that is graphical plan from SSMS.People find it much easier to look and find the issue with graphical plan rather than xml or text plan.Reading them is very painful..

GulliMeel

Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]

syed.hajamohideen SSC Journeyman Points: 79 More actions · Answer 15

Hi,

Please find the attached execute plan in .sqlplan format.

Please help me in this regard.

Note : Just now i got the file. Sorry for delayed response.

Thanks,

Syed.