we have some performance issue with a program, I traced tsql using sql server 2005 profiler and find below result for an INSERT statement,
cpu: 0 read: 28 write: 0 duration: 32804
I guess it's because the inserted table has several indexes and it's getting big. is there any other possibilities that I should check? thanks.
28 writes that take 32 seconds? That has nothing to do with indexes, you can't have that many indexes as to justify such large time. That is concurency, the insert is blocked by other operations. Use sys.dm_exec_requests and watch the wait_time and wait_resource of the insert request.
Duration is in microseconds. 32804 microseconds = 0.032804 seconds. I don't think you have a problem.
Actually that depends on if your looking at the results in a table or if you are looking at them from the Profiler UI. If it's the UI then it is milliseconds.
"Beginning with SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6, of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10-3, of a second). In SQL Server 2000, the server reported both duration and CPU time in milliseconds. In SQL Server 2005 and later, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds." http://msdn.microsoft.com/en-us/library/ms175848.aspx
How many indexes are there and how many columns are there in the associated indexes. What is the execution plan for this query?