Index defragmentation

Fragmentation can be defined as any condition that cause more than optimal amount of disk I/O to be performed in accessing a table or cause the longer disk I/O. For optimal performance of SQL queries, the data pages of tables are as contiguous as possible and pages are as fully packed as possible. Fragmentation breaks this rule thus reducing the performance of the queries. There are two levels of fragmentation: file system level (physical disk fragmentation) and index level fragmentation.

Physical disk fragmentation is the fragmentation of the database file in the file system, when file system cannot allocate contiguous space for it. As a result disk head has to move back and forth when reading from the datbase files. That happens when database file is stored on the same disk as other files, such as OS, application, or log files or when database grows frequently and in small chunks. The way to avoid it is to allocate enough space originally and to specify growth option to allocate larger chunks instead of small ones.

Before running windows defragmentation tool, SQL Server needs to be stopped, otherwise database file will be skipped.

Index fragmentation can be reduced by selecting an appropriate fill factor when creating the index.

There are two ways to defragment indexes: rebuild and reorganize.

When you reorganize an index, SQL Server physically reorders the leaf-level pages to match logical order of the leaf nodes. The process does not allocate new pages but only uses existing ones, compacting the indexes. Reorganization uses minimal resources and does not block queries or updates.

Reorganization should only be used on lightly fragmented indexes (no more than 30% fragmentation), heavily fragmented indexes should be rebuilt.

The sql below reorganizes the specified index:

ALTER INDEX index_name 
ON DBName.TableName
REORGANIZE

The important thing to keep in mind is that if you try to reorganize or rebuild an index with few pages, most likely nothing will change.

Rebuilding an index involves dropping the original index and building a new one, so that index starts clean, with minimal fragmentation. Index can be rebuilt offline and online. Rebuilding is done the same was as reorganizing except REBUILD keyword is used instead of REORGANIZE:

ALTER INDEX index_name 
ON DBName.TableName
REBUILD

if you want to add some options to your rebuild, use WITH clause

ALTER INDEX index_name 
ON DBName.TableName 
REBUILD WITH (
  FILLFACTOR = 60,
  ONLINE = ON
)

Analyzing index fragmentation

The sys.dm_db_index_physical_stats function is used to determine which indexes are fragmented and the extent of their fragmentation.

The function takes the following parameters: database_id, object_id, index_id, partition_number, mode.

database_id - ia an integer value that represents id number of your database
object_number - is an integer value that represents your table or view
index_id - integer that represents id of your index
mode - can be either LIMITED, SAMPLED, or DETAILED

LIMITED: Scans the smallest number of pages, which means this is the fastest mode. The LIMITED mode is equivalent to NULL and DEFAULT.
SAMPLED: Scans 1% of all pages. If an index contains fewer than 10,000 pages, then DETAILED mode is used.
DETAILED: Scans all index pages, which means this is the slowest mode, but most accurate.

You must specify all five parameters, even if their values are null.

Here are some important values returned by sys.dm_db_index_physical_stats function:

index_type_desc - index type (clustered, non-clustere)
avg_fragmentation_in_percent - percentage of the logical index that is fragmented
fragment_count - number of fragments in the leaf level
page_count - number of index or data pages.

An index always has at least one fragment (fragment_count). The maximum number of fragments that an index can have is equal to the number of pages (page_count). For example, an index that is made up of 3 pages can at the most have 3 fragments. The larger the fragment, the less disk I/O that is required. Ideally, the avg_fragmentation_in_percent value should be as close to zero as possible.

Microsoft recommends that you reorganize your index if the avg_fragmentation_in_percent value is less than or equal to 30% and rebuild the index if the value is greater than 30%

I am using the following query to retrieve the fragmentation data:

SELECT OBJECT_NAME(ps.object_id) As TableName, i.name As IndexName,ps.index_type_desc As IndexType, ps.index_depth, ps.index_level, ps.avg_fragmentation_in_percent, ps.fragment_count, ps.page_count 
FROM sys.dm_db_index_physical_stats(DB_ID('Northwind'), null, null, null, 'LIMITED') as ps
INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id 
WHERE NOT i.name IS NULL

