February 27, 2009 at 2:28 pm
Hi...I have a problem with a stored proc. we have dev and production environments. In production server, this proc takes forever. The tables have all the indexes and they were being rebuilt every week. But the same proc, in dev environment executes very fast. I checked the indexes, they are same in both the environments. But when I checked execution plans, In production it is using table scans where as in dev there are index seeks!!. what could be the reason for this and how we can solve this problem???
Please advice me...Thank you
February 27, 2009 at 2:42 pm
Probably need to update statistics on the tables involved in the SP.
February 27, 2009 at 2:57 pm
Thanks for the quick reply..... Yeah...I already did that...But Still I m getting the same results...
February 28, 2009 at 6:08 am
Is the amount of data the same in both environments?
Are the indexes the same?
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
February 28, 2009 at 6:43 am
Indexes are same, But the data is different. and there is no much difference in data. say, it has 5 tables which are of total size 30gb in prod and 25GB in Dev.
Thank you
February 28, 2009 at 12:30 pm
ssismaddy (2/28/2009)
there is no much difference in data. say, it has 5 tables which are of total size 30gb in prod and 25GB in Dev.
That's more than enough to make the plans different, especially if the distribution of data is different.
Rather than focus on why there's a difference, focus on improving production. It's hardly uncommon for two servers to show different exec plans. Post the plan, query and table and index definitions if you want help.
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
March 1, 2009 at 1:37 am
Doug Howk (2/27/2009)
Probably need to update statistics on the tables involved in the SP.
statistics are updated when the index are rebuild...
Regards,
[font="Verdana"]Sqlfrenzy[/font]
March 2, 2009 at 6:30 am
Thank you for all your replies and help....After keen working out on this, I found an issue...I m giving 2 queries here...1st query, In the where condition I gave the lastname Directly, which gives the result in 1 sec. 2nd query, I m declaring the lastname variable and passing the value to it, which takes forever. What is the difference?? why does it take so long?? please advice me....Thank you
1) SELECT tlcSOP.OrderID,
vlcSOP30200wArchive.CUSTNAME as CUSTNAME,
CASE WHEN tlcVehicleOwner.LastName IS NULL THEN '' ELSE tlcVehicleOwner.LastName + ', ' + tlcVehicleOwner.FirstName END AS VehicleOwner,
tlcVehicleOwner.LastName as VLastName,
'Posted' as OrderStatus,
'noserialno' as SerialNumber
,tlcVehicleOwner.FirstName as VFirstName
From tlcSOP (nolock)
INNER JOIN vlcSOP30200wArchive (nolock) ON (tlcSOP.OrderID = vlcSOP30200wArchive.MSTRNUMB)
LEFT JOIN tlcVehicleOwner (nolock) ON (tlcSOP.VehicleOwnerID = tlcVehicleOwner.VehicleOwnerID)
WHERE tlcVehicleOwner.LastName LIKE 'vidcom%'
2) DECLARE @LastName VARCHAR(25)
set @lastname = 'vidcom'
SELECT tlcSOP.OrderID,
vlcSOP30200wArchive.CUSTNAME as CUSTNAME,
CASE WHEN tlcVehicleOwner.LastName IS NULL THEN '' ELSE tlcVehicleOwner.LastName + ', ' + tlcVehicleOwner.FirstName END AS VehicleOwner,
'' as ILastName,
tlcVehicleOwner.LastName as VLastName,
'Posted' as OrderStatus,
'noserialno' as SerialNumber
,tlcVehicleOwner.FirstName as VFirstName
From tlcSOP (nolock)
INNER JOIN vlcSOP30200wArchive (nolock) ON (tlcSOP.OrderID = vlcSOP30200wArchive.MSTRNUMB)
LEFT JOIN tlcVehicleOwner (nolock) ON (tlcSOP.VehicleOwnerID = tlcVehicleOwner.VehicleOwnerID)
WHERE tlcVehicleOwner.LastName LIKE @lastname+'%'
March 2, 2009 at 6:43 am
http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/
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
March 2, 2009 at 7:44 am
Thank you very much GilaMonster....It works superb...Thanks a lot..
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply