Big index - slow efficiency

Hello Experts.

I have a big clustered index on my table. How to rebuild this index to increase efficiency ?
I try to use DBCC INDEXDEFRAG and DBCC DBREINDEX but  productivity is still low.

Here is the result DBCC SHOWCONTIG

DBCC SHOWCONTIG scanning 'REJ' table...
Table: 'REJ' (1301579675); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 538
- Extents Scanned..............................: 68
- Extent Switches..............................: 67
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 100.00% [68:68]
- Logical Scan Fragmentation ..................: 0.19%
- Extent Scan Fragmentation ...................: 95.59%
- Avg. Bytes Free per Page.....................: 84.8
- Avg. Page Density (full).....................: 98.95%
ASkEPAAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
This is not a big table, so more than likely lowering the extent fragmentation isn't going to help too much.  

I am curious about this statement:  "I try to use DBCC INDEXDEFRAG and DBCC DBREINDEX but  productivity is still low."

What exactly do you mean?
ASkEPAAuthor Commented:
I expected that after using these commands, the fragmentation index decreases and productivity increases, however, this did not happen.Please take a look at the attached actual execution plan (all the time consuming scan of the index).
1.JPG
chapmandewCommented:
That part of your exec plan doesn't tell me anything (other than your query could potentially be written better).
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
this means you have no proper index for your query.
please post the query
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
But...
a clustered index scan is essentially a table scan
You should have a a clustered index seek instead to gain performance.

And this is not always the case, if you choose many records then a index is useless. It's good when your'e selecting a few rows.

But agin, youre not utilizing the index, your'e doing a table scan (since the clustered index is the order in wich the table is written on disc).

Perhaps a nonclustered composite index is whats needed to make a good database design. I cant tell from your information.

For more knowledge about indexes:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;814324
http://support.microsoft.com/default.aspx?scid=kb;EN-US;325024
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tunesql.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/statquery.asp

//Marten
ASkEPAAuthor Commented:
The query is written correctly, the problem lies 100% in the index. Problems with the productivity appeared after several years have passed of work of the base.
chapmandewCommented:
Are you updating your statistics?

Like I said, this isn't a big table, so it is possible that you're just now seeing issues as more data is in your system.

You can always drop and recreate your indexes.
ASkEPAAuthor Commented:
What do you mean by updating the statistics? Could you write how I do this?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>The query is written correctly, the problem lies 100% in the index
the explan plan tells something different, UNLESS the query has to query all the table data anyhow, in which case the problem IS in the query

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ASkEPAAuthor Commented:
This is it! I upgraded statistics - my query is very quickly now .. Thank you very much for your help!
chapmandewCommented:
Neato...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.