Quick SELECT sometimes timeout
I have a stored procedure that does a simple selection. Every time I start it manually, it works under the second one. But in production (SQL Azure S2 Database) it runs inside a scheduled task every 12 of ours, so I think it's reasonable to expect it to start every time with "cold" - no cached data. And performance is very unpredictable - sometimes it takes 5 seconds, sometimes 30 and sometimes 100.
The selection is optimized to the maximum (as far as I know, anyway) - I created a filtered index including all columns returned from the SELECT, so the only operation in the execution plan is an index scan. There is a huge difference between estimated and actual lines:
But overall, the request seems pretty easy. I do not blame the environment (SQL Azure) because there are a lot of queries running all the time and this is the only thing related to this performance issue.
Here is an XML execution plan for SQL ninjas wanting to help: http://pastebin.com/u5GCz0vW
EDIT:
Table structure:
CREATE TABLE [myproject].[Purchase](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ProductId] [nvarchar](50) NOT NULL,
[DeviceId] [nvarchar](255) NOT NULL,
[UserId] [nvarchar](255) NOT NULL,
[Receipt] [nvarchar](max) NULL,
[AppVersion] [nvarchar](50) NOT NULL,
[OSType] [tinyint] NOT NULL,
[IP] [nchar](15) NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[ValidationState] [smallint] NOT NULL,
[ValidationInfo] [nvarchar](max) NULL,
[ValidationError] [nvarchar](max) NULL,
[ValidatedOn] [datetime] NULL,
[PurchaseId] [nvarchar](255) NULL,
[PurchaseDate] [datetime] NULL,
[ExpirationDate] [datetime] NULL,
CONSTRAINT [PK_Purchase] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
Index definition:
CREATE NONCLUSTERED INDEX [IX_AndroidRevalidationTargets3] ON [myproject].[Purchase]
(
[ExpirationDate] ASC,
[ValidatedOn] ASC
)
INCLUDE ( [ProductId],
[DeviceId],
[UserId],
[Receipt],
[AppVersion],
[OSType],
[IP],
[CreatedOn],
[ValidationState],
[ValidationInfo],
[ValidationError],
[PurchaseId],
[PurchaseDate])
WHERE ([OSType]=(1) AND [ProductId] IS NOT NULL AND [ProductId]<>'trial' AND ([ValidationState] IN ((1), (0), (-2))))
The data can be considered sensitive, so I cannot provide a sample.
source to share
Since your query only returns 1 match, I think you should reduce your index to a minimum. You can get the remaining columns using Key Lookup from the clustered index:
CREATE NONCLUSTERED INDEX [IX_AndroidRevalidationTargets3] ON [myproject].[Purchase]
(
[ExpirationDate] ASC,
[ValidatedOn] ASC
)
WHERE ([OSType]=(1) AND [ProductId] IS NOT NULL AND [ProductId]<>'trial' AND ([ValidationState] IN ((1), (0), (-2))))
This does not eliminate crawling, but it does make the index much faster to read quickly.
Edit: The OP stated that the antialiased row index was ignored by SQL Server. You can force SQL Server to use the filter index:
SELECT *
FROM [myproject].[Purchase] WITH (INDEX(IX_AndroidRevalidationTargets3))
source to share