SQLServerCentral Best Practices Clinic: Part 4

Question

Post reply

SQLServerCentral Best Practices Clinic: Part 4

Brad McGehee

SSCertifiable

Points: 5282
More actions
June 4, 2011 at 10:13 pm

#240874

Comments posted to this topic are about the item SQLServerCentral Best Practices Clinic: Part 4
Brad M. McGehee
DBA

Viewing 6 posts - 1 through 5 (of 5 total)

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

currym SSC Eights! Points: 949 More actions · Answer 1

Brad,

I appreciate your work! I do have a question though.

With respect to monitoring SAN activity via an OS source (perfmon/sysmon), a while back I was discouraged from relying on anything but expensive proprietary SAN software - likely by a vendor. As a government DBA managing a couple thousand SQL instances, I'm always trying to save your money. 🙂 If you have a moment, would you mind speaking or linking to that apparent misdirection?

Again, many thanks,

Patrick

Brad McGehee SSCertifiable Points: 5282 More actions · Answer 2

currym (6/6/2011)
Brad,
I appreciate your work! I do have a question though.
With respect to monitoring SAN activity via an OS source (perfmon/sysmon), a while back I was discouraged from relying on anything but expensive proprietary SAN software - likely by a vendor. As a government DBA managing a couple thousand SQL instances, I'm always trying to save your money. 🙂 If you have a moment, would you mind speaking or linking to that apparent misdirection?
Again, many thanks,
Patrick

Not all of the built-in IO-related performance monitor counters work well if SQL Server is using a SAN for data storage, although some of them do. Two articles that discuss this topic (and their are many more) include:

http://msdn.microsoft.com/en-us/library/cc966412.aspx

http://www.sqlskills.com/BLOGS/PAUL/post/How-to-examine-IO-subsystem-latencies-from-within-SQL-Server.aspx

Brad M. McGehee
DBA

currym SSC Eights! Points: 949 More actions · Answer 3

currym

SSC Eights!

Points: 949

June 6, 2011 at 9:43 am

#1334469

Great, thanks!

Patrick

Matt Guthrie SSC Enthusiast Points: 161 More actions · Answer 4

Am I the only one who noticed that the write latency for TEMPDB was measured in *seconds*, not ms?

I would think 3.5 sec is a lot more worrisome than 3.5ms. Just sayin'.

Brad McGehee SSCertifiable Points: 5282 More actions · Answer 5

Matt Guthrie (6/6/2011)
Am I the only one who noticed that the write latency for TEMPDB was measured in *seconds*, not ms?
I would think 3.5 sec is a lot more worrisome than 3.5ms. Just sayin'.

You must be, as I missed this, and so did the technical reviewer. Good catch! Next time I will pay more attention to the scale, instead of making assumptions, which is apparently what I did when I looked at the graph.

This definitely needs more in-depth analysis to see what is really going on during this maintenance window within tempdb. I have already planned to do a specific follow-up article on tempdb, and I will add this to my list to investigate.

Brad M. McGehee
DBA