February 14, 2016 at 10:32 pm
Comments posted to this topic are about the item The DBA's Essential Index Monitoring and Maintenance Tasks
February 15, 2016 at 3:11 pm
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?
February 15, 2016 at 3:19 pm
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.
February 15, 2016 at 10:17 pm
The query given in "Detecting Heaps" will also list tables that have indexes, for example non-clustered PK.
February 16, 2016 at 5:57 am
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.
February 16, 2016 at 6:04 am
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.
February 16, 2016 at 3:36 pm
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;
February 16, 2016 at 3:48 pm
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.
February 16, 2016 at 7:07 pm
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'
February 22, 2016 at 12:56 pm
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/
March 8, 2016 at 4:54 pm
Good article, thanks.
March 9, 2018 at 6:49 am
i learned the hard way that if you have a busy replication environment then dropping unused indexes on your subscribers is a huge performance improvement and will get rid of weird replication issues you might have
March 9, 2018 at 11:39 am
Inserts is not at the end - where this is - but where rows are deleted. So if there is space on a page, this is used before a new page is allocated to the table. See the following script!
-- Heap - Reuse space on a page
USE master;
GO
DROP DATABASE HeapDB;
GO
CREATE DATABASE HeapDB;
GO
USE HeapDB;
CREATE TABLE dbo.HeapData
(
ID INT NOT NULL
CONSTRAINT PK_HeapData PRIMARY KEY NONCLUSTERED,
Txt CHAR(200) NOT NULL
CONSTRAINT DF_HeapData_Txt DEFAULT (REPLICATE('x', 200))
);
GO
SET NOCOUNT ON;
WITH
Id_Data
AS
(
SELECT 1 AS ID
UNION ALL
SELECT ID + 1
FROM Id_Data
WHERE ID < 500
)
SELECT ID,
NEWID() AS Sortkol
INTO Data
FROM ID_Data
OPTION (MAXRECURSION 0);
GO
DECLARE @Sortkol UNIQUEIDENTIFIER = (SELECT MIN(Sortkol)
FROM Data);
DECLARE @ID INT = (SELECT ID
FROM Data
WHERE Sortkol = @Sortkol);
WHILE EXISTS (SELECT * FROM Data WHERE Sortkol > @Sortkol)
BEGIN
INSERT INTO dbo.HeapData (ID) VALUES (@ID);
SELECT @Sortkol = MIN(Sortkol)
FROM Data
WHERE Sortkol > @Sortkol;
SELECT @ID = ID
FROM Data
WHERE Sortkol = @Sortkol;
END;
SET NOCOUNT OFF;
GO
SELECT SUBSTRING( sys.fn_PhysLocFormatter(%%physloc%%),
2,
LEN(sys.fn_PhysLocFormatter(%%physloc%%)) - 2) AS PhysicalRID,
*
INTO t1
FROM dbo.HeapData
ORDER BY 1;
GO
DELETE
FROM dbo.HeapData
WHERE ID % 2 = 1;
GO
SELECT SUBSTRING( sys.fn_PhysLocFormatter(%%physloc%%),
2,
LEN(sys.fn_PhysLocFormatter(%%physloc%%)) - 2) AS PhysicalRID,
*
INTO t2
FROM dbo.HeapData
ORDER BY 1;
GO
INSERT INTO dbo.HeapData (ID, Txt)
SELECT ID + 1,
REPLICATE('x', 100)
FROM dbo.HeapData;
GO
SELECT SUBSTRING( sys.fn_PhysLocFormatter(%%physloc%%),
2,
LEN(sys.fn_PhysLocFormatter(%%physloc%%)) - 2) AS PhysicalRID,
*
INTO t3
FROM dbo.HeapData
ORDER BY 1;
GO
SELECT LEFT(SUBSTRING(PhysicalRID, 3, 20),
CHARINDEX(':', SUBSTRING(PhysicalRID, 3, 20)) - 1) AS Pageno,
RIGHT(PhysicalRID,
CHARINDEX(':', REVERSE(SUBSTRING(PhysicalRID, 3, 20))) - 1) AS Offset,
*
INTO AfterPriInsert
FROM t1;
SELECT LEFT(SUBSTRING(PhysicalRID, 3, 20),
CHARINDEX(':', SUBSTRING(PhysicalRID, 3, 20)) - 1) AS Pageno,
RIGHT(PhysicalRID,
CHARINDEX(':', REVERSE(SUBSTRING(PhysicalRID, 3, 20))) - 1) AS Offset,
*
INTO AfterDelete
FROM t2;
SELECT LEFT(SUBSTRING(PhysicalRID, 3, 20),
CHARINDEX(':', SUBSTRING(PhysicalRID, 3, 20)) - 1) AS Pageno,
RIGHT(PhysicalRID,
CHARINDEX(':', REVERSE(SUBSTRING(PhysicalRID, 3, 20))) - 1) AS Offset,
*
INTO AfterSecInsert
FROM t3;
GO
SELECT Pageno,
COUNT(*) AS RowsOnPage
FROM dbo.AfterPriInsert
GROUP BY Pageno
ORDER BY Pageno;
SELECT Pageno,
COUNT(*) AS RowsOnPage
FROM dbo.AfterDelete
GROUP BY Pageno
ORDER BY Pageno;
And since 2008 it has been possible to rebuild a HEAP - see the following script!
USE master;
DROP DATABASE HeapDB;
GO
CREATE DATABASE HeapDB;
GO
USE HeapDB;
CREATE TABLE dbo.HeapData
(
ID INT NOT NULL
CONSTRAINT PK_HeapData PRIMARY KEY NONCLUSTERED,
Txt CHAR(200) NOT NULL
CONSTRAINT DF_HeapData_Txt DEFAULT (REPLICATE('x', 200))
);
GO
SET NOCOUNT ON;
WITH
Id_Data
AS
(
SELECT 1 AS ID
UNION ALL
SELECT ID + 1
FROM Id_Data
WHERE ID < 500
)
SELECT ID,
NEWID() AS Sortkol
INTO Data
FROM ID_Data
OPTION (MAXRECURSION 0);
GO
DECLARE @Sortkol UNIQUEIDENTIFIER = (SELECT MIN(Sortkol)
FROM Data);
DECLARE @ID INT = (SELECT ID
FROM Data
WHERE Sortkol = @Sortkol);
WHILE EXISTS (SELECT * FROM Data WHERE Sortkol > @Sortkol)
BEGIN
INSERT INTO dbo.HeapData (ID) VALUES (@ID);
SELECT @Sortkol = MIN(Sortkol)
FROM Data
WHERE Sortkol > @Sortkol;
SELECT @ID = ID
FROM Data
WHERE Sortkol = @Sortkol;
END;
SET NOCOUNT OFF;
GO
SELECT SUBSTRING( sys.fn_PhysLocFormatter(%%physloc%%),
2,
LEN(sys.fn_PhysLocFormatter(%%physloc%%)) - 2) AS PhysicalRID,
*
INTO t1
FROM dbo.HeapData
ORDER BY 1;
GO
DELETE
FROM dbo.HeapData
WHERE ID % 2 = 1;
GO
SELECT SUBSTRING( sys.fn_PhysLocFormatter(%%physloc%%),
2,
LEN(sys.fn_PhysLocFormatter(%%physloc%%)) - 2) AS PhysicalRID,
*
INTO t2
FROM dbo.HeapData
ORDER BY 1;
GO
ALTER TABLE dbo.HeapData REBUILD;
GO
SELECT SUBSTRING( sys.fn_PhysLocFormatter(%%physloc%%),
2,
LEN(sys.fn_PhysLocFormatter(%%physloc%%)) - 2) AS PhysicalRID,
*
INTO t3
FROM dbo.HeapData
ORDER BY 1;
GO
SELECT LEFT(SUBSTRING(PhysicalRID, 3, 20),
CHARINDEX(':', SUBSTRING(PhysicalRID, 3, 20)) - 1) AS Pageno,
RIGHT(PhysicalRID,
CHARINDEX(':', REVERSE(SUBSTRING(PhysicalRID, 3, 20))) - 1) AS Offset,
*
INTO AfterPriInsert
FROM t1;
SELECT LEFT(SUBSTRING(PhysicalRID, 3, 20),
CHARINDEX(':', SUBSTRING(PhysicalRID, 3, 20)) - 1) AS Pageno,
RIGHT(PhysicalRID,
CHARINDEX(':', REVERSE(SUBSTRING(PhysicalRID, 3, 20))) - 1) AS Offset,
*
INTO AfterDelete
FROM t2;
SELECT LEFT(SUBSTRING(PhysicalRID, 3, 20),
CHARINDEX(':', SUBSTRING(PhysicalRID, 3, 20)) - 1) AS Pageno,
RIGHT(PhysicalRID,
CHARINDEX(':', REVERSE(SUBSTRING(PhysicalRID, 3, 20))) - 1) AS Offset,
*
INTO AfterRebuild
FROM t3;
GO
SELECT Pageno,
COUNT(*) AS RowsOnPage
FROM dbo.AfterPriInsert
GROUP BY Pageno
ORDER BY Pageno;
SELECT Pageno,
COUNT(*) AS RowsOnPage
FROM dbo.AfterDelete
GROUP BY Pageno
ORDER BY Pageno;
SELECT Pageno,
COUNT(*) AS RowsOnPage
FROM dbo.AfterRebuild
GROUP BY Pageno
ORDER BY Pageno;
March 12, 2018 at 12:49 pm
If you rebuild a heap, how long does it take for it to become fragmented again?
March 12, 2018 at 4:56 pm
alen teplitsky - Monday, March 12, 2018 12:49 PMIf you rebuild a heap, how long does it take for it to become fragmented again?
It is not possible to answer. It depends on how many rows you DELETE and how many UPDATE's with changing data in variable length columns.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply