November 26, 2009 at 2:14 pm
Our full text catalog is just over 800meg
This is a very busy database with procedure execution counts in the millions weekly.
The Query in issue is a join on two tables plus an internal join containstable on 2 fields (title and body). One of the tables has millions of rows. title field is nvarchar(512), body field is nvarchar(max).
The Where clause is language = English and date > getdate() - 1 month
A customer went to our home page and entered rim in our Search News box and clicked go. (Research in Motion)
I captured the query and saw that the:
whole query returns 64 records for RIM forn the last month.
SQL Server did just over 11 thousand records
Query plan shows me that the remote scan did 43.5 million rows.
I had 2 managers at my desk today and I was showing them the issue. I changed the text in the query from rim to Microsoft and executed the query.
The query returned in about 5 seconds. I did not get the records counts.
We tried many combinations with similar issues.
While doing this OUR INTERNAL NETWORK WENT DOWN between our office and our data centre.
2 minutes later or so we were on our backup network. We continued our analysis and all versions of this query including rim return in under 1 second.
When we came back to our primary network the issue was still gone.
The remote scan now returns 3638 rows.
I just got off of the phone with the CIO I notified him of the good news "the problem is gone" but I stated that I was embarrassed to tell him that I did not know why.
He stated he will not accept us crashing the internal network everytime we have a database issue:-)
We both had a good laugh!
Any suggestions?
November 26, 2009 at 2:50 pm
I was quite certain the issue had nothing to do with the network crash but it did happen during our analysis...
After the crash somehow we had lost the order by clause in the query
After puting the order by clause back in the query we go back to the 43.5 million rows returned by the full text query.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply