Voice of the DBA

Author: way0utwest

  • Brent Ozar Blitz at SQL Server Connections

    The first session of the day for me was Brent Ozars Blitz talk on how to quickly get information on a new server. I have seen Brent’s video on this and read some of his blog posts, but i wanted to see him go over the script in a session.

    Brent is funny and has a way of interacting with the audience that makes his talks enjoyable. In this one, he walks you through the script, taking you through the list of items that he finds important to check when he first takes over a server. You can get the script from BrentOzar.com, or search Brent and Blitz in your favorite engine.

    It’s important to go over certain things in a new server, and i would agree with what Brent checks. He looks for backups, DBCC execution, jobs, privileged accounts and more. He has scripts that query the system databases to find out this information. It’s a good practice to use these types of scripts to ensure that you have self-documenting information from your instance. It prevents your information from being out of date.

    A few things I learned in here. One was to check for backup history in msdb since that can slow down your backup process over time. The msdb table for backup tracking isn’t well indexed and can fill over time 30-60 days should be enough history. Another is to check for encryption of your databases. I typically don’t work with Enterprise Edition so I don’t run into TDE. However its good to know this and prep your keys for a DR situation. Nothing worse than trying to restore and not having those keys. Also, once you encrypt a database, tempdb is always encrypted. Even if you remove the individual database encryption. That’s good to know. It might not e a big deal, but it is extra overhead.

    I also found a nice check for objects in master or model, which isn’t a recommended practice, and you might not think to look and be aware of anything that is stored here. One thing i was not aware of is a query to check for Enterprise Edition features being used in a database since you cannot restore these databases in other editions. actually you can, but at the end the restore process will throw an error and then fail.

    I would definitely recommend this session if you find Brent speaking at an event near you.

  • Locking and Blocking from Joe Webb – DevConnections

    Locking should be simple, right? You need to access a row or table and you get a lock. Oh yeah, blocking is bad.

    It’s not that simple, and I find this is an area that many people do not understand that well. Joe does a nice job of presenting this from a beginning level and going into more details on locking using lots of demos.

    Blocking is the result of contention, but that is good. It helps enforce the consistency between resources. Joe shows how SQL Server tries to balance the concurrency and consistency requirements, showing escalation and how to query the sys.dm_tran_locks table and understand what is being locked and why.

    When does SQL Server escalate? The default is 5000 locks but it is not that simple. There are other factors that come into play.

    This was an area I studied a lot for the MCM and I learned a lot about the details of locking behavior. Joe does a nice job of simplifying locking for the DBA or developer looking to better understand why locks and blocks occur on your server.

    Has anyone modified the parallelism lock threshold? No one in the audience and I would be interested in hearing if anyone reading this has.

    Controlling locking is a bit of an art. There are hints you can use, like NOLOCK or READPAST. These can help, but this should not be a standard in all your queues. It’s like duct tape. It can “appear” to fix a problem but it’s really just alleviating some pain. A tool but not necessarily the first one you want to use.

    Keep in mind that your hints are “requests”, not mandates. SQL Server many not honor them.

    By default, SQL Server will wait indefinitely for a lock to clear. Deadlocks are an exception, where one process will have it’s locks clears when it is killed. You can set a lock timeout but if you do this, the application need to handle the 1222 error and then retry.

    This is a good session for you to attend if you are new to locking and want to learn more about how this works. As with many things in SQL Served, there are no hard and fast rules to apply but you will learn some tricks to try and where to look to get more information.

    There are also isolation level settings you can change. These are a little more drastic and you really need to understand the impact of them, especially snapshot isolation.

  • On the Move to SQL Server Connections

    Brian Kelley, speaking on the SQLServerCentral track.

    A bit of a crazy day for me, starting off a crazy week. I leave this afternoon for SQL Server Connections in Orlando, where it’s in the 80F with some rain. It was about 60F yesterday and sunny in Denver, a beautiful t-shirt day and today I woke up to snow on the ground and more coming down now. I’m a little torn, since I’d rather be heading to the mountains to snowboard than the pool to swim, but I am looking forward to SQL Server Connections.

    SQLServerCentral is sponsoring a track, with Brad McGehee, Brian Kelley, and Jonathan Kehayias speaking. We have set up sessions and I’ll be down there as the roving reporter and SSC host, sporting a Hawaiian shirt, my cowboy hat, and  no pants. At least, I’m hoping I get by with shorts for the two days I’m down there.

    We have sponsored a track the last two years and it’s great fun. We get the chance to pick speakers, all seasoned SQL Server experts and presenters that will give you a great experience on the SQL Server track. Unlike PASS, the speakers are invitation only, both from our track, and the SQLskills track, managed by Paul Randal and Kimberly Tripp. Unlike some other events, I always see great sessions at Connections and I am almost never disappointed.

    There’s also great variety since the conference is located in the same hotel as the ASP.NET, Silverlight, Visual Studio, Sharepoint, Exchange, and Windows conferences from the DevConnections event. If you work with multiple technologies, this is the best conference to go to and get a variety of knowledge on different sessions.

    It’s also in a great location. I’m one of the strange ones that likes winter, and I don’t necessarily want it to end quickly, but most people are happy to get away from the cold for a warmer location. The spring event in Orlando, and the fall event in Las Vegas, give you the chance to do that.

    If you aren’t coming this week, keep an eye on the blog for notes and content from the show where I’ll be reporting on sessions, taking some pictures, and perhaps some video. Think about coming to the fall show in Vegas, and get some great SQL information in a great location.

  • SQL in the City – The Red Gate Tour

    Red Gate Software is announcing SQL in the City today, a series of events that will bring a free day of training to a few locations later this year. The events will be free and features specific Red Gate products and solutions to the common problems and situations that many SQL Server professionals face. It’s a mini tour with a few people from Red Gate and a few local speakers as well. We have two dates/locations scheduled so far, and we’re hoping for more soon.

    sqlcitylondon

    London. July 15, 2011.

    Once again I’ll be heading across the pond. I will probably go 3-4 days early since it seems I regularly have issues flying internationally, but I’ll be there, along with fellow Microsoft MVPs Brad McGehee (blog | @bradmcgehee) and Grant Fritchey (blog | @gfritchey). There will be a few Red Gate people and MVP Mladen Prajdić is going to be giving a talk as well. We will be at One Wimpole St, wherever that is in London. Hopefully someone will ensure I arrive at the right place.

    It’s a full day of SQL training and networking, giving you the chance to pick one of two tracks and learn some development tricks and tips, or come over to my track and learn how administrators deal with the problems they face on a regular basis. The agenda has a variety of topics that you’ll want to see. I’ll be doing the Preparation for Disaster talk, showing you some of the issues you might face at work and how to prepare for them, and recover from large and small disasters. It will feature some Red Gate tools that speed up your problem solving.

    There are some other great sessions, and if you will be in London on July 15, 2011, register for the event.

    The City of Angels

    sqlcitylaWe’ll do it again on Oct 28, 2011 in Los Angeles, just before DevConnections. Brad, Grant, and I will be in the City of Angels for a Friday event and probably heading out to enjoy a few drinks that evening.

    The agenda is similar, though Mladen isn’t making the trip across the water  (lucky guy). Instead we have MVP and MCM Denny Cherry, expert on hardware, virtualization, and security in SQL Server joining us to talk encryption and how you should protect your data.

    This event will be at the Skirball Cultural Center in the Santa Monica mountains. A great location, and one I’m looking forward to visiting.

    If you’ll be in LA, please register and come see us.