208 reputation
27
bio website sqlsoft.co.uk
location
age
visits member for 3 years, 7 months
seen Dec 4 '13 at 0:40

Sep
5
awarded  Nice Question
Mar
28
awarded  Notable Question
Mar
5
awarded  Yearling
Mar
5
awarded  Popular Question
Feb
10
answered Performance differences between executing via Linked Server vs. SSMS
Feb
5
awarded  Student
Feb
5
awarded  Scholar
Feb
5
awarded  Commentator
Dec
28
awarded  Teacher
May
31
comment Optimising join on large table
Thanks - that's a mighty big table.
May
31
comment Optimising join on large table
@Quick Joe Smith - the more you toy with database optimization, the more you learn, it's a process of eroding your misconceptions - I know it was for me. :)
May
31
comment Optimising join on large table
@Quick Joe Smith - thanks for the sp_spaceused. 75GB of index and 18GB of data - is ix_hugetable not the only index on the table?
May
31
comment Optimising join on large table
But there's more to it than this. If #smalltable had a large number of rows then a merge join might be appropriate. If, as it's name suggests, it has a small number of rows then a loop join could be the right choice. Imagine #smalltable had one or two rows, and matched vs. a handful of rows from the other table - it would be hard to justify a merge join here.
May
31
comment Optimising join on large table
Could you include the result of sp_spaceused 'dbo.hugetable', please?
May
31
answered Optimising join on large table
May
20
comment How to improve sql server 2008 table scan speed?
Please attach the output of DBCC SHOWCONTIG(yourtablename) WITH TABLERESULTS
May
17
comment Error Msg 823, Level 24, State 2, Line 1
Have you tried the suggested DBCC CHECKDB? Also, verify the backup with RESTORE VERIFYONLY - msdn.microsoft.com/en-us/library/ms189587.aspx
Jan
28
awarded  Supporter
Jul
8
accepted SQL Server - Temporary vs. Physical Tables
Jul
4
comment SQL Server - Temporary vs. Physical Tables
Looks useful - thanks SPE109, I'll check into this and make recommendations to our DBA as appropriate.