the waittype in SP_who2 and whoisactive

http://logicalread.solarwinds.com/common-sql-server-wait-types/#.Vd04Sfmqp8l

so after reading all those, this means:
1) if the waitype is DTC, it is waiting for transactions distributed in all machine to complete before it actually/total complete, so the only wait to solve it is to wait ?
2) I don't understand this, PAGEIOLATCH_EX, please explain what it means
3) so nothing can b done for this wait type: SOS_SCHEDULER_YIELD ? CPU hight usage means need to buy new CPU ?
4) WRITELOG, so this one keep waiting can mean disk problem as it can't find the log disk to write to or STILL writing which is quite impossible ?

    so it is hard to see which SYSTEM DB or user database's log file or which log files having problem ...

so it seems for phsical IO related problem, only the following is needed:
1) LOGBUFFER
2) WRITELOG

?


as by whoisactive or whatever, I ALSO want to pinpoint any potential hardware/disk problem.

5) no wait type for indicate any RAM problem ?

I read this: http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx

also those for SQL server 2005/2008 but not 2012/2014, wait type no diff ?

from here: http://www.sqlskills.com/blogs/paul/worrying-wait-type/

"I’d be unsurprised to see ASYNC_NETWORK_IO, CXPACKET, PAGEIOLATCH_SH, or WRITELOG as the top wait, and I’d be intrigued to see SOS_SCHEDULER_YIELD as the top wait as that’s very uncommon. None of these would be the most worrying IMHO. PAGELATCH_EX would mean there’s a big bottleneck somewhere, which may be easy to alleviate (if in tempdb) or not, but queries would still be progressing."

So is it also the same wait type you all might worry too ? usually what should be worry about ? I worry about disk IO problem.

now some logical table access lock waittype:
1) LCK_M_IX:
2)LCK_M_X:

do they means some one type the table lock without releasing it ? very important to catch something developer done wrong.... :):)):)

if seeing this LCK_M_IX, by a spid, the query of that spid is waiting for a lock release by another spid show by the blocking_session_id, and we can read the SQL text of that blocking_Session_id and start to tune it or/and ask developer why that blocking_Session_id query ran so long?

so the author of that link seems very worry about LCK_M_IX, how about you all ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ZberteocCommented:
Hi again. I will repeat to you that these kind of questions are not really the object of EE. You can't expect poeple to type for you about things that you can find abundent on the net. Here is an exemple:

http://blog.sqlauthority.com/2011/02/09/sql-server-pageiolatch_dt-pageiolatch_ex-pageiolatch_kp-pageiolatch_sh-pageiolatch_up-wait-type-day-9-of-28/

It seems there is a series of articloes about wait types and queues:

http://blog.sqlauthority.com/2011/02/01/sql-server-wait-stats-wait-types-wait-queues-day-0-of-28-2/

Nobody can explain here more and better than that. It is an answer that would involve lot of writing, as you can see.


In regards to locks. That has nothing to do with developer. Locks are there for a reason and they are put in place by the sql engine to protect your data. The only mistake someone can do is to explicitly open a transaction and run an insert, update pr delete or a schema change and forget to commit and keep the connection open.

When LOCKS take too much time is usually due to bad written queries that take too long to complete and they lock the resources. I am not sure why are you so concerned about the locks unless they happen very often and take longer time. Usually lock happen many times during a period, say a day, but they should last only for split seconds.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"http://blog.sqlauthority.com/2011/02/01/sql-server-wait-stats-wait-types-wait-queues-day-0-of-28-2/

 Nobody can explain here more and better than that. It is an answer that would involve lot of writing, as you can see"

tks.

 I like that.

"The only mistake someone can do is to explicitly open a transaction and run an insert, update pr delete or a schema change and forget to commit and keep the connection open. "

that's what I am asking and by looking at whoisactive or sp_who2, I am not sure if the ROW that tell me the wait type is LCK_M_IX, the SQL statement shown on that row can quickly indicate that.

"I am not sure why are you so concerned about the locks unless they happen very often and take longer time. "

no just because I hear this kind of question all the time and I am not sure if this can be address easily by so_who2 or whoisactive.

"When LOCKS take too much time is usually due to bad written queries that take too long to complete and they lock the resources."

I knew Locking is good but in case we don't know why some resource get lock ( all developer will keep saying NO NO  NO! not me !!) , then we have to pinpoint that out.

a lot of junior program, really don't know it and I think  I should do something to show them, that's why I ask.