On index statistics

If an index is not being used much, it will have a lot of free space in the index pages, thus occupying a lot more disk space then it should and it may be worth compacting it (via rebuild or defrag) to get the disk space back. At the same time is an index is barely used, there might not be a point to spend resources to remove any kind of fragmentation.

Another thing is a non-clustered index affects performance in a number of ways, one of them being the amount of extra IO it takes to maintain each index. For example whenever a new record is added to the database, a matching record is inserted into a non-clustered index, or whenevr the record is deleted, a matching record for the index must be deleted as well, or a clustered index's value is changed during update, the matching record for non-clustered index must be updated as well.

There is a dynamic management view sys.dm_db_index_usage_stats that can help us determine whether a particular index is being used. This view does not keep historical data but keeps the data only since the last time database was open. It does not keep the data after the database has been closed.

The sql below will retrieve data for a particular database

SELECT * FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('DatabaseName')

Below are some useful fields from sys.dm_db_index_usage_stats view:

user_seeks - either looking up a single row or doing a range scan
user_scans - select * operation performed on the table
user_lookups - a bookmark lookup where a non-clustered index does not fully cover a query and additional columns must be retrieved from the base table row
user_updates - number of times it was used in update operations (this included inserts, updates, and deletes)

Some useful queries:

--get database name based on database_id
select * from sysdatabases where dbid=7

--get object name based on object_id
SELECT name
FROM sys.objects
WHERE object_id = 37575172

--retrieves database_id of a current database
Select db_id()

--retrieves an index name based on object_id and index_id from sys.dm_db_index_usage_stats view
SELECT name FROM sys.indexes WHERE object_id = @object_id and index_id = @index_id

--retrieves index statistics, including table name to which the index belongs, and index name
SELECT object_name(iu.object_id, iu.database_id) as tablename, i.name as IndexName, iu.user_seeks,iu.user_scans,iu.user_lookups,iu.user_updates,iu.last_user_seek, iu.last_user_scan,iu.last_user_lookup,iu.last_user_update 
FROM sys.dm_db_index_usage_stats as iu
LEFT JOIN sys.indexes as i ON iu.index_id=i.index_id AND iu.object_id=i.object_id
WHERE iu.database_id = DB_ID('Northwind')

The query below will return a lit of all indexes with corresponding table names that have not been used by SQL Server since the service started

SELECT  ObjectName = OBJECT_NAME(ind.object_id),  IndexName = ind.name
FROM Sys.Indexes ind            
INNER JOIN Sys.Objects obj ON obj.object_id = ind.object_id
WHERE OBJECTPROPERTY(obj.object_id,'IsUserTable') = 1            
AND NOT EXISTS ( SELECT 1 FROM Sys.dm_db_index_usage_stats usg WHERE usg.object_id = ind.object_id AND usg.index_id = ind.index_id AND ind.index_id = usg.index_id)
ORDER BY ObjectName,IndexName

Login failed for user [name] error

So you have been moving database from one server or instance to another, you backed up and restored the database, scripted all logins, did everything in the book, and suddently, when loging in with sql server login, that worked so well before, you get "Login failed for user [name]" error.

First thing to check is whether your SQL Server is configured to use Mixed mode or Windows only authentication. because if it is configured to use Windows only, any sql login will be rejected, no matter what type of permissions it has.

Right-click on the server name, go to Properties, select Security and make sure "SQL Server and Windows Authentication mode" is selected.

Moving databases: Detach/Attach method vs. Backup/Restore

Personally, when moving a database from one server to another, I prefer using backup/restore since if I encounter any issues with SQL Server versions or something else, I can always restore backup somewhere else and I will also have a working copy of the database on the source server, while if anything goes wrong during the detach/attach process, I will not have a copy of the database elsewhere. My second reason is that backup/restore requires no downtime, which is always a big plus.

So far I have encountered only one case where using detach/attach scenario was better then backup/restore - I needed to move a database from the default instance to a named instance on the same server and I did not want to use backup/restore since I wanted mdf and ldf files to stay at the same location on the server. Also detach/attach is much faster than backup/restore since it only takes minutes to detach database, copy the files to another server and attach the database, and both backup and restore processes can take hours.

Fill factor explained

The Fill Factor specifies the % of fullness of the leaf level pages of an index. When an index is created or rebuilt the leaf level pages are written to the level where the pages are filled up to the fill factor value and the remainder of the page is left blank for future usage. This is the case when a value other than 0 or 100 is specified. For example, if a fill factor value of 70 is chosen, the index pages are all written with the pages being 70 % full, leaving 30 % of space for future usage.

You might choose a high fill factor value when there is expected little to no change on the underlying table. High fill factor value creates an index smaller in size and the queries on the underyling table can retrieve data with less disk I/O operations since there are less pages to read. But if you have an index that changes frequently, you want to have a lower value to keep some free space available for the new index entries. otherwise, if fill factor is large and there is little free space in the indexes, SQL Server would have to do a lot of page splits to fit the new values into the index pages.

the lower fill factor of your index, the larger the index size. if we take a fill factor of 50% increases index size to about 2 times the size of an index with fill factor of 100%.

With new data added to the table, the index page needs to have sufficient space to take the new entries. Where there is not enough space, a page split needs to take place, therefore impacting the performance.

SQL Server DBA Duties

DAILY

Action: Check Network Connectivity
Reason: To check that hardware & server is up.
To check that IP address & name have not been changed.
Gives early warning if server fails.
Checks IP address & name resolution (sometimes a problem with wins, dns, lmhosts).
Method: 1. Ping sql servers every 15 mins with IP Sentry.
2. Use batch file to ping servers.
3. Use a server monitoring tool.

Action: Check SQL services
Reason: To check that SQL server is available.
To check MSSQLserver & SQLexecutive/agent, DTS services are running.
To check that we can connect.
Method: SEM:
Green lights if SEM, or connect to each server by clicking on the ‘+’ for each server & open sql executive.

Action: Check Scheduled Tasks/ Jobs
Reason: Backups, DBCC checks, etc. are run overnight as scheduled tasks.
Method: SEM: Highlight server, servers | scheduled tasks

Action: Check dba_tools..scheduledtasklog
Reason: If get a failed scheduled task.
Some DBA’s create an error log table, which gives more detail on errors during task execution
Method: SEM: Open ISQL/W, Select * from dba_tools..scheduledtasklog.

Action: Check DBCC output
Reason: DBCC commands check DB integrity & consistency.
Finds errors in the structure of the server & databases.
If more than the usual ‘msg’ string errors, check BOL.
Method: Create desktop or folder shortcuts to point to the dbcc output text files.
Check date when checks done.

Action: Check Hard Disk Space
Reason: If system drives run low they crash.
Backup devices expand dynamically & will fail if insufficient space.
Need to know which partition is the system & which partition holds the backup devices.
Method: Windows explorer: Check drive properties
SEM: Check database devices, even if automatic.
ISQL/W: Create stored procedure or batch file, using xp_fixeddrives for each server.

Action: Check Database & Transaction Log Space
Reason: If DB or txn log space runs out, then transactions will fail. Ver 6.5
Method: SEM: Databases x2 click the DB
ISQL/W: sp_spaceused
www.SQLserverPortal.com
3rd party tools for SQL server

Action: Check Event Logs
Reason: OS errors are logged here & some SQL errors are logged here.
OS & SQL usually warns you before a problem becomes critical.
Method: Control panel, admin tools\event viewer\

Action: Check SQL Error Logs
Reason: SQL errors are logged here, SQL usually warns you before a problem becomes critical.
Method: SEM: servers | error log, scroll to bottom (ctrl+end) = most recent.
If get errors, tap in number into BOL.

WEEKLY

Action: Save all server configuration info.
Reason: You need more than just the db backup to rebuild a SQL server.
Method: ISQL/W: Create stored procedures to get config info. etc & output to txt files.

MONTHLY



Action: Do a test restore of a db backup
Reason: Sometimes backups don’t work eg due to errors found by DBCC commands.
Method: Create a test device, restore from tape to a bkup device, restore, then run DBCC checks on it.