Voice of the DBA

Tag: syndicated

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

  • How Many Bytes Are In My Column? – T-SQL Functions

    One of the things you want to be aware of when writing T-SQL is using the proper function for a particular problem. Someone posted a question asking about why they were getting a 0 for this code:

    SELECT Mychar
    , '''' + mychar + ''''
   FROM dbo.MyTable

    That gave me these results

    mytable1

    I used the quotes in order to show that one of my columns has spaces trailing in one of the columns. I noticed that the poster was wondering why they had these results?

    SELECT Mychar
    , LEN(mychar)
   FROM dbo.MyTable

    mytable2

    In the table, clearly there are 4 characters for the row with “4D” and 5 characters for the next row. However the length is being returned as 0. If you were planning on testing for blank strings, or using some substring function, this could be an issue.

    The reason is simple. LEN, as noted in Books Online, ignores trailing spaces. The description of the function is: Returns the number of characters of the specified string expression, excluding trailing blanks.

    So if you have a space at the end of your string, or just a string of spaces, you don’t get the correct length. What should you use?

    Datalength – This function is designed to show the number of bytes used by the string, not the characters. Code shown below:

    SELECT 
   MyID
 , '''' + mychar + ''''
 , LEN(mychar)
 , DATALENGTH(mychar)
   FROM dbo.MyTable

    mytable3

     

    A good thing to be aware of if you are writing string test routines. LEN is the function I know most people use, but it is somewhat flawed, IMHO, in T-SQL