Solved

query running too slow

Posted on 2013-06-23
16
388 Views
Last Modified: 2013-07-01
I have 2 enviroments.In one enviroment query runs within 7 seconds ,in another enviroment it takes 4 hours. Both have same memory,cpu core,same partitions.
There is no blockings as well.
memory ultization is 50% and cpu utilization is 60%. What will be a issue?It's nightmare ...

I have rebuilded thee index and updated the stats but no luck.
0
Comment
  • 5
  • 4
  • 2
  • +4
16 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39270058
is volume of data the same?

can we see the query and the table definitions?
(have you compared the table definitions, are they the same)
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 39270089
on environment which takes long time to run has less volume than on the environment. running fast. One column is not present in the environment that is running slow.Anyway that table is not referred in the query. I have attached the table definition in the attachment.

SELECT 
wbi.FranchiseSeasonDeptKey,
wbi.RangeId,
wbi.WayRefNumber,
article.ArticleNumber,
wbi.TotalSeasonSgls,
wbi.TotalLaunchSgls,
brw.PrimRangeCode,
brw.SecRangeCode,
brw.PrimSizeIndex,
brw.SecSizeIndex,
cas.SeasonType,
franchise.StoreNumber,
CASE WHEN method.AnRMethodDesc = 'Allocation' THEN 0
      WHEN method.AnRMethodDesc = 'Fulfilment' THEN 1
     WHEN method.AnRMethodDesc = 'Transition DF' THEN 2 
END,
ISNULL(bd.NumberofWeeksbwDrops,0), 
brw.SglsValue,
1,
wayseason.WayseasonID,
ICBM.fnGetJDADropDate(wayseason.WayseasonID,cas.SeasonType,wbi.FranchiseSeasonDeptKey,1,wbi.WayRefNumber,wbi.RangeId,ISNULL(bd.NumberofWeeksbwDrops,0)),
CAS.DeptNum
FROM
      ICBM.tblContractApprovalStatus cas
INNER JOIN
      ICBM.tblwaybudgetinformation wbi
      ON cas.WayRefNumber = wbi.WayRefNumber    
LEFT JOIN   --[inner join ->left join added by naresh M 24052013 ]
      ICBM.tblBuyingRatioWayDetails brw
      ON brw.WayRefNumber = wbi.WayRefNumber 
      AND brw.FranchiseSeasonDeptKey = wbi.FranchiseSeasonDeptKey
INNER JOIN
      ICBM.tblarticlemaster article
      ON article.WayRefNumber = wbi. WayRefNumber
INNER JOIN
      ICBM.tblFranchiseSeasonDeptMap fsdk
      ON fsdk.FranchiseSeasonDeptKey = wbi.FranchiseSeasonDeptKey 
      AND fsdk.DeptNum = cas.DeptNum
      AND fsdk.SeasonType = cas.SeasonType
INNER JOIN
      ICBM.tblInitialHub inithub
      ON inithub.HubId = wbi.HubId              
INNER JOIN
      ICBM.tblFranchise franchise
      ON franchise.FranchiseId = fsdk.FranchiseId
INNER JOIN
      ICBM.tblway way
      ON way.wayRefNumber = wbi.WayRefNumber
INNER JOIN
      ICBM.tblwayseason wayseason
      ON wayseason.WayRefNumber = wbi.WayRefNumber
      AND wayseason.RangeId = wbi.RangeID
INNER JOIN
      ICBM.tblwaysupplier waysupplier
      ON waysupplier.WAYSEASONID = wayseason.WAYSEASONID 
      AND waysupplier.SERIESNUM = wbi.SUPPLIERSERIESNUM
INNER JOIN
      ICBM.tblBudgetingDepartment bd
      ON bd.FranchiseSeasonDeptKey = wbi.FranchiseSeasonDeptKey
