SQL Server Full Text Search – The ObjectPropertyEx Function

It can be quite useful to have a query that will list all of the tables in your database that currently hold full text indexes. Fortunately there is a function we can use, ObjectPropertyEx. We’ll combine it with a system view named sys.tables which gives us a list of all the tables in the current database.

Note, for todays example I will continue to use the AdventureWorks database, AdvWorksCatalog, and index I created in yesterday’s post on the FullTextCatalogProperty function.

ObjectPropertyEx takes two parameters. The first is the object ID of the table we want to work with. That’s why using sys.tables view is so nice, it already containts both the name of the table and it’s object ID. The second parameter is the name of the property we want to get, in this case TableHasActiveFulltextIndex will tell us if the table has an FTS (Full Text Search) index or not. Here’s a query that will list all of the tables with a full text index:

select t.[Name] as TableName

    , ObjectPropertyEx(t.[object_id], ‘TableHasActiveFulltextIndex’) as IsFullTextIndexed

from sys.tables t

where ObjectPropertyEx(t.[object_id], ‘TableHasActiveFulltextIndex’)  = 1

order by t.[Name]

TableName            IsFullTextIndexed
——————– ——————
ProductDescription                   1

This returns a list of the table name, and the value of 1 indicating the table does have a full text index on it. To get a list of all tables, simply omit the where clause. Tables without an full text index will have a 0 for the IsFullTextIndexed value.

We can further extend this function by using a second property, TableFulltextItemCount. This will tell us how many rows exist in the full text index for this table.

select t.[Name] as TableName

    , ObjectPropertyEx(t.[object_id], ‘TableFulltextItemCount’) as NumberOfRows

from sys.tables t

where ObjectPropertyEx(t.[object_id], ‘TableHasActiveFulltextIndex’)  = 1

order by t.[Name]

TableName           NumberOfRows
——————- ————-
ProductDescription           762

In this case the number of rows matches the number of rows in the table.

This is because I have change tracking set to auto, and have a speedy system. Be aware there are times when this number won’t match the row count. This would primarily be when you have change tracking set to manual, have inserted new rows in to the source and have not issued the command to force the update of the full text index.

By comparing the TableFulltextItemCount to the number of actual rows in the source table, you can monitor your system health and activity. If you see the numbers grow ouside the normal boundaries, it can be an indication something is wrong. You can also use it for adjusting the frequency of manual update commands. If the numbers grow apart too much for your comfort, you may wish to make the updates occur more frequently. Conversely, if most of the time the numbers match you may wish to reduce the frequency of manual updates.

It’s not absolutely required to go to the sys.tables in order to get the count. If you already have the name of the table, you can use it in combination with the object_id function to get the count for that one table.

select ‘Production.ProductDescription’ as TableName

    , ObjectPropertyEx(object_id(‘Production.ProductDescription’)

                        , ‘TableFulltextItemCount’) as NumberOfRows

TableName           NumberOfRows
——————- ————-
ProductDescription          762

Using the above logic you could easily create a function to pass in the name of a single table and get the number of rows in the full text index.

As you can see, using ObjectPropertyEx with its two simple full text search related properties will give you some useful functionality for monitoring your system.

SQL Server Full Text Search – The FullTextCatalogProperty Function

There is a useful function built into SQL Server for dealing with Full Text Search Catalogs: FullTextCatalogProperty . It takes two parameters, the first is the name of the catalog, the second is the name of the property you want. While there are quite a few properties in the list, most of them have been depreciated. However, there are still a handful that can provide valuable information.

For todays examples, I am using the AdventureWorks database. I’ve created a catalog named AdvWorksCatalog, using the following command:

create fulltext catalog AdvWorksCatalog as default;

I then created one index, using this command:

create fulltext index on Production.ProductDescription

  ([Description])

  key index PK_ProductDescription_ProductDescriptionID

  on AdvWorksCatalog

  with change_tracking auto

For more info on these commands, see my original series on Full Text Search which began in June of last year. (See the Arcane Lessons page for links to all the lessons).

The first property we will look at is IndexSize. You can call it up with the following command:

select FullTextCatalogProperty(‘AdvWorksCatalog’, ‘IndexSize’)

This will return the size, in megabytes (MB) of the indexes in the catalog. Since we only have one in this example, it is still quite tiny and thus will return a 0 for the result.

Next, let’s say you’d like to monitor the growth of the catalog by monitoring the number of items. To help with that, SQL Server provides a property named ItemCount.

select FullTextCatalogProperty(‘AdvWorksCatalog’, ‘ItemCount’)

In my case, it returned a value of 762 items in the catalog. An item is the same thing as a row in the source table. In my example, a select count from the Production.ProductDescription table also has a count of 762. These match since I’ve only created a full text index on one table in the database. In your system it will be the grand total number of rows for all the tables you have created indexes for.

A similar statistic is the UniqueKeyCount property, produced with this command:

select FullTextCatalogProperty(‘AdvWorksCatalog’, ‘UniqueKeyCount’)

This will tell you how many unique words were found in the catalog. In the Adventure Works database I’m using for SQL Server 2005, the count was 3,195. This would be like doing a select distinct word on the full text catalog. Each word may appear multiple times (once for each row it was found in) but will only be counted once in the UniqueKeyCount total.

The final property that is useful is PopulateStatus. This returns a value from 0 to 9 indicating what the full text engine is doing right now.

select FullTextCatalogProperty(‘AdvWorksCatalog’, ‘PopulateStatus’)

Since I didn’t want to constantly reference the on-line help to decipher the value, I added a little case statement to my SQL and will let it do the work:

select case FullTextCatalogProperty(‘AdvWorksCatalog’, ‘PopulateStatus’)

          when 0 then ‘Idle’

          when 1 then ‘Full population in progress’

          when 2 then ‘Paused’

          when 3 then ‘Throttled’

          when 4 then ‘Recovering’

          when 5 then ‘Shutdown’

          when 6 then ‘Incremental population in progress’

          when 7 then ‘Building index’

          when 8 then ‘Disk is full. Paused.’

          when 9 then ‘Change tracking’

          else ‘Error reading FullTextCatalogProperty PopulateStatus’

        end

There, much more user friendly. I would envision this would be most useful when preparing to do upgrades to the server. You’d want to be sure all full text activity had ceased before doing a server reboot, or applying a lot of SQL Scripts that altered the full text index structures.

In all of these examples, be aware SQL Server will return a NULL if there is an error. For example, you pass in the name of a catalog that does not exist, or the property is incorrect.

SQL Server Full Text Searching – Can you hear me now?

There are times when you might need to know, programmatically, if the Full Text Search component for SQL Server is even installed, and if so is it installed for a particular database. In SQL Server 2008 this won’t be an issue, as (according to the books on-line, and remember this is still an unreleased product) the full text search engine is integrated into the product and thus will always be there. However, in SQL Server 2005 the Full Text Search engine is an optionally installed component. Worse, by default the FTS engine is not installed, you have to explicitly install it.

Therefore, before setting up your app on a new server it would be a wise precaution to run a simple query and confirm the engine is there. It turns out there’s actually two methods for doing so:

select ServerProperty(‘IsFullTextInstalled’)

and

select FullTextServiceProperty(‘IsFulltextInstalled’)

both behave the same, they return a 1 if FTS is installed, if not installed they return a 0. Be aware they will also return a Null if there is an error (for example, you misspelled IsFulltextInstalled). If it turns out not to be installed, there’s not a great deal programmatically you can do to install new software on the server, you’ll have to have a manual intervention on the part of the system admin or DBA.

Once you have confirmed the engine is installed on the server, you should then check to see if the database has had full text enabled for it. Another simple query fills the bill. The first parameter we pass is the name of a database, the second is the name of the database property we wish to examine, in this case IsFullTextEnabled:

select DatabaseProperty(‘AdventureWorks’, ‘IsFullTextEnabled’);

Like the others, it returns 1 if it’s enabled, 0 if it’s not, or a Null if there was an error (for example, passing in a database name that doesn’t exist). Please note the DatabaseProperty does not indicate the presence of a catalog, it merely indicates whether the database has been enabled for full text searching.

Should the database not be enabled (a 0 is returned) you can enable it by calling a stored procedure:

sp_fulltext_database ‘enable’

This will enable full text searching for your current database. You can also call the stored proc with a ‘disable’ parameter to turn off FTS, but why would you want to ! ;-)

By the way, to do most of this you will need DBA level privileges. That’s a logical conclusion, especially if you are calling these as part of a program or set of scripts to create or modify a database.

Installing Ubuntu 8.04 under Microsoft Virtual PC 2007

Update: Nov. 10, 2008 – New blog post on installing Ubuntu 8.10 is now out: http://tinyurl.com/vpcubuntu810

I’m pleased to say that Ubuntu 8.04 is probably the easiest install I’ve had to do with VPC yet! One quick reminder before we begin, when working inside the VPC your mouse will get “trapped” or captured by the virtual computer. You won’t be able to move outside of it. To get it released, just press the RIGHT side ALT key. Left side won’t work, has to be the RIGHT side of your keyboard.

To start with, create a new Virtual PC. For a tutorial, see either my step by step tutorial or the video tutorial if you need more instructions. Since I had the space, I was using 768 meg of ram, and left the disk space at the default of 16 gig. If you can, try and use at least 512 meg of ram for good performance. Use the CD menu option to capture the desktop ISO you downloaded from Ubuntu, or if you have a real CD put it in the drive and capture that. When it launches, you’ll see this screen. (By the way, you can click on any of the screens to see the full size graphic, these have been resized slightly to fit in with most common browser sizes).

[image - Select Language]

Pick your language, I just took the default of English.

[image - Safe graphics mode]

Now press F4 to select an alternate starting mode. When it pops up, change to Safe graphics mode, as you see above, and press Enter. Now pick “Try Ubuntu…” (should already be selected) and press enter. Do NOT pick the Install Ubuntu option, I kept getting VPC errors when trying to install directly.

Additionally, don’t be alarmed if the screen goes black for a while, then you see some garbled graphics. This is perfectly normal, it is just passing through and will be OK when Ubuntu gets done doing it’s thing. It took me about 7 minutes to get from the previous screen to the next one.

[image - live environment]

After it boots you should be in the live session trial environment. Double click the Install icon to begin the install process.

[image - Installer welcome screen]

Screen 1 is just a welcome screen, although you can change your language here if you need to. Press Forward to continue.

[image - Installer Set Time Zone]

Next it wants to know where you are, at least time zone wise. I’m in the central time zone, but set yours appropriately and click Forward.

[image - Installer Pick your Keyboard]

Next you can specify your keyboard. Since I’m using a typical USA style keyboard, I just clicked Forward.

[image - Installer Prepare Disk Space]

Next it asks how you want your disk space partitioned. Since we’re in a virtual environment, it made the most sense to just take the defaults and click Forward.

Be aware, after clicking forward my mouse went into the “I’m busy” mode, and there was a delay while the disks were prepared. Mine went about five minutes. Don’t be alarmed, just wait a few minutes and you’ll then proceed to the next screen.

[image - Installer Who Are You]

On this screen, first supply your name; this will be used in documents and the like. The next text box is the important one – it is for your Ubuntu user name. By default it uses your first name, now is your chance to change it. I rather like mine so will accept it. Next you’ll need to key in a good password and confirm, and finally name the computer. When you are happy, click Forward.

Now is where you may get confused. In the screen above, you are on step 5 of 7. When you click forward, you are suddenly on step 7 of 7. I’m not sure what happened to step 6, I even ran the installer yet one more time just to make sure it was gone. Perhaps it was kidnapped by space aliens?

[image - Installer is Ready]

Apparently even without the missing step 6, the installer has everything it needs. Just click Install to begin the install process. Kick back and wait. Don’t be alarmed if the screen goes black during the process, it’s just the screen saver kicking in. Just click in the VPC and wiggle your mouse and your display will return. I had it kick in several times during the 45 (or so) minutes it took to get everything installed.

[image - Install complete time to reboot]

Eventually Ubuntu will complete it’s install, then give you the above message. On the Virtual PC menu click CD, then release the cd. Then click on the big Restart now button inside VPC.

This was the only real snag I hit in the whole install process, I waited a while and it never did restart on its own. I gave it about five minutes, then in the Virtual PC menu I clicked Action, Reset. I figured since it’s already installed, I wouldn’t lose anything, and I was right.

The boot process does take a few minutes; you’ll see some text then a black screen for about 90 seconds. Then it comes up to the big Ubuntu logo and the orange bar as it loads. You’ll see some garbled graphics for a few seconds, then the login screen finally appeared. I gave it my user id and password, and minutes later I was in Ubuntu.

One last piece of business, fixing the networking. First make sure the network card is mapped to a real network card in your computer. For more instructions on this, see my video, Virtual PC Advanced Settings. After that, click on the network icon in the upper right side of the toolbar, as you see below.

[image - Fix Networking]

Then just pick Wired Network. Once connected you’ll be free to visit your favorite websites!

[image - Ubuntu open for business]

I haven’t had much time to check out other features, or get the sound working so if anyone has a quick fix for that by all means leave a comment below.

Virtual PC 2007 Step by Step – The Advanced Settings Video

As I promised last week, here is the second video in the Virtual PC Step by Step series. This video covers some of the advanced settings available in Virtual PC, and explains what the ramifications are for changing each setting.

If you don’t wish to watch in the flash player, or want the best video quality, I would suggest downloading the 20 megabyte WMV file from this link:
http://arcanecode.files.wordpress.com/2008/04/vpcstepbystep02.wmv

This video is the second in the VPC series, you can find the first one on this post from last week.

I would appreciate some feedback, do you like the video format? Is it useful? I’m enjoying producing the videos, but I do find it takes considerably more time than a regular blog post. Let me know your thoughts in the comments area.

BarCamp Birmingham 2 Presentations

At last Saturday’s BarCamp Birmingham, I gave three presentations. The first was on Virtual PC 2007. For more info on it just look to my previous post, which has the first video on VPC. I’m currently working on the other videos in the series and should have them up this week.

My second presentation was “The Developer’s Experience”. As promised in the session, here’s the complete PDF of my slides: The Developer Experience. This has hyperlinks to all of the tools I presented.

My final presentation was on Full Text Searching on SQL Server 2005.  First, here is a PDF of the PowerPoint slides: Full Text Search Power Points

Next, most of the demos used SQL statements. This PDF file has all of the SQL plus some associated notes. Full Text Search Demo Scripts

Finally, I didn’t get to demo this at BarCamp due to time, but I do have a WPF project that demonstrated how to call a full text search query from a WPF Windows application. Annoyingly enough WordPress (who hosts my blog) won’t let me upload ZIP files, so I renamed the extension to pdf. After you download the file to your drive, remove the .pdf and put the zip extension back on, then it should expand all the source for you correctly. (Yes, I know, I really need to get a host server for binaries, one of these days I’ll get around to it, but for today…) Source for WPF Demo

See you at the next BarCamp!

Virtual PC 2007 Step by Step – The Video

Some time back I wrote a post titled Virtual PC Step By Step, which detailed the steps to setup a new machine in Virtual PC. Since we now have a new version (VPC 2007) I decided it was time to update the post. Only now I’ve done it in video form! Yes, Arcane Code is now producing videocasts for the web. This is the first in what I plan to be a series of video content.

Without further delay, here is the video in flash player:

Or you can download the 640×480 WMV version here (about 8.5 megabytes in size):
http://arcanecode.files.wordpress.com/2008/04/vpcstepbystep01.wmv

This is my first, so let me know how this works for you. Still working out various video editing settings, as well as the many options for WordPress. Your feedback will help to improve future productions.

Follow

Get every new post delivered to your Inbox.

Join 104 other followers