Voice of the DBA

Tag: sql server

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

  • 2013 Goals

    SQL books
    Are you tackling any of these subjects in 2013?

    It’s the last Friday of 2012. If you’re reading this, then I assume the world hasn’t ended and you are planning on starting the next year next week. This is the time of year where we often look forward and make resolutions with the best of intentions. Most people don’t keep many of their resolutions, though I’m not sure if this is because they aim too high or too far from their current path.

    As this year ends, I’d like to ask you to think smaller, and think about your career. For the last poll of 2012, I wanted to ask this:

    What’s your goal in 2013?

    For your goal, I’d like you to think about one thing that you’d like to improve in your skill set in 2013. Is there one area of improvement that you’d like to tackle across 2013? I’m sure there is. SQL Server is a huge platform, and we all have holes in our knowledge.

    With that in mind, I’d like you to think about something you wish you could do. Perhaps you want to understand how a tally table works, or you’d like to be able to get the versions for all your instances using PowerShell. Maybe you want to tackle something like The OLAP Sprint and improve your BI skills.

    Choose one area, and then make a goal. Give yourself a concrete project to tackle. The project shouldn’t be large, and it shouldn’t take the entire year to complete. Drop a note in the discussion and then set an appointment for yourself that repeats every week, every two weeks, or every month and work on the project. Next year I’ll ask you if you actually accomplished your goal.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • The Daily Routine

    Steve and a horse
    This can be my daily routine some days

    This editorial was originally published on Mar 10, 2008. It is being re-run as Steve is on holiday.

    If you work in an operational area, especially if you’re a DBA, you typically have a fairly tedious routine. You definitely have work, but it’s often the same types of things over and over. You are checking logs, ensuring backups work, testing restores, tuning servers, and more.

    Now this type of work can be fairly boring and many people don’t really like it. They don’t handle the operational routine that well over time. Most often I’ve found that database people would rather be on the development side, solving problems, working on new things, and exercising their brains a little more.

    However I really like the operational area and I’ve been much better at it in my career than the development area. I like the routine, much like the daily routines that I have around the ranch when my wife is out of town (check out the podcast for a look at some of what I do). I have to feed horses, muck out stalls, clean up the barn a little and more.

    When I was a production DBA, I had to handle daily things on a regular basis, and it becomes somewhat tedious. However it’s not always boring, just like it’s not boring around the ranch.

    Last year we had a horse get kicked and hurt by another horse. I wasn’t really sure what to do other than call the vet, which is something my wife has decided I’m qualified to do. When he came over, he wasn’t sure what was wrong, but he had an idea and the first stage was to lock up the horse, give him a few days to rest and re-evaluate. However along with that rest (and feeding/cleaning/etc. in the stall), I had to give the horse aspirin.

    Now it’s actually less fun than it sounds. Just like you probably wouldn’t want to eat (as in chew) any aspirin from the bottle, horses don’t like the pasty stuff we give them. So you have to force it.

    And it’s not like forcing your dog to take a pill. I have a 130lb dog and I don’t think twice about forcing her to take medicine, but the thought of forcing a 1200 lb animal is something else. Especially when they’re taller than you!

    The vet laughed at me a little (and my wife a lot over the phone), but shower me how to bridle the horse, hold it’s mouth and then stick in the tube and squirt some aspirin in the horse’ mouth. Not much fun the first time, but it got easier.

    Managing a crisis and learning new skills is definitely part of the DBA routine. If you can remember the first time you’ve had a production database fail and you had to restore it, you know what I mean. Hopefully not a daily part, but it can, should, and likely will, happen to you.

    A daily routine however easy, does require a balance. You have to remember that there is life outside of work and that you have to get away and recharge. You have to break things up and remember what else you like in your life besides your career. You also sometimes have to let some tasks go for awhile to get that break.

    Because if there’s one thing I’ve learned in a couple decades of working, it’s that the work never ends.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

    Overall RSS Feed: or now on iTunes!

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    I really appreciate and value feedback on the podcasts. Let us know what you like, don’t like, or even send in ideas for the show. If you’d like to comment, post something here. The boss will be sure to read it.

  • The Load Poll

    Hopefully this isn't the load you see on your servers, but do you know what level of tps would cause this?
    Hopefully this isn’t the load you see on your servers, but do you know what level of tps would cause this?

    I was taking to someone recently and this person had a large transaction load on their SQL Server. At least, I thought it was a large load. This person said they experienced 1000tps most of the time and this made maintenance, DR planning, and more a challenge. That’s a lot lower than the TPCe performance system that has 4,614tps, but it’s nothing to sneer at. That feels like a significant load to me.

    However it’s been awhile since I worked on a variety of systems as a consultant. I often see the forums at SQLServerCentral showing only tens of transactions/sec, based on the performance data that you can see at monitor.red-gate.com. This is the actual data from our servers and it seems like a low number to me.

    This week I was trying to decide what 1000tps really means. Is this a busy system, maybe even an edge case if this is the average for your system over a week or month? Is it a representative system for many companies? I wanted to see what others report from their systems this week:

    What are your average and peak tps for most of the systems you manage?

    I’d like to hear about 1 or 2 systems that you consider to representative of the loads in your environment. Taken across your business time, whether that’s 9-5, 24×7 or something else, can you get the average and peak values for your transactions/sec and disclose them? You don’t need to let us know about your application or company, though industry would be nice.

    I’m curious how busy systems are for some of you and whether this presents you with challenges for migration, upgrades, virtualization, DR, maintenance or more.

    Steve Jones

    The Voice of the DBA Podcasts

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