Voice of the DBA

Author: way0utwest

  • Renewed

    I received notice that my MVP was renewed today, making this my sixth award. Honored to be recognized by Microsoft again.

  • The Year 2013

    My predictions for 2013.
    My predictions for 2013.

    Today is the first day of a new calendar year. I’m hoping most of you have the day off, and I certainly do, but I wanted to ring in the new year with a look forward. What will happen in 2013? I think it’s incredibly hard to predict, but I’ll take a chance and make a few predictions.

    Mobile devices will continue to grow in popularity. No great surprise there for many of you, but I think that we will see more smartphones sold than regular phones in 2013. With a large market of used devices and the constant push of new devices, new form factors and sizes, I truly think that we will be in a very data centric world most for most of the time in 2013. Between tablets and laptops sporting cellular connections and pocket sized smartphones, most of us (including non-technical people) will have some type of data consumption device in our lives.

    What does this mean for data professionals? I don’t think it matters which platform sells the most phones because all of them will be looking for more data-drive applications. Whether these are apps, HTML5 websites, or something new, all of us that manage data will have to deal with more and more mobile delivery of information in 2013. That means security, especially XSS and injection attacks, will be more of a concern. Good backups, warm copies of data in the event of vandalism, and comprehensive auditing will be something we need to focus on implementing.

    I think we’ll continue to see more and more BYOD at work, though with the nature of security being what it is and constant data loss, there will be a push to work through more virtual interfaces like RDP. It won’t succeed and we’ll have more data breeches in 2013, including some big ones as Verizon as predicted. Encryption will become a bigger battle in 2013, with more companies asking for personal devices to implement encryption. I’m not hopeful that this will become the norm

    Lastly, I’ll predict that we see a change for SQL Server. I think that some of our instances will move away from SAN storage pools and move to dedicated SANs or perhaps back to local storage. We will get more SSD storage implemented in new system, perhaps the standard becoming 2-4 SSDs for new hardware. I think flash is here to stay, at least until high speed optical storage becomes economical.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • Full-Text Search – Stoplists in SQL Server

    Full-text search is an interesting subsystem in SQL Server. It allows you to implement searches through a variety of text formats stored in SQL Server. This is a one of a series of posts that looks at different facets of full-text search.

    What is a Stoplist?

    A stoplist is a list of stopwords that SQL Server should not include in a full-text index.  These are words that are seen as not adding any value to the full-text index. We typically see these words as important in language for structure, but not for content. Examples of stopwords are:

    • the
    • a
    • an
    • is
    • are

    In previous versions of SQL Server, these were also known as noise words and a noise word list. You can read about the topic in Books Online.

    How are they used?

    Stoplists are used when building the index. The words that are contained in the text, and also in the stoplist are ignored and not populated inside the index. This makes for a smaller index, and it also means that the stopwords are not

    The position of these words in the text being indexed, however, still do matter. This is to be sure that searches using proximity are still correctly carried out.

    Each index can have a specific stoplist associated with it. You can associate a stoplist at index creation time, or alter the index later to add or change the stoplist.

    Creating a Stoplist

    For each language supported in the full-text system, there is a stoplist installed with SQL Server. These are the commonly used words that should be ignored for each language.

    You can, however, create your own stoplist of word with the CREATE FULLTEXT STOPLIST command. The creation can be for a new stoplist, or you can copy an existing stoplist.This includes system stoplists, which you can use as a basis for your custom stoplist. The commands are simple, and they are well documented in BOL.

    To add or remove words from a stoplist, the ALTER FULLTEXT STOPLIST command is used with the ADD or DROP parameters. Alterations to a stoplist must be for a specific language, which is specified with the LCID or name of the language.

    Practical Points

    The stoplists are important for limiting the size of the stoplist and making a more efficient index. Full-text indexes are very efficient and scalable in SQL Server, but the less data that needs to be indexed and searched, the most efficient the system will operate.

    System stoplists works well for many natural language searches, but are not necessarily adequate for domain specific searches. For example, if I were indexing all white papers on SQL Server, I might want to ignore extremely common words or phrases that are in all documents. For example, I might consider “SQL” to be so common as to be useless in searches. Rather than bloat the size of the index with this word, I may add this to a stoplist for the full-text index and assume it’s a word like “the”, which I would not use for searches of these documents.

    I haven’t necessarily found a reason to use custom stoplists in the past, but if my full-text index were extremely large or I had a large volume of searches, I might consider using stoplists to prune down my indexes.

    If you have used these in your system, I’d be interested in knowing the reasons and effects.

  • 2012 in Review

    It was an interesting year, though not quite this interesting.
    It was an interesting year, though not quite this interesting.

    It’s the end of 2012, but not the end of the world. As I look back at this past year, I think it’s been a great year for SQL Server and data professionals. The big news of the year was the release SQL Server 2012. This is a great step forward for the platform with a number of enhancements that both make our jobs easier, as well as challenge our skills. The addition of AlwaysOn provides a number of great new ways to scale out SQL Server as well as ensure high availability. Power View, columnstore indexes, and the BI Semantic Model allow for better BI applications, and who could applaud the undo/redo features in Integration Services. There are lots of great things in this new release for the technical staff, though the licensing changes might cause some companies to reconsider or delay their upgrade plans.

    We had more opportunities to use SQL Server in new ways, many of them with the expansion of cloud services. Amazon added SQL Server to its RDS platform, allowing quick and easy deployment of SQL Server in the AWS cloud. SQL Azure lost its name, as it was folded into Windows Azure, but it was enhanced throughout the year with a number of releases.

    SQL Server is still one of the most secure database platforms available, though it did require patching for a critical flaw in an ActiveX control. There have been fewer advisories and issues with SQL Server 2008 and later than any of the other RDBMS platforms. That’s good news as security continues to be an ongoing challenge for many organizations. However the security of the platform doesn’t mean that DBAs can relax. There has been no shortage of data loss in 2012, mainly though laptop loss or insider actions. Everyone should continue to be vigilant about security, auditing, and especially SQL Injection.

    The positive side of 2012 were lots of events for SQL Server professionals. There were 79 SQL Saturday events in 2012. That’s amazing to me and it’s something Andy Warren and I never would have imagined when SQL Saturday #1 took place in Orlando in 2007. A lot of the success is due to Karla Landrum’s work in helping organize the events. Many thanks and congrats to Karla for an amazing job.

    I only attended 4 of these because I participated in the SQL in the City tour that Red Gate Software put on. We held 8 fantastic events in the US and UK this past year, and we will put on a few events next year as well. Grant Fritchey and I will also be visiting some SQL Saturdays in 2013, and I hope to see some of you there. Along with Connections, SQL Server Live, the PASS Summit, and a number of other free events, there’s a lot of great training and knowledge being shared in the SQL Server community. If you’ve got pictures or blogs of your time at an event, please share them in the discussion.

    Here at SQLServerCentral I had a few milestones as well. Our Stairway Series took off and we published over 120 articles on various topics. We crossed over 1.5 million registrations, and receive over a million unique visitors every month, which are fantastic milestones for us. ASKSSC continues to grow as well for those people that prefer a Q&A format rather than a discussion. Based on comments and emails, the site continues to help educate and inform many data professionals on a daily basis. I hope that you have found us to be a valuable resource in your job and I hope we can continue to do so in 2013. If you have suggestions, comments, or critiques, please feel free to share them with us.

    Happy New Year and my best wishes to each of you.

    Steve Jones

    The Voice of the DBA Podcasts

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