2,741 reputation
1829
bio website 127.0.0.1
location Frankfurt Am Main, Germany
age 26
visits member for 2 years, 11 months
seen 17 hours ago

Jan
26
comment What does Page Life Expectancy say about the instance?
In order to help you understand the sudden drops: When a big scan of uncached data is executed lots of pages are being evicted to make room for the new pages. It's a modified LRU algorithm. New pages drop old ones.
Jan
16
comment Deadlock due to concurrent updates (with SIU lock)
Post the execution plan. I don't get why one process took a SIU lock which encompasses a shared page lock. You are updating exactly one row, right? Are there other statements in the transaction? line="63" => there seem to be many in the proc at least.
Jan
16
comment Would it be better for query plans to be split by statement for re-usability?
@PaulWhite that design design could have been made the other way around. I was talking from a product designer's perspective.
Jan
15
comment Would it be better for query plans to be split by statement for re-usability?
I always thought the unit of caching is the batch because they use a hash of the text as the cache key. Splitting statements would require parsing which is expensive.
Jan
13
awarded  Notable Question
Jan
7
awarded  Nice Question
Jan
7
comment Use of empty tables in Join in SQL server
Try adding a check constraint 0=1. Hopefully the optimizer picks it up and removes the table access. If you do that in the query directly I know that it does remove the access.
Jan
5
awarded  Popular Question
Jan
4
comment Programmatically determine whether a database was shut down cleanly or whether it required crash recovery
@ShawnMelton auto-close? No, thanks :) Joking aside: At the point I'm measuring the crash recovery history all databases are online and open.
Jan
4
revised Programmatically determine whether a database was shut down cleanly or whether it required crash recovery
added 27 characters in body
Jan
4
comment Programmatically determine whether a database was shut down cleanly or whether it required crash recovery
I have yet to find circumstances in which it becomes 1. Even cleanly taking a database offline leaves it 0.
Jan
4
comment SQL Server recovery with LVM snapshot as base
If this is an emergency, this might work: Delete the logs before starting the service. Put the DB into emergency mode. Perform a tail log backup and a full backup. Maybe you can then piece together the database by restoring all the logs you have.
Jan
4
asked Programmatically determine whether a database was shut down cleanly or whether it required crash recovery
Jan
2
comment At What Point Should I Split or Partition a Very Large but Simple Table
Performance never tanks just be the fact that a table is big. In fact what's big to many is small to some. Understand what operations are being made faster and which slower by partitioning. Partitioning is not a go faster switch. It's a mostly slower switch and some things become blindingly fast.
Jan
2
comment Having problems with concurrency while trying to lock a record in custom messaging system (Queue)
Also, lock level hints are missing such as UPDLOCK. Also HOLDLOCK is missing.
Jan
2
comment Having problems with concurrency while trying to lock a record in custom messaging system (Queue)
I don't see a transaction. Is there one?
Jan
2
comment Why not rebuild indexes with page count <1000?
Small indexes are likely to be cached in memory. Indexes that do not incur IO anyway do not benefit from defragmention. This 1000 page count rule is a heuristic.
Jan
1
revised SQL Native backup MIRROR TO performance
added 313 characters in body
Dec
31
revised SQL Native backup MIRROR TO performance
added 828 characters in body
Dec
31
comment SQL Native backup MIRROR TO performance
Actually, I tested this now (curiosity took over): Backing up from SSD (very fast) to two (almost) identical magnetic drives. Using data compression (but the CPU was not maxed out). Mirror: 1:37. No mirror: 1:10 to drive 1. No mirror: 1:10 to drive 2. This slowdown is well within the tolerance of my theory.