Slow performance of SP

Question

Slow performance of SP

Abhishek

SSCommitted

Points: 1707
More actions
June 23, 2017 at 2:48 am

#402210

One of my query is performing really badly since yesterday.
When I generate the execution plan , I see that est rows and actual rows have a huge difference . Is that a problem ?
How do I troubleshoot from here . Please guide .Attached

Viewing 7 posts - 1 through 6 (of 6 total)

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

Thom A SSC Guru Points: 99126 More actions · Answer 1

Having the estimated rows being off can be a problem yes, as it'll often mean that not enough resources are allocated to run the statement. This normally then means that the query spills over to tempdb, where things are (in general) slower.

Without a copy of your query and your SQL query plan, there's not a lot people can do to answer your question other than guess. Could you post these?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Abhishek SSCommitted Points: 1707 More actions · Answer 2

select article
into #list
from XYZ.ABC.RT_Main
where tracked_out_datetime<getdate()-90

delete from XYZ.ABC.RT_Main
where article in (select article from #list)

delete from XYZ.ABC.RT_Flags
where article in (select article from #list)

delete from XYZ.ABC.RT_HoldTime
where article in (select article from #list)

delete from XYZ.ABC.RT_WaitTime
where article in (select article from #list)

delete from XYZ.ABC.RT_UnschedTime
where article in (select article from #list)

drop table #list

Abhishek SSCommitted Points: 1707 More actions · Answer 3

yes your idea is correct . this is causing high tempdb consumption

Thom A SSC Guru Points: 99126 More actions · Answer 4

Do you have the query plans. Those are the really important part here, if you're estimates are off.

How often do you run these delete statements? Do you also update your statistics on a regular basis (especially you're deleting a large amount of data)?

Considering, however, how simple the queries are, do you have an index the article columns on each of your tables?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

You need to share more information if you want actual help. You could also anonymize the plan before sharing it. SQL Sentry has that option.
How to Post Performance Problems - SQLServerCentral

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Thom A - Friday, June 23, 2017 4:29 AM
Do you have the query plans. Those are the really important part here, if you're estimates are off.
How often do you run these delete statements? Do you also update your statistics on a regular basis (especially you're deleting a large amount of data)?
Considering, however, how simple the queries are, do you have an index the article columns on each of your tables?

Also need to be aware of FKs. If there are any tables that reference the ones being deleted from, you can get great benefit by indexing those column on those tables that point to these. I'm talking about a night and day change. On one of our larger tables, a single row delete was taking minutes. Once we added indexes to all of the columns in the tables that were referencing the table we were deleting from, the time went so nearly 0.

--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)