Determining Index fragmentation / when to rebuild / when to reorganize

Index fragmentation:
When data is inserted, updated or deleted in a table, if clustered or non-clustered indexes exist on that table, they must be maintained to reflect the changes. The maintenance of these indexes eventually will cause the indexes less efficient. This inefficiency leads to index fragmentation.

We have two types of index fragmentation:
Internal Fragmentation
External Fragmentation

In result of DELETE operation, spaces occurs in the underlying index page, so when index page is not full as it should be, it leads to internal fragmentation.

Sometimes when INSERT or UPDATE operation occurs, database engine creates additional index page to accommodate this operation, a page split occurs and a new page created by maintaining the logical order, as it does not maintain the physical order of index pages. So this variation in logical and physical ordering of index pages called external fragmentation.

SQL Server maintains the counters or values to keep track indexes internal and external fragmentation, so you can evaluate by using these indicators, when to rebuild and when to reorganize the indexes to increase its efficiency.

You can get these stats from a dynamic management function that is replacement of DBCC SHOCONTIG statement:

SYS.DM_DB_INDEX_PHYSICAL_STATS(database_id/Null, object_id/Null, index_id/Null/0, partition_number/Null, mode/Null/Defualt)

database_id/Null: ID of the database, you can use DB_ID(‘database_name’) or DB_ID() to get the current database id. If you specify Null then it will return the information for all databases, and you must supply object_id, index_id and partition_number with Null values.
object_id/Null: ID of base table or view. You can use Object_ID(‘table/view name’) to get object id. If you specify Null, it will return the information of all table/view of current database, you must supply index_id and partition_number with Null values.
index_id/Null/0: ID of index. You can specify Null, it will return the information of all indexes for specified base table or index, you must supply partition_number with Null value. 0 index_id is for heap tables.
partition_number/Null: partition number of index/heap or Null to return the information for all partitions.
mode/Null/Default: mode specifies the scan level that is used to obtain statistics. Valid inputs are DEFAULT, NULL, LIMITED, SAMPLED or DETAILED. The default is LIMITED.


Now you have to decide when to rebuild (recreating the current indexes) the indexes and when to reorganize (de-fragmentation of the current indexes) them, you have to observe the values of avg_fragmentation_in_percent and avg_page_space_used_in_percent columns returned by sys.dm_db_index_physical_stats DMF.


If avg_fragmentation_in_percent greater than 15 OR avg_fragmentation_in_percent less than 60 then you have to rebuild or recreate the indexes as:

ALTER INDEX ALL ON Table_Name REBUILD


If avg_page_space_used_in_percent between 60 and 75 Or avg_fragmentation_in_percent between 10 and 15 then you have to reorganize the indexes as:

ALTER INDEX ALL ON Table_Name REORGANIZE

Example:

SELECT
DB_NAME(DATABASE_ID)
,OBJECT_NAME(OBJECT_ID)
,INDEX_ID
,INDEX_TYPE_DESC
,AVG_FRAGMENTATION_IN_PERCENT
,AVG_PAGE_SPACE_USED_IN_PERCENT
,CASE WHEN AVG_FRAGMENTATION_IN_PERCENT >15 OR AVG_PAGE_SPACE_USED_IN_PERCENT<60 THEN 'REBUILD INDEX' END AS INDEX_REBUILD_STATE
,CASE WHEN (AVG_FRAGMENTATION_IN_PERCENT >10 AND AVG_FRAGMENTATION_IN_PERCENT<15) OR
(AVG_PAGE_SPACE_USED_IN_PERCENT>60 AND AVG_PAGE_SPACE_USED_IN_PERCENT<75) THEN 'RE-ORGANIZE INDEX' END AS INDEX_REORGANIZE_STATE
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(), OBJECT_ID('TEST'), NULL, NULL, 'DETAILED')
WHERE INDEX_ID<>0

Finding total / available / used space by transactional log file

DBCC SQLPERF(LOGSPACE)

It provides statistics that how much transactional log space was used by each database in the current server. So, you can monitor the amount of space used and can decide when to take backup or to truncate the transactional log file.

Its result set consists of:

Database Name – Name of the database
Log Size (MB) – Actual amount of log file size, it is less than initial value specified in database properties, because Database Engine reseves small amount of disk space for internal header information.
Log Space Used (%) – Percentage of total space that is occupied by transactional log.
Status – that is always 0

Example:

DBCC SQLPERF(LOGSPACE)

Database Name Log Size (MB) Log Space Used (%) Status
master 0.7421875 43.68421 0
tempdb 0.7421875 64.73684 0
model 0.4921875 76.19048 0
msdb 0.9921875 61.02362 0

Determining indexes utilization Or unused indexes

You can use following DMV to check index utilization or to see unused indexes.

SYS.DM_DB_INDEX_USAGE_STATS

By determining the stats, you can see what indexes are mostly used by the queries and what indexes are unused and overhead for the system from maintenance point of view. These stats/counters will be initialized to empty whenever SQL Server restarts, database is de-attached, or database is shutdown.

When you perform SELECT, INSERT, UPDATE or DELETE operations, then this DMV keeps track all of your performed operations in each separate counter as:

USER_SCANS: selection performed on whole table without specifying any criteria. (SELECT * operation)

USER_SEEKS: selection performed by specifying any criteria. (Either looking up a single or doing range scan)

USER_LOOKUPS: large selection performed with specified criteria by using non-clustered indexes.

USER_UPDATES: level of maintenance when INSERT, UPDATE or DELETE operation performed.

Above counters have associated dates, these are updated when above any counter incremented. This means this date will reflect about the last date of action performed or last time when index was accessed.

LAST_USER_SCAN
LAST_USER_SEEK
LAST_USER_LOOKUP
LAST_USER_UPDATE

You can use the following query to get index name and its associated user stats.

SELECT SI.INDEX_ID,SI.NAME,USER_SCANS,USER_SEEKS,USER_LOOKUPS, USER_UPDATES, LAST_USER_SCAN, LAST_USER_SEEK, LAST_USER_LOOKUP,LAST_USER_UPDATE
FROM SYS.DM_DB_INDEX_USAGE_STATS DMI
INNER JOIN SYS.INDEXES SI ON SI.INDEX_ID=DMI.INDEX_ID AND DMI.OBJECT_ID=SI.OBJECT_ID
WHERE DMI.OBJECT_ID=OBJECT_ID('TEST')

Example:

1) Create test table
CREATE TABLE TEST
(VAL1 INT IDENTITY(1,1)
,VAL2 VARCHAR(2)
,VAL3 VARCHAR(3)
)

2) Create Indexes
CREATE CLUSTERED INDEX VAL1_INDEX ON TEST (VAL1)
CREATE NONCLUSTERED INDEX VAL2_INDEX ON TEST (VAL2)
CREATE NONCLUSTERED INDEX VAL3_INDEX ON TEST (VAL3)

3) Insert test data

DECLARE @I INT
SET @I=1
WHILE (@I<100000)
BEGIN
INSERT INTO TEST VALUES('C','B')
SET @I=@I+1
END

4) Check USER_UPDATES and LAST_USER_UPDATE values

5) Update 50% data with new values

UPDATE TEST SET VAL2='D',VAL3='F' WHERE VAL1>50000

6) Check values for USER_UPDATES, LAST_USER_UPDATE, USER_SEEKS, LAST_USER_SEEK

7) SELECT * FROM TEST

8) Check values for USER_SCANS and LAST_USER_SCAN

9) SELECT * FROM TEST WHERE VAL1=40

10) Check values for USER_SEEKS and LAST_USER_SEEK

11) SELECT * FROM TEST WHERE VAL2='F'

12) Check values for USER_SEEKS, LAST_USER_SEEK, USER_LOOKUPS and LAST_USER_LOOKUP

Change the name of a Database, Table or Column

You can use the following procedure to change database, table, index or column name.

SP_RENAME 'Object_Old_Name','Object_New_Name','Object_Type'

Object Type can be DATABASE, OBJECT, INDEX, COLUMN and USERDATATYPE.

To Change Database name:
It will change the database name from DB1 to DB1_New.
EXEC SP_RENAME 'DB1','DB1_New','DATABASE'

To change Table name:
It will update the table name from Table1 to Table1_New.

EXEC SP_RENAME 'Table1','Table1_New','OBJECT'


To change Column name:
It will change column name val1 in Table1 to val1_new.

EXEC SP_RENAME 'Table1.val1','val1_new','COLUMN'

Scalar Configuration Functions

All configuration functions are nondeterministic. This means these functions do not always return the same results every time they are called, even with the same set of input values.

The following scalar functions return information about current configuration option settings,

@@DATEFIRST
Returns the current value, for the session, of SET DATEFIRST.
e.g. SELECT @@DATEFIRST
you can change first day of week setting by using the following command.
SET DATEFIRST [integer]
e.g Set DATEFIRST 5
It will set Firday as first day of week.

@@DBTS
It returns the last-used timestamp value of the current database. A new timestamp value is generated when a row with a timestamp column is inserted or updated.
e.g. SELECT @@DBTS

@@LANGID
Returns the local language identifier (ID) of the language that is currently being used.
e.g. SELECT @@LANGID

To view information about language settings, including language ID numbers, you can use the following system procedure.
EXEC SP_HELPLANGUAGE

You can change language settings as
e.g. SET LANGUAGE 'Italian'

@@SERVERNAME
Returns the name of the local server running SQL Server.
e.g. SELECT @@SERVERNAME

@@LANGUAGE
Returns the name of the language currently being used.
e.g. SELECT @@LANGUAGE
Remaining details will be the same as defined under @@LANGID

@@SERVICENAME
Returns the name of the registry key under which SQL Server is running. @@SERVICENAME returns 'MSSQLSERVER' if the current instance is the default instance; this function returns the instance name if the current instance is a named instance.
e.g. SELECT @@SERVICENAME

@@LOCK_TIMEOUT
Returns the current lock time-out setting in milliseconds for the current session.
It allows an application to set the maximum time that a statement waits on a blocked resource. When a statement has waited longer than the LOCK_TIMEOUT setting, the blocked statement is automatically canceled, and an error message is returned to the application.

It returns a value of -1 if SET LOCK_TIMEOUT has not yet been run in the current session.
e.g. SELECT @@LOCK_TIMEOUT

@@SPID
Returns the session ID of the current user process.
It can be used to identify the current user process in the output of sp_who.
e.g. SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'

@@TEXTSIZE
Returns the current value of the TEXTSIZE option of the SET statement. This specifies the maximum length, in bytes, of varchar(max), nvarchar(max), varbinary(max), text, or image data that a SELECT statement returns.
The default size is 4096 bytes. The maximum size that @@TEXTSIZE returns is 2^31-1 bytes.

You can change its setting as:

SET TEXTSIZE [integer]

e.g.
Displays current value
SELECT @@TEXTSIZE AS 'Text Size'
Setting new value
SET TEXTSIZE 2048
Displays new value
SELECT @@TEXTSIZE AS 'Text Size'

@@VERSION
Returns version, processor architecture, build date, and operating system for the current installation of SQL Server.
The information returned by @@VERSION is similar to the product name, version, platform, and file data returned by the xp_msver stored procedure, which provides more detailed information.
e.g. SELECT @@VERSION

Searching a Table in all databases on Current Server

You can use Information_Schema view Tables to see the table list in the current DB as:
Select * From DB1.Information_Schema.Tables

we will use the above Sql query with the following stored procedure to search a table.

Procedure: dbo.sp_MSforeachdb
Description: This procedure will execute the attached string as a command against every database on the server. Any question mark(?) , within the string will be replaced by every database name.

Example:
so you can use it to find a table name in every database as:

Exec dbo.sp_MSforeachdb 'select ''?'', * from [?].INFORMATION_SCHEMA.TABLES where table_name like ''authors'' '

Calcualting estimated time for any running SQL command

you can use the following SQL statement to calculate estimated time for any sql command. This script is applicable for Sql Server 2005 and above.

1) you can get the command label by executing below procedures or sql statement, that you will use in estimated time calculation query.

Exec sp_Who
Or
Exec sp_Who2
Or
Select * From sys.sysprocesses
Or
Select * From sys.dm_exec_requests

2) script for estimated time calculation, in example it is calculating time for back up command.
-------------------------------------
SELECT
R.SESSION_ID
,R.COMMAND
,CONVERT(NUMERIC(6,2),R.PERCENT_COMPLETE) AS [PERCENT COMPLETE]
,CONVERT(VARCHAR(20),DATEADD(MS,R.ESTIMATED_COMPLETION_TIME,GETDATE()),20) AS [ETA COMPLETION TIME]
,CONVERT(NUMERIC(6,2),R.TOTAL_ELAPSED_TIME/1000.0/60.0) AS [ELAPSED MIN]
,CONVERT(NUMERIC(6,2),R.ESTIMATED_COMPLETION_TIME/1000.0/60.0) AS [ETA MIN]
,CONVERT(NUMERIC(6,2),R.ESTIMATED_COMPLETION_TIME/1000.0/60.0/60.0) AS [ETA HOURS]
,CONVERT(VARCHAR(100)
,(SELECT SUBSTRING(TEXT,R.STATEMENT_START_OFFSET/2
,CASE WHEN R.STATEMENT_END_OFFSET = -1 THEN 1000 ELSE (R.STATEMENT_END_OFFSET-R.STATEMENT_START_OFFSET)/2 END)
FROM SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE)))
FROM SYS.DM_EXEC_REQUESTS R
WHERE COMMAND IN ('BACKUP DATABASE')

---------------------------------

How to get Sql text without using dbcc inputbuffer

You can use a dynamic management view for this purpose, details given below:

Dynamic Management View:
sys.dm_exec_sql_text(sql_handle)

sql_handle SQL handle of the text to be looked up.

It retruns:
dbid - Database ID, you can get database name by using DB_Name(dbid) function.
Objectid - Object ID, you can get Object name by using Object_Name(objectid) funciton.
number - store procedures number if any
encrypted - if text is encrypted, it will have value 1
text - text of SQL query

Example:

SELECT T.TEXT, P.*
FROM SYS.SYSPROCESSES P
OUTER APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) T

List of Permission granted to caller [Calling User]

Following statements returns the permission details for the user who is executing the statement.

-- List of Permission at Server Level
SELECT * FROM fn_my_permissions(NULL, 'SERVER')

-- List of Permission at Database Level
SELECT * FROM fn_my_permissions('DB1', 'DATABASE')

-- List of Permission at Object Level
SELECT * FROM fn_my_permissions('Object1', 'OBJECT')
ORDER BY subentity_name, permission_name

It reports:
Entity_Name
Subentity_Name
Permission_Name

Test the connection to a Linked Server

Procedure: SP_TESTLINKEDSERVER

Syntax:
sp_testlinkedserver @servername = servername

@Servername: Linked servername

Note: Caller must have appropriate login mapping.

e.g.
Exec sp_testlinkedserver SQL1

Get List of Linked Servers on Curent/Local Server

Procedure: SP_LINKEDSERVERS

Description: Returns the list of linked servers defined in the local server.

It returns:
Column name - Description
SRV_NAME - Name of the linked server.
SRV_PROVIDERNAME - Friendly name of the OLE DB provider managing access to the specified linked server.
SRV_PRODUCT - Product name of the linked server.
SRV_DATASOURCE - OLE DB data source property corresponding to the specified linked server.
SRV_PROVIDERSTRING - OLE DB provider string property corresponding to the linked server.
SRV_LOCATION - OLE DB location property corresponding to the specified linked server.
SRV_CAT - OLE DB catalog property corresponding to the specified linked server.

Database Level Principals List : System Stored Procedure

Procedure Name: sp_helpuser
Definition: Reports information about database-level principals in the current database.
Syntax: sp_helpuser [ [ @name_in_db = ] 'security_account' ]

[ @name_in_db = ] 'security_account'
Is the name of database user or database role in the current database. security_account must exist in the current database. security_account is sysname, with a default of NULL. If security_account is not specified, sp_helpuser returns information about all database principals.

It returns:
Username
Groupname [User Role]
LoginName [Login for user]
DefDBName [Default database name]
DefSchemaName [Default Schema Name]
UserId [ID of username in current database]
SID [User Security Identification Number]

Example:
Exec Sp_Helpuser
it will display the stats for all available users in the current database

Or

Exec sp_helpuser 'dbo'
it will display the stats only for dbo user.

case sensitive selection by using Collation Clause

1) Create temporary table to insert values to check the implemenation

Create Table #temp
( ID int identity(1,1)
, val1 varchar(50)
)

2) Values insertion
Insert into #temp values('abc')
Insert into #temp values('ABC')
Insert into #temp values('aBc')

3) Selection by using collation function
select * from #temp where val1 like 'abc%'
collate SQL_Latin1_General_CP1_CS_AS

4) above collation is only for general english, you can use the colloation accordingly.
5) you can view the all available collations by using the following statement.
SELECT * FROM fn_helpcollations()

6) General Syntax for Collate Clause
COLLATE { | database_default }

collation_name

Is the name of the collation to be applied to the expression, column definition, or database definition. collation_name can be only a specified Windows_collation_name or a SQL_collation_name.

database_default

Causes the COLLATE clause to inherit the collation of the current database.