26,977 reputation
659111
bio website
location United Kingdom
age
visits member for 2 years, 9 months
seen 3 mins ago

Email: stackoverflowmartinsmith@gmail.com


Jun
5
awarded  Nice Answer
Jun
4
comment Query pauses after returning a fixed number of rows
What is SET STATISTICS IO ON;SET STATISTICS TIME ON; output for TOP 41 and TOP 44? Does sys.dm_os_waiting_tasks show any waits? - Or use extended events to focus in on a single SPID. If nothing there can you provide execution plans?
Jun
4
revised Schema qualification necessary for query plan reuse?
added 68 characters in body
Jun
4
revised Schema qualification necessary for query plan reuse?
added 585 characters in body
Jun
4
comment Schema qualification necessary for query plan reuse?
@Bort - Actually I've just realised that it depends on the default schema not just the user_id. In my original test I had the second user WITH DEFAULT_SCHEMA=[guest]. As long as two different users have the same default schema it appears they can share each other's plans.
Jun
4
revised Schema qualification necessary for query plan reuse?
edited title
Jun
3
revised Unexpected scans during delete operation using WHERE IN
Maths the wrong way round
Jun
3
comment Unexpected scans during delete operation using WHERE IN
Is there any way of seeing the row goal used in the plan? (After reading your QO deep dive series wondering if there is a trace flag for that!)
Jun
3
revised Unexpected scans during delete operation using WHERE IN
No InRow attribute. Pushed down to scan not storage engine
Jun
2
comment Unexpected scans during delete operation using WHERE IN
@MarkStorey-Smith - I think that possibly this is the "containment assumption" mentioned in SQL Kiwi's answer here at work.
Jun
2
comment Unexpected scans during delete operation using WHERE IN
@MarkStorey-Smith - I'm not sure how valid my 900 row figure actually is as the plan doesn't expose anywhere how many rows it expects the storage engine to process so I had to get it by the calculation in my answer. I think there might be some fixed cost per operator it adds on as well rather than scaling linearly with row count. From playing around with various numbers here SELECT TOP 147 BrowserID FROM [dbo].[tblFEStatsPaperHits] gives the closest subtree cost to 0.003911592 at 0.0039113
Jun
2
revised Unexpected scans during delete operation using WHERE IN
added 930 characters in body
Jun
2
revised How do you kick users out of a SQL Server 2008 database?
added 16 characters in body
Jun
2
answered Unexpected scans during delete operation using WHERE IN
Jun
2
answered Why does SQL Server “Compute Scalar” when I SELECT a persisted computed column?
Jun
2
comment Does the order of columns in a table's definition matter?
+1 I agree. I've always found that within each section the order of the columns initially is as per the CREATE TABLE statement (except that CI key columns do come first in the section). Though the order of the columns can change if ALTER COLUMN changes datatypes/column lengths. The only minor case where it matters that I can think of is that columns at the end of the variable length section with empty string or NULL take no space at all in the column offset array (demonstrated by Kalen Delaney in the 2008 internals book)
Jun
1
comment Why does SQL Server “Compute Scalar” when I SELECT a persisted computed column?
@ypercube - SELECT definition FROM sys.computed_columns is probably what needs to match.
Jun
1
comment Why does SQL Server “Compute Scalar” when I SELECT a persisted computed column?
So I presume that at some stage in optimisation / algebrization it recognizes that id5p is the same formula as id5 and associates these two columns together. Which ever one it encounters first becomes the primary source for the other column.
Jun
1
comment Why does SQL Server “Compute Scalar” when I SELECT a persisted computed column?
And if the order of id5 and id5p are reversed in the CREATE TABLE statement it uses the persisted value too so the non use of the persisted value seems to happen if there is a non persisted computed column with the same formula earlier in column ordinal order even if that column does not appear in the query. Edit - Just realised that is what @ypercube found as well.
Jun
1
comment Why does SQL Server “Compute Scalar” when I SELECT a persisted computed column?
And if id5 is removed from the table it seems to use the persisted value as well. It is the presence of the non persisted column with the same formula that changes things.