Execution plans

Question

Execution plans

m.rajesh.uk

Hall of Fame

Points: 3653
More actions
June 5, 2013 at 4:41 am

#274610

Hi All,
one of the application created in .net and using sql in the backend is given to me for identify the problematic queries ,long running queries.
i used the profiler and used tuning template to capture data.
After capturing data i saved that into sql table.
after querying that table i found that 12 queries are taking more than 4000 milli seconds . which we consider as long running queries.
now what to do next.
i copied one query from table and pasted in ssms and try to create execution plan. but i am not getting execution plans. Is there any way that i can get execution plans for those queries.
when i am trying to create execution plans it is showing error
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.PriceGroup'.
The queries taking more than 4000 milli seconds are
select business0_.Id as Id0_, business0_.Code as Code0_, business0_.CountryCode as CountryC3_0_ from dbo.[Business] business0_ order by business0_.Code asc
select laborrate0_.Id as Id15_, laborrate0_.CostRate as CostRate15_, laborrate0_.SellRate as SellRate15_, laborrate0_.CurrencyId as CurrencyId15_ from dbo.[LaborRate] laborrate0_
select labortype0_.Id as Id16_, labortype0_.CountryId as CountryId16_, labortype0_.LaborRateModelId as LaborRat3_16_, labortype0_.Description as Descript4_16_ from dbo.[LaborType] labortype0_ order by labortype0_.Description asc
select risk0_.Id as Id57_, risk0_.Type as Type57_, risk0_.Description as Descript3_57_ from dbo.[Risk] risk0_ order by risk0_.Description asc
select risksurvey0_.Id as Id41_, risksurvey0_.Text as Text41_, risksurvey0_.ParentStepId as ParentSt3_41_, risksurvey0_.Sequence as Sequence41_ from dbo.[RiskSurveyQuestion] risksurvey0_ order by risksurvey0_.ParentStepId asc, risksurvey0_.Sequence asc
select risksurvey0_.Id as Id40_, risksurvey0_.ParentQuestionId as ParentQu2_40_, risksurvey0_.ResultsInStepId as ResultsI3_40_, risksurvey0_.ResultsInRiskId as ResultsI4_40_, risksurvey0_.Text as Text40_, risksurvey0_.Sequence as Sequence40_ from dbo.[RiskSurveyAnswer] risksurvey0_ order by risksurvey0_.ParentQuestionId asc, risksurvey0_.Sequence asc
select countrydis0_.Id as Id5_, countrydis0_.Disc_ID as Disc2_5_, countrydis0_.Name as Name5_, countrydis0_.CountryId as CountryId5_ from dbo.[CountryDiscountType] countrydis0_ order by countrydis0_.Name asc
select pricegroup0_.Id as Id24_, pricegroup0_.ProductGroupCode as ProductG2_24_, pricegroup0_.PriceGroupDesc as PriceGro3_24_, pricegroup0_.PriceFamilyCode as PriceFam4_24_ from dbo.[PriceGroup] pricegroup0_ order by pricegroup0_.ProductGroupCode asc

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

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

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 1

The error message "Msg 208, Level 16, State 1, Line 1

Invalid object name 'dbo.PriceGroup'." is pretty straight forward; it means that the parser is unable to parse the object name.

If you simply run SELECT * FROM dbo.PriceGroup, does it error? It may be that you haven't created the objects, or that you are running under a different database.

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

m.rajesh.uk Hall of Fame Points: 3653 More actions · Answer 2

i was given task identify long running queries and problematic queries.. but in the reults i got the above queries and none of the involve complex code or any joins.. then why they are taking long time to execute..

hvermaak SSChasing Mays Points: 642 More actions · Answer 3

Are you trying to get an execution plan for the whole script block or can you generate it on a per query basis.

The error can't find the object.

The queries are lacking where clauses so table scans are going to be a problem

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

m.rajesh.uk (6/5/2013)
i was given task identify long running queries and problematic queries.. but in the reults i got the above queries and none of the involve complex code or any joins.. then why they are taking long time to execute..

They are all unrestricted SELECTs with an ORDER BY.

They retrieve columns from every row of the table - which will take time for larger tables. If there is no index to support the ORDER BY then you can expect an expensive sort in the plan.

Run the queries against the server/db where they are problematic.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

Are you running the queries in the correct database?

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

m.rajesh.uk Hall of Fame Points: 3653 More actions · Answer 6

Now i got the execution plans .. i have to change the db .. i got it... Thank you all..........