29,316 reputation
772123
bio website
location United Kingdom
age
visits member for 3 years, 2 months
seen 3 hours ago

Email: stackoverflowmartinsmith@gmail.com


Sep
28
reviewed Approve help required with indexing and query optimzation
Sep
27
comment Who changed/modified stored procedure in SSMS 2005 or 2008?
Yes. I think that the SSMS reports look at the default trace so if they don't show it the info is probably gone (though without checking the code I'm not sure if the SSMS reports look at the historic rolled over files in the default trace directory)
Sep
27
answered SQL Server 2005, Foreign key check against part of a table
Sep
27
comment IN Clause causes Execution plan to change from Nested Loops to Hash Match
@nosilleg - That's a shame because short of forcing the whole plan shape with join hints or a plan guide simplifying the query would seem the easiest way of getting a good plan. You might try looking into the joins where the actual vs estimated number of rows are orders of magnitudes out and see if you can do anything about that (e.g. are you missing any multi column statistics that may be useful)
Sep
27
comment Are these two queries technically equal to each other?
The WHERE you have added turns the outer join back into an inner join. You would need the rewrite in @ypercube's answer with the filter in the ON (and a filter in the where clause to bring back only null extended (unmatched) rows). Table definitions (including column nullability) would definitely be useful here as well.
Sep
26
comment IN Clause causes Execution plan to change from Nested Loops to Hash Match
BTW Am I missing something but why are you doing some of the joins twice? cjl,cjl_d pcjl,pcjl_d s360qjl,s360qjl_d. The _d suffixed ones look to have identical predicates to the ones without the suffix.
Sep
26
comment IN Clause causes Execution plan to change from Nested Loops to Hash Match
@nosilleg - That's interesting. The big cardinality errors don't appear at all in that plan. The join order is different and it is not processing anywhere near the same number of rows (BTW I am pasting this XML into SQL Sentry Plan Explorer and they show up glaringly obviously as big thick lines)
Sep
26
comment IN Clause causes Execution plan to change from Nested Loops to Hash Match
It might still show that. It just means SQL Server didn't have enough time to exhaustively consider all the (many) permutations for your 20 table join. Any chance you could also upload the fast plan for us to look at? I see the hash joins but the big cardinality errors don't seem to affect the build side which is where I would have thought they would be much more of a problem. Wondering if there is some other difference.
Sep
26
comment How do I get the date that each user last logged onto the server / database in SQL Server 2005
@AaronBertrand - Quite impressed that I managed to make two errors in such a small amount of text! Thanks for the corrections.
Sep
26
comment How do I get the date that each user last logged onto the server / database in SQL Server 2005
I don't see this stored anywhere by default. You would need to record it yourself via a DDL login trigger I think (though you could also extract it from the default trace if prepared to lose the info when the trace files rollover)
Sep
26
comment IN Clause causes Execution plan to change from Nested Loops to Hash Match
Could you also supply the entire query text? It is truncated in the plan. I would probably divide this query up into several smaller ones using a temporary table to materialize the intermediate results though.
Sep
26
comment IN Clause causes Execution plan to change from Nested Loops to Hash Match
Thanks for posting the plan. Still having a look at it but I count 22 joins in there and see that StatementOptmEarlyAbortReason="TimeOut". There is also a cardinality error that seems to get worse throughout the plan so at some points it is doing nested loops joins with estimated one execution but actual over 3 million.
Sep
26
comment Msg 15064, 15275, and 15235 when running sp_spaceused
Have you checked the database for corruption with DBCC CHECKDB?
Sep
26
comment LEFT JOIN / WHERE clause with is null and false Estimated Number Of Rows
@AndriyM - Agreed my suggestion would change the semantics. Will delete!
Sep
25
reviewed Approve IN Clause causes Execution plan to change from Nested Loops to Hash Match
Sep
25
comment Store categories in database or use hard coded integers?
You could equally well enforce this via a check constraint as an FK constraint though. The second table approach tends to be more convenient for querying and updating IMO but not everyone is a fan
Sep
25
comment IN Clause causes Execution plan to change from Nested Loops to Hash Match
What is AnIDColumn? This doesn't exist as a column in your CTE so the query you posted would be invalid. Please give your actual query. Also definition of [AView] would be good.
Sep
25
revised IN Clause causes Execution plan to change from Nested Loops to Hash Match
added 104 characters in body
Sep
25
comment Can I change table structure in a transaction and then roll it back if there is an error?
Are you talking about Redgate SQL Compare? One of the synchronization options there is to use transactions IIRC so you could look at the generated script there to see some boiler plate code for this.
Sep
25
answered Can I change table structure in a transaction and then roll it back if there is an error?