Voice of the DBA

Author: way0utwest

  • Intelligence from Data

    There is an incredible amount of data in the world, and all that data is changing the way industries work. That’s the opening to a keynote talk from Jim McHugh at the O’Reilly Artificial Intelligence conference. The talk is short, 12 minutes, and interesting to listen to as Mr. McHugh looks at autonomous cars and healthcare, talking about the impact of artificial intelligence on advancing these industries. There are examples showing how data and AI systems are already being used to change the way the transportation and medical fields can work.

    Whether you want to see more robot help in our world or not, I suspect some level of this is coming, and it’s being driven by data. We have more and more data, and as companies have success in analyzing this data with various types of AI and machine learning systems, there is pressure for other companies to join the trend and build their own systems. We certainly see that with the push from Microsoft that emphasizes the R Services in SQL Server. At the recent Data Science Summit, there was a demo in the keynote (around 17:00) of over 1 million classification queries per second running inside SQL Server. You can even try this yourself on SQL Server 2016 Developer Edition (for free).

    I’m sure that a few of you will start to get more complex analysis projects inside of your organization. Maybe you’ll help develop some sort of prototype, or maybe you’ll just be responsible for helping get the data to the data scientists. I’m also sure that some of you won’t be thrilled with the results. After all, throwing a bunch of data at a few algorithms and expecting some rapid development isn’t likely to work great.

    At least not the first time.

    One of the thing I’ve seen from many people as I study data science, machine learning, and related topics is that this isn’t a simple process. Building a useful and successful machine learning system requires experimentation, and really, ongoing experimentation, as you examine, clean, discard, and make decisions on your data. In fact, the data preparation might be the most difficult and time consuming part of the process. That’s great, since many of us are the people that will work with the data, but it’s bad in that our management might not want to have the patience to experiment, evaluate, and re-tune their systems, much less wait for data to be well prepared.

    I do have high hopes for many complex problems to be assisted with machine learning and artificial intelligence in the future. I’m glad that companies are experimenting, and I think it’s great that so many data professionals are getting excited by the possibilities. Remember that this field is hard, and requires lots of work. Keep learning and growing your skills, and above all, remember that the scoring against your data is more likely to be closer to a baseball game than a bowling match. A 30% success rate might be amazing and those perfect games are likely very close to impossible.

    Steve Jones

     

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