Reputation
Top tag
Next privilege 200 Rep.
See reduced ads
Badges
1 7
Newest
 Critic
Impact
~3k people reached

Apr
9
comment Understanding below excecution plan
The "Forced Index" property would be true only if you used the INDEX table hint (e.g., "WITH (INDEX(1))" to force SQL Server to use a particular index for that table. ForceScan true only if you used the FORCESCAN table hint. ForceSeek true only if you used the FORCESEEK table hint. See msdn.microsoft.com/en-us/library/ms187373.aspx for more info on table hints. It's likely that you are not generally going to be using these hints and can therefore typically ignore these properties in the query plan.
Apr
9
comment Understanding below excecution plan
To answer your question about why the table scan on 1,000,000 rows is just 2% and the index seek on 500 rows is 98%, that index seek is on the inner side of the nested loop, meaning that it is happening 1,000,000 times, and it is the cumulative cost of those 1,000,000 seeks that is estimated to be 98% of the plan cost. (You can see the "number of executions" in your screenshot hovering over the index seek operator to confirm this.) For this reason, it's likely that a plan that uses just one pass over each table (such as a hash join with tb2 on the build side) would be much more efficient.
Feb
26
comment Simple avg query on large table much slower in PostgreSQL than SQL Server
It's not clear why the difference might be so extreme, but if you have multiple cores on the machine and this is the only query you are running, I would expect SQL Server to be much faster. Postgres will not use more than 1 core for a single query, whereas SQL Server could be using 8 or more cores for the query depending on your hardware and MAXDOP settings. At a glance, this looks like a query that SQL Server would be able to parallelize quite efficiently. So this might be at least one of the factors explaining the difference you are seeing.
Feb
9
awarded  Critic
Feb
4
comment Why and how sometimes I get the “absent table error” while there are definitely Sch-M lock?
I ran the test scripts as READ COMMITTED originally, but also thought the isolation level might matter. However, it turns out that the error occurs even with SERIALIZABLE.
Feb
3
answered Why and how sometimes I get the “absent table error” while there are definitely Sch-M lock?
Jan
16
awarded  Teacher
Jan
16
answered Looking for a set-based solution for a rolling 30-day period
Dec
8
awarded  Notable Question
Jul
1
comment Does SQL Server allow INSERTs into tables and indexes to be written in parallel?
Thanks for the thorough discussion. The new SQL 2014 SELECT INTO parallelism is interesting. It would certainly be useful to have that parallelism capability expanded to handle INSERT...SELECT in parallel as well, at least in cases with TABLOCK where minimal logging is enabled.
Jul
8
awarded  Nice Question
Apr
22
awarded  Popular Question
Mar
14
awarded  Supporter
Sep
14
comment SQL Server does not optimize parallel merge join on two equivalently partitioned tables
Thanks Paul! Great information here, and it certainly addresses the question in detail. We are in a mixed SQL 2008/2012 environment, but I will consider exploring the column-store further for the future. Of course, I still wish SQL Server could effectively leverage a parallel merge join--and the much lower memory requirements it could have--in my use case :) I filed the following Connect issue in case anybody cares to take a look and comment or vote on it: connect.microsoft.com/SQLServer/feedback/details/759266/…
Sep
14
awarded  Scholar
Sep
14
awarded  Student
Sep
14
comment SQL Server does not optimize parallel merge join on two equivalently partitioned tables
Thanks Paul! Great information here, and it certainly addresses the question in detail.
Sep
14
accepted SQL Server does not optimize parallel merge join on two equivalently partitioned tables
Aug
9
asked SQL Server does not optimize parallel merge join on two equivalently partitioned tables