SQL Index Fragmentation and sys.dm_db_index_physical_stats

Fragmentation of an index can severely affect performance. When logical ordering of the key within a page does not match the physical ordering within the data file, fragmentation exists.

I execute index maintenance scripts  for databases on a regular basis.  If I’m executing a  custom job, such as a large UPDATE , I may need to analyse just one index on a single table.
Use the SQL views: sys.sysdatabases,  sys.sysobjects,  sys.sysindexes  to find  , all the relevant ids.  Push the ids as values on the sys.dm_db_index_physical_stats view.
sys.dm_db_index_physical_stats present fragmentation information for data and indexes.

USE MyDB
GO
DECLARE @ixId INT,@dbase_id INT,@table_id INT
SELECT @dbase_id=dbid FROM sys.sysdatabases  WHERE name = 'MyDB'
SELECT @table_id=id FROM sys.sysobjects WHERE name = 'MyTable' AND xtype = 'U'
SELECT @ixId=indid FROM sys.sysindexes WHERE id=OBJECT_ID('dbo.MyTable') and [name] = 'MyIndex'

SELECT * FROM sys.dm_db_index_physical_stats(@dbase_id,@table_id,@ixId,NULL,'LIMITED')

SQL Server - Find last time STATISTICS updated - update statistics

Maintaining statistics is an important factor for SQL Server performance. Accurate information allows SQL Server to make more effective decisions on how  to execute SQL Server code.
The SQL Server optimizer uses cardinality estimations as part of the decision making process. If inaccurate data distribution statistics exist for a table or index, then the Optimizer will make inefficient decisions
To find out the last time statistics were updated on a table or index , use the sys.stats view. This view has a row for each statistic of a tabular object.
The sys.stats view does not provide histogram data. You’ll need to use DBCC SHOW STATISTICS .

 
use db_name
go
DBCC SHOW_STATISTICS('a_table',a_statistic)

Sp_updatestats RESAMPLE option

sp_updatestats  executes UPDATE STATISTICS against all the tables on a database, that require an update. Sp_updatestats accepts the @resample argument. This forces the UPDATE STATISTICS resample option.
Using  RESAMPLE updates the statistics based on the latest sample rate.  To view the latest sample rate use the DBCC SHOW STATISTICS command.

 
use db_name
go
DBCC SHOW_STATISTICS('a_table',a_statistic)

 These SQL Server performance problems  could be avoided by some proactive reporting \  database server maintenance .
 1)       Fragmentation
2)       No Indexes or Index Problems
3)       SQL Server Backups at  peak usage time
4)       Inefficient queries – Bad Estimates creating hash joins and index scans
5)       Incorrect plan used by Stored Procedure
6)       Inaccurate\missing statistics
7)       TempDB performance issues
8)       Log Files on incorrect drive
9)       To much data retrieval
10)      DBAs not troubleshooting performance problems properly

SQL Server SLEEPING MODE , locks and transactions

 Is a SQL Server transaction holding a lock on resources when the client aborts the operation?
 I receive a regular question “What does the SQL Server sleeping state mean , and are resources locked?”
 Let’s investigate .
 Activity Monitor lists information about SQL Server processes. A session in the sleeping state means a client connection without an active query.
But , If a client :
1)creates a session ,
2)submits a long running transaction
3)No commit or rollback is executed
4)Client application states a query timeout of 60 seconds and the transaction hasn’t completed (or the connection is broken )
5)The SQL server session will go into a sleeping state. And maintain locks
 To test ,  the steps above do the following –
 On the SQL Server . Assuming there is a “testable”
 CREATE PROCEDURE MYSPSLEEPING
AS
 BEGIN TRAN
 INSERT INTO testtable (ID,avalue) VALUES(1 ,'myvalue')
 WAITFOR DELAY '0:5:10'--
 ROLLBACK

SQL Server - SQL open transactions and how to find

Which SQL open transactions are causing a performance issue?

 Method 1   - DBCC OPENTRAN() and DBCC INPUTBUFFER()

 

Method 2 – Check for Open Transactions in SYS.SYSPROCESSES

 Use this SQL Statement , using SYS.SYSPROCESSES  and CROSS APPLY to SYS.DM_EXEC_SQL_TEXT
 SELECT SP.SPID,[TEXT] as SQLcode FROM SYS.SYSPROCESSES SP
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.[SQL_HANDLE])AS DEST WHERE OPEN_TRAN=1

 

SQL Blocking script

SELECT * FROM dbo.sysprocessesWHERE blocked <> 0;
SELECT * FROM dbo.sysprocesses WHERE spid IN (SELECT blocked FROM dbo.sysprocesses where blocked <> 0);

SQL Server - BackUp All Databases 

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Backup\' 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR 
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') 

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
       BACKUP DATABASE @name TO DISK = @fileName 

       FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

SQL Server - BACKUP LOG WITH NO_LOG

The BACKUP LOG WITH NO_LOG and WITH TRUNCATE_ONLY options are discontinued in SQL Server 2008.
There is no replacement for this functionality. When the command is executed there is the message suggesting to switch to Simple Recovery.
Of course , a SQL Server DBA knows that this breaks the Log Chain, and a full backup is required to start a new Log Chain.
There are plenty of  DBA scripts in Production environments that use BACKUP LOG WITH NO_LOG in SQL Server 2005 . Some thoughts to consider:
 1)      NO_LOG and TRUNCATE_ONLY are  similar
2)      Switching from FULL Recovery to SIMPLE Recovery will break the log chain.
3)      Using  NO_LOG in SQL Server 2005 will create a BACKUP with no logs
4)      If used with BACKUP LOG , a checkpoint is forced. The transaction log is truncated.
5)      If using NO_LOG , the truncated portion is not recoverable. Good practise is to commit a full BACKUP after the NO_LOG  (or TRUNCATE_ONLY)
6)      Use the NO_LOG only if absolutely necessary.
7)      If Log file growth management is required – use monitoring and regular BACKUPs to manage growth

Fill Factor

Fillfactor will specify how full sql server should make each index page when it creates a new index using existing data.ie, it specify how full each page in the leaf level of an index should be. Index value can be from 0 thruough 100.

*  Fill factor 100 implies the leaf Pages(ie;data pages if clustered index, else FID(FileID) +PN(Page Number) +RN (Row Number)) will be 100 percent full.

*  Fill factor 0 implies, the leaf pages will be almost full, but SQL Server leaves some space within the upper level of the index tree. In both the cases even though it fills the leaf pages(data pages), the root and intermediate pages will still have room for two additional rows (1 root + 1 intermediate).

*   Fill factor from 1 through 100, will be the percentage of each leaf page to fill with rows.

”The fillfactor is used only when you create the index,it is not maintained over time.This value is not maintained after index creation, you index leaf pages may become full and experience many page splitting even you specify a very low value at the beginning.”

Generally, when page splitting occurs, half of the data rows will be moved to the newly allocated data page, and half of the rows are remained on the original page. the Fill Factor is not maintained during the spliting. For example, if the Fill Factor is 40% full, in this case, the value is not maintained. One exception may be, when inserting a row containing variable-length columns, if this row is very large, more data rows will be kept on the other page to vacate space for this big-size row.

Select an appropriate fill factor for each index. If the data has a minimal amount of changes to the middle of the table, configure the indexes to have a high fill factor, i.e., closer to 100%, which will save on the storage needed. If the data has many changes to the middle of the table, select a lower fill factor, i.e., 65% to 85%, so that as data is added to a page, page splitting is minimized until the indexes are rebuilt.

Also,

Fill factor 100%                      ==>  If DB is read only
Fill factor b/w 50% and 70%   ==>  If DB is write intensive (writes greatly exceed reads)
Fill factor b/w 80% to 90%      ==>  If DB is both read and write intensive

http://sqlserver.in/blog/index.php/archive/category/administration/index/

Archives for RDBMS Concepts category

Normalization

*  In relational database design, the process of organizing data to minimize duplication.
*  Normalization usually involves dividing a database into two or more tables and defining relationships between the tables.
*  The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just
one table and then propagated through the rest of the database via the defined relationships.”
“Normalization” refers to the process of creating an efficient, reliable, flexible, and appropriate “relational” structure for storing information. Normalized data must be in a “relational” data structure.
1st Normal Form
1st Normal Form ==> There should be no repeating groups.
For instance consider the following structure
Student Table

StudentID (PK)

Name

Dob

Advisor

Advisor Telephone

Advisor Address

Course

Course Instructor

Problem to Fix : A student can take multiple courses and hence repeating groups.
Soultion : Make a separate table for course & link it to student table using StudentID. This will results in the below design
Student Table

StudentID (PK)

Name

Dob

Advisor

Advisor Telephone

Advisor Address

Courses table

ID (Course ID) (Primary Key -> Composite Key ID + StudentID)

Course

Course Instructor

StudentID

2nd Normal Form ==> No Non-Key field should depend on the part of the primary key.
Problem to Fix : Here in Courses table the primary key is a composite key ID + StudentID. (student ID is not unique in itself, as one student may take multiple courses; similarly, course ID is not unique in itself as many students may take the same course; however, each student will only be taking a particular course once at any one time, so the combination of student ID + course ID gives us a unique primary key). And a non-key field Course Instructor is depending on the Course ID alone, which is only a part of the primary key. Solution : To fix this we create a third table, to get the below structure for our database.
Student Table

StudentID (PK)

Name

Dob

Advisor

Advisor Telephone

Advisor Address

Courses table

ID (Course ID) (PK)

Course

Course Instructor

Student Courses table

Student ID (Primary Key ==> Composite Key)

Course ID

3rd Normal Form ==> No fields may depend on other non-key fields. Ie each field in a record should contain information about the entity that is defined by the primary key.
Problem to Fix : Here in student table, the primary key is Student ID & the non-key fields like Advisor Telephone & Advisor Address cannot be defined with the Primary key. Solution : To fix this we need to create a 4th table Advisor table, to get the below structure.
Student Table

StudentID (PK)

Name

Dob

Advisor ID

Courses table

ID (Course ID) (PK)

Course

Course Instructor

Student Courses table

Student ID (Primary Key ==> Composite Key)

Course ID

Advisor table

ID (Advisor ID) (Primary Key)

Advisor

Advisor Telephone

Advisor Address

The database is in 3rd normal form now

Setting the ‘At Startup’ Options in SQL Server Management Studio

When you connect to SQL Server using SQL Server Management Studio, by default it shows the Object Explorer.
You can change this default option in SQL Server Management Studio:
click Tools -> Options

Click: Environment / General

In the ‘At Startup’ drop down, choose one option from the drop down list and click OK.
SQL Server Management Studio must be restarted for this change to take effect.
You can choose between these options:
Open Object Explorer
Open New Query Window
Open Object Explorer and New Query
Open Object Explorer and Activity Monitor
Open Empty Environment

Hide System Objects in Object Explorer in SQL Server Management Studio

By default you can see SQL Server system objects using SQL Server Management Studio.
In the Databases node of Object Explorer, System Databases are shown:

System Stored Procedures are shown also:

To hide system objects in Object Explorer, in SQL Server Management Studio:
click Tools -> Options

Click: Environment

select Hide system objects in Object Explorer -> click OK:

SQL Server Management Studio must be restarted for this change to take effect:

After you close and reopen SQL Server Management Studio, System Databases are not shown:

System Stored Procedures are also not shown:

System functions are not affected by this setting:

System data types are also not affected by this setting:

List all indexes for all tables in a SQL Server database

To list all indexes for all tables in a database, you can use this code (in this example we use AdventureWorks sample database):

USE AdventureWorks
GO
SELECT DB_NAME() AS Database_Name
, sc.name AS Schema_Name
, o.name AS Table_Name
, i.name AS Index_Name
, i.type_desc AS Index_Type
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.name IS NOT NULL
AND o.type = 'U'
ORDER BY o.name, i.type

Find Start Time of SQL Server

There are several ways to find out when your SQL Server instance has been started.
One of the Server Standard Reports shows the SQL Server instance startup time:
Connect to SQL Server Instance -> Right Click on instance name -> Click Reports -> Click Standard Reports -> Choose Server Dashboard Report:

For details what other Standars Reports are there in SQL Server and how to open them, read previous blog post:
Standard Reports in SQL Server Management Studio
In Windows Event Viewer you can find the startup time of SQL Server service. Filter the Aplication Event Sources by instance name, and Event ID 17162:


To find out the SQL Server instance start time in the current SQL Server error log in SQL Server Management Studio:
Management -> SQL Server Logs
Find the “SQL Server is starting” line at the bottom of the log. If the log was cycled, open previous logs until you find the SQL Server start time:

You can query sys.dm_exec_sessions Dynamic Management View that shows information about all active connections and find out the login time of the Session Id 1, that is created when the SQL Server is started:
SELECT login_time
FROM sys.dm_exec_sessions WHERE session_id=1

The tempdb system database is re-created every time SQL Server starts, so by finding out the creation time of tempdb, you can find out the start time of SQL Server instance (database ID of tempdb database is always 2):

SELECT create_date
FROM sys.databases where database_id=2

If the Default Trace is not explicitly disabled on your SQL Server instance, you can find out the start time of SQL Server instance by finding out the start time of the Default Trace, because the Default Trace is started when the SQL Server is started:

SELECT start_time FROM sys.traces
WHERE is_default = 1

In SQL Server 2008 and SQL Server 2012 you can query sys.dm_os_sys_info Dynamic Management View, but it requires VIEW SERVER STATE permission on the server:
SELECT sqlserver_start_time
FROM sys.dm_os_sys_info

There is a small time difference between the results, depending on the method you used to determine the SQL Server start time.

Rename logical and physical names of database files in SQL Server

To find the current logical and physical database file names:
USE Database_name
EXEC sp_helpfile
or
USE master
GO
SELECT
name AS [Logical_name],
physical_name AS [File_Path],
type_desc AS [File_Type],
state_desc AS [State]
FROM sys.master_files
WHERE database_id = DB_ID(N'Database_name')
GO
To rename the Logical names of database files:

ALTER DATABASE [Database_name] MODIFY FILE (NAME=N'Logical_file_name', NEWNAME=N'Logical_file_name_new')
GO
ALTER DATABASE [Database_name] MODIFY FILE (NAME=N'Logical_file_name_log', NEWNAME=N'Logical_file_name_log_new')
GO
Or using SQL Server Management Studio:
Right-click Database -> Click Properties -> Click Files
Rename Logical Names and click OK:

To rename the physical names of database files:
put the database in single user mode, detach the database, then rename the physical files:
ALTER DATABASE Database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'Database_name'
GO
Rename the physical files.
After you renamed the physical files, attach the database, using this script:
USE [master]
GO
CREATE DATABASE Database_name ON
( FILENAME = N'D:\DATA\Database_file_name.mdf' ),
( FILENAME = N'D:\DATA\Database_file_name_log.ldf' )
FOR ATTACH
GO
Put database in multi user mode again:
ALTER DATABASE Database_name SET MULTI_USER
GO
Or using SQL Server Management Studio:
Right-click Database -> Click Tasks -> Click Detach…
Rename the physical files.
After you renamed the physical files, attach the database:
Right-click Database -> Click Tasks -> Click Attach…

How to rename a database in SQL Server

To rename a database in SQL Server use this code:
USE master;
GO
ALTER DATABASE old_database_name
Modify Name = new_database_name;
GO
To rename a database using SQL Server Management Studio:
Right-click the database you want to rename, and then click Rename:

Enter the new database name, and then click OK.
Make sure that no one is using the database, and then set the database to single-user mode before renaming the database.
System databases cannot be renamed.
When a database is renamed, filegroup names and file names (.mdf, .ldf) are not changed.
To see how to rename logical and physical names of database files in SQL Server read this blog post:
Rename logical and physical names of database files in SQL Server
It is recommended to back up the master database after you rename any database.

Launch SQL Server Management Studio from Command Prompt

SQL Server 2005:
To launch SQL Server Management Studio 2005 from Command Prompt:
Click Start -> Run -> Type sqlwb.exe
or Start -> Run -> cmd -> Type sqlwb.exe

You can pass different parameters with the command. To see the list of parameters:
Click Start -> Run -> Type sqlwb.exe /? or sqlwb.exe -?
or Start -> Run -> cmd -> Type sqlwb.exe /? or sqlwb.exe -?

Usage:
sqlwb.exe [-S server_name[\instance_name]] [-d database] [-U user] [-P password] [-E] [file_name[, file_name]] [/?]
[-S The name of the SQL Server instance to which to connect]
[-d The name of the SQL Server database to which to connect]
[-E] Use Windows Authentication to login to SQL Server
[-U The name of the SQL Server login with which to connect]
[-P The password associated with the login]
[file_name[, file_name]] names of files to load
[-nosplash] Supress splash screen
[/?] Displays this usage information

SQL Server 2008/2012:
To launch SQL Server Management Studio 2008 or SQL Server Management Studio 2012 from Command Prompt:
Click Start -> Run -> Type SSMS.exe
or Start -> Run -> cmd -> Type SSMS.exe

You can pass different parameters with the command. To see the list of parameters:
Click Start -> Run -> Type ssms.exe /? or ssms.exe -?
or Start -> Run -> cmd -> Type SSMS.exe /? or ssms.exe -?

Usage:
ssms.exe [-S server_name[\instance_name]] [-d database] [-U user] [-P password] [-E] [file_name[, file_name]] [/?]
[-S The name of the SQL Server instance to which to connect]
[-d The name of the SQL Server database to which to connect]
[-E] Use Windows Authentication to login to SQL Server
[-U The name of the SQL Server login with which to connect]
[-P The password associated with the login]
[file_name[, file_name]] names of files to load
[-nosplash] Supress splash screen
[/?] Displays this usage information

Difference between CONTROL SERVER and sysadmin membership in SQL Server

Difference between CONTROL SERVER permission and sysadmin fixed server role:
- logins with CONTROL SERVER permission are checked for explicit DENY of server level permissions
- members of sysadmin fixed server role bypass the check for explicit DENY of server level permissions.
To test this, we will grant test_permissions login CONTROL SERVER rights, and DENY CONNECT SQL for that login:
USE master;
GO
GRANT CONTROL SERVER TO test_permissions;
GO
DENY CONNECT SQL TO test_permissions;
GO
When test_permissions login attempts to connect, there will be the login failed message:

If we make the test_permissions login a member of the sysadmin fixed server role:
EXEC sp_addsrvrolemember 'test_permissions', 'sysadmin';
GO

…the login will be able to connect because the members of sysadmin fixed server role bypass the explicitly denied server level permission check.
To list the members of the sysadmin fixed server role, you can use the system stored procedure sp_helpsrvrolemember:
EXEC sys.sp_helpsrvrolemember 'sysadmin';
To list logins with CONTROL SERVER permission, you can use this code:
SELECT s.name
FROM sys.server_principals s
JOIN sys.server_permissions p
ON s.principal_id = p.grantee_principal_id
WHERE p.class = 100
AND p.type = 'CL'
AND (p.state = 'G'
OR p.state = 'W')

Unable to shrink the transaction log of SQL Server database

It is possible that even after trying to shrink the transaction log of a database, the ldf file size remains the same even if there is a lot of available free space in the log.
Because the active part of the log cannot be truncated or removed by shrinking, truncation can be delayed when log records remain active for a long time.
To see what is the log_reuse_wait for the transaction logs of databases run this query:
select name, database_id, log_reuse_wait, log_reuse_wait_desc from sys.databases

Reuse of transaction log space can be waiting on one of the following:
0 = Nothing
1 = Checkpoint
2 = Log backup
3 = Active backup or restore
4 = Active transaction
5 = Database mirroring
6 = Replication
7 = Database snapshot creation
8 = Log Scan
9 = An AlwaysOn Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database.
10 = For internal use only
11 = For internal use only
12 = For internal use only
13 = Oldest page
14 = Other (transient)

SQL Server 2008 R2:
http://msdn.microsoft.com/en-us/library/ms345414%28v=sql.105%29.aspx
SQL Server 2012:
http://msdn.microsoft.com/en-us/library/ms178534.aspx