August 11, 2009 at 9:08 am
Hello everyone,
I'm an application developer for a company that doesn't put much stock in Database administration, so I've been trying to figure it out myself (with some help from your archives).
However, I'm running into a problem for which I cannot find the answer (assuming there is one)
The problem is this: We have a database which contains data that is regularly archived. The archived data is then deleted from the production database.
The query that I need help optimising is one of the deletes.
It looks quite simple, (delete from table where pKey = ?), but it's taking minutes to run due to a PAGEIOLATCH_SH lock.
There are however about a hundred foreign keys that point to this table.
According to the query plan, the statement should take about .7 seconds to run. Instead, as stated earlier, it runs for about 150 seconds before finishing.
Up until recently, we were running on SQL2000, but a couple of months ago the decision was made to migrate to 2005. The same cleanup script we're using now was used on SQL2000, and performance has never been bad enough that someone needed to look into it.
The server is Windows 2k3, quad P4, 4GB memory, raid 5 diskset.
MaxDoP is set to 2.
The query plan, a screenshot of perfmon, create statements of both the 'Job' table and one of the data tables (they're basically the same, just with different names and data) are attached in a zip.
The query is: delete from job where j_Id = '000051233453747206'.
The Job table contains about 700K rows, the processdata tables each contain between 0 an 700K rows, with an average of about 50K rows.
Can anybody here tell me how I can get the performance near to what the query plan promisses? I'l settle for a couple of seconds. :-p
Kind regards,
Martin
August 11, 2009 at 9:12 am
Hello,
Check the following document, there is a section (I/O Bottlenecks) which explain your question.
Regards,
Victor Alvarez
http://sqlpost.blogspot.com
August 11, 2009 at 9:48 am
Victor,
Thanks for your reply.
I've scanned the document, and found the statement to extract the number of reads/writes.
And the logical reads are through the roof.:ermm: (Over a million)
Logical writes are at 238...? I'm deleting one row, what's all the writing about?
However, physical reads are nonexistent.
So my question now becomes: How do I get the count on the logical reads down (I posted the query plan, I don't see where they're coming from)
Kind regards,
Martin
August 11, 2009 at 1:36 pm
Looks like you're getting pounded by table scans.
Do all of those foreign keys have matching nonclustered indexes? The table scripts you included have the foreign keys listed, but there are no indexes on them. Check the tables you see in the plan with a Clustered Index Scan first. 'Clustered Index Scan' a fancy way of saying 'table scan', which will show lots of PAGEIOLATCH_SH and PAGEIOLATCH_EX waits.
Additionally, when you upgraded to SQL Server 2005 from SQL Server 2000, did you update the statistics on all of your tables? Do you regularly update statistics on your tables?
-Eddie
Eddie Wuerch
MCM: SQL
August 11, 2009 at 2:59 pm
Eddie,
Thanks. 🙂
I'll try tomorrow, but I think that may be it.
I've allways created indices for the queries that I perform, but never thought about the fact that the database may need (or at least like) them on the foreign keys.
The database was never 'upgraded' from 2k to 2k5, as this resulted in unpredictable behaviour. Instead we created it empty on 2k5 and pumped the data through.
There is a weekly maintenance task to upgrade all statistics (at least that's what the comment says, I've never actually checked the code.)
Anyway, I'll let you know if the creation of two extra indices on each process table solved my problem sometime tomorrow.
Kind regards,
Martin
August 12, 2009 at 9:15 am
Hello everyone,
As I suspected, Eddie's solution was the right one. Thanks. 🙂
If anyone's curious, the statement now takes less than 2 seconds, which is good enough for me.:)
For some reason, I've always thought that indices on foreign keys were created 'in background' so to speak. Obviously, I was wrong.:blush:
Anyway, thanks for helping.
Kind regards,
Martin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply