December 1, 2009 at 3:35 am
Good day, I wonder if anyone can help with this on-going issue?
I have a SQL database that is used to index emails. This database stores a number of items including date/time received, subject, sender etc. and also the body text of the message in a text column. There are a number of indexes on the table including on the date/time received and a full text index for the body text.
Throughput of data is not huge, basically at the rate emails are received and sent, maybe a few per minute average.
Users can search by these fields from a VB6 application using ADO.
This application has been in use for over 7 years, and in the days of SQL 2000 Server I never had the problem about to be described. Since SQL 2005 and now also in SQL 2008 I have an issue. If a user searches for messages in a date range ONLY, the server returns the recordset more or less instantly. If the user searches by message body (and chooses a sensible text value that will not return huge numbers of records) then the results are also quickly given. But SOMETIMES, when the user searches BOTH the received date/time AND the body text then the search will hang, I have waited for over 20 minutes with no results being returned. A rebuild on the date/time index cures the issue for a while but eventually it will come back.
I contacted Microsoft and they explained that the redesign of SQL meant I would have to introduce a maintenance plan to update statistics. Also perhaps an index rebuild plan. Since taking their advice I have tended to have an update statistics as frequent as hourly and index rebuild maybe every day or sometimes 2 or 3 times a day. However, I still get users calling and complaining the search is hanging. I cannot understand how the newer versions of SQL should find it impossible to track the additions to the database over the course of less than one hour between statistics updates, and require to have an execute of the plans forced manually.
I have done testing on this for a long time and the issue is ONLY when the date/time field is searched in combination with the full text field. Anything else seems to work fine!
Can anyone shed any light on this?
Best Regards
Alan
December 1, 2009 at 7:31 am
Sounds like a parameter sniffing problem, not a statistics problem. The stats update would fix things, because it forces plans to recompile.
Can you post the stored procs that are used for these searches please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2009 at 8:07 am
Hi Gail,
Many thanks for taking an interest in this. There are no stored procedures, the search is done by a SQL statement in VB6 code. I also realise I forgot to mention the problem can be narrowed down even more precisely For example, the statements:
SELECT * FROM MsgArchive WHERE Received < '01/01/2009' will work
SELECT * FROM MsgArchive WHERE Received >= '01/01/2009' will work
SELECT * FROM MsgArchive WHERE CONTAINS(BodyText, ' "some text" ') will work
SELECT * FROM MsgArchive WHERE (Received < '01/01/2009') AND (CONTAINS(BodyText, ' "some text" ')) will work
but
SELECT * FROM MsgArchive WHERE (Received >= '01/01/2009') AND (CONTAINS(BodyText, ' "some text" ')) will HANG.
The search hangs when the date/time being searched is recent i.e. within the last day for example. This suggests the index is not properly maintained? Removing the date/time index makes the searches always work but of course is slower and defeats the purpose of having an index! I would repeat the problem ONLY happens when both the date/time index AND the full text table is searched and with a recent date/time. Searching just a recent date/time works, searching just full text works.
I will happily provide any further information. Please bear in mind as I said that SQL 2000 never showed this issue, but 2005 and 2008 both do.
Best Regards
Alan
December 1, 2009 at 8:16 am
It doesn't suggest anything about the index maintenance. It does possibly indicate stale stats, but they shouldn't get stale that fast. How big's the table, how fast are you inserting rows?
I don't know full text well, so may have to leave that for someone else, but can you post the execution plans (the actual plans please) for the two queries that filter on both the datetime and the full text index? If possible, at a time when things are slow, just leave the query, it'll finish eventually (though what eventually is may be another question)
Also, can you post the table's full definition, with all indexes please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2009 at 9:11 am
Hi Gail,
Here is the table "Incoming Archive" definition:
IDNo int not null
MsgNo char(8) null
Received smalldatetime null
Subject varchar(255) null
FromAddress varchar(255) null
MsgType char(1) null
BodyText text null
EntryID varchar(255) null
EMailExists bit not null default 1
IsArchive bit not null default 0
Keyword varchar(50) null
Indexes are:
PK_IncomingArchive clustered primary key on unique IDNo
IX_Received non-clustered on Received
IX_MsgNo non-clustered on MsgNo
IX_Subject non-clustered on Subject
IX_FromAddress non-clustered on FromAddress
Column "BodyType" has full text index with identifier IDNo.
I checked a typical installation and there are about 580,000 rows. Rows added no more than 10 per minute maximum, more typically 1 or 2 per minute only.
I have no experience of identifying the execution plan but I have had a look at the SQL Profiler to see if I can do this. Any hints would help! I have read running a trap on the plans can be server-intensive and since I will have to do this on a production server I shall probably need to catch a server when a user complains about the search and before any of the maintenance plans run. I can then try and profile the execution plan using the sql statement the user will have searched with. So it may be a while before I can give further information. I do appreciate your help.
Best Regards
Alan
December 1, 2009 at 9:27 am
Easiest way to get the execution plans is simply to run the queries manually. Question is whether or not they'll generate identical plans to the ones the app runs. If they do, great (ie if the manually-run queries are slow at the same time as the app-run queries are). If they don't, then it tells us that it's not statistics, so it's a win either way.
Some info on generating exec plans here: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
If you do want to use profiler, the event is ShowPlan XML. Generate a server-side trace, filter on a specific machine if you can and watch the impact. It's not the worst of events, it's not a nice one though
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply