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 |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2012-11-01 : 15:31:36
|
| I have a table in which an insertion of one row takes around 6 seconds. Besides intuition, is there a way to figure out which indexes are slowing it down so I can lower the fill factor on those and see if that helps? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-11-01 : 15:34:36
|
| All indexes slow down insert performance. You would check sys.dm_db_index_usage_stats for indexes that have recent updates but no recent scans, seeks or lookups. Those are indexes that are probably not being used and can be dropped safely. Be sure to keep the index definition around, and monitor performance to see if it gets worse after you drop it. |
 |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-11-01 : 16:10:31
|
| Thanks but I already knew that stuff, I should have been more clear. All the indexes are used regularly. I want to know if there's a way to see precisely which indexes are taking precisely how long to update when an insert is performed. I appreciate the help! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-11-01 : 16:40:35
|
| Not that I know of. You'd want to detect page splits on the indexes, there's a method here: http://strictlysql.blogspot.com/2009/10/identifying-page-splits.htmlYou'd have to dig a bit to narrow it down to a specific index, but it should be possible. Once you've done that, you'll want to check their padding and fillfactor values. If they are at 100% fill and zero padding, you can try rebuilding them with appropriate values. If those settings are already good, rebuild anyway. That should reset the free space and reduce page splitting and hopefully the insert time. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-11-01 : 17:03:32
|
| What types of waits are you seeing?-Chad |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-11-02 : 12:41:22
|
| I'm not sure. Can you remind me how to determine which waits are happening only from the insert command when there's a lot happening in the database every second? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-11-02 : 13:40:21
|
| Well that illuminates, if not exactly answers, your question: there's a lot of other stuff going on. What makes you believe it's one index that's particularly slow? Have you tested the inserts on a copy of the database on a non-busy system? You'd need to look at whether the system is waiting on disk, CPU, memory, etc.sys.dm_os_waiting_tasks can tell you what a specific session/SPID is waiting on, and sys.dm_os_wait_stats has aggregate data on all wait types. You'd have to snapshot the latter before and after and compare values, and it will aggregate waits on all sessions. |
|
|
|
|
|
|
|
|
which indexes are slowing inserts?