Reputation
3,797
Top tag
Next privilege 5,000 Rep.
Approve tag wiki edits
Badges
2 14 38
Impact
~168k people reached

Feb
2
answered What process would you use to determine references on a database that has no integrity?
Feb
1
comment SQL Server 2014 Sudden Slow Down / No inserts/ Not related to hardware or index
Those wait stats show a lot of IO but we don't know what proportion of the query is IO and what is CPU. Perform the tests that I recommended as post the results.
Feb
1
comment SQL Server 2014 Sudden Slow Down / No inserts/ Not related to hardware or index
Post an actual execution plan of a surprisingly slow query. The simpler the query the better.
Feb
1
comment SQL Server 2014 Sudden Slow Down / No inserts/ Not related to hardware or index
Run sp_whoisactive during the slow queries 10 times. What kind of wait type appears most often? Is it IO, blocking or CPU?
Feb
1
comment SQL Server 2014 Sudden Slow Down / No inserts/ Not related to hardware or index
So the same queries start becoming slow although you did not write to the DB? Does restarting the server help in these situations? That would be a clue.
Feb
1
comment Does a covering nonclustered index help to answer Select * queries?
@Magier given the information in the question it is not possible to recommend a particular indexing strategy. An (actual) execution plan would be very helpful to do that.
Jan
30
answered Does a covering nonclustered index help to answer Select * queries?
Jan
29
comment How to Resolve Corruption Detected by BACKUP WITH CHECKSUM, But NOT DBCC CHECKDB?
@HerculePoirot maybe you can add a comment here if the issue ever finds a resolution.
Jan
29
answered How to Resolve Corruption Detected by BACKUP WITH CHECKSUM, But NOT DBCC CHECKDB?
Jan
29
comment How to Resolve Corruption Detected by BACKUP WITH CHECKSUM, But NOT DBCC CHECKDB?
I never found out how to correctly interpret these "open ranges" in alloc page output. I think this means "allocated from here to the end of the GAM interval". But I found cases where this was not true. Maybe this is a DBCC PAGE bug. But it looks like this page is marked allocated. Try this: Copy the DB, delete everything in it. Now it's empty but this one broken page should still be allocated. You can now back up the DB and send it to MS support. The backup will be tiny and reveal minimal (but some) info about your data. I assume you can't post that backup publicly(?).
Jan
29
comment How to Resolve Corruption Detected by BACKUP WITH CHECKSUM, But NOT DBCC CHECKDB?
Right, I was to lazy to type that out. If you want to keep going investigating: There is a 2012 DMV that outputs a list of all allocated pages in the DB. You could loop up these page ids there.
Jan
29
comment How to Resolve Corruption Detected by BACKUP WITH CHECKSUM, But NOT DBCC CHECKDB?
If the page is allocated this is, IMHO, a CHECKDB bug. If it's not allocated I know how to fix this.
Jan
29
comment How to Resolve Corruption Detected by BACKUP WITH CHECKSUM, But NOT DBCC CHECKDB?
I think the only remaining part of the puzzle is why this page is shown as allocated and CHECKDB did not find it. Isn't CHECKDB supposed to validate that all allocated pages are actually integrated into some partition?! Do this: Run PAGE(3) on (1:2) and find out whether page 428321 is marked allocated there or not. Also, you can use PAGE(3) to inspect the rows on this broken page to see if you want to salvage something before we kill it.
Jan
29
comment How to Resolve Corruption Detected by BACKUP WITH CHECKSUM, But NOT DBCC CHECKDB?
Follow the ids (e.g. allocunitid 72057594386120704) to see what object etc. this is supposed to be. Looks like somehow this page landed at the wrong place. But it's allocated so not sure why CHECKDB did not see it. In any case you'll probably find that the page is garbage. Still, interesting to dig. Does the page have rows on it? Type 1 is a data page. Or, maybe that alloc info is for pageid 2021305! Also look at the contents of pageid 2021305.
Jan
29
comment Why does adding a TOP 1 dramatically worsen performance?
Not sure I like this answer. Join hints are very invasive. Some simple indexing changes should be tried first, for example index on the date column.
Jan
29
comment How to Resolve Corruption Detected by BACKUP WITH CHECKSUM, But NOT DBCC CHECKDB?
Find out what index that page belongs to. Is it even allocated? Probably not which is why BACKUP checks it but CHECKDB does not. I think you can resolve that error by somehow overwriting that page (maybe fill up the fill with dummy data?).
Jan
28
comment DBCC Usage on production system
Wow, in 2016 there are still people creating such "maintenance" plans? They don't know the first thing about SQL Server.
Jan
24
revised Will the Query Optimizer Ignore A Fragmented Index?
added 204 characters in body
Jan
23
answered Will the Query Optimizer Ignore A Fragmented Index?
Jan
17
comment VLDB is stuck in recovery state. It's a permission issue, now what should I do?
This is an OS-level error. SQL Server cannot just decide to continue. You can't tell it to continue. You need to ensure OS-level permissions. Grand full control to Everyone to test this. Then revert to saner permissions.