sql server - SQL query improved performance needed (1 table) -

sql server - SQL query improved performance needed (1 table) -

so, i've got query (which utilize stored procedure) in sql server 2008 r2. works, cannot believe there isn't more efficient way.

the info in table 'serviceinstance'. one, flat table containing 16 'instances' per ipaddress, each of them having unique tcpport ipaddress.

the columns 'isrestarting' , 'isinuse' not of import system; both when 'isrestarting' or 'isinuse' true, 'isavailable' false

the column 'cpuid' is; each server has 4 cpus - , delhpi application i'm running can have 1 application on cpu @ same time. so, when cpu #1 on server ip '192.168.4.151' in use, cpuid on ip not allowed returned query. (there 16 instances on 1 server 4 cores)

)

so, sp must following:

get available instance, must comply to:

the sp should homecoming 1 row. this low prio me atm - can rerun sp if needed the serverinstance' isenabled must true when serverinstance returned, must set 'isavailable' = false won't picked 1 time again until work done (reset done application logic) none of same cpuid on ip must 'isavailable = false' this makes sure cpu # on server free the serverinstance hasn't been used longest preferred. because have big pool , want load balance traffic the 'found' serverinstance updated immediately. the 'lastrequestdate' should stamped the 'isinuse' set true the 'isavailable' set false

so. info i've created monster:

update top(1) serviceinstance set lastrequestdate=getdate() ,isinuse=1 ,isavailable=0 output inserted.serviceinstanceid, inserted.ipaddress, inserted.tcpport, inserted.lastrequestdate, inserted.isinuse serviceinstanceid in ( select top (1) serviceinstanceid serviceinstance (serviceinstance.isavailable = 1 , serviceinstance.isenabled = 1) , serviceinstanceid not in ( select ngi1.serviceinstanceid (select cpuid,ipaddress [serviceinstance] ngi isinuse=1) inner bring together serviceinstance ngi1 on a.ipaddress = ngi1.ipaddress , a.cpuid = ngi1.cpuid ) order lastrequestdate asc )

however, sense cannot efficient way go things. query supposed running ~10 times per sec on peek hours , putting heavy cpu pressure level on sql server.

any tips welcome! sense should able utilize partition on or bring together own table, cannot seem create successfully...!

ok, so, table construction following:

serviceinstanceid int not null ipaddres varchar(20) not null tcpport varchar(5) not null lastrequestdate datetime not null isenabled bit not null isavailable bit not null isrestarting bit not null isinuse bit not null cpu int not null

on moment, have no indexes. because table mutated lot (every time serverinstance 'used' table mutates 3 or 4 times (1= use, 2 = restart after use, 3= set isavailable, 4= restart on failure) guess that, if made indexes, these have updated every mutation. not sure felt decrease performance :)

exec plan:

important add-on after loadtests:

i needed utilize exec @rc =sp_getapplock @resource='mylock', @lockmode='exclusive', @lockowner='transaction', @locktimeout = 1000 storedprocedure. doesn't work without it!

with unavailablecpus ( select ipaddress ,cpuid serviceinstance isavailable = 0 grouping ipaddress ,cpuid ) ,availableinstances ( select serviceinstanceid ,lastrequestdate serviceinstance left bring together unavailablecpus on unavailablecpus.ipaddress = serviceinstance.ipaddress , unavailablecpus.cpuid = serviceinstance.cpuid serviceinstance.isavailable = 1 , serviceinstance.isenabled = 1 , unavailablecpus.ipaddress null ) ,preferredinstance ( select top 1 serviceinstanceid availableinstances order lastrequestdate ) update serviceinstance set lastrequestdate=getdate() ,isinuse=1 ,isavailable=0 output inserted.serviceinstanceid ,inserted.ipaddress ,inserted.tcpport ,inserted.lastrequestdate ,inserted.isinuse serviceinstanceid in (select serviceinstanceid preferredinstance)

sql sql-server