Saturday, February 28, 2009

Index Maintenance

I'm prepping a presentation for SSWUG on index maintenance. I have had experience with this in most of my jobs. some, I have even had to create systems to perform the maintenance. One such shop was ready to purchase hardware to upgrade their servers, but I started maintaining indexes periodically at night, the worst performing indexes, and it improved performance enough, that we didnt have to upgrade hardware, like they had previously intended. This little system was improved upon over the years, but would basically perform ShowContigs to get the status of indexes, then maintain them as needed.
In my current job, we have the need for maintenance after some ETL processes on a monthly basis. Tonight is one of those nights. After a couple parts of the process have been run, then we have to maintain the indexes. I've written an automated script that helps me out by dumping the results of the ShowContig to a temp table, performss maintenance on the worst performing indexes, and then runs another ShowContig. These results are gathered, and shared with the team to prove that things are in good working order.
Back to the SSWUG presentation. I have a virtual server that i am creating temp data for, so i can show off the steps of this system. I created a table, and was adding random names to cause an index on the names to get out of whack. Initially I found a sql script that had a ton of names in it, that I had used before to create tons of random names. After all that effort, I realized that adding my families names, in order, would produce an fragmented index.
My name is TJay. Starts with 'T'. My wife is named Christy. 'C' is before 'T'. So, alphabetically, these are not in order. Next is Kylie. Then Alyssa. And finally Treyson.
I created a simple table at work. Inserted 100 records for each name, in that order, and check the table afterwards, and its density, as reported by ShowContig, is not near 100, and indicates its out of whack.
I just thought it was pretty cool that the names of my family was in such an order that it could be easily used for a scenario for testing fragmentation. Obviously, its a better test scenario to add tons of distinct names. I just thought it was cool.

Wednesday, February 18, 2009

Google Analytics

I started blogging back in November of 2008, just prior to the PASS Summit in Seattle. During the summit, I discovered Google Analytics, and applied the code necessary to my blog to allow it to be tracked. Every few days I'll go out and check it out, usually after a blog post. Its interesting to see the progress and see that people are actually hitting my blog. I don't pretend to be very widely read, but I have had a few comments from kind folks that they enjoy my writings. Thats the point of why I blog. To help someone out there with my particular opinion, view, experience. When I am no longer accomplishing this small task, I will cease the activity. Sometimes the person that it truly helps is myself.

So, Google Analytics. I thought it would be interesting to show off a couple images of the dashboard, that were recently snapshotted of my blog.

This first pic is a simple timeline, with the high points being quantity of hits to the blog. Its rather up and down. As expected, most of the down times are on a weekend. However, I've had some weekends with 15-20 hits on them. Odd.
I have had a few peaks, when a particular blog post got a lot of attention. I am grateful for those periods.

The next image shows the timeline, along with some other helpful data, along with a map. This I find fascinating. I've had many hits from Europe, Africa, even 3 from Iran. A lot of hits from USA and Canada, as expected. Its interesting.

In any event, I just wanted to share this interesting information. I do think it would be interesting for others to do the same thing, and report their analytics of their blog. I find it fascinating.

My First Computer

I was tagged again, this time by Mr Denny who wants me to try to remember my first computer.

My brain went back to my first computer as an adult. I recall having to get a loan from mom and dad to pay for it, as it cost me $3800. I recall that each computer after that purchase dropped half in price. We seemed to get a new computer each time we had a kid too. It just worked out that way. For the kid's first birthday, they would be surrounded by computer parts, and photos were taken. They had no idea, and the family needed a computer. So, we've had 3 kids, and had 4 computers as they were raised. Each kid was about 3 years apart from their siblings, so were our computer upgrades. We finally had to start simply buying new computers, and stop having kids for the last few upgrades.

But that wasnt the point of the blog requested. What was my first computer?

In college, i recall my loving parents sending me to BYU to study Computer Science, and shipping me off with a typewriter. It made an excellent foot rest. When my parents asked if I was using it, I would inevitably reply, 'Every day'. This was true. I often put my feet up on it. I do not think I ever used it. I did purchase an 8088 while in school. Monitor was 2 colors. Off=Black and Purple. We used to do all kinds of crazy things with that machine, like goto bbs's. Wow, we were insane with it... I remember upgrading my modem from a 2400 baud, to 9600, then 14.4. Wow, so fast, what would I do with all that speed.

Wait, that wasn't my first computer either.
Back in the day, I remember my mom, who was a budding romance novel writer, coming home with a computer. An Apple IIe. It lived on our dining room table for the longest time, as an office space did not exist in our home. She had set it up, and was trying to get it useful to writing her books. Later she moved to a Kaypro, as it was easier.

So, I wonder into the dining room one day, see the computer and manuals. I pick up the manual, and start reading. I come across a command 'init home' and attempt it. I figured that this would get the computer thing going and doing stuff. I entered the command, and nothing seemed to happen. The next thing I recall was that feeling i have grown to not be able to live without. That feeling of utter despair that I screwed something up. Nothing was happening on the machine. It just whirred and whirred. I hollered out to my Mom. What did I do? All I did was this command, 'Init Home'. What did that do? Well, it erased the disk that was in the drive. Making it new and shiny. So, my first action on my first computer was to wipe away all its storage.

From there, its been a downhill battle. I still love working with computers. I love using them to solve problems. I love the creative process that is involved with computing. I love to work with databases.
But, from time to time, I get that feeling in my stomach. I retrace my steps. Sure enough, I did another 'Init Home' command.

I’m going to tag Pat Wright, Tim Ford, Louis Davidson
To ensure that this keeps moving, I will contact the above via twitter, so that they can read my blog, and move it along.

Monday, February 16, 2009


I have received a discount code for the SSWUG Virtual Conference. You can register for the conference here and make sure you use my code 'SPVTBESP09'.

The discount is for $10 off the registration price of $125, and can be used in conjunction with other discounts, such as the Early Bird ($80).

I have not attended this event in the past, but have heard great things about it. I hope you will think of attending, and learning from some of the greats out there in the SQL Community. And stop by my presentations as well. I hope to help someone out there with my experience.

SSWUG Virtual Conference Presentations

For the first time, i will have the honor of presenting a few topics at the SSWUG Virtual Conference on April 22-24th.

It was suggested by my handler (Chris Shaw) that I submit as many topics as possible, to ensure that I get at least 1 accepted. So, I submitted a slew of them, and had 4 regular sessions accepted, and 3 uplink sessions accepted.

Now, I am going thru them, making sure that they are in a good state to present, and practicing. One of the topics I presented at my UG last month. I will present another one in March.

vConference abstracts

Auditing your Users
Periodically, an auditor would stop by my desk and ask me to show them all the users and their permission’s on a particular database. Most of us know how to do this, and it is easily gathered. Typically, the next question is ‘What has changed?’. After a couple iterations of this, you will realize that you need to devise a way to store this information to adequately respond to this question. A snapshot of the user information needs to be gathered from time to time, and stored. In this presentation, we’ll discuss reasons for storing this information, show code for tables, and explain processes that will enable you to quickly and effectively answer the auditors requests in a consistent and timely manner.

Best Practices
Having worked with databases for some time now, I felt it important to get some best practices documented. This presentation will touch on the simple things that we often take for granted while using our favorite database systems. From naming conventions, to cursors, to temporary, derived and variable tables, to the use of dynamic sql. We’ll touch on a great many different ideas and hopefully further cement your own best practices, making you a more exceptional DBA or Database Developer.

Job execution system
In a production environment, as a DBA protecting the environment, you do not want to give out Admin rights to many, if any individuals in the company. Granting the least possible permissions needed is the desired route. This is a protection for the system you have a responsibility to care for. However, people often need to gain access to perform their job functions. When folks need jobs to be executed outside of the normal execution times, how do we solve this dilemma? This presentation will detail a system that solves this problem.

Monitor Index Fragmentation
As our databases are being used, our index fragmentation can tend to increase. Having an automated system that defrags or removes the fragmentation is necessary. Even a rarely used system will peak the fragmentation with their periodic executions. Keeping track of the level of fragmentation prior to and after fragmentation has been removed will help gather metrics on your systems. Automating this process will relieve pressure on your on-call staff, pressure on your database system, and even increase performance. Let’s discuss ways to accomplish this task.

ITUplink sessions

SQL Server Profiler and Tracing
We have all needed to know what is happening in our database systems. We have often been tasked with finding poor performing queries. Maybe we are being hit with Auditing and Compliance needs. All these and many more issues can be resolved with SQL Server Profiler. Maybe with SQL Server Trace. But which one, and when?
Auditing databases can be tiresome, but a good trace and supporting reports can really help out.
This presentation will show you some of my own ideas, along with others' ideas on best uses for Profiler and Trace.
Once armed with this knowledge, you should be able to take it back to your shops and implement systems that assist you track down these issues more easily.

Auditing & Compliance and the DBA
Sarbanes-Oxley laws are changing the way companies collect, retain, and manage information. Database Administrators need to understand what is happening in the corporate business world, what auditors will be seeking and how it will directly impact their job role. What do auditors want? And how can we help?
This is one DBA’s tale of auditing his databases for modifications. There will be many different ways that you can approach this task, and reach a solution. I will explain how I reached my solution, and give you some good ideas of how to accomplish a similar task, for your own systems.

I am excited to participate in this event. I hope that my sessions go well, are informational, and help people out.

Friday, February 13, 2009

Things I wish I had Known When Starting my IT Career

I was tagged by Tim Mitchell in the latest get-to-know-you question. This one asks, “What do you wish you had known when you started?”

Sometimes I think I may have forgotten more than I have learned. But I always feel like we can learn more and more every day. In fact, its one of my driving forces. I always seem to be trying to learn more things and get the things I learned more perfectly implemented.

Being open to helping
Many moons ago, I was excited to be in the IT industry out of college. I’d imagine with a similar enthusiasm as a fresh IT degree grad. I felt I had a great...

Many moons ago, I was excited to be in the IT industry out of college. I'd imagine with a similar enthusiasm as a fresh IT degree grad. I felt I had a great quantity of knowledge, and often didnt share it with other coworkers. Regardless of the knowledge I may or may not have had, the point here is that I had a hard time sharing it and helping others out. I tended to gaurd the information that I had for job security. Later, I learned, that I needed to be more open with info and share with others. Of course, I often will go to the other extreme and share way too much. Its a fine science to find that middle ground of not being selfish with knowledge, but not overwhelm when not needed.
We all work with people that have a variety of information in their brain stores, and some will appreciate being taught, and others will not. And I need to remember that I too am on that sliding scale, and I need to accept knowledge that I do not currently have.

User Community
I have seen others mention this, and I felt it warranted a mention in my list of things I wish I had known. When I was in college, there was a community of people that were surrounding me, learning, willing to learn, and so on. We all tended to help each other out in class and study sessions. This group effort seemed to buoy us all up a bit more. When I entered the job force, it seemed that I hadto stand on my own. After all, I went to interviews alone. Went to work alone. Granted I had coworkers, but in the end, I felt like I had to stand up and prove my skills. At this point, looking back, I wonder what would have happened had I entered into the SQL Community...
I have benefited so much from attending Community events, and volunteering. Networking is a great benefit, and the knowledge that comes these interactions has added to my own. I suggest to all to partake in this opportunity.

Be yourself
As I am surrounded by people much smarter than myself, I need to remember that I to have a level of smarts. I have different skills that others, and bring different opinions and perspective to the table. Its often difficult to remember that one has a distinct set of skills that others. When you begin to compare yourself to others, you tend to loose yourself and your own skills in the mix.
Remember that you have abilities that others may not have. Expand your knowledge. Learn from others. But do not try to be just like them. Find your own path to SQL Stardom, or wherever your career journey takes you.

These are the first items that pop into my mind that I wish i had known. Most of them are things I am still working on.

I’m going to tag Pat Wright, Tim Ford, Louis Davidson
To ensure that this keeps moving, I will contact the above via twitter, so that they can read my blog, and move it along.

Wednesday, February 11, 2009

Twitter Rocks or how Data tweeted me

Today, after lunch, I had the need to do some research on a Replication issue we are facing at work. So, I did a couple google searches. I found my way to BOL from Microsoft on the command, as well as an article on SQL Server Central. Both of which were great assistance to me. However, I had the need to communicate with humans to see their experience. So, I looked at my Instant Messenger, looking for a DBA buddy that has a lot of experience with Replication. He was not online at the moment. So, there I sat, with questions and concerns and no where to turn.

That's when I remembered Twitter. Yeah, I use Twitter a lot. I tell my tweeps that I am at the gym. I tell them some random thoughts as they cross my mind. I recently quoted a U2 song via Twitter. I'll often send out random tweets about random things. But I tend to forget the reason I joined Twitter. It was to link up with fellow DBA's and gain from their experience.

So, I posted the question. Almost immediately I got a couple responses and follow-up questions. A dialogue ensued between a few Tweeps (@SQLFool, @SQLDBA). I was able to glean from their experiences and lend them to my own. This is one of the amazing things that come out of a simple question on Twitter.

Later that night, I was showing this off to my wife, amazed at the power that resides here. Not long after showing off Twitter, I received a random tweet from someone not of the DBA world. A while back, I started following @BrentSpiner and @LevarBurton, from StarTrek fame. It's been interesting to see into the tweets of these celebraties. So, a tweet comes across my screen from @BrentSpiner about work he is doing. I respond with a question. I have responded to tweets from both these folks, but never expected a response. @BrentSpiner has nearly 18000 followers. Since he mentioned working with other folks in show biz, I had asked him what his favorite Celeb encounter was. And he responded. There I sat at home, staring at my computer screen with a tweet from Data from StarTrek. How cool is that? Of course, I could only sit for a bit, before I ran upstairs to tell my wife how cool that was. I'm sure I'll pull this little gem out on various times and show off that @BrentSpiner responded to me.

The best thing about twitter is making connections. Not all the tweeps you follow will talk to you. Just like you will not talk to all the tweeps that follow you. But here and there, a connection will be made. A thought shared. A joke told. And maybe you too will have an encounter that can become a story to tell others. Maybe Twitter will help you out or just raise your mood. I goto bed tonight having spoken to Data from Star Trek. Just once. Briefly. But Twitter Rocks!!