Tag Archives: TSQLTuesday

T-SQL Tuesday #45–Follow the Yellow Brick Road

Leave a reply

SQL TuesdayIs it me or does it seem like we just had a T-SQL Tuesday blog party? These days are just flying by! This week Mickey Stuewe (b|t) is hosting and she has brought up a fabulous question about auditing with the topic being Follow The Yellow Brick Road. I have been on both sides of the fence in shops where there has been very limited auditing versus mandated regulatory auditing of their systems – so what’s my preference? Glad you asked; pull up a chair as we travel down the yellow brick road from munchkin land while trying to avoid the wicked witch and her ape like minions.

What to Audit?

As we start out on this journey, there are multiple questions on what needs to be audited one should ask themselves. Let’s face it, there are many wicked witches out there that just flat out want to get to our data and the longer I’m in the business I’ve seen more and more attacks from within, then from the outside. Here are a few ideas on some things to audit:

  • Tables with sensitive data such as SSN’s, personal information, transaction data
  • Review of your QA, UAT, and Prod environments (some shops like to audit all 3 some like to only audit Prod) – don’t be the one who audit’s none!
  • Check-ins of code into your repository
  • Inserts
  • Updates
  • Growth trends
  • Security Access
  • File Shares

These are just a few ideas, but don’t limit yourself to just auditing who inserts or updates data into your systems. As you fly through the house like Dorothy did in the SQL tornado remember to think outside the box some and audit other areas such as growth, baseline trends. The SQL environment is always evolving, use the necessary tools to keep one step ahead of the storm.

How To Audit

All right, so I’ve identified what I want to Audit. It seems like it is a daunting task and I have no clue where to begin; that’s where the Tin Man comes into play. If you are a Data Professional then you have to care about the data enough to even be reading this post – – means you have a heart.

There are many different avenues you can take to audit your systems, a few of those are:

  • Triggers within SQL
  • 3rd Party Auditing Tools
  • CDC
  • Home grown utilities

Whatever avenue you decide to take, just take the stance of doing something. Doing nothing is not something I would advise; knowing who is changing your data, schema, files, etc. is an important aspect of any data professionals life – who knows if you have a mighty Wizard of Oz such as your auditing department or compliance department they might just be thankful for your efforts.

Summary

When you are going through your own SQL journey on the yellow brick road remember that auditing can be viewed upon as an asset. Look into what you are doing currently, can you improve upon it? Are you doing anything at all? Is your data being protected? Even better do you know if your data is being protected?

Take the time to put some safeguards in place in the end you will be glad you did.

T-SQL Tuesday

Wait a minute, wait just a minute. What is this T-SQL Tuesday you speak of. The mighty Wizard of Oz, Adam Machanic (b|t), started this party in 2009. Basically each month on the first Tuesday an invitation is sent out that describes a topic for that month; the second Tuesday bloggers put together a post regarding the topic and then send it back to the person who is hosting. If  you have a blog and are interested contact the mighty Wizard of Oz and he’ll get you on the schedule.

T-SQL Tuesday #040: File and Filegroup Wisdom

2 Replies

SQL Tuesday

It’s that time again for the T-SQL Tuesday party! This party was created by none other than Adam Machanic (Twitter). If you are interested in hosting a party at some point this year give him a shout; you need to have participated in two T-SQL Tuesdays along the way and also maintain your own blog for at least 6 months.

Now that we have what the party is all about let’s get into what this month’s party is centered around Filegroups and his hosted by Jen McCown / MidnightDBA

My focus today is garnered toward indexes on filegroups and what they can do to your index strategy. I’m a big fan of having strategies when tackling issues, problems, or even believe it or not from the beginning of a project. Placing indexes on filegroups carefully can improve query performance (at the same time I want to note that indexes can also hurt performance in some situations so thorough testing needs to be taken into consideration).

Back from my 2008 R2 studies, if my memory serves me correctly, indexes are stored in the same filegroup by default; a non-partitioned clustered index and the associated table always reside in the same filegroup however you can do one of three things:

  1. You can partition both clustered and non clustered indexes to span multiple filegroups
  2. Create non clustered indexes on a filegroup
  3. Move a table from one filegroup to another

You can achieve performance gains by created non clustered indexes on a different filegroup if the filegroups are using different physical drives. The data and index information can be read in parallel by the multiple disk heads when the physical drives are on their own controllers.

One cannot foresee the access that will transpire or when it will happen, a better decision to spread your tables and indexes across all file groups might be of help. This would guarantee all disks are being used and accessed because all data and indexes would be spread evenly across all disks.

To bring this all back together you can think of a filegroup in its simplest of forms. Every database that you create has at least a data file and a log file, and every database has a primary filegroup. The filegroup contains the primary data file and any secondary files that are associated with it. One filegroup can contain multiple mdf/ndf files.

In the end I have seen significant gains with indexes being placed on specific filegroups, but as I stated before it is good to test all this out. Set up some scenarios on your test server and start doing some test cases to prove different theories and ideologies. One thing to remember as well is not every case is the same; ensure that the decisions you are making is good for what you are working on; never take a suggestion and drop it into a production environment. Prove the statement to be true or false no matter who it comes from.

Well, that’s a wrap for today’s party. Until next month…….

T-SQL Tuesday #38 – Standing Firm

5 Replies

SQL TuesdayIt seems as though months are coming and going at an alarming rate but nonetheless I can say I am enjoying the ride. Today’s T-SQL Tuesday party is hosted by Jason Brimhall (B | T) T-SQL Tuesday #38 – Standing Firm.

Jason has provided a three word topic that entails the words resolve, resolute, and resolution…given these three words one must come up with a story based on one of them.

I believe I have chosen mine and have taken a somewhat different spin on it and how I applied it to T-SQL and my DBA career.

RESOLVE

The word as some various meanings but pertaining the expression I would like to use; I will use it literally as a noun “A determination or decision; a fixed purpose”.

Many of us who are DBA’s (which I am proud to be one) have all taken various routes, various avenues if you will, along the way to get where we are at. Growing up I had a passion and desire to play the game of basketball. I would train very hard; my days would consist of waking up at 4:00 a.m. prior to school, go to the gym where I would meet my assistant coach, shoot 700 jumpers and 100 free throws, shower and go to school, practice after school with team, and then repeat the same process daily. My summers consisted of going to various camps in the United States where coaches from all over would attend with my hope of one day gaining a scholarship. I am 6′ foot tall and not overly fast I was always told that I was too small to play college and too slow. The day finally came for me to graduate and guess what ~ I made it to college to play the game I had worked so hard at for so long.

After my career was over now what? I started out in Physical Therapy but a friend of mine showed me a program he had developed. Once I saw the inner workings of what he was doing it I was intrigued and went down the path of becoming a developer to which led me into the DBA world. You know what; along those same lines I got the same rhetoric from people….”You can’t do that; you don’t know enough”; “You can’t do that; stick with sports its to long of a road for you”. Resolve – a determination or decision; a fixed purpose. You see I had a fixed purpose when playing basketball. I knew what I wanted and where I wanted  to go. The same can be said with becoming a DBA or if you have a desire to learn something in T-SQL; if you  have a desire to learn something new in SQL or track down a long running query and improve it; show some resolve – have a fixed purpose. Don’t ever let anyone stand in front of your dreams to become something you want to be. It takes hard work and dedication and a drive to become not just a good DBA but an exceptional DBA.

In the end you will build a strong character and life values. What I learned on the court has carried over to my work habits. I love being part of a team, but I also enjoy when the game is on the line and SQL tasks are at hand and it is fourth quarter and systems are down I want to be the one to take the last shot.

I said all that to say this, if you are faced with “potholes” or “adversity” along your journey from a technical standpoint show some resolve. The community as a whole is here to help and don’t lose sight of your endpoint; granted that endpoint may change many times but remain purpose driven and adapt when need be.

Hosting a T-SQLTuesday Party

If you are interested in hosting a T-SQL Tuesday Party contact the founder Adam Machanic (B | T). It is a great way to get involved in the community and provide an avenue to share your ideas.

 

T-SQL Tuesday #37: Invite To Join Me In a Month of Joins

2 Replies

TSQL2sdayThis month’s T-SQL Tuesday is being hosted by Sebastian Meine (blog | twitter) and his topic is anything and everything about “joins”. He’s dedicted the whole month of December to this topic.

Having had the opportunity to do extensive SQL work as a developer when first starting out I have been accustomed to joins for some time. Joins are one of the basic constructions of SQL and Databases as such – they combine records from two or more database tables into one row source. Depending on join type and join restrictions returned row count can be from 0 till all possible combinations of involved tables. Databases are built to make joins as efficient as possible. It means almost always joining data in database is more efficient than doing that somewhere else. It also means one has to know the power and possibilities of joins to fully exploit their strength.

Types of Joins

These are the most common joins that I have ran into:

  • INNER JOIN – only rows satisfying selection criteria from both joined tables are selected.
  • LEFT OUTER JOIN – rows satisfying selection criteria from both joined tables are selected as well as all remaining rows from left joined table are being kept along with Nulls instead of actual right joined table values.
  • RIGHT OUTER JOIN – rows satisfying selection criteria from both joined tables are selected as well as all remaining rows from right joined table are being kept along with Nulls instead of actual left joined table values.
  • FULL OUTER JOIN – rows satisfying selection criteria from both joined tables are selected as well as all remaining rows both from left joined table and right joined table are being kept along with Nulls instead of values from other table.
  • CROSS JOIN – A cross join performs a cartesian product on the tuples of the two sets.

Usually cross joins are used quite rarely; some of the scenarios could be as follows:

  • Possibility to generate high amount of rows. As we can see from relatively small tables there is possibility to get quite monstrous numbers.
  • Find out all possible row combinations of some tables. Mostly this is useful for reports where one needs to generate all combinations for example all nationalities x genders for persons.
  • To join a table with just one row. Most often used to get some configuration parameters.

ALIAS JOINING

Some other questions I’ve been asked over the years is proper aliasing. I’ve had people use the a,b,c, method and a short table def. for example:

Example 1

Select *

from table 1 a (nolock)

inner join table 2 b on a.id = b.id

Example 2

Select *

from customer cust (nolock)

inner join address adr on cust.id = adr.id

The Performance

I’ve seen queries astronomicaly long with nasty subselects, joins, union alls, etc. Alot of times in my query tuning and looking at poorly designed queries, stored procedures, udf’s etc it is quickly seeable whether joins are being used correctly or incorrectly. When one takes the proper time to review the database and the schema and then build the queries with powerful joins performance can be quite optimal.

Future T-SQL Tuesdays

I urge you to take the time to check out Sebastians site and hey while I’m at it if you feel like you want to host a T-SQL Tuesday give Adam Machanic a shout. I can tell you from experience that I’m glad I did. Getting involved is a great way to get going with the SQL Community and the SQL family.

Thanks Sebastian for hosting this month!

The Roundup – #TSQL2sday

2 Replies

Another T-SQL Tuesday has come and gone and I’ve got to admit that all the responses that were received are pretty awesome and even had a few responses from first timers that attended the PASS Summit 2012. While all the posts were great, I had one that blew me away and is exactly what the Community is about. Kendal Van Dyke allowed a post to be tied to his blog by Andy Levy who currently didn’t have a blog and is fairly new to SQL. If you get a chance check it out.

One theme that I read over and over again is that we are one huge family and we have each others backs. The Community has a whole goes out of their way to assist our fellow members. Below is the roundup from everyone who chimed in. Thanks everyone for their participation!

Remember if you are interested in hosting please contact Adam Machanic (B|T)

POSTS

Jason BrimHall – SQL Family – After Summit 2012 | SQL RNNR

Rob Farley – Rob Farley : SQL Community – stronger than ever

Aaron Bertrand – T-SQL Tuesday : Reflections on the PASS Summit and our community

Robert Davis – T-SQL Tuesday #36 – SQL Community | SQLSoldier

Chris Shaw – Esprit de Corps T-SQL Tuesday #36 « Chris Shaw’s Weblog

Valentino Vranken – BI: Beer Intelligence? · PASS Summit 2012: Impressions Of A First Timer

Steve Jones – T-SQL Tuesday #36 – What Does Community Mean? « Voice of the DBA

Oliver Asmus – T-SQL Tuesday #36 – What Does the SQL Community Mean To You? | OliverAsmus.com

Edwin Sarmiento – PASS Summit and The Value Of Building A Community

Wayne Sheffield – T-SQL Tuesday #36 – Community

Mike Fal – T-SQL Tuesday #36 (#tsql2sday) – Coolness factor | Art of the DBA

Robert Pearl – TSQL Tuesday – What SQL Community Means To Me

Tamera Clark – SQL Community and Family

Kendal Van Dyke (On Behalf of Andy Levy) – T-SQL Tuesday #36 – SQL Community (Guest Post)

Kerry Tyler – T-SQL Tuesday #36: What Does the SQL Community Mean to You (Me)?

Argenis Fernandez – T-SQL Tuesday #36 (#tsql2sday)– Post-PASS Summit Depression

SQL Asylum – T-SQL Tuesday #36 SQL Community how you can get involved

Dev Jef: – T-SQL TuesDay #36 – What does the community mean to you? « SQL from the Trenches

Mickey Stuewe – T-SQL Tuesday #36 – What Community Means to a Newbie « Mickey’s T-SQL Ponderings

David Maxwell – T-SQL Tuesday #36: What Community Means To Me | David Maxwell on SQL Server

Stacia Misner – Data Inspirations » We are (SQL) Family! (T-SQL Tuesday #36)

Jenny Salvo – T-SQL Tuesday: What Does the SQL Community Mean to You? | Salvo(z)

T-SQL Tuesday #36 SQL Community / What Does the Community Mean To You?

40 Replies

What? Tuesday already? Is it me or is time really flying by?

Well, I’m fortunate enough this month to host Adam Machanic’s (B|T) concept of T-SQLTuesday #36. I’ve found the previous T-SQLTuesday hosts topics to be very interesting and I hope that I can do it some justice.

Over the course of the last few weeks I’ve gone through some health concerns, a World Series has taken place, Sandy has occurred up North of me, Nascar is ending it’s season, College football is ending, spoke to a group of DBA’s last weekend, attended various webex sessions, etc. One common thread that kept coming to my mind is Community, team work, team effort.

Ironically enough, this week is the PASS Summit 2012. It’s killing me not to be in attendance but I am thankful for PASSTV and for co-workers of mine who are getting to attend for the first time (5 total).

Merriam-Webster defines Community as “a unified body of individuals”. For me the SQL Community is something that has helped me in my career; whether it is questions that I’ve had along the way where I was stuck, helping other DBA’s with issues they were having, networking with other DBA’s or making contacts for the future. The SQL Community is just that; we are a team. All on the same team; if one falls we pick each other up. I’ve never been part of a group of people who want to help each other more so than the SQL Community.

One of the best conferences I’ve been to is the PASS Summit. I was fortunate enough to attend last year and this years will provide new attendees the same fortune and opportunities that I have had. Sitting and seeing some of the top DBA’s in the industry learning in sessions along with me…..yeah I was floored.

So my question today is a simple one; I had several topics to choose from technically but I’m curious as to what others think about our SQL Community. Not just some off the cuff answer but really what do you think about it and how has it helped you?

Below are some thoughts I had in creating this topic:

  • How has the community helped me in my career
  • How can I better the community
  • How can I preserve what we already have
  • How can I help other people in the community

With that said there will be some first timers and there are rules to follow:

  1. Your blog post must be published between Tuesday, November 13th 2012 00:00:00 GMT and Wednesday, November 14th 2012 00:00:00 GMT
  2. Include the T-SQL Tuesday logo (above) and hyperlink it back to this post.
  3. If you don’t see your post in trackbacks, add the link to the comments below.
  4. If you are on Twitter please tweet your blog using the #TSQL2sDay hashtag. I can be contacted there as @YatesSQL, in case you have questions or problems with comments/trackback

Many thanks again to Adam Machanic; if you’d like to host a TSQL Tuesday please give him a shout!