SQL Server Performance Forum – Threads Archive

lots of reads and writes when delete

when insert , lots of writes occured to database . when select , lots of writes occured to database . when delete , lots of reads and writes occured , why ? or what happened indeed in database when delete some data? thanks in advance<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

when insert , lots of writes occured to database . When inserting, you have writes because you are writing data to the table.
–You also have reads because you are reading the data you are inserting. when select , lots of writes occured to database . ??? Ummm, no. You have lots of reads. when delete , lots of reads and writes occured , why ? When deleting you have reads to determine what you will delete.
–The more indexes on the table you have, the more reads you produce.
–Writes??? Not so many. How are you getting these stats, and what constitutes "lots"? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

As it purely depends on the query fired to perform certain task and suggested also on the indexes on the involved tables. PROFILER is the best tool to monitor the activity and also PERFMON to collect few counters with connection to the hardware. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Referential integrity could cause lots of reads too.

yes , i use trigger to do cascade delete of three tables( i know this is not a good idea , but the app needs) and the recovery mode of the database is simple . when i delete the records , i found that writes/sec was 1.5 multipes to the reads/sec , but i think writes/sec should be at most 0.5 multipes to the reads/sec . any idea ?

If there are, check if the foreign keys are indexed. If they are not indexed, SQL server has to scan that table (in your case) 70 times and if it are big tables that could take some time. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Of course,the foreign keys are indexed . Deleting records may cause reindex , so lots of writes/sec was necessary to update the indexes , this is the case ??

Deleting the records will not cause any reindexing, until unless you issue DBREINDEX statement to defrag them. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Wait a minute, did you say writes are 1.5 times the reads. Did you clean SQL Server buffers before executing the delete operation. If the page is already presnt in the buffer cache, SQL Server will not reuqest the same. Also how did you calculate the pages written and read? Pages are written to the disk only when checkpoint occurs. Did you manually execute checkpoint. How many records are being deleted. We may be talking of writing to Transaction Log as well. Secondly, what does the delete operation do? Does it delete on the clusterd index as well. We may be talking here about writing index pages. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Thanks all! now i monitor the os counter PhysicalDisk Reads/Sec and Writes/Sec of the SQL Server datafiles resided disks when app delete the records , and found that writes/sec was about 0.4 times the reads/sec , some trans log was writen to disk , and i think this is a normal stutas .

Also capture the counters – PhysicalDisk Object: Avg. Disk Queue Length & Physical Disk Object: % Disk Time counter for more information. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>