SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The DBA's Essential Index Monitoring and Maintenance Tasks


The DBA's Essential Index Monitoring and Maintenance Tasks

Author
Message
John Tamburo
John Tamburo
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5155 Visits: 600
Comments posted to this topic are about the item The DBA's Essential Index Monitoring and Maintenance Tasks
Indy Mike
Indy Mike
Old Hand
Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)

Group: General Forum Members
Points: 364 Visits: 362
I've come to understand that tables below a certain size will not use the indexes assigned to them, because the table is more efficiently joined by loading it entirely into memory. In the scripts I've created, I put the cutoff at 1000 pages, a completely arbitrary number based only on the opinions of other posters on the topic.

Is there any way to determine on my system's unique configuration of memory and processors what that cutoff should really be?
John Tamburo
John Tamburo
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5155 Visits: 600
Indy Mike (2/15/2016)
I've come to understand that tables below a certain size will not use the indexes assigned to them, because the table is more efficiently joined by loading it entirely into memory. In the scripts I've created, I put the cutoff at 1000 pages, a completely arbitrary number based only on the opinions of other posters on the topic.

Is there any way to determine on my system's unique configuration of memory and processors what that cutoff should really be?


Wow. An interesting question to be sure. Let me see if I can find anything on that.

Thanks
John.
SQLEnthusiastic
SQLEnthusiastic
Mr or Mrs. 500
Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)

Group: General Forum Members
Points: 539 Visits: 104
The query given in "Detecting Heaps" will also list tables that have indexes, for example non-clustered PK.
John Tamburo
John Tamburo
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5155 Visits: 600
SQLEnthusiastic (2/15/2016)
The query given in "Detecting Heaps" will also list tables that have indexes, for example non-clustered PK.


Hi. That is correct. A heap is a table with no clustered index.

Thanks
John.
Recce70
Recce70
SSChasing Mays
SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)

Group: General Forum Members
Points: 654 Visits: 437
Indy Mike (2/15/2016)
I've come to understand that tables below a certain size will not use the indexes assigned to them, because the table is more efficiently joined by loading it entirely into memory. ..... Is there any way to determine on my system's unique configuration of memory and processors what that cutoff should really be?


There are a number of different variables that will effect whether or not the optimiser decides to use an index or not, but I really doubt that the size of the RAM comes into play. The optimiser is going to be making a number of assumptions in the interest of producing a plan quickly, and I'm sure the details of the optimiser can and will change between versions.

A rule of thumb (and rules of thumb are usual wrong) is you're unlikely to see a non-covering index used unless you are selecting less than 2% of the table, and possibly as little as 0.5%. I have a feeling that one of Itzik Ben-Gan's "Inside SQL Server" books covers this in good detail.

EDIT:- I've had a look at my old copy of "Inside SQL Server 2005 - T-SQL Querying" and in Chapter 3, page 159, Itzik demonstrates how on his Orders table the selectivity of the query had to be as low as 0.72% before the index was used. He doesn't demonstrate the calculation and/or the assumptions made by the optimiser in it's decision, but a trial and error test is quicker and way more reliable. If you really want how the optimiser is making the decision you may be able to find something by Kimberly Tripp about it.
AZJim
AZJim
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1196 Visits: 305
My current employment only uses purchased software. I find it amazing how many vendors do not create their indexes with a fill factor (other than the default). The default is zero (or 100 percent filled). If you have a data warehouse, read only table, or a table whose primary key is in ascending sequence, then that is fine (unless there are a lot of updates to the ascending sequence table). Otherwise, start with a minimum of 90% fill (or 10% free space). You will find that there will be a lot less need to reorg/rebuild indexes. Saying this in a slightly different way, there will be a lot less fragmentation during the day. Monitor your database periodically. If it still gets fragmented too quickly, lower the fill factor.

Here is what I run to check index fragmentation for the databases on myserver. You can tweak it according to your shop's environment:

USE master
GO
SELECT DB_NAME(database_id) AS 'DBName'
,OBJECT_SCHEMA_NAME(ips.OBJECT_ID, database_id) AS 'SchemaNm'
,OBJECT_NAME(ips.OBJECT_ID, database_id) AS 'TabName'
,OBJECT_ID AS 'ObjectID'
,ips.index_id AS 'IndexID'
,CAST (avg_fragmentation_in_percent AS decimal(4,2)) AS 'AvgFrag%'
,page_count AS 'PageCount'
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'Limited') ips
WHERE page_count > 1000
AND index_type_desc <> 'HEAP'
AND avg_fragmentation_in_percent > 2.5
ORDER BY DBName
, SchemaNm
, OBJECT_NAME(ips.OBJECT_ID, database_id)
, IndexID, [AvgFrag%] desc;
John Tamburo
John Tamburo
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5155 Visits: 600
AZJim (2/16/2016)
My current employment only uses purchased software. I find it amazing how many vendors do not create their indexes with a fill factor (other than the default). The default is zero (or 100 percent filled). If you have a data warehouse, read only table, or a table whose primary key is in ascending sequence, then that is fine (unless there are a lot of updates to the ascending sequence table). Otherwise, start with a minimum of 90% fill (or 10% free space). You will find that there will be a lot less need to reorg/rebuild indexes. Saying this in a slightly different way, there will be a lot less fragmentation during the day. Monitor your database periodically. If it still gets fragmented too quickly, lower the fill factor.

Here is what I run to check index fragmentation for the databases on myserver. You can tweak it according to your shop's environment:
<snip>


Cool; Michelle Ufford has a proc named dba_indexdefrag that diagnoses and remediates fragmentation.

I try to standardize on fillfactor = 90 for most NC indexes.

Thanks
John.
Robert Eder
Robert Eder
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2866 Visits: 521
The query provided to detect heaps will also return table valued functions. The query below will return only user tables that are heaps.


SELECT
OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName
, OBJECT_NAME(i.object_id) AS TableName
, i.*
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE i.index_id = 0
AND o.type = 'U'
gs_100
gs_100
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 347
I found this article on Index usage interesting. By Kimberly Tripp. Talks about Tipping Points.
http://www.sqlskills.com/blogs/kimberly/category/the-tipping-point/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search