My db has a lot of PAGELATCH_SH waits going on

Question

My db has a lot of PAGELATCH_SH waits going on

girac127

SSC-Addicted

Points: 454
More actions
July 20, 2017 at 8:51 am

#339036

Hi all, Hope this topic is in the right area.
I have a highly normalized Dynamics AX database, and lately there are a lot of PAGELATCH_SH waits going on in it. The transaction db and logs are on the same spindles, the temp db is on its own and consists of 4 files to match the number of cores.
I am running a create index and it is taking forever.
CREATE INDEX I_xxxBLAIDX ON "DBO".MyTable (FIELDp,FIELD1) INCLUDE (FIELD2,FIELD3,FIELD4)
How can I determine what is causing this and how to resolve it?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

girac127 - Thursday, July 20, 2017 8:51 AM
Hi all, Hope this topic is in the right area.
I have a highly normalized Dynamics AX database, and lately there are a lot of PAGELATCH_SH waits going on in it. The transaction db and logs are on the same spindles, the temp db is on its own and consists of 4 files to match the number of cores.
I am running a create index and it is taking forever.
CREATE INDEX I_xxxBLAIDX ON "DBO".MyTable (FIELDp,FIELD1) INCLUDE (FIELD2,FIELD3,FIELD4)
How can I determine what is causing this and how to resolve it?

Use sp_whoisactive and some of it's optional parameters to get details about the exact waits that are happening in real time. Adam Machanic has a 30-day blog post series on SQLBlog.com explaining how you can use the awesome freebie he wrote.

Also, could your create index could simply be blocked by other activity?

I would also do a file IO stall analysis during the period where bad stuff is happening.

What is your file growth increment for various data files in the database and tempdb?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

girac127 SSC-Addicted Points: 454 More actions · Answer 2

TheSQLGuru - Thursday, July 20, 2017 10:40 AM
girac127 - Thursday, July 20, 2017 8:51 AM
Hi all, Hope this topic is in the right area.
I have a highly normalized Dynamics AX database, and lately there are a lot of PAGELATCH_SH waits going on in it. The transaction db and logs are on the same spindles, the temp db is on its own and consists of 4 files to match the number of cores.
I am running a create index and it is taking forever.
CREATE INDEX I_xxxBLAIDX ON "DBO".MyTable (FIELDp,FIELD1) INCLUDE (FIELD2,FIELD3,FIELD4)
How can I determine what is causing this and how to resolve it?
Use sp_whoisactive and some of it's optional parameters to get details about the exact waits that are happening in real time. Adam Machanic has a 30-day blog post series on SQLBlog.com explaining how you can use the awesome freebie he wrote.
Also, could your create index could simply be blocked by other activity?
I would also do a file IO stall analysis during the period where bad stuff is happening.
What is your file growth increment for various data files in the database and tempdb?

Thank you Kevin.
I have downloaded the sp_whoIsActive and reviewing that.

Axapta is very noisy there was no other activities going on while the indexes were being created.

How do I do a file IO stall analysis?
file growth for the temp file is autogrowth by 10% with an initial size of 25G, traceflag -1117 is enabled to allow the temp db to grow at same rate. Is this optimal?

girac127 SSC-Addicted Points: 454 More actions · Answer 3

girac127 - Thursday, July 20, 2017 12:09 PM
TheSQLGuru - Thursday, July 20, 2017 10:40 AM
girac127 - Thursday, July 20, 2017 8:51 AM
Hi all, Hope this topic is in the right area.
I have a highly normalized Dynamics AX database, and lately there are a lot of PAGELATCH_SH waits going on in it. The transaction db and logs are on the same spindles, the temp db is on its own and consists of 4 files to match the number of cores.
I am running a create index and it is taking forever.
CREATE INDEX I_xxxBLAIDX ON "DBO".MyTable (FIELDp,FIELD1) INCLUDE (FIELD2,FIELD3,FIELD4)
How can I determine what is causing this and how to resolve it?
Use sp_whoisactive and some of it's optional parameters to get details about the exact waits that are happening in real time. Adam Machanic has a 30-day blog post series on SQLBlog.com explaining how you can use the awesome freebie he wrote.
Also, could your create index could simply be blocked by other activity?
I would also do a file IO stall analysis during the period where bad stuff is happening.
What is your file growth increment for various data files in the database and tempdb?
Thank you Kevin.
I have downloaded the sp_whoIsActive and reviewing that.
Axapta is very noisy there was no other activities going on while the indexes were being created.
How do I do a file IO stall analysis?
file growth for the temp file is autogrowth by 10% with an initial size of 25G, traceflag -1117 is enabled to allow the temp db to grow at same rate. Is this optimal?

My SQL skills are limited in this area.
Are the number of stalls high in comparison to the reads and writes and db size?
I ran the following query on on Axapta and temp db. using this query:
SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'AX2012_Sandbox'), 2);
SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'tempdb'), 2);
GO

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

girac127 - Thursday, July 20, 2017 12:09 PM
TheSQLGuru - Thursday, July 20, 2017 10:40 AM
girac127 - Thursday, July 20, 2017 8:51 AM
Hi all, Hope this topic is in the right area.
I have a highly normalized Dynamics AX database, and lately there are a lot of PAGELATCH_SH waits going on in it. The transaction db and logs are on the same spindles, the temp db is on its own and consists of 4 files to match the number of cores.
I am running a create index and it is taking forever.
CREATE INDEX I_xxxBLAIDX ON "DBO".MyTable (FIELDp,FIELD1) INCLUDE (FIELD2,FIELD3,FIELD4)
How can I determine what is causing this and how to resolve it?
Use sp_whoisactive and some of it's optional parameters to get details about the exact waits that are happening in real time. Adam Machanic has a 30-day blog post series on SQLBlog.com explaining how you can use the awesome freebie he wrote.
Also, could your create index could simply be blocked by other activity?
I would also do a file IO stall analysis during the period where bad stuff is happening.
What is your file growth increment for various data files in the database and tempdb?
Thank you Kevin.
I have downloaded the sp_whoIsActive and reviewing that.
Axapta is very noisy there was no other activities going on while the indexes were being created.
How do I do a file IO stall analysis?
file growth for the temp file is autogrowth by 10% with an initial size of 25G, traceflag -1117 is enabled to allow the temp db to grow at same rate. Is this optimal?

I feel your pain. Some of my "worst" clients have been Dynamics ones. 😀

TF 1118 and 1117 should be enabled in virtually every SQL Server environment.

NO database file of any type should EVER be set up for any percentage growth. In your case if you don't have Instant File Initialization enabled you could wait for zeroing out 2.5GB of bits on disk when a growth happened. And since you have 4 files that would actually be 10GB of zeroing. That could take quite a while on many IO subsystems.

See attached for file IO stalls. Take a snapshot, wait, take a snapshot, diff and divide by time --> file IO stalls in milliseconds per IO.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Perry Whittle SSC Guru Points: 233898 More actions · Answer 5

girac127 - Thursday, July 20, 2017 8:51 AM
Hi all, Hope this topic is in the right area.
I have a highly normalized Dynamics AX database, and lately there are a lot of PAGELATCH_SH waits going on in it. The transaction db and logs are on the same spindles, the temp db is on its own and consists of 4 files to match the number of cores.
I am running a create index and it is taking forever.
CREATE INDEX I_xxxBLAIDX ON "DBO".MyTable (FIELDp,FIELD1) INCLUDE (FIELD2,FIELD3,FIELD4)
How can I determine what is causing this and how to resolve it?

what type of index are you creating, pagelatch_xx waits occur on buffer pool pages im memory.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

girac127 SSC-Addicted Points: 454 More actions · Answer 6

Perry Whittle - Friday, July 21, 2017 6:45 AM
girac127 - Thursday, July 20, 2017 8:51 AM
Hi all, Hope this topic is in the right area.
I have a highly normalized Dynamics AX database, and lately there are a lot of PAGELATCH_SH waits going on in it. The transaction db and logs are on the same spindles, the temp db is on its own and consists of 4 files to match the number of cores.
I am running a create index and it is taking forever.
CREATE INDEX I_xxxBLAIDX ON "DBO".MyTable (FIELDp,FIELD1) INCLUDE (FIELD2,FIELD3,FIELD4)
How can I determine what is causing this and how to resolve it?
what type of index are you creating, pagelatch_xx waits occur on buffer pool pages im memory.

I was creating both cluster and non-cluster indexed and also adding a field. All this happens through the application. I can only see what queries are running by either the use of sql profiler or activity monitor. Some of these queries will make your head spin. I am having serious performance issues with this particular system.

I should have mentioned that I am working in a non-production system and production is set up a little different with 8 temp files and the growth set at 50 mb. again transaction, log and temp on different spindles according to the DBA.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

Perry Whittle - Friday, July 21, 2017 6:45 AM
girac127 - Thursday, July 20, 2017 8:51 AM
Hi all, Hope this topic is in the right area.
I have a highly normalized Dynamics AX database, and lately there are a lot of PAGELATCH_SH waits going on in it. The transaction db and logs are on the same spindles, the temp db is on its own and consists of 4 files to match the number of cores.
I am running a create index and it is taking forever.
CREATE INDEX I_xxxBLAIDX ON "DBO".MyTable (FIELDp,FIELD1) INCLUDE (FIELD2,FIELD3,FIELD4)
How can I determine what is causing this and how to resolve it?
what type of index are you creating, pagelatch_xx waits occur on buffer pool pages im memory.

Are you sure about that Perry? Wouldn't that be PageIOLatch_xx waits?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 8

PAGEIOLATCH_* waits occur when a page has to be fetched from disk into the buffer pool.

PAGELATCH_* waits occur for data file pages in memory, as Perry said.

Cheers!

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

Jacob Wilkins - Friday, July 21, 2017 11:55 AM
PAGEIOLATCH_* waits occur when a page has to be fetched from disk into the buffer pool.
PAGELATCH_* waits occur for data file pages in memory, as Perry said.
Cheers!

Your first was the way I (mis)interpreted his statement. :hehe:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Perry Whittle SSC Guru Points: 233898 More actions · Answer 10

TheSQLGuru - Friday, July 21, 2017 11:44 AM
Perry Whittle - Friday, July 21, 2017 6:45 AM
girac127 - Thursday, July 20, 2017 8:51 AM
Hi all, Hope this topic is in the right area.
I have a highly normalized Dynamics AX database, and lately there are a lot of PAGELATCH_SH waits going on in it. The transaction db and logs are on the same spindles, the temp db is on its own and consists of 4 files to match the number of cores.
I am running a create index and it is taking forever.
CREATE INDEX I_xxxBLAIDX ON "DBO".MyTable (FIELDp,FIELD1) INCLUDE (FIELD2,FIELD3,FIELD4)
How can I determine what is causing this and how to resolve it?
what type of index are you creating, pagelatch_xx waits occur on buffer pool pages im memory.
Are you sure about that Perry? Wouldn't that be PageIOLatch_xx waits?

Yes Kevin, PAGELATCH_XX waits are database pages in memory, PAGEIOLATCH_XX are fetches from to disk to furnish the buffer pool

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