sql - Quick SELECT sometimes time out -


i have stored procedure execute simple select. time run manually, runs under second. in production (sql azure s2 database) runs inside scheduled task every 12 ours - think reasonable expect run every time "cold" - no cached data. , performance unpredictable - takes 5 second, 30 , 100.

the select optimized maximum (of knowledge, anyway) - created filtered index including columns returned select, operation in execution plan index scan. there huge difference between estimated , actual rows:

enter image description here

but overall query seems pretty lightweight. not blame environment (sql azure) because there lot of queries executing time, , 1 1 performance problem.

here xml execution plan sql ninjas willing : 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])  ([ostype]=(1) , [productid] not null , [productid]<>'trial' , ([validationstate] in ((1), (0), (-2)))) 

data can considered sensitive, cant provide sample.

since query returns 1 match, think should trim down index bare minimum. can remaining columns via key lookup clustered index:

create nonclustered index [ix_androidrevalidationtargets3] on [myproject].[purchase] (     [expirationdate] asc,     [validatedon] asc ) ([ostype]=(1) , [productid] not null , [productid]<>'trial' , ([validationstate] in ((1), (0), (-2)))) 

this doesn't eliminate scan, makes index leaner fast read.

edit: op stated slimmed-down index ignored sql server. can force sql server use filter index:

select * [myproject].[purchase] (index(ix_androidrevalidationtargets3)) 

Comments

Popular posts from this blog

python - No exponential form of the z-axis in matplotlib-3D-plots -

php - Best Light server (Linux + Web server + Database) for Raspberry Pi -

c# - "Newtonsoft.Json.JsonSerializationException unable to find constructor to use for types" error when deserializing class -