Social media these days can be a full time job – at least I could easily kill many hours per week in this area. My job doesn’t allot any time for twitter, blogging, reading blogs, etc. Therefore if I want to network, read blogs, blog, keep an eye on what’s happening in the SQL world in general, I’ve got to be smart about it, stay organized and make some shortcuts. Here’s a list of my top 10 time savers to keep my finger on the pulse and interact with the SQL Community. Note, all of these apps/services are free – at least at the basic level, which works fine for me.
Google Reader – Anytime I’m on a computer, Google Reader is usually up. Anytime I am browsing the web, I’m looking for feeds. My three Google Reader tips:
Google Chrome – every browser has it’s strong and weak points – I’ve learned the ins and outs of Google Chrome and it works for well me. I know the keyboard shortcuts and I love the fact that the address bar is a built in Google search box with instant results.
Paper.li – This creates a daily paper from the shared links on Twitter. I try to set aside 5 minutes a day to read up on what’s happened in the last 24 hours. I’d recommend these two papers:
Buffer – This site allows you to schedule tweets (and facebook updates), but the really cool thing it provides is the analytics of the links you send via twitter. I was quite surprised – a few things I thought people loved, nobody clicked on. A few things I thought was no big deal, tons of people clicked on.
Dropbox – Is a simple, easy to use file synchronization tool. I install it in the same location on all of my computers for consistency sake. This also allows me to make bookmarks in chrome to dropbox files, sync my chrome bookmarks and have them work. It also works well for tasks in ifttt.
Keepass – Password manager – works great, I keep it in my dropbox folder and thus it’s on all my computers.
TrueCrypt – Creates encrypted volumes, which looks just like a file. Anything in my Dropbox folder that I want extra security on, I mount an encrypted volume in my Dropbox folder and add the files there. Okay, I know on the surface it doesn’t look like it has much to do with Social Media, but I can use this to secure files for presentations, upcoming blog posts, presentations, etc – plus I also have created drives using truecrypt for data files on my local machine to mimic the drive layout of a server.
Windows Live Writer – My blog editor of choice because it’s fast, free and easy to use. It’s just like MSWord. You can also customize it and add plugins to it.
I hope this has helped – I know they help me all the time. In fact, I started this post on a different computer, put the notes in my dropbox folder and posted via Live Writer on a different computer. Hopefully it will at least introduce you to one tool that will help you get organized, be more productive or interact with the SQL Community.
On March 3, 2012 I will be jumping into a hole in the ice in Lake Calhoun to raise money for the Special Olympics. One item on my bucket list will be crossed off – I don’t think I want to get good at this, but I do want to try it.
The big reason we are doing this is to raise money for the Special Olympics, this has always been one of my favorite charities. I love sports and competing in anything – this organization gives people with disabilities the opportunity to experience the thrill of competition and the fun of learning a skill and working on a craft.
This is where you come in. I need your help raising money. If you wish to donate to the cause, you can do so here.
Here are the facts about the event:
Who - Tracy McKibben* (B/T), Jason Strate (B/T), Afroza Ahmed and I make up Team #SQL Plunge
Why – I’m still not sure, Tracy tweeted something about not knowing if he should do it this year, I responded that with these temperatures, this would be an easy year to do it. The next thing I know…
Almost a year ago, we had a major database migration issue, actually it was one issue that caused another issue that cause performance issues – nevermind the specifics, it was complicated. The bottom line is that I knew I could fix our SQL server issues, so for a couple of weeks, I was deep, deep, deep into not only SQL issues, but presenting the issues to management, other developers – technical and non-technical, my boss’s boss’s boss was hanging on every word I’d say. In fact, the days were so full of talking, I ended up doing most of the technical work in the evenings after the kids were in bed (it’s been a re-occurring theme/frustration for me).
After the issues were solved, which I merely assisted in solving, but I ran a lot of the meetings – there were a lot of others who stepped up and really impressed me with there determination – I wrote some notes on what I’d learned from those weeks. Here’s the list I came up with:
Five Technical tips that should be kept in the back of your mind when troubleshooting MAJOR issues to avoid the HACK title:
Temp tables vs table variables – be consistent, I didn’t think the code would last, so we had scripts with Temp tables and table variables – HACK!.
Insert statement – use full insert statements when using Excel or a SQL Script to create a script. There is a 1000 row limit to using the insert …. values (…), (…), etc.
Instantiate variables – especially if you are using them in loops
Update stats as a performance boost
Check missing indexes
Four Non-Technical Issues to be kept in the back of your mind when troubleshooting MAJOR issues to avoid the Hack title:
Stay focused on the issues.
Project your screen so people can see your logic – you can be the show and they will learn from watching you – at least they will learn that you are smart!
Know the data don’t do extra work just to feel good – don’t waste time checking everything, at times there needs to be the leap of faith that things will work – if there are issues, baby steps is a good method for troubleshooting.
Sometimes quick hacks turn into permanent/repeatable fixes, keep TSQL clean/consistent/use best practices even in your quick fixes – you never know.
That last one was really in my notes – unedited! I said I was going to post a blog about it, but haven’t until now.
So, keep in mind, your quick hacks may turn into permanent fixes, therefore consistently follow best practices and you will avoid the HACK title!
This month’s T-SQL Tuesday, “Aggregate Functions” is hosted by Jes Schultz Borland (Blog/Twitter) aka grrlgeek.
The Task
One of my duties as a development DBA is to verify our databases are set up the same in each of our environments. One of the tasks of this duty is to verify our static data/look-up tables are all the same in each environment. I’ve got a good method for writing the insert/update statements – see this post – but I still need to verify the values are the same in our static data tables. My tool of choice is the CHECKSUM_AGG function.
How do I do this?
The easiest way I’ve found to do this is to take the BINARY_CHECKSUM of each row in a table that has static data in it (i.e. no datetimes, timestamps, etc). Then I take the CHECKSUM_AGG of the each row’s binary_checksum – thus giving me a Checksum for the entire table. I do this on the same table in two different environments and I compare the value. They will be equal if the values in the tables are the equal. For a more in depth explanation of this, see this post.
How this grew…
This method described above is easy to do for one table manually, but I’m responsible for approximately 10 to 50 static data tables in each of our 15 databases – in each of our five environments per project, so I needed to automate it. The good news is that we’ve got good naming standards for our tables, so all the static data tables end in “Type”. So I created a script that would calculate a CHECKSUM_AGG of all the BINARY_CHECKSUMs of each of the tables ending in “type” in a database, I could export that to excel, run the same script on a different server and compare the results. This worked for a bit, but I was doing the same thing on each database, so I added a bit of logic to include specified databases to my original script. Now, I run this script on each server and I compare the results.
The script:
A couple of highlights, the vSQLCall is included in the final output, so if there is a difference in values, you can easily run the single table script and compare the values of each row. (I had to do this one time as an update statement that was missing a period and the values were different.) Also, forgive the combination of temp tables and table value parameters – I like a temp table for the final results, so I can work with it and not drop it.
Create table #TestTable
(ID int NOT NULL IDENTITY (1, 1),
ServerName varchar(255),
DBName varchar(255),
TableName varchar(255),
vSQLCall nvarchar(2550),
ChecksumVal varchar(255))
--Add default for blank column
ALTER TABLE #TestTable ADD CONSTRAINT [DF_testtable] DEFAULT (' ') FOR [vSQLCall]
-- A work table to track the databases and the server name
Declare @TestTableDBs Table
(ID int NOT NULL IDENTITY (1, 1),
ServerName varchar(255),
DBName varchar(255))
Declare @icount int,
@TopNum int,
@sql1 nvarchar(2000)
--Initialize variables
Set @icount = 1
--Fill up the databases to do...Change DB list here if scope changes.
Insert @TestTableDBs
Select @@ServerName, Name from master.sys.databases
where name in (<<DBNAME1 goes here>>, <<DBNAME2 goes here>>, <<DBNAME3 goes here>>, ....)
Select @TopNum = Max(ID) from @TestTableDBs
--Fill in main table with all the tables needed to check
--Note any table name with '%type%' in the name
While @icount <= @TopNum
Begin
Set @sql1 = 'Insert #TestTable (ServerName, DBName, TableName) '
Select @sql1 = @sql1 + 'Select @@ServerName, Table_catalog, Table_Name from '+ DBName
+ '.information_schema.tables where table_type = ''BASE TABLE'' and table_name like ''%Type%'''
from @TestTableDBs where id = @icount
Exec (@sql1)
Set @icount = @icount + 1
End
---Now figure out the colums to of the tables and the SQL call to find the checksum
Declare @TableID int,
@CurDBName varchar(255),
@CurTableName varchar(255),
@Start int,
@loopStart int,
@end int,
@maxEnd int,
@vColumnName varchar(2000),
@sql nvarchar(2000)
Create table #TestTableColumns
(ID int NOT NULL IDENTITY (1, 1),
ServerName varchar(255),
DBName varchar(255),
TableName varchar(255),
columnNames varchar (2000))
--Initialize the variables
Select @Start = 1
Set @TableID = 1
Set @loopStart = 1
Select @maxEnd = Max(ID) from #TestTable
--Loop for each table to get the columns
While @Start <= @maxEnd
Begin
Select @CurDBName = DBName,
@CurTableName = TableName
from #TestTable where ID = @TableID
Set @vColumnName = ''
Select @sql =
'Insert #TestTableColumns
(ServerName,
DBName,
TableName,
columnNames)
Select @@serverName,
Table_Catalog,
Table_Name,
Column_Name
from ' + @CurDBName + '.information_schema.columns
Join #TestTable on DBName = Table_Catalog and TableName = Table_Name
where data_type <> ''timestamp''
and #TestTable.ID = ' + STR(@TableID)
Exec (@sql)
Select @end = Max(ID) from #TestTableColumns
--Loop through to get the column name for each table
While @loopStart <= @end
Begin
Select @vColumnName = @vColumnName + columnNames + ', ' from #TestTableColumns where id = @loopStart
Set @loopStart = @loopStart + 1
End --End of loop for all columns for each table
--Update #TestTable with the SQL call to get the checksum
update #TestTable
Set vSQLCall = 'Select CHECKSUM_AGG(BINARY_CHECKSUM(' + Left(@vColumnName, Len(@vColumnName) - 1)
+ ')) from ' + @CurDBName + '.dbo.' + @CurTableName
where ID = @TableID
Set @TableID = @TableID + 1
Set @Start = @Start + 1
End --End of loop for all tables
--No longer needed
DROP TABLE #TestTableColumns
---Finally execute the SQL in the column
Declare @Finalcount int,
@TopNum1 int,
@SQL2 nvarchar(2550)
Set @Finalcount = 1
Select @TopNum1 = Max(ID) from #TestTable
While @Finalcount <= @TopNum1
begin
Select @SQL2 = 'Update #TestTable Set CheckSumVal = (' + vSQLCall + ') where ID = ' + STR(@Finalcount) from #TestTable where ID = @Finalcount
EXEC (@SQL2)
Set @Finalcount = @Finalcount + 1
End
--Output the results
Select * from #TestTable
--Clean up
Drop table #TestTable
The Output
The Future
I’ve got plans to have Powershell run this on each server I specify, then combine and compare the results in a file and save all the supporting data. Thus when I need the documentation, it’ll take about 3 minutes (2.5 of which will be getting coffee), instead of the 1 hour it’s currently listed in the schedule to take.
This month’s T-SQL Tuesday’s topic is “Resolutions” and is hosted by Jen McCown (Blog/Twitter).
Here’s how I see it, my 2011 resolutions break down to the 3 Ps: PASSMN, Powershell and Present. If I do all of these, at the end of the year, I’ll say OH, what a year!
I am on the PASSMN Executive Board as the Director of Technology for 2011. I want to do my best in that role to, not only help the PASSMN group, but also to help me meet more people in the SQL world. I’m impressed with the board members and our user community as well. This gets me out from behind the keyboard and gives me a chance to rub elbows with some very interesting people.
Powershell
I want to work more with powershell. I’ve read about it and attended two presentations on it. I dabbled with it on the flight back from PASS. Then my daily work got the better of me and my powershell scripts collected dust. The other day, I started playing around with it and began to realize the power of it when I re-wrote our entire database deploy process in about an hour. I tweeted that and I got the most buzz on twitter I’ve ever had. In the past 3 days I’ve dabbled in it more and I’ve refined a few things. I like the power of it as well as the ease of use. I want to continue to learn about powershell.
Present
I’m going to do it this year – I’m going to present some topic to some people. What the topic is and who the people are, I’m not sure. Some of my ideas are:
A presentation on using DMVs for performance tuning for my developer co-workers
A General SQL Performance Series to my developer co-workers
Present something at a SQL Saturday Event
Present something at a PASSMN Monthly meeting
I’m on the look out for what the “something”s are. Maybe I’ll do a powershell presentation at the PASSMN meeting – that would kill 3 birds with one stone! Actually, I won’t do that – I’ve got things that I could do a much better job presenting than something new I’m just learning.
Okay, so now, take a look at the bold words in the first paragraph: “See”, “3 P” and “OH” or C3PO – that’s my tool to remember my resolutions. If you are still reading – may the force be with you!
Happy Thanksgiving – sorry it’s a day late – I’ve had some technical difficulties up in Grand Marais. I’m writing this in response to Jason Strate’s (blog/twitter) post asking “What are you thankful for?” This year, I’ve got many, many things to be thankful for. I try to keep my life simple: Family, Work and Running. Usually in that order…
Family/Friends
Always #1! I’ve got a great family that I’m very thankful for and some unbelievable friends. Here’s a link to the Family Photo Site – see tons of photos of my family. Here are some of my favorite group shots and my two 11 year old dogs.
Work
I’m very thankful that my career has taken an unexpected turn – in a good way. I’ve got lots of new opportunities coming up…
Some time to actually do development DBA work.
Keeping up this blog.
Working with others on the MN SQL Pass executive board
I’ve been blessed with good health. For some reason I like running – at times I’m not exactly sure why…but I’m very thankful for it! I get to run the Boston Marathon this year. Read all about the trials and tribulations I went through to qualify and raise money for the American Cancer Society here.
I’ve worked at many different sized companies. I worked for a company that went from from 300 total employees to 9500 employees in a six year time span. I’ve seen the point where database administration goes from someone’s part time tasks to 12 FTEs. What I hadn’t seen, until about a year ago, was the benefit of DBA skills while working with a group of smart, technologically up-to-date, well-meaning application developers. A little more than a year ago was the time I moved into a newly created role as a database developer.
At first, I was very impressed with my coworkers’ knowledge of the databases in our environment. Everybody seemed to know the data and how to get the data in and out. At first I thought, “Wow – this is going to be easy.” Then after really digging into things, I realized we’ve got 30 people doing things 30 different ways in the database. Not only is it confusing and time consuming to trouble shoot anything, but it was also hurting performance. We’ve had our battles with deadlocks due to procedures doing similar things in different orders.
Time for my DBA skills to go to work! I started to really plant the seed that things in the database need to be scalable and maintainable – those are the points I wanted to drive home.
Initially, I worked with our database architect and we created naming standards. These are pretty simple and straight forward, but they are rules now. Due to time, budget, resources, etc… we didn’t take the time and go back and rename anything. We just drew a line in the sand and said – anything new will be named according to these standards and anything that gets edited will also be renamed.
Not only did we work on the naming conventions, we created a database development guideline document. This is basically a list of best practices for developers. All of the practices in the document can be traced back to our databases needing to be scalable and maintainable. For instance, one of the catch phrases I came up with was, Big Trys, Small Trans (the quick recap is to have a Try..Catch block around the entire procedure and make all trans actions as short as possible) .
Finally, all database changes are now done by me or reviewed by me, but this doesn’t mean my work to share my DBA skills is done here. For starters, if I’m doing ALL the changes, I’m not scalable. Also, we’ve got a long way to go to get educate these smart, technologically up-to-date, well-meaning application developers. Honestly, just this last Friday, as I was at SQL Saturday #58, I got an email asking, “Can we store the GUID as a combo of first and last name?” As I said…a long way to go!
In order to make databases that are both scalable and maintainable, whenever databases are involved, DBA skills aren’t just nice to haves, they are necessities.
This month’s T-SQL Tuesday, “Misconceptions in SQL Server” is hosted by Sankar Reddy (Blog/Twitter). According to freedictionary.com, the word “sophomore” was influenced by Greek words “sophos” – which means wise – and the word “mros” – which means stupid. I’ve also heard it means “Wise Fool”.
Every place I’ve ever worked has had their own misconceptions about SQL server. Almost every place has had the mythical silver bullet…the one root cause – the silver bullet – that all issues come from.
Initially, in my career, the users I supported had a fear of log backups killing server performance. This was, in large part, due to my ignorance and carelessness. When asked if there was anything going on on the server, I announced that the log files were being backed up, that’s the only thing that’s going on. This had nothing to do with the real performance issue (blocking), but people heard that come out of my mouth, so THAT was the issue! Two days later, I got a call from a user who wasn’t involved the initial situation. It went something like this:
User, “Can you stop the log file backups?”
Me, “I could….but I don’t think it is a good idea. Why are you suggesting this?”
User, “Well, it’s taking 20 seconds to pull up a customer page.”
Me – in my Eddie Haskell voice, “Thanks, for the the suggestion, but I’ll take a look at the server and see what’s going on – hopefully I don’t have to stop the log backups.”
At a different company I was at, there was a poorly planned server that I administered where all the files were on the D drive – binaries, data, logs, system DBs, backups, etc – all on the D drive. There was a large import job running that caused the TempDB to expand. This expansion caused the backup jobs to fail, which then caused the backup deletion jobs to fail, which then caused the log backups to fail and finally the log files couldn’t expand, so no transactions were allowed on the server. (Note – This was back in SQL 7.0, so things weren’t nearly as smooth back then as they are now). But I mentioned that the TempDB expansion may have caused the entire incident. Sure enough – a couple of weeks later (weeks this time), I get a call asking if I could shrink the temp files.
I can spin yarns all day. Different companies, different databases, but a similar situations always occur. When all users have is a hammer, everything is a nail. Recently, the silver bullets have included:
Reindexing
Batch import processes
Reports running
Tables not having a timestamp column
This is why, as a DBA, you ALWAYS have to watch what you say. There are “Wise Fools” always looking for the answer that sounds really smart. Inevitably, there will be a situation where someone has a misconception and insist they are correct.
Here are some suggestions on how NOT to to deal with these situations:
Partitioning an entire table, that is currently not partitioned, based on rebuilding a clustered index
Or
Moving data from a temporary table to an empty table that is partitioned based on the clustered index
We are currently testing both situations as we can’t logically figuring the fastest way based on theory. There have been lots of debates and lots of theories on which is going to hold up.
Why?
You may ask why would one care about the faster option, well, here’s the situation we are currently in:
One of our logging tables in our production environment has an ID column that is an int. This ID column is currently at about the 1,900,000,000 range – that alone makes me say YIKES! An int “only” goes up to 2,147,483,647, so we are approaching this mark fast. The solution that we are going with is to convert that column to a bigint data type. A bigint’s max value goes up to 9,223,372,036,854,775,807 – now that ought to do it!
Our Options:
Here’s our current options with the basic steps to change the data type:
Option 1
Drop all the indexes on the table
Drop the partition schema
Drop the partition function
Alter table to the new data type
Create the partition function
Create the partition schema
Recreate the indexes on new functions which will partition the table
Test
Option 2
Copy data to new table
Drop existing table
Drop the partition schema
Drop the partition function
Create the partition function
Create the partition schema
Create table and indexes
Copy data back to original table
Test
We’ve started a pool on what’s going to be the fastest. It’s all coming down to the indexing – leave a comment and let me know your educated guess and why. I’ll definitely be blogging more about this one!
This month’s T-SQL Tuesday, “T-SQL Tuesday #009: Beach Time” is hosted by Jason Brimhall (Blog/Twitter) aka the sqlrnnr. The topic is: What do you do as a DB professional to earn a little “Beach Time?” What do you do prior to “Beach Time” to ensure that the beach time will not involve work?
Most of my vacations involved traveling out of cell range, so my vacations are usually guaranteed to not be interrupted by work. My college buddies and I go on an annual trip to Canada’s Quetico Provincial Park, my parents have a cabin on the Gunflint Trail in northern MN. Being out of cell range is a bit more stressful than it sounds, but that’s why I’ve worked out a system to ensure things run smoothly when I’m gone. My system is all based on taking care of things before I leave the office because – I believe it was Ben Franklin who said, “an ounce of prevention is worth a pound of cure.”
First, I always let the people who I’m working with know I’m going on vacation weeks before. I tell them in meetings and email my boss once plans are firmed up and dates are set. One thing at my current job which I couldn’t stand at first, but now I find useful is that we send out meeting requests to each other saying when we are out of the office. The big keys to sending these out is to disable the reminder, set the time as free time and do not request responses. This way it’s on other’s calendar as a reminder, but that’s about it.
Next, and this is the hard one for me, at times, is I tell people no. No, I can’t get this done before I leave. No, I can’t make your meeting, I’m finishing up Project A. etc. I also decline all meetings and block out my calendar (set to busy) for the days I’m going to be gone.
Finally, I’m a big list maker. I usually list out what I’ve got to do everyday, when I’m going on vacation I’ll add a column to my list and put a secondary name by it. I go over everything I can with the secondary person, so they know what’s going on and if there is an issue while I’m away, they can be contacted. Because I’m a list maker, I’ve also got a checklist of “Things to do for Vacation”. Most of the items are for issues with specific environmental tasks around here, but the big one is to turn on my out of office assistant. I did forget once and had a few of upset users know the best way to get a hold of me is via email and I wasn’t responding.
These things don’t really take too much time to do. If you do these simple things, there will be not only less hassle when you get back in the office – making your vacation stick a bit longer, but also it will give you a chance to fully relax when your on vacation. It’s just an ounce of prevention!