LEFT JOIN
      ICBM.tblAnRMethod method
      ON way.AnRMethodNonOutlet = method.AnRMethodID
      AND method.AnRTypeID = 1      
WHERE
      CONVERT(varchar(10),cas.ApprovedOn,120) = CONVERT(varchar(10),GETDATE(),120)
      AND cas.ApproveStatus = 'Approve'
      AND cas.PassWayQuantityToRP = 1
      AND waysupplier.ContractStatus in ('U','C','L')
      AND wbi.StatusCode = 'IFG_Approve'
      AND inithub.HubName = 'Hemel DC (5542)'   
      --[Commented by NAresh M 23052013 - Neena Req change - INT A deployment]
      --AND wbi.TotalSeasonSgls > 0
      --AND wbi.TotalLaunchSgls > 0
      --AND brw.SglsValue > 0
      --[End]
      AND ISNULL(way.AnRMethodNonOutlet,0) IN (1,2,3)

Open in new window

table-def.xlsx
0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39270101
Have you compared the execution plans in both to see if they're the same ?
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 450 total points
ID: 39270126
thanks for the details. can we assume the indexes are also the same in both environments?

anyway I would probably look at execution plans from both environments (you can post the .sqlplan files here if you like)

Just one quick observation
WHERE
      CONVERT(varchar(10),cas.ApprovedOn,120) = CONVERT(varchar(10),GETDATE(),120)

conversion-to/comparisons-with datetime information to varchar is slower than alternatives returning datetime value. Additionally using a convert function on this field can remove any index on that field from being used. The following removes the convert function from the data, and uses date function on getdate(). This construct will give you the same select outcome:

WHERE
      ( cas.ApprovedOn >= dateadd(day, datediff(day,0, GETDATE()), 0)
      AND cas.ApprovedOn < dateadd(day, datediff(day,0, GETDATE()), 1) )

-- cas.ApprovedOn >= today at 00:00:00, AND
-- cas.ApprovedOn < tomorrow at 00:00:00
0
 
LVL 9

Expert Comment

by:sarabhai
ID: 39270343
Please check the Actual query Execution plan form query run best in environment.
It will help to find where is the issue when comparing both execution plans.
0
 
LVL 1

Expert Comment

by:yechan
ID: 39270990
We run into a similar situation here at work last year.  Turns out that Idera (a database monitoring tool) caused certain queries (not all) to run very slow.  Once we disabled Idera, everything was fine.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 39272392
Worst and good has different query plan.Good is using harsh join,where worst is using nested loop.
I also tried with OPTION (HASH GROUP, FAST 10);
at end of the query but it didn't work out. hOW TO RESOLVE THIS ISSUE?

I have also cleared the proccache and re-run the query.But no hope,it's been using the nested loop join.

Why the execution plan is different in 2 environment.How to achieve to make sure query uses harsh join.I'm attaching the query plan for the review.Kindly advice.
good.txt
worst.txt
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 50 total points
ID: 39276014
From a quick glance through your execution plans, it looks like the row counts vary pretty dramatically - are you statistics up to date in the "slower" database? Can you run the following statement in any databases that have objects referenced by your query?

EXEC sp_updatestats

Open in new window


