olle
Reputation
462
Top tag
Next privilege 500 Rep.
Access review queues
Badges
5 18
Newest
Impact
~30k people reached

• 0 posts edited
• 0 helpful flags
• 23 votes cast

13 Comments

 Jun 14 comment How to prevent daily index fragmentation of 99% I am rebuilding every day right now but I wonder if I can prevent this from having to happen every day since I can anticipate pretty well on how the data evolves. Jan 16 comment What are the disadvantage/consequences of moving updated column to separate table to prevent table lock on the original table? I ended up going with the snapshot isolation level. The batched approach also worked but I got very random performance out of it and sometimes over 1000% worse. Jan 14 comment What are the disadvantage/consequences of moving updated column to separate table to prevent table lock on the original table? I am reading up on both options. Jan 14 comment What are the disadvantage/consequences of moving updated column to separate table to prevent table lock on the original table? I'll have to look into that, the calculation is quite simple it's comparing the value of columns in p to another table and then adding a number of points depending on whether they are <, = or >. The calculated points will not change over time for a row thats been calculated but there will be billions of records and 100's of millions that will be calculated in one go. Jan 13 comment What are the disadvantage/consequences of moving updated column to separate table to prevent table lock on the original table? @JNK no that isn't necessary I can do that in the procedure that calculates the points. Jan 13 comment What are the disadvantage/consequences of moving updated column to separate table to prevent table lock on the original table? The table is locked because the number of rows/pages that are involved is so large that it is more efficient to lock the table Jan 12 comment why doesn't time for a simple update scale linearly with the number of records? Marked this answer as correct since the problem was out of date statistics which was visible in the execution plan as a difference in nr of expected and actual rows. Jan 11 comment why doesn't time for a simple update scale linearly with the number of records? I am seeing a 800% difference in estimated rows and actual rows. I will update the statistics tomorrow (don't have the proper permission currently) and see if the problem persists and update the question accordingly. Jan 11 comment why doesn't time for a simple update scale linearly with the number of records? @ids contains the same 10 id's in both cases so the performance of the split function is equal. Jan 11 comment why doesn't time for a simple update scale linearly with the number of records? Ok I will collect them and update the question with them. THanks so far. Nov 2 comment sp_whoisactive keeps returning create procedure statements @Marian but that doesn't explain why it's executing CREATE PROCEDURE [dbo].[TempGetStateItemExclusive3] ever x minutes (not like clockwork). And that is what looks like the blocker. Nov 2 comment sp_whoisactive keeps returning create procedure statements Thanks reading up on it. Jan 17 comment What is the impact of changing the index creation memory setting in SQL Server? Yes on index creation.