Index Fragmentation in SQL Server 2005

Index fragmentation is a phenomenon where the index contents are scattered. Normally the contents are in contiguous fashion which helps in fast retrieval of the underlying data. When the indexes are fragmented the data access becomes time consuming because of the scattered data that needs to be searched and read. Fragmentation occurs as data is modified. The following are the two types of Index fragmentation:

1. Internal fragmentation
2. External fragmentation

Internal fragmentation: This happens when space is available within your index page i.e. when the index pages have not been filled as full as possible. Due to internal fragmentation the index is taking up more space than it needs to. Thus when scanning the index it results in more read operations. Internal fragmentation also happens due to specifying a low value of fill factor (which determines the % of space to be filled in a leaf level page). This is also caused by rows that are removed by DELETE statements or when pages are split and only filled to about half. Empty space on pages means there are less rows per page, which in turn means more page reads.

 External Fragmentation: External fragmentation occurs when the pages are not contiguous on the index. If the pages in a book are NOT ordered in a logical way (page 1, then page 2, then page 3 and so on) causing you to go back and forward to compound the information and make sense of the reading. External fragmentation happens when there are frequent UPDATES and INSERTS in a table having small amount of free space in the index page. Since the page is already full or only has less free space left and if it is not able to accommodate the new row inserted or updated, as a result Page split happens in order to allocate the new row. Due to page split, original page will be split such that half the rows are left on the original page and the other half is moved to the new page. Mostly the new page is not contiguous to the page being split. Page split is an expensive operation and should always be avoided. How to determine fragmentation ? The following query will give the fragmentation information of a particular table named person.address in adventureworks database. Please modify the query to replace the database name and table name according to your requirements.

1 2 3

SELECT CAST(DB_NAME(database_id) AS VARCHAR(20)) AS [DATABASE Name], CAST(OBJECT_NAME(OBJECT_ID) AS VARCHAR(20)) AS [TABLE NAME], Index_id, Index_type_desc, Avg_fragmentation_in_percent, Avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),OBJECT_ID('person.address'),NULL,NULL,'Detailed')

If you wish to identify the fragmentation information for the tables in a particular database please use the below query. I am using it to find the fragmentation in Adventureworks database.

1 2 3

SELECT CAST(DB_NAME(database_id) AS VARCHAR(20)) AS [DATABASE Name], CAST(OBJECT_NAME(OBJECT_ID) AS VARCHAR(20)) AS [TABLE NAME], Index_id, Index_type_desc, Avg_fragmentation_in_percent, Avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),NULL,NULL,NULL,'Detailed')

Take a look at the output of the following columns in the above query: Avg_fragmentation_in_percent: If the value is >5 and <30 you need to REORGANIZE the index using ALTER index REORGANIZE command. If the value is >30 you need to REBUILD the indexes using ALTER index REBUILD command. Avg_page_space_used_in_percent: This value represents the amount of page space used in an index. If the value is <75% and >60% we need to REORGANIZE the indexes else REBUILD the indexes. Defragmenting the Indexes: We need to either use ALTER INDEX REBUILD or ALTER INDEX REORGANIZE commands to remove the fragmentation from the indexes. Generally its advisable to schedule a job to do this operations in OFF-Production hours as they consume lots of resources.

 

SQL Server 2005 Best Practices

 article lists the best practices for SQL Server 2005 Tempdb:

1. Set recovery model as Simple.
2. Allow tempdb files to grow automatically until the disk is full
3. Set the file growth for tempdb in terms of 10%
4. Enable Instant file initialization to speed up Auto growth, addition of new files
5. Put tempdb database files in a separate drive different from those of user databases
6. Create multiple tempdb database files and place them in different drives

Memory:

1. Physical Address Extension (PAE) enables x86 processors to access up to 128 GB of physical memory. X64 windows does not support this option
2. The /3GB switch makes a full 3 GB of virtual address space available to applications and reduces the amount available to the system to 1 GB
3. Using /PAE with /3GB limits the operating system (32 bit) with only 16GB of physical memory
4. AWE is a set of extensions to the memory management functions of Windows that allow applications to address more memory than the 2-3 GB that is available through standard 32-bit addressing. AWE is not required and cannot be configured on 64-bit operating systems.
5. Enable lock pages in memory in x64 if SQL Server memory is getting paged out
6. Enable max and min server memory settings while running multiple instances of SQL Server

Database Engine:

1. Database backup files and database files must be on separate volumes
2. Place the log files in uncompressed drives
3. For high critical databases use Full Recovery model with regular transaction log backups
4. Enable default trace to capture DDL changes
5. Enable Instant File initialization for databases to speedup auto growth
6. Enable Blocked process threshold to capture blocked processes
7. Use the same collation for user and system databases
8. Set the database option AUTO_Close to OFF for all databases
9. Set the database option AUTO_Shrink to OFF for all databases
10. Set the max degree of parallelism option to 8 or less
11. Set the Auto_update_statistics option to ON for all databases

Replication:

1. Enable Latency warning for transactional replication topology
2. Replicate stored procedure execution when making batch updates to published tables.
3. Set XACT_Abort ON while replicating stored procedures
4. Run snapshot agent only during off-peak hours
5. Place the snapshot folder on a drive local to the Distributor that is not used to store database or log files.
6. Consider specifying a recovery model of simple or bulk-logged while applying snapshot to a subscriber.

Optimizing Indexes:

1. Rebuild the indexes if the fragmentation is 30% or above else reorganize the indexes
2. Perform them during off-peak hours since it requires a lot of resources and causes blocks
3. Set the Fill factor as 100 for highly read only tables with less number of updates
4. Set the Fill factor as 70 for highly write intensive tables
5. Set the Fill factor as 80 to 90 for highly read and write intensive objects
6. Check the integrity of all the databases using DBCC Checkdb for every 14 days
7. Set the AUTO_UPDATE_STATISTICS option to ON to automatically update the statistics
8. Set the AUTO_UPDATE_STATISTICS_ASYNC to ON to use asynchronous statistic updating
9. Set the STATISTICS_NORECOMPUTE to OFF in the Alter index statement to enable auto update statistics
10. CREATE INDEX and ALTER INDEX REBUILD are always minimally logged in Bulk_logged and Simple recovery model, but the rest are always fully logged so its advisable to change the recovery model to Bulk logged or Simple during these operations

Read more: http://sql-articles.com/articles/performance-tunning/sql-server-2005-best-practices/#ixzz2TuLoZi5X

How to Get Exclusive Access to SQL Server Database

Introduction

There are scenarios when database administrator needs to disconnect all the connected users from a SQL Server Database to get exclusive access of the Database. The exclusive access is needed before restoring a database, before a database can be detached, to perform critical maintenance tasks, to recover a database which is in Suspect Mode etc. In this article we will take a look at how database administrator can leverage ALTER DATABASE Commands to disconnect users from a database.

Using ALTER DATABASE SET Options

Database administrator can executed ALTER DATABASE command to get exclusive or restricted access of a database.

Get Restricted Access to a SQL Server Database

Database administrator can execute the below mentioned TSQL command to get Restrictive access to a database.

ALTER DATABASE DatabaseName
SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO

Get Single User Access to a SQL Server Database

Database administrator can execute the below mentioned TSQL command to get a Single User access to a database.

ALTER DATABASE DatabaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

Give Multi User Access to a SQL Server Database

Database administrator can execute the below mentioned TSQL command to give Multiple User access to a database.

ALTER DATABASE DatabaseName
SET MULTI_USER
GO

Difference Between SINGLE_USER, RESTRICTED_USER & MULTI_USER Alter Database SET Commands

SINGLE_USER

When SINGLE_USER WITH ROLLBACK IMMEDIATE command is used only one user can connect to the database at a time.

RESTRICTED_USER

When RESTRICTED_USER WITH ROLLBACK IMMEDIATE command is used any number of users who are in DB_OWNER, DB_CREATOR or SYSADMIN roles can connect to the database.

MULTI_USER

When MULTI_USER command is used any number of users who have rights to connect to the database will be able to connect to the database.

How to Identify Currently Used SQL Server Authentication Mode

Introduction Microsoft SQL Server Support basically supports two types of Authentication Modes to using which one can connect to an instance of SQL Server 2000 and higher versions. Two different authentication modes are Windows Authentication Mode & SQL Server and Windows Authentication Mode (Mixed Mode). In this article we will take a look at how you can verify the currently used authentication mode and how to change the authentication mode using TSQL and SQL Server Management Studio.

Different ways by which SQL Server Authenticate Users

Windows Authentication Mode: - When Windows Authentication Mode is used only windows logins can connect to SQL Server. Windows authentication is much more secure when compared to mixed authentication as in an enterprise environment Windows Login credentials are generally Active Directory domain credentials.

Mixed Mode / SQL Server and Windows Authentication Mode Authentication: - When Mixed Mode aka SQL Server and Windows authentication mode is used either Windows Logins or SQL Server Logins can be used to connect to SQL Server.

Identify SQL Server Authentication Mode Using TSQL

Database administrator can use the below mentioned TSQL code to identify the currently used SQL Server Authentication Mode.

Use Master
GO

SELECT
            CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
            WHEN 0 THEN 'Mixed Mode - Allows Both SQL Server and Windows Authentication Mode'
            WHEN 1 THEN 'Allows Only Windows Authentication Mode'
END AS [Currently Used SQL Server Authentication Mode]
GO

You can execute the below mentioned TSQL code to identify the login security configuration on an instance of SQL Server. As per Microsoft one can use this only for backward compatibility purpose.

Use Master
GO

EXEC xp_loginconfig
GO

Identify SQL Server Authentication Mode Using SQL Server Management Studio

Database administrators can also identify the SQL Server Authentication Mode using SQL Server Management Studio.
1. Connect to an Instance of SQL Server using SQL Server Management Studio
2. In Object Explorer, right click the SQL Server Instance and select Properties from the drop down list.

Q: Which solution, SAN Replication, Windows and SQL Clustering, or Database Mirroring should be used for which situations?
A: These are three very different technologies that for the most part aren't competing with each other, but instead are complementary to each other.
SAN Replication vs. SQL Clustering
There's no competition at all here. In fact when doing a geographically distributed cluster (where the SQL Cluster is physically located in two different data centers) SAN Replication is required to handle keeping the data in sync between the two sites. The SAN Replication runs under the cluster so that both storage arrays (one in each site) are kept in sync using either synchronous or asynchronous replication.
SAN Replication vs. Database Mirroring
SAN Replication and Database Mirroring are sort of competing technologies as they both do data replication from one location to another. However when using SAN Replication there isn't a way to automatically fail over like there is with database mirroring.  SAN Replication can fail over automatically when used with a Windows Cluster and when you have software which is installed on the Windows Cluster and tells the storage array to fail over. However without a Windows Cluster in place, just having a server sitting waiting for the storage array to present the LUN(s) to the backup server would probably require either some impressing scripting to get the storage array up and running automatically or some manual configuration.
Related: Reasons not to configure automatic failover for database mirroring
SQL Clustering vs. Database Mirroring
With SQL Server clustering and database mirroring we are getting into a competition as SQL Server clustering and database mirroring are semi-competing features. With SQL (and Windows) clustering the database instance runs on one of several machines which are all grouped together.  All the databases are failed over from one machine to another as a group using the same name and IP address. The downside with SQL clustering is that there is the requirement of having shared storage as either a shared disk array or a large storage array.
With database mirroring the databases are failed over individually not in a group. With database mirroring however the newer SQL Server Native Client needs to be used so that database failover can be automatic.  Without the newer SQL Native Client the client application's connection string would need to be reconfigured with the new active server name.
If you are looking for instance specific failover, and you are ok with having just a single copy of the database then clustering is probably the better option. If you prefer database level failover and you prefer having the second copy of the data then mirroring is probably the better option for you. Mirroring also has an advantage for some companies as there is no requirement for an Active Directory domain so if you are hosted with Amazon's EC2 for example mirroring is still an option where clustering isn't.
Like everything else in the SQL Server world there's no guaranteed correct answer every time for which (if any) of these technologies you should be using. Some, all or none of them may have a place in your shop today.

The new SQL Server 2008 R2 lineup includes

• SQL Server 2008 R2 Parallel Data Warehouse Edition
• SQL Server 2008 R2 Datacenter Edition
• SQL Server 2008 R2 Enterprise Edition
• SQL Server 2008 R2 Developer Edition
• SQL Server 2008 R2 Standard Edition
• SQL Server 2008 R2 Web Edition
• SQL Server 2008 R2 Workgroup Edition
• SQL Server 2008 R2 Express Edition (Free)
• SQL Server 2008 Compact Edition (Free)

More detailed information about the SQL Server 2008 R2 editions, their pricing, and the features that they support can be found in Table 1. SQL Server 2008 R2 supports upgrading from SQL Server 2008, SQL Server 2005, and SQL Server 2000.

Table 1: SQL Server 2008 R2 Editions

SQL Server 2008 R2 Editions	Pricing	Significant Features
Parallel Data Warehouse	$57,498 per CPU Not offered via server CAL	MPP scale-out architecture BI—SSAS, SSIS, SSRS
Datacenter	$57,498 per CPU Not offered via server CAL	64 CPUs and up to 256 cores 2TB of RAM 16-node failover clustering Database mirroring StreamInsight Multi-Server management Master Data Services BI—SSAS, SSIS, SSRS PowerPivot for SharePoint Partitioning Resource Governor Online indexing and restore backup compression
Enterprise	$28,749 per CPU $13,969 per server      with 25 CALs	64 CPUs and up to 256 cores 2TB of RAM 16-node failover clustering Database mirroring Multiserver management Master Data Services BI—SSAS, SSIS, SSRS PowerPivot for SharePoint Partitioning Resource Governor Online indexing and restore Backup compression
Developer	$50 per developer	Same as the Enterprise Edition
Standard	$7,499 per CPU $1,849 per server       with 5 CALs	4 CPUs 2TB of RAM 2-node failover clustering Database mirroring BI—SSAS, SSIS, SSRS Backup compression
Web	$15 per CPU per month Not offered via server CAL	4 CPUs 2TB of RAM BI—SSRS
Workgroup	$3,899 per CPU $739 per server      with 5 CALs	2 CPUs 4GB of RAM BI—SSRS
Express Base	Free	1 CPU 1GB ofRAM
Express with Tools	Free	1 CPU 1GB of RAM
Express with Advanced Services	Free	1 CPU 1GB of RAM BI—SSRS (for the local instance)

Support for Up to 256 Processor Cores

On the hardware side, SQL Server 2008 R2 Datacenter Edition now supports systems with up to 64 physical processors and 256 cores. This support enables greater scalability in the x64 line than ever before. SQL Server 2008 R2 Enterprise Edition supports up to 64 processors, and Standard Edition supports up to four processors.

It’s noteworthy that SQL Server 2008 R2 remains one of the few Microsoft server platforms that is still available in both 32-bit and 64-bit versions. I expect that it will be the last 32-bit version of SQL Server that Microsoft releases.

PowerPivot and Self-Service BI

Without a doubt, the most publicized new feature in SQL Server 2008 R2 is PowerPivot and self-service BI. SQL Server 2008 R2’s PowerPivot for Excel (formerly code-named Gemini) is essentially an Excel add-in that brings the SSAS engine into Excel. It adds powerful data analysis capabilities to Excel, the front-end data analysis tool that knowledge workers know and use on a daily basis. Built-in data compression enables PowerPivot for Excel to work with millions of rows and still deliver subsecond response time. As you would expect, PowerPivot for Excel can connect to SQL Server 2008 databases, but it can also connect to previous versions of SQL Server as well as other data sources, including Oracle and Teradata, and even SSRS reports. In addition to its data manipulation capabilities, PowerPivot for Excel also includes a new cube-oriented calculation language called Data Analysis Expressions (DAX), which extends Excel’s data analysis capabilities with the multidimensional capabilities of the MDX language. Figure 1 shows the new PowerPivot for Excel add-in being used to create a PowerPivot chart and PowerPivot table for data analysis.

1. SQL Server 2008 R2 Parallel Computing Edition

One huge change is the Parallel Computing edition (formerly code-named Madison), essentially the SQL Server equivalent to Windows Server High Performance Computing (HPC) Edition. It will utilize a Massively Parallel Processing (MPP) scale-out architecture to support data warehousing ranging from 10TB to 1+ PB. The Parallel Computing Edition is priced at $57,489 per processor.

2. SQL Server 2008 R2 Datacenter Edition

The biggest change is undoubtedly the new SQL Server 2008 R2 Datacenter Edition. This edition parallels Windows Server Datacenter Edition, and with support from up to 256 logical processors, it's designed to address the highest levels of scalability. The Datacenter edition has no memory limitation and offers support for more than 25 instances. It also will be priced at $57,489 per processor.

3. SQL Server 2008 R2 Enterprise Edition and Developer Edition

The Enterprise edition is the one that’s targeted toward most businesses. With the advent of the new Datacenter edition, the Enterprise edition will be limited to support for 64 logical processors and 25 instances, which really isn’t much of a change. It will be priced at $28,749 per processor or $13,969 per server with 25 CALs. The Developer edition shares the same feature set and is licensed at $50 per developer.

4. SQL Server 2008 R2 Standard Edition

Designed for small and medium businesses, the Standard edition supports up to 4 CPUs and 2TB of RAM, with unlimited database size. It includes support for all BI subsystems including Analysis Services, Integration Services, and Reporting Services, but lacks many of the enterprise-oriented features from the Enterprise edition. It will be priced at $7,499 per processor or $1,849 per server with five CALs.
Related: SQL Server 2008 R2 Appliances

5. SQL Server 2008 R2 Web Edition

This edition is designed to be run by web hosting providers. It provides support for up to 4 CPUs, 2TB of RAM, and unlimited database size. In addition to its relational database engine, SQL Server 2008 R2 Web Edition supports Reporting Services. It's priced at $15 per processor per month.

6. SQL Server 2008 R2 Workgroup Edition

Workgroup edition is designed to provide basic relational database services for departments and branch offices. It supports a maximum of 2 CPUs, 4GB of RAM, and unlimited database size. Its only BI feature is Reporting Services. It's priced at $3,899 or $739 per server including five CALs.

7. SQL Server 2008 R2 Express Edition

Targeted toward developers and ISVs, SQL Server Express Edition comes in three versions. SQL Server 2008 R2 Express provides relational database services; SQL Server 2008 R2 Express with Tools Edition adds SSMS; and SQL Server 2008 R2 Express with Advanced Services adds SSMS and a local instance of Reporting Services. All of the SQL Server 2008 R2 Express editions will continue to be free downloads.