May 16, 2008 at 12:17 pm
OK this is an embarrasing question but one I have to ask...
I have a vendor who is "suggesting" that an issue is our problem and is due to poor maintenance of the database...
Short version of the story is we are doing all the normal stuff for maintenance...
Correct free space
Backups nightly with hourly logs
Enough resources on the server
Nightly Maintenance plan that does a reindex to the original fillfactor, leaves free space
yada, yada, yada...
So...if you were to do a dbcc showcontig after the optimization maintenance plan runs everything is tip top...
fast forward 12 hours and a couple of tables are fragmented all the hell...and they are pointing to the SCAN DENSITY FRAGMENTATION and saying that is an issue...
I looked at one of the tables that gets hit a lot and apparently is used in subqueries as a look up table and it only has two NON-Clustered Unique indexes
CREATE UNIQUE INDEX [XU1_MYWTKEMPLOYEE] ON [dbo].[MYWTKEMPLOYEE]([SESSIONID], [PERSONID]) WITH FILLFACTOR = 75 ON [tkcs2]
GO
CREATE UNIQUE INDEX [XU2_MYWTKEMPLOYEE] ON [dbo].[MYWTKEMPLOYEE]([SESSIONID], [EMPLOYEEID]) WITH FILLFACTOR = 75 ON [tkcs2]
GO
The table is very narrow
SESSIONID varchar
PERSONID int
EMPLOYEEID int
STARTDATEdatetime
ENDDATE datetime
QUESTION
Since the table doesn't have a clustered index does the table actually get reindexed? I am to the point of confusing myself
if I do a DBCC REINDEX on the table in question everything clears up but like I said since they use the table a lot with some sort of reporting (using the table contents for a subquery) there are a lot of inserts and deletes which I believe is causing the higher level of fragmentation in a short amount of time.
I am looking to come up with a valid response to this specific vendor regarding the performance "issue" and tell we are doing everything correctly to maintain their database.
Would a clustered index help in this instance? or would it hinder performance in regards of the overhead associated with the clustered index.
Please comment if you have time.
Thanks in advance,
Lee
May 19, 2008 at 8:43 am
Have you tried to increase the Fillfactor for this index?
Toni
May 19, 2008 at 4:02 pm
toniupstny (5/19/2008)
Have you tried to increase the Fillfactor for this index?Toni
Hi Toni,
That fill factor is the "recommended" fillfactor from the company and hence is the delivered fillfactor for those indexes...
I was trying to get someone's opinion on what happens to an index (non-clustered) when you do a DBCC DBREINDEX...IF the table doesn't have a clustered index...
since there is no clustered index, then is the table considered a HEAP even if you have a NON-CLUSTERED index on the table?
The DBCC REINDEX won't re-org the data per say, it will just re-org the non-clustered index pointers? (hope I am saying this correctly)
which is bad if the table is used in "range" type queries vs. exact matches...correct?
Thanks for reading
Lee
May 19, 2008 at 6:35 pm
Lee Hart (5/19/2008)
toniupstny (5/19/2008)
Have you tried to increase the Fillfactor for this index?Toni
Hi Toni,
That fill factor is the "recommended" fillfactor from the company and hence is the delivered fillfactor for those indexes...
I was trying to get someone's opinion on what happens to an index (non-clustered) when you do a DBCC DBREINDEX...IF the table doesn't have a clustered index...
since there is no clustered index, then is the table considered a HEAP even if you have a NON-CLUSTERED index on the table?
The DBCC REINDEX won't re-org the data per say, it will just re-org the non-clustered index pointers? (hope I am saying this correctly)
which is bad if the table is used in "range" type queries vs. exact matches...correct?
Thanks for reading
Lee
Yup - it's a heap. Unless you have a clustered index - it's a heap.
When you don't have a clustered index - the non-clustered indexes are carrying the heap RowID (FileID+dataPageID+rownumber) in the leaf level.
The fill factor seems like a waste to me being that low.
DBCC REINDEX does the same thing to non-clustered indexes that it would in a table with a clustered index. It rebuilds the index data pages, rebalancing if need be, defragementing the index pages, etc...The data table itself isn't getting reorged.
If you use the table a lot for range searches - no index at all to help it along is going to hurt performance, true. A covering NCI would also help quite a bit.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply