Tag Info

Hot answers tagged

7

is it possible to view the locks, along with the type, acquired during the execution of a query? Yes, for determining locks, You can use beta_lockinfo by Erland Sommarskog beta_lockinfo is a stored procedure that provides information about processes and the locks they hold as well their active transactions. beta_lockinfo is designed to gather as ...


3

The short answer is that no, the same SQL doesn't automatically lead to the same execution plan. For example, one may have a small table and the optimizer may realise that there's no point in using an index since an FTS (Full Table Scan) would be cheaper than trawling through an index and then performing lookups. The "No" answer is the reason that Oracle ...


3

It looks like SQL Server is generating a parameterized query plan that can work for any value of @CustomerPartitionKey. In order to do so, it seems to treat @CustomerPartitionKey as both a partition and a column you are seeking upon. If we take a look at the query plan where we have the bad estimate (3000 rows estimated, 300000 actual), we see that there ...


3

I can reproduce the bad plan. I found three workarounds: OPTION (RECOMPILE) INNER LOOP JOIN hints A nasty, crazy rewrite: . SELECT y.* FROM (VALUES (@CustomerPartitionKey)) x(CustomerPartitionKey) CROSS APPLY ( SELECT --TOP 300 CI.ContactId, I.Ordinal, I.Identifier FROM #identifiers I INNER JOIN ...


2

Here is how I look at locks by process/table/lock type: SELECT HostName, "OS UserName", Login, spid, "Database", TableID, "Table Name_________", IndID, -- [Index Name], "Lock Type", "Lock Mode", Status, -- Resource, Count(*) AS "Lock Count" FROM ( SELECT Convert(VarChar(30), ...


2

When running the execution plan, I noticed that each one of the 5 or so queries that are parsing the filter data are costing about 12% which is over 60% of the query just to determine the data we are going to be filtering by. The query costs are based on estimates even in the actual execution plans. They do not tell you how efficient the query ...


1

Since it seems clear that a statistic already exists for the particular column, I offer two possibilities: Object Explorer is pointed to a different database - I'm suspicious because the database has DEV in the name, so it's possible that in one case you're looking at dev and in another you're not. The plan being used is from before the statistic was ...


1

You can view the locks for a session using sp_lock or sys.dm_tran_locks. In both ways you can filter by the session. You can also use Extended Events to do that.



Only top voted, non community-wiki answers of a minimum length are eligible