sp_helpExpandView – Well I didn’t expect that…

Posted on

So a few minutes ago, I had a random thought.  sys.dm_sql_referenced_entities(), which is the fundamental basis of sp_helpExpandView, works on an assortment of objects, not just views.  Hmmm… I wonder… let’s run it against a stored procedure?  BOOM!  I had a list of all objects referenced in that proc!!!  And of course, all of the views that were referenced were also expanded out.

Well that’s nifty!  You can use sp_helpExpandView to quickly assess objects referenced by a stored procedure!

Yep – I planned that feature… sure did!  Hah!

Check out the latest version here:  sp_helpExpandView

Debuting sp_helpExpandView

 

Debuting sp_helpExpandView

Posted on Updated on

Who here hates nested views? I sure do. And whenever I find them used in code, untangling them can be a behemoth of a task.

Having faced them one too many times, I finally became fed up. I thought to myself, that there has to be a better way than opening up each object, checking each table reference, and crawling further and further down the rabbit hole. SQL Server must have functionality buried within it that would allow me to programmatically dissect and untangle a nested view.

So I started writing a script against system views. But then I ran into some roadblocks. As I researched those, I discovered the DMF sys.dm_sql_referenced_entities(). And thanks to that wonderful DMF all of those problems went away.

The result is a script tool that I am calling sp_helpExpandView!


TL;DR

sp_helpExpandView will take a view & return a list of all objects referenced underneath. V1 supports references to child views, tables, inline functions, table valued functions, and 1 level of synonyms.

Output comes in two formats – vertical and horizontal.  Default is ‘all.’

Usage:
EXEC sp_helpExpandView
@ViewName = ‘[schema].[view]’,
@OutputFormat = ‘[all|vertical|horizontal]’

Download Here V2 released 2015-03-03

Known Issues:

  • Synonyms against objects not fully qualified with 3 part names will not be correctly processed.
  • Does not process 3 part names correctly. ex: ([db].[schema].[object])
  • If no schema specified, does not default dbo. schema.
  • Error in output if 3 part name used in underlying code & no schema specified. ex: ([db]..[object])

I hope people find this helpful.  I would love to hear any suggestions or feedback you may have.

Special thanks to Ben Thul (b|t), who mentioned PARSENAME() to me on Twitter. I’d forgotten that function existed & it helped clean some things up.

Debuting Cleaning House – Indexing Edition

Posted on

Five weeks ago, my good friend Gina Meronek contacted me about MADPASS. Seems their scheduled speaker for February had to cancel, so they were trying to find someone to fill the spot. I only had my first and only presentation, Every Byte Counts, in my portfolio, and presented it to MADPASS last August. Buuuuutttttttt… I’ve been wanting to add a second presentation to my portfolio. I’ve had the primary idea brewing for several months but just had not gotten around to finally doing it. Well, nothing motivates like a hard deadline!

Flash forward to last night, where I debuted Cleaning House: Indexing Edition. I was pleasantly surprised that despite the lousy February weather, a couple of dozen people made their way out. Another dozen or so joined the online broadcast too! And from the questions & feedback I got, I think the session was a resounding success! I already have some ideas on tweaking content, and definitely must focus on time management, but all in all, I’m extremely pleased.

Many folks asked for my slides & demo scripts, which I’ve just finished bundling up for distribution. You can get them here.

http://1drv.ms/1LNvmkk

Be sure to check the Readme.txt file, which has links to 2008 & 2014 .bak files of my demo database.

Thank you again MADPASS, for having me back, and thank you to everyone who came out, sat through my presentation, gave me great feedback, and hopefully learned something new and practical!

2014 Tribal Awards

Posted on Updated on

About a month ago, I learned that I was nominated for the 2014 Tribal Awards held by SQL Server Central & Simple-Talk. These are fun awards, whose nominees & results are determined by the SQL Server community at large.

I remember sipping my coffee before work, skimming Twitter, and seeing Jen Stirrup (b|t) tweet that she’d been nominated for a category. I thought “oh cool, I ought to check out the full list later today.” Eventually I pulled up the nomination listing and nearly spat out my coffee. My name was listed under the Best New Community Voice category!

Before I continue the story – THANK YOU to those who nominated me. It still humbles me that someone even thought to list my name and raise me up as a nominee.

Yesterday, the results of the community voting were announced. I was delighted to learn that my good friend Catherine Wilhelmsen (b|t) won our category. I still remember meeting her at Summit 2013 and have loved watching how she’s exploded within the SQL Server community in the year thereafter. I was thrilled for her – she’s definitely a rising rockstar in the community.

Catherine W. & Andy Y.

Then Simple-Talk surprised me with a tweet…

 

TribalAwards2014

 

Wow! Let me tell you, that made my day!  Here’s the official announcement & full results.

So THANK YOU to the SQL Server community at large, for not only everything you’ve given to me, but for inspiring me to give back as much as I can and to continue doing so!

 

TIL #7 – Right-Sizing Your SQL Server VM w. David Klee

Posted on

I’m back again for another TIL Holiday Learning Series entry! I’m building on TIL #6 with David Klee’s (b|t) other Summit Session: Right-Sizing Your SQL Server VM. I’m very happy that I did, since he builds on some of the talking points.

DISCLAIMER: In the interest of not “exposing” each presenter’s entire session, I’m not going into great detail or documenting “everything” I pulled out. After all, I want to encourage everyone to view the session for themselves.

Here’s my list of interesting notes & tidbits:

  • vCPU scheduling – we want to minimize idle time. Scheduling is how too many vCPUs can hinder, not help a VM.
  • vCPU counts matter. Size for what you need TODAY, not necessarily what you MAY need in the future.
  • vNUMA configuration also matters. Align with pNUMA.
  • Search David’s blog for post on how to set up Perfmon end-to-end.
  • Avoid using Host Memory Overcommitment, due to how SQL Server will grab all possible RAM, will cause more thrashing.
  • Fantastic demo scripts for gathering & analyzing performance metrics.
  • David announced that he is working on a product, that can run in the background on a SQL Server, and determine the approximate number of vCPUs & memory allocation required for that particular server.  This tool will be FREE.  Contact David if you wish to be a beta tester.

Must say that I was quite pleased with this session. It is aiding me in ramping up my knowledge of VMs. But not only that, but it is helping me become far more comfortable with diving into the world of virtualized SQL Servers, and understanding their real world potential and how to leverage them efficiently and effectively.

One final note – this session was shown live on PASStv, which also means that the recording is one of the few sessions that are freely available.  Click here to watch it yourself!

TIL #6 – Achieving Peak Performance from your Virtual SQL Servers w. David Klee

Posted on

Merry Christmas Eve to my SQLFamily! For today’s TIL Holiday Learning Series session, I selected my good friend David Klee’s (b|t) session: Achieving Peak Performance from your Virtual SQL Servers. I’ve been increasing my interest & knowledge of VMs and have a number of VM related Summit Sessions that I intend to watch, so this was a good one to refresh & get started with.

DISCLAIMER: In the interest of not “exposing” each presenter’s entire session, I’m not going into great detail or documenting “everything” I pulled out. After all, I want to encourage everyone to view the session for themselves.

Here’s my list of interesting notes & tidbits:

  • Abstract hardware away & think about things as “resources” & “queues” instead.
  • In VMs, there will be “hard limits” on resources. Physical CPUs. X amount of RAM. SAN IOPs. Interconnect path speeds.
  • In VMs, there will be “soft limits” on queues. Memory oversubscription. CPU Scheduler Contention – 8 physical cores & 100 virtual cores. “Noisy neighbors.”
  • VM Perf Counters Lie – Search for Klee’s blog Post
  • Storage Test – check out DiskSpd on GitHub. New benchmarking tool.
  • Latter portion of session has lots of general “best practice” recommendations.

I’ve had the fortune of seeing this session before, but for me ramping up on Virtual Machine tech, it was a great refresh/primer. If you’re in the same place I am, then this is a great starter session to get you more comfortable with running SQL Servers in a Virtual environment. And it’s got me excited to check out some of other VM related Summit Sessions next!

TIL #5 – Are Your Indexes Helping or Hurting? w. Jes Borland

Posted on Updated on

After taking the weekend off, I’m back with my next TIL Holiday SQL Learning series entry. Tonight, I chose Jes Borland’s (b|t) session Are Your Indexes Helping You or Hurting You? I remember running into Jes in the Community Zone, after she finished this presentation, and she was elated with how well it went. This made me really excited to check it out once recordings became available, so here we are!

DISCLAIMER: In the interest of not “exposing” each presenter’s entire session, I’m not going into great detail or documenting “everything” I pulled out. After all, I want to encourage everyone to view the session for themselves.

Here’s my list of interesting notes & tidbits:

  • What’s your Workload?
    OLTP = Transactional. Generally write speed is priority over read speed. Narrow, fewer indexes.
    OLAP = Analytical. Generally read speed is priority over write speed. Wider indexes more acceptable.
  • Use the SSMS Split Window functionality – it’s the bomb!
  • SSMS Execution Plan – On an INSERT operator, SSMS hides subsequent operations against Non-Clustered Indexes. Must dig into Properties to see this! Or just use SQL Sentry Plan Explorer.
  • Trace Flag 2371 – Changes SQL Server auto-update statistics threshold, to scale based on # of rows. See KB 2754171.
  • STATS_DATE() – Didn’t know this function existed! Can see when statistics was last updated via sys.indexes. Great for a baseline/benchmarking diagnostic query!

Yet again, Jes hits a home run with this session. I love how she opens, rapidly ramping up and making sure everyone is on the same page. I also recall her talking about how she explained SARGability and how well received it was… and wow, I really liked her clever idea to demonstrate it to the audience! That worked VERY well! I’d say that this session is most definitely a must-watch for anyone who has to consider doing anything with indexing.