T-SQL Tuesday 109: Influence Somebody Recap

Comments: No Comments
Published on: December 18, 2018

How have you impacted somebody in the community?

This month I am in charge of the topic for TSQLTuesday. The invite was published here a couple of weeks ago. I knew I was picking something rather difficult for people in general and probably more difficult for technologists due to the notion of jumping head first into a discussion with somebody about yourself. I have been asked by previous employers to do my own personal annual job review. This idea is very much like that – but more personal.

Some say it eats at the core of being prideful to discuss this topic or to broach the “review” with others. I like to take a different angle on it. I see it as a means to personal growth and has nothing to do with bragging. I tend to find that people gifted with humility will ask others how they could improve professionally or personally. Being able to find where you are doing well or not so well is key to progress. I would hope those that participated found this difficult exercise helpful in uncovering a new angle on personal and/or professional improvement.

 

Discomfort

There is no hiding from the fact that this task provided a great deal of discomfort and probably angst as demonstrated from this tweet.

 

Multiple participants, as you will see as you read their articles, shared their feelings on the level of discomfort and difficulty they had with the topic. I expected the discomfort, I felt it too.

As I read the articles, I could feel the discomfort alongside the author. I get it – it’s hard because none of us want to come off as bragging about ourselves. Take into account that discomfort and then the message that each of these people has to share with you!

WHO?

Steve Jones (b | t) – Influence. Steve is not comfortable writing about this topic as he dives in to share with us. As he does he hits a very strong note for me. We, the SQL Community, give a lot of ourselves and that giving grows exponentially. We give of ourselves and many times we end up the recipient of this exponential experience.

It can be scary and intimidating to share knowledge with others publicly, but it is also immensely rewarding! -Steve Jones

I believe the same can be said of sharing a personal review publicly.

Wayne Sheffield (b | t) – Influence Somebody. I personally know that Wayne had a hard time with this topic and sharing an article. Wayne shares a couple of stories outlining some very small actions on his part. He was largely unaware of his influence and in turn ended up dumbfounded and awestruck at what his influence meant to other people.

Small actions. That’s all it takes to make a positive difference in a person’s life. Be interested, and encouraging. You never know what kind of effect you are truly having on people. And when you do find out, prepare to be astonished. – Wayne Sheffield

Malathi Mahadevan (b | t) – To Influence and be Influenced. Mala is a very humble person. Mala shares a personal experience about her influence several other people as she worked on her very first book project this past year. The really cool part is the joy she got from seeing many people filled with the joy of having the opportunity to be in the book.

It is a positive way to end the year on a tone of gratitude to people who have influenced you – and it is doubly positive/uplifting to see/hear of what you have done to other people too. – Mala

I am grateful for the friendship I have with Mala, she is a genuine and good person.

Shane O’Neill (b | t) – Influence Somebody. Shane shares how difficult this task was going in and even stumbles on a bit of writers block. Shane doesn’t go out daily to try and find somebody to profoundly impact. He goes about his day in an unassuming way as he just does what he does – shares knowledge just because it needs to be shared. Shane is aware of the impact as people have met him and thanked him personally for his contributions to the community. That’s a win win!

I’ve had some great surprises from people in the community about stuff they’ve seen on here.

It’s nice to know that when you’re shouting into the void, that the void sometimes shouts back.

Each token of appreciation, each expression of gratitude is a replenishment to my morale.

Just a way to say that all efforts put in, while not showing straight away, build up. – Shane O’Neill

I am sure many of us have felt that replenishment to morale from the various tokens of gratitude. The sincere and personal sharing of appreciation carries so much more weight and replenishes the morale so much more completely.

Rob Farley (b | t) – People I Influence. Rob is a humble kind and caring person! He struggled with the topic as he illustrated on twitter. All of that aside, he hit on a note I was hoping somebody would hit on – employees and coworkers. Employees are a reflection of leadership. If the leader is a class act, s/he will impact the employees in a positive manner for the better of the company and the employee.

That aside, Rob is hopeful of being an influence to other people outside of work. Rob is not comfortable with thinking about whether he is influential or not and I get it. I don’t think many of us think regularly about our sphere of influence – until somebody gives us that token of gratitude for some small action we performed.

For a while now, I’ve thought that I would rather employ MVPs than be one myself (although I hope this doesn’t actually cause me to lose my status), and I have often looked for opportunity to get my team in front of people.

As an employer who encourages his staff to be community minded and on the path to being MVPs like Rob is, I think this an outstanding level of influence that is merely demonstrative of the humility that resonates within Rob.

Jason Brimhall (me) – To Influence or be Influenced. I share a couple of experiences in my entry for the month. More important than the stories is the message that as one gives of themselves, there is a gain that will be returned to that person at some point that is likely exponential in nature to the little token of giving that happened in the first place. Give and you will receive! Steve hit on that same note.

And in case you are still curious what this TSQL Tuesday thing is, here are the details…

What is T-SQL Tuesday?

TSQL2sDay150x150T-SQL Tuesday is a monthly blog party hosted by a different blogger each month. This blog party was started by Adam Machanic (blog|twitter). You can take part by posting your own participating post that fits the topic of the month and follows the requirements below. Additionally, if you are interested in hosting a future T-SQL Tuesday, contact Steve Jones via the tsqltuesday website – here.

How to Participate

  • Your post must be published between 00:00 GMT Tuesday, December 11e, 2018, and 00:00 GMT Wednesday December 12e, 2018.
  • Your post must contain the T-SQL Tuesday logo from above and the image should link back to this blog post.
  • Trackbacks should work. But, please do add a link to your post in the comments section below so everyone can see your work.
  • Tweet about your post using the hash tag #TSQL2sDay.

Synonyms in SQL Server – Good and Bad

When SQL Server 2005 was released, a nifty little feature was included called synonyms.  Despite being around since SQL Server 2005, I think this feature is often under-utilized or, more importantly, it is implemented in a very bad way.

Today I want to share a couple of examples.  We will take a look at examples of both good and bad implementations of synonyms.

First, let’s take a look at the syntax for creating a synonym.  Per BOL (and for the fun of it, here is the 2005 link).

So a sample implementation of a Synonym could be something like the following.

Before we delve into that sample synonym, lets look at an example of a really bad implementation.

The BAD

While working with a client, I received a request to look into why a linked server query was failing.  (This isn’t necessarily the bad, just hang in there for a bit.)  The message was something like this:

The OLE DB provider “SQLNCLI10” for linked server “blahblahblah” indicates that either the object has no columns or the current user does not have permissions on that object.

The error message seems at least semi-descriptive and gives a starting point.  In this case, I decided to verify the linked server was created properly, verified that the permissions were done properly and even tested the linked server.  On the source (linked) and destination server (let’s call the Source server ServerA and the Destination server we will call ServerB), I verified that permissions were in place for each database to be touched.  Still no dice!

Well, let’s go take a look and see if that referenced table actually exists.  It did not!  Does it exist as a view?  It did not!  Alas, the table existed as a synonym.  This is where it gets wonky.  In looking at the definition of the synonym, I found that the table defined in the synonym had a linked server table as its source.  To top things off, the linked server was back on the originating server that was coming across the link in the first place.  So yes, that would be ServerB initiated a query against ServerA to pull data back to ServerB.  But the data needed (as defined by the vendor) was available on ServerA – supposedly.  Reality had that data actually sitting on ServerB the whole time.

At any rate, thanks to having a synonym for each and every table sitting on ServerA that referenced a table across a linked server on ServerB, we had mass confusion.  In the end, the query was far simpler to execute by just pulling it from the originating query server (ServerB).

This implementation of a synonym was not the best.  All it did was cause confusion, create documentation inaccuracies and delay the developer from accomplishing her task.  Do you really need 1000s of synonyms in your database?  Do you need all of them going across a linked server?  If you do, did you ever think about the potential for performance issues?  (The vendor in this case used those linked servers and synonyms to perform a data conversion that took 36 hrs each time for a rather small dataset – ouch!!!!).

On the other Hand

Imagine, if you will, two databases sitting on the same box.  One database will be for your user data, and the other for “staging” data for processes such as those related to ETL.  Imagine further that, for some inane reason, the naming standard of your databases must include the suffix denoting the environment of the database.

Now picture an SSIS package that must utilize tables from both databases in data sources somehow.  At least one of the tables has millions of records.  And both of the tables have nearly 100 columns.  Even just pulling in the minimum amount of data using a query from each source can cause memory issues.  Two separate data sources means you will likely have a sort transformation (for each source) as well as a join transformation.

Trying to reduce the amount of data in the larger table source could be done via TSQL.  But to reference a database in one environment versus another environment means a code change with each deployment (due to the TSQL – think three part naming).  So you have been hampered by the environment.  Or have you?

By using a synonym in this situation, the data can be joined in a tsql data source by referencing that synonym.  Let’s look back at the sample synonym posted earlier in this article.

You can see that this synonym follows the same sort of naming standards as was just laid out in the preceding scenario.  If I create a synonym in each environment by the same name, and referencing the appropriate environment named database, I have just opened up a performance tweak for my SSIS datasource.

By implementing this slight tweak, I have been able to gain a 10x performance improvement in package performance.  I am now requiring SSIS to ingest fewer records and thus chew up less memory.  Fewer transformations are required and the package can just fly into the required transformations, rather than tinkering around with the transformations needed to just get the data into a usable state for those transformations.

There are other benefits within SSIS to using synonyms for databases on the same server as well.  Especially when dealing with this kind of naming standard that requires the databases to be named differently in each environment.

Conclusion

How you use a synonym can be a huge asset or it can be a significant dampener to performance.  There are benefits and uses for these nifty little things.  Check them out and let us know how you have been able to put synonyms to use to benefit you.

Check out some of these other articles on synonyms here and here.

Maintenance Plan Owner – Back to Basics

We all inherit things from time to time through our profession.  Sometimes we inherit some good things, sometimes we inherit some things that are not so good.  Other times we inherit some things that are just plan annoying.  Yet other times, we inherit things that may be annoying and we probably just haven’t discovered them yet.

Dizzying, I know.

Inheritance

Have you ever taken over a server that had several maintenance plans on it?  Have you ever really checked who the owner of those plans is?  Or, maybe you had a failing job relating to one of these maintenance plans and you changed the job owner, but did you really fix the root cause?  That could be one of those things that you inherited that could be annoying but you just don’t know it yet.

Step by Step

No this is not New Kids on the Block (I think I just threw up in my mouth thinking that).

Let’s create a generic maintenance plan and see what happens.

The first thing we do is navigate to Maintenance Plans under the Management menu in Management Studio.

 

Right Click the Maintenance Plan folder and select New Maintenance Plan… from the context menu.  This will prompt us with the following dialog box.

In this box, we can type a name for this Maintenance Plan that is to be created.  I chose MaintPlanOwner, since that is the topic of this article.

After clicking ok on this dialog box, you will be presented with a blank canvas with which to design your maintenance plan.  I have chose a simple task for the purposes of this article.

I will create a subplan named Statistics and add the Update Statistics task to the canvas.

You can see this illustrated to the left.  I chose to update the statistics on all databases and left all other options as the default option – for simplicity of this article.

At this point, the only thing left to do is to save this Maintenance Plan.  Once the plan is saved, then we can move on to the next step – some fun with TSQL.

 

 

Fun with TSQL

This is the stage of the article where we get to play with TSQL and investigate at a high level the Maintenance Plan we just created.

Within the msdb database, we have some system tables that store information about SSIS packages, DTS packages, and Maintenance Plans.  We will be investigating from a SQL 2008 and SQL 2005 standpoint (it changed in 2005 and then again in 2008).

In SQL 2005, we can query the sysdtspackages90 and sysdtspackagefolders90 to gain insight into who owns these Maintenance Plans.  In SQL 2008 and up, we can query sysssispackages and sysssispackagefolders to gain the same insight.  These system tables are within the msdb database.

In SQL Server, we can use the following to find that I am now the owner of that maintenance plan we just created.

Notice that in this query, I delve out to the sys.server_principals catalog view.  I did this to retrieve the name of the owner of the package that was found in the sysdtspackages90 and sysssispackages tables respective to version of SQL Server. I also am running a dynamic SQL query to support both views dependent on version of SQL Server.  I figured this might be a tad more helpful than the previous version here. This query would yield the following result set for that new “Maintenance Plan” that was just created.

Caveat

Let’s assume that this package is scheduled via a SQL Agent job on a production server.  I then get moved to a different department and no longer have permissions on this particular production server.  The job will start failing due to the principal not having access.  One fix would be to change the owner of the job.

That will work.  However, there is a problem with that fix.  As soon as somebody opens and saves the Maintenance Plan, the owner of the job will revert back to the owner of the Maintenance Plan.  When that happens, then the job will fail again.

A permanent fix is needed.  The permanent fix is to change the owner of the Maintenance Plan.  The following will change the owner to ‘sa’ for both SQL 2005 and SQL 2008 (and up).

SQL 2005

SQL 2008

Now if you run the code used earlier to investigate, you will find that the owner has indeed changed.  The results of that query should be similar to the following.

There you have it.  No more hair tugging over something as benign as the owner of a Maintenance Plan.  This is one of those things that should be looked at as soon as you inherit a new server.

The Wrap

In this article I took a rather long route to a simple fix. It’s easy to try each of the steps I showed in this article thinking it will help. It isn’t illogical to try some of those steps. They just don’t work unfortunately. In the end, getting to know the settings in the database and what the errors are really trying to get at is most helpful. Sometimes, it just takes a few more steps to get to the real meaning of the error.

This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.

To Influence or Be Influenced?

Comments: No Comments
Published on: December 11, 2018

How have you impacted somebody in the community?

Well, have I come up with a doozy of a topic for this month for the TSQLTuesday party. Its not easy to step out of ones own comfort zone. Its even more difficult to fathom the thought and action of performing a self review. That is in essence the task at hand – self review or have somebody else review your personal contributions to the community.

In the invite, I mentioned how many of us have been able to come up with lists of people that have impacted us personally. Using that previous exercise would be a pretty good resource for some to maybe find out more how it was they influenced another person in the community.

To be candid, the invite is not just for those who have influenced others but also for those who have been influenced. If somebody had an impact on your career or your personal identity, take a minute to chat with them and let them know!

Surprises

How great is it to provide a service to somebody and then to receive a legitimate and meaningful thank you? The SQL community gives and gives and gives and all too often there is no personal note of gratitude for the help given. Every once in a blue moon, that thank you comes and a really cool calming feeling may just overwhelm you. That is when you know you have done good and have meant something to somebody in their life or career.

I am not talking about the flippant thumbs ups, or likes as you may see in social media. Granted, seeing those likes and retweets is probably nice to see on occasion. It just doesn’t bring a real valid connection to the people you are trying to help.

These kind of personal connections are the types of things that build friendships, networks, and long lasting relationships. Someday, this relationship may turn into a client or co-worker or even future mentor (we all can learn something new from other people).

I have had this kinds of conversations more than a few times with the most recent just occurring today from a client. The latest opportunity came as a candid conversation after taking a week long vacation. The client asked me over for a quick chat and reminded me of how thankful they are for the effort and long hours I have given them. The stability they now see with the environment has brought peace of mind and comfort knowing the database will be up when they need them. It has been a culture shift in how databases are viewed there and it has been a good and meaningful shift. This organization is now setup to bring on more talent to a database team.

Just as meaningful is the opportunity I had to chat with another MVP. This person pulled me aside at a very popular conference. I must admit this one blew me away because I had no idea. This person proceeded to tell me how thankful he was that I was a good example as a friend and mentor. He was also appreciative of the time I would take to answer his technical or non-technical questions. Unbeknownst to me, this was happening while he was hitting a rough patch (his words).

This second experience was extremely humbling to me. I share this because I was unaware of the profound impact I was having on this person who later became an MVP. I didn’t do anything super special. I just did what I felt was right. The experience reminded me of why I do the things I do in the community. While extremely gratifying to give and help, it is also very humbling to be able to give, help and be reminded of how a little effort can have a profound effect.

For me, this reminder came when I was personally getting a little down. I needed the reminder to keep it humble and continue to give. I enjoy helping in various ways in the SQL community and I was getting a little sidetracked from doing those things I enjoy. So by these people pulling me aside and telling me about the influence I had on them, they unwittingly also had an impact on me. As I have said multiple times, when you give, you also get something in return. That is the nature of service.

 

 

 

Circling Back

TSQL2sDay150x150Just because you give to the community doesn’t mean you can’t also receive. It also doesn’t mean that it has to be done without ever knowing the true impact of your giving. You simply cannot improve yourself if you have no clue how well you are doing.

Getting feedback from others is that little check that is needed to help progress and move in the right direction. When allowing time and opportunity for feedback from others, you are doing yourself a service as well as them. As I noted, these people that gave me this feedback not only told me about how much I helped them, but they also impacted me at the right moment to help me remember the reasons behind why I like to help. Give a little and you will also get a little!

The Mystery of Missing Backup Files

Comments: 1 Comment
Published on: December 10, 2018

Yoink – Backups Gone

Not only is this mystery of missing backup files worth discussing now, but has been a mystery worth talking about previously (albeit in slightly different form). One such exploration into this mystery can be read here. And as a requirement for exploring any mystery some tools are necessary to be able to explore the clues that might be revealed. For this mystery, we will be utilizing this script along with our acutely developed sense of perception, ability to fact-find and our ability to troubleshoot.

Without further ado and all of our tools in hand, let’s begin!

Peter Deebweehay and Tommy Tsisamin work at Global Exchange Wholesale Retailers (any similarity to real entities is wholly unintentional and completely fictitious). Both Peter and Tommy are diligent hard working individuals. Peter happens to be a DBA and is stubborn about ensuring databases are backed up regularly. He has carefully planned out the disaster recovery scenarios and has nit-pickingly designed the backup strategy to ensure business continuity.

Tommy on the other hand is just as head-strong about performing his daily duties as the systems administrator. Tommy is responsible for the virtual machines, network infrastructure, operating systems, domain, mail services and desktop machines (OS to hardware). Tommy has also carefully planned out all of the disaster recovery needs of the business for all of the servers and specific workstations of individuals that might be pertinent to the continuity of the business.

Tommy and Peter get along together glowingly. The two work together any time there is a new SQL Server to be stood up and have worked together previously many times to ensure good performance of the SQL Servers on both physical and virtual machines.

Lately users have been complaining about a performance slowdown. The slowdown passes quickly but it keeps coming back. Peter starts to look into the reports and is slowly developing a pattern. He notices that the slowdown occurs every hour. Like a good DBA, Peter decides to implement an Extended Event session to capture the queries and server activity that occurs for a period of 3 hours to hopefully capture data that could indicate what the offender is.

After the three hour window has passed, Peter pulls out the captured data and starts looking it over. He notices that every hour there is a backup that occurs against this database. Peter knows that shouldn’t be happening because it is out of cycle with what he had implemented. To be absolutely certain, Peter decides to check the SQL Agent jobs and the Windows task scheduler. Nothing indicating a backup in either location. Next Peter pulls out his trusty backup history script to confirm the data he pulled from the XE session data. When he starts looking at the data, his jaw drops as he sees something that looks like the following.

Missing backup files

Looking at these results, Peter starts looking in the Backup share he had created for these backups and can’t find anything representing those backups with the GUIDs in the BackupPath result. Then he ponders for a minute. Peter knows he has seen something like this before and then recalls where he saw it. Those Full backups with the GUID as the path are coming from outside of SQL Server.

Peter is curious if Tommy could possibly help with the new-found mystery on this server. When Peter presents the information to Tommy, Peter is met with another jaw-dropping moment. Tommy, with a wry smile and pride in himself, explains to Peter exactly what those GUIDs happen to be (not knowing the connection). Tommy explains that he has set the virtual machine to perform an incremental backup every hour. This incremental backup is represented to SQL Server as a Full database backup and produces a backup that equates to .005MB. With this data, Peter is curious to know if Tommy is quiescing the database(s) prior to doing this incremental backup. As it turns out, Tommy was not. Peter was mildly frustrated but he was able to work out a better solution with Tommy. Without quiescing the database, these full backups become questionable as to the usability to actually recover this server (an incremental OS backup is not the same as a Database Differential backup).

As far as the database is concerned, these GUID backups are much the same as a missing backup. Any backup that is taken that a DBA cannot explain or find would also be a missing backup.

What does this mean for you?

In the case of Peter and Tommy, this meant that there was a bit of a mis-communication and a bit of a performance hit for these off cycle backups. Had Peter implemented differential backups, the recovery plan he had implemented would be for naught because each of those full backups caused by the incremental backup for the VM would break his differential chain. Also, if Peter had attempted to recover the database to a different server, he might have discovered this sooner and before the users had reported the performance issues that led him to the discovery. Not only is a backup plan essential, but monitoring of the logs and backups as well as routine practice restores is necessary.

After reading this article, enjoy more articles similar in nature such as this series on Extended Events or this series called “Back to Basics

«page 1 of 122

Calendar
December 2018
M T W T F S S
« Nov    
 12
3456789
10111213141516
17181920212223
24252627282930
31  

Welcome , today is Tuesday, December 18, 2018