July 3, 2012 at 4:09 am
Hi There,
we have an indexed view thats causing some issues.
We access the view via a synonym in a Stored Proc.
when the proc is run, it takes hours to complete.
If we then remove the synonyms from the stored proc and directly reference the view instead, we get the results in <5 sec.
Looking at the execution plan, the directly reference code uses index seeks, but the code using synonyms to the same view uses a different plan with index scans.
Why would the 2 be using different execution plans if they are essentially pointing to the same view? I have updated stats on everything but still it wont work with synonyms.
Any help would be appreciated. Let me know if you need more information!
Cheers.
July 3, 2012 at 4:23 am
I don't know whether anything like this applies in your case, but I've had trouble with synonyms before. It happened when I restored a second copy of the database on to the same server. After witnessing some very strange behaviour, I realised that the synonyms in the new database were pointing at the tables in the old. It might be worth scripting out your synonyms to check they're pointing where you think they are.
John
July 3, 2012 at 5:24 am
John Mitchell-245523 (7/3/2012)
I don't know whether anything like this applies in your case, but I've had trouble with synonyms before. It happened when I restored a second copy of the database on to the same server. After witnessing some very strange behaviour, I realised that the synonyms in the new database were pointing at the tables in the old. It might be worth scripting out your synonyms to check they're pointing where you think they are.John
Thanks for the response. I have already dropped and recreated the synonym in question with no luck. I did make sure that it is pointing to the right place, and infact I copied the exact string from the synonym definition and used that to replace the synonym in the stored proc.
result: synonym: 4+ hours, directly pointing: < 5 sec.
So even though they are defiantly pointing to the same view, they give very different performance.
July 3, 2012 at 1:54 pm
Taking a wild *** guess here and wondering if its because when using the synonym the query optimizer does not see the index on the view?? not sure why since synonym's should pass all of the indexes that are on the underlying object. the other question is if there is an obvious performance boost using the view directly why not just use the view directly. (although since your trying to use a synonym there is probably a good business reason to.)
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
July 5, 2012 at 4:09 am
capn.hector (7/3/2012)
Taking a wild *** guess here and wondering if its because when using the synonym the query optimizer does not see the index on the view?? not sure why since synonym's should pass all of the indexes that are on the underlying object. the other question is if there is an obvious performance boost using the view directly why not just use the view directly. (although since your trying to use a synonym there is probably a good business reason to.)
Yeah- we use synonyms to ease the migration from dev to qa to live...
From what I've read yes- synonyms should just point to the underlying object transparently and should see it as if it was just referenced normally.
But the query optimizer is not seeing that index on the view for some reason. I was just wondering if anyone else had this experience and found a way to solve it. At the moment the query is just referencing the indexed view directly until we can work out why the performance hit when using a synonym.
Its got to be something to do with the optimizer ignoring the index on the view. I can't see any way to force it to use the index...
Help!
July 5, 2012 at 4:36 am
Not sure if this applies but: http://support.microsoft.com/kb/963684
Carlton.
July 5, 2012 at 5:42 am
Rin Sitah (7/3/2012)
Hi There,when the proc is run, it takes hours to complete.
Any help would be appreciated. Let me know if you need more information!
Cheers.
I'm thinking the issue has a lot more to do with the parameters in the procedure than it does with the synonym itself.
parameter sniffing and out of date statistics would be what i'd think are adversly affecting the performance.
you didn't exactly describe how you tested the synonym: did you recompile the procedure to not use the synonym, and then test the procedure with the exact same parameters, or did you run the query part of the proc in SSMS to test it?
can you post the code of the procedure itself?
Lowell
July 5, 2012 at 6:04 am
Lowell (7/5/2012)
Rin Sitah (7/3/2012)
Hi There,when the proc is run, it takes hours to complete.
Any help would be appreciated. Let me know if you need more information!
Cheers.
I'm thinking the issue has a lot more to do with the parameters in the procedure than it does with the synonym itself.
parameter sniffing and out of date statistics would be what i'd think are adversly affecting the performance.
you didn't exactly describe how you tested the synonym: did you recompile the procedure to not use the synonym, and then test the procedure with the exact same parameters, or did you run the query part of the proc in SSMS to test it?
can you post the code of the procedure itself?
Im not using the proc itself via exec procname to test- I scripted out the proc and am running the SQL generated.
This is the method I used to test.
Firstly I updated all the statistics with a full scan.
Next I cleared the proc and buffer cache using
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
Next I ran the SQL from the proc using the synonyms and timed it.
Next I cleared the proc and buffers again.
Then run the SQL from the proc again but with direct references to the indexed view, and do timings.
Synonyms live in the VgmUtilities DB.
The SQL I am running is as follows:
--set up variables and define...
declare @StartDate datetime
declare @EndDate datetime
set @StartDate = N'1 june 2012'
set @EndDate = N'27 june 2012'
--Proc codes starts here:
SET NOCOUNT ON;
if @EndDate is Null
select @EndDate = getdate()
SELECT SUM(amount) [Total],SUM(number) [Number of Records],
--these synonyms are for functions, and are not causing any issues i dont think...
VgmUtilities.dbo.GetTxnCurrencyLookUp(TransactionCurrency)TransactionCurrency,
VgmUtilities.dbo.GetTxnTypeLookUp(TransactionType)TransactionType,
VgmUtilities.dbo.GetTxnCodeLookUp(TransactionCode)TransactionCode,TransactionSubCode,SecondarySubCode
FROM
(
Select
SUM(Total) [amount],count(*) [Number], wt.TransactionCurrency,
wt.TransactionType,
wt.TransactionCode,
wt.TransactionSubCode,
wt.SecondarySubCode
--Here is the line that I change to point to the synonym or directly reference the indexed view...
FROM--VgmUtilities.dbo.VgmWallet_vw_WalletTransactionSummary (NOEXPAND) wt
VgmWalletSubscriber.Reports.vw_WalletTransactionSummary (NOEXPAND) wt
WHERE
(
transactiondatetime < @enddate
AND transactiondatetime >= @startdate
)
OR
(
transactiondatetime < @StartDate
AND LastUpdated < @enddate
AND LastUpdated >= @startdate
AND wt.TransactionCode = 3 --'Customer'
AND wt.TransactionType = 2 --'Debit'
)
GROUP BY wt.TransactionCurrency ,wt.TransactionType,wt.TransactionSubCode,wt.TransactionCurrency,
wt.TransactionCode,wt.TransactionSubCode,wt.SecondarySubCode
UNION ALL
select
SUM(Total)*-1 [amount],0 [Number], wt.TransactionCurrency ,wt.TransactionType,wt.TransactionCode,wt.TransactionSubCode, wt.SecondarySubCode
--Here is the line that I change to point to the synonym or directly reference the indexed view...
FROM--VgmUtilities.dbo.VgmWallet_vw_WalletTransactionSummary (NOEXPAND) wt
VgmWalletSubscriber.Reports.vw_WalletTransactionSummary (NOEXPAND) wt
WHERE
wt.TransactionCode = 3--'Customer'
AND wt.TransactionType=2--'Debit'
AND TransactionDateTime < @enddate
AND transactiondatetime >= @startdate
AND LastUpdated >= @enddate
GROUP BY wt.TransactionCurrency ,wt.TransactionType,wt.TransactionCode,wt.TransactionSubCode, wt.SecondarySubCode
) data
GROUP BY TransactionCurrency,TransactionType,TransactionCode,TransactionSubCode,SecondarySubCode
How can I put up the execution plans in an easy to read format?
July 5, 2012 at 6:11 am
Might I humbly recommend including the actual execution plans from each? That would immediately tell where the problem lies.
July 5, 2012 at 6:20 am
yeah i'm more sure than ever it's parameter sniffing, and nothing to do with synonyms at all;
my first clue:
if @EndDate is Null
select @EndDate = getdate()
i'll assume that the costructor for the proc looks something like this:
create procedure myProc( @StartDate datetime = null, @EndDate datetime = null)
AS
when a proc like that gets compiled, since the "default" values are null, the plan that gets create assumes that would be the typical/most often used values, and builds a plan catered specifically for that;
since the proc is not called typically with double null,s the plan is a poor performer for the real values.
the fixes are optimize for unknown, or to declare local variables inside the proc, and assign them to teh values passed.
Lowell
July 5, 2012 at 6:49 am
Lowell (7/5/2012)
yeah i'm more sure than ever it's parameter sniffing, and nothing to do with synonyms at all;my first clue:
if @EndDate is Null
select @EndDate = getdate()
i'll assume that the costructor for the proc looks something like this:
create procedure myProc( @StartDate datetime = null, @EndDate datetime = null)
AS
when a proc like that gets compiled, since the "default" values are null, the plan that gets create assumes that would be the typical/most often used values, and builds a plan catered specifically for that;
since the proc is not called typically with double null,s the plan is a poor performer for the real values.
the fixes are optimize for unknown, or to declare local variables inside the proc, and assign them to teh values passed.
Yes this is all true, but I am not running it via the stored procedure- i am just running SQL code with the parameters passed directly , as in the above code in my last post. Why would changing it from synonyms to directly pointing at the view give different execution plans?
July 5, 2012 at 6:50 am
venoym (7/5/2012)
Might I humbly recommend including the actual execution plans from each? That would immediately tell where the problem lies.
Whats the best way to post execution plans?
July 5, 2012 at 6:58 am
For clarity here is what I am running:
I put this SQL in, and run it. It takes hours. This is the one using synonyms.
--set up variables and define...
declare @StartDate datetime
declare @EndDate datetime
set @StartDate = N'1 june 2012'
set @EndDate = N'27 june 2012'
SELECT SUM(amount) [Total],SUM(number) [Number of Records],
VgmUtilities.dbo.GetTxnCurrencyLookUp(TransactionCurrency)TransactionCurrency,
VgmUtilities.dbo.GetTxnTypeLookUp(TransactionType)TransactionType,
VgmUtilities.dbo.GetTxnCodeLookUp(TransactionCode)TransactionCode,TransactionSubCode,SecondarySubCode
FROM
(
Select
SUM(Total) [amount],count(*) [Number], wt.TransactionCurrency,
wt.TransactionType,
wt.TransactionCode,
wt.TransactionSubCode,
wt.SecondarySubCode
--Here is the line that I change to point to the synonym or directly reference the indexed view...
--this time we're using synonyms
FROM VgmUtilities.dbo.VgmWallet_vw_WalletTransactionSummary (NOEXPAND) wt
WHERE
(
transactiondatetime < @enddate
AND transactiondatetime >= @startdate
)
OR
(
transactiondatetime < @StartDate
AND LastUpdated < @enddate
AND LastUpdated >= @startdate
AND wt.TransactionCode = 3 --'Customer'
AND wt.TransactionType = 2 --'Debit'
)
GROUP BY wt.TransactionCurrency ,wt.TransactionType,wt.TransactionSubCode,wt.TransactionCurrency,
wt.TransactionCode,wt.TransactionSubCode,wt.SecondarySubCode
UNION ALL
select
SUM(Total)*-1 [amount],0 [Number], wt.TransactionCurrency ,wt.TransactionType,wt.TransactionCode,wt.TransactionSubCode, wt.SecondarySubCode
--Here is the line that I change to point to the synonym or directly reference the indexed view...
--its using synonym this time
FROMVgmUtilities.dbo.VgmWallet_vw_WalletTransactionSummary (NOEXPAND) wt
WHERE
wt.TransactionCode = 3--'Customer'
AND wt.TransactionType=2--'Debit'
AND TransactionDateTime < @enddate
AND transactiondatetime >= @startdate
AND LastUpdated >= @enddate
GROUP BY wt.TransactionCurrency ,wt.TransactionType,wt.TransactionCode,wt.TransactionSubCode, wt.SecondarySubCode
) data
GROUP BY TransactionCurrency,TransactionType,TransactionCode,TransactionSubCode,SecondarySubCode
Then after clearing all the bufferes and proc cache, I enter this SQL and run it. (this one is pointing directly to the indexed view)
--set up variables and define...
declare @StartDate datetime
declare @EndDate datetime
set @StartDate = N'1 june 2012'
set @EndDate = N'27 june 2012'
SELECT SUM(amount) [Total],SUM(number) [Number of Records],
VgmUtilities.dbo.GetTxnCurrencyLookUp(TransactionCurrency)TransactionCurrency,
VgmUtilities.dbo.GetTxnTypeLookUp(TransactionType)TransactionType,
VgmUtilities.dbo.GetTxnCodeLookUp(TransactionCode)TransactionCode,TransactionSubCode,SecondarySubCode
FROM
(
Select
SUM(Total) [amount],count(*) [Number], wt.TransactionCurrency,
wt.TransactionType,
wt.TransactionCode,
wt.TransactionSubCode,
wt.SecondarySubCode
--Here is the line that I change to point to the synonym or directly reference the indexed view...
--its pointing directly to the view this time
FROMVgmWalletSubscriber.Reports.vw_WalletTransactionSummary (NOEXPAND) wt
WHERE
(
transactiondatetime < @enddate
AND transactiondatetime >= @startdate
)
OR
(
transactiondatetime < @StartDate
AND LastUpdated < @enddate
AND LastUpdated >= @startdate
AND wt.TransactionCode = 3 --'Customer'
AND wt.TransactionType = 2 --'Debit'
)
GROUP BY wt.TransactionCurrency ,wt.TransactionType,wt.TransactionSubCode,wt.TransactionCurrency,
wt.TransactionCode,wt.TransactionSubCode,wt.SecondarySubCode
UNION ALL
select
SUM(Total)*-1 [amount],0 [Number], wt.TransactionCurrency ,wt.TransactionType,wt.TransactionCode,wt.TransactionSubCode, wt.SecondarySubCode
--Here is the line that I change to point to the synonym or directly reference the indexed view...
--its pointing to the view directly this time...
FROMVgmWalletSubscriber.Reports.vw_WalletTransactionSummary (NOEXPAND) wt
WHERE
wt.TransactionCode = 3--'Customer'
AND wt.TransactionType=2--'Debit'
AND TransactionDateTime < @enddate
AND transactiondatetime >= @startdate
AND LastUpdated >= @enddate
GROUP BY wt.TransactionCurrency ,wt.TransactionType,wt.TransactionCode,wt.TransactionSubCode, wt.SecondarySubCode
) data
GROUP BY TransactionCurrency,TransactionType,TransactionCode,TransactionSubCode,SecondarySubCode
1st one takes hours, 2nd one takes < 5sec. Exact same code apart from one pointing to view directly and one going via synonyms...
My question is why? and how can i fix this?
Stats have been updated with Full Scan and buffer + proc cache have been cleared before each test...
July 5, 2012 at 7:33 am
Yes this is all true, but I am not running it via the stored procedure- i am just running SQL code with the parameters passed directly , as in the above code in my last post. Why would changing it from synonyms to directly pointing at the view give different execution plans?
exactly my point: you are not testing the procedure itself, and building a completely different adhoc execution plan for yourself when you run it directly.
the issue is with a bad execution plan that is stored for the procedure. when you pass specific values to the ad hoc code, a plan for those values gets built., which is not how the proc is running. the issue is with a bad execution plan for the procedure, not the synonym.
Lowell
July 5, 2012 at 8:00 am
for the item that is running slow, can you try adding this option(optimize for ) to your query?
SELECT SUM(amount) [Total],SUM(number) [Number of Records],
...[snipped for clarity]...
) data
GROUP BY TransactionCurrency,TransactionType,TransactionCode,TransactionSubCode,SecondarySubCode
--this is what i think willa ddress the issue
option (OPTIMIZE FOR (@StartDate UNKNOWN, @EndDate UNKNOWN))
Lowell
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply