20,451 reputation
459112
bio website sqlperformance.com/author/…
location New Zealand
age 45
visits member for 3 years, 7 months
seen 52 mins ago

2d
comment AFTER DELETE trigger will not fire during a cascade delete
This should work, if I am understanding your question correctly. Please add real code to the question to allow us to reproduce the issue. Also, specify SQL Server version and build number. An instead of delete trigger would not be allowed, by the way.
2d
comment Why would I not specify ROWLOCK to get rid of page locks?
@sharptooth Capture the execution plan for the query and add it to your question. As I say in the answer above, with appropriate indexing, the question shouldn't arise at all.
Oct
27
comment Why would I not specify ROWLOCK to get rid of page locks?
@sharptooth Yes.
Oct
27
comment Re-writing a sub-select as a condition on a column generated with 'over (partition by …)'
+1 This is nice. It should reject NULLs on id and code to preserve the semantics of the original query, but sadly even if you do that, the optimizer does not transform this logic to the efficient segment top plan generated by the original (somewhat ironically).
Oct
23
comment Seeing multiple CXPACKET blocks when trying to load data from a INSERT SELECT
Try adding the query hint OPTION (FAST 10000)
Oct
22
comment SQL Server Performance
Perhaps. 2GB might be enough to hold the data and index pages, but extra memory can be used for other purposes e.g. caching execution plans and sorting/hashing.
Oct
20
comment how to order a field based on two parts of the row value
A bit of explanation would be helpful here. Code-only answers are rarely great answers.
Oct
16
comment Unexpected results with random numbers and join types
connect.microsoft.com/SQLServer/feedbackdetail/view/350485/…
Oct
2
comment Are RANK() and DENSE_RANK() deterministic or non-deterministic?
@PavelNefyodov Yep.
Sep
23
comment Attempting to Pivot a column of grades and coming up with NULL values
@JamesDurkee Add an 'answer' to the question with details of the resolution, it might help future visitors to the site. Thanks.
Sep
17
comment Can you use COUNT DISTINCT with an OVER clause?
@KennethFisher I'm glad you worked the details out, I'm afraid I didn't have the time to fully parse your requirements, but I'm glad the technique was useful. By the way, using MAX and DENSE_RANK is fine, so long as you handle the conversion to integer explicitly with MAX(CONVERT(integer, rn)) OVER (.... Otherwise, you will see an unnecessary final Eager Table Spool.
Sep
16
comment Troubleshooting THREADPOOL Waits
Thanks. Any failed to create worker events on sys.dm_os_schedulers?
Sep
15
comment Is database record access sequential after retrieving the indexed page from disk?
@waheebyaqub A physical change to the page will invalidate the cookie. The question of order is complex. For an index page, the offset array is always ordered, even though the entries it points to may be in any order. Big topic.
Sep
8
comment Replication Error when adding small table as new article
For future visitors: support.microsoft.com/kb/2655789
Sep
6
comment Casting fails on subquery result
@ckerth No it is not.
Aug
14
comment Why does transaction log continue to grow in Simple recovery mode with nightly backups
Another thing to consider: an automatic checkpoint for a simple recovery database is only queued once the log is already 70% full. So, you might not need all that much log activity to result in growth, depending on timing.
Aug
11
comment Slow compilation of a SQL statement
How long does compilation take if you don't use the new cardinality estimator? (add OPTION (QUERYTRACEON 9481) to the query).
Aug
8
comment How to find when change tracking was enabled?
Just curious: what problem is this solving? I can't immediately think of a reason I'd need to know when CT was enabled (as opposed to checking the version I have versus CHANGE_TRACKING_MIN_VALID_VERSION)
Aug
8
comment Filtered index not chosen, and rejected with hint
@AdamRackis Speaking of the merge join, it's hard to be certain without seeing all the details, but you may be running up against this issue
Aug
7
comment SQL Server data structure (in protocol)
Ok FWIW I'm pretty certain there isn't any compression of the sort you are asking about. One wonders why you are concerned about this though.