sorry to ask the whole thing in detail,  I will try to read that link and please keep helping me as you are a very good resource on coaching, you can give me good link to read.
0
Mark WillsTopic AdvisorCommented:
*laughing* (in a good and respectful way)

We have been discussing performance, wait types, locks and a whole bunch of IO issues for years with marrowyung in EE.

If we have been doing a good job, I would have hoped all our collective feedback would have empowered enough to be giving us some tips :) :)

Always good to read those links and advice from Zbertoec.

And in trying to answer your points....

1) No, there might be orphaned transactions which might be creating other problems.
2) PAGEIOLATCH_EX - generally the EX typifies exclusive mode, which means it must wait until the resource is available. Now we know that PAGE is referring to the page in buffer pool and IO is well disk IO. So what it is doing is waiting for buffer pool to load a new page into memory from disk. Time is the important part (more so than volume). It doesn't really mean you must have an IO problem, there are other reasons, often SQL Server based. If you remember a while back, I said check page life expectancy (low) and buffer hit cache ratio (high).
3) SOS_SCHEDULER_YIELD actually, probably indicates that memory is being used and in one way, good to see because there is no resource contention. Cant really say if it is bad or not. Again, other metrics are better for working out memory and CPU.
4) WRITELOG - No, it means it has to keep flushing lots of transactions. So, it is more the size of transactions or the rate of having to flush to disk. The better metrics for disk are the Average Disk Sec Reads / Writes / and the queue length. Big numbers means you have some disk housekeeping, some SQL changes (maybe for transaction COMMiT, Locks and of course backing up the transaction log frequently enough). IO_completion is the newer metric to look at, and then logbuffer. but make sure you have the housekeeping in order first.
5) RAM problems are normally associated with the buffer pool type metrics. Often more a config problem with how much memory has been made available. If not enough, SQL will simply go to disk that's why the IO metrics are used to indicate more than just a disk IO bottleneck.

As for Paul's post. It is good and informative. The reason he chose that wait type is impact on user. but is also written from a bit of a developer bias.

For a SQL administrator it is the overall smooth operating environment, so have to look at a few things and gather benchmarks to be able to compare when you get that call complaining about "bloody slow today". The user doesn't care, just wants responsiveness.

For me, IO is always the one thing that is likely to mean a greater interruption because to fix it, other areas are normally involved that impacts other decisions (like budgets, new machines etc.)

But that also assumes you have done the homework on config and housekeeping and a bit more tuning.

In that regard, best to add Brent Ozar to your reading materials : http://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/ he has a hardware / sysadmin perspective to balance Pauls (who is brilliant with the insides of SQL).
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mark WillsTopic AdvisorCommented:
Same old problem...

Answer the question only to find it has been already closed, after hitting "submit"
0
marrowyungSenior Technical architecture (Data)Author Commented:
Mark Wills,

how have you been ? waiting for you to answer this question too and I found good Zbertoec ! tks for his teaching and I believe that I can close that one.

never say too late man. :):)

Zbertoec, please keep helping me and you are good teaching as I need some help to understand what SQL developer/programmer is thinking, they always need help and try to push ball to DBA.  like "our program has no problem, the MS SQL is not good " ....... etc.

"balance Pauls (who is brilliant with the insides of SQL). "

which paul ? he publish any SQL books / oracle books ?

"We have been discussing performance, wait types, locks and a whole bunch of IO issues for years with marrowyung in EE.

 If we have been doing a good job, I would have hoped all our collective feedback would have empowered enough to be giving us some tips :) :)"

I am converting myself to application DBA from infrastructure DBA. you all doing good but this time a bit diff, , I try dig deeply into the problems of queries suggest query improvement quickly. so concept is very important to me.

can't relies on developer here ! they didn't know a lot of thing! so from time to time, they will ask for advice.

just like why cursor and while loop is bad they don't know ! table lock or no table lock, they didn't know and how can I , infrastructure DBA know.

so you can see I keep posting question here and I know from time to time I have to shut up earlier !:):):, I appreciate all you help.

mark_will, please keep helping me and don't bored by me.......... I will just ask once. (until I get the main point)

I have to read a lot books too.
0
Mark WillsTopic AdvisorCommented:
G'day marrowyung

Paul is CEO of sqlskills https://www.sqlskills.com/about/paul-s-randal/ and married to another great SQL personality Kimberly Tripp : https://www.sqlskills.com/about/kimberly-l-tripp/ 

Imagine the dinner conversation :)

SQLskills have very good people there. While I might not always agree with some of their assertions (like that wait type of concern), but generally, they don't give any bad advice, and it is always accurate. After all, they are a team of well known, respected and published SQL MVP's.

Not frustrated with anyone in the thread, just get annoyed when I am too slow to answer and when I do finally hit submit, it has already been answered. At the very least, EE should check when submit button has been pressed and give me the option.

I am trying to decide whether to stay active here or take up another opportunity. Time will tell, and a little bit time poor (so hard to respond to questions in this place).

Just a heads up...

When discussing performance problems with developers, keep to the basics. Make sure their queries are structured properly (using indexes, joins, avoiding scans, accurate conditional statements) and have the transaction commits. Make sure your DB and any new tables have all the keys, indexes, foreign keys (etc). Don't be overly critical of inelegant code - it can sometimes outperform the most elegant T-SQL. If the code is easy to read, easy to understand, and makes sense, then there is a greater possibility that the optimizer will do a better job of delivering the results. After that, then you can start by looking for the trouble spots. Start with the more obvious. Like big batches or high counts (frequently run queries) . Then you can drill down into query plans and wait types. Try avoiding jumping straight into the fine details, it is big picture and overall health. A routine running once a month might not be as important (performance wise) as a daily headache more easily repaired by a missing index.

Welcome to the dark side :)
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks and do you have a time on help answering this..

http://www.experts-exchange.com/questions/28709409/can't-open-Windows-10-store.html

I will change to a new company and some developement questions should come, hope you don't mind to answer that.

oh, application side DBA.
0
marrowyungSenior Technical architecture (Data)Author Commented:
0
marrowyungSenior Technical architecture (Data)Author Commented:
hi, sir,

"
 SQLskills have very good people there. While I might not always agree with some of their assertions (like that wait type of concern), but generally, they don't give any bad advice, and it is always accurate. After all, they are a team of well known, respected and published SQL MVP's. "

so I contact them by this page.. ?  https://www.sqlskills.com/contact-us/

"I am trying to decide whether to stay active here or take up another opportunity"

what's wrong ? unhappy about .... ?

"A routine running once a month might not be as important (performance wise) as a daily headache more easily repaired by a missing index.
"
that's why I have another 2 x post, feel free to discuss some over there:

http://www.experts-exchange.com/questions/28706091/create-necessary-dB-index-from-any-SQL-server-view.html

http://www.experts-exchange.com/questions/28706090/find-out-unused-index.html

I am quite concerning about this.
0
Mark WillsTopic AdvisorCommented:
Hi marrowyung,

Not particularly happy or unhappy, just the other opportunity is very appealing and will consume time, effort and resources. And a little time poor at the moment.

I did have a look at your Win10 question and tidied up a couple of expletives for you (need to be careful). But chose not to participate.

I will have a look at the two above and if I can add any value to the conversation, you will see me participate.

By the way, there is an interesting Microsoft Support Article about memory in 2012 and later because it was changed because no longer supporting Multi-Page allocations : http://support2.microsoft.com/default.aspx?scid=kb;EN-US;2663912 

There is also an interesting impact on some of the statistics (which still thinks in multi-page). See : http://social.technet.microsoft.com/wiki/contents/articles/28640.sql-server-2012-troubleshooting-wrong-buffer-pool-value-in-dbcc-memorystatus-and-perfmon-counter.aspx The reason why that is interesting in this thread is because of all the work Paul Randal did in writing the DBCC routines for Microsoft.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"By the way, there is an interesting Microsoft Support Article about memory in 2012 and later because it was changed because no longer supporting Multi-Page allocations : http://support2.microsoft.com/default.aspx?scid=kb;EN-US;2663912 

There is also an interesting impact on some of the statistics (which still thinks in multi-page). See : http://social.technet.microsoft.com/wiki/contents/articles/28640.sql-server-2012-troubleshooting-wrong-buffer-pool-value-in-dbcc-memorystatus-and-perfmon-counter.aspx The reason why that is interesting in this thread is because of all the work Paul Randal did in writing the DBCC routines for Microsoft."

all those for SQL server 2012 only ? we knew SQL server 2012 has a lot of functional bugs. if we upgrade, we should be upgrading from SQL server 2008 to 2014. directly upgradable, right?
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Not particularly happy or unhappy, just the other opportunity is very appealing and will consume time, effort and resources. And a little time poor at the moment."

what other DB group/forum/site you went to ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
"2) PAGEIOLATCH_EX - generally the EX typifies exclusive mode, which means it must wait until the resource is available......... "

