August 4, 2010 at 10:56 pm
Hi all,
I have one table with 100 columns.
That contains One clustered Index and 99 Non_clustered Indexes.
Already table have 2 Lakhs Records.
Is any performance issue when i am inserting new record?
Please help me.
Why am I asking this...
Clustered Index organised physically. But Non-clustered organised logically.
August 4, 2010 at 11:38 pm
kalivaraprasadm (8/4/2010)
Hi all,I have one table with 100 columns.
That contains One clustered Index and 99 Non_clustered Indexes.
Already table have 2 Lakhs Records.
Is any performance issue when i am inserting new record?
Please help me.
Why am I asking this...
Clustered Index organised physically. But Non-clustered organised logically.
You tell me... if you insert a new row, how many indexes will need to be updated? Then ask yourself another question... if you insert a new row and all of the indexes have full pages, how many of those indexes are going to have to do a split to accomodate the new rows?
Not that it matters, but it seems a bit odd to have an index on every bloody column. Do you really need them and can you tell me why a couple of well designed covering indexes wouldn't do the trick instead?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 5, 2010 at 12:21 am
kalivaraprasadm (8/4/2010)
That contains One clustered Index and 99 Non_clustered Indexes.
Why? Why? Why?
How many of those indexes are actually useful? (I'll bet less than 10% of them). If all are single column then they're only really useful if all your queries filter on a single column.
If I was tuning that table, I'd drop all the NC indexes and start over identifying what indexes were really needed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 5, 2010 at 12:41 pm
Also, both types of indexes are ordered logically. Physical ordering is not guaranteed. Regardless though, details are not the issue. 99 indexes is the issue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 6, 2010 at 4:18 am
As a rule of thumb I have the magical number "7" 😀
Meaning, if you need more than 7 indexes, you'll have to expect troubles and take things more in your control.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 6, 2010 at 4:24 am
In addition to the index problem, having 100 columns in a table just by itself will cause performance problems. I have to wonder how many times a single record is split because not all the data will fit on a page.
If the server has to pull a single record from multiple pages, that alone is going to cause an issue as you get more and more records in your table. Then to add 100 indexes on top of that?
Yikes!
August 6, 2010 at 4:35 am
Brandie Tarvin (8/6/2010)
I have to wonder how many times a single record is split because not all the data will fit on a page.
Not permitted (except row overflow of variable-length data types). A row must be on a single page, that's why there's the 8060 byte limit on the size of a row.
When there is row overflow, the extra data is stored like LOB data is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 6, 2010 at 4:49 am
GilaMonster (8/6/2010)
Brandie Tarvin (8/6/2010)
I have to wonder how many times a single record is split because not all the data will fit on a page.Not permitted (except row overflow of variable-length data types). A row must be on a single page, that's why there's the 8060 byte limit on the size of a row.
When there is row overflow, the extra data is stored like LOB data is.
Not quite, Gail.
Books Online
Rows cannot span pages in SQL Server 2005, however portions of the row may be moved off the row's page so that the row can actually be very large. The maximum amount of data and overhead that is contained in a single row on a page is 8,060 bytes (8 KB). However, this does not include the data stored in the Text/Image page type. In SQL Server 2005, this restriction is relaxed for tables that contain varchar, nvarchar, varbinary, or sql_variant columns. When the total row size of all fixed and variable columns in a table exceeds the 8,060 byte limitation, SQL Server dynamically moves one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit, starting with the column with the largest width.
This is what I'm talking about. My understanding of LOB data is that it is stored in a slightly different manner.
August 6, 2010 at 4:59 am
Brandie Tarvin (8/6/2010)
GilaMonster (8/6/2010)
Not permitted (except row overflow of variable-length data types). A row must be on a single page, that's why there's the 8060 byte limit on the size of a row.When there is row overflow, the extra data is stored like LOB data is.
Not quite, Gail.
I don 't see what you're disagreeing with. Rows cannot span pages except for row_overflow of variable-length data types. The BoL extract says what I said just in a lot more detail.
My understanding of LOB data is that it is stored in a slightly different manner.
Both row overflow and LOB are stored out-of-page, in a linked list structure. Not mixed, but stored very similarly. Point is not stored in pages in the index leaf level.
We have no indication that's happening here, since we don't know what the data types in this table are.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 6, 2010 at 4:59 am
Brandie Tarvin (8/6/2010)
GilaMonster (8/6/2010)
Brandie Tarvin (8/6/2010)
I have to wonder how many times a single record is split because not all the data will fit on a page.Not permitted (except row overflow of variable-length data types). A row must be on a single page, that's why there's the 8060 byte limit on the size of a row.
When there is row overflow, the extra data is stored like LOB data is.
Not quite, Gail.
Books Online
Rows cannot span pages in SQL Server 2005, however portions of the row may be moved off the row's page so that the row can actually be very large. The maximum amount of data and overhead that is contained in a single row on a page is 8,060 bytes (8 KB). However, this does not include the data stored in the Text/Image page type. In SQL Server 2005, this restriction is relaxed for tables that contain varchar, nvarchar, varbinary, or sql_variant columns. When the total row size of all fixed and variable columns in a table exceeds the 8,060 byte limitation, SQL Server dynamically moves one or more variable length columns to pages in the ROW_OVERFLOW_DATA allocation unit, starting with the column with the largest width.This is what I'm talking about. My understanding of LOB data is that it is stored in a slightly different manner.
At least the concept is the same. Insert a data location pointer and ship the data off row to the overflow data zone. Only difference is you cannot influence where that zone is as you can do with the images. (create table ... on filegroup TEXTIMAGE_ON theOtherFilegroup)
However, I haven't tested if it will also put off row data in theOterFilegroup storage.:Whistling:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 6, 2010 at 5:06 am
One thing I'm suddenly confused about. 1) Why is a performance issue question in the Security forum?
Back to the point, Doesn't the data being in different places, pointers or no, require a little extra processing on the part of SQL? If it has to go out to another place to gather the rest of the data, that's an extra X milliseconds that gets added for every record. Maybe I'm not using the correct terminology here, but essentially this is my original point.
As far as us not knowing the datatypes, that's the reason why I said I didn't know how many times the record was split. (Again with the terminology issue. IMHO, if not all the data is in the same place, then it's a "split" record. But that's obviously a semantic that's not worth quibbling about.)
August 6, 2010 at 5:11 am
Brandie Tarvin (8/6/2010)
One thing I'm suddenly confused about. 1) Why is a performance issue question in the Security forum?Back to the point, Doesn't the data being in different places, pointers or no, require a little extra processing on the part of SQL? If it has to go out to another place to gather the rest of the data, that's an extra X milliseconds that gets added for every record. Maybe I'm not using the correct terminology here, but essentially this is my original point.
As far as us not knowing the datatypes, that's the reason why I said I didn't know how many times the record was split. (Again with the terminology issue. IMHO, if not all the data is in the same place, then it's a "split" record. But that's obviously a semantic that's not worth quibbling about.)
OK. I'm back on track with you. :w00t:
btw My excuses for "speaking before my turn" ... :blush:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply