| Description |
{color:#ff0000}NOTE: RM-2531 should be completed before this one.{color}
# I ran the SQL Profiler on MCP and noticed that the uspRKDPRInvDailyPositionDetail proc is using the most CPU and is has a very high Duration value:
!pastedImage_d110167_0.png!
I also noticed that the uspRKDPRHedgeDailyPositionDetailByMonth and uspRKDPRHedgeDailyPositionDetail procs are executed more than once. Why?
# I manually executed that SP with the *Include Actual Execution Plan* turned on:
{color:blue}exec{color}{color:maroon}sp_executesql{color}{color:red}N'[uspRKDPRInvDailyPositionDetail] @intCommodityId ,@intLocationId,@intVendorId,{color}
{color:red}@strPurchaseSales,@strPositionIncludes,@dtmToDate,@strByType, @strPositionBy'{color}{color:gray},{color}{color:red}N'@intCommodityId nvarchar(2),{color}
{color:red}@intLocationId int,@intVendorId int,@strPurchaseSales nvarchar(4000),@strPositionIncludes nvarchar(16),{color}
{color:red}@dtmToDate nvarchar(19),@strByType nvarchar(4000),@strPositionBy nvarchar(4000)'{color}{color:gray},{color}
@intCommodityId{color:gray}={color}{color:red}N'1,'{color}{color:gray},{color}@intLocationId{color:gray}={color}0{color:gray},{color}@intVendorId{color:gray}={color}0{color:gray},{color}@strPurchaseSales{color:gray}={color}{color:red}N''{color}{color:gray},{color}
@strPositionIncludes{color:gray}={color}{color:red}N'Licensed Storage'{color}{color:gray},{color}@dtmToDate{color:gray}={color}{color:red}N'2019-01-21T00:00:00'{color}{color:gray},{color}
@strByType{color:gray}={color}{color:red}N''{color}{color:gray},{color}@strPositionBy{color:gray}={color}{color:red}N''{color}
I can see many warnings and missing indexes:
!pastedImage_d110167_1.png|height=321,width=684!
I think we should start by fixing these issues. Please do the following:
# Acquire a recent copy of MCP's backup
# Restore to DEV1830CR2 on the QC server
# Repeat my steps above and log the baseline (CPU, Reads, Writes, Duration) in this ticket for the SP
# Resolve the warnings if able
# Create the missing indexes
# Update statistics (this should help to improve the query plan)
# Repeat the steps above and compare the results
A small improvement could lead to a large improvement at MCP because their server would have a much larger resource demand versus the QC server.
Liz can test this by restoring the DB copy, run baseline, run your fix scripts and then compare.
This process should be repeated with the other stored procs that live behind the DPR report.
|