May 23, 2017 at 12:27 am
Hi Experts,
we have a table which stores documents and have about 100,000 records currently. The table will have about 400 new records every day and showing high PAGEIOLATCH_EX for Insert into this table. The document size can range from few KB to max 10MB. Is this a concern for me. The database is in seperate disk which is a Virtual Machine.
May 23, 2017 at 7:43 am
"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
May 24, 2017 at 2:07 am
Grant Fritchey - Tuesday, May 23, 2017 7:43 AMAn insert requires that it do an exclusive lock on a page in order to complete the insert. That's how it works, by design. There doesn't sound like anything wrong here. A 100k row table is very small. 400 rows a day is a very light load. If there are issues, focus on ensuring that your queries are well written and that the indexes on the table are structured to support the inserts (basically, that your clustered key which defines the storage isn't on a column that would lead to lots of page splits on an insert).
Thanks Grant
May 24, 2017 at 2:21 am
Grant Fritchey - Tuesday, May 23, 2017 7:43 AMAn insert requires that it do an exclusivelocklatch on a page in order to complete the insert.
PAGELATCH_EX != PAGEIOLATCH_EX
The latch needed for inserts is PAGELATCH_EX.
PAGEIOLATCH_EX is a latch taken when the page is being read into memory so that the insert can occur. If you're seeing these high, there's probably not enough memory for the database to keep the busy portions of the database in memory. consider adding memory or tuning queries/indexes so that they read only the portions of the tables that they need.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 24, 2017 at 3:19 am
GilaMonster - Wednesday, May 24, 2017 2:21 AMGrant Fritchey - Tuesday, May 23, 2017 7:43 AMAn insert requires that it do an exclusivelocklatch on a page in order to complete the insert.PAGELATCH_EX != PAGEIOLATCH_EX
The latch needed for inserts is PAGELATCH_EX.
PAGEIOLATCH_EX is a latch taken when the page is being read into memory so that the insert can occur. If you're seeing these high, there's probably not enough memory for the database to keep the busy portions of the database in memory. consider adding memory or tuning queries/indexes so that they read only the portions of the tables that they need.
Thanks a lot Gail.
Below is the TSQL ,the table have seperate NC indexes for ExternalID and DocID
INSERT [dbo].[ExternalDocument]([ExternalID], [DocID], [Retrievable], [Filename],
[IdToDocSystem], [StatusID], [AttachedFile], [UserID],
[CreationDate], [LastUpdateUserID], [LastUpdateDT], [SentDate], [SentUserID],
[IsMobUpload], [UploadRemarks], [EStatus], [Comment])
VALUES (@0, @1, NULL, @2, NULL, @3, @4, @5, @6, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
May 24, 2017 at 3:27 am
Ok, and you posted it why?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 24, 2017 at 3:32 am
GilaMonster - Wednesday, May 24, 2017 3:27 AMOk, and you posted it why?
😀 Thought you can give me some more help on that. Do I need to create indexes on the other columns using variable?
May 24, 2017 at 4:34 am
Errr...
PAGEIOLATCH_EX is a latch taken when the page is being read into memory so that the insert can occur. If you're seeing these high, there's probably not enough memory for the database to keep the busy portions of the database in memory. consider adding memory or tuning queries/indexes so that they read only the portions of the tables that they need.
Queries/indexes in general. Inserts don't use indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 24, 2017 at 4:36 am
GilaMonster - Wednesday, May 24, 2017 4:34 AMErrr...PAGEIOLATCH_EX is a latch taken when the page is being read into memory so that the insert can occur. If you're seeing these high, there's probably not enough memory for the database to keep the busy portions of the database in memory. consider adding memory or tuning queries/indexes so that they read only the portions of the tables that they need.
Queries/indexes in general. Inserts don't use indexes.
Thanks Gail .. My bad
May 24, 2017 at 5:39 am
GilaMonster - Wednesday, May 24, 2017 4:34 AMErrr...PAGEIOLATCH_EX is a latch taken when the page is being read into memory so that the insert can occur. If you're seeing these high, there's probably not enough memory for the database to keep the busy portions of the database in memory. consider adding memory or tuning queries/indexes so that they read only the portions of the tables that they need.
Queries/indexes in general. Inserts don't use indexes.
Well, not exactly. They sure do add rows to 'em during the inserts. There's just not a seek or scan of the index during that part of the processing.
"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
May 24, 2017 at 6:20 am
Grant Fritchey - Wednesday, May 24, 2017 5:39 AMGilaMonster - Wednesday, May 24, 2017 4:34 AMErrr...PAGEIOLATCH_EX is a latch taken when the page is being read into memory so that the insert can occur. If you're seeing these high, there's probably not enough memory for the database to keep the busy portions of the database in memory. consider adding memory or tuning queries/indexes so that they read only the portions of the tables that they need.
Queries/indexes in general. Inserts don't use indexes.
Well, not exactly. They sure do add rows to 'em during the inserts. There's just not a seek or scan of the index during that part of the processing.
Yeah. What I means is that if you're tuning to reduce IO load, by improving the buffer pool utilisation, looking at inserts is probably not the way to go.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply