indexing - Why isn't SQL Server using my index? -


in our database have table 200.000 rows

create table dbo.usertask (     usertask_id int not null identity (1, 1),     usertask_sequencenumber int not null default 0,     usertask_identitat uniqueidentifier not null,     usertask_subject varchar(100) not null,     usertask_description varchar(500) not null,             .....             .....     constraint [pk_usertask] primary key clustered      (         [usertask_id] asc     ) on [primary] ) on [primary]

i have created index on usertask_identitat column with

create nonclustered index ix_usertask_identitat on dbo.usertask  (     usertask_identitat )

executing following query, execution plan shows index on usertask_identitat used query:

select usertask_id   usertask     usertask_identitat = @identitat   order usertask_lastsendsystemdatetime desc

but if add column in select list, index not used

select usertask_id, usertask_sequencenumber, usertask_identitat, ....., usertask_subject   usertask     usertask_identitat = @identitat   order usertask_lastsendsystemdatetime desc

why adding column different primary key makes sql server execution plan doesn't use index on usertask_identitat column?

following link http://bytes.com/topic/sql-server/answers/144592-sqlsever-not-using-index seems number of times filtered value repeated on column, can make index not used, have tried doing query @identitat value repeated 60.000 times , other repeated 175 times , results same, index on identitat column ignored.

this taking me crazy!!!

thanks help.

ok - long select column that's in index, or clustering key (usually, primary key), index used, since sql server can find information needs (the usertask_identitat column, , clustered index column(s) ) in leaf level of index navigation structure. can return data needed select query directly index's leaf level pages.

however: if need select second column, neither in index definition, nor part of clustering key, sql server have so-called bookmark lookup actual data pages.

so for every single row finds in nonclustered index, have take clustering index value, search clustered index find actual data page @ leaf level of clustered index, , pick out 1 column want.

bookmark lookups great small numbers of hits - totally devastating performance if you're selecting thousands of rows. in case, sql server query optimizer correctly uses clustered index scan instead - since in clustered index, on leaf level, has all rows available right away.

so: if have index on usertask_identitat , need second column usertask_sequencenumber - include column in nonclustered index of yours:

create nonclustered index ix_usertask_identitat  on dbo.usertask(usertask_identitat) include(usertask_sequencenumber)

with this, additional column present in leaf level of non-clustered index (it cannot used in where clause - it's not part of navigation structure of index!) - , second select can again satisfied leaf-level nodes of nonclustered index -> no expensive bookmark lookups needed -> index used again.

long story short: unless nonclustered index highly selective (e.g. returns 1% of rows or less), , unless nonclustered index covering index (an index contains columns needed satisfy particular query), changes pretty high sql server not use nonclustered index.

for more information:


Comments

Post a Comment

Popular posts from this blog

curl - PHP fsockopen help required -

i trying work on cloudflare api , have found in documentation. unfortunately have no idea how can use in fsockopen or through curl. here example of post request. need know how can make request below data post using curl or fsockopen curl https://www.cloudflare.com/api_json.html -d 'a=cache_lvl' \ -d 'tkn=8afbe6dea02407989af4dd4c97bb6e25' \ -d 'email=sample@example.com' \ -d 'z=example.com' \ -d 'v=agg' here link cloudflare https://www.cloudflare.com/docs/client-api.html#s4.2 try one: $ch = curl_init("https://www.cloudflare.com/api_json.html"); curl_setopt($ch, curlopt_returntransfer,1); curl_setopt($ch, curlopt_followlocation, 1); curl_setopt($ch, curlopt_ssl_verifypeer,false); // handling of -d curl parameter here. $param = array( 'a' => 'cache_lvl', 'tkn' => '8afbe6dea02407989af4dd4c97bb6e25', 'email' => 'sample@example.com', 'z' =...
Read more

HTTP/1.0 407 Proxy Authentication Required PHP -

i working in restricted network/limited access internet. since have use proxy make web service call. here php code calling web service , response . $client = new soapclient("http: //www.xyz.com/abc/wsdls/login.wsdl", array("trace" => 1, "exceptions" => 1, 'proxy_host' => $proxy_host, 'proxy_port' => $proxy_port, 'proxy_login' => $proxy_login, 'proxy_password' => $proxy_password)); response is fatal error: uncaught soapfault exception: [wsdl] soap-error: parsing wsdl: couldn't load ' http://www.xyz.com/abc/wsdls/login.wsdl ' : failed load external entity if copy url error message , paste in browser(with same proxy settings) works. 2nd scenario is, if pass values instead of variables giving me diffarant response $client = new soapclient("http: //www.xyz.com/abc/wsdls/login.wsdl", array("trace" => 1, "exceptions" => 1, 'proxy_host' ...
Read more

c# - Resource not found error -

i have problem accessing method on homecontroller. show code of method : [httpget] public actionresult decriptidentifiantetredirige(string login_crypter, string mdp_crypter) { string loginacrypter = _globalmanager.protegemotdepasse(login_crypter); string mdpacrypter = _globalmanager.protegemotdepasse(mdp_crypter); user userapp = new models.user(login_crypter, mdp_crypter); if (userapp.authentificationvalidee(userapp.userlogin, userapp.password)) { session["name"] = userapp.userlogin; return redirecttoaction("accueil", "home"); } else { return redirecttoaction("validerauthentification", "home"); } } then in routeconfig.cs wrote route : routes.maproute( name: "authentificationapresdecryptage", url: "{controller}/{action}/{login_crypter}/{mdp_crypter}", defaults: new...
Read more