402 reputation
414
bio website endforward.nl
location Amsterdam, Netherlands
age 31
visits member for 3 years, 8 months
seen Jun 24 at 11:06
.net developer with interest in c#, faceted navigation (solr), distributed systems, wcf, xslt, umbraco, asp.net mvc and some other things...

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.