Best Practices for Troubleshooting Slow Running Queries

In this article we will share some of the common reasons for slow-running queries and what your approach should be for identifying and fixing them. The best and the quickest way to address any slow running query should be by first preparing a Troubleshooting Checklist or Troubleshooting Guide and than by using it during slow running query situations. This way it can help any developer or DBA to easily and quickly narrow down the issue. (This troubleshooting guide or checklist should be posted on a central location from where all the DBA's / Developers of your team can access and update when required)

Please note: If you experience something new which is not listed in the below list than you may update your TSG (Troubleshooting Guide) appropriately.

Queries or updates that take more than the expected time to execute can be caused by a variety of reasons. Slow-running queries can be caused by performance problems related to your network or the computer where SQL Server is running. Slow-running queries can also be caused by problems with your physical database design and SQL Server configuration.

Some of the most common reasons for slow-running queries and updates are:

1) CPU could be causing the bottleneck
2) Slow network communication (high latency).
3) Insufficient memory in the server computer, or the memory is not properly configured for SQL Server instance.
4) Insufficient useful statistics on indexed columns.
5) Outdated statistics on indexed columns.
6) Insufficient useful indexes.
7) Lack of useful indexed views.
8) Lack of useful data striping.
9) Improper partitioning of data.

Troubleshooting Checklist (covering most common scenarios)

1) CPU could be causing the bottleneck

What to do?
One of the most common complaints I get is that SQL Server CPU started spiking excessively all of a sudden. A CPU bottleneck that happens suddenly and unexpectedly, without any changes or load on the server may be caused due to several reasons but some of the common reasons are:

1) Non-optimal query plan,
2) A poor SQL Server configuration,
3) Improper Application/Database design,
4) Insufficient hardware resources.


2) Could it be slow network communication (high latency).

What to do?
Investigate if the performance problem is related to a component other than queries? For example, is there a problem with network performance, do you see any latency issue, try copying the files across to measure the performance of your network. Also, try to identify if there are any other components that might be causing or contributing to performance degradation?

What tool to use?
The Windows System Monitor can be used to monitor the performance of SQL Server and non-SQL Server related components.

3) Could it be Insufficient memory on the server computer, or the memory is not properly configured for SQL Server instance?

What to do?
If your investigation reveals that the performance issue is related to queries then Identify which query or set of queries are involved?

What tool to use?
You may use SQL Server Profiler to help identify the slow query or queries.

What’s next?
After you have identified the slow-running query or queries, you can further analyze query performance by producing a query SHOWPLAN, which can be a text, XML, or graphical representation of the query execution plan that the query optimizer generates. You can produce query SHOWPLAN using:
Ø Transact-SQL SET options,
Ø SQL Server Management Studio, or
Ø SQL Server Profiler.

The information gathered by these tools would allow you to determine how a query is executed by the SQL Server query optimizer and which indexes are being used. This could help you determine if performance improvements can be made by rewriting the query, changing the indexes on the tables, or perhaps modifying the database design.

4) Could it be due to Insufficient useful statistics on indexed columns.

What to do?
Investigate if the query was optimized with useful statistics or not? In SQL Server, statistics on the distribution of values in a column are created automatically on the indexed columns. They can also be created on non-indexed columns either manually using SQL Server Management Studio or CREATE STATISTICS statement, or automatically, if the AUTO_CREATE_STATISTICS database option is set to TRUE. These statistics can be used by the query processor to determine the optimal strategy for evaluating a query. Maintaining additional statistics on non-indexed columns involved in join operations can improve query performance.

Alternatively, you can also monitor the query using SQL Server Profiler or the graphical execution plan in SQL Server Management Studio to determine if the query has enough statistics.

What tools to use?
Ø You can use Create Statistics, AUTO_CREATE_STATISTICS for creating and updating indexes. (Look at the below section for resource links)
Ø You can also use SQL Server Profiler to determine if query has enough statistics
Ø You can also use Graphical Execution plan from SQL Server Management studio to determine if query has enough statistics or not.

5) Could it be due to outdated statistics on indexed columns.

What to do?
Check if the query statistics are up to date? Are the statistics automatically updated?
SQL Server would automatically create and update query statistics on indexed columns (as long as automatic query statistic updating is not disabled). Additionally, statistics can be updated on non-indexed columns either manually, using SQL Server Management Studio or the UPDATE STATISTICS statement, or automatically, if the AUTO_UPDATE_STATISTICS database option is set to TRUE.

Tip: Up-to-date statistics are not dependent upon date or time data. If no UPDATE operations have taken place, the query statistics are still up-to-date.

What tool to use?
You may use Auto_Create_Statistics property of the database to enable or disable the creation of statistics automatically.


6) Could it be due to Insufficient useful indexes.

What to do?
Investigate if suitable indexes are available? Investigate if adding one or more indexes can improve query performance?

7) Could it be the lack of useful indexed views.

What to do?
Identify if there are there any data or index hot spots? You may also consider using disk striping which can be implemented by using RAID (redundant array of independent disks) level 0 in which the data is distributed across multiple disk drives.

8) Could it be due to lack of useful data striping.

What to do?
Check if the query optimizer has provided with the best opportunity to optimize a complex query?

9) Could it be due to improper partitioning of data.

What to do?
Check if you have a large volume of data and if you do then you need to partition that data to achieve maximum performance from your queries. Data manageability is the main benefit of partitioning, but if your tables and indexes on them are partitioned similarly, partitioning can also improve query performance greatly.

Download book on best of SQL Server Central Vol 5 & 6

Guys,

I am sure most of you must be aware of www.SQLSERVERCENTRAL.com. Here is your opportunity to grab Best of SQL Server Central Vol 5 & 6. To get your copy visit the following website (Red Gate).

SQL Server Central Vol 5 & 6

In case you face any problem in downloading the copy, do let me know. I will pass it to you.

Enjoy

More updates on Date Time Data Types

Let’s discuss about new data types introduced in SQL 2008 on date:

1. DATE: Date defines a date without time. Earlier we had datetime data type, which was used to store date values but unfortunately it used to store time also in it. Suppose we have a column DOB in the table, in this case if we have the data type as DATE.

For example: select cast (getdate() as DATE)
The result is 2008-06-04 00:00:00.000

The storage capacity of DATE data type is 3 bytes as compared to 8 bytes for a datetime data type.

2. TIME: Time extracts / stores the value of time. Similar to datetime data type, which had date and time values in it. Now we can store only time using TIME data type. The time is without time zone awareness and is based on a 24-hour clock

For example: select cast (getdate() as TIME)
The result is 10:23:00.5900000

The storage capacity of TIME data type is 5 bytes as compared to 8 bytes for a datetime data type.

3. DateTime2: Defines a date that is combined with a time of day that is based on 24-hour clock. DateTime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

For example:
select cast (getdate() as DATETIME)
The result is 2008-06-04 10:25:06.123
select cast (getdate() as DATETIME2)
The result is 2008-06-04 10:25:06.123

The storage capacity of DateTime2 data type is 8 bytes as compared to 8 bytes for a datetime data type.

4. DateTimeOffset: Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.

For example: select cast (getdate() as DATETIMEOFFSET)
The result is 6/4/2008 10:49:20 AM +00:00

The storage capacity of DateTimeOffset data type is 10 bytes as compared to 8 bytes for a datetime data type.

Date and Time Data Types
The Transact-SQL date and time data types are listed below:

Data type = Time
Format = hh:mm:ss[.nnnnnnn]
Range = 00:00:00.0000000 through 23:59:59.9999999
Accuracy = 100 nanoseconds
Storage size (bytes) = 3-5
User-defined fractional second precision = YES
Time zone offset = NO


Data type = Date
Format = YYYY-MM-DD
Range = 0001-01-01 through 9999-12-31
Accuracy = 1 day
Storage size (bytes) = 3
User-defined fractional second precision = No
Time zone offset = NO



Data type = datetime
Format = YYYY-MM-DD hh:mm:ss[.nnn]
Range = 1753-01-01 through 9999-12-31
Accuracy = 0.00333 second
Storage size (bytes) = 8
User-defined fractional second precision = No
Time zone offset = NO



Data type = datetime2
Format = YYYY-MM-DD hh:mm:ss[.nnnnnnn]
Range = 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999
Accuracy = 100 nanoseconds
Storage size (bytes) = 6-8
User-defined fractional second precision = Yes
Time zone offset = NO



Data type = datetimeoffset
Format = YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm
Range = 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC)
Accuracy = 100 nanoseconds
Storage size (bytes) = 8-10
User-defined fractional second precision = Yes
Time zone offset = Yes

Performance Tuning SQL Server 2008 basics

Performance Tuning SQL Server 2008 basics

SQL Server performance tuning has always been thought of as one of the dark arts. While there have been many tools out there, not many database administrators have known how to use them or how to interpret the results. Because of this, performance tuning has always taken a back seat to project deadlines and unless there were noticeable performance issues with a piece of code or an install of SQL Server, it was not a high priority.


Because performance tuning has traditionally taken a back seat to delivery, database administrator and developers have generally had to be prodded to try to figure out how to trap the performance characteristics of their serves. Generally, this occurred only when a major performance issue cropped up, forcing admins and developers to troubleshooting ongoing issues and spot new issues before they became a major problem.


SQL Server 2008 can be thought of as the release for the database administrator. Many of its features are geared towards the administration of SQL Server and making the database administrator’s job easier to accomplish. With this delivery of administration tools in SQL Server 2008, several features that will help in the performance monitoring and tuning of many SQL Server installations around the world have been included in this next release.


While there are many performance improvements and smaller features found in SQL Server 2008, the main performance tuning features are: Data Collector, Resource Governor, and Policy Management. Utilizing one or more of these new features will help a database administrator to monitor, detect, troubleshoot, and resolve the vast majority of their performance tuning issues in SQL Server 2008.


Data Collector


The Data Collector actually began as the Performance Studio. Despite the name change, its role is to collect data from one or more SQL Server installations into a centralized management warehouse in order to provide a single place the database administrator can go in order to determine the performance characteristics of their database servers and installations.


The Data Collector works by the creation of data collection sets. Data collection sets are a collection of data collectors that roll up a variety of data from many different sources. The sources can be internal to the database, Dynamic Management Views and Functions, or they can be external components such as SQL Server Profiler and Windows System Monitor. The data collectors then store the data in a relational database called the management warehouse so it can be used in performance reports.


Once the data has been collected, a series of predefined and custom reports are built of the stored performance data in order to analyze the data for performance issues. The use of these reports will form the basis of most data performance monitoring solutions. But, since the underlying data is stored in a relational database, additional methods of analyzing and viewing the performance data are possible.


Resource Governor


While the Data Collector serves to collect and analyze performance data, it is a reactive system, as the performance issue must show up before it can be addressed. To combat performance issues as they happen, SQL Server 2008 has made a Resource Governor available to the database administrator. The Resource Governor can be used to categorize incoming connections into different pre-defined workloads and then specify the resources that are available to each of those workloads. This allows the database administrator to prevent certain types of workloads, such as reporting, from utilizing the majority of system resources. This often causes performance issues for the other uses of the database.


The Resource Governor helps to balance the use of the system resources among different types of workloads. It also helps the database administrator make better use of the limited resources that are often found on many database servers.


Policy Management

While not strictly a performance feature, the new Policy Management feature in SQL Server 2008 can be used to help the performance in database environments.


Policy Management enables database administrators to configure different policies that help to manage the instance and database configurations across many different servers and databases. Having a pre-defined policy that covers a set of configuration options helps ensure a consistent set of configurations across all servers and databases. This, in turn, helps database administrators resolve many of the performance issues that often crop up because of incorrect configuration settings.


Summary


While there are many different features of SQL Server 2008, database administrators trying to understand and solve performance tuning issues will find that SQL Server 2008 has included many different features and functionalities to help them in their quest. Database administrators already utilizing performance tools such as Dynamic Management Views and Functions, SQL Profiler, SQL Trace, and Database Tuning Advisor will find that they still exist in SQL Server 2008 and in many cases have been improved in this release. These older tools, along with the new performance features of SQL Server 2008, should help to make this release of SQL Server a favorite among database administrators trying to resolve performance issues.

Table Valued Parameters in SQL 2008

Table-Valued Parameters is a new feature in SQL Server 2008. As the name implies, you can now pass "table type" as a parameter to a function or stored procedure. At a high level the TVP allows you to populate a table declared as a T-SQL variable, then pass that table as a parameter to a stored procedure or function. The benefit of the TVP is that you can send multiple rows of data to the stored procedure or function rather than having to declare multiple parameters or possibly use an XML parameter type to handle a variable number of rows.

According to Books on Line, a TVP is an efficient option for up to 1,000 or so rows. In this tip we are going to gain an understanding of TVP by walking through a simple code sample to demonstrate how to:

Create a table type that can be passed as a TVP to a function or stored procedure

Create a stored procedure that uses a TVP

Declare the table type, populate it with data, and pass it to a stored procedure

Now let's describe our example. In the extract, transform, and load (ETL) process in our data warehousing applications we typically map source system keys to surrogate keys during dimension processing; we then use the surrogate keys to uniquely identify the dimension rows in the warehouse. This allows us to retain the complete history of dimension rows, as each change to a dimension row can be stored in a new row with a new surrogate key. As dimension rows are changed or added, we simply assign a new surrogate key to the source system key and insert a new row into the dimension table. For more details on surrogate key processing see our earlier tip Handling Slowly Changing Dimensions in SQL Server Integration Services (SSIS) Packages.

When processing fact rows we lookup the surrogate keys for the source system keys in the fact and store the surrogate key in the fact table; our queries join the fact table to the dimension table by the surrogate key. Since multiple fact tables typically reference a given dimension (e.g. Customer), the surrogate key lookup provides a good example for using TVP, allowing us to implement the surrogate key lookup one time in a stored procedure, then call it during our ETL process for multiple fact tables.

In addition to simply looking up the surrogate key for a source system key, we also have a situation where a fact table may have a source system key that doesn't exist in a dimension table. In this case we want to create an inferred member in the dimension; i.e. create a new surrogate key and add it to the dimension then update it later when we get the actual dimension row from the source system. For more details on inferred member processing for a dimension, take a look at our earlier tip Handling Early Arriving Facts in SQL Server Integration Services (SSIS) Packages.

The demo code below was only tested on the February, 2008 Community Technology Preview (CTP) of SQL Server 2008.

Create a Table Type
In order to pass a table as parameter to a stored procedure or function, you first create a TABLE TYPE as follows:

CREATE TYPE SourceKeyList AS TABLE (
SourceKey NVARCHAR(50) )
GO 

The T-SQL code is very similar to creating an ordinary table. You can query sys.types in the current database to determine any table types that have been created:

SELECT name, system_type_id, user_type_id
FROM sys.types
WHERE is_table_type = 1

Create a Stored Procedure with a TVP
We are going to create a stored procedure that performs the surrogate key lookup and adds an inferred member if the source key doesn't exist. First we need to create a sample dimension table:

CREATE TABLE dbo.dim_Customer (
sk_Customer INT IDENTITY NOT NULL,
CustomerSourceKey NVARCHAR(50) NOT NULL,
CustomerName NVARCHAR(50) NOT NULL,
InferredMember BIT NOT NULL )

The surrogate key is an integer type and we use the IDENTITY property to automatically assign the next sequential number when inserting rows. The InferredMember column gets set to 1 when we insert a row for a source key that doesn't exist. When the row is extracted from the source system during dimension processing, the inferred row is updated with the columns from the source and the InferredMember column is set to 0.

Now let's create a stored procedure that takes our table type as a parameter and performs the surrogate key lookup and inferred processing:

CREATE PROCEDURE dbo.stp_GetCustomerSK
@source_key_list SourceKeyList READONLY
AS
BEGIN
INSERT INTO dbo.dim_Customer(
CustomerSourceKey, CustomerName, InferredMember
)
SELECT SourceKey, N'INFERRED', 1
FROM @source_key_list k
LEFT JOIN dbo.dim_Customer c ON c.CustomerSourceKey = k.SourceKey
WHERE sk_Customer IS NULL


SELECT sk_Customer, CustomerSourceKey
FROM dbo.dim_Customer c
JOIN @source_key_list k ON k.SourceKey = c.CustomerSourceKey
END
GO 

The TVP must be declared READONLY . You cannot perform any DML (i.e. INSERT, UPDATE, DELETE) against the TVP; you can only reference it in a SELECT statement. The stored procedure joins the TVP to the customer dimension to determine any source keys that do not already exist then inserts them. The stored procedure then joins the TVP to the customer dimension to return a result set of the source key and their corresponding surrogate key.
You can query sys.parameters to view any parameters that have been declared READONLY:

SELECT object_id, name FROM sys.parameters
WHERE is_readonly = 1

GO 

Declare TVP, Populate and Pass as a Parameter. You declare a T-SQL variable as a table type then populate it with rows by using an INSERT statement:

DECLARE @source_key_list SourceKeyList
INSERT INTO @source_key_list
SELECT 'CustomerID_001' 
UNION ALL
SELECT 'CustomerID_002' 
UNION ALL
SELECT 'CustomerID_003'
EXEC dbo.stp_GetCustomerSK @source_key_list
GO 

For demonstration purposes the above SELECT statement just hard-codes values to insert for the source system keys; you would normally do a SELECT DISTINCT from your source system table to extract the list of source system keys on which you want to perform the surrogate key lookup. The output from the above would look like this:

The output shows the surrogate key for each source key.

Hope this helps to understand the function of TVP. Appreciate your feedback / comments.

Regards