August 16, 2010 at 12:27 pm
seems like there should be some type of correlation.
I just added more max memory since it was below 100 and now 1,000 avg and sometime drops to 300
I have Job that is causing Parallelism thats been running a long time and I am sure this the issues.
I just expected to see the Avg Disk sec/read(.007) be higher. the Latch_ex tell me its waiting on pages, but Avg Disk sec/read tells me its keeping up?
THANKS
August 16, 2010 at 2:24 pm
Latch_ex is not an IO-related wait. The IO-related latch waits are the PageIOLatch waits. Latch_ex is a non-buffer exclusive latch, meaning it's not related to pages.
From a kb article on wait types -
Non-buffer (Non-BUF) latch: The non-buffer latches provide synchronization services to in-memory data structures or provide re-entrancy protection for concurrency-sensitive code lines. These latches can be used for a variety of things, but they are not used to synchronize access to buffer pages.
In short, I wouldn't expect to see a correlation between these and IO delays' I'd expect to see a correlation between the PageIOLatch waits and disk activity.
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
August 16, 2010 at 3:23 pm
Thanks Gail!
after looking around for a while this was the best example I could find, but this seems to say it could be related to IO...the article you pointed out show BUFFER and NON buffer. Just making sure I correctly understand it.
This article seems to be telling me it COULD be io related....but not always?
http://blogs.msdn.com/b/psssql/archive/2009/07/08/q-a-on-latches-in-the-sql-server-engine.aspx
Question: What kind of latch does SQL Server use when reading a page from disk?
Answer: Anytime you talk about latching and database pages, think in terms of BUF (buffer) latches. So to read a page from disk and put this into cache, the worker thread will take out a EX (Exclusive) latch on the BUF associated with the page. Then the read is issues for the page. Then a SH latch is taken out by this same thread on this BUF structure. Since these are not compatible, the thread will wait. Makes sense since we need to wait for the page to be read in from disk before trying to look at it. Any other thread needing to read this page will also wait since you need a SH latch on a BUF to read the page in cache. When the read has completed, the EX latch is released, and now the SH latch is acquired and the thread(s) can read the page in cache.
August 16, 2010 at 11:24 pm
456789psw (8/16/2010)
This article seems to be telling me it COULD be io related....but not always?
No
Question: What kind of latch does SQL Server use when reading a page from disk?
Answer: Anytime you talk about latching and database pages, think in terms of BUF (buffer) latches. So to read a page from disk and put this into cache, the worker thread will take out a EX (Exclusive) latch on the BUF associated with the page. ...
Emphasis mine. Bob's talking about buffer latches. Latch_ex is a non-buffer latch. The buffer latches are the PageLatch_sh, PageLatch_Ex, etc
See the quote that I posted from the kb article for what the non-buffer latches control.
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
August 17, 2010 at 7:39 am
Gail....THANKS a BUNCH!
I reread Bob Article and there I see IO latches and NON IO latches.... which is what a EX_Latch is (NON).
🙂
August 17, 2010 at 7:48 am
456789psw (8/17/2010)
I reread Bob Article and there I see IO latches and NON IO latches.... which is what a EX_Latch is (NON)
No, it's not.
There are three categories of latches. (as per the kb article I linked)
1) Non-buffer latches (eg Latch_EX)
2) Buffer latches (eg PageLatch_EX)
3) IO latches. (eg PageIOLatch_Ex)
Bob's article only covers 2 and 3. He makes no mention at all of non-buffer latches at all, and Latch_Ex is a non-buffer latch. It's not related to pages (latches related to pages are the PageLatch_* and PageIOLatch_*).
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
August 17, 2010 at 9:09 am
Thanks again....I misspoke...
the article that you referred me to is the one that made since, I saw how they were broken up into the 3 areas and then it all made since 🙂
August 18, 2010 at 7:19 am
did you resolve your problem? parallelism could be result of missing index, which could improve performance a thousand times or more. You might also be getting too many CXPACKET waits and need to adjust your MAXDOP to take poor IO performance into account.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 18, 2010 at 8:29 am
well..yes and no..Just started a new job and its a MESS
The cxpacket I know are caused by no indexes....I have 145 heap tables....not my design!!
I had I/O issues one array on RAID 5 everything but the kitchen sink put on it!!! yea YIKES.
added some memory solved one problem now its not waiting on I/o but CPU
Problem is, this is bought application..all my SP are encrypted you cant see any qry plans either!!
so I would normally just open up the bad SP set the MAXDOP on that SP and its fixed....dotn really want to turn down the cpu for the entire instance as this could cause problems elsewhere...
at the end of the day I am going to have to! just the old n-1 and adjust from there!!!
fun stuff!!
THANKS
August 18, 2010 at 10:53 am
You might be able to find a good starting point for potential indexes by using the following query, which is actually from an excellent whitepaper on the "waits and queues" performance tuning methodology
http://technet.microsoft.com/en-us/library/cc966413.aspx
--Potentially Useful Indexes
select d.*
, s.avg_total_user_cost
, s.avg_user_impact
, s.last_user_seek
,s.unique_compiles
from sys.dm_db_missing_index_group_stats s
inner join sys.dm_db_missing_index_groups g
on s.group_handle = g.index_group_handle
inner join sys.dm_db_missing_index_details d
on d.index_handle = g.index_handle
order by s.avg_user_impact desc
September 20, 2010 at 9:13 am
You may want to increase the number of files in each of your filegroups to match the number of physical CPUs on the server as well as insure the size of each file is uniform.
In my experience high LATCH_EX and CX_PACKET waits often indicate an inefficient physical design of the database.
September 20, 2010 at 9:19 am
damon.ashman (9/20/2010)
You may want to increase the number of files in each of your filegroups to match the number of physical CPUs on the server as well as insure the size of each file is uniform.
That's for page latch and page io latch waits, mostly in TempDB. Latch_Ex waits are not related to pages. They are waits on memory structures.
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 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply