27,037 reputation
660112
bio website
location United Kingdom
age
visits member for 2 years, 9 months
seen 1 hour ago

Email: stackoverflowmartinsmith@gmail.com


Jun
5
revised Using the correct database when calling a system stored procedure in SQL Server 2008
added 329 characters in body
Jun
5
comment Using the correct database when calling a system stored procedure in SQL Server 2008
@PetterBrodin - Yes it does. I've tested it in 2008. Are you sure you changed the context back to mydb after marking it as a system object?
Jun
5
answered Using the correct database when calling a system stored procedure in SQL Server 2008
Jun
5
comment sql server , set N prefix as unicode for all queries by default
No, there's no way of doing this. A string literal in TSQL without the N prefix is always treated as a varchar in the code page of the default collation.
Jun
5
comment Query pauses after returning a fixed number of rows
@JonofAllTrades - You can filter queries against sys.dm_os_waiting_tasks by session_id and poll it whilst the slow query is running to get a quick idea if it is spending much time in wait states. Extended Events is a much more accurate approach though. Not sure it will reveal much in this case as likely your query is busy working rather than waiting but doesn't hurt to look. Interesting that Total logical reads only go up about 2.5 times from 40K to 102K but CPU Time is 20 times greater and Elapsed Time is 23 times greater. What about actual row count? Is that 20 times too?
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?