Category Archives: SQLServerPedia Syndication

The Microsoft SQL Server MVP

Leave a reply

What is an MVP?

For myself growing up in the realm of sports through high school and college an MVP is a most valuable player. In general an MVP is recognized in his area or field, an honor bestowed on him or her that distinguishes them as being recognized by their peers.

What is a SQL MVP

This carries over for me from my statement above on what an MVP is. I have friends that are SQL MVP’s and some friends that aren’t. Microsoft’s SQL MVP program recognizes individuals who make exceptional contributions to technical communities, sharing their passion, knowledge, etc.

Am I a SQL MVP?

No, I am not currently a SQL MVP and this is where my thought and blog really comes to life and the purpose for the post. As I stated before I have several friends who are SQL MVP’s and a lot who aren’t. One who is not approached me the other day via phone and I could tell something was bothering them. After some inquiring I discovered that the person was clearly upset that they did not have an MVP title next to their name so much so that they disclosed they were going to stop writing, being involved in the SQL Community etc.

The Outlook

I have mad respect for all of the current SQL MVP’s that are available to the community and the efforts that they put forth day in and day out; they are examples to me of what hard work and diligence can achieve in the profession and I hope one day I can become one; but I also want to share a different point of view to other fellow SQL Server Professionals. The SQL Community is just that a community of individual professionals that provide a knowledge base like no other. I implore the individuals who like my friend, basically was going to throw the towel in to keep working hard.

I once was told by my coach “Attitude – what you or I feel or think about something or somebody”. What’s your attitude today? Are you making a difference? Are you helping your co-workers? Are you continually learning to make yourself better? Do you want to me a game changer?

Somewhere somebody will always be practicing, learning, fine-tuning their skills – what will you be doing? Let’s get in the game, stay in the game, and while we are at it we might as well have some fun with it. All the other stuff will fall into place in due time, give 110% every time out.

 

 

 

Few Of My Favorite Articles From Others

Leave a reply

I enjoy getting viewpoints by others in the community, the other day I sat down and started to ponder on some of the articles I’ve read that have either helped me along my way and career or have sparked me to think about how I would apply what I read to an issue I was having.

Below are 5 articles written by others in the community that I’ve read over time that have helped me; they are in no particular order and I’ll do my best to give a brief synopsis of what each represent are:

Chris Shaw – Utility Databases –  this topic is geared toward having a utility database at your disposal as a DBA. The link is for Chris’ demo; I first heard this topic at PASS 2011, made so much sense to me to have a database at your disposal to keep as a repository to report off of and trend. This idea sparked other ideas which has lead to a full blown monitoring solution for me.

Adam Machanic – sp_WhoIsActive – I am going to take the blurb directly from Adam’s site. If you don’t have this tool I recommend getting it. “Who is Active? is a comprehensive server activity stored procedure based on the SQL Server 2005 and 2008 dynamic management views (DMVs). Think of it as sp_who2 on a hefty dose of anabolic steroids.~ Adam Machanic”. I highly recommend checking this one out

Glenn Berry – Diagnostic Query – The link will take you to different SQL versions of his diagnostic queries. These have helped me tremendously throughout my time as a DBA.

Brent Ozar – sp_Blitz – I enjoy reading from Brent’s outfit. If you have never been to his blog you should check it out. Jeremiah Peschka, Kendra Little, and Jes Borland have some great stuff they blog about. When I switched jobs I used Brent’s “free” utility to help with me learning what the servers were and what I was dealing with. For me this was a life saver. I give Brent and his team credit on this one.

John Sansom – How To Become A DBA – in this awesome article I liked John’s “How to become an outstanding DBA section”

I have many more articles that I will share periodically but these are just some to start with that I think from my standpoint have helped me. Hope you can get as much out of them as I have……….I’m tempted to just go ahead and start doing something like this monthly or weekly haven’t decided yet.

Have a good one!

T-SQL Tuesday #35 – Soylent Green

1 Reply

This month’s host for T-SQL Tuesday is Nick Haslam (b | t) and the question he poses is based on a movie called Soylent Green. I have not seen the movie; however his question  he asks is what the most horrifying thing you’ve seen in SQL Server.

WHAT THE MOST HORRIFYING THING YOU’VE SEEN IN SQL SERVER

Thinking back through the past I’ve had some serious, funny, and down right what the type of things happen. I’m sure all of us have.

One that sticks out to me is early on in my career I was fortunate enough to work for a company (mid-size) earning millions of dollars in revenue. Back then I had my own homegrown check list to go through, so after getting acclimated to everything I start going down my checks.

I come to my backup check……I start searching……looking………looking……..still looking. Come to find a previous employee had removed all the back up jobs; which now leads me into the next question, “Were there checks daily for certain things oh I don’t know verification of backups were occurring?”  Unfortunately, the person who was handed over the DBA daily tasks was not the person who should have been in the position of such importance.

