July 27, 2008 at 10:58 pm
Hi everyone!
I cant find this bottleneck for the life of me.
I'm pulling data from an offsite RSS feed, interpreting it in coldfusion(building a query), looping that query and for each record checking for a duplicate, and if its not, inserting it into my table.
The system handles that just fine. a little slow, but that's understandable.. less than 20sec total.
MY problem comes in somewhere else and i don't know where.
All of that data gets inserted just fine, then within 5 mins the db server is at 100% and is basically useless.
the www server is fine, has no problems whatsoever with processing, but for some reason, if there's data in that table.. the db server goes down.
nothing on the site continuously uses that table, its only on a per request basis, and that's rare. for example, im the only one testing tonight, and i locked it up, again.
Once I empty the table, everything balances out, server load is back to < 10%
I don't know what the hell is going on.
I'm fairly new to SQL server, so please excuse my ignorance if i ask dumb questions.
If you can help, please do?
Ah yes, server info:
Windows Server 2K3
SQL Server 2K5
Strictly a DB server, no WWW serving whatsoever.
TIA
-=---------------- [edit]-----------------=-
Found out a cause, but i still don't know why.
a record that was inserted had null data, that killed everything. when i filter out those records first, the system keeps on going.
July 28, 2008 at 3:17 am
Your table need to get repaired. If possible then recreate your table. or just run checkDB or dbcc checktable option with repair
July 28, 2008 at 3:34 am
aj (7/27/2008)
Hi everyone!looping that query and for each record checking for a duplicate, and if its not, inserting it into my table.
If you're doing that in SQL, there's part of your problem. SQL works well for set-based code, not row by row processing.
As for the slowdown after, I would recommend that you run profiler for a short while after the load, capture the T-SQL Batch completed and the Stored procedure RPC completed events. Make sure you get the duration, reads, writes, cpu and textdata for both events
Once you've captured the trace, look for long running queries or queries with very high reads/cpu, then look at what they are doing.
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
July 28, 2008 at 3:36 am
Harish (7/28/2008)
Your table need to get repaired. If possible then recreate your table. or just run checkDB or dbcc checktable option with repair
Never run a checkDB or CheckTable with any of the repair options without doing a careful analysis first. CheckDB with repair is the last resort for fixing DB corruption, not the first and definitely not a knee-jerk reaction to a problem.
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
July 28, 2008 at 3:45 am
I think there was something wrong with the table. I found a PK on it that was pointing to a field or table that didnt exist anymore. Fairly sure that was the clog.
I just deleted the table, created a new one with the same fields, and changed some CF code. works great now 🙂
as for looping and RSS parsing.... no, that's done in Coldfusion, not SQL.
can you guys recommend any sort of DB monitors tools, or scripts so i can watch this DB? its never really given me a problem, except for this. but, i still like to be in control of things and make sure i know its all healthy. its not a huge database, i think our biggest table still has < 100k rows.
we do some heavy lifting with timezones and date algorithms, matching airports and whatnot.
I still don't know all the commands for SQL server.. I came from the open-source side of things and jumped into a new pond with this job.
July 28, 2008 at 6:56 am
Tons to learn then.
First, make sure you've got a good backup strategy in place. Before you worry about monitoring and all that stuff, protect your data.
Also, you should schedule a DBCC to run occassionally (most of our databases get one right before the backup so that we only backup clean copies of the database).
Now, you're ready to start monitoring the system. I've got an very introductory article posted here[/url] that might help a little bit. Other than that, do searches here at SQL Server Central. There are a ton of good resources here. When you get stuck, post the question just like you did.
"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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply