Reputation
328
Top tag
Next privilege 500 Rep.
Access review queues
Badges
2 8
Newest
 Yearling
Impact
~3k people reached

  • 0 posts edited
  • 0 helpful flags
  • 50 votes cast
May
21
comment Is it possible to have a deadlock in this situation
That makes sense to me if we assume that the two processes are processing the same items in the same order. If for some reason the two processes may be updating the items in a different order (i.e., each may have built the "list" using it's own separate logic), then you could also have another deadlock situation where each process has already updated a row (but not yet committed the transaction) that the other process will later need to update.
May
21
comment In Sql Server, is there a way to check if a selected group of rows are locked or not?
You might also look into LOCK_TIMEOUT (msdn.microsoft.com/en-us/library/ms189470.aspx). For example, this is the way that Adam Machanic's sp_whoisactive ensures that the procedure doesn't wait for too long if it is blocked when trying to gather an execution plan. You can set a short timeout or even use a value of 0 ("0 means to not wait at all and return a message as soon as a lock is encountered.") You can combine this with a TRY/CATCH to catch error 1222 ("Lock request time out period exceeded") and proceed to the next batch.
May
20
comment Retreive data EXEC SQL for temp table?
The way I understood your question, you can simply use "select * into #temp from datatable where id = 1". But maybe I misunderstood. Can you clarify your question if so?
May
15
answered How do I merge two rows only if there is the TagWidgetIds are same but the tagValues are different?
May
14
answered Finding all joins required to programmatically join a table
May
13
awarded  Yearling
May
12
answered Getting a realistic query plan when partitioning with low volumes of data
May
12
awarded  Commentator
May
12
comment Getting a realistic query plan when partitioning with low volumes of data
Yes, the problem appears to be fixed by the new Cardinality Estimator in SQL 2014. However, if you run with OPTION (QUERYTRACEON 9481) to use the old Cardinality Estimator, you should be able to reproduce the problem in SQL 2014 EE.
Apr
27
comment String or binary data would be truncated
In addition to the correct answer below about CHAR(1), you might want to be aware that REPLICATE silently truncates data to the first 8000 characters (at least when passing in a VARCHAR string without an explicit cast to VARCHAR max). So replicate('a',8053) actually returns just 8000 characters, not 8053 as you might expect. See BOL: msdn.microsoft.com/en-us/library/ms174383.aspx
Apr
21
comment Index to filter a view before joining the underlying tables
Another option you could test out if you need to optimize this query and are not concerned with the overhead of adding non-clustered indexes on cms_usersettings is to add two indexes, one on UserSettingUserId and another on UserPasswordRequestHash. This might allow SQL Server to efficiently identify the rows (or probably just one row) for each predicate and then concatenate those results before needing to perform any join or key lookup.
Apr
9
comment Understanding below execution 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 execution 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