I will not say how long the company went without any backups but needless to say that along with several other issues were resolved!

For information on hosting or getting involved with T-SQL Tuesday you can contact Adam Machanic.

PASS Summit 2012 – Closing In

2 Replies

 

Last year, 2011, I had the opportunity to attend this conference. I never had been to Seattle and my peers who have attended before me said that I would be amazed at what I would be getting myself into.

I’m not sure how to explain it other than by telling you my experience about it.

NETWORKING

Upon arriving to the conference I was amazed to see that I was there with roughly 3 to 5 thousand of my fellow community brothers and sisters. I am not by any means an introvert; but I can see how people would be awestruck when walking into the convention center.

I quickly found that networking, sharing issues, sharing solutions, and gaining knowledge was at the forefront. Each day PASS puts on a daily luncheon, now I tell you one thing…..food…..SQL…..community…can’t really top that.

Last year I had the opportunity to meet some of the most respected SQL MVP’s in the industry such as Chris Shaw, Jason Strate, Brad Mcgehee, Grant Fritchey, and Steve Jones.

FIRST TIME ATTENDING

First and foremost let me congratulate you on attending. Secondly, don’t be alarmed or get to overwhelmed. PASS does a great job of orientation and getting attendees acclimated to the week ahead.

For an insight into the workshops and orientation you can go directly to the PASS website  for reference.

GET IN THE GAME

This will be the most beneficial conference SQL related you could attend. If you are looking for something that could change your career or outlook I would seriously consider attending; where else will you get

REGISTRATION

Registration is simple; for exact information you can visit the PASS site directly here

Controlled DR Testing vs. Unplanned DR Testing

Leave a reply

Recently I did a post on Disaster Recovery, well I just went through a simulated test which I believe is good. It provided a plethora of information on gaps that we need to shore up on.

One of the things that came to my mind though during this simulated test is most companies that I’ve worked with in the past have an annual Disaster Recovery. Then my mind wondered some more and thought about not only the frequency but what about a total unplanned Disaster Recovery test.

I’m very curious to what others think about this topic and what they are doing. Things that I would like to see are:

  1. Frequency – how often do you DR test
  2. Is your DR environment the same as your prod environment in terms of hardware, sizing, etc. or do you just have enough to get by
  3. Planned or Unplanned – do you have a totally planned simulation or do you have a select view individuals know and then trigger a DR on a certain date
  4. What is your favorite method for bringing data over to DR site
  5. Do you have checklists in place

These are just a few of the topics that ran through my head over the weekend. If you get time drop me a line and let me know your thoughts, and please feel free to expound on any of the topic related to DR.

PASS 2012 Elections Ballot

Leave a reply

Top of the morning to everyone. It’s that time again, today marks the kick off for electing three new seats to the PASS Board of Directors. If you are part of the PASS SQL community I implore you to take a moment, read up on the candidates and select the ones you think will best fit the positions.

This years candidates is yet again another stellar pool. They are:

  • James Rowland-Jones
  • Sri Sridharan
  • Allen Kinsel
  • Wendy Pastrick
  • Kendal Van Dyke

You can view the candidate pages that PASS has put together here

Voting will run through October 12th

I personally would like to wish all of the candidates luck as all five of them would be a great fit.

Get out and vote!

What Is A Disaster?

Leave a reply

One of my ex-colleagues recently approached me and asked me what really is considered a disaster recovery? There have been and will continue to be many discussions on this topic so much so that I’m sure I won’t encapsulate all that needs to be discussed, but I will express some of my views.

I believe every business needs to  have a strong disaster recovery plan in place. I have found in my own experiences that building a formidable disaster recovery plan can prove difficult as it is something most business leaders do not want to think about, but is something everyone needs to plan for. To me if I had to define out what a disaster recovery is I would state it this way. The ability to continue work after any number of catastrophic events – this could entail hacking, virus’ and up through natural disasters such as fires, floods, or tornadoes. I’ve heard people say that having a disaster recovery plan in place takes little time and effort – I tend to disagree. I believe it needs to be thought out and practiced on a routine basis.

Each business is different, but I do believe that disaster recovery must take into consideration a few things:

  1. How is the business run
  2. What are all the elements that are required to keep the business going.

Having a generic disaster recovery plan in place, while better than nothing at all I assume, is not the way I would approach it. I really do believe it should be tailored to what the business needs are. Just copying a template of someone else’s recovery plan or design can provide perhaps some great ideas but could and probably will leave out some critical aspects of what your business needs are.

I am a firm believer in planning up front; crafting an individual disaster recovery plan; mapping out the most critical aspects of the business; and then implementing on how and what the processes are to ensure process flow is maintained.

