Select * Syndrome

NoSomething that I have seen lately over and over again and even ran into this morning is a practice that I would say is a pretty bad habit in SQL….the dreaded Select * syndrome

This method is heavily used for adhoc querying and I’ve seen it used in some troubleshooting scenarios but in my case I don’t have room for it in a production environment embedded in functions, procedures, or views.

To me it is a wasteful tactic in bringing back what is needed; it can produce unwanted scans or look-ups when in some cases all that is needed is index tuning. I’m a big fan of bringing back what you need instead of bringing back a tremendous amount of data. One can also make an argument for all the unused overhead it can produce.

I cannot begin to tell you the many times of deploying something out and then to find out the schema has changed and the select * in a view that was left in place years ago is my culprit from years of past coding that has been done.

For example; one that I have seen within the past couple of months is a view:

Select *

From table 1

Union All

Select *

From table2

This was being used quite frequently and is just asking for trouble and poor performance. There will always be excuses as to why it wasn’t done differently but in the end you will go back in and clean it up so it is best to think the process you are working on through in the beginning instead of the end.

Can I ever use it?

Sure….I’ve seen it used in If Exists clauses many times over and from my research and what I know SQL does not count this in the execution plans; if you leverage SQL the correct way it is more than powerful to handle what you need.

Tools to fight the good fight………

My source control is TFS and I like the newest version as you can set up controls that if a Select * is found it will break the build in dev forcing it to be resolved

If you haven’t already downloaded the free version check out the SQL Plan Explorer provided by SQL Sentry. Execute the tasks different ways with the select * and with pulling back designated columns and review the execution plan; you will be surprised at the outcome, and if you are old school that is fine too – analyze it in SSMS and see what you find.

Dashboard Time

AutomationI was fortunate enough to attend the PASS 2011 Summit in Seattle. If you do not know what I am speaking of when I say PASS I encourage you to check it out. PASS stands for Professional Association for SQL Server. The event that is put on yearly speaks for itself and I can dedicate a whole blog to just that but no; I’m going to speak of something I picked up while at the conference.

SQL Server MVP – Deep Dives Vol 2

This book has a plethora of valuable information and golden nuggets so much so I figured I’d implement something on my own that I can use everyday from it. There are countless number of good authors in this book

The Dashboard

I’m on a team that runs a full range of SQL servers from 2000 to 2012 on physical and VM’s, but chapter 12 stood out to me the other day which I decided to tried out. I’ve built reports and metrics in the Utility Database (idea spawned in my head after attending a session by Chris Shaw (B|T) but I started thinking of building a dashboard off the information.

Pawel Potasinski (B|T) wrote a chapter in this book called “Build your own SQL Server 2008 performance dashboard” – as I read through the chapter ideas started to spin in my head and before I knew it I was giving it a try.

I combined some of his ideas with the metrics I pull back using Glenn Berry’s (B|T) Diagnostic Queries and built a standard dashboard for myself that gets generated every morning when I walk in the door. In it I include some of the basics such as CPU, PLE, %Log Used. Pawel uses DMV’s and SQLCLR to get the performance counters; I’ve started to incorporate some extended events results in there as well.

Some additional items I’ll be incorporating in the near future is further drill downs into the details of the counters themselves and sharing the report out to the team I am on as a custom report. Once I have everything completed my plan is to make another post entry with the screen shots, code, etc.

In the end I would say I was not fully taking advantage of what SQL Server has to offer for me….are you? I’ve enjoyed digging further into Reporting Services and what I can leverage from it in administering databases I’m responsible for. Take a look at what your processes are and if it isn’t automated how can you better leverage your time and can it be automated?

Where Do I Start – The Journey?

journey

I’ve recently been approached by numerous people at work, on boards, forums, twitter, etc. regarding different ways to learn and improve one’s skill set.

The Forums

I find myself on forums more so than normal perusing through questions, providing feedback where applicable and so on. Some of the ones I traffic often are:

  1. SQL Server Central
  1. The SQL Brit’s Forum

I think forums are a great avenue to see what others are sharing on topics that I might have gone through, are going through, or will go through.

Bloggers

I enjoy reading/following bloggers of who I consider to be at “the top” of the SQL game. I will not list them all out here but if you look to the right you under DBA Blogs you will find additional links of blogs I follow. Over my decade in dealing with SQL I have tried to model and pick up some of the knowledge that they share on a regular basis. Take time to hit some of their sites and remember if you do reach out to one of them I always say be respectful; a lot of their time is providing free knowledge to the community which is what helps make the community grow and be better; be sure to thank them for all they do.

Email Subscriptions

My days normally consist of several hundred emails daily but I try to take a certain part of my day and read the emails I subscribe to; I really enjoy MSSQLTips, SWUGG, the live burn feeds I receive from the bloggers I follow, and several more. Getting involved and subscribing to some of the top tier SQL sites provides another avenue for learning opportunities, networking, etc.

Books

Really depends on what SQL Server versions you are running right now. A lot of people have been asking me about 2008 R2 and for that I liked the DBA Cookbook; felt it had some good stuff in there. Red Gate puts out a lot of good stuff along with the SQL Deep Dive editions.

Training Sites

Some ones that I’ve used or recommended in the past are:

  • Pluralsight – I’m a big Paul Randal and Glenn Berry fan so when SQLSkills.com joined it was a no brainer for me.
  • SQL Course
  • PASS (take advantage of the local chapters and virtual chapters)

Closing

I read an article this week by SQL Brit (John Sansom) regarding “Overconfidence – How it Almost Cost Me and What You Can Do to Avoid It”. After reading it I felt like it was one of the better ones I’ve read in the past month. It put things into perspective in a sense that while I’m very confident in my abilities you can never become “lax”. I am a believer in learning everyday and I will continue to hone in on my skillset; at the same time it is imperative that we do not just “assume” or “take for granted” the regular routines we do on a daily basis.

These are some of the things (not all) I like to dig into on a regular basis and I’m sure you have your own; I’d love to hear what others utilize if you have time drop me a line and I’ll share it out on the post so other community members who pass by might be able to gain something from it.

Where Did January Go?

Well, January seemed like a blur work wise to me.  It normally is the busiest time of the year; more so than the other months which would explain my scarce posts for the month. I’ve been fortunate enough to catch some of the forums that John Sansom has over at his place and one of the threads I got involved with was regarding time.

Time is of the essence it seems anymore and where you devote your time whether it is family, blogging, technical writing, work related tasks, and so on it all requires time. During the day I found myself caught in the endless trap of using the time crutch of not being able to provide posts on topics I deal with everyday.

In order to remedy this I am starting to re-organize the way I am handling things so I can be more consistent with the content getting out on this blog; if you were to look at my OneNote section you will find a plethora of topics that I haven’t been able to touch yet.

I look forward to starting this month off the way I want to and allow for specified times to write.