Author Archives: Amit Banerjee

SQL BPA – Part II

Posted on by

I have already blogged about this in a previous post of mine (Best Practice Analyzer) but I was recently working on the SQL Server 2005 version of the tool and thought that this deserved a second mention.

This tool has been enhanced a lot and provides a great deal more information than it’s SQL 2000 counterpart. This creates a XML data output file in your %appdata%/Microsoft/SQL BPA folder. This output can be imported using the SQL BPA UI and then a set of reports can be generated to check the following:

  • Gathers configuration information from an instance of SQL Server.
  • Performs specific tests on the instance of SQL Server.
  • Proactively verifies that the configuration is set according to recommended best practices. Some high level checks are even performed on the Operating System level.
  • Reports all settings that differ from the default settings.
  • Reports recent changes in the instance of SQL Server.

    On a broader level, the tool verifies the above mentioned based on rules divided into the following categories:

    1. Security rules
    2. Database Engine rules
    3. Analysis Services rules
    4. Replication rules
    5. Integration Services rules

    For example, if you applied the initial release version of SQL Server 2005 SP2, existing SQL Server 2005 maintenance plans and SSIS packages that contain cleanup tasks might run those tasks at shorter intervals. The tool if it scans your SQL Server instance and finds out if you are on a build lower than the one mentioned in KB933508, then it would provide the recommendation to apply the fix.

    The SQL BPA UI can be used to run scans on remote machines also. So, there is no need to install the tool on the SQL Server box which you want to scan. You can also configure the type of scan you want the SQL BPA tool to perform on your server.

    Microsoft PSS also has the capability to include this tool as a part of the PSSDIAG collection that they send out to collect diagnostic data from the instance based on the need to collect BPA analytics data.

    For a more detailed information about the above mentioned points, you can always refer the SQL Server Best Practices Analyzer Help chm file.

    • Best Practices Analyzer

    Posted on by

    I have seen a lot of people asking "Are we following best practices for our SQL Servers?". The Best Practice Analyzer can try and guide you along a path which would lead to the right answer. A simple answer is not possible because of the following reasons:
    1. We are not conversant with your environment and setup
    2. We do not know what kind of business implementation this SQL Sever is
    3. We do not know what constraints are present which prevents some of the best practices from being followed
    4. We do not know which data/databases are critical/non-critical etc. and also do not know what kind of SLAs need to be met.

    Even then, an attempt is made using the Best Practice Analyzer to verify if common best practices are being implemented across your SQL Server. It creates a repository on the our server and stores the analysis for the server in the database which can be used at a later date.
    The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.

    This tool is available at:
    For SQL Server 2000
    http://www.microsoft.com/downloads/details.aspx?familyid=B352EB1F-D3CA-44EE-893E-9E07339C1F22&displaylang=en
    For SQL Server 2005
    http://www.microsoft.com/downloads/details.aspx?FamilyId=DA0531E4-E94C-4991-82FA-F0E3FBD05E63&displaylang=en

    Reorganize data and index pages

    Posted on by

    There are a few ways that this can be achieved:

    1. Shrinking the database files (data file or transaction log file)

    2. DBCC DBREINDEX (Similar to dropping and re-creating the indexes)

    3. DBBC INDEXDEFRAG (De-fragmenting the indexed pages to remove logical scan fragmentation)

    Note: It needs to be kept in mind that DBREINDEX is a fully-logged operation and can cause the transaction log to grow by large amounts when the database size is considerably large. So there have been situations when the transaction log has grown out of proportion and filled up the entire disk. If the T-log does grow out of proportion and you do choose to truncate/shrink the file, then please do so after verifying the points below:

    1. Transactional replication is not configured for the database

    2. Log shipping is not configured for the database

    3. Database mirroring is not configured for the database (For SQL 2005 SP1 and above)

    4. Any other operations are not being performed which need transactional log consistency in terms of LSN chains

    Online re-indexing is a feature which is available in SQL Server 2005 Enterprise Edition only. Please note that rebuilding the indexes would cause the statistics associated with those indexes to also be updated. This however would not update the AUTO CREATED statistics.

    The following articles could be useful:

    SET OPTION considerations when running DBCC with indexes on computed columns

    http://support.microsoft.com/kb/301292/

    DBCC DBREINDEX

    http://msdn2.microsoft.com/en-us/library/ms181671.aspx

    The DBCC SHOWCONTIG output for SQL Server 2000 and 2005 will help you find out the current status of fragmentation for the database. A sample output would look like this:

    DBCC SHOWCONTIG scanning ‘Customers’ table…

    Table: ‘Customers’ (2073058421); index ID: 1, database ID: 6

    TABLE level scan performed.

    – Pages Scanned…………………………..: 3

    – Extents Scanned…………………………: 2

    – Extent Switches…………………………: 1

    – Avg. Pages per Extent……………………: 1.5

    – Scan Density [Best Count:Actual Count]…….: 50.00% [1:2]

    – Logical Scan Fragmentation ………………: 0.00%

    – Extent Scan Fragmentation ……………….: 50.00%

    – Avg. Bytes Free per Page…………………: 246.7

    – Avg. Page Density (full)…………………: 96.95%

    The logical scan fragmentation should ideally be below 20% but anything above 40% would mean an INDEXDEFRAG or DBREINDEX is required. The script which is from the same MSDN article can help you run an INDEXDEFRAG or DBREINDEX based on the logical scan fragmentation count.

    A sample script for the same can be found under the topic of "DBCC SHOWCONTIG" in SQL Server Books Online (2000 and 2005)

    In SQL Server 2005, the following features may be helpful to you while using the ALTER INDEX command:

    REBUILD [ WITH ( [ ,… n]) ]

    Specifies the index will be rebuilt using the same columns, index type, uniqueness attribute, and sort order. This clause is equivalent to DBCC DBREINDEX. REBUILD enables a disabled index. Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified. If index options are not specified, the existing index option values stored in sys.indexes are applied. For any index option whose value is not stored in sys.indexes, the default indicated in the argument definition of the option applies.

    The options ONLINE and IGNORE_DUP_KEY are not valid when you rebuild an XML index.

    If ALL is specified and the underlying table is a heap, the rebuild operation has no affect on the table. Any nonclustered indexes associated with the table are rebuilt.

    The rebuild operation can be minimally logged if the database recovery model is set to either bulk-logged or simple.

    WITH ( LOB_COMPACTION = { ON OFF } )

    Specifies that all pages that contain large object (LOB) data are compacted. The LOB data types are image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml. Compacting this data can improve disk space use. The default is ON.

    ON

    All pages that contain large object data are compacted.Reorganizing a specified clustered index compacts all LOB columns that are contained in the clustered index. Reorganizing a nonclustered index compacts all LOB columns that are nonkey (included) columns in the index. For more information, see Creating Indexes with Included Columns. When ALL is specified, all indexes that are associated with the specified table or view are reorganized, and all LOB columns that are associated with the clustered index, underlying table, or nonclustered index with included columns are compacted. OFF Pages that contain large object data are not compacted.

    OFF

    has no affect on a heap.The LOB_COMPACTION clause is ignored if LOB columns are not present.

    REORGANIZE

    Specifies the index leaf level will be reorganized. This clause is equivalent to DBCC INDEXDEFRAG. ALTER INDEX REORGANIZE statement is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a disabled index or an index with ALLOW_PAGE_LOCKS set to OFF.

    Furthermore, the dynamic management view sys.dm_db_index_physical_stats would help you get statistical information pertaining to indexes on SQL Server 2005. The columns mentioned below in this DMV output could be helpful in analyzing the need for index reorganization.

    avg_fragmentation_in_percent: Logical fragmentation for indexes, or extent fragmentation for heaps in the IN_ROW_DATA allocation unit. The value is measured as a percentage and takes into account multiple files. For definitions of logical and extent fragmentation, see Remarks. 0 for LOB_DATA and ROW_OVERFLOW_DATA allocation units.NULL for heaps when mode = SAMPLED.

    fragment_count: Number of fragments in the leaf level of an IN_ROW_DATA allocation unit. For more information about fragments, see Remarks.NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units. NULL for heaps when mode = SAMPLED.

    avg_fragment_size_in_pages: Average number of pages in one fragment in the leaf level of an IN_ROW_DATA allocation unit. NULL for nonleaf levels of an index, and LOB_DATA or ROW_OVERFLOW_DATA allocation units. NULL for heaps when mode = SAMPLED.

    The following article could be helpful:Microsoft SQL Server 2000 Index Defragmentation Best Practices

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

    Database Maintenance Plans

    Posted on by

    Database Maintenance Plans for SQL Server 2000
    Database Maintenance Plans offer the following features in SQL Server 2000:
    1. Backing up databases
    2. Reorganization of indexes and data pages
    3. Statistics Update
    4. Shrinking the database
    5. Database integrity checks
    The above tasks of the maintenance plans would have associated with respective SQL Agent jobs and also these could be scheduled to suit your needs. Furthermore, these jobs could be created manually also without the help of maintenance plans.

    Database Maintenance Plans for SQL Server 2005
    In addition to the above mentioned features, SQL Server 2005 Enterprise Edition provides the option of rebuilding the indexes as an online operation. This makes it possible for the indexes to remain online while a rebuild index task is being performed on the database.
    ONLINE = { ON OFF }
    Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF.
    Note: Online index operations are available only in SQL Server 2005 Enterprise Edition.
    ON
    Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This enables queries or updates to the underlying table and indexes to continue. At the start of the operation, a Shared (S) lock is held on the source object for a very short amount of time. At the end of the operation, for a short time, an S (Shared) lock is acquired on the source if a nonclustered index is being created; or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online and when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table.
    OFF
    Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.

    The frequency of these tasks and which tasks are needed need to be performed need to be decided on the following factors:
    1. Importance of the database in question
    2. Size of the database
    3. Maintenance window frequency
    4. Load on the server
    5. Whether it is an OLTP or OLAP database or both
    6. The kind of transactions that take place on the database (Point 5 and 6 would decide how often index reorganization/index rebuild/statistics updates are required and whether a shrink database/file is required)

    Stuck Stalled IO Messages

    Posted on by

    Sometimes, we see the following messages in the SQL Server 2000 (Service Pack 4 and above) and SQL Server 2005 ERRORLOGs:

    2004-11-11 00:21:25.26 spid1 SQL Server has encountered 192 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [E:\db_data2.ndf] in database [user_db] (7). The OS file handle is 0x00000000000074D4. The offset of the latest long IO is:
    0x00000000022000″.

    A given I/O request that becomes stalled or stuck is only reported one time. If the message reports 10 I/Os as stalled, these 10 reports will not occur again. If the next message reports 15 I/Os stalled, it indicates that 15 new I/O requests have become stalled.
    Most of these messages if occur frequently in a SQL Server, then it could be due to one of the following:

    1. There is an underlying hardware issue where in your firmware and disk drivers need to be upgraded. This is mostly done by the Internal Storage Team or preferably by the Hardware Vendor
    2. Or the disks are overwhelmed with the IO Requests being posted on the risks. In this case, you would want to go ahead and make sure that if possible some of the data files and log files would need to be moved to a different drive
    3. Or it could be due to bad plans SQL Server ends up performing extra number reads than required

    Also, monitoring the disks using the following counters under performance monitor under the would be helpful (under the performance objects – Logical Disk and Physical Disk):
    1. Average Disk Queue Length
    2. Average Disk Secs\Transfer
    3. Average Disk Secs\ Write
    4. Average Disk Secs\ Reads
    The last three counters should never show a prolonged peak period which shows values of 30 milli-seconds and above. Please refer my blog article Setting up Perfmon Logs on how to setup a perfmon trace.

    One important point that needs to be kept is  the amount of I/O being performed by SQL Server. You can find out the amount of I/O being driven by SQL Server by using the Process performance object with the SQL Server instance (sqlservr#) that you are concerned with and looking at the values for I/O Data Bytes value. It might be a case where your queries have sub-optimal plans due to which they are performing an excessive amount of reads/writes which is causing the disk to be throttled.

    If you find out that this is actually an issue with the disk sub-system, then it is always a good idea to involve the disk vendor/or your internal storage team to find out if all the drivers/firmware are updated with the latest patches.

    Addition: May 26, 2011:
    If you want to get your hands dirty and debug a Stuck-Stalled IO request, then Bob Dorr, Principal Escalation Engineer at Microsoft, has a blog post on this:
    http://blogs.msdn.com/b/psssql/archive/2008/03/03/how-it-works-debugging-sql-server-stalled-or-stuck-i-o-problems-root-cause.aspx

    Helpful Articles:
    SQL IO Basics

    SQL Stuck Stalled IO KB Article

    Stuck Stalled IO Messages

    SQL Server 2005 IO Basics

    Working with TempDB in SQL Server 2005

    Troubleshooting Performance Problems (section on I/O)

    Physical Database Storage

    Storage Top 10 Best Practices

    Ruling Out Disk-Bound Problems