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

20h
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
22
comment RTF to HTML tags conversion
Extended stored procedures are deprecated. You could use SQLCLR instead.
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.
Aug
7
comment SQL Server 2008 R2, error creating file for FileStream
HBSS? What's that?
Aug
7
comment SQL Server data structure (in protocol)
If you really want to dig into the protocol details, see the TDS documentation
Aug
7
comment Dropped index from view still referenced in execution plans
This would be a bug. I imagine it has been fixed now (2005 SP2 is quite old).
Aug
5
comment Update on view over partitioned tables updating all indexes
@user3789393 Complex partitioned view queries can be tricky to get optimal. It often/usually/always helps to keep things as simple as possible and e.g. use literals instead of variables, try OPTION (RECOMPILE) and so on. Aaron's proposed solution effectively does much of the optimizer's work by rewriting the update against the base tables. It's a perfectly good strategy, see how it works out for you. My aim was just to explain bits from the question that hadn't been addressed yet.
Aug
5
comment Update on view over partitioned tables updating all indexes
@user3789393 Always remember the costs (and percentages) seen in plans are always optimizer estimates based on a model. Always. Even in actual plans. The optimizer uses these costs to choose between strategies. There are ways to get a feel for actual performance, e.g. SET STATISTICS IO ON - reflected in the Table I/O tab of Plan Explorer but only when the query is run directly from P/E. Sorry there are lots of details in this area.