Category Archives: DBMS

How to Identify I/O Bottlenecks in MS SQL Server

Problem

\r\nWe experience regular slowdowns on our SQL Server databases. After analyzing the memory and CPU usage we would like to continue the root cause investigation by examining I/O bottlenecks. What is your recommendation to recognize I/O related bottlenecks in SQL Server?\r\n

Solution

\r\nThe I/O subsystem is a key factor when it comes to SQL Server performance since database pages are constantly moved in and out of the buffer pool. Also the transaction logs and tempDB generate significant I/O traffic. Therefore you have to ensure that your I/O subsystem performs as expected, otherwise you will be a victim of increased response times and frequent time-outs. In this tip I will describe some of the ways to identify I/O related bottlenecks using the built-in tools and provide some disk configuration ideas.\r\n\r\n


\r\n\r\n

Performance Monitor

\r\nYou can use Performance Monitor to check the load on your I/O subsystem. The following performance counters can be setup to check disk performance.\r\n\r\nThe PhysicalDisk Object: Avg. Disk Queue Length counter shows you the average number of read and write requests that were queued on the selected physical disk. The higher the number the more disk operations are waiting. It requires attention if this value frequently exceeds a value of 2 during peak usage of SQL Server.  If you have multiple drives you should take this number and divide by the number of drives in the array to see if the number is above 2.  For example, you have 4 drives and a disk queue length of 10, this would be 10/4 = 2.5, so this would be the value you want to use not 10.\r\n\r\nAvg. Disk Sec/Read and Avg. Disk Sec/Write shows the average time of a data reads and writes from/to the disk. It is good up to 10 ms, but it is still acceptable if less than 20 ms. Any higher value needs further investigation.\r\n\r\nPhysical Disk: %Disk Time is the ratio of elapsed time when the disk drive was busy with read or write requests. The rule of thumb for this value is that it should be below 50 percent.\r\n\r\nThe counters Disk Reads/Sec and Disk Writes/Sec show you the rate of read/write operations on the disk. It should be less than 85 percent of the disk capacity since the disk access time increases exponentially beyond this value.\r\n\r\nYou can determine the disk capacity by gradually increasing the load on the system. One way to do this is to use SQLIO.  You should look for the point where the throughput is constant, but the latency increases.\r\n\r\nYou can use the counters for RAID configurations with the following calculations:\r\nRaid 0: I/O per disk = (reads + writes) / number of disks\r\nRaid 1: I/O per disk = [reads + (writes*2)] / 2\r\nRaid 5: I/O per disk = [reads + (writes*4)] / number of disks\r\nRaid 10: I/O per disk = [reads + (writes*2)] / number of disks\r\n\r\nHere is an example of your I/O per disk for RAID 1, if we get these values from the counters:\r\n\r\nDisk Reads/sec = 90\r\nDisk Writes/sec = 75\r\nThe formula for I/O on a RAID-1 array is [reads + (writes*2)] / 2 or [90 + (75*2)] / 2 = 120 I/Os per disk\r\n\r\n


\r\n\r\n

Dynamic Management Views

\r\nThere are some useful Dynamic Management Views (DMVs) to check I/O bottlenecks.\r\n\r\nAn I/O latch wait occurs when a page is accessed for reading or writing but the page is not available in the buffer pool. It causes waits on PAGEIOLATCH_EX or PAGEIOLATCH_SH, depending upon the type of request. These wait types can indicate an I/O bottleneck. You can query the sys.dm_os_wait_stats DMV to find latch wait statistics. You can identify I/O problems if you save query outputs of waiting_task_counts and wait_time_ms values from a normal working state of your SQL Server and compare these values when performance is degraded.\r\n

\r\n
select *\r\nfrom sys.dm_os_wait_stats\r\nwhere wait_type like 'PAGEIOLATCH%'\r\norder by wait_type asc

\r\n

\r\nPending I/O requests can be found by querying the following DMVs and can be used to identify which disk is responsible for the bottleneck.\r\n

\r\n
select database_id,\r\n       file_id,\r\n       io_stall,\r\n       io_pending_ms_ticks,\r\n       scheduler_address\r\nfrom sys.dm_io_virtual_file_stats(NULL, NULL) iovfs,\r\n     sys.dm_io_pending_io_requests as iopior\r\nwhere iovfs.file_handle = iopior.io_handle

\r\n

\r\n \r\n\r\n


\r\n\r\n

Disk Fragmentation

\r\nI would recommend that you check the disk fragmentation and the configuration of your disks used by the SQL Server instance.\r\n\r\nFragmentation of files on NTFS can cause significant reductions in performance. Disks should be defragmented regularly and a defragmentation policy and plan should be put in place.  Research shows that in some cases a SAN can actually perform worse with defragmentation enabled thus SANs need to be treated on a case-by-case basis.\r\n\r\nFragmentation of indexes can also cause high I/O utilization on NTFS, but this does not have the same affect on SANs which perform better on random I/Os.\r\n\r\n


\r\n\r\n

Disk Configuration / Best Practices

\r\nAs a general rule, you should have log files on a physical disk that is separate from the data files for better performance.  The I/O profile for a heavily used database data files (including tempDB) are random. The I/O profile for all database log files are sequential except when a transaction needs to be rolled back.\r\n\r\nThe internal disks should only be used for database log files, because they excel at sequential I/O, but perform poorly on random I/Os.\r\n\r\nThe database data and log files should each be placed on their own dedicated disk packs. To ensure optimal performance, I recommend that the database log file be placed on two internal disks configured as RAID 1.  The database data file should reside on a SAN system which is only accessed by SQL Server and has controlled querying and reporting.  Ad hoc access should be disallowed.\r\n\r\nWrite caching should be enabled where possible and you should make sure the cache is protected from power failures and other possible failures.\r\n\r\nTo limit possible I/O bottlenecks for your OLTP system you should not mix OLAP and OLTP environments.   Also, make sure your code is tuned and create indexes where necessary to avoid unnecessary I/Os.\r\n\r\n \r\n

Next Steps

\r\n

    \r\n
  • Collect and compare performance counters
  • \r\n

  • Analyze DMV information
  • \r\n

  • Run SQL Server Profiler to find high Read and Write queries that can be tuned
  • \r\n

\r\n \r\n\r\n \r\n\r\nReference: Tybor Nagi, sql t!p\r\n\r\n 

SQL Server : Take offline database

EXEC sp_dboption N’mydb’, N’offline’, N’true’\r\nOR\r\nALTER DATABASE [mydb] SET OFFLINE WITH\r\nROLLBACK AFTER 30 SECONDS\r\nOR\r\nALTER DATABASE [mydb] SET OFFLINE WITH\r\nROLLBACK IMMEDIATE

\r\n

    \r\n
  • Using the alter database statement (SQL Server 2k and beyond) is the preferred method.
  • \r\n

  • The rollback after statement will force currently executing statements to rollback after N seconds.
  • \r\n

  • The default is to wait for all currently running transactions to complete and for the sessions to be terminated. Use the rollback immediate clause to rollback transactions immediately.
  • \r\n

How to attach a SQL Server data file MDF without LDF

To attach a MDF without LDF you can follow the steps below:\r\n

\r\n

1. Create a new database with the same name and same database (i.e. mydatabase) MDF and LDF files 

\r\n

(i.e. mydatabase_data.mdf and mydatabase_log.ldf).\r\n

\r\n

2. Stop SQL server and change the name of new MDF (i.e. mydatabase.mdf to mydatabase-new.mdf) and 

\r\n

copy the original MDF to this location and delete the LDF file.\r\n

\r\n

3. Start SQL Server.\r\n

\r\n

4. Now your database will be marked suspect.\r\n

\r\n

5. Update the sysdatabases to update to Emergency mode. This will not use LOG files in start up:\r\n

\r\n

\r\n

\r\n

\r\n
\r\n

\r\n

\r\n

\r\n

\r\n
Sp_configure "allow updates", 1\r\n

\r\n

go\r\n

\r\n

Reconfigure with override\r\n

\r\n

GO\r\n

\r\n

Update sysdatabases set status = 32768 where name = "BadDbName"\r\n

\r\n

go\r\n

\r\n

Sp_configure "allow updates", 0\r\n

\r\n

go\r\n

\r\n

Reconfigure with override\r\n

\r\n

GO

\r\n

\r\n

\r\n

6. Restart SQL server. Now the database will be in emergency mode.\r\n

\r\n

7. Now execute the undocumented DBCC to create a log file:\r\n

\r\n

\r\n

\r\n

\r\n
\r\n

\r\n

\r\n

\r\n

\r\n
DBCC REBUILD_LOG (mydatabase,'c:\mydatabase.ldf').\r\n

\r\n

Note: (replace the mydatabase and log file name and path based on your requirement)\r\n

\r\n

\r\n

\r\n

8. Execute sp_resetstatus <mydatabase>.\r\n

\r\n

9. Restart SQL server and see the database is online.\r\n

\r\n

\r\n

Solving ReportViewer Rendering Issue on IIS7

Solving ReportViewer Rendering Issue on IIS7\r\n\r\n\r\n\r\nApplies to:\r\n

    \r\n
  • Internet Information Services 7.0 (IIS7)
  • \r\n

  • Microsoft Report Viewer Redistributable 2005
  • \r\n

\r\nSymptoms:\r\n

    \r\n
  • Unable to render ReportViewer on ASP.NET Web pages while running on IIS7.
  • \r\n

  • You have no problem viewing your reports when running on debug mode with your Visual Studio 2005.
  • \r\n

  • You are able to view your reports on Report Manager but not able to view them on IIS7.
  • \r\n

  • You encounter JavaScript error when loading your report page with ReportViewer. Image buttons such as calendar appear as red ‘X’.
  • \r\n

\r\nCause:\r\n

    \r\n
  • When the ReportViewer control is added to Web Form (.aspx), the\r\nReserved.ReportViewerWebControl.axd httpHandler is added to System.Web section of the Web.Config file. In IIS7, it should be added under System.Webserver section.
  • \r\n

  • IIS7 Handler Mappings does not contain Reserved.ReportViewerWebControl.axd httpHandler, and therefore unable to render the ReportViewer elements needed by the JavaSript.
  • \r\n

\r\nResolution:\r\n

    \r\n
  • Open Internet Information Services (IIS) Manager and select your Web application.
  • \r\n

  • Under IIS area, double-click on Handler Mappings icon.
  • \r\n

  • At the Action pane on your right, click on Add Managed Handler.
  • \r\n

  • At the Add Managed Handler dialog, enter the following:\r\nRequest path: Reserved.ReportViewerWebControl.axd\r\nType: Microsoft.Reporting.WebForms.HttpHandler\r\nName: Reserved-ReportViewerWebControl-axd
  • \r\n

  • Click OK.
  • \r\n

\r\nReserved-ReportViewerWebControl-axd handler is now added to your Handler Mappings list. Notice that the following line has also been added to your Web.config file under the system.webserver’s handler section:\r\n

<add name="Reserved-ReportViewerWebControl-axd" path="Reserved.ReportViewerWebControl.axd" verb="*" type="Microsoft.Reporting.WebForms.HttpHandler" resourceType="Unspecified" />

\r\nRun your report again.

How to fix creating index (Full-Text catalogs) – Error 229 (Permission denied) in SQL Server?

Symptoms:\r\nI have my development database on a SQL Server 2005 server. I enabled it for full-text catalogs and created a catalog with no issues whatsoever. When I go to create the index, however, I run through the wizard (using SQL Server Management Studio) until the last step. Upon finishing the wizard, I get the following error:\r\n\r\nError:\r\n

Failed to retrieve data for this request (Microsoft.SqlServer.SmoEnum)\r\nAdditional Information:\r\nA exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)\r\nEXECUTE permission denied on object 'sp_help_category', database 'msdb', schema 'dbo'.\r\nSELECT permission denied on object 'sysjobs_view', database 'msdb', schema 'dbo'. (Microsoft SQL Server, Error: 229)

\r\nI am the DBOwner. The DBA created the index for me with no problems. I can see the index. I can pull up the property pages and see “General” and “Columns” views fine, but when I click on “Schedules”, I receive the same error as above.\r\n\r\nSolution:\r\nYou do not have permissions to access sysjobs_view in database msdb and also executesp_help_category in msdb.\r\nUser must have CREATE FULLTEXT CATALOG permission on this database, or be a member of thedb_owner, or db_ddladmin fixed database roles.

How to change SQL Server database collation

\r\n

\r\n
\r\n\r\nIf you tried changing collation to from English to Turkish or other using the script as below but it will fail due to dependencies issues, You have to script them, drop them, change db collation and recreate them from script.\r\n\r\nThe Collation change script:\r\n
\r\n
Code:

\r\n

ALTER DATABASE {your database} SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\nALTER DATABASE {your database} COLLATE Turkish_CI_AS\r\nALTER DATABASE {your database} SET MULTI_USER

\r\n

\r\n

\r\n
\r\n
where you need changing {your database} with your database name i.e. mydb

\r\n

\r\n

\r\nThese links would help you:\r\n

\r\nKeep in mind that changing the colaltion of the database will NOT change the collation of the columns that are already in the database, only the default collation of any new columns you will create.\r\n\r\nThe error you may face while changing collation:\r\n

Error Code:

\r\n

Msg 5075, Level 16, State 1, Line 2\r\nThe object '{ObjectName}' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.\r\nMsg 5075, Level 16, State 1, Line 2\r\nThe object '{objectName{' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.\r\nMsg 5075, Level 16, State 1, Line 2\r\nThe object '{ObjectName}' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.\r\nMsg 5072, Level 16, State 1, Line 2\r\nALTER DATABASE failed. The default collation of database 'myDB' cannot be set to Turkish_CI_AS.

\r\n

\r\n

Recover SQL Server Database from Suspect Mode

To recover database from suspect mode in SQL Server 2005 / 2008 use this script in MS SQL Server management Studio:\r\n\r\nEXEC sp_resetstatus ‘yourDBname';\r\nALTER DATABASE yourDBname SET EMERGENCY\r\nDBCC checkdb(‘yourDBname’)\r\nALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\nDBCC CheckDB (‘yourDBname’, REPAIR_ALLOW_DATA_LOSS)\r\nALTER DATABASE yourDBname SET MULTI_USER\r\n\r\nNote: change ‘yourDBname’ with database name you want to recover from suspect mode.