Roman Pokrovskij

less info
121 reputation
5
bio website linkedin.com/pub/…
location Vilnius, Republic of Lithuania
age 39
visits member for 3 years, 5 months
seen 16 hours ago

That is about what I'm thinking most often...


Feb
27
awarded  Supporter
Feb
13
awarded  Commentator
Feb
21
revised How can I disable async IO or isolate one session that slows or freezes other sessions?
added 389 characters in body
Feb
20
revised How can I disable async IO or isolate one session that slows or freezes other sessions?
added 58 characters in body
Feb
18
comment How can I disable async IO or isolate one session that slows or freezes other sessions?
Thank you, mrdenny, but could you tell, this is your understanding (that SQL Server creates as many assync IO readings as it need, or more exactly how many pages need to be loaded) or you have read it in the documentation somewhere? I'm also have the same discussion with myself :), but only documentation can make a point.
Feb
18
comment How can I disable async IO or isolate one session that slows or freezes other sessions?
Clustered Index created on int. And this "int" allways grow.So on the reading of last data the selectivness of clustered index can be very high. Possibly that why Query Optimizer sometimes decide to try it. But this is other line of story...
Feb
17
comment How can I disable async IO or isolate one session that slows or freezes other sessions?
Yes, this is also very possible: buffer flash. I've ordered dmv on pending assyncs already. (Unfortunately I am even can't access the server, "hoster" understands security this way).
Feb
17
revised How can I disable async IO or isolate one session that slows or freezes other sessions?
added 88 characters in body
Feb
17
comment How can I disable async IO or isolate one session that slows or freezes other sessions?
Of course ">" on clustered index /identity looks strange but that how Navision works and do iterations.
Feb
17
comment How can I disable async IO or isolate one session that slows or freezes other sessions?
Anyway I'm not interesting to solve the problem with plans. I'm going to understand why sql server decide to create such huge queue. What about plans the situation is even more complex: 1) I always have good plans in the cache (but sometimes Query Optimizer decide to "make an experiment", this I can't explain good). 2) I have not only one such query but about 10 with different parameter's sets, and 4 big tables where scans sometimes provoke huge queue. So I don't like an idea to fix 40 plans in cache, that are always good (see 1).
Feb
17
comment How can I disable async IO or isolate one session that slows or freezes other sessions?
I know about sp_control_plan_guide but this article is new for me. Thank you.
Feb
17
comment How can I disable async IO or isolate one session that slows or freezes other sessions?
I do not see any logic in the recommendation to move clustered index on different column. Clustered index is where it should be - on the identity column. This is not a problem of index. It could be a problem of SQL Optimizer that sometimes choose wrong plan, but also I could imagine his logic (selectiveness of ">" on identity column could be sometimes very high)
Feb
17
comment How can I disable async IO or isolate one session that slows or freezes other sessions?
Do you mean assync IO or something else? I know about assync IO. The question is how many assync operations can be generated for reading one table? 800 ? It's too much. I'm interesting there how SQL Server decide that it will generate "X" assync calls. It can't create it "blind way". Possibly I have a problem with IO controller on this server.
Feb
14
revised How can I disable async IO or isolate one session that slows or freezes other sessions?
deleted 3 characters in body
Feb
14
revised How can I disable async IO or isolate one session that slows or freezes other sessions?
added 46 characters in body
Feb
14
awarded  Student
Feb
14
revised How can I disable async IO or isolate one session that slows or freezes other sessions?
added 44 characters in body; deleted 1 characters in body; added 45 characters in body
Feb
14
comment How can I disable async IO or isolate one session that slows or freezes other sessions?
Thank you Eric, at least I have tried to explain whole situation.
Feb
14
revised How can I disable async IO or isolate one session that slows or freezes other sessions?
added 250 characters in body; edited title
Feb
14
awarded  Editor