October 2, 2012 at 7:10 pm
Today we had a problem with a stored procedure in production, here is the code within the SP:
SELECT
TOP 1 s.SiteID
FROM
Sites s
INNER JOIN SiteUrls su
ON su.SiteID = s.SiteID
WHERE
@Url like su.URL + '%'
ORDER BY
len(su.URL) desc
For a long time, even though there are duplicated URLs in that table it always returned the "correct" SiteID which was the smallest SiteID. But today SQL Server decided to return the "biggest" SiteID.
I took this select and ran it outside the stored procedure and I got the expected SiteID (the smallest), also if I remove the TOP and just do select * it returns the smallestID as the first row.
Basically I need to explain my boss why this happened, what change could have caused this behavior if the data and the code have not changed.
Any Ideas!?
Thx
October 2, 2012 at 7:12 pm
Alexander-449406 (10/2/2012)
Today we had a problem with a stored procedure in production, here is the code within the SP:SELECT
TOP 1 s.SiteID
FROM
Sites s
INNER JOIN SiteUrls su
ON su.SiteID = s.SiteID
WHERE
@Url like su.URL + '%'
ORDER BY
len(su.URL) desc
For a long time, even though there are duplicated URLs in that table it always returned the "correct" SiteID which was the smallest SiteID. But today SQL Server decided to return the "biggest" SiteID.
I took this select and ran it outside the stored procedure and I got the expected SiteID (the smallest), also if I remove the TOP and just do select * it returns the smallestID as the first row.
Basically I need to explain my boss why this happened, what change could have caused this behavior if the data and the code have not changed.
Any Ideas!?
Thx
On my QA environment its working as expected with the same code and data. So I rebuilt the indexes on both tables to simulate the maintance job in Prod but it didnt replicate the issue.
October 3, 2012 at 1:38 am
Can you provide the DDL for the tables and Indexes as they stand on Production so we can offer some help.
Has anyone changed anything on that table?
What is the sort order on the Indexes that include SiteId, did they change from Ascending to Descending?
You could try
SELECT
TOP 1 s.SiteID
FROM
Sites s
INNER JOIN SiteUrls su
ON su.SiteID = s.SiteID
WHERE
@Url like su.URL + '%'
ORDER BY
len(su.URL) desc,s.SiteID ASC
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 3, 2012 at 2:04 am
Alexander-449406 (10/2/2012)
Today we had a problem with a stored procedure in production, here is the code within the SP:SELECT
TOP 1 s.SiteID
FROM
Sites s
INNER JOIN SiteUrls su
ON su.SiteID = s.SiteID
WHERE
@Url like su.URL + '%'
ORDER BY
len(su.URL) desc
For a long time, even though there are duplicated URLs in that table it always returned the "correct" SiteID which was the smallest SiteID. But today SQL Server decided to return the "biggest" SiteID.
Why is the smallest SiteID 'correct'? The only order by is on the length of the URL, so if there are multiple rows with the same length URL, any of them can be returned and the results are still correct because there's nothing in the query that specifies otherwise.
If you always want the smallest SiteID, you need to add that to the order by
ORDER BY
LEN(su.URL) DESC, SiteID ASC
This is a variation on the 'I didn't specify an order by but expect a specific order' problem. The optimiser is free to change plans (usually as a result of data volumes) as long as the query still returns what is specified, which in this case is one of the rows with the longest URL, any one of them will do.
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
October 3, 2012 at 8:39 am
I understand this is a horrible design to expect an ordering without specifying it (this has been already fixed as you suggest). But I'm still trying to find out what caused this, here is the DDL of both tables:
/****** Object: Table [dbo].[Sites] Script Date: 10/03/2012 08:38:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Sites](
[SiteID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SiteName] [nvarchar](255) NOT NULL,
[DisplayName] [nvarchar](255) NULL,
[Description] [nvarchar](255) NULL,
[DistributorID] [int] NOT NULL,
[AccountNumber] [varchar](255) NULL,
[DateSignedUp] [datetime] NOT NULL,
[SiteTypeID] [smallint] NOT NULL,
[SiteStatusID] [smallint] NOT NULL,
[BaseSiteID] [int] NOT NULL,
[CreatedBy] [int] NULL,
[AdminPassword] [nvarchar](50) NULL,
[TemplateID] [int] NULL,
[StyleID] [int] NULL,
[CatalogID] [int] NULL,
[BillingScheduleID] [int] NULL,
[BillingAmount] [float] NULL,
[MarketID] [int] NULL,
[IsBase] [bit] NULL,
[LanguageID] [int] NULL,
[StatusChangeReasonID] [int] NULL,
CONSTRAINT [PK_Extent] PRIMARY KEY CLUSTERED
(
[SiteID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [ix_Sites_DistributorID] ON [dbo].[Sites]
(
[DistributorID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [RFLIVE_IDX]
GO
CREATE NONCLUSTERED INDEX [ix_Sites_SiteTypeID_IsBase] ON [dbo].[Sites]
(
[SiteTypeID] ASC,
[IsBase] ASC
)
INCLUDE ( [SiteID],
[MarketID],
[LanguageID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [RFLIVE_IDX]
GO
/****** Object: Table [dbo].[SiteURLs] Script Date: 10/03/2012 08:38:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SiteURLs](
[SiteURLID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SiteID] [int] NULL,
[RegisteredWith] [smallint] NULL,
[ExpDate] [datetime] NULL,
[PrimaryURL] [bit] NULL,
[CultureInfo] [nvarchar](50) NULL,
CONSTRAINT [PK_SiteURLs] PRIMARY KEY CLUSTERED
(
[SiteURLID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [ix_SiteURLs_SiteID] ON [dbo].[SiteURLs]
(
[SiteID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [RFLIVE_IDX]
GO
/****** Object: ForeignKey [FK_Sites_Languages] Script Date: 10/03/2012 08:38:23 ******/
ALTER TABLE [dbo].[Sites] WITH NOCHECK ADD CONSTRAINT [FK_Sites_Languages] FOREIGN KEY([LanguageID])
REFERENCES [dbo].[Languages] ([LanguageID])
GO
ALTER TABLE [dbo].[Sites] CHECK CONSTRAINT [FK_Sites_Languages]
GO
/****** Object: ForeignKey [FK_SiteURLs_Sites] Script Date: 10/03/2012 08:38:23 ******/
ALTER TABLE [dbo].[SiteURLs] WITH NOCHECK ADD CONSTRAINT [FK_SiteURLs_Sites] FOREIGN KEY([SiteID])
REFERENCES [dbo].[Sites] ([SiteID])
GO
ALTER TABLE [dbo].[SiteURLs] CHECK CONSTRAINT [FK_SiteURLs_Sites]
GO
October 3, 2012 at 8:52 am
Alexander-449406 (10/3/2012)
I understand this is a horrible design to expect an ordering without specifying it (this has been already fixed as you suggest). But I'm still trying to find out what caused this
A different execution plan from the optimiser. There is no guarantee that the optimiser will always create the same plan for a query, just about anything could have resulted in the optimiser picking a different plan. Probably a change in data volume or distribution. Even a small one could do the trick.
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
October 3, 2012 at 8:57 am
Alexander-449406 (10/3/2012)
I understand this is a horrible design to expect an ordering without specifying it (this has been already fixed as you suggest). But I'm still trying to find out what caused this
How's this for the reason why it occurred?
October 3, 2012 at 9:30 am
GilaMonster (10/3/2012)
Alexander-449406 (10/3/2012)
I understand this is a horrible design to expect an ordering without specifying it (this has been already fixed as you suggest). But I'm still trying to find out what caused thisA different execution plan from the optimiser. There is no guarantee that the optimiser will always create the same plan for a query, just about anything could have resulted in the optimiser picking a different plan. Probably a change in data volume or distribution. Even a small one could do the trick.
Thank you.
October 3, 2012 at 11:11 am
The term for the issue in your query that you're dealing with is that it is "non-deterministic". Although you experienced the same order of results in the past, there was nothing about the query that absolutely guaranteed that the same result set would inherently be returned every single time. Then eventually the optimizer came up with a different plan that resulted in a different ordering. What Gail did was to show you how to make your query "deterministic" and always-every-time-no-matter what return the same records (of course barring changes to the data itself) by adding a unique value to the ordering so that it can only be done one way.
A non-deterministic query leaves it up to the optimizer which records it wants to return, which is very undesirable.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply