LCK_M_IX in SQL Server database

1

Well, I have two separate client bases in SQL Server 2008, and both are using the same system, which uses BDE to connect.

I have an application Scripter to do SQL queries on the databases.

Problem

On both bases, I run select * from TABELA through Scripter and try to get into the system, I get it without problems.

But if I run select * from TABELA order by COLUNA desc , and try to get into the system, on one basis I can enter, and not another.

I am using sp_WhoIsActive to try to find the problem, and I have identified that in both databases, SELECT is ASYNC_NETWORK_IO .

On the basis that I can not log in says SELECT is blocking INSERT .

Attempts

Ihavetriedtorecreateindex,shirinkofthedatabaseandnothing.Icomparedthebases,andbothhavethesameconfiguration.

Observations

  • Ihaveidentifiedthatthedatabasethatgenerateslock,tempdb_allocationsandtempdb_currentarezeroed,doesithavesomethingtodo?
  • Anotherdetailisthatinthedatabasethatdoesnotgeneratelock,SpaceAvailableislarge,andinthedatabasethatgenerateslockisalmost0zero)asshownbelow

Donotgeneratelock

Geralock

Doubt

What can you do to identify the reason for the error?

Additional data (02/20/2018 at 13:25)

I circled sp_lock and the result was:

spid   dbid   ObjId       IndId  Type Resource                         Mode     Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
51     42     0           0      DB                                    S        GRANT
52     42     0           0      DB                                    S        GRANT
54     76     0           0      DB                                    S        GRANT
55     103    0           0      DB                                    S        GRANT
56     103    0           0      MD   5(1:0:0)                         Sch-S    GRANT
56     103    0           0      DB                                    S        GRANT
56     103    366624349   6      PAG  1:624712                         S        GRANT
56     103    366624349   6      PAG  1:624713                         S        GRANT
56     103    366624349   6      PAG  1:624714                         S        GRANT
56     103    366624349   6      PAG  1:624715                         S        GRANT
56     103    366624349   6      PAG  1:624708                         S        GRANT
56     103    366624349   6      PAG  1:624709                         S        GRANT
56     103    366624349   6      PAG  1:624710                         S        GRANT
56     103    366624349   6      PAG  1:624711                         S        GRANT
56     103    366624349   6      PAG  1:624704                         S        GRANT
56     103    366624349   6      PAG  1:624705                         S        GRANT
56     103    366624349   6      PAG  1:624706                         S        GRANT
56     103    366624349   6      PAG  1:624707                         S        GRANT
56     103    366624349   6      PAG  1:624660                         S        GRANT
56     103    366624349   6      PAG  1:624661                         S        GRANT
56     103    366624349   6      PAG  1:624662                         S        GRANT
56     103    366624349   6      PAG  1:624663                         S        GRANT
56     103    366624349   6      PAG  1:624659                         S        GRANT
56     103    366624349   1      PAG  1:526026                         IS       GRANT
56     103    366624349   6      PAG  1:624716                         S        GRANT
56     103    366624349   6      PAG  1:624717                         S        GRANT
56     103    366624349   0      TAB                                   IS       GRANT
57     103    366624349   0      TAB                                   IX       GRANT
57     103    366624349   6      PAG  1:624717                         IX       WAIT
57     103    0           0      DB                                    S        GRANT
57     103    366624349   1      KEY  (07680471601e)                   X        GRANT
57     103    366624349   2      KEY  (9d8e9d84975e)                   X        GRANT
57     103    366624349   3      KEY  (2fcebe7f747e)                   X        GRANT
57     103    366624349   1      PAG  1:927056                         IX       GRANT
57     103    366624349   2      PAG  1:573725                         IX       GRANT
57     103    1477580302  0      TAB                                   IS       GRANT
57     103    366624349   4      PAG  1:607421                         IX       GRANT
57     103    1938105945  0      TAB                                   IS       GRANT
57     103    366624349   3      PAG  1:582200                         IX       GRANT
57     103    366624349   4      KEY  (da0106a2694d)                   X        GRANT
57     103    366624349   5      PAG  1:617162                         IX       GRANT
57     103    0           0      MD   5(1:0:0)                         Sch-S    GRANT
57     103    366624349   5      KEY  (1d3df3b848ca)                   X        GRANT
58     76     0           0      DB                                    S        GRANT
59     103    0           0      DB                                    S        GRANT
60     103    0           0      DB                                    S        GRANT
61     103    0           0      DB                                    S        GRANT
62     103    0           0      DB                                    S        GRANT
63     103    0           0      DB                                    S        GRANT
66     42     0           0      DB                                    S        GRANT
66     1      1131151075  0      TAB                                   IS       GRANT
67     42     0           0      DB                                    S        GRANT
70     103    0           0      DB                                    S        GRANT
    
asked by anonymous 20.02.2018 / 13:56

0 answers