Voice of the DBA

Author: way0utwest

  • Off to the Richmond User Group, a Red Gate Experiment and SQL Saturday #187

    This weekend is SQL Saturday #187 in Richmond, VA. Today’s a travel day for me, as I head out early because Red Gate Software is sponsoring a half day event tomorrow, Friday, where myself and Grant Fritchey (b | t) will be speaking. This is an experiment for us, and I’m hoping it goes well. If so, you might find us doing a number of these events around the country.

    Today I travel, speaking on encryption at the Richmond SQL Server Users Group, then tomorrow at our Red Gate event, and Saturday at SQL Saturday. Then two days off, hanging with my brother, his wife, and my nephews in Maryland before coming back next week.

    A busy few days. Four different talks, on four subjects. Fortunately I’ve been rehearsing this week.

    Hope to see some of you there.

  • Dropping Indexes

    While working on some demos recently, I needed to drop an index for a test. I executed this generic statement for an index I’d just created.

       1: DROP INDEX ix_IndexName

    Needless to say I was surprised when I got this error:

    Msg 159, Level 15, State 1, Line 1

    Must specify the table name and index name for the DROP INDEX statement.

     

    I haven’t done much index maintenance in the last few years, but since I had specified the name, and I expected names to be unique, I was surprised. That’s not the case, however, since indexes aren’t seen as objects.

    I created the index in AdventureWorks with this code:

       1: -- paste in create index statement

       2: CREATE NONCLUSTERED INDEX ix_IndexName

       3: ON Sales.SalesOrderHeader ( [TerritoryID],[ShipMethodID], [SubTotal], [Freight] )

       4: INCLUDE ([SalesOrderNumber], [CustomerID]);

    As a test, I then added this index:

       1: CREATE NONCLUSTERED INDEX ix_IndexName

       2: ON Production.Product ( [Name],[ListPrice]);

    Same name, different table.

    A quick check in sys.objects surprised me.

       1: select *

       2:  from sys.objects

       3:  where name = 'ix_Indexname'

    This returned no results. Hmmm, let’s investigate further. I next decided to check sys.indexes.

       1: select *

       2:  from sys.indexes where name = 'ix_Indexname'

    This returned two results:

    indexes

    Two entries, with two object_ids. I wondered what those objects were, so I ran more code:

       1: select *

       2:   from sys.objects

       3:   where object_id in (1010102639, 1717581157)

    I received the two tables back as the objects.

    indexes2

    This surprised me, though I’m sure I’ve read the details in a book at some point, or even seen the documentation in sys.indexes. The entry for name says it is unique only within the space of the object, which would be the parent table.

    I had assumed that indexes were objects, but they aren’t. They are an attribute of an object, and as such, I needed this code to remove my index:

       1: -- cleanup

       2: DROP INDEX ix_IndexName

       3:  ON Sales.SalesOrderHeader

       4: ;

       5: GO

    Update: As noted in a few comments, you can also drop the index as:

       2: DROP INDEX Sales.SalesOrderHeader.ix_IndexName

    And, of course, I needed to drop my test object.

       1: Drop INDEX ix_IndexName

       2: ON Production.Product

       3: ;

  • Two Plugs Away

    The office at Valve. Notice the wheeled desks.
    The office at Valve. Notice the wheeled desks.

    I’ve always enjoyed smaller environments, where people are more free to work in the way they want to, when they want to, as long as they are productive. I applaud the efforts of small companies to design offices or spaces where employees feel comfortable working and enjoy spending time. Some large companies do this as well and maybe this is necessary to offset the grind of long hours, but I believe the owners and founders of smaller companies often cultivate a friendly, close atmosphere and they hope their people to want to come to work. After all there are plenty of places one can work that will exact and demand long hours, often without any recognition of the hardships or without any additional benefits for your efforts..

    I ran across this piece on the culture and hiring at Valve, a gaming company that makes Steam and Half Life. They have a very free flowing culture, depending on the individuals to make good decisions for the company and their own teams. I’m not sure how scalable this is, or even how easily this can be replicated to other companies. As I’ve watched Red Gate grow over the years, it’s been an amazing place to work, but it’s not without some pain points. We’ve done well, though I don’t know that the ideas from Valve would work there.

    At the bottom of the article, however, I was struck buy this quote:  Everyone’s desk is on wheels. “There are only two plugs that need to be unplugged in order to shift from one team to another.” That’s an interesting way to design an office setup. Your desk is your desk, and you can customize it for you, but since it’s on wheels, you can move it if the need to change arises. That doesn’t happen too often in larger companies, but I do think the idea of virtual teams that come together for projects and then disband would work well in this environment.

    Even in the operational world, the option of periodically moving my desk and changing neighbors without the hassles of packing up lots of stuff, would be interesting. It would also make weekend practical jokes much more interesting. Imagine coming in Monday morning and having no idea where you desk, with all your stuff, might have moved.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Data We Don’t Want

    Filldisk.com might do this to your hard drive.
    Filldisk.com might do this to your hard drive.

    Don’t visit the FillDisk.com site, which I ran across a link to froman Arts Technica article that talks about a flaw in web browsers. It’s possible a security flaw, possibly an availability flaw as well. Apparently the new HTML specification allows for sites to use the Web Storage Standard to keep data on your hard disk. There is a limit in most browsers for how much data you can store per domain, but the FillDisk site uses sub domains to put random junk on your drive. The author of the site built this as a proof of concept and was able to add 1GB of data to an SSD on a laptop every 16 seconds.

    That’s a denial of service type attack that I hadn’t expected, but it is an interesting attack vector. I wouldn’t expect this to impact servers, but if servers are consuming web services, and using controls based on browsers, there is the possibility this type of attack might affect them. I’d hope this were limited to web servers and not impact database servers, but it’s certainly a concern if you have processes running on your database server that might retrieve data from a remote source.

    This makes me want to re-architect the way we build data driven application in the future, to prevent this type of vandalism. Maybe building an application level firewall that proxies all access to a database server. The idea of application servers was very popular a decade ago, but it seems few systems actually implemented this type of architecture. Perhaps this is because the web server/database server pairing is such an easy paradigm to build for most developers.

    Frameworks that allowed separation of the application through a middle layer could allow for caching of data in addition to more security. That could increase performance and scalability as the database wouldn’t be the single bottleneck for all requests.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.