The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

T-SQL Script for SQL Server Statistics Last Updated Time & Percent Rows modified

Posted by Sudarshan Narasimhan on August 4, 2011


I wrote a post on the same topic @ Amit’s Blog. I made some changes to the script to make it easier for DBA’s to read the output. I am re-posting this here for your reference. This can be quite handy if you are managing multiple SQL instances and you have lot of tables or have large databases where you only want to update statistics on some tables based on row modifications (data changes e.g. OLTP). You can always use DBCC SHOW_STATISTICS or SP_AUTOSTATS to find out the same information for a specific table, index or column stats.

select

schemas.name as table_schema,

tbls.name as Object_name,

i.id as Object_id,

i.name as index_name,

i.indid as index_id,

i.rowmodctr as modifiedRows,

(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) as rowcnt,

convert(DECIMAL(18,8), convert(DECIMAL(18,8),i.rowmodctr) / convert(DECIMAL(18,8),(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2))) as ModifiedPercent,

stats_date( i.id, i.indid ) as lastStatsUpdateTime

from sysindexes i

inner join sysobjects tbls on i.id = tbls.id

inner join sysusers schemas on tbls.uid = schemas.uid

inner join information_schema.tables tl

on tbls.name = tl.table_name

and schemas.name = tl.table_schema

and tl.table_type=‘BASE TABLE’

where 0 < i.indid and i.indid < 255

and table_schema <> ‘sys’

and i.rowmodctr <> 0

and i.status not in (8388704,8388672)

and (select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) > 0

order by modifiedRows desc

Keep in mind that Rowmodctr values displayed in above output are not entirely reliable starting with SQL 2005 onwards. But, you can rely on last updated time to make a decision if the statistics need to be updated. Read here for more info on this.

 

 

You can use this script to generate the UPDATE STATISTICS command for each table in your table. I am using FULL SCAN by default, which you can of course modify to use specific SAMPLE. You have to un-comment the comments lines, to actually perform the update.

 

select identity(int,1,1) as rownum,table_name into table_count from information_schema.tables where table_type=‘base table’

declare @count int,@stmt varchar (255),@maxcount int,@tblname varchar(50)

set @count=1

select @maxcount=count(*) from table_count

while @count < @maxcount+1

begin

      select @tblname=table_name from table_count where rownum=@count

      set @stmt = ‘UPDATE STATISTICS ‘+ ‘[‘ +@tblname+ ‘]’ + ‘ WITH FULLSCAN’

      print @stmt

      –PRINT (‘Updating statistics for table :’+@tblname)

      –EXEC(@stmt)

      –PRINT (‘Finished Updating statistics for table :’+@tblname)

      print ”

      set @count=@count+1

      set @stmt=”

End

drop table table_count

Have fun with your database maintenanceSmile. As always stay tuned for more cool SQL stuff.

Posted in DB Maintenance | Tagged: , , , , , | 4 Comments »

SQL Server service does not start after enabling SSL encryption

Posted by Sudarshan Narasimhan on August 3, 2011


I recently had a customer who came up to me with a SQL Service start-up issue. This issue started after they had enabled SSL encryption using a personal certificate. Now, before I begin let me confirm a couple of things :-

  1. The correct SSL certificate has been chosen in SQL Configuration Manager.
  2. The ForceEncryption flag is set to Yes.

There are some forum posts on this issue and I don’t entirely agree with the solutions proposed there, because they did not actually solve the real problem but worked around it without understanding how/why the problem occurred, and how to get a complete solution.

http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/b308d9c8-6aba-4b3b-9b32-f919816d1be2/

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/63c15cb5-0d12-4ea8-bf84-e3ea8a42a866/

I am going to be bold and say this; please do not follow the above posts since they are workarounds. Most people in enterprise environments cannot run SQL Service under the Local System account, which is what the above posts are asking you do to, yikes!!!

As always, the first place to check when a SQL Service does not start is the SQL ERRORLOG (yes, ERRORLOG and NOT the EVENTLOGS – I said 1st place).

For a complete step-by-step guide to approach SQL Service start-up issues, you can read the blog post I wrote on MSDN SQL Server FAQ –> HERE

Here is what I found in the ERRORLOG,

2011-06-08 12:35:30.56 Server Unable to load user-specified certificate [Cert Hash(sha1) “XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX“]. The server will not accept a connection. You should verify that the certificate is correctly installed. See “Configuring Certificate for Use by SSL” in Books Online.

2011-06-08 12:35:30.59 Server Error: 17182, Severity: 16, State: 1.
2010-06-08 12:35:30.59 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.

2011-06-08 12:35:30.62 Server Error: 17182, Severity: 16, State: 1.
2011-06-08 12:35:30.62 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.

So, it appears the certificate chosen for SSL encryption has some issues. Here are the things I checked next:-
1. The certificate itself is valid and has not expired (using certmgr.msc).
2. The certificate hash matched that of the certificate associated with the instance but it wasn’t loading.

The certificate appears to be fine. Well, what next?

The way SQL Server loads the certificate happens through the credentials of the SQL Server service account defined for this instance. So, I started to check what the SQLSvc account was and whether it had permissions to access the certificate. At this point I cam across this forum post for a similar issue

http://social.msdn.microsoft.com/Forums/en/sqlexpress/thread/db68b3bf-33a8-42d6-8dac-f7229a2f9cde

The solution listed there was to ensure that the SQL service account had a valid user profile on the server machine. This profile can sometimes be missing if the service account was changed to a new account, which doesn’t yet have a user profile created. You can verify the user profile on any machine by going to
My Computer –> Properties –> Advanced –> Settings (under User Profiles section).

image

Solution #1 (as applicable)

Identify the SQL service account and log on to the server machine that is running SQL Server, with the service account. During logon, Windows will create a new user profile for the service account.

Unfortunately, this wasn’t the scenario in my case. Another thing I have to caution people is regarding service account change. When changing the SQL Svc account ALWAYS ALWAYS use the SQL Server Configuration Manager, and NEVER do it through services.msc. Here are some reasons why:-

1. Permissions & ACL’s of SQL Server registry keys are done only by SSCM
2. SQL Server User Groups are updated with the new service account only by SSCM.
3. WMI namespace permissions are only set through SSCM.

So, if you changed the service account through services.msc, it is possible that the new service account does not have permissions to access the certificate. The certificate hash is stored in the following registry key. If the new service account cannot access it, then you “can” get the error above, since SQL cannot load the certificate due to insufficient privileges.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL2008\MSSQLServer\SuperSocketNetLib\Certificate

I started to check if this was happening in my case and found that the SQL Service account was not even present in the local group SQLServerMSSQLUser$[Computer_Name]$[Instance_Name]. This confirms that the service account change wasn’t done through SQL Configuration Manager.

This means the SQL Service account does not have permissions to open the private key for the certificate. i.e. SQL Server failed to load this specific certificate due to insufficient permissions. Once we give the service account permission to read the private key, the SQL Server should start up without a problem.

Here is what I did to resolve it,

Solution #2

1. Identify the SQL service account (could be a domain account or any of the built-in accounts like LocalSystem/LocalService/NetworkService).

2. If the service account was set properly using SQL Configuration Manager, then it should have been added to the SQL Security Groups. Look for this on the local machine groups. e.g. SQLServerMSSQLUser$[Computer_Name]$[Instance_Name]

3. Open the Microsoft Management Console (MMC) by click Start -> Run, entering mmc and pressing Enter.

4. Add the Certificates snap-in by clicking File -> Add/Remove Snap-in… and double clicking the Certificates item.

Note:
Select computer account and Local computer in the two pages on the wizard that appears.
Click OK.

5. Expand Certificates (Local Computer) -> Personal -> Certificates and find the SSL certificate you imported or created.

6. Right click on the imported certificate (the one you selected in the SQL Server Configuration Manager) and click All Tasks -> Manage Private Keys.

Note:
You will only see this option for SSL certificates imported from a certifying authority. E.g. you will not see this for EFS, RDP certificates

7. Click the Add button under the Group or user names list box.

8. Enter the SQL service security group that you identified in step #2 and click OK.

You can also directly add the service account itself here, but for any future changes you need to repeat these steps to add that individual account. Instead its better to add the security group, since the service account will always be present here if account change was done using SSCM.

Now, when I started the SQL service you can double-check that the certificate is being loaded and used by looking for this entry in the errorlog. In my case, it started fine and SSL encryption was working.

Open-mouthed smile

2011-06-08 12:48:47.88 Server The certificate [Cert Hash(sha1) “XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX“] was successfully loaded for encryption.


Problem Solved!

Now, to check if the connection is actually encrypted you can use Netmon or Wireshark to check the network packet. Refer to the screenshot posted in this blog on how to do that. An easier way for DBA’s to verify this is to use the DMV sys.dm_exec_connections, look at the encrypt_option column. This DMV also tells you if the connection is using Kerberos/NTLM/SQL Authentication -> auth_scheme column has this value.

Hope this saves you some time.

Posted in Startup | Tagged: , , , , , , , | 19 Comments »

Data Collector (Management Data Warehouse) – Issues, Tips and Solutions

Posted by Sudarshan Narasimhan on August 3, 2011


Somehow I ended up working on data collector issues quite a bit in the last 6 months or so. It’s certainly a useful feature but not an easy one to work with and definitely not an easy one to troubleshoot for you DBA’s. I’m listing down some issues that I faced and solutions for these issues. If you come up with or face any new issues in data collector, please drop a mail to sudarn.

1. Data Collector Upload Job Timeouts

The Data Collector jobs were getting timeout errors on for data upload job ONLY AT THE TIME WHEN PURGE JOB IS RUNNING. You notice that once the purge job completes, the data upload job also starts succeeding, but till that time it simply keeps failing. Here is what you would see in job history for these upload jobs.

02/08/2011 05:25:00,collection_set_3_upload,Error,0,SERVERXYZ\INSTANCEXYZ,collection_set_3_upload,(Job outcome),,The job failed. 
The Job was invoked by Schedule 2 (CollectorSchedule_Every_5min).  The last step to run was step 2 (collection_set_3_upload_upload).,01:00:01,0,0,,,,0

02/08/2011 05:25:00,collection_set_3_upload,Error,2,SERVERXYZ\INSTANCEXYZ,collection_set_3_upload,collection_set_3_upload_upload,,Executed as user: STARWARS\Yoda
The thread "ExecMasterPackage" has timed out 3600 seconds after being signaled to stop.  Process Exit Code 259.  The step failed.,01:00:01,0,0,,,,0

Since we know that the purge job running was the only time when these uploads were failing, we have a simple solution

Solution:
Schedule the purge and upload to run at different schedules. You can use the SSMS UI to define a new schedule for the collection set, just make sure it doesn’t fall under the schedule of the purge job.

 

2. Data Collector Upload Job Deadlocks intermittently

The collection set upload job is running into deadlocks now and then (aka intermittent). This is again related to the purge jobs. Why?

There have been multiple reports of this issue on Connect & MSDN Forums and I’ve had the “pleasure” of talking to customers about this issue. Here are some,

Deadlock in MDW Upload Purge Logs Job Step
http://connect.microsoft.com/SQLServer/feedback/details/539547/deadlock-in-mdw-upload-purge-logs-job-step

Management Data Warehouse Data Collector upload job deadlocks
http://connect.microsoft.com/SQLServer/feedback/details/504557/management-data-warehouse-data-collector-upload-job-deadlocks

Here is a sample output of a failed Upload job that reported the deadlock.

Log Job History (collection_set_3_upload)
Step ID 1
Job Name collection_set_3_upload
Step Name collection_set_3_upload_purge_logs

Message
Executed as user: STARWARS\Yoda. Transaction (Process ID 457) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed.

Solution:

Stagger the two collector jobs. E.g. collection_set_2_upload Server Activity and collection_set_3_upload Query Statistics to run a couple of minutes apart. Now, here is a catch! Don’t change the schedule for the SQL Server Agent job, you need to change the schedule using the collector “pick schedule” option in SSMS.

To do this, right click on the collector agent (not the SQL Agent job) select properties and then uploads in the left hand pane. Click on "New" to create a new schedule. If you do it this way you can create two new schedules that are independent of each other.

3. Unable to change/define Schedules for MDW Collection Sets

I did run into another quirky issue when attempting to define a new schedule for the collection sets. i.e I was not able to define a new schedule for this and kept getting this error.

clip_image002[4]

These are the collection sets that are present by default,

  1. Disk Usage
  2. Server Activity
  3. Query Statistics
  4. Utility Information

Of these, the Utility Information is disabled and does not have a schedule defined by default. The reason for the above error was the Collection set “Query Statistics” mentioned above, did not have a valid schedule UID stored. You can confirm this by doing these queries.

1. In the context of MSDB database, run the following query and note down the schedule_uid value for Query Statistics collection set

select * from dbo.syscollector_collection_sets where collection_set_id=3

e.g output. A575FFD0-98A0-4D0E-B43C-B63482FB5B00

2. Again in the MSDB context, run the following,

SELECT schedule_id from sysschedules_localserver_view WHERE ‘XYZ’ = schedule_uid

                — XYZ is the value obtained from step#1. You will see that there is NO value returned for step#2

 

3. As I mentioned, Utility Info has no schedule by default, so we need to use this to get out of this situation. So I used the Utility Information collection set and defined a new schedule for it for every 10 minutes.

4. Next, I queried the schedule_id for this in dbo.syscollector_collection_sets and used this schedule_id to map to the collection set that was failing.

declare @schedule_uid uniqueidentifier

select @schedule_uid = schedule_uid from dbo.syscollector_collection_sets where collection_set_id = 5 – whichever is the Utility Info collection set ID

exec dbo.sp_syscollector_update_collection_set @collection_set_id = 3, @schedule_uid = @schedule_uid — whichever is the Query Statistics collection set ID (change according to the one failing at your end)

 

5. This fixed the issue with Query Stats and I was able to change/define a schedule for that. But, since we created a schedule for Utility Info and you don’t want that to run, I tried to disable it by setting it to “On Demand”. But this failed. Oops!

6. So I enabled the Utility Info collection set and only then did it create a valid job_id for it, but I got another error when trying to remove the schedule.

7. I stopped the collection and then deleted the job manually. To get things back to old state, I updated the metadata using like this,

UPDATE [msdb].[dbo].[syscollector_collection_sets_internal]

SET [collection_job_id] = NULL, [upload_job_id] = NULL

WHERE collection_set_id = 7 – whichever is the Utility Info collection set ID

8. Now, you can stop the Utility Info collection set and also get the other collection sets schedule changed to fix issue #2 mentioned aboveSmile.

 

4. Data Collector Purge Job (Clean-up job) takes a long time to complete

This is actually the root cause of issue #1 and #2 listed above. The purge procedure is complicated and is responsible for cleaning up the metadata tables of old entries. This work is done by the core.sp_purge_data stored procedure. As a troubleshooting step, I captured the execution plan of the procedure and noticed a missing index recommendation in the XML Showplan.

<MissingIndexes>
  <MissingIndexGroup Impact="92.2913">
    <MissingIndex Database="[MDW]" Schema="[snapshots]" Table="[query_stats]">
      <ColumnGroup Usage="EQUALITY">
        <Column Name="[sql_handle]" ColumnId="1" />
      </ColumnGroup>
    </MissingIndex>

If you were to translate this into a CREATE INDEX statement this is how it would look,

CREATE NONCLUSTERED INDEX [Ix_query_stats_sql_handle]

ON [snapshots].[query_stats] ([sql_handle] ASC)

WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,

DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)

ON [PRIMARY]

 

Now, creating this requires modifying the MDW system table and of course this isn’t supported! This same thing is mentioned in this blog as well and is this blog post by the SQL Server Development Team. Don’t do it!  Patience, I will explain.

 

Here are some facts:-

1. Purge job can get slow on large MDW databases (40+ GB).

2. The DELETE TOP statement on snapshots.notable_query_plan is the one where most execution time is spent.

 

 

Solution:

Like I mentioned earlier, don’t modify system Stored Procedure code unless guided by Microsoft Support. Luckily, for the slow purge procedure a fix has been released in SQL Server 2008 R2 Service Pack 1, which can be downloaded here.

 

This fix updates the Purge procedure TSQL code and the purge has been broken down and re-written in an optimized way. The runtime will come down drastically once you update to SP1. The new procedures doing the purge is called “[core].[sp_purge_orphaned_notable_query_plan]” and “[core].[sp_purge_orphaned_notable_query_text]”

 

Hang on, it’s not over yet!

 

 

AFTER you apply SP1, you will need to modify the Stored Procedure sp_purge_orphaned_notable_query_text as shown below. These changes are required to correct the text of the sp_purge_orphaned_notable_query_text stored procedure because the delete statement incorrectly references the snapshots.notable_query_plan table after you apply Service Pack 1.

 

 

SP1 Code

        — Deleting TOP N orphaned rows in query plan table by joining info from temp table variable

        — This is done to speed up delete query.

        DELETE TOP (@delete_batch_size) snapshots.notable_query_plan

        FROM snapshots.notable_query_plan AS qp , #tmp_notable_query_plan AS tmp

        WHERE tmp.[sql_handle] = qp.[sql_handle]

 

Change this to following once you apply SP1

— Deleting TOP N orphaned rows in query text table by joining info from temp table
        — This is done to speed up delete query.
        DELETE TOP (@delete_batch_size) snapshots.notable_query_text 
         FROM snapshots.notable_query_text AS qt, #tmp_notable_query_text AS tmp
        WHERE tmp.[sql_handle] = qt.[sql_handle]

 

Hopefully, this code change will be included in a future cumulative update post-SP1, so that you don’t have to manually change the code. The same applies for SQL Server 2008 as well, where I am hopeful these changes will be included in a future Service Pack. This fix mentioned above is at present once valid for SQL Server 2008 R2 (as of Aug 2, 2011 when I wrote this). With these, the slow purge issues should be put to bed, once and for all!


UPDATE (August 3rd, 2011)
After working with our KB team, we have published an official KB article that talks about this issue. For all those running into slow purge issues, please follow the resolution given in this KB article,

FIX: Data Collector job takes a long time to clear data from a MDW database in SQL Server 2008 R2
http://support.microsoft.com/kb/2584903

 

Other Useful Links

FIX: The Management Data Warehouse database grows very large after you enable the Data Collector feature in SQL Server 2008
http://support.microsoft.com/kb/970014

Data Collector’s sp_purge_data may run for hours if there are many orphaned plans to purge
http://blogs.msdn.com/b/sqlagent/archive/2011/04/12/data-collector-s-sp-purge-data-may-run-for-hours-if-there-are-many-orphaned-plans-to-purge.aspx

Posted in Data Collector | Tagged: , , , , , , | 8 Comments »

Getting a custom IFilter working with SQL Server 2008/R2 (IFilterSample)

Posted by Sudarshan Narasimhan on July 14, 2011


For those of you not familiar with my past work, I am a moderator of the official SQL Server Full-Text Search blog on MSDN and I recently posted there an article about custom IFilter and SQL Server. I am attaching a sample of the project I downloaded and built from Windows SDK v6.1 which works with SQL Server 2008 and R2.

Background

In this post I will cover how to get a custom IFilter working on SQL Server 2008/R2 (64-bit or 32-bit). For the purpose of this demo, I am using the IFilterSample that is available with Windows Server 2008 SDK.  Once you have installed the SDK you can find the sample here à C:\Program Files\Microsoft SDKs\Windows\v6.1\Samples\winui\wsearch\IFilterSample

The IFilterSample implements a sample IFilter for a fictitious file format called “.filtersample” which is actually an XML file. This sample will work on Windows Vista and Windows 7.

 

Developers out there might already be familiar with using the IFilter interface to develop the filters. For those who aren’t you can start from here,


Windows Search Interfaces

http://msdn.microsoft.com/en-us/library/ee872063(v=VS.85).aspx


Data Add-in Interfaces

http://msdn.microsoft.com/en-us/library/ee872090(v=VS.85).aspx


IFilter Interface

http://msdn.microsoft.com/en-us/library/bb266451(v=vs.85).aspx

 


Building and Setting up the IFilter DLL

I am going to use IFilterSample as reference and must warn you this post is NOT a how-to-write-custom-IFilter lessonJ.


The IFilterSample is a native C++ COM based DLL and as part of its registration provides a CLSID that can be registered on the system. Here are the step-by-step instructions you came here for:-

 

1.       Download and install the Windows Server 2008 SDK on your machine.

2.       Copy the sample from C:\Program Files\Microsoft SDKs\Windows\v6.1\Samples\winui\wsearch\FilterSample, onto your desktop which is the copy we will use to build the project.

3.       Open the FilterSample.sln file and  in Additional Dependencies in Linker Properties, add xmllite.lib

clip_image002[4]

4.       Build the project for x64 or Win32 as appropriate (this would be dependent on the architecture of your SQL Server this IFilter will be used on).

5.       Once the project is built you will have FilterSample.DLL in your Debug or Release folder. Make sure that the SQL Server service account has FULL CONTROL permissions on this folder.

6.      From an elevated command prompt browse to the location of the DLL and register it using regsvr32 FilterSample.dll

clip_image004[4]

 

clip_image006[4]

 

7.       Now we need to create some registry keys in SQL Server to make it use this filter DLL.

a.       The keys I created below are for a default instance of SQL Server 2008, so change the InstanceID as appropriate.

b.      The CLSID given below is specific to this IFilterSample only.

c.       Notice that the file extension I provided is called “.filtersample”.

d.      The path provided has to match the path from where it was registered in step #6.

 

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSearch\Filters\.filtersample]
@="{6C337B26-3E38-4F98-813B-FBA18BAB64F5}"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSearch\CLSID\{6C337B26-3E38-4F98-813B-FBA18BAB64F5}]
@="C:\\Users\\sudarn\\Desktop\\FilterSample\\Release\\FilterSample.dll"
"ThreadingModel"="Both"
"Flags"=dword:00000001

 

8.       Now, connect to the SQL instance as a sysadmin and run the following sequence of commands

 

EXEC sp_fulltext_service @action=‘load_os_resources’, @value=1;

EXEC sp_fulltext_service ‘verify_signature’, 0

EXEC sp_fulltext_service ‘update_languages’;

EXEC sp_fulltext_service ‘restart_all_fdhosts’;

 

9.       To verify the FilterSample.DLL has been successfully loaded and recognised by SQL Server, you can check the DMV sys.fulltext_document_types or use sp_help_fulltext_system_components ‘filter’

 

 

Testing the IFilter

That was all, folks! You can now do a test to verify that the search is working as expected. There are 2 ways of testing the IFilter.

 

1.      Using SQL Server

a.       Create a test table and enable it for full-text indexing.

