Search This Blog

Tuesday, June 26, 2018

Database Tuning Approach Using Response Time Analysis

Database Tuning Approach Using Response Time Analysis

What is Response Time Analysis?

Response time analysis (RTA) is a method of measuring the execution time of each queries. It is a natural way to quantify the metric of the speed. For example, when one travels to the destination, it quantifies how long it takes to reach the destination. In SQL Server, a Profiler is a common way to trace the execution or elapsed time of each queries.

Response time analysis is a great way to quantify the execution time, but it does not give a deeper level of what causes the execution time takes longer than the baseline or the average. Wait Time Analysis (WTA) is a way to provide the insight of what slows down the query performance. In SQL Server, WTA is also known as Wait Stats. In the performance analysis, it is common to combine RTA with WTA.

What is Wait Time Analysis?

Think about the previous example of traveling to the destination. For example, my typical driving time to the office is 20 minutes. This is my baseline of driving time. During the summer time, the traveling time can increase to 25 to 30 minutes. What cause the baseline of 20 minutes increased by 5 to 10 minutes? During the summer, it is a high season for tourists in my area, so the traffic has increased. In Wait Time Analysis, the tourist factor is called “Tourist” wait type. If there is an accident in the road, the travelling time can increase to one hour. In this case, the “Accident” wait type contributes to the longer response time.

In the daily commute, we encounter different wait types, such as flat tire, car broke down, lost a key, road construction, wake up late, etc. Similarly, SQL Server has many wait types, so WTA can be used as part of the performance analysis. The common wait types are:


·        CXPACKET

·        LCK_M_IX

·        LCK_M_X




·        WRITELOG

·        OLEDB

Where is Wait Type Recorded in SQL Server?

The history of SQL Server started to introduce WTA dated back to SQL Server 2000 SP3 while other RDBMS competitors had already have WTA built in the database engine. It was not until SQL Server 2005, Microsoft had fully implemented WTA by introducing Dynamic Management View (DMV) which included wait stats to catch up with other RDBMS. There are several DMVs related to WTA capturing wait stats.


The DMV aggregates wait stats at the system level.


The DMV captures wait stats at each request level.


This a relatively new DMV introduced in SQL Server 2016. The DMV capture wait stats at each request level. From each request, WTA can be performed at the query level.

Wednesday, November 30, 2016

Troubleshooting Problems with MSDTC

Steps by steps diagnosing:

How to configure RPC dynamic port allocation to work with firewalls

Great article on understanding DTC , firewall and Cluster:

Tools to troubleshoot:

Security Settings

Network DTC  = allows to configure whether or not the transaction coordinator has access to the network. (required for BizTalk)
Allow Remote Clients = recommended to be off for security restrictions; need to test for Orange Lake apps
Allow Remote Administration =  recommended to be off for security restrictions;

Transaction Manager Communication

Allow Inbound = whether transactions initiated on source machines will be allowed. (required for BizTalk)
Allow Outbound = determines whether to allow the local computer to initiate a transaction and run that transaction on a remote computer.
Mutual Authentication Required. If the remote access communication is performed between two DTC services, this authentication information must specify a computer account that matches the remote transaction mode computer's host name.

Incoming caller Authentication Required. Following Microsoft Best Practices, MSDTC on SQL Server must be clustered. If this is your case, you must configure the Transaction Manager Communication with this option because In a clustered environment, DTC authentication does not use the host name, it uses the name of the virtual service and just for being clustered, the configured DTC already trusts on the destination DTC.

No Authentication Required is used in the following instances:
  • Running Microsoft Windows 2000.  (Non applicable to BizTalk 2006 and above)
  • The transaction occurs between servers in two domains that do not have a mutual trust (Could be the case)
  • Computers are communicating through workgroup  (non applicable to BizTalk)
XA Transactions
In Windows XP , Windows Server 2003, and subsequent versions you can disable XA transactions to help prevent the security risk that arises when a user-specified DLL, used by the DTC to communicate with the XA partner's transaction manager, is loaded directly into the DTC process. This situation exposes a resource manager's databases to serious data corruption and can cause Denial of Service (DOS) attacks. The disabling of XA transactions helps to protect the DTC from this DLL attack.

Friday, April 16, 2010

Connecting To SQL Server Embedded Edition (SSEE)

By default, the remote connection is not allowed in SSEE. To connect to the database from the command line is to rdp to the server and run this command:

osql -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query -E

Windows Sharepoint Services 3.0 (WSS) installs and uses SSEE 2005.

Thursday, July 30, 2009

Excessive Messages of 19030 and 19031

When using a third party tool like Idera SQL Compliance Manager, it generates a lot of messages in the windows eventlog and SQL Server error log. The event ids are 19030 and 19031. The reason is the tool uses the SQL Trace that starts and stops the trace which by default will log to the eventlog.

In SQL Server 2000, we can use sp_altermessage to suppress the message. However in SQL Server 2005, it does not work. We have to turn on the trace flag -T3688 to suppress it.

The Microsoft kbase article#922578: describes the solution.

Monday, June 08, 2009

Troubleshooting Common Data Collection Problem

When running a remote data collection using tools, such as SQL Diagnostic Manager from Idera, or SQLH2 (a free tool to collect SQL Server/Windows configuration), these are the common permission problems in Windows 2003:
  • Unable to connect to perfmon counter remotely.
  • Cannot read remote registry.

The permission issue with "Unable To Connect to Perfmon Counter Remotely" can be fixed using the steps below:

  1. Open regedit on the machine to which you are trying to connect to perfmon.
  2. Browse to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\WindowsNT\CurrentVersion\Perflib"HKLM\SYSTEM\CurrentControlSet\control\Secure\SecurePipeServer\winreg"
  3. Right click on Perflib key and select permissions.
  4. Click Add and add Local Service with full control.
  5. Save and exit.
  6. Restart the Remote Registry Service.

To resolve the issue of "Unable to Read Remote Registry", use Registry Editor to grant Read Only permissions to the Local Service user account. To do this, follow these steps:

  1. Click Start, click Run, type regedit, and then click OK.
  2. Expand the following registry subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\ SecurePipeServers
  3. Right-click winreg, and then click Permissions.
  4. Click Add.In the Enter the object names to select box, type Local Service, and then click OK. Alternatively, you can type the name of a user or group that you want to grant access permission.
  5. In the Permissions for LOCAL SERVICE list, next to Read, click to select the Allow check box.
  6. Quit Registry Editor, and then restart Remote Registry service.

Application DBA Role

An APPs DBA knows all that a regular DBA knows plus the following:

  • Application fundamentals such as concepts and architecture
  • Application Installation and configuration
  • Patching and Upgrades
  • Patch Management and Version Control
  • Customization Updates
  • Cloning requirements. This can be frequent in an Apps environment
  • Concurrent Managers
  • Forms/Report Servers
  • Web Servers
  • All the "ad" utilities
  • All the database restrictions for the applications. That is so that the installation is still supportable and conforms to all vendor (Oracle) requirements.
  • All special application related regular and maintenance functions. Such as month end and period end functions.
  • Understand any backup and recovery constraints. Such as for table imports (NOT) and partial recoveries.
  • Special requirements and restrictions for tuning. Example optimizer and index requirements. Printer Configuration
  • User Access Administration
This could be a matter for further discussion as it could be argued that all DBA's should be aware of the above or equivalent for any database application (Oracle or otherwise).

Tuesday, October 24, 2006

Cross Reference of SQL Server and Oracle

These are the cross reference of the technical terms used by SQL Server and Oracle:

SQL Server - Oracle

transaction log - redo log
database - schema
instance - instance/database
page - block

simple recovery model - noarchive mode
full recovery model - archive mode

indexed view - materialized view
filegroup - tablespace
clustered index - index organized table (IOT)

Good Scenarios To Use Surrogate Key

Surrogate key can be beneficial if it is used appropriately. Here are the lists of the scenarios:
  • to use it in the dimension table in the star schema. Updating the natural key in the dimension table will not cause the update in the fact table.
  • to replace the composite primary key as join to other tables. The guideline is different between SQL Server and Oracle. SQL Server is more aggressive to use the surrogate key: 2-3 columns vs. 4-5 columns in Oracle.

First Experience With SQL Server

My first encounter with SQL Server was version 6.0 in 1997. It was a very short period of time before digging more in SQL Server version 6.5. Moved to 7.0 for a few years. Most of my time, I worked with SQL Server 2000.