October 20, 2022 at 4:01 pm
I am playing with extended events. I noticed that when I do a select on table A there are numerous locks on tables B, C, and D. B, C, and D are related to A, but I don't understand the need for those additional locks.
Extra credit for telling me where the search function is for this forum. I would search for similar questions if I knew. The only search box I see appears to be for Data Pass 2022.
<><
Livin' down on the cube farm. Left, left, then a right.
October 20, 2022 at 5:06 pm
"It Depends". Are there any computed columns or Foreign Keys associated with those other tables?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2022 at 5:08 pm
The "Search functionality" you speak of is in the upper right of this page where it says "Find out more -->" . Type something between that and the magnifying glass and then click the magnifying glass.
The "Search functionality" on forums usually isn't great. You might do better with a Google search with a site hint.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2022 at 6:29 pm
SQL will take only those locks needed to guarantee consistency and accuracy in results. As Jeff said, the details of that depend, but that is the general approach.
Is A an actual table or is it a view? Naturally a view could reference many tables.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 20, 2022 at 6:36 pm
Check out the picture of the banner. The ad and the search look like one object.
Foreign keys in the file. Good call Jeff. (I have not been active in this forum in a long time. Nice to see you are still here offering support Jeff.)
<><
Livin' down on the cube farm. Left, left, then a right.
October 21, 2022 at 3:20 pm
You sure it's a table and not a function or a view? A simple select on one table shouldn't place locks on a second table, even if there are foreign key constraints to those other tables.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 21, 2022 at 6:36 pm
Check out the picture of the banner. The ad and the search look like one object.
Foreign keys in the file. Good call Jeff. (I have not been active in this forum in a long time. Nice to see you are still here offering support Jeff.)
Nice seeing you around again, Tobar. It has been awhile. And thank you for the kind words.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 21, 2022 at 6:37 pm
You sure it's a table and not a function or a view? A simple select on one table shouldn't place locks on a second table, even if there are foreign key constraints to those other tables.
Another good call! I made the assumption that the OP had proven that it was a table. Bad on me.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 23, 2022 at 11:21 am
Yet again it would be helpful if we could see the actual execution plan!
😎
October 24, 2022 at 1:26 pm
Yet again it would be helpful if we could see the actual execution plan! 😎
Amen to that!
--Jeff Moden
Change is inevitable... Change for the better is not.
October 24, 2022 at 7:15 pm
I am flattered to have piqued the interest of some SSC heavy hitters. I do not have an execution plan for you. I am not even certain what it should be on, so if you have a suggestion.
I am showing the 4 tables involved (A, B, C, D). a 5th table is referenced (Z) but I don't think it is relevant. I am a bit embarrassed to see that, when I created these tables 6+ years ago, I declared the "COMMON_ID" alternatively as varchar[25], int, and varchar[10]. Code has been working 6+years, so I guess I got lucky.
CREATE TABLE A(
COMMON_ID [int] NOT NULL,
[Code] [varchar](10) NULL,
[Value] [varchar](40) NULL,
[Datestamp] [date] NULL
) ON [PRIMARY]
GO
ALTER TABLE A WITH NOCHECK ADD CONSTRAINT [FK_A_C] FOREIGN KEY(COMMON_ID)
REFERENCES C(COMMON_ID)
GO
ALTER TABLE A NOCHECK CONSTRAINT [FK_A_C]
GO
CREATE TABLE B(
COMMON_ID [varchar](10) NULL,
[TimeStamp] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE C(
COMMON_ID [int] NOT NULL,
*** [varchar](64) NULL,
*** [varchar](64) NULL,
*** [varchar](4) NULL,
*** [varchar](1) NULL,
*** [varchar](20) NULL,
*** [int] NULL,
*** [int] NULL,
*** [float] NULL,
*** [float] NULL,
*** [float] NULL,
*** [float] NULL,
*** [varchar](30) NULL,
*** [float] NULL,
*** [varchar](40) NULL,
[ReferenceID] [int] NULL,
*** [varchar](2) NULL,
*** [int] NULL,
*** [datetime] NULL,
*** [int] NULL,
*** [datetime] NULL,
*** [datetime] NULL,
*** [int] NULL,
*** [datetime] NULL,
*** [datetime] NULL,
CONSTRAINT [PK_C] PRIMARY KEY CLUSTERED
(
COMMON_ID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE C WITH NOCHECK ADD CONSTRAINT [FK_A_Z] FOREIGN KEY([ReferenceID])
REFERENCES Z ([RecordIDZ)
NOT FOR REPLICATION
GO
ALTER TABLE C NOCHECK CONSTRAINT [FK_A_Z]
GO
CREATE TABLE D(
COMMON_ID [varchar](25) NOT NULL,
[Code] [varchar](25) NULL,
[Value] [varchar](25) NULL,
[Datestamp] [date] NULL
) ON [PRIMARY]
GO
I grabbed the event stuff straight off of learn.microsoft. I modified it for my tables.
-- Find objects in a particular database that have the most
-- lock acquired. This sample uses AdventureWorksDW2012.
-- Create the session and add an event and target.
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='LockCounts')
DROP EVENT session LockCounts ON SERVER;
GO
DECLARE @dbid int;
SELECT @dbid = db_id('*********');
DECLARE @sql nvarchar(1024);
SET @sql = '
CREATE event session LockCounts ON SERVER
ADD EVENT sqlserver.lock_acquired (WHERE database_id ='
+ CAST(@dbid AS nvarchar) +')
ADD TARGET package0.histogram(
SET filtering_event_name=''sqlserver.lock_acquired'',
source_type=0, source=''resource_0'')';
EXEC (@sql);
GO
ALTER EVENT session LockCounts ON SERVER
STATE=start;
GO
-- Create a simple workload that takes locks.
USE Destrehan;
GO
SELECT TOP 1 * FROM dbo.A;
GO
SELECT
c.name,
Count(*) AS [Count-Per-Column-Repeated-Name]
FROM
sys.syscolumns AS c
JOIN sys.sysobjects AS o
ON o.id = c.id
WHERE
o.type = 'V'
AND
c.name like '%event%'
GROUP BY
c.name
HAVING
Count(*) >= 2 --Try 2 and 3 I DID BOTH
ORDER BY
c.name;
-- The histogram target output is available from the
-- sys.dm_xe_session_targets dynamic management view in
-- XML format.
-- The following query joins the bucketizing target output with
-- sys.objects to obtain the object names.
SELECT name, object_id, lock_count
FROM
(
SELECT objstats.value('.','bigint') AS lobject_id,
objstats.value('@count', 'bigint') AS lock_count
FROM (
SELECT CAST(xest.target_data AS XML)
LockData
FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
JOIN sys.server_event_sessions ses ON xes.name = ses.name
WHERE xest.target_name = 'histogram' AND xes.name = 'LockCounts'
) Locks
CROSS APPLY LockData.nodes('//HistogramTarget/Slot') AS T(objstats)
) LockedObjects
INNER JOIN sys.objects o ON LockedObjects.lobject_id = o.object_id
WHERE o.type != 'S' AND o.type = 'U'
ORDER BY lock_count desc;
GO
-- Stop the event session.
ALTER EVENT SESSION LockCounts ON SERVER
state=stop;
GO
Here are the results
name object_id lock_count
D 614810585 11
C 6808419 6
B 1058167611 3
A 2106291896 1
Thanks for the assist.
Tobar.
(You're an idiot is an acceptable answer but please point out why.)
<><
Livin' down on the cube farm. Left, left, then a right.
October 25, 2022 at 12:07 am
I guess maybe I'm the idiot. I don't know extended events at all but I'm not seeing anything in that code that tells you those locks are from JUST the select that you're talking about. I'll get the popcorn and watch as someone that knows about EE prove that I'm the idiot that I think I might be here.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 25, 2022 at 11:09 am
From what I read, and think I understood, this code is creating a watchdog that looks for SQL statements that contain the "Having" clause. When it sees that clause, it checks for tables with locks. I am wondering if I forgot a piece. I thought it was more obvious that the "having" clause was being searched for.
I would appreciate others chiming in that are smarter than me, (and evidently Jeff Moden 😉 ).
<><
Livin' down on the cube farm. Left, left, then a right.
October 25, 2022 at 11:19 am
I got confused somewhere. Here is the code that should have been used to create the event. I must have combined 2 of the practices. I have no idea what the results should be. Alas, I do not have time to recreate what I did. Maybe later ... Thanks All. - Tobar
CREATE EVENT SESSION [YourSession]
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
ACTION(sqlserver.sql_text)
WHERE
( [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text], N'%SELECT%HAVING%')
)
)
ADD TARGET package0.event_file
(SET
filename = N'C:\Junk\YourSession_Target.xel',
max_file_size = (2),
max_rollover_files = (2)
)
WITH (
MAX_MEMORY = 2048 KB,
EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 3 SECONDS,
MAX_EVENT_SIZE = 0 KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = OFF,
STARTUP_STATE = OFF
);
GO
<><
Livin' down on the cube farm. Left, left, then a right.
October 25, 2022 at 12:47 pm
Jeff's on target from what I can see. I don't see both queries and locks there in a way that you can accurately associate them with one another.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply