Author: SQL Padre

Sometimes you just have to step away

SQL Padre Family Time

A very hard lesson that I am seriously still having problems with, is learning to spend some time away from work.  I would not necessarily categorize myself as a “workaholic”, I do leave work at work unless called upon; but I don’t spend enough time way from the office as I probably should. I’m talking Vacations!

So, after work today my wife, my kids and I are all heading to Florida for the Mardi Gras weekend.  Yes, in Louisiana we (most people) get Monday and Tuesday off for Mardi Gras. Whereas a lot of people are coming into Louisiana, specifically New Orleans for Mardi Gras; true Louisiana natives try and get the hell away from it all!

I am looking forward to a little break away from work and some valuable time with my kiddos before they get too old to want to do anything with their parents!

 

Get ready Disney World, the BISHOPS are coming!

The Value of Mentoring

SQL Padre SQL ,

I would like to discuss something that has been missing in my professional career, mentoring.  With a degree in psychology and a master’s in counseling, I stumbled into the IT field after 10 years of food service management. As a member of Generation-X, I have pretty much grown up with the personal computer (although it wasn’t always an IBM clone). So computer technology has always interested me.  But it wasn’t until I developed a Food Inventory Tracking system using SQL Express 2005 and Visual Basic 2005 Express edition, that realized that I could actually make a career out of being a developer. Fast forward 10 or so years, now I am a Database Administrator for a local hospital managing almost 100 SQL Server instances.

Over the years, I have played with all every Express edition Microsoft had to offer (either VB or SQL).  I have learned from doing (and breaking), reading articles, reading online forums, and  reading blogs.  All of these were an attempt to try and further my understanding of SQL Server; but the one that that was missing in all my 10 years of IT work?  A mentor.

A mentor is such a valuable gift and it should be treated as such. Growing up, I had one of the world greatest mentors in my Scoutmaster.  Mr. Emile Oestriecher, was and is to this day the best mentor one could have for life lessons.  Things he taught me by being an example are things that I live by today: honesty, integrity, loyalty and never give up.

In the SQL Server world, a mentor is just as valuable. Someone to pass on knowledge, tips, and experience.  Of course throwing questions at him would be the simplest way to learn; but getting the chance to discuss SQL Server and how to further my knowledge of it would be priceless. Sometimes the hardest part of learning something, if figuring out the best source of knowledge, especially since there are several hundreds of people who write on the subject of SQL Server.

A SQL Server mentor in my mind would be the type of person who would not give you the answers, but turn you in the right direction so you can figure out the answers yourself.  Not making you drink but definitely leading you to the water!

Learning is a never ending task in the world of SQL Server and life!  Having started may career as an “accidental” IT person, more importantly as an “Accidental DBA”, sometimes I feel I am way behind the curve of knowledge. I am hoping a mentor can help straighten that curve by at least putting me down the correct path.

So Paul Randal, I submit my blog post as a request for mentorship! I look forward to discussing with you the topic of “ways to learn”.

I/O,I/O it’s off to … Bang my head against a wall!

SQL Padre Monitoring, SQL

I/O, Input/Output, Reads/Writes what ever you want to call it.  It is one of the most aggravating aspects of SQL Server. The physical spinning hard drives of your SAN can bring your entire database server to its knees, no matter how powerful a beast of a server you have.

When you as the DBA are at the mercy of the SAN Administrator (or better yet 3rd party SAN administrator), your hands are somewhat tied on configuration changes. So what do you do?  Bang your head against a wall and shout, “It’s not my fault, it’s not my fault!” Then the big burly guys in white shirts will come visit your cubicle, ask you some questions and then take you on a little “vacation”.

one-flew-over-the-cuckoos-nest

 

That’s not the path I want to go down.

Not having any type of 3rd party monitoring solution, I needed a way to prove which drive was having trouble and just how much work it had to do!

Now I have done my due diligence and have read about I/O stalls, I/O Latency, other I/O measurements and such; but most everything I found, because the way SQL Server works, were cumulative information. Glenn Berry’s DMV Diagnostic Information Queries is great place to learn where to get this information.  But again, it is based on SQL Server cumulative information. I hope my Read Latency is not 150+ ms!

I needed a way to say, “for this sample period” this was the read latency of a drive?

Then I found Jon Gurgul b|t, his post titled Delta and Cumulative IO Stats was a great read; however it wasn’t exactly what I needed.  But it did get me thinking about his method of capturing two result sets with a time delay and finding the difference.  So why couldn’t I do this for Glenn Berry’s Read/Write Latency script.

Below is what I came up with.  It probably is not perfect, but it works for me. I have setup a SQL Agent job to run this code every 15 minutes with a 5 min delay between result sets.  Then the results are stored in a Diagnostic database so I can chart and report on trends.

I am eager to see the trending over time and truly see what my Latency time for my SAN drives are!

   1:  

   2: IF OBJECT_ID(N'tempdb..#Sample1') IS NOT NULL BEGIN DROP TABLE #Sample1 END;

   3: IF OBJECT_ID(N'tempdb..#Sample2') IS NOT NULL BEGIN DROP TABLE #Sample2 END;

   4: SELECT 

   5:     LEFT(UPPER(mf.physical_name), 2) AS Drive

   6:     ,SUM(num_of_reads) AS num_of_reads

   7:     ,SUM(io_stall_read_ms) AS io_stall_read_ms

   8:     ,SUM(num_of_writes) AS num_of_writes

   9:     ,SUM(io_stall_write_ms) AS io_stall_write_ms

  10:     ,SUM(num_of_bytes_read) AS num_of_bytes_read

  11:     ,SUM(num_of_bytes_written) AS num_of_bytes_written

  12:     ,SUM(io_stall) AS io_stall

  13: INTO #Sample1

  14: FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs

  15: INNER JOIN sys.master_files AS mf WITH (NOLOCK)

  16:     ON vfs.database_id = mf.database_id 

  17:     AND vfs.file_id = mf.file_id

  18: GROUP BY LEFT(UPPER(mf.physical_name), 2);

  19:  

  20: WAITFOR DELAY '00:05:00'

  21:  

  22: SELECT 

  23:     LEFT(UPPER(mf.physical_name), 2) AS Drive

  24:     ,SUM(num_of_reads) AS num_of_reads

  25:     ,SUM(io_stall_read_ms) AS io_stall_read_ms

  26:     ,SUM(num_of_writes) AS num_of_writes

  27:     ,SUM(io_stall_write_ms) AS io_stall_write_ms

  28:     ,SUM(num_of_bytes_read) AS num_of_bytes_read

  29:     ,SUM(num_of_bytes_written) AS num_of_bytes_written

  30:     ,SUM(io_stall) AS io_stall

  31: INTO #Sample2

  32: FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs

  33: INNER JOIN sys.master_files AS mf WITH (NOLOCK)

  34:     ON vfs.database_id = mf.database_id 

  35:     AND vfs.file_id = mf.file_id

  36: GROUP BY LEFT(UPPER(mf.physical_name), 2);

  37:  

  38: INSERT INTO Diagnostic_WH.dbo.[IO-Stats]

  39:     (Drive

  40:     ,DriveType

  41:     ,num_of_reads

  42:     ,io_stall_reads_ms

  43:     ,num_bytes_read

  44:     ,read_latency

  45:     ,num_of_writes

  46:     ,io_stall_write_ms

  47:     ,num_bytes_written

  48:     ,write_latency

  49:     ,io_stall

  50:     ,RowDateTime)

  51: SELECT

  52:     t.Drive

  53:     ,CASE 

  54:         WHEN t.Drive='C:' THEN 'SSISDB/System'

  55:         WHEN t.Drive='H:' THEN 'mdf Data'

  56:         WHEN t.Drive='L:' THEN 'ldf LOG'

  57:         WHEN t.Drive='E:' THEN 'tempdb'

  58:         WHEN t.Drive='T:' THEN 'Indexes'

  59:     END AS [DriveType]

  60:     ,t.num_of_reads-s.num_of_reads [num_of_reads]

  61:     ,t.io_stall_read_ms-s.io_stall_read_ms [io_stall_read_ms]

  62:     ,(t.num_of_bytes_read-s.num_of_bytes_read) [num_bytes_read]

  63:     ,CASE 

  64:         WHEN (t.num_of_reads-s.num_of_reads)=0 THEN 0

  65:         ELSE (t.io_stall_read_ms-s.io_stall_read_ms)

  66:                 /(t.num_of_reads-s.num_of_reads)

  67:         END AS [read_latency]

  68:     ,t.num_of_writes-s.num_of_writes [num_of_writes]

  69:     ,t.io_stall_write_ms-s.io_stall_write_ms [io_stall_write_ms]

  70:     ,t.num_of_bytes_written-s.num_of_bytes_written [num_bytes_written]

  71:     ,CASE

  72:         WHEN (t.num_of_writes-s.num_of_writes)=0 THEN 0

  73:         ELSE (t.io_stall_write_ms-s.io_stall_write_ms)

  74:                 /(t.num_of_writes-s.num_of_writes)

  75:         END AS [write_latency]

  76:     ,t.io_stall-s.io_stall [io_stall]

  77:     ,GETDATE()

  78: FROM #Sample1 s

  79: INNER JOIN #Sample2 t

  80:     ON s.Drive=t.Drive

  81: ORDER BY t.Drive

I have hit the jackpot!

SQL Padre Helping, SQL ,

No, I am not talking about Powerball or Mega-millions, if I had won either of those I don’t think there would be any reason to carry on with SQL as a career!  Just sayin’ Smile

Anyway, in an attempt to break down, track down or map out the evil of Nested Views, I came across a 4 year old post, titled Detangling Nested Views from @JenniferMcCown, 1/2 of the @MidnightDBA team.

This nifty little script, provides exactly what I need to show “the powers that be” how convoluted some nested views can be. Armed with this and SEVERAL execution plans, I hope I can convince my bosses to allow me to restrict some access around here!

Thank you Jennifer McCown!