February 14, 2009 at 8:47 pm
Dear masters,
I want to know about indexing on insert query. I've read MSDN - Index Structures, but I don't find what I want. This is the case.
I have a table that only have an index. We know that when an insert query is executed, SQL Server will insert it on table AND index. My table is a heap (just contain non-clustered index).
What SQL Server do to an (non-clustered) index that newly inserted? When SQL Server split a leaf that becoming full? And how SQL Server maintain that insert? I want for more detail explanation.
http://en.wikipedia.org/wiki/B-tree#Insertion
SQL Server don't do an insert like this one (wikipedia). So, how SQL Server do to an insert query? do to 1000 insert queries? and last question, how to see it? I used showplan, but I just got very little information.
Thx for your expertise.
Regards,
Wiwin.
February 14, 2009 at 8:56 pm
The following query will help you see what is happening with the index when you insert. Best thing to do is create a test table with an index and run some inserts so that you get an idea of what is happening, running this query following the inserts so that you can see the deltas.
Hope this helps.
Oh, the query came from The SQL CAT team and reading the link would be profitable as well.
declare @dbid int
declare @objectid int
select @dbid = db_id()
/*This is to look at the utilization of indexes on a specific table.
Comment following statement and the statement in the where clause if you
want to look at the whole database.
*/
select @objectid = object_id('YourTableName')
selectobjectname=object_name(s.object_id)
, indexname=i.name
, i.index_id
, reads=range_scan_count + singleton_lookup_count
, 'leaf_writes'=leaf_insert_count+leaf_update_count+ leaf_delete_count
, 'leaf_page_splits' = leaf_allocation_count
, 'nonleaf_writes'=nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count
, 'nonleaf_page_splits' = nonleaf_allocation_count
fromsys.dm_db_index_operational_stats (@dbid,NULL,NULL,NULL) s,
sys.indexes i
whereobjectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
and s.object_id = @objectid
order by
reads desc --asc
, leaf_page_splits desc
, leaf_writes
, nonleaf_writes
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 15, 2009 at 4:49 pm
Thx a lot, DavidB, you give me incredible information! 🙂
Regards,
Wiwin.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply