Check and Enable Automatic Statistic Update on Database Level


AS SQL Server Best Practices: Auto-Create and Auto-Update Statistics Should Be On – Most of the Time in general they are a very good thing for performance. You could try to figure out which columns need statistics, but it’s often better to let SQL Server do that for you.You can turn on the AUTO_CREATE_STATISTICS database option and SQL Server will automatically determine when it needs the Statistics and create them for you.

Check And Enable Automatic Statistic Update on all databases have this option is disable

Select ‘ALTER DATABASE [‘+Name+’] SET AUTO_CREATE_STATISTICS ON;
GO
ALTER DATABASE [‘+Name+’] SET AUTO_UPDATE_STATISTICS ON;
GO
‘ from Sys.Databases where is_auto_update_stats_on = 0 or is_auto_create_stats_on = 0

Take the Result and Execute it in another session

Update Statistics

To check all my posts you godirect to my personal website or you can download this Excel sheet My Technical POSTS

View all my tips , LinkedIn Slideshare ,Youtube Channel.My posts in MSDN

Bookmark lookup , Key lookup , RID lookup in SQL Server


Introduction : 

Today i will Show  :

What  is the Meaning of Bookmark lookup , Key lookup , RID lookup in SQL Server ?

when SQL Server Retrieve Bookmark lookup , Key lookup , RID lookup ?

How can we Remove the Bookmark lookup , Key lookup , RID lookup ?

Bookmark lookup , Key lookup , RID lookup Meaning  and when SQL Server Retrieve it ?

Continue reading “Bookmark lookup , Key lookup , RID lookup in SQL Server”

Display Table ,Column ,KeyCols ,IncludeCols for All index


Hi Guys

by this Script you can return 

Table Name 

Index Name

Column Name Created on the index

Include Column 

=======================================================

SELECT ‘[‘ + Sch.NAME + ‘].[‘ + Tab.[name] + ‘]’ AS TableName
,Ind.[name] AS IndexName
,SUBSTRING((
SELECT ‘, ‘ + AC.NAME
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.
[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id
AND IC.is_included_column = 0
ORDER BY IC.key_ordinal
FOR XML PATH(”)
), 2, 8000) AS KeyCols
,SUBSTRING((
SELECT ‘, ‘ + AC.NAME
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.
[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id
AND IC.is_included_column = 1
ORDER BY IC.key_ordinal
FOR XML PATH(”)
), 2, 8000) AS IncludeCols
FROM sys.[indexes] Ind
INNER JOIN sys.[tables] AS Tab ON Tab.[object_id] = Ind.[object_id]
INNER JOIN sys.[schemas] AS Sch ON Sch.[schema_id] = Tab.[schema_id]
— WHERE Tab.name = ‘MyTableNameHere’
— uncomment to get single table indexes detail
ORDER BY TableName

Generate Script To drop and Recreate all index in Database


Hi Guys

by this Script you can Drop all indexes on your Database then Created it Again

this Cursor Script after the Execution the Result is Script With  Drop the Create all index in your database but n0t include the index on the Primary Key

DECLARE
@IncludeFileGroup bit = 1,
@IncludeDrop bit = 1,
@IncludeFillFactor bit = 1

— Get all existing indexes, but NOT the primary keys
DECLARE Indexes_cursor CURSOR
FOR SELECT
SC.Name AS SchemaName
, SO.Name AS TableName
, SI.Object_Id AS TableId
, SI.[Name] AS IndexName
, SI.Index_ID AS IndexId
, FG.[Name] AS FileGroupName
, CASE WHEN SI.Fill_Factor = 0 THEN 100 ELSE SI.Fill_Factor END Fill_Factor
FROM sys.indexes SI
LEFT JOIN sys.filegroups FG
ON SI.data_space_id = FG.data_space_id
INNER JOIN sys.objects SO
ON SI.object_id = SO.object_id
INNER JOIN sys.schemas SC
ON SC.schema_id = SO.schema_id
WHERE ObjectProperty(SI.Object_Id, ‘IsUserTable’) = 1
AND SI.[Name] IS NOT NULL
AND SI.is_primary_key = 0
AND SI.is_unique_constraint = 0
AND IndexProperty(SI.Object_Id, SI.[Name], ‘IsStatistics’) = 0
ORDER BY Object_name(SI.Object_Id), SI.Index_ID

DECLARE @SchemaName sysname
DECLARE @TableName sysname
DECLARE @TableId int
DECLARE @IndexName sysname
DECLARE @FileGroupName sysname
DECLARE @IndexId int
DECLARE @FillFactor int

DECLARE @NewLine nvarchar(4000) SET @NewLine = CHAR(13) + CHAR(10)
DECLARE @Tab nvarchar(4000) SET @Tab = Space(4)

— Loop through all indexes
OPEN Indexes_cursor

FETCH NEXT
FROM Indexes_cursor
INTO @SchemaName, @TableName, @TableId, @IndexName,
@IndexId, @FileGroupName, @FillFactor

WHILE (@@Fetch_Status = 0)
BEGIN

DECLARE @sIndexDesc nvarchar(4000)
DECLARE @sCreateSql nvarchar(4000)
DECLARE @sDropSql nvarchar(4000)

SET @sIndexDesc = ‘– Index ‘ + @IndexName + ‘ on table ‘ + @TableName
SET @sDropSql = ‘IF EXISTS (SELECT 1’ + @NewLine
+ ‘ FROM sysindexes si’ + @NewLine
+ ‘ INNER JOIN sysobjects so’ + @NewLine
+ ‘ ON so.id = si.id’ + @NewLine
+ ‘ WHERE si.[Name] = N”’ + @IndexName + ”’ — Index Name’ + @NewLine
+ ‘ AND so.[Name] = N”’ + @TableName + ”’) — Table Name’ + @NewLine
+ ‘BEGIN’ + @NewLine
+ ‘ DROP INDEX [‘ + @IndexName + ‘] ON
[‘ + @SchemaName + ‘].[‘ + @TableName + ‘]’ + @NewLine
+ ‘END’ + @NewLine

SET @sCreateSql = ‘CREATE’

— Check if the index is unique
IF (IndexProperty(@TableId, @IndexName, ‘ IsUnique’) = 1)
BEGIN
SET @sCreateSql = @sCreateSql + ‘ UNIQUE’
END
–END IF
— Check if the index is clustered
IF (IndexProperty(@TableId, @IndexName, ‘ IsClustered’) = 1)
BEGIN
SET @sCreateSql = @sCreateSql + ‘ CLUSTERED’
END
–END IF

SET @sCreateSql = @sCreateSql + ‘ INDEX [‘ + @IndexName + ‘]
ON [‘ + @SchemaName + ‘].[‘ + @TableName + ‘]’ + @NewLine + ‘(‘ + @NewLine

— Get all columns of the index
DECLARE IndexColumns_cursor CURSOR
FOR SELECT SC.[Name],
IC.[is_included_column],
IC.is_descending_key
FROM sys.index_columns IC
INNER JOIN sys.columns SC
ON IC.Object_Id = SC.Object_Id
AND IC.Column_ID = SC.Column_ID
WHERE IC.Object_Id = @TableId
AND Index_ID = @IndexId
ORDER BY IC.key_ordinal

DECLARE @IxColumn sysname
DECLARE @IxIncl bit
DECLARE @Desc bit
DECLARE @IxIsIncl bit SET @IxIsIncl = 0
DECLARE @IxFirstColumn bit SET @IxFirstColumn = 1

— Loop through all columns of the index and append them to the CREATE statement
OPEN IndexColumns_cursor
FETCH NEXT
FROM IndexColumns_cursor
INTO @IxColumn, @IxIncl, @Desc

WHILE (@@Fetch_Status = 0)
BEGIN
IF (@IxFirstColumn = 1)
BEGIN
SET @IxFirstColumn = 0
END
ELSE
BEGIN
–check to see if it’s an included column
IF (@IxIsIncl = 0) AND (@IxIncl = 1)
BEGIN
SET @IxIsIncl = 1
SET @sCreateSql = @sCreateSql + @NewLine + ‘)’ +
@NewLine + ‘INCLUDE’ + @NewLine + ‘(‘ + @NewLine
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + ‘,’ + @NewLine
END
–END IF
END
–END IF

SET @sCreateSql = @sCreateSql + @Tab + ‘[‘ + @IxColumn + ‘]’
— check if ASC or DESC
IF @IxIsIncl = 0
BEGIN
IF @Desc = 1
BEGIN
SET @sCreateSql = @sCreateSql + ‘ DESC’
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + ‘ ASC’
END
–END IF
END
–END IF
FETCH NEXT
FROM IndexColumns_cursor
INTO @IxColumn, @IxIncl, @Desc
END
–END WHILE
CLOSE IndexColumns_cursor
DEALLOCATE IndexColumns_cursor

SET @sCreateSql = @sCreateSql + @NewLine + ‘) ‘

IF @IncludeFillFactor = 1
BEGIN
SET @sCreateSql = @sCreateSql + @NewLine +
‘WITH (FillFactor = ‘ + Cast(@FillFactor as varchar(13)) + ‘)’ + @NewLine
END
–END IF

IF @IncludeFileGroup = 1
BEGIN
SET @sCreateSql = @sCreateSql + ‘ON [‘+ @FileGroupName + ‘]’ + @NewLine
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + @NewLine
END
–END IF

PRINT ‘– **************************************************************’
PRINT @sIndexDesc
PRINT ‘– **************************************************************’

IF @IncludeDrop = 1
BEGIN
PRINT @sDropSql
PRINT ‘GO’
END
–END IF

PRINT @sCreateSql
PRINT ‘GO’ + @NewLine + @NewLine

FETCH NEXT
FROM Indexes_cursor
INTO @SchemaName, @TableName, @TableId, @IndexName,
@IndexId, @FileGroupName, @FillFactor
END
–END WHILE
CLOSE Indexes_cursor
DEALLOCATE Indexes_cursor

 

When we Create Primary Key !!!


Question : Choose the wrong Answer

When We Create primary key on Table :

1- it Always Create ” Clustered index ”

2- It Always Create ” Non Clustered index ”

3- It Always Create ” Clustered and Non Clustered index ”

Answer :

The Wrong answer in number 3 primary key create one index on table (Clustered or Non Clustered ) Automatically .

When we created Primary key on any table this primary key Check the Table if this table have Non Clustered Index Automatic Create Clustered index Else if the table have Clustered index primary key Automatic Create Non Clustered index

 

Demo :

1- CREATE TABLE

Create table Table_primaryKey

(

Pkey_ID int  Not Null,

Pkey_name Nvarchar(50) ,

Pkey_Lsn bigint

)

2- Check Index type on Table Name ” Table_PrimaryKey ” 

after the result Show we will check the Column Name ” typ_desc ” to know the type of the indexes on this table .

Note : Typ_desc = HEAP ( Meaning this Table not have ” CLUSTERED INDEX ” .

 

A- Check the index on table by Script

SELECT obj.name,
ind.name,
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IndexFillFactor’),0) [fill_factor],
create_date, modify_date, ind.type_desc,
fill_factor, has_filter,
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IndexDepth’),0) [IndexDepth],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsAutoStatistics’),0) [IsAutoStatistics],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsStatistics’),0) [IsStatistics],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsUnique’),0) [IsUnique],
ISNULL(INDEXPROPERTY(OBJECT_ID(obj.name),ind.name,’IsClustered’),0) [IsClustered]
FROM sys.objects obj
INNER JOIN sys.indexes ind
ON obj.object_id = ind.object_id
WHERE obj.type = ‘U’
and obj.name = ‘Table_primaryKey’

 

Heap

now we see the result is HEAP  So we don’t have any clustered index or Non Clustered index also because the result not show any thing expected the HEAP

B -Check index table from SQL Server Management Studio 

Check Database >> Tables >> Indexes – you will not found any index

3- Create Primary Key

ALTER TABLE Table_primaryKey
ADD CONSTRAINT [PK_Table_primaryKey_Pkey_ID]
PRIMARY KEY (Pkey_ID ASC
)
GO

Primary key will Create Automatic Clustered index ” PK_Table_primaryKey_Pkey_ID ” on the table

Clustered index

4- Drop the Primary Key

ALTER TABLE Table_primaryKey
DROP CONSTRAINT [PK_Table_primaryKey_Pkey_ID]
GO

Now the Clustered index Dropped

Check Database >> Tables >> Indexes – No indexes

5- Create Clustered Index

CREATE CLUSTERED INDEX
[CL_Table_primaryKey_Pkey_ID] ON Table_primaryKey
( Pkey_ID ASC)
GO

Check Database >> Tables >> Indexes – Index Name (CL_Table_primaryKey_Pkey_ID) Clustered Index

6- Create Primary Kay again (Check point No 3 )

Check Database >> Tables >> Indexes – PK_Table_primaryKey_Pkey_ID (NONCLUSTERED)

NonCluestered

Now we now if the table have Clustered iindex and you create Primary key it will Create automatic Non-clustered Index

7- drop the primary key again (check point No 4)

Check Database >> Tables >> Indexes – Index Name PK_Table_primaryKey_Pkey_ID (NONCLUSTERED) DELETED

8- Create Non-Clustered Index 

9- Create primary Key

Check Database >> Tables >> Indexes -you will find New Non Clustered index now we have 2 Non-Clustered Index

10- drop All index in table 

Check Database >> Tables >> Indexes >>> Delete one by one

11 -drop the primary key again (check point No 4)

Note : Now i i Create New primary key it will Create Automatic New Clustered Index but if you need to Create Primary Key With Non-Clustered index .

12- Create Primary Key With Non-Clustered Index

ALTER TABLE Table_primaryKey
ADD CONSTRAINT [PK_Table_primaryKey_Pkey_ID]
PRIMARY KEY NONCLUSTERED (Pkey_ID ASC
)
GO

13 – Check the index on Table 

Primary Key With NonClustered

Primary Key Can Create Non-Clustered Index When we don’t have Clustered index in on Table .

I Wish Success To Everyone

Eng. Mostafa Elmasry

Database Administrator