Imagine a 3500 Page Book…

having 400-500 level SQL Server internals content WITH (COMPRESSION=PAGE) that gets it down to char(140) per page.

The changing face of community

I am talking about Paul Randal’s twitter feed. Tweet after tweet of internals and storage engine goodness. I hope to take a little credit for getting Paul on twitter. Peer pressure is tough. Although, who is the first person he follows? A marketing dude! @BrentO WTF?

Then he turned into a monster. 50-100 tweets a day! Noisy, yes, but how do you unfollow Paul Randal?!?! That said, IMO, he is now on the cutting edge of the SQL community. Both technically and logistically. That combination is as rare a SQL Server(past or present) dev team member who also has great presentation skills. Err, wait… That would be Paul too.

Ok, I am about to go get some sandpaper, a blow torch, hydrochloric acid and my wife’s tooth brush to get that sh!t off of my nose. Shhh, don’t tell wifey.

http://twitter.com/paulrandal

This is just today!

1. #sqlconn 2008 extended events allow session-level wait state tracking. Neat demo from Gert.about 3 hours ago from TweetDeck

2. #sqlconn Don’t worry about high preemptive waits, they can be expected for some operationsabout 3 hours ago from TweetDeck

3. #sqlconn Running xp_cmdshell will show up as a PREEMPTIVE_OS_PIPEOPS wait in sys.dm_os_wait_stats.about 3 hours ago from TweetDeck

4. @datachick Truffles work well – they’re Kimberly-crack.about 3 hours ago from TweetDeck

5. @datachick I don’t think I’d get the same response from @KimberlyLTripp…about 3 hours ago from TweetDeck

6. #sqlconn Use sys.dm_os_threads to get the OS-level threads running. Then look in Process Explorer to get call stack + see why it’s waiting.about 4 hours ago from TweetDeck

7. @mrdenny That is true. I guess in the city one would pose, and on the freeway, one would zooom.about 4 hours ago from TweetDeck

8. #sqlconn Which are run by threads, running on multiple schedulers within an SMP/NUMA node 2/2 Try saying that ten times after a few beers!about 4 hours ago from TweetDeck

9. #sqlconn A session can issue multiple requests. Which can run multiple tasks. Which are run by workers 1/2about 4 hours ago from TweetDeck

10. #sqlconn 2005 RTM assigned tasks to least loaded scheduler – that code is commented out know and it just does round-robin now. Too complex.about 4 hours ago from TweetDeck

11. @mrdenny I don’t know about that – whenever I’m driving from SFO to Mountain View, the 101 seems like a race track!about 4 hours ago from TweetDeck

12. #sqlconn Assuming affinity mask is 0, you’ll have at least 1 scheduler per CPU, plus some hidden schedulers (e.g. DAC)about 4 hours ago from TweetDeck

13. #sqlconn Forget about fibers. Benchmark special, lot’s of things stop working. I totally agree.about 4 hours ago from TweetDeck

14. #sqlconn SQL does not distinguish between logical procs and physical procs – watch out for hyperthreadingabout 4 hours ago from TweetDeck

15. #sqlconn Gert’s explaining about preemptive scheduling models, makes me want to go back and read Tanenbaum’s classic OS books from college.about 4 hours ago from TweetDeck

16. @kbriankelley That’s like Wired. I don’t read every issue, but $12/year, how can you go wrong?about 4 hours ago from TweetDeck

17. Waiting for Gert Drapers to start the last session of the #sqlconnference, on What are You Waiting On?about 4 hours ago from TweetDeck

1. @AdamMachanic Scared to yell ‘Buck’: might be a thunderclap + @BuckWoody appears out of thin air. Bad enough when I know he’s coming! ;-Dabout 5 hours ago from TweetDeck

2. #sqlconn Walked into the 500-person room again and this time let loose with a thundering ‘echo’. Results were childishly impressive about 5 hours ago from TweetDeck

3. RT @KimberlyLTripp: #sqlconn #sqlpass Just blogged SQL Server MVP Deep Dives bk (w/ pic from launch @ PASS):http://bit.ly/42YNDj. Enjoy!about 6 hours ago from TweetDeck

