June 11, 2008 at 12:38 pm
Hello, I have a unique problem that started happening recently. In sql server 2005 when i run a script to drop and stored procedure and re-create it using the script genereated from the script generator that sproc doesn't work properly.
It's a fairly complex sproc with lots of temp tables and joing and such. Everything in the sproc works except my keyword search where it's only doing a LIKE statement in a column (title). If anything but the keyword is searched such as topics, members, etc. etc., the sproc works fine. It's the simplest inclusion of keyword using a LIKE statemnt that seems to cause the hang.
Running the sproc in query analyzer under the context of the web account works perfect and fast. However, the same statement run from my command object in asp times out.
What's interesting is running the scripts on my development site works great. It's only on the Production servers that things get screwy. The only difference between the two environments is the Production box has been hardened security-wise.
If I create a new sproc with a different name and update my code it works perfect again. I do this by right clicking on the non-working sproc and selecting SCRIPT STORED PROCEDURE AS --> CREATE TO and then just change the name from SearchMedia1 to SearchMedia2 and execute and all is Great. It's just very sketchy doing updates with the hope that this techniqe works and I obviously can't be changing storeprocedure names with every update. Again, if i just drop it and recreate it with the same name, it behaves as described above. It only just recently started behaving this way. I would typically do a drop/create a weekly basis without any issues.
Additionally, the cpu spikes and hangs high (in the 80-90%).
After giving a new name to the spoc the cpu is normal again (in the 1-5% range) and all is great. It's a high traffic website which I'm sure is causing the cpu spike with all the timeouts.
I've tried searching for resolutions but it's a difficult problem to search for and get relevent results.
I don't think this is part of the issue but worth throwing out. The same day I discovered the above probelem with the keyword searching we discovered we were victom of a sql injection attack which updated a few tables and all their columns by appending a js script include tag. This was fairly easily fixed and the holes where the problem arose were fixed.
June 11, 2008 at 1:38 pm
I'm not sure if the injection attack is related or not.
It sounds like you recompiled after dropping and creating the script and got an execution plan that didn't run well. I've seen that type of thing occur with my stuff.
It's also possible that the ANSI_NULLS or some other settings generated by the script were not the same as they had been and were incompatible with your web app.
Those are my best two guesses.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply