Voice of the DBA

Author: way0utwest

  • The Exciting World of Data

    I was honored to have the chance to give the keynote at SQL Saturday #520 in Cambridge this year. This was a quick keynote, and it was fast. I didn’t record it, but people seemed to enjoy it, and I decided to share some of my thoughts on the Exciting World of Data, the title of the talk.

    We love data. At least, I do. It’s a way of learning more about the world around us, describing it, modeling it, understanding it, even enhancing it. And the world of data is changing. Size is increasing. We’ve moved from bits to bytes, to kilobytes to megabytes to gigabytes to terabytes, just in our hands. We can’t even really conceive of what this amount of storage means in a physical sense. Our large systems have grown to petabytes, perhaps exabytes and zettabytes one day and eventually to yottabytes and beyond.

    We have to transfer data quicker as well. My first modem was 300 baud Hayes Smartmodem. This was at university, where I could watch the text crawl across the screen. From here I had a few upgrades and standarized on 28.8k for quite some time before moving briefly to 56k speeds. When I started SQLSeverCentral, I had an ISDN line in my house. I’ve configured T1 lines at work, and upgraded to faster DSL and Cable routers at both home and work. I never worked in the OC space, but some of you may have transferred data across OC-256 or even OC-768 lines.

    Our mobile data moved from SMS to GPRS to Edge, where with a Sidekick, where I could actually type real messages on a keyboard. When I got to 3G, I thought was all the speed I’d need for a long time. I think I spent 2 or 3 years with an iPhone 3GS. However, like many of you, I upgraded to 4G and LTE, which are amazing speeds, faster than many of the early networks I had at work. We’re testing 5G and 6G and maybe we’ll keep going to subspace radio? Who knows.

    Here on earth, we move more data in our systems. Some of you may have worked with tape storage. My first PC had a tape drive. So I was quite pleased to get a floppy disk drive, first 5.25″ and then 3.5″. I thought we’d have those forever, but I’ve migrated to hard disks to solid state disks to 3D drives. I think 3D SSD technology is going to fundamentally change the world, with latencies that will require our software to be very, very efficient.

    Our interfaces have improved, to allow us to move more data, quicker. From SMD to ESDI to ATA to IDE to SATA to SCSI to Wide SCSI to Fast SCISI to Fast Wide SCSI to Ultra SCSI to Ultra Wide SCSI. SCSI 2 to SCSI 3 to Fibrechannel, infiniband and beyond. USB to Firewire 400 to USB 2 to Firewire 800 to USB 3, 3.1, eSata, Thunderbolt, Thunderbolt 2, Thunderbolt 3, and what’s next? Who knows?

    Our computers used to be the room, but we moved to minis, with the computer in the room. Then we got desktops and portable luggable machines, moving to laptops that we can carry one handed to handhelds computers in our pockets. We even went to tiny devices that we found were too small. So we’ve gone the other way with smartphones and phablets and iPads and tablets. Soon the small things will be larger and the world  around us will become enhanced with virtual reality and Hololens. Maybe.

    Our world is using all this technology to monitor, mark, chip, tag, record, watch, measure, and gather data. We get to work with that data. We get to gather, store, manage, index, backup, transfer, clean, and care for all that data. We need to work with it. We’ve got to move it with text files, CSVs, Excel, Word, PDF, MP3, MP4 and more.

    We send data over TCP, FTP, SMB, AirDrop, VPN, Web services, REST, jQuery, and more. We share data with files, messages, texts, clicks, likes, tweets, pings, drops, shares, snaps, hangouts, and once in awhile, we communicate with phones.

    What do we do with all that data? Why, we can do anything. We have PowerPivot, Power query, Power View, Power map, and Power BI. It seems Microsoft really believes data has power.

    We have the chance and potential to build amazing visualizations. We can analyze our business progress, producing tables, charts, graphs, animations, and of course, reports. We can map our own activities and events, tracking how we interact with the world, experience it, perhaps even using the data to relive, remember, or reinvent the world around us.

    But, we have so many things to learn in order to reach our potential in working with data. Fortunately, we’ve got all sorts of resources to help us, no shortage of books, articles, blogs, podcasts, tutorials, classes, and most impotantly, friends. I hope you take advantage of the resources to learn more.

    And you can start today.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 6.8MB) podcast or subscribe to the feed at iTunes and Libsyn

  • Finding the Attribute

    I was playing with some Extended Events recently. If you haven’t tried, I’d encourage you to do so. However, working with XML is not my favorite. I know I can get the GUI in SSMS 16.x to show me events, but I sometimes want to query.

    Here was my quick adventure in XML and XQUERY. I should know this stuff better, but I think I’m working with XML so rarely that I’m constantly re-learning things.

    I had a document like this:

    DECLARE @x XML = CONVERT (XML, 
'<event name="login" package="sqlserver" timestamp="2016-09-28T01:48:31.743Z">
  <data name="is_cached">
    <value>false</value>
  </data>
  <data name="is_recovered">
    <value>false</value>
  </data>
  <data name="is_dac">
    <value>false</value>
  </data>
  <data name="database_id">
    <value>1</value>
  </data>
  <data name="database_name">
    <value>master</value>
  </data>
  <action name="username" package="sqlserver">
    <value>PLATO\Steve</value>
  </action>
  <action name="session_nt_username" package="sqlserver">
    <value>PLATO\Steve</value>
  </action>
  <action name="session_id" package="sqlserver">
    <value>60</value>
  </action>’

    There was more, but this is fine. I had a query someone else sent me that looked like this:

    SELECT 
[message] = @x.value(
                     '(event/data[@name="database_name"]/value)[1]',
                     'nvarchar(250)'
                     )

    That’s fairly simple, but what I really wanted was to get an attribute at the top. In the “event” node, I wanted the “name” attribute. I can go from the query above to that, right? I could have dug into XQUERY, but I’ve found it logical in the past, so I thought I could actually figure this out.

    I know that the path was just event, and I needed to get the attribute from that. I tried this:

    SELECT 
[message] = @x.value(
                     '(event[@name="name"]/value)[1]',
                     'nvarchar(250)'
                     )

    That didn’t work. So I modified things to

    SELECT 
[message] = @x.value(
                     '(event/name/value)[1]',
                     'nvarchar(250)'
                     )

    No go.

    Hmmmm. What do I need to do? I decided to Google a little and saw a note that the attribute is accessed with the @ symbol. OK, so I need to provide that as the path.

    SELECT 
[message] = @x.value(
                     '(event/@name/value)[1]',
                     'nvarchar(250)'
                     )

    Still no good, but then I removed the value.

    SELECT      @x.value(
                '(event/@name)[1]', 
                'nvarchar(250)'
               )

    That was it.

    XPATH and XQUERY make sense once you get the rules, but they’re still annoying to work with. I’ll be trying to work with the GUI in SSMS as much as possible with XE.

  • The Quiet Zone

    I’ve been in a data center when most servers turned off. I’ve actually heard dozens of systems powered off quickly, and it’s a strange sound. You become so used to the white noise of numerous fans that having them turned off is a little unnerving. It’s neat when it’s a scheduled patch day and all servers cleanly shut down together. It’s an altogether different experience when there’s an unexpected issue and management sees their expensive hardware not working.

    However, imagine losing your servers because of a loud noise. That’s what happened to ING Bank when a fire extinguishing test caused a number of hard drives to fail. To be fair, the loud noise was north of 130db, which is very loud. Since sound is really vibration, the impact to read/write heads caused numerous failures. The bank needed 10 hours to restart systems in their DR center, and managed to do so. While that might not have been what the bank officials wanted, this is a good DR test, and I hope they learned a few things that might help to fail over much quicker in the future.

    This might be a good reason to think about SSDs, which are less susceptible to vibration than the older, spinning rust drives. I’d guess that there are other issues that could affect SSDs and someone is going to discover them at an inopportune time. Already we’ve seen dramatic improvement in SSD technology, driven by numerous early issues relate to writes and reliability.

    Engineering facilities is hard, and there can be many unexpected issues. I’m sure the people that designed the fire suppression system weren’t concerned about the noise; they were concerned about shutting down flames quickly. I’m sure that the people filling the system didn’t think a little extra pressure would matter. These seemingly innocuous decisions can cascade, which is why we practice and preach DR preparation. Not just backups, but restores and quick fail over.

    If it’s not your organization, it might be humorous rather than stressful, but you never know what design flaws might lurk inside your facilities. I once worked in a data center that had only about half the cooling that we expected. Why? The engineers assumed that since we worked an 8 hour day, so did the computers, which we’d turn off at night. Luckily they had built a pad into their calculations so we were only short half the capacity rather than two thirds.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.7MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • What is the Future of Monitoring?

    I read Tom LaRock’s post on the future of monitoring, looking at the rise of machine learning and complex algorithms to evaluate systems. Tom notes that the data input into a system is crucial in determining whether the system can produce a prediction or output that has value. Since the humans that decide what data is included may not choose wisely, the system won’t necessarily reduce the time required to manage a system.

    I tend to agree. From my conversations with those people having success with machine learning systems, the data preparation is the most critical (and difficult) step. Shortcut the cleansing and organizing, or make poor choices of what data to include and you won’t likely get actionable results.

    However, I have a different view of monitoring. After my watching of the Darpa Grand Challenge (editorial on this recently), I think the future of monitoring is perhaps actually making changes to code in-line. Perhaps with approval from humans, but I suspect that we can train some process to understand how particular batches can cause issues. We can certainly set filters that might note index changes on large tables could be problematic and should be investigated before an issue arises. We can teach a system to recognize code deployments, perhaps even roll back certain changes if the application fails. We certainly could have a machine learning system watching index usage and query plans to recommend indexes in a real time manner, perhaps even turning on and off on-line rebuilds.

    I think there is possibility, but where I’d really like to see advanced monitoring is not in production environments. I’d like to see better systems that can watch development, helping suggest or rewrite SQL before it’s deployed. Perhaps applications can warning developers of potential performance or security issues. Such systems could help us in building more consistency into our applications. We can have systems that help our developers code better.

    As our databases and software become more critical to the functioning of most of our businesses, we certainly need more reliable and robust development practices.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.6MB) podcast or subscribe to the feed at iTunes and Libsyn