Is a Deprecated feature making my job more difficult?

By definition, a “deprecated” feature refers to a feature or specific function in a piece of software that will be replaced with a newer function/feature or just flat out will be no longer available.

Microsoft is notorious for changing and/or removing features in every version of SQL!  SQL 2014 has an extensive list of deprecated features: https://msdn.microsoft.com/en-us/ms143729.aspx.

The one I found out about today is SET FMTONLY (format only).  This particular setting allows a stored procedure to return the format of the results without actually returning results. This feature has been available since SQL 2005 and apparently will be removed from a future version, to be determined later.

   1: USE AdventureWorks2012;

   2: GO

   3: SET FMTONLY ON;

   4: GO

   5: SELECT * 

   6: FROM HumanResources.Employee;

   7: GO

   8: SET FMTONLY OFF;

   9: GO

In my line of work, I do a lot of SSIS packages that export information to flat files.  And my developers use temp tables religiously.  The problem with this, SSIS packages typically have a problem with resolving the columns of the final result set from a stored procedure when temp tables are used.  The use of SET FMTONLY OFF allows the SSIS package to resolve the stored procedure without attempting get the column definitions first so the statements can complete, which in turn presents the SSIS Data Source with the final column definition.

Beginning in SQL 2012 two new features were debuted to replace FMTONLY setting.

sp_describe_first_result_set Returns the metadata for the first possible result set of the Transact-SQL batch.

   1: sp_describe_first_result_set [ @tsql = ] N'Transact-SQL_batch' 

   2:     [ , [ @params = ] N'parameters' ] 

   3:     [ , [ @browse_information_mode = ] <tinyint> ] ]

sys.dm_exec_describe_first_result_set This dynamic management function takes a Transact-SQL statement as a parameter and describes the metadata of the first result set for the statement

   1: sys.dm_exec_describe_first_result(@tsql, @params, @include_browse_information)

Why 2 you ask?  I have no clue.  These two seem to do the same thing, not sure why two different ways to do the same thing, but I digress…

The point of this article is now when I create a stored procedure with temp tables for an SSIS package, I have to pass the T-SQL statements as a parameter into these function first then execute the T-SQL statements.

Maybe I am too much of a novice, but to take a stored procedure with several hundred lines of code and turn it into a variable @tsql, then pass that variable into a function just to get column definition, then EXEC @tsql sounds more difficult than simply typing SET FMTONLY OFF; Apparently smarter people than me have decided this is the best way to do this.

Of course by accident, I discovered another, almost as simple, solution for SSIS packages and temp tables.  Instead of using a temp table, define a Table Variable at the beginning of the stored procedure.  This @TableVariable will be your final result set, insert records into this table. Using this method, your stored procedure can still use temp tables and have a defined result set for the SSIS data source. 

So, to answer the original question:  Is a deprecated feature making my job more difficult? 

Well, Yes and No.  Yes, because I believe the replacement feature is more difficult to execute and write code for, especially when your stored procedure has multiple statements.  And adding more steps to an already lengthy process is never a good thing. 

And No, because I found a just as easy useable solution with table variables. I just switch # for @ and define my columns.  easy as 1…2…3!

MCSA: SQL 2008

After a couple of tries (finding time to study is harder than I thought), I have finally passed my 70-432 to earn my Microsoft Certified Solutions Associate SQL 2008!  Having previously earned my MCTIP: SQL Server 2008 Business Intelligence, I only needed this one test.

Now, I must crack down!  I will study, break, fix and torture a SQL Development server for my 70-457 and 70-458 to transition to SQL 2012!

I know some people in the SQL world do not see certifications as all that necessary and definitely not completely indicative of real world experiences; but this was a personal/professional goal of mine to earn my MCSA SQL 2012.  I am now 2 tests away!

Who knows, I may go for my MCSE!

Learning about Licensing

So, I was working on a project for my employer trying to determine the best course of action for licensing a new instance of SQL Server (i.e. what is the cheapest way to get it legally).  And I came across something I did not know.

Found deep inside the bowels of a 40 something page document, I learned something new from the Microsoft SQL Server 2014 Licensing Guide

SQL Server Developer Edition
SQL Server 2014 Developer Edition is a full-function version of SQL Server software—including all of the features and capabilities of Enterprise Edition—licensed under the Developer Tools model, which is a “per user” model. One license is required for each person that accesses or uses the software.

“Per User” is what I didn’t realize.  If I translate this correctly, I purchase one Developer License and install it on a machine, only I can use it as a development box.  To open it up to a team of developers, each member of the team must purchase a Developer License.  If I have multiple development servers for a multiple person team, this could get expensive.  But hey, it is still cheaper than licensing a server by Cores and most likely cheaper than Server+CAL method.

Write this up as my “learning for the day”!

Some self reflection

Earlier last month, a contest was brewing to be mentored by Paul Randal (b|t).  Yes, THAT Paul Randal of SQLSkills.  Despite knowing my SQL Server level of proficiency, I figured what the heck, it can’t hurt to try and the worst that could happen is I wouldn’t win.  But in Paul’s quirky fashion, everyone won!  That’s right, I am extremely fortunate to be included in the Mentoring Class of 2015 by Paul Randal. I hope to document this experience here.

Now, that all being said, one of the first requirements beyond the obligatory “hellos” was to complete my homework Work Items.  (Eeek gad, homework?  I haven’t heard that word in a long time, not counting my kiddos of course).

They were simple: #1) Who are you? and #2) What do you want help with?  Are they really that simple though? They both all seem simple enough.  All I have to do is provide basic info about me, what I like to do and simple stuff.

It was the second “Work Item” that really got me thinking.  What do I want help with?  Really?  I could write for days where I need help: both professionally, physically, mentally, personally?  But, I didn’t want to scare Paul away so I kept my answer simplified. 

I want to learn! Knowledge is Power! 

Reflecting back on my experiences and then interacting with others in the SQL community has taught me lesson. I am only but a “notch up” from a novice.  There is so much I should learn about SQL.  I always feel like I am a step behind on everything.  I want to catch up. I want to earn my MCSE SQL 2012.  I want to be able to defend my recommendations with facts and documentation.

So Paul, if you are reading this, strap your boots on because this is going to be one hell of ride; I am dedicating the next year to learning as much as I can about SQL Server administration. And hopefully with your guidance, after this year I can say, I am a “real” Database Administrator!

Padre’s Toolbox

I have been working with SQL long enough that I have built my personal “toolbox” for SQL related tools that I use on a regular basis. They range from FREE to minimal cost (by that I mean mostly under $100; yeah, I know I am cheap).

You can find my tools here, Padre’s Toolbox and I hope to keep it updated as I test drive and use more and more different SQL programs.  For now, I will briefly introduce my favorite tools (in no particular order).  In the future, I hope to write a post on how and why I am using these tools.

SQL CoPilot

Since my initial review of this software, Richard Fryar has even further enhanced his product to include Query Plan analysis. 

SQL Server First-Aid Kit

This is from Brent Ozar Unlimited.  Enough Said! You not only get their excellent diagnostic scripts, but e-books, whitepapers, and posters Oh My!

DMV Diagnostic Queries

Here is the most recent iteration of Glenn Berry’s fantastic information queries.  Glenn has been providing these since SQL 2005 and if SQL stores the information, these queries will find it!

Idera Free Tools

A collection of free SQL tools, I find my self using these more and more for “spot checking” issues or problems.

Terminals

Not technically a SQL Tools, but as a DBA we have to sometimes manage the OS itself.  This tool is crucial for me to be able to remote into multiple servers without having to minimize RDP session to switch between servers.

SQL Sentry Plan Explorer

My newest tool in my arsenal, is one that quite frankly scared the hell out of me at first.  Just like Cache Plans in general. But I am mudding through the learning process and I am coming to rely more and more on this program.  I haven’t purchased the “Pro” version yet, so I am still evaluating the usefulness of this program and see if I can justify the cost to my “powers-that-be”

Lock or not to Lock Pages in Memory, that is the quesiton!

We all are aware of the “standard practice” of enabling the Security Policy “Lock Page in Memory” for your SQL Server service account.  Just about everywhere you read, it is pretty much standard practice, and so like an red-blooded “Accidental DBA”, I have always used it.

Until today…, well at least for one particular machine!

Let me preface this blog post by saying: I can’t prove nor do I know how to prove that my solutions was the answer to the problem; but it hasn’t been a problem since the change.

 

Problem:

I have a physical 32 core, 448 GB RAM, SQL Server 2012 Enterprise Edition, her after referred to as “The Beast”!  Unfortunately the Beast got it’s legs chopped off at the knees.  Sometime ago, my company bought a new SAN for this server and other shared servers as well.  This new SAN, would be 10K drives with loads of storage. The Beast was used to 15K drives which would hide a myriad of query problems. This new SAN also had what the vendor called “tiered storage”.  A SSD that would be refreshed every night with ~100GB of the most used files actually read from the SAN since the previous 24 hour check to allow quick access.  I personally don’t believe this is a wise configuration for a SQL server with multiple MDF files greater than 400 GB, just sayin’! We have disabled this feature for now.

Anyway, since the new SAN went online, every so often, especially on larger READ queries, the SQL Server would crash and burn. OS RAM was maxed out to the point RDP was not accessible, SQL Server would create a SQL Mini dump referencing page latch issues and the only way to revive it was to execute a remote SHUTDOWN /r command.  This went on for a couple of months.

Solution:

Then I read, Jonathan Kehayias’ article Great SQL Server Debates: Lock Pages In Memory.  It is a little old, but it is still a good read.  Now this got me thinking, quite possibly this policy may not be for every server.  So I researched and researched the whole concept of storing data in memory, dirty pages, and everything I could about how the SQL Engine manage memory, drives and data.

So, with the thought process that my SQL server engine was trying to take more memory from the OS and literally strangle the Beast out of any life by consuming and trying to add more data pages to the Buffer and not releasing older pages, I disabled the policy Lock Pages In Memory. 

Since then, my Beast has been happy Smile.  I have noticed more writes to the MDF file in the last three weeks, not a steady increase just bursts here and there.  I am thinking that is SQL writing dirty pages back to disk, which wasn’t happening before. The Buffer cache is being swapped out with data being used vs. old data at a more consistent rate now.

At least that’s my story and I’m sticking to it!

Sometimes you just have to step away

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

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!

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!

Follow

Get every new post delivered to your Inbox.

Join 128 other followers