SQL server IO problem

Dear all,

I read this:

http://blog.sqlauthority.com/2015/05/09/sql-server-script-current-io-related-waits-on-sql-server/

I use the second part of the script and the return result has no record, I am not sure how this script help on determining if IO problem is the problem.

Please suggset.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, the schema is SYS and not DBO (the default) so it need to be explicit:
SELECT * FROM sys.dm_io_pending_io_requests
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I would say it's because you don't have a pending request in your SQL instance. dm_io_pending_io_requests must be empty.
0
 
Mandeep SinghDatabase AdministratorCommented:
0
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.

 
marrowyungSenior Technical architecture (Data)Author Commented:
"I would say it's because you don't have a pending request in your SQL instance."

so you mean as there are no pending request on phycial IO access, so the output as nothing ?

I have the same guess.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes and you can confirm that just by making a simple select:
SELECT * FROM dm_io_pending_io_requests
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
Mandeep Singh,

the link you provide is more about from performance counter's point of view, but not purely from SQL server point of view, I think it will be hard to separate the real SQL server IO  and IO overhead related to other process.

Basically if I remember correctly there is a counter called IO queue, please correct me if I am wrong.

Vitor Montalvão,

tks and it find it is funny that, when I do this "SELECT * FROM dm_io_pending_io_requests"

it said :

Msg 208, Level 16, State 1, Line 1
Invalid object name 'dm_io_pending_io_requests'.

but running that script can is ok and return nothing as expected.

so this tools is handy and easily check if the IO is SQL server related, agree?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
yeah. it returns nothing. so we are fine, that script also told us that query cause that, what is the cpu, RAM and tempdb usage, logical and physical IO of that query takes and the execution plan of it?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
That view alone isn't that helpful and that's why you need to join it with another views.
The only think that you'll really need from that table is the io.handle to join with other views.
Pinal Dave's script is more for IO issues related with database files.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"The only think that you'll really need from that table is the io.handle to join with other views."

yeah.. tks.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
any idea on how to means SQL server IO problem, which the SQL server on Hyper -V ?

a lot of SQL server  we have are on Hype -V

read/write of tempdb disk can be check, but how about overall of that VM box's IO ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
any idea on how to means SQL server IO problem, which the SQL server on Hyper -V ?
I think this is one of the issues when moving to a virtual environment solution. Until now I could manage to have our Database Production servers in physical boxes but soon we'll need to move to virtual machines, so until there I can't help you on this subject.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
ok. I think it is quite hard. I heard from friends using Azure, all SQL server move to that Hype -V will be around 20-30% slower and they don't know why. They don't have DBA on site at all.

but as a DBA, what can we do, everything is VM.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
With Azure you just don't know what's happening there. All is managed by Microsoft and their DBAs.
We here are building our private cloud, so DBAs and Sys Admins are ours. More control :)
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"We here are building our private cloud, so DBAs and Sys Admins are ours. More control :) "

your private cloud only for use by your company, Microsoft Azure too ?all hyper-V ? if it is private cloud, how you tune it then ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
your private cloud only for use by your company
Yes.


Microsoft Azure too ?
No. Mostly Windows Server and Linux virtual machines.


if it is private cloud, how you tune it then ?
We have our own VMware specialists plus Windows and Linux Engineers.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"No. Mostly Windows Server and Linux virtual machines."

then how to form a cloud.

"We have our own VMware specialists plus Windows and Linux Engineers. "

then the SQL server still tunable ? it is not hyper V ? oh too bad !
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
then how to form a cloud.
A Cloud isn't more than services that can be found in an Internet. Like Azure, Amazon or others public clouds we have a portal where you can request services that provides machines and applications.


then the SQL server still tunable ?
Of course. That my team's job :)
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"Of course. That my team's job :) "

good, but in the same way ? the VMware's figure can't give you the correct figure, specially on the disk access speed, tempdb speed and replication speed, right?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
the VMware's figure can't give you the correct figure, specially on the disk access speed, tempdb speed and replication speed, right?
I'm not a VMware specialist but I think it gives. We usually contact with them but they might need to contact with Storage and Network teams also but I can't confirm that.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
ahhaha, very good already !
 

tks.

I will close this ticket first but I probably come back later, sorry about that.
0
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.

All Courses

From novice to tech pro — start learning today.