This will refresh the statistics on all your indexes, and can result in the engine making better decisions, including things like choosing a hash join instead of a nested loop (both have a particular purpose, but hash is generally faster on larger tables when there's sufficient memory available). If your stats are out of date, SQL Server doesn't know how many rows the tables have for particular key values, and it can make less than optimal decisions.

Update the statistics and then share if you're still seeing issues.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 39276403
I have already updated the statistics but didn't work.But just I tried again and checked.It's taking same execution plan? Please help!!!
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 39276477
Tricky one... there are some tables doesn't exist at all,then how does query is executing successfully?

But for the tables which doen't exist has recent update stats on the environment working fine and old stats on the environment running slow.


tblBuyingRatioWayDetails
tblWayBudgetInformation
tblWay
How is this possible ???
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 450 total points
ID: 39276511
if those tables truly do not exist - then the query will not run - it would error
the 'worst' executopn plan also identofies missing indexes on at least one of those tables
            <MissingIndexes>
              <MissingIndexGroup Impact="54.2431">
                <MissingIndex Database="[icbmdb]" Schema="[ICBM]" Table="[tblWayBudgetInformation]">
                  <ColumnGroup Usage="EQUALITY">
                    <Column Name="[StatusCode]" ColumnId="13" />
                  </ColumnGroup>
                  <ColumnGroup Usage="INCLUDE">
                    <Column Name="[FranchiseSeasonDeptKey]" ColumnId="1" />
                    <Column Name="[RangeId]" ColumnId="2" />
                    <Column Name="[WayRefNumber]" ColumnId="3" />
                    <Column Name="[TotalLaunchSgls]" ColumnId="5" />
                    <Column Name="[TotalSeasonSgls]" ColumnId="9" />
                    <Column Name="[HubId]" ColumnId="30" />
                    <Column Name="[SupplierSeriesNum]" ColumnId="31" />
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
              <MissingIndexGroup Impact="82.0165">
                <MissingIndex Database="[icbmdb]" Schema="[ICBM]" Table="[tblWayBudgetInformation]">
                  <ColumnGroup Usage="EQUALITY">
                    <Column Name="[StatusCode]" ColumnId="13" />
                  </ColumnGroup>
                  <ColumnGroup Usage="INCLUDE">
                    <Column Name="[FranchiseSeasonDeptKey]" ColumnId="1" />
                    <Column Name="[RangeId]" ColumnId="2" />
                    <Column Name="[WayRefNumber]" ColumnId="3" />
                    <Column Name="[TotalLaunchSgls]" ColumnId="5" />
                    <Column Name="[TotalSeasonSgls]" ColumnId="9" />
                    <Column Name="[HubId]" ColumnId="30" />
                    <Column Name="[SupplierSeriesNum]" ColumnId="31" />
                  </ColumnGroup>
                </MissingIndex>
              </MissingIndexGroup>
            </MissingIndexes>

Open in new window

0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39276533
Can you follow these steps to generate a "Stats only" copy of your database? It will include all the information necessary for us to test execution plans, without including any of your actual data:

http://support.microsoft.com/kb/914288

SQL generates the best execution plan it can based on the information it knows, but it only knows what the statistics tell it. Also, if you're missing indexes, the execution plans will vary greatly between the two servers because the query engine can't use the same objects.

If you follow the steps above and generate a script for each of your two databases (the "quick" one and the "slow" one) and include the query you're executing, any of us can generate our own execution plan, identify the difference that's causing it, and then make a specific recommendation.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 450 total points
ID: 39276735
I would like to concentrate on this:
" there are some tables doesn't exist at all, "

the query would simply stop with an error if this were true.

Why do you believe those tables don't exist?
Is the query using the correct database/schema?

more than one database/schema is involved in the worst execution plan
<ColumnReference Database="[BMSDB]" Schema="[BMS]" Table="[tblArticleMaster]" Alias="[article]" Column="ArticleNumber" />
<ColumnReference Database="[icbmdb]" Schema="[ICBM]" Table="[tblFranchise]" Alias="[franchise]" Column="StoreNumber" />
<ColumnReference Database="[BMSDB]" Schema="[BMS]" Table="[tblWaySeason]" Alias="[wayseason]" Column="WaySeasonID" />

Open in new window

0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 39277659
You have antivirus installed?
If so, antivirus should have exceptions for not scan *.mdf, *.ndf and *.ldf files.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 39291159
Issue is fixed. I was consfused as there is no object like ICBMDB.tblWaySeason,ICBMDB.tblArticleMaster.
It's because, dev team had made use of synonymns.I initially missed this out.

ANd fixed statistics on BMSDB.Which fixed the issue.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now