A friend of mine experienced a disaster not long ago, the company did not have a steady disaster recovery plan in place and the one they did have was years old. Needless to say they had to learn a lesson the hard way. I ask this question with the utmost respect and urgency as a DBA, in the event of a disaster, will your business have the ability to pick up the pieces, get back to work, or will things grind to a dead halt?

Do I think it is possible to plan for every event, no I do not; however I do believe wholeheartedly that it is possible to have a solid, firm disaster recovery plan in place that can make all the difference when a disaster does hit. Disaster recovery is to me in  my experiences a difficult but necessary topic of business. I hope that you never have to rely on a disaster recovery plan, but if you do at some point you will definitely be glad that you planned ahead for it.

Filtered Indexes 2012 Style

Leave a reply

I recently was approached by some fellow DBA friends of mine inquiring about filtered indexes in SQL 2012 (never really got into them too much in 2008; just on a minimal basis). In researching I found that they could be very useful for what I encounter on a daily basis.

What exactly is a filtered index? The best way that I can describe it is an optimized non clustered index that is used to cover queries pulled from a very unique and defined subset of data. In researching, I have found that it uses a filter predicate that indexes a portion of rows in a table; not only can it improve query performance, which we all want, but is also can reduce the index maintenance and storage cost.

Filtered vs. Full-Table Indexes

Accuracy is key here. Because the filtered index only covers the rows in the filtered index the statistics will be more accurate; it also is smaller than a full table non clustered index

Reducing Index Storage Costs

Everyone always talks about disk storage and how important it is. A filtered index can greatly reduce the storage, especially when a full table index is not necessary.

Reduction in Maintenance Costs

A filtered index is only changed when the data within that filter changes; and if you think about because of the smaller size of the index it reduces the cost of updating the statistics.

What kind of Permissions are needed

Filtered indexes require Alter permissions on the table or view. The user must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.

Limitations / Restrictions

With everything else filtered indexes do have their limitations, restrictions, and yes even considerations in how you design them.

Some of the limitations are noted below:

  1. A filtered index cannot be created on a view. However there is a gotcha to this which when research is done will show you how the query optimizer can benefit from a filtered index on a table that utilizes a view
  2. Filtered indexes are defined on one table

Other Notes

With any new trick so to speak you always need to try it out in a sandbox or a local environment. I behoove you to conduct your own research prior to implementing. This post just starts to scratch the surface with filtered indexes. I’ve enjoyed the research and testing I’ve done thus far with them on 2012; I think it is definitely worth checking out if you are looking for some new ways to incorporate indexing

 

Is My Table Part of Replication?

2 Replies

Recently, I had someone approach me asking if a table was being utilized for replication. The person didn’t posses the knowledge of the different methodologies to track this information down.

While there are several ways to accomplish this goal one of the quickest is to run a query. The below query will provide you the publication to which your table is found in. This is extremely helpful when you have multiple publications for a database and one is unsure which one it is located in.

USE [DBNAME]
GO

SELECT sps.name
FROM syspublications sps WITH ( NOLOCK )
JOIN sysarticles sas WITH ( NOLOCK ) ON sps.pubid = sas.pubid
WHERE sas.name = ‘TableName’

Replace the [DBNAME] with the database that your table is found in, and replace the ‘TableName’ in the where clause with your table.

How Much Longer?

Leave a reply

There have been instances in my career where I’ve had to perform backups off a routine schedule; I mean it’s inevitable, right? It’s nice to look at job history to see about how long the backup job normally takes; can give an estimate or an ideal of what to expect.

However, if you are like me I like to monitor and keep an eye on how much longer my backup will take. Below is a T-SQL script that I’ve picked up and tweaked over the years to help determine an estimated time on how much longer my backup will take.

SELECT r.session_id ,
r.command ,
CONVERT(NUMERIC(6, 2), r.percent_complete) AS [Percent Complete] ,
CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time,
GETDATE()), 20) AS [ETA Completion Time] ,
CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min] ,
CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min] ,
CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0
/ 60.0) AS [ETA Hours] ,
CONVERT(VARCHAR(1000), ( SELECT SUBSTRING(text,
r.statement_start_offset / 2,
CASE WHEN r.statement_end_offset = -1
THEN 1000
ELSE ( r.statement_end_offset
– r.statement_start_offset )
/ 2
END)
FROM sys.dm_exec_sql_text(sql_handle)
))
FROM sys.dm_exec_requests r
WHERE command IN ( ‘RESTORE DATABASE’, ‘BACKUP DATABASE’ )

I apologize beforehand of the code being so choppy; my plugin editor is not working properly at the moment. The result set of this code set will provide you the following columns:

  • Session_id
  • Command
  • Percent Complete
  • ETA Completion Time
  • Elapsed Min
  • ETA Min
  • ETA Hours

There you have it; pretty straight forward and when a backup is kicked off you can execute this query to determine how much longer the backup will take. If my memory serves me correct I’ve ran this on SQL 2005 or greater.