network profile
| bio | website | |
|---|---|---|
| location | ||
| age | ||
| visits | member for | 11 months |
| seen | 8 hours ago | |
| stats | profile views | 9 |
|
Apr 29 |
accepted | How to determine cause of runtime increase given two query plans with SpillToTempDb warning |
|
Apr 29 |
comment |
How to determine cause of runtime increase given two query plans with SpillToTempDb warning Accepting this answer. For the record: No hints = ~4s, Merge hints = ~14s (with spills); merge hints with option recompile ~1.5s, hash hints 3s (with hash warning), hash hints with recompile: 1.3s (and pretty consistent). Note that the join order was bad. After improving the join order merge join with option recompile is 1.1s to 1.3s and hash hints with recompile are similar. It seems the recompile is very helpful. When it finds the row estimates are too far off does it try again (that's not how the docs read)? Robust Plan gave ~3s on average for hash. |
|
Apr 27 |
revised |
How to determine cause of runtime increase given two query plans with SpillToTempDb warning Added possible hint |
|
Apr 26 |
revised |
How to determine cause of runtime increase given two query plans with SpillToTempDb warning added tempdb stats screenshot |
|
Apr 25 |
asked | How to determine cause of runtime increase given two query plans with SpillToTempDb warning |
|
Apr 16 |
answered | When should a primary key be declared non-clustered? |
|
Apr 5 |
comment |
What's the difference between a CTE and a Temp Table? One reason CTE's can be employable for performance reasons is because their (possibly forced) materialization could be more easily parallelized whereby temp tables are generally computed serially before their use. |
|
Mar 25 |
accepted | Unable to run union in parallel on SQL Server |
|
Mar 25 |
revised |
Unable to run union in parallel on SQL Server removed distinct from second half of union (mistake) |
|
Mar 25 |
comment |
Unable to run union in parallel on SQL Server Great tips, I tried the 8649 option and it did add much parallelization, unfortunately too much such that the queries came in slower than with maxdop(1). I tried maxdop(2) and the optimizer did not choose to put the parallel operations at the union point but rather in one of the subqueries so I it looks like I would need more control to achieve this. Such as being able to specify maxdop(1) for each side of the union and a hint like "concat parallel union". The extra "distinct" with the group by is just a mistake. I will edit it out. Too big to pre-calc all combinations of possible inputs. |
|
Mar 25 |
comment |
Unable to run union in parallel on SQL Server Aaron, I saw some runtimes come in at .45. I wasn't generating exec plans at the time so can't guarantee that it was actually going parallel and not some other mechanism (such as caching?). However I also commented on your answer. |
|
Mar 22 |
asked | Unable to run union in parallel on SQL Server |
|
Mar 22 |
comment |
Create a plan guide to cache (lazy spool) CTE result Seriously, XML Plans are ignorable?!, I thought that was the whole point? If they are invalid it should throw. |
|
Jan 29 |
awarded | Popular Question |
|
Aug 11 |
comment |
Filtering data ordered by rowversion For the (Id, RV) order not to lose rows, one could calc a min(RV) and max(RV) across a client's Ids whenever the Id's rollover and pass the range back to client as part of state. The client would then pass LastId, LastIdRv, MinRv, MaxRv with each call. The min/max calc should be fast with the (Id, RV) index but sensing the rollover and combining rows for the lastId with the firstId++ within the requested number of rows might require a temp table or maybe a recursive union if that's possible. I don't think the client could seed the search if they lost the min/max values mid-batch. |
|
Aug 9 |
comment |
Filtering data ordered by rowversion Can the client accept results in (Id, Rv) order and provide a LastId argument in addition to LastRowVersion argument to eliminate RV sort across ids? My previous comments were all based on the assumption that RV had duplicates. The filtered index per client looked interesting. |
|
Aug 8 |
awarded | Commentator |
|
Aug 8 |
comment |
Filtering data ordered by rowversion Seems like for GetData() attempt, the order by should include the Id (order by RV, Id). Can you comment on using an index of (Rv, Id)? Also using ">" max rowversion from previous call seems like it will miss records between chunks if rows have the same rowversion (isn't that possible?). |
|
Aug 4 |
answered | Documenting a giant web of inter-related stored procedures in an MS SQL database: What tool or format? |
|
Aug 4 |
awarded | Supporter |