exclusive mode ALSO  means this action is the first priority action SQL server will do/doing?

 "I said check page life expectancy (low) and buffer hit cache ratio (high). " 

I recall that this one should say everything still in RAM and disk IO should be low then.. no need to fetch a page to memory, right?

"3) SOS_SCHEDULER_YIELD actually, probably indicates that memory is being used and in one way, good to see because there is no resource contention"

then this is very different from what the links below tells me:

http://www.sqlskills.com/blogs/paul/worrying-wait-type/

http://www.brentozar.com/sql/wait-stats/

http://logicalread.solarwinds.com/common-sql-server-wait-types/#.Vdr5BvlViko

http://blogs.msdn.com/b/psssql/archive/2009/11/03/the-sql-server-wait-type-repository.aspx

it seems that if we see a lot of this then the system resource has contention ? a single one is not !

"IO_completion is the newer metric to look at, and then logbuffer. but make sure you have the housekeeping in order first."

IO_completion (from windows permon ?) means the COMMIT statement ?

I am sorry, so what is log buffer is about then ?

". The user doesn't care, just wants responsiveness. "

yes ! they are !
0
marrowyungSenior Technical architecture (Data)Author Commented:
for RAM problem, we should look at ALL e buffer pool type metrics. ? hight means not good ?

"why the IO metrics are used to indicate more than just a disk IO bottleneck."

you mean this:

"The better metrics for disk are the Average Disk Sec Reads / Writes / and the queue length. "

and watch for queue length ?

then for this type : LCK_M_IX, any suggestion?
0
Mark WillsTopic AdvisorCommented:
SQL 2012 ... yes, those links were for 2012. The newer versions do change a few things. Something to watch out for. But then 2016 will be here before too much longer. What you are trying hard to understand in this thread will not be entirely applicable in the future versions. A lot will, but some things wont.

PAGEIOLATCH_EX Yes the 'X' in particular normally indicates eXclusive mode. No, it doesn't mean SQL will do that first. More accurate to say it is waiting to be able to acquire that resource in exclusive mode

page life expectancy (low) and buffer hit cache ratio (high).  Yes.

SOS_SCHEDULER_YIELD You might like to review those links again. I think they will pretty much agree with my assertion that it *might* be a good thing. Or, at least it needs further (and probably different) analysis.

I would far prefer SQL in memory than having to go to disk IO

IO_completion If you have high wait times then there is an I/O bottleneck.

Here's the trick with all these wait types... They normally measure a specific thing and normally it is not just the count, but the time that is the important aspect.

But then, by being specific things you need to take different aspects of what your systems are doing like sort in TempDB shows in IO_Completion, but then transaction log is represented by Writelog, and PAGEIOLATCH is to do with database pages.

So, for some of the I/O metrics, then high is not good. But then other aspects like buffer cache hit ratio, then the higher the better.  And, because some are counters, then that might not mean it is bad, you need to look at the time - like the average disk sec ones. And then queue lengths should always be the smaller the better.

It is the various combinations and how they interact. Fixing one aspect in isolation might manifest a different problem. That is what makes it so hard to give advice. We can try to explain point by point but the real education is seeing those metrics holistically and then make suggestions (but would normally mean sysadmin type access to SQL and to the Server.

Arguably the best example is the last lock type - LCK_M_IX looking at it in isolation in that chart, you would be correct (in one sense) to ignore. But begs the question, why is it that author's pick of being the most concern ?

I think Zbertoc has already answered that :)
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Arguably the best example is the last lock type - LCK_M_IX looking at it in isolation in that chart, you would be correct (in one sense) to ignore. But begs the question, why is it that author's pick of being the most concern ?"


you mean this:

 "The only mistake someone can do is to explicitly open a transaction and run an insert, update pr delete or a schema change and forget to commit and keep the connection open. "

"IO_completion If you have high wait times then there is an I/O bottleneck."

I am sorry, that one is a SQL server wait time value, right? not Windows server perf mon counter, right? (sorry just try to make sure)
0
Mark WillsTopic AdvisorCommented:
"You mean this..." Kind of. could also be a lot of processes trying seemingly innocuous updates / inserts, or a victim of isolation levels. and yes, uncommitted transactions (including lost / remote connections holding a lock) is seriously not good.

IO_completion That is correct, but if SQL is reporting it, then anything external is also affected. Externally, it might show up as a different set of combinations and quite possibly, suggest an alternate fix. That is the problem. That's why you cannot take one measure in isolation over another. Think of an external file process hammering the same disks as used for tempdb. If just looking at SQL Server then you might not be aware of the other (server based) activities and see that killing that process that caused the disks to be hammered, will in turn, free up SQL Server and the "apparent" problem as reported by SQL is no more.
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks a lot , you are a great helper!

What other forum you login most recently?
0
marrowyungSenior Technical architecture (Data)Author Commented:
Zberteoc,

is the execution plan from whoisactive is estimated or actual plan ?

any do you know why the plan some time in graphical format and sometimes in XML format ?
0
ZberteocCommented:
I am not sure but it is probably the cached plan that is used by the query. If is a query executed for the first time I would assume it will show the estimated plan because the query is still executing at the time you run sp_whoisactive. I never got an execution plan in xml format but I always run the sp_whoisactive in Management Studio, which knows how to graphically represent it. Probably in other tools it will show the XML format.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"I am not sure but it is probably the cached plan that is used by the query"

it is an estimated plan and I am told by the author to install SQL server SP/patch to get the plan consistently.

only actual execution plan show missing index suggestion, right?

"I never got an execution plan in xml format but I always run the sp_whoisactive in Management Studio, which knows how to graphically represent it. "

me too but from time to time, it sometimes show XML information plan, can't see why !
0
ZberteocCommented:
"only actual execution plan show missing index suggestion, right?"

Wrong.
0
Anthony PerkinsCommented:
is the execution plan from whoisactive is estimated or actual plan ?
It is neither one.  It obviously cannot contain the row count or the iterations you will find in the actual plan,  but other than that it is the same plan.
0
marrowyungSenior Technical architecture (Data)Author Commented:
oh, some training video tells this and I also doubt about that !

this make me think the execution plan from whoisactive can't tell much at all.

btw, my new company's whoisactive result don't shows any missing index suggestion from any slow query execution plan.

surprise!

they are not that slow it means but they keep saying it is slow, DB is bottleneck.

please also help to answer this when you have time :

http://www.experts-exchange.com/questions/28728781/the-memory-tier-between-web-server-and-DB-hardware-tier.html

expertist can advise this.
0
marrowyungSenior Technical architecture (Data)Author Commented:
0
marrowyungSenior Technical architecture (Data)Author Commented:
Anthony Perkins,

tks for messaging here,

"is the execution plan from whoisactive is estimated or actual plan ?
 It is neither one.  "

basically the programmer reply it is the estimated one and he/she want me to patch my SSMS to get the actual plan, but I have all those patch and I don't know why I should do this.

"It obviously cannot contain the row count or the iterations you will find in the actual plan,  but other than that it is the same plan. "

you mean the plan is estimated too just because whoisactive don't contain the row count or the iterations ?

and you also mean estimated and actual plan 99.9% the same as long as stats updated?

I just want to read the missing index suggest from whoisactive, can't see why it don't tells now.


or what I have to do is find the slow one from whoisactive and then ask programmer for the whole script and I debug it individually myself in SSMS.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"If you want to discuss execution plans and/or missing / unused indexes, they should be a couple more questions. Especially since this one was accepted some time ago."

Yeah, I listen to you my friend. sometime a lot of thing appear in my mind and I will just type in the RELATED topic as I don't know why other thing verify that discussion is sometimes wrong.
0
marrowyungSenior Technical architecture (Data)Author Commented:
Zberteoc,

tks for it and I just am not sure why the execution plan from whoisactive doesn't show index suggestion.

bye.
0
ZberteocCommented:
Because there is no index to be created that will improve the query!
0
Anthony PerkinsCommented:
Or because it is not the Actual plan ...
0
marrowyungSenior Technical architecture (Data)Author Commented:
Zberteoc,

probably, this is one of the reason ! I think about that.

Anthony,

Zberteoc already said sth,

"only actual execution plan show missing index suggestion, right?"

Wrong."

so you also believe that only actual execution plan has missing index suggestion.
0
Anthony PerkinsCommented:
so you also believe that only actual execution plan has missing index suggestion.
Actually I stand corrected.  The Execution Plan as presented by sp_whoisactive does indeed include suggestions about about missing indexes, at least in the test queries I just tried.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"The Execution Plan as presented by sp_whoisactive does indeed include suggestions about about missing indexes, at least in the test queries I just tried."

yes, I saw one yesterday, tks.

as the owner presend it is estimated plan and as long as the statistics is up to date I think this can be good enough.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.