CREATE TABLE [dbo].[testTBL](

      [doctype] [nvarchar](50) NOT NULL,

      [document] [varbinary](max) NULL,

      [docname] [varchar](50) NOT NULL,

 CONSTRAINT [PK_testTBL] PRIMARY KEY CLUSTERED

(

      [docname] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

CREATE FULLTEXT CATALOG [CatalogTEST] WITH ACCENT_SENSITIVITY = OFF
AUTHORIZATION [dbo]

 

GO

CREATE FULLTEXT INDEX ON [dbo].[testTBL](

[docname] LANGUAGE [English],

[doctype] LANGUAGE [English],

[document] TYPE COLUMN [doctype] LANGUAGE [English])

KEY INDEX [PK_testTBL]ON ([CatalogTEST], FILEGROUP [PRIMARY])

WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM)

GO

 

b.      Insert a sample document into the table. The IFilterSample comes with a file called “SampleFile.filtersample”, which can be used for test purposes.

 

INSERT INTO dbo.testTBL(doctype,docname,document)

SELECT ‘.filtersample’ AS doctype, ‘SampleFile.filtersample’ AS docname,

* FROM OPENROWSET(BULK N’C:\Users\sudarn\Desktop\FilterSample\SampleFile.filtersample’, SINGLE_BLOB) AS Document

 

c.       Perform a test search

select * from dbo.testTBL where contains(document, ’emergency’)

 

d.      You can also check to see the document was indexed by querying DMV,

SELECT * FROM sys.dm_fts_index_keywords(db_id(‘test’), object_id(‘dbo.testTBL));

 

2.      Using FILTDUMP.exe

 

Filtdump.exe is a sample tool that is available again in WinSDK and can be used to test a particular filter by loading it and then getting the text chunk out of any sample document. It is available in both 32-bit and 64-bit versions. It can be found here once you install WinSDK,

C:\Program Files\Microsoft SDKs\Windows\v6.1\Bin\x64\filtdump.exe

C:\Program Files\Microsoft SDKs\Windows\v6.1\Bin\filtdump.exe

 

For example,

C:\Program Files\Microsoft SDKs\Windows\v6.1\Bin\x64>filtdump.exe "C:\Users\s

udarn\Desktop\FilterSample\SampleFile.filtersample"

 

<snippet of the output>

———————————————————————-

Type = 31 (0x1f): AuthorXYZ

 

———————————————————————-

                                Attribute = B725F130-47EF-101A-A5F102608C9EEBAC\19

                                idChunk = 4

                                BreakType = 0 (No Break)

                                Flags(chunkstate) = 0x1 (Text)

                                Locale = 1033 (0x409)

                                IdChunkSource = 0

                                cwcStartSource = 0

                                cwcLenSource = 0

———————————————————————-

 this is a test of the emergency broadcasting system.

 

This is the content of the document.

 

I’m most pleased that it is the content of the document.

 

</snippet>

 

If you see the above then the IFilter is working fine as you are able to read it and get the text chunks out.

 

 

Possible Issue(s)

You might receive this error message in the SQLFT logs when trying to index a newly inserted row. This indicates that the IFilter was not recognised and there was an issue in loading it.

 

“Warning: No appropriate filter was found during full-text index population for table or indexed view ‘[test].[dbo].[Documents]’ (table or indexed view ID ‘1003150619’, database ID ‘6’), full-text key value ‘SampleFile.filtersample’. Some columns of the row were not indexed.”

 

(OR)

 

Error ‘0x8004170c: The document format is not recognized by the filter.’ occurred during full-text index population for table or indexed view ‘[test].[dbo].[Documents]’ (table or indexed view ID ‘1003150619’, database ID ‘6’), full-text key value ‘SampleFile.filtersample’. Failed to index the row.

 

 

The possible things to check for above error are:-

1.       Ensure that the registration and other steps given above are done in the same sequence as indicated.

2.       If you are using the Debug build ensure that the VCRuntime Debug dependency DLL is present (msvcr90d.dll or msvcr100d.dll).

3.       Use Process Monitor to ensure that your DLL is getting loaded

4.       Use Dependency Walker to ensure there are no other missing dependencies for your DLL.

5.       Verify that the DLL architecture (x64/x86) matches your SQL Server architecture. You cannot mix & match this.

 

I must point out one important thing here if you are going to use this DLL on other machines. You will need to have the Visual Studio Redistributable package installed on the target machine where you plan to deploy this. You will need the corresponding redistributable package for the VS version you used to build the DLL.


FilterSample VS2010 Project

You can download the FilterSample project I used for my testing from here. This was built using Visual Studio 2010 on Windows Server 2008 R2 x64. Just FYI – The Release and Debug DLL present in the attached project is x64. You can rebuild to x86 as per your requirements.

https://skydrive.live.com/?cid=2c5b6bdee139d310&sc=documents&uc=1&id=2C5B6BDEE139D310%21135# 

 

Have fun IFiltering and stay tuned for more on this blog.

Posted in FTS (Full-Text Search) | Tagged: , , , , , , | 4 Comments »

Hello world!

Posted by Sudarshan Narasimhan on July 10, 2011


What is the theSQLDude? Control. The SQLDude is a human-generated dream world/blog built to keep you in control of your SQL Server in order to change a DBA into being enlightened.

I’ll make you an offer you can’t refuse. You see, Johnny, I  feel that technical expertise & internals is going to be a big factor in drawing DBA’s into the blog. I’m hoping I’ll sign a deal with you agreeing to post 5 times a month. Perhaps convince some of your friends in the SQL world to do the same. We’re counting on you, Johnny.

The goal of my blog and I will consider it a true success when time passes, If I can get my readers to say this about my SQL Server posts.
Its mine, my own, my love… my… preciousssss. 

I leave you with these famous words of Tolkien’s Lord of the Rings and the promise of SQL Server posts in the days to come,
One Ring to rule them all,
One Ring to find them,
One Ring to bring them all,
and in the darkness bind them!

Posted in Uncategorized | Leave a Comment »

Next Entries »