Here's the table definition. There are no indexes nor constraints on it.
CREATE TABLE #Action
(Project_Id INTEGER NOT NULL,
Agent VARCHAR(64) NOT NULL,
dCallDate DATE NOT NULL,
Call_No INTEGER NOT NULL,
nWeek INTEGER NOT NULL,
bNesting BIT NOT NULL,
nWasMonitored TINYINT NOT NULL)
I do a bunch of inserts, adding up to 225K rows. On each insert, I'm seeing logical reads on that table.
I don't understand why?
Thanks!
June 14, 2021 at 4:33 pm
Where are you seeing logical reads? How are you inserting data into the table?
If you are using a query - then that query must read the data, therefore you are going to get logical reads and possibly physical reads on every insert.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 14, 2021 at 4:54 pm
I could have been clearer: the logical reads are on the target of the INSERT.
It's an INSERT #Action/EXECUTE dbo.sp_executeSQL, inside a cursor. Could it be because it's dynamic SQL?
I SET STATISTICS IO ON:
Table.... #Action. Scan count 0, logical reads 1474, physical reads 0, read-ahead reads 0, lob logical reads
I get one of these for each loop of the cursor, of varying reads on #Action, depending on the rows. #Action is not referenced in the query/dynamic SQL.
June 14, 2021 at 5:59 pm
Well SQL Server still has to access the table to do the inserts which is IO.
June 14, 2021 at 6:57 pm
What is the reason you are concerned about those logical reads? What issue are you trying to resolve - or is this just educational?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
I agree with ZZartin and even have a sample script that replicates this behavior:
CREATE TABLE #tmpTable (id INT)
GO
SET STATISTICS IO ON
INSERT INTO [#tmpTable]
(
[id]
)
VALUES
(
0-- id - int
)
INSERT INTO [#tmpTable]
(
[id]
)
VALUES
(
0-- id - int
)
INSERT INTO #tmpTable
SELECT 1
UNION
SELECT 2
SET STATISTICS IO OFF
GO
DROP TABLE [#tmpTable]
GO
Creates temp table, turns statistics IO on, does 3 inserts, then cleans itself up. During the first insert, 1 logical read and 1 row inserted. Second insert, 1 logical read and 1 row inserted. 3rd insert, 2 logical reads and 2 rows inserted.
My guess without having access to SQL internals is that the insert needs to read the page out before doing the insert. So when you are seeing 1474 logical reads, it is reading 1474 pages into memory, modifying them with the new data and writing them back to disk.
Now, one thing I'd be curious about is if that cursor and dynamic SQL is actually required or if it could be written without either the cursor or the dynamic SQL. Cursors are generally slower bits of code and dynamic SQL is generally a dangerous bit of code. Not always though; both do have their place, but in general I try to avoid them.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 15, 2021 at 11:12 am
Thank you all.
This was a purely educational matter.
I share your concerns re: cursors and dynamic SQL, but sometimes there is no other way.
At any rate, this report will likely run off-hours, not more than once-a-day.
Cheers!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply