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

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!

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!

Hostile Takeover

Last night, I had the privilege of presenting to the Baton Rouge SQL Server User Group (@BRSSUG).  This was my 2nd time presenting to the group and I hope it was as informational as it was enjoyable for me.

The Nuts and Bolts of the presentation

The whole premise of the presentation was to outline and layout what to do when you are given a SQL Server to manage.  In some cases you may “discover” a new SQL Instance on your network and not have access to it.  I presented one method of gaining access through what I call a “hidden door”.

I am a big supporter (even though I don’t do it as often as I should) of documentation!  PowerKnowledge is Power and as our friendly neighborhood Spiderman would say “With great power comes great responsibility”.

What type of information do you need to collect and document on?  I explained several bits of information that I gather on a regular basis and gave several tips and demonstrated some wonderful scripts from people much smarter than I am on how to collect this information.  I have been using these scripts in “My SQL Toolbox” for a  long time.

Here is the slide-deck I used last night with all the links to the various tools of “people smarter than me” that I use on a regular basis. 

Hostile Takeover

On September 10th, I will be presenting at the Baton Rouge SQL Server Users Group (@BRSSUG) on a topic that is near and dear to me.

Since becoming the DBA at Woman’s Hospital a year ago, I have used these techniques and processes almost every day in taking “ownership” of over 90 SQL Server instances. After a year, I still have some instances I have to “deal with”; but I am using these steps to implement my “Hostile takeover”!

I will be discussing my tips, processes and SQL tools I use to gather as much information about the SQL Server instances that I have now become administrator.

I will upload my slide deck after the presentation and will give a debriefing here.

BTW, this is my 2nd time presenting to the User Group, maybe next time I will update my SQL Jeopardy and we can play again!

Well, it is official!

I have been asked, I agreed, and my topic has been accepted.

I am the September Presenter at the Baton Rouge SQL Server User Group meeting!

 

Hostile Takeover

–A SQL Server instance was just handed to you to manage.  OK, great, now what?  What are the logical steps and/or best practices in taking over the management of a SQL Server instance.

 

I will provide more info on my presentation after I present it.

Thank the SQL Gods!

Microsoft has listened to the out cry of SQL DBAs everywhere!

New service packs will be released for SQL Server 2008 and SQL Server 2008 R2 extending it’s lifecycle for another 12 months!

I am not sure about you but I for one am happy.  That give me more time to plan!

SQL 2008 Service Pack Announcement

SQL CoPilot–A Software Review

As the only DBA in a 24 x7, mission critical shop (I work in a hospital) managing over 60 instances of SQL Server ranging from version 8 to 11; I am ALWAYS on the lookout for Software that will help me manage instances.  Heck, who am I kidding.  I want a cheap piece of software (since I have NO software budget to speak of) that tells me what I need to know when I need to know it and be able to present it to the “powers that be” so I can argue for more RAM, more CPU cycles and more SAN space.  And that is exactly what SQL CoPilot does for me as well as many other things.

SQL CoPilot is not really a “program” in the traditional sense of the word.  It does not get installed, it does not run a windows service and it doesn’t require a bunch of resources on target servers.  So how does it work?  To be perfectly honest, I am not sure.  But because it is not compatible with SQL 2000, I can only assume it uses DMV and DMF to determine all the information it needs. Because SQL 2005 and later are designed to return DMV and DMF results quickly, the response time for CoPilot is amazing, even with heavily used machines.

So what can the program do for you?  Just read this “About Page” to see what all information is presented to you in a quick and efficient manner.  This is a snap shot of the main page.  It’s interface is VERY intuitive and simple to understand.  All of the “snap shot” squares found in the “Big Picture” are navigable to a page with detailed information.

image

The information presented for databases is very detailed and very informative.databases-gallery

And the best part?  If you just want a snap shot or a quick glance at your instances, then try out the SQL CoPilot Free Edition, but I promise you, after a week of using SQL CoPilot you will want to buy a full license. You will want to see the additional features: like unused indexes, duplicate indexes, index fragmentation and all sort of other “goodies”.  I know I did!  And for only $120 per licensed USER (that’s right USER, not instance) it is the best bargain out there.  Many 3rd party software that provides this type of information charges hundred’s of dollars per instance monitored.

As much as I have praised SQL CoPilot, for me, there are some downsides.  It doesn’t work with SQL 2000.  But hey, I may be the only schmuck who is still using it in production.  I doubt it, but at least I have fewer instances to really dig up the details on because of SQL CoPilot.

SQL CoPilot does not have a method of “capturing” the data it collects for baseline measurements or historical information.  This is not a monitoring tool with a historical repository, but it is so close to that it makes me want more.  Maybe just a simple button to say, “snap shot it” and it would record all the information for that view in a pre-determined repository database somewhere.

All in all, this product is well worth the $120 purchase price!  Give the Free Version a try and I would be willing to bet you a cup of coffee that within a month you will pay for the full version!

Merry Christmas

A little personal post this morning.   I wish everyone, those I know and those I don’t, a blessed Merry Christmas and a Happy New Year!

YodaSanta

8 Weeks of Indexes: Types of Indexes

This is the 3rd post in a series entitled “8 Weeks of Indexes”.  The purpose of this series was more for me to learn more about indexes. 

There are many types of indexes used in Microsoft SQL. I could easily use up the remainder of my weekly posts to discuss each one in depth, but in order to actually get to the meat and bones of how to use indexes, this post is an overview of the more commonly used indexes.

Each Edition of MS SQL from 2005 and up, continues to add types of index to the engine. SQL 2005 had a total of 7 types of indexes, SQL 2008 added 1 additional type, SQL 2012 has a total of 10 types of indexes and SQL 2014 lists 12 types of different indexes.

My personal experiences with index usage unfortunately has been limited to numbers 1-6 for SQL 2005. I currently do not manage any data store with spatial data, so I haven’t had a need to use that one.  I am really excited about experimenting with Filtered Indexes any I do not (yet) have a SQL 2012 server in production, so I have not looked at the two new added indexes.

Here is the break down of each type of index by SQL Engine. Highlighted indexes are new to that specific edition. 

SQL 2005

  1. Clustered – based on a column or multiple columns, this index determines the physical sort order of how the data is physically written to the data base.  A clustered index for a phone book would be on “Last Name”
  2. Non-clustered – can be created on any column or multiple columns in addition to a Clustered index. this index create a “pointer” to find what row data is located on. If you wanted to know all the “Robert”s in a phone book, you could create a Non-Clustered index on First Name column.
  3. Unique – this ensures that either a clustered or non-clustered indexes key does not have duplicate values.
  4. Index with included columns – extends the functionality of non-clustered indexes to include “non-key” items in the index.
  5. Indexed Views – a unique way to present structured, indexed data in a view rather than a table. can use Non-Clustered as well, but only after a Clustered index was created.
  6. Full-text – a special type of index used exclusively with the Microsoft Full-Text Engine to facilitate word searches within data columns.
  7. XML – a way to index XML data type columns

SQL 2008 and 2008 R2

  1. Clustered
  2. Non-Clustered
  3. Unique
  4. Index with included columns
  5. Full-Text
  6. Spatial  provides the ability to index “spatial data” in a column of geometry data type.
  7. Filtered—provides a way to filter out unnecessary rows of a commonly used subset of data
  8. XML

SQL 2012

  1. Clustered
  2. Non-Clustered
  3. Unique
  4. Column store – based on vertical partitioning of the data by columns
  5. Index with included columns
  6. Index on computed columns – an index on a column that is derived of one or more columns
  7. Filtered
  8. Spatial
  9. XML
  10. Full-Text

SQL 2014

  1. Hash – an index that provides data through an in-memory hash table
  2. memory-optimized nonclustered indexes – used in the new feature of “in memory optimized tables”
  3. Clustered
  4. Non-clustered
  5. Unique
  6. Column store
  7. Index with Included columns
  8. Index on computed columns
  9. Filtered
  10. Spatial
  11. XML
  12. Full-Text

8 Weeks of Indexes: What is an Index?

First and foremost, I am a slacker!  It takes discipline to write a weekly blog, I am not sure how some people can do it daily!  However, I hope to get back on track for this series.

Merriam-Webster defines index as:

a list … arranged usually in alphabetical order of some specified datum

One of the most common real world examples of an Index is your telephone book. The book stores information (Name, address and phone number) sorted alphabetically by last name. The pages are written in such a way that A comes before B and B comes before C, etc.. If one knows their alphabet, then any name can be easily looked up.  Typically the first “key” to finding a name is at the top of the page, which tells you what section of the book you are in.  If you were to locate my entry in the phonebook, you would quickly scan through the key until you found the letter B at the top of the page.  Then you would continue to scan until you find the group of entries for BISHOP.  And of course, then locate which entry matched my name, BISHOP, ROBERT. If there were no key at the top of the page, you would have to seek through all the pages one at a time until you got to the B section.  Another excellent real-world example of an index system, is the Dewey Decimal System.  Libraries have been indexing their books with a numbering system for years. 

So, how does this all relate to SQL Server?  There are several bold print words above that translate to SQL Server terms and how SQL works the same way as a phone book.  To fully understand how SQL Indexes work one really needs to know how SQL stores data. We know SQL has the .mdf files that actually stores all your data.  However, the data file is made of pages that are 8 KB in size.  At the top of each page is a “page header” used to store system information about that page.  There are many different types of pages that store different things, but two specific types of pages I want to talk about are “data” pages and “index” pages.

A “data page” is where your actual data (based on data types) is stored and as you guessed it, the index page stores index information. The “key” to proper storage of data is a Clustered Index.  A Clustered Index, physically writes and stores a row of data in a page by selected column and by sort order selected.  So a Clustered Index on a user table could be by the column “Last Name”, just like a phone book. This will ensure that the data rows are written in alphabetical order on each page and in turn each page will be in alphabetical order as well, very efficient.  SQL Engine “scans” the index to determine exactly what page the “B” last names are located.  If a table did not have a clustered index, the data would be stored in a “first come-first served” fashion.  In this scenario, the SQL Engine would have to scan then entire page or multiple pages to find your entry, very inefficient.  Imagine how inefficient a phone book would be if the publisher just kept adding rows to the end of the book every year without being sorted by name.  How long would it take you to find my name then?

So, the key to storing data in SQL, is to have a pre-determined way you want the data rows saved to the page. Ideally this would be the most used method of finding a row, i.e. by “Last Name”. 

Next week…..Types of Indexes

Follow

Get every new post delivered to your Inbox.

Join 111 other followers