Tag: syndicated

  • Full-Text Search – Word Breakers and Stemmers

    There are numerous components to the Full-Text Search (FTS) subsystem in SQL Server that help provide efficient, relative answers to queries. Full-text Search is a little complex, and as I’ve been working with the system in an effort to learn more about it, I decided to document how a few things work.

    Word Breakers and stemmers are two interesting parts of the FTS system. They deal with certain language specific operations that help searches work better. They are related as the two items are loaded together if you use third party word breakers and stemmers.

    I haven’t seen third party word breakers, but some work from other products. As an example, here’s a post to load the Greek FTS search word breaker and stemmer from Sharepoint server if you are on SQL Server 2008. It’s included in SQL Server 2012.

    Word Breakers

    Let’s start with word breakers, which do just what the term implies: they break words. It would seem to be obvious that spaces are the word boundaries, and they are in English, but not necessarily in all languages. There are also the issues of characters in Asian languages like Japanese and Chinese. You can’t count spaces as the word boundaries on those languages.

    Word breakers use the lexical rules of the language to determine word boundaries. Essentially they find what the words are, and then further action can be taken in building the FTS index or processing the query.

    The “words” that the word breaker spits out are seen a “tokens” to the FTS index, and each can then be processed by stemmers, stoplists, thesaurus, etc.

    Stemmers

    Stemmers are an interesting part of the full-text search system. They remind me of my high school Latin classes, where we had to conjugate words. A stemmer takes a word and generates inflectional forms, or conjugations. The example in Books Online, and an easy one to understand is “run”. There are various forms of "run” that we would want to consider as equivalent when performing a search. For example, you would want to consider:

    • ran
    • running
    • runs
    • runner (perhaps)

    The same could be said for “lay”. That would generate

    • lie
    • laying
    • lain
    • lays

    This is one of the big advantages over the LIKE predicate in that stemmers can match these forms of the word being searched for. The index would relate all of these to the core, base word.

    More

    The Books Online page for Word Breakers and Stemmers has technical information on checking what’s installed, some troubleshooting, language settings, and some drier documentation on what you can do with word breakers, but not a lot of explanatory detail.

    I used a little of the information in Books Online, and some from Pro Full-Text Search in SQL Server 2008. You can read more, but unfortunately I haven’t found a lot more documentation on the details of how things work.

    You would probably learn more if you write your own Word Breaker and Stemmer, and there is a sample in the Windows SDK to get you started, but that’s beyond what I want to do.

  • sp_Blitz, v16, on SQLServerCentral

    Brent Ozar Unlimited released v16 of this sp_blitz script, which is designed to run on a SQL Server instance and give you a quick, comprehensive report of the state of the instnace. It’s a great script, and if you have the chance to see Brent present on the script, go see it. It’s a great show and a good explanation of why you might want this type of script.

    I haven’t had to manage the SQLServerCentral instances, but I decided to run this and see what jumped out at me. I started a series at SQLServerCentral where you can follow along with my results. The first article went up today.

    • Security
    • Reliability
    • Performance
    • Query Plans
    • Backup
    • Global Trace Flag
    • High Availability
    • Informational
    • Non-Default Server Config

    You should think about auditing your servers periodically with something like the sp_Blitz script, which will allow you to ensure your servers are running at peak performance.

    Administering your instances is an ongoing job. It’s not a time intensive daily job, but it’s something you should be doing periodically on all your instances, with monitoring setup on your servers to let you know when things might be causing problems.

  • A Learning Experiment

    I’ve been looking for ways to improve the way I do my job, which is mostly writing and editing, but at a higher level it’s teaching people. That was the goal of SQLServerCentral, to help people learn about SQL Server and become better at their jobs.

    Last summer I was looking at a few of the other online learning techniques, and decided to give one a try. I signed up for a Single Variable Calculus class at Coursera. It was delivered by Professor Ghrist from the University of Pennsylvania. There were some problems getting started, and the course was delayed from August until January. I had forgotten about this during the busy fall, but the first week of January I received an email that the class would start on Jan 7, and sure enough, I got a welcome email with a note to start.

    The class is set up as you might expect for a college class. I received a syllabus, some introductory remarks (8 minutes, about what I’d get in class), grading instructions, and an initial exam to see how prepared I am. I’ll be starting this week, setting aside a little time a few nights a week to work through this.

    I thought this would be a good class to understand the delivery mechanisms and structures as I have had 7 or 8 semester of calculus, though that was over 20 years ago. I’m hoping this won’t take too much time, but it will give me a chance to review some skills I’ll need to help my son in a few years, and it should be fun. I like math.

    I’m also looking for ideas that might help me build some better features for my regular job.

  • T-SQL Tuesday #38 – Standing Firm

    tsqltuesdayIt’s T-SQL Time again, and this time it’s #38, from Jason Brimhall (b | t | li). The topic this month is Standing Firm. You are supposed to look at any of these words, and write something that fits with one of them.

    • resolve
    • resolution
    • resolute

    This is the monthly blog party, with each month hosted by someone in the SQL Server community. To participate, just write a blog post on the top, include the logo, and link to Jason’s invitation.

    If you’d like to host, contact the founder, Adam Machanic (b | t).

    Standing Firm

    I decided to write about being Resolute.

    res·o·lute

    /ˈrezəˌlo͞ot/

    Adjective

    Admirably purposeful, determined, and unwavering.

    (from the Googles)

    One of the things that I’ve found in my career is that so many people are unwilling to stand up for what is important to them. They’ll argue points, they’ll debate about what is better or worse, but ultimately they are often afraid to make a stand for things that are important for their own personal well being. It’s not even the things that they feel strongly about that they give in to, often it’s subtle pressure from managers, from peers, or from the community to act, say, or do certain things.

    In short, they can’t say no.

    Most of us like to please others. We want to do a good job. We want others to like us, and we want to solve problems. We want to get things done and be seen as a positive part of the community.

    That’s good, and I admire that. I also know it’s not sustainable. As humans, we need flex in our lives. We need some down time to compensate for the busy times. We need practice time in between learning times. We need balance.

    One of the hardest things I’ve learned to do in my career is say no. I learned it’s important from Andy Warren, and this part year I’ve finally felt like I can comfortably say no. I can say “no” to more work. I can say “no” to another opportunity for a client or job. Most importantly, I can say “no” to myself, when my goals, ambitions, and desires greatly impact my family, my wife, or my opportunity for downtime.

    I would urge you to learn to say no. It’s harder than you think, and it takes practice. However learning to stand up for the balance in your life is important to your long term career, health, and enjoyment of life.