Category Archives: Uncategorized

Entering the SQL Matrix

1 Reply

MatrixI know why you are here and I know what you’ve been doing. You hardly sleep and why night after night you sit by the computer; you are looking for what they call ~ The SQL Knowledge. I know because I too have looked for the knowledge, and when that knowledge found me it told me I was not really looking for it; I was simply looking for an answer. It’s the question that drives SQL Data Professionals and is the question that brought you here……

The SQL Matrix is comprised of many components and with that comes SQL Data Professionals from all walks of life from all over the world. It has been amazing to see components of peoples skill set come together to provide different view points on various topics.

Well, I’m about to welcome everyone to the Desert of the Real where real SQL Data Professionals collaborate with one another on various topics related to SQL. What you will come to know as MCJJ (Mickey Stuewe, Chris Yates, Jeffrey Verheul, and Julie Koesmarno); you will find four distinct Data Professionals who seek to gain more knowledge inside the SQL Matrix and provide thoughts and real life scenarios into series of blog posts.

With that said I now must tell you; you have two choices:

  1. You can take the blue pill and go back to sitting in front of your computer all day long stuck in the mundane rut that happens to all of us
  2. You can take the red pill and join MCJJ on this collaboration effort and perhaps hopefully pick up some new and interesting SQL Tips along the way.

The option is yours what will you decide.

Tomorrow will mark our first collaboration; I’m happy to announce that it will be done on Red Gate’s Utility – SQL Search.

Check out my peers reviews tomorrow and also check out the others collaboration release posts.

Stay Tuned

Please visit the following links to see the unique views of my collaborators.

To follow our quest for SQL knowledge through this collaborative project, follow the #SQLCoOp tag on Twitter.

Renovations On The Way

Leave a reply

ProfessorWhat Is The SQL Corner

I attended my first PASS Summit back in 2011 in Seattle and quickly realized that I could be doing more for the community that what I was currently doing. When I came back I thought I could tackle the SQL world, leap tall buildings in a single bound, round up all the unnecessary bad practices and so on. The SQL Corner came about as a meeting place where people could come and gather to find solutions, present new techniques, and share some knowledge.

Growth

It has been an enjoyable three years in continuing to write for this blog and other avenues. I have realized that over time I enjoy being able to discuss what I may be going through and solutions found to issues that occur in an DBA’s life.

As time has progressed the community has seemed to welcome what The SQL Corner represented “Leadership Through Service”.

Time For A Change

With time, things change. In the latter part of last year I’ve developed a passion for writing more, sharing more in the community, and speaking to various groups. I started to see morphing The SQL Corner into something more; transitioning it over to what will become the SQL Professor.

Why The Change

The name change represents where I am at and what I’d like for the SQL Corner to become; a place for “continued learning”. In my 12+ SQL career one thing has always remained the same – there is always something to learn and always a skill set to work on. For me, I believe this is why I enjoy working with SQL so much. I enjoy coming into work everyday and the job of being a DBA. With being a DBA comes great responsibility and striving to continual learn new things is a passion that I hope will never burn out.

Upcoming

With all that said you will start to see some renovations being introduced over the next few months both from this blog standpoint, social media, etc. I’m thankful for the ride I’ve been on over the last few years and looking forward to what the new journey will hold.

Feedback is welcome

End of 2013

Leave a reply

As the year winds down I, like many others in the SQL Community, start to take inventory. The year has been an explosive year; not only for The SQL Corner but in many aspects career wise. I think one of the major highlights was being allowed to be apart of John Sansom’s project – DBA Jumpstart

Looking back at the year I can honestly say that being part of and in some small way of helping the community has meant the most. It’s easy to get caught up in the game of numbers; believe me I do it all the time. At the end of the day though if we can help one person then it has been worth it.

So here is to 2013, it was a great one, and I look forward to what 2014 holds within our SQL Community. For the new speakers and bloggers like myself lets keep blazing trails and working hard, and for the seasoned SQL veterans who I continue to learn from every day I tip my hat to you.

Looking forward to making 2014 a great year! Be a GameChanger

Adversity – How Do You Handle It?

Leave a reply

Adversity Adversity by definition is a difficult situation or condition misfortune or tragedy. When that time comes; how do you handle it?

If replication breaks causing the business to not get the data they need in a timely fashion and you are the lone conductor driving the train down the tracks and everyone is standing at your desk, how do you handle it? I

f your backups failed from the previous night and for some reason your notification of failure didn’t reach you and you had no knowledge of the situation until days later, how do you handle it?

Someone calls and said they can’t access their systems and you find out something is causing tempdb to fill up, how do you handle it?

Better yet, you find out that a certification you were working toward had been retired (MCM), how do you handle it?

Response

I can only speak for myself and no one else, I fell into the same category a lot of people across the nation did of working toward the MCM certification goal. I mean let’s face it, last I checked a very small percentage obtained this goal and being the competitive person I am I wanted to reach that goal. Not for anyone else but for myself and the standards I set for my own self.

At first I was upset and like so many others not so much toward the retiring of the program but how it was delivered by Microsoft. I purposefully waited to construct a blog post as I didn’t want to let emotions get in the way of potential views toward the topic.

Adversity has now hit for a lot of people, for me in particular I choose not to dwell on it but wait to see what will be offered next by Microsoft, restructure my goals, and move forward. I am a bit biased but I get to work with some of the best technology day in and day out and am in constantly learning mode. Some of the things that we can control are the way we handle adversity when it rears its ugly head, the attitude to how we conduct ourselves through the adversity, and the character building we can learn from it.

Am I happy about the retirement, no I wasn’t. At the end of the day though I do have a lot to be thankful for and looking back on my studying, lab testing, hours of working toward the certification it allowed me to push myself to learn a lot more than what I did the previous day and the day before that. I’m eager to see where the next chapter leads in the stepping stone of my learning.

Adversity – how will you handle it? Each of us are different; doesn’t make one person better than the next by how we handle it, but I do encourage others to push through adversity when it comes. You’ll be a stronger person in doing so.

So You Want To Go To Pass Summit?!?!

Leave a reply

http://www.sqlpass.org/summit/2013/ Back in 2011 I was afforded the opportunity to attend PASS in Seattle, Washington. It was the first time I had made such a journey to this conference; I really didn’t know what to expect. I’ve heard the stories, the     many positive things regarding the sessions, learning, and networking – but to be quite honest the stories do not give PASS justice until you experience it first hand. I get asked this question all the time by various  people throughout the country – Should I attend? Because of the interest I want to take a moment and lay out how it has benefited me in the span of 2 years.

The Beginning

When asked to go to PASS I figured it would be like any other business conference I had been to (boy was I wrong). Leading up to my departure I had a lot of work to get done and wasn’t really in a place I felt like I should go. I started to review the sessions online and I quickly saw how vast the information really is. Looking at the sessions I began to look at the speakers, some I knew and some I did not but what was obvious to me is that a lot of the leading leaders in my industry (DBA) were going to be there; hearing them speak on topics of interest to me I thought to be an extreme plus…..moving on

The Expectations

My expectations were that of learning some new techniques from the Jason Strate’s (B|T), Brent Ozar’s (B|T), Chris Shaw’s (B|T), etc. but what I walked away with were new connections with a SQL family that was over 2k strong and memories that I will take with me for the rest of my life. PASS has so much to offer to each individual, from the sessions, to the vendors interacting, the kick-off speakers, and much more.

Perspective

From my own personal experience attending is something that kick started my career into overdrive. From it I became more active in the community, started to speak locally, created the SQL Corner, and made many new friends along the journey. No, PASS is just not another business conference where you go and sit in on meetings; of course you do that but it is much more. Being part of the SQL community means you are part of a family and all the family comes together to gain more knowledge within the perspective fields.

Summary

Why should you go; glad you asked. I think PASS sums it up better than I ever could here. This year in 2013 PASS is going to be in Charlotte, N.C.; if you haven’t reserved your spot yet I hope you do so. It is one of the best conferences I’ve ever attended from both a business and personal standpoint. Do you want to kick start your career? Are you eager to learn more and connect with other people that are trying to do the same? If you are then PASS Summit is for you. If you have questions you can contact them here or drop me a line and I will do my best to get you an answer.

How’s Your Database Mail?

4 Replies

Database MailFrom time to time I field questions regarding Database Mail usage within SQL. Questions come from all over discussing how to identify what the job is doing or what the job has done. Sure, you can send some test mails through the nice GUI part, but that is not what this post is about. I enjoy T-SQL and looking inside SQL the old fashion way so to speak so I utilize some simple queries that a colleague of mine recommended for me.

Database Mail in and of itself is a useful tool; it allows for notifications of failed SQL jobs for instance. The messages in and of itself can contain a plethora of information that can assist one in troubleshooting a variety of issues. According to Microsoft they state Database Mail in this manner – “Database Mail is designed for reliability, scalability, security, and supportability.”

**NOTE** Database Mail is not active by default; it has to be configured and turned on. The below information assumes that Database Mail is already set up. For information on how to set up Database Mail you can go here

To give a brief overview the below script is broken out into 7 mini scripts; these scripts consist of checks against Database Mail along with the process of stopping and restarting database mail. Please note the disclaimer and hope this helps with some of the questions that I’ve received thus far regarding Database Mail.

/**************************************************************************************************************

Disclaimer: Do not execute code found on the internet without testing on your local or testing environment. Running any code in a production environment that you find on the internet is not an acceptable practice and this site is not responsible for any repercussions that may follow if you choose to do so.

Scripts below are numbered; the corresponding numbers will give you a description of what they are utilized for.

1. The status of the Database Mail. Possible values are Started and Stopped (msdn article on sysmail_help_status_sp)

2. Stops the database mail queue that holds outgoing message requests (msdn article on sysmail_stop_sp)

3. Starts the database mail queue that holds outgoing message requests (msdn article on sysmail_start_sp)

4. Shows all the mail items

5. Shows all the unsent mail items

6. Shows all the sent mail items

7. Shows all the failed mail items

**************************************************************************************************************/

USE msdb

GO

/*1.*/ EXECUTE sysmail_help_status_sp

/*2.*/ EXECUTE sysmail_stop_sp

/*3.*/ EXECUTE sysmail_start_sp

/*4.*/ SELECT * FROM dbo.sysmail_mailitems (NOLOCK)

/*5.*/ SELECT * FROM dbo.sysmail_unsentitems (NOLOCK)

/*6.*/ SELECT * FROM dbo.sysmail_sentitems (NOLOCK)

/*7.*/ SELECT * FROM dbo.sysmail_faileditems (NOLOCK)

Why is Count(*) Taking So Long

4 Replies

Phone rings……..I answer…….a DBA from a third party vendor has supplied someone with two scripts. The first script is a simple insert with a where clause:

insert into [databasename].[dbo].[tablename]
( column1,column2, column3, column4 )

select column1, column2, column3, column4
from [databasename].[dbo].[tablename]

where column1<[integeramount]
and column1>[integeramount]

First question I ask; how much data is loading into the table – answer millions of records; the vendor gave us a script to see if the number is increasing. What is the script; do you have it? Sure…..script below:

select COUNT(*) from tablename

I was reminded of something I came across several years ago about this very scenario so figured why not put it to the test. I will try to explain to the best of my ability why the the second query was taking an hour to run.

The first problem I see right off hand is the COUNT(*) statement has to do a table scan; it is a requirement to figure out the calculation to return the result set. You take this statement and run it against several million row tables with a ton of reads on it you have a recipe of being prepared to sit and watch and wait for the result set to return.

How Do I Get Around This?

It’s not that difficult and here is a nice trick to provide a quick solution. I’m a huge fan of DMV’s and it just so happens that you can utilize one to return row counts for all tables in a database or specific tables in the database:

SELECT o.name,
ddps.row_count
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
–AND o.name = [table name] /*UNCOMMENT AND PLUG IN TABLE NAME FOR SPECIFIC TABLE INFO*/
ORDER BY o.NAME

The result will give you the specific table name with row count

image

Don’t be alarmed by using the system object. Unlike others the row count does not depend on any updated statistics so the count is accurate. On this 132 million record table I can get the result set to return immediately.

Next time you get stuck waiting on a COUNT(*) statement to run; think about using a DMV; for a listing check out what Microsoft has listed into categories

It is always nice to have some tricks up your sleeve; especially when dealing with outside vendors.

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…….

Where Did January Go?

Leave a reply

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.

New Year, New Topics, New Site Updates

Leave a reply

I got to admit, 2012 was a good year. I had a blast on writing, technical blogging, speaking, attending seminars, conferences etc. Going into 2013 I will be reviewing and making changes to the blog site, adding additional resources to the resource page (my intent was to beef that up a lot but it never materialized), present a weekly top posts that caught my eye that might be of some benefit for everyone, and to provide some good sound topics for discussion.

I appreciate everyone who participated in the events on this blog last year and who just meandered into some reading. Always glad to help the SQL Community in any way I can; if you have questions feel free to drop me a message and I will respond back just as soon as I can.

Good luck to everyone this upcoming year and check back frequently for updates etc.