28,280 reputation
771121
bio website
location United Kingdom
age
visits member for 3 years
seen 10 hours ago

Email: stackoverflowmartinsmith@gmail.com


Jun
30
comment T-SQL query using completely different plan depending on number of rows I'm updating
You say the TOP 50 never finishes so I guess you can't get the actual execution plan. Can you try some smaller number e.g. TOP 10 with a USE PLAN hint to get it to use the bad plan and let us know the estimated vs actual rows coming out of the join on t.TimeValue between sma.StartDate and sma.enddate?
Jun
30
comment T-SQL query using completely different plan depending on number of rows I'm updating
The dta prefixed index just looks like it was created by following a DTA recommendation without customising the name. Hypothetical indexes can't appear in actual execution plans (and won't in estimated either without some undocumented commands). Not sure how your second suggestion would work. t.TimeValue between sma.StartDate and sma.enddate is correlated so can change for each row in the #temp table. What would the OP replace it with?
Jun
29
comment T-SQL query using completely different plan depending on number of rows I'm updating
@usr joining on t.TimeValue between sma.StartDate and sma.enddate might end up generating a lot more useless rows that later get filtered out in the join against FactSubscriber and so don't end up in the final result.
Jun
29
revised Slow Performance Inserting Few Rows Into Huge Table
Deleted repeated paragraph
Jun
29
comment T-SQL query using completely different plan depending on number of rows I'm updating
The second one is a wide (per index) update plan rather than a narrow (per row) one which explains some of the extra visible complexity. But really the only difference is join order from #mac sma join f on f.macid = sma.macid join dimTime t on f.TimeID = t.TimeID and t.TimeValue between sma.StartDate and sma.enddate vs from #mac join t on t.TimeValue between sma.StartDate and sma.enddate join f on f.TimeID = t.TimeID and f.macid = sma.macid
Jun
29
comment How does SQL Server choose an index key for a foreign key reference?
I asked a related question about this on Stack Overflow Will FK validation in SQL Server always use the index specified in sys.foreign_keys?
Jun
28
comment Parameter sniffing work arounds
@AbeMiessler - No the end results are not the same, reassigning to variables works the same as OPTIMIZE FOR (UNKNOWN). The statement is compiled without looking at specific parameter values at all (except if the statement ends up getting a recompile anyway in which case the values of the variable will be used)
Jun
28
comment SQL Server 2008R2 - Why is my index not used
@jimw - You are updating the data to empty string as well not just the query I assume? If so what if you try and update the statistics on the table (though updating 50% of the rows should have triggered an update on these anyway)
Jun
28
comment SQL Server 2008R2 - Why is my index not used
No I've no idea on the NULL/ empty string one either. I presume that it must alter the estimate of number of rows that will match the predicate for some reason. If you do a SELECT COUNT(*) FROM ... WHERE COLUMN_A IS NULL AND COLUMN_B IS NULL are the estimated rows different from the case where the data is empty strings and the query is SELECT COUNT(*) FROM ... WHERE COLUMN_A = '' AND COLUMN_B = '' ? Maybe you can show us the dbcc show_statistics for the two cases.
Jun
28
comment SQL Server 2008R2 - Why is my index not used
I guess if half of the rows match the predicate SQL Server will assume that it only has to scan 2 rows whereas in fact the rows that do match are highly correlated with id and the first one only appears after scanning half the table.
Jun
28
comment SQL Server 2008R2 - Why is my index not used
What if you change the query to just select the indexed columns rather than *? Also you might find using tables as queues useful.
Jun
28
comment syscacheobjects is cleared randomly
Anything logged to the error log when this occurs?
Jun
27
comment Efficient INSERT INTO a Table With Clustered Index
@SQLKiwi - Looks like in 2012 the optimiser would now ignore the explicit sort anyway
Jun
26
comment Suppress SQL Server Management Studio auto complete
@AaronBertrand - Sounds much better. The partial match will be handy as often I remember part of the name of an obscure DMV but am not quite sure what the prefix is.
Jun
26
comment Suppress SQL Server Management Studio auto complete
@AaronBertrand - Does hitting space or comma still auto complete? I find that very annoying as I'm used to having to tab for that in other environments and 95% of the time it auto completes to something that I then need to delete the end of.
Jun
26
comment Will a delete then insert within a transaction cascade?
Postgres has deferred constraints but not deferred cascades and that is as per SQL Standard according to here
Jun
26
comment A Script to insert dummy Data in all tables of Database
Also I would prefer set based inserts rather than RBAR and a "nice to have" would be an option to configure column selectivity.
Jun
25
comment How to identify which query is filling up the tempdb transaction log?
@AaronBertrand - That makes sense.
Jun
25
comment How to identify which query is filling up the tempdb transaction log?
@BenThul - I ran the query in another connection. The @@SPID is <> not =. dm_db_task_space_usage reports 0 for the spid with the open transaction for all columns for me. Wonder if you need to query it when the request is actually executing rather than idle with an open transaction.
Jun
25
comment How to identify which query is filling up the tempdb transaction log?
Hmm, Just tested that and it hasn't found my offending session even though the session_id shows up with the following query SELECT database_transaction_log_bytes_reserved,session_id FROM sys.dm_tran_database_transactions tdt JOIN sys.dm_tran_session_transactions tst ON tdt.transaction_id = tst.transaction_id WHERE database_id = 2. The query I was expecting to find was after running the following BEGIN TRAN CREATE TABLE #T(X CHAR(8000)) INSERT INTO #T SELECT name FROM sys.objects