Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pphillips001
Starting Member
23 Posts |
 Posted - 2012-05-02 : 09:36:45
|
| Hi,I have an insert writing onto a heap (700Gb) running a lot. This insert has been running up to 103 physical reads and up to 19611 logical reads - which considering it has run over half a million times (in the last month) has added up!Is there a way to bringing these reads down?(OLTP SQL 2005 Standard)Cheers,Paul===========================There is no reality - only perspective. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-05-02 : 11:44:19
|
| I don't use heaps so I've not experienced this behavior. Since you are on 2005 you can't take advantage of minimally logged inserts (2008+), which are hard to justify anyway. However, there are lots of articles discussing the pro and cons of heaps versus clustered tables.Here is a Microsoft best practices article that may be of interest:http://technet.microsoft.com/library/Cc917672Also, you didn't mention it, but do you have other indexes/constraints on that table? |
 |
|
|
pphillips001
Starting Member
23 Posts |
Posted - 2012-05-03 : 12:04:50
|
| Hi Lamprey,Thanks for the feedback. That link made some interesting reading. The table is a logging table, so a clustered index would've been costly with not much yield.The table does have a couple of non-clustered indexes on it - probably to speed up some auditing queries. There are no triggers.Is it possible that these indexes could be causing unwanted IO?===========================There is no reality - only perspective. |
|
|
|
|
|
|
Is There A Way To Speed Up An Insert?