 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Full-Text Search / July 2004Getting Recent Search Terms
Hi, I dont know if this post has been repeated before. Is there a way to get all the recent search terms passed through Full text search? Thanks, P no, there is no way. On most search projects I have worked on we asynchronously scrape the web logs for the search terms. > Hi, > I dont know if this post has been repeated before. Is there a way to > get all the recent search terms passed through Full text search? > Thanks, > P Hilary, Actually, there is a way to do this via SQL Profiler, either interactively using the SQL Profiler GUI tool or via server-side tracing, for example: -- Filename: FTS_Trace.sql -- Modified: 4:30 PM 2/19/2003 declare @rc int declare @TraceID int declare @maxfilesize bigint set @maxfilesize = 5 exec @rc = sp_trace_create @TraceID output, 0, N'D:\FTS_Activity_Trace', @maxfilesize, NULL if (@rc != 0) goto error -- Set the events declare @on bit set @on = 1 exec sp_trace_setevent @TraceID, 12, 1, @on exec sp_trace_setevent @TraceID, 12, 12, @on -- Set the Filters declare @intfilter int declare @bigintfilter bigint exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler' exec sp_trace_setfilter @TraceID, 10, 1, 6, N'CONTAINS' exec sp_trace_setfilter @TraceID, 10, 1, 6, N'contains' -- Set the trace status to start exec sp_trace_setstatus @TraceID, 1 -- display trace id for future references select TraceID=@TraceID goto finish error: select ErrorCode=@rc finish: go PShah, you can also setup a query log table and when you accept the user's input and then pass it to the Contains* or Freetext* query, you can log the input to a Query log table for further review. If you provide more details on what you are looking for, I can provide more specific solutions. Regards, John > no, there is no way. On most search projects I have worked on we > asynchronously scrape the web logs for the search terms. [quoted text clipped - 4 lines] > > Thanks, > > P yes this is a possibility, but in my tests running profiler in a production environment causes a 10 to 30% performance hit which will exacerbate locking to a point where it really is unusable. Your experience may vary, but my experience is that it is unacceptable. > Hilary, > Actually, there is a way to do this via SQL Profiler, either interactively [quoted text clipped - 46 lines] > > > Thanks, > > > P Hilary, I only said that this can be done using SQL Profiler vs. "there is no way" to do this... I didn't say that I would recommend this method over using the query log approach or visa versa, nor did I say that I would recommend using the SQL Profiler GUI in production environments. In fact, I agree that using the SQL Profiler GUI tool to monitor production environment can adversely affect production performance and that is why I provided the server-side script. This script or any other profiler server-side script can be executed is such a way (say, only for 4 minutes periods, executed randomly over a specified time period, approx. 1 hr.) that will have minimum impact on a production SQL Server environment. Actually, I've run SQL Profiler server-side traces at very large customer (>1TB databases) sites as well as at smaller customer sites with minimal impact. Additionally, the current SQL Server Magazine has a very good article entitled "9 Steps to an Automated Trace" at http://www.winnetmag.com/SQLServer/Article/ArticleID/43014/43014.html that almost exactly describes what I've done at large customer sites and I would recommend using scheduled server-side Profiler traces to get "all the recent search terms passed through Full text search" over using the SQL Profiler GUI for large/active production sites. Finally, I did ask PShaw to provide more details on what he is looking for, as there are viable solutions to this issue, and if we had more info on what he is specifically looking for we could provide more and better solutions. Regards, John > yes this is a possibility, but in my tests running profiler in a production > environment causes a 10 to 30% performance hit which will exacerbate locking [quoted text clipped - 53 lines] > > > > Thanks, > > > > P Strange, I can't get your trace to work. By chance are you filtering on an application with a name like contains or CONTAINS? ;)  SignatureHilary Cotter Looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html > Hilary, > I only said that this can be done using SQL Profiler vs. "there is no way" [quoted text clipped - 87 lines] > > > > > Thanks, > > > > > P Hilary, Sorry, that code was one of many 'test' versions that I had created during my testing of this issue last year... I've attached a zip file that contains two sql files, the Trace start & stop .sql files and the .trc output file that demonstrates exactly how to do this. It's still a bit crude and I would not recommend running - this version - on production servers, as it is a proof-of-concept that server-side Profiler tracing of FTS queries is not only possible, but functional. With modifications and via careful scheduling of when and how often the server-side trace is executed, it can be used in production environments without adversely affecting the overall SQL Server performance... Below is the primary difference: EXEC @RC = sp_trace_setfilter @TraceID, 1, 1, 6, N'%CONTAINS%' EXEC @RC = sp_trace_setfilter @TraceID, 1, 1, 6, N'%contains%' EXEC @RC = sp_trace_setfilter @TraceID, 1, 1, 6, N'%containstable%' EXEC @RC = sp_trace_setfilter @TraceID, 1, 1, 6, N'%freetext%' EXEC @RC = sp_trace_setfilter @TraceID, 1, 1, 6, N'%freetexttable%' PShaw, if you reply with more detailed requirements of your environment and how you would like to get the recent search terms that are passed through to Full text search, I can also provide solutions that use FTS query logging techniques. Regards, John > Strange, I can't get your trace to work. By chance are you filtering on an > application with a name like contains or CONTAINS? ;) [quoted text clipped - 102 lines] > > > > > > Thanks, > > > > > > P |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.