Reindexing the entire database in sql server

Re-indexing the entire database:

Usually, if the database is too slow. DBA's were advised to re-index the tables. For some cases, we need to rebuild the indexes availables in the entire database.

For this scenario, we can rebuild the index by taking each table and re-index all the indexes associated with each table.

Fill factor:

Amount or compactness of data in the leaf level is defined by the term fill factor. Based on the operations on the database DBA's will decide the fill factor. If the insert/update/delete are very high in that case we will have very less fill factor (Around 60 to 70). If there is very less insert/update/delete, in that case we will have very high fill factor (Around 90). On an average, we will give 80 -90 %.



Below is the script to achieve this,

----------------------------------------------------------------------------
DECLARE @DatabaseTable VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT name AS DatabaseTable
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @DatabaseTable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'DBCC DBREINDEX('+ @DatabaseTable + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @DatabaseTable
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

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

How abt re-organsing the indexes (Rebuild index vs reorganise index):

1. Re-built can't be done on the production run time (Due to its high impact on rearranging the data and recreate of index) where as re-organise index can be done.

2. Indexes were recreated in case of re-building the index.

3. Re-built index is very effective when compared to the other.

To find the free space available in the log files:

Below is the command used to identify the amount the space available or percentage used.

DBCC SQLPERF (LOGSPACE)

Reindexing the entire database in sql server

Re-indexing the entire database:

Usually, if the database is too slow. DBA's were advised to re-index the tables. For some cases, we need to rebuild the indexes availables in the entire database.

For this scenario, we can rebuild the index by taking each table and re-index all the indexes associated with each table.

Fill factor:

Amount or compactness of data in the leaf level is defined by the term fill factor. Based on the operations on the database DBA's will decide the fill factor. If the insert/update/delete are very high in that case we will have very less fill factor (Around 60 to 70). If there is very less insert/update/delete, in that case we will have very high fill factor (Around 90). On an average, we will give 80 -90 %.



Below is the script to achieve this,

----------------------------------------------------------------------------
DECLARE @DatabaseTable VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT name AS DatabaseTable
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @DatabaseTable
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'DBCC DBREINDEX('+ @DatabaseTable + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @DatabaseTable
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

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

How abt re-organsing the indexes (Rebuild index vs reorganise index):

1. Re-built can't be done on the production run time (Due to its high impact on rearranging the data and recreate of index) where as re-organise index can be done.

2. Indexes were recreated in case of re-building the index.

3. Re-built index is very effective when compared to the other.

To find the free space available in the log files:

Below is the command used to identify the amount the space available or percentage used.

DBCC SQLPERF (LOGSPACE)

Performance monitoring command in SQL Server

DBCC SQLPERF(umsstats) - This command will provide all the OS related information for your threads in the SQL Server instance. It provides the below values,

Node Id
Avg Sched
LoadSched
SwitchesSched Pass
IO Comp Passes
Scheduler ID (The below data will be populated for each scheduler ID)
online - Whether it's online or not
num tasks - Number of tasks
num runnable - Runnable tasks
num workers - Parallel workers involved in the scheduler
active workers - Active parallel workers involved in the scheduler
work queued - Works queued up in this schedule
cntxt switches - How many context switching happening in this schedule
cntxt switches(idle) - Idle threads after context switches.
preemptive switches - Prirority switches happened in this schedule.

Get details on the table contigencies

DBCC Showcontig will give a detailed insight on the data pages(OS level) for the tables.

This command will scan the table and provide the below details,

- Pages Scanned......................... - Number of pages scanned (Denotes the number of pages occupied by this table )
- Extents Scanned..............................: Number of Extents scanned (Denotes the number of Extents occupied by this table )
- Extent Switches..............................: Mixed extents
- Avg. Pages per Extent........................
- Scan Density [Best Count:Actual Count].......: How the data is compacted in the page.
- Extent Scan Fragmentation ...................: Fragmentation from Extent point of view
- Avg. Bytes Free per Page.....................
- Avg. Page Density (full) ...................... (High page density denotes the data is intact and your search will be faster. If the value is less, it indicates your data is scattered.

DBCC SHOWCONTIG --- This command will provide the details for the whole database tables.

DBCC SHOWCONTIG ('Murli_table') -- This command will provide the necessary information for that particular table.

Get Databasename from Database ID in SQL Server

DB_Name(DB_ID) will provide you the database name. The hierarchy is like system tables will have the initial values followed by user databases.

select DB_NAME(1) --- returns Master database
select DB_NAME(2)--- returns Tempdb database
select DB_NAME(3)--- returns Model database
select DB_NAME(4)--- returns MSDB database
select DB_NAME(5)--- returns User database1 etc..,

To find recently executed queries in SQL Server

To find recently executed queries,

Select dmStats.last_execution_time as 'Last Executed Time',dmText.text as 'Executed Query' from sys.dm_exec_query_stats as dmStats Cross apply sys.dm_exec_sql_text(dmStats.sql_handle) as dmText Order By
dmStats.last_execution_time desc

Update records form diff tables

This blog post illustrates how to update more than one column in a table with values from columns in another table and explains how to do it in the three RDBMS that we support.

Table Structures and values:

TableA has four columns: a, b, c, d (a is the primary key column)
TableB has five columns: a1, b1, c1, d1, e1 (a1 and b1 together constitute the primary key for this table)

The foreign key relationship between the two tables is based on A.a = B.a1

The data in these 2 tables is as follows:

I.    TableA
a    b    c    d
1    x    y    z
2    a    b    c
3    t    x    z
 
II.    TableB
a1    b1    c1    d1    e1
1    x1    y1    z1    40
2    a1    b1    c1    50

The requirement is to write a SQL to update columns b, c and d in TableA from the columns b1, c1 and d1 from TableB where-ever the join condition satisfies and e1 > 40 in TABLEB.

Oracle:

UPDATE TABLEA
SET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
/

Results after the update:
 
a    b    c    d
————————————
1     x          y           z
2     a1         b1         c1
3     t          x          z
 

SQL Server:

UPDATE TABLEA
SET b = TABLEB.b1,
c = TABLEB.c1,
d = TABLEB.d1
FROM TABLEA, TABLEB
WHERE TABLEA.a = TABLEB.a1
AND TABLEB.e1 > 40
GO

Note: This is an extension in SQL Server i.e. the FROM clause – it does make it simple to understand and is a nice feature.

Results after the update:
 
a    b    c    d
————————————
1     x          y          z
2     a1         b1         c1
3     t          x          z
 

DB2 LUW:

–Same as Oracle–

UPDATE TABLEA
SET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40);

Results after the update:
 
a    b    c    d
————————————
1     x         y           z
2     a1        b1         c1
3     t         x         z
 

NOTE:

It is very important to make sure that your where clause for the update statement is correct since that is what identifies the records that the update statement is going to qualify and do the update upon. If it is incorrect, then you can get wrong results. The reason I am mentioning this is because I have seen people write wrong where clauses and then wondering what went wrong because they specified the correct condition in the SET clause.

In the above example, if the Where condition was omitted, the other record’s columns would be updated to NULL value and this will be the final result set:

a    b    c    d
————————————
1     Null      Null      Null
2     a1        b1         c1
3     Null     Null      Null