4. #sqlconn RML Utilities looks to me like performance data collection in 2008about 6 hours ago from TweetDeck

5. @MladenPrajdic You beat me to the tweetabout 6 hours ago from TweetDeck

6. @BradDBA @heigesr2 I wondered if anyone would get the joke about 6 hours ago from TweetDeck

7. #sqlconn Use fn_trace_gettable() to treat a trace file as if it were a trace tableabout 7 hours ago from TweetDeck

8. #sqlconn Q: if you stop/start a trace, does it append to existing file or create new one? A: it appendsabout 7 hours ago from TweetDeck

9. RT @SQLChicken: @PaulRandal Here’s an article I wrote on how to create server-side trace with profiler http://bit.ly/eZQYV #sqlpass#sqlconnabout 7 hours ago from TweetDeck

10. #sqlconn But it’s far easier to get a scripted out trace definition from Profilerabout 7 hours ago from TweetDeck

11. #sqlconn Looks like setting up SQL Trace manually is pretty quirky compared to using the GUIabout 7 hours ago from TweetDeck

12. @MladenPrajdic Answer is no then I guess.about 7 hours ago from TweetDeck

13. #sqlconn Profile when a batch completes, not starts. Common mistake. You can’t get duration, cpu, etc except when it completes.about 7 hours ago from TweetDeck

14. #sqlconn When profiling, make sure you filter, but don’t over-filter.about 7 hours ago from TweetDeck

15. @MladenPrajdic What do you mean by type? XML vs varchar (max) vs text, for example?about 7 hours ago from TweetDeck

16. When I don’t know the answer to an internals question, I ask Bob Ward @bobwardms. Follow him.about 7 hours ago from TweetDeck

17. @StrateSQL Common problem. SharePoint is an example.about 7 hours ago from TweetDeck

18. Someone next to me just had a phone call and it was the muppets as a ring tone – MANAMANA!!! Doo dooo de doo doo. Excellent!about 7 hours ago from TweetDeck

19. This is the first #sqlconn ference in 3 years where I have a spare day where I can actually watch sessions and learn stuff – very cool!about 7 hours ago from TweetDeck

Can you be too Thorough?

Can you take thoroughness to a fault? There are some things that cannot be taken too far like intelligence. In a business setting, have you ever met someone who was just too smart(competitors do not count)? How about too honest? Too skilled? Too good with customers? I do not think thoroughness fails into this category though my opinion has recently changed. I used to think that, as a DBA, I could not be through enough. In some scenarios this is true like backups and recovery testing. There are other areas where it just does not pay. For example, performance tuning. That last 10% might not be worth the 6 months that goes into it.

Let’s look at an extreme example of cycling the errorlog nightly. This could be as simple as setting the retention policy to greater than the default of 6 and configuring a job to run sp_cycle_errorlog. However, let’s get thorough.

1. Lets write our own proc to  do this. It calls sp_cycle_errorlog but we want to add our own logic.
2. Lets start off by writing to the windows application log: “Beginning Errorlog Cycling Process.”
3. Lets dump the errorlog to a table in msdb in case we ever want to query it.
   1. Dump xp_read_errorlog to #table and do a binary_checksum() with the new rows in the errorlog table so we are sure that SQL Server inserted them correctly.
   2. Errorlog table cleanup process
   3. SSIS package to push errorlog entries of the past 24 hours to a centralized enterprise-wide errorlog repository.
      1. Checksum across linked server
      2. Extract to text file increase we ever want to view it like a real errorlog file.
         1. Create a file name creation function that created a “unique” name and a table that maps the “unique” name with something that a human can understand.
4. Prechecks for sp_cycle_errorlog
   • Check disk space
   • Run checkdisk
   • Verify the previously cycled errorlog has a date of getdate()-1
     • If the previous errorlog is less than 24 hours old, check to see how long SQL has been up.
     • If the previous errorlog is older than 24 hours, halt processing. Page the DBA team with a critical alert because this should never happen EXCEPT once a year during the time change.
   • Run a custom process that steps through the previous steps to verify the completed successful.
1. Run sp_cycle_errorlog
   • Set the step to retry 10 times.
   • Log the Output to a text file.
   • Write a custom windows email service to send the results of the job step to the DBA team encrypted with PGP.
2. Post sp_cycle_errorlog steps
   • Zip errorlog
   • verify the archive with the compression software
   • Run a CRC on the zipped file
   • Copy the archive to the file server along with a text file containing the CRC.
     • Run a CRC on the file server and compare with CRC in the text file
3. Write “Cycle Errorlog Process Complete” to the windows application log.
4. Email the entire DBA that this process was successful.
5. Move copies to the cloud at Amazon’s S3, Windows Live Drive, Drop.io.
6. Pass the entire content of the errorlog to twitter CHAR(140) as a time for the internet can crowdsource your errors.

Again, extreme example but that is being overly thorough to put it mildly. Where is the balance? I think I have come to realize that a trait of a great DBA is balancing thoroughness with discretion. Some tasks\process\queries need the utmost level of thoroughness yet some just need to work. Sometimes, you have something that needs to be thorough but it is more important for the query\task\process to be in place yesterday. In that case, you have to sacrifice thoroughness for speed.

I am glad I was able to step back and learn from the real world example that got me thinking about this.

What personality traits do you think tend to be common among the top 10% DBAs or geeks in general?

Upgrade but Avoid v1.0 Features

Holding off on the newest features sounds like a no brainer especially when the next SQL Server release seem a million years away and is BI focused. In the reality, I was partitioning tables the first night we upgraded to SQL Server 2005. I could not replace TEXT with VARCHAR(MAX) fast enough. Some DTS packages got hit with the shamwow and emerged in SSIS. All of these changes broke stuff. None of these were necessary. A straight upgrade of the engine would have been fine and brought plenty of bugs on its own.

So, new features have more bugs when we eyeball it based on SP and CU fix lists based on these three examples.

Mirroring

SQL Server 2005 at SP2 – 6 fixed published bugs

SQL Server 2008 at SP1 CU3 – 1 fixed published bug

Partitioning

SQL Server 2005 at SP2 – 5 fixed published bugs

SQL Server 2008 at SP1 CU3 – 1 fixed published bug

SSIS

SQL Server 2005 at SP2 – 10 fixed published bugs

SQL Server 2008 at SP1 CU3 – 3 fixed published bugs

This, of course, it not scientific. Not all bugs are published and not all bugs are fixed. For example, I found a bug that has been present since SQL Server 2005 RTM and they just stated “issue should be addressed in the next major release of SQL Server that comes after 2008 R2.”. That is like 2012 or 2013. Granted, it is not a major problem but still.

To MSFT’s credit, they have revamped the SQL Sever dev process after SQL Server 2005 so hopefully that will result in less bugs but sometimes I get the impression that they could throw more dev cycles at bug fixes.

So, yes, upgrade but hold off on the version 1.0 features unless there is a good business case for using them. How would you like to be one of the handful of customers relying on SQL Server Notification Services?

DBA Compliments

“Wow, the application sure is smoking today.”

“You know, Bob, I cannot remember the last time SQL was down. You sure are doing a good  job.”

“Gee, is the log always that big or are you happy to see me?”

Ha, every production DBA knows those compliments NEVER happen. We have a thankless job. No one ever notices until the database is down. Think I am kidding? When was the last time you told your Exchange buddy how solid the mail server has been?

Well, I am talking about a different compliment. Actually, Colin Stasiuk (Twitter) is talking. Complimenting skills is of which he speaks. I just thought this topic deserved more than a comment.

Colin writes:

I’ve always ranked myself from a discipline point of view as:

1. Database Administration
2. Database Development
3. Business Intelligence

Now I’m trying to decide if I should “bone up” on the business intelligence side of SQL Server to try and get that around where my database development skills are at or whether I should branch off and do something different altogether.

The short answer is YES and it is not meant to answer Colin. I just think this is a great topic. It really doesn’t matter how you grow as a professional(and person in general IMO) as long as you grow. Aww, *hugs4all luvu2* It would be helpful if you choose something that adds to your professional capital but, hey, taking up Feng shui may provide the stress relief that you need to take it to the next level.

Staying on topic marginally, we will focus on the most relevant side technologies to a DBA and related database professionals.

Colin mentions the three pillars of a database professional:

Database Administration

Database Development

Business Intelligence

 

I submit to you you must be proficient in two of those to be in the top 25% of the market. A DBA better be sufficient in tsql if they need to write admin scripts or troubleshooting OLTP code and performance. A BI professional needs to know database development for loading data and writing reports. A Database Developer definitely benefits professionally by knowing either of the other two pillars.

I would specialize in one at the 500 level and get to know a 2nd one at the 400 level. Think of the side skill as your “minor” in college. There are not many who can focus long enough to master all three and gain real world experience. So you have two pillars that you are fully functional in, now what?

Here is a far inclusive list of desired talents in Database Professionals:

• SAN administration
• .Net development
• Powershell administration
• Project management
• Windows\Active directory administration
• Biztalk
• Message queuing apps like MSMQ
• Hardware experience
• IIS administration
• Other RDBMS
• MSFT or Vendor X App\Middleware servers
• Technical writing
• Automated load testing
• Presales consulting skills
• Virtualization experience
• etc

The list is really endless. You could even find small shops that would love their DBA to also know exchange so they get 2 for 1. If you can think of it, there is or will be an employer looking it. “Nude midget” returns a job posting on Monster. I kid you not.

So what do you “minor” in? You probably choose out of necessity first. The success of your project requires a project manager or some small .net code refactoring. You either suck it up or the project fails. That is why they have a DBA who has to manage the project instead of a PM who learns database administration on the fly. If you have the luxury of choosing, I would pick the skill that balances what interests you and what adds to your brand the most. This makes learning easy while helping you professionally.

Now you know what you are going to work on. What is next? I would try to tie the learning with experience. If you are learning out of necessity, that is a non-issue.  If you are trying to pick up a new skill in between consulting gigs, you may consider other options. If you are working on project management, volunteer. A lot of charities post their IT projects on craig’s list and you may be able to help while learning. They may post looking for a Dev but hit them up and see if the need a PM, tech writer, OS admin etc. If you are going with .Net development, you can join and contribute to an open source project like on codeplex or DotNetNuke. Use your imagination. For every one paying gig, there are hundred of opportunities to gain experience while learning. You see, a project manager suddenly becomes an option when the price is free.

Once again, complimenting your DBA skills is a definite YES. Complimenting your DBA is optional but don’t blame SQL Server when your resources automagically get governed.

Capt. Varchar & the Pagelatch Posse Vol. 26

The Default Value.

DBA’s are notoriously noncommittal. If you don’t believe me, next time you see Paul Randal(WWW, Twitter) at a conference or other event, walk right up to him and ask him how long checkdb on a 500GB database will take.

Things you Know Now…

There is a new meme started by Mike Walsh (Twitter, blog). He tagged Brent Ozar(RSS, Twitter) who tagged me amongst other.

Here is the the basis of the meme to quote Mike.

When I wrote about empirical evidence and learning through trying (instead of asking only), I got thinking about things I wish I knew when I was a Junior DBA that I know now.

So here is what I know now that I wish I knew then(and usually learned the hard way).

Microsoft Project is your friend.

I have written about this before but it is worth it to revisit. I started out making big production changes to mission critical systems with nothing but a task list in my head. I evolved to notepad and then excel. My success % improved with each jump. Now, I can floor my boss and customers with downtime estimates accurate the minute. On top of that, I can establish doable timelines and get more resources if my time line does meet expectations.

You can be your worst enemy.

Ego can make a brilliant employee a liability. It manifests is several ways( at least for me).

• Jump on the new groups sporting all your brand new certs in your sig trying to “help”. However based on attitude, it is clear you are there for the wrong reasons usually attention.
• Bogarting knowledge. So you know something someone else does not, teach them instead of holding it over them. Help out he jr guy. He may be your boss someday.
• Ego can keep you from asking for help when you are clearly in over your head. I still struggle with this one.

Life is so much better when you are modest rather than smug.

If the hole is round, a square peg may not be the best fit.

I have officially become platform agnostic. SQL Server will always be my first love and what I am best at but there are other products out there. Not that I know everything there is about SQL but I don’t learn 10 new things about it everyday like I used to. As a n00b, I learn 30 new things a day about MySQLOracle. There are valid reasons to go MySQL or Oracle over SQL Server. That is just the way it is. Imagine rewriting the DAL layer for Wiki or Wordpress just because you had to run it on SQL Server. If you drink that much koolaid, more power to you. I think knowing the features and limitations of other platforms helps me as a SQL Server DBA as well.

The GUI is not your friend.

I used to be an enterprise manager DBA. When I learned how to admin from TSQL, that is were the Senior DBA level skills came in. I still use the GUI if it a click or two vs. several lines of code but I know how to write it and, if need be, automate it. If you can’t, learn.

Know X as good or better than the subject matter experts

Where X is technology that interacts with the database: The OS, hardware, SAN, network, and application code. Of course, this is not always feasible. I have never jumped on a switch to prove it is not a SQL Server problem but I have gotten pretty close. Once when all fingers pointed at SQL Server, I had them check the switch for errors and sure enough the firewall was set 100/half duplex. If nothing else, learn the hardware and OS inside and out.

Next Victims

Jonathan Kehayias(RSS, Twitter)

Jason Strate(RSS, Twitter)

Rob Boek(RSS, Twitter)

MySQL Cheatsheet for SQL DBAs

SHOW VARIABLES – This is like sp_configure or sys.configurations

SHOW STATUS – This like sys.dm_os_%. Basically, all runtime counters for the database engine components.

SHOW PROCESSLIST – This is like sp_who2

SHOW TABLE STATUS – This is like sp_help

These can be filtered like this: SHOW VARIABLES LIKE ‘%innodb%’

Those are the big ones. The full list can be found here.

A great post on Twitter and the DB Pro

 And not just cause my name was dropped. I have said it before and I will say it again… TwitterFacebookFriendFeed has made the internet interesting for me again. The flame has been rekindled.

‘A New Look at New Technology’ Or ‘How Brent Ozar Made Me Buy A New Phone’

by Tim Benninghoff

 

 

 

 

 

DBA Shenanigans

 Check all the other geek graffiti @ oddee

Social Computing for The DBA Slides and Source

Here are the slides from my SQL PASS DBA SIG live meeting today. I thought is was a timely topic with the increase of twitter usage in the SQL Server community after the PASS Summit. A lot of people said they could not get in with the passcode. My apologies on that… I am not sure what happened there. Anyway, here are the slides and .sql file. Please feel to hit me up if you have any questions.

Edit: If you missed the audio too, this post is still valid.

Next Page »

• My RSS

  

• 

• My LifeStream

• My Presence

• My Friends

• My Games

  
  

• Recent Topics

  • Imagine a 3500 Page Book…
  • What is Professional Development?
  • Filtered Stats to Counter Data Skew Issues
  • Sampling Problem with Filtered Statistics
  • Life
  • Professional Development: Internet Image
  • OT: Music – What the Fork?
  • The DR Cloud: An Example
  • Can you be too Thorough?
  • Disaster Recovery in the Cloud

• My Cloud

  • automagic bad practices beta Blogging bugfix Capt. Varchar certification cloud Clustering codeplex community conferences Crybaby cxpacket Data collector Database Mirroring DBA disaster recovery DMV's DMV’s editorial Feature pack filtered indexes flamebait friendfeed fud geek geek pr0n google reader HA Hardware Heroes Happen Here humor IaaS indexes lamp lifestreaming MCITP meme memory Mirroring Mixed Workloads music mysql NTSSUG Off topic Offtopic OLTP opml Oracle pbm performance powershell procedure cache professional development profiler query optimizer query tuning RC0 RDBMS Reporting rss rumors SAN scale out vs. scale up scaling screencasts SDS Security social computing SQL Connections sql heros sql injection sql pass SQL Server 11 SQL Server 2000 SQL Server 2005 SQL Server 2008 sql server web edition SQL shorts SQLCLR sqlheros SQLPASS sqlps SSD ssms SSRS Statistics teched tsql twitter Upgrade utility computing Visual Studio 2008 VMWare Windows Windows Server 2008 WMI x64 xquery