Query Hints – FORCE ORDER

SQL Query Optimizer basically determines most efficient way to execute given query by considering several query plans. Query Hints tell the optimizer to apply hints throughout execution of query. Here we will talk about FORCE ORDER query hint.

Example Script:

CREATE TABLE DIM_1(
ROWID INT IDENTITY(1, 1) NOT NULL,
CODE NVARCHAR(50) NULL
)
CREATE TABLE DIM_2(
ROWID INT IDENTITY(1, 1) NOT NULL,
CODE NVARCHAR(50) NULL
)

CREATE TABLE FACT(
ROWID INT IDENTITY(1, 1) NOT NULL,
VALUE_1 NVARCHAR(50) NOT NULL,
VALUE_2 NVARCHAR(50) NOT NULL,
DIM_1_ID INT,
DIM_2_ID INT
)

SELECT *
FROM FACT t1
JOIN DIM_1 t2 ON t1.DIM_1_ID = t2.ROWID
JOIN DIM_2 t3 ON t1.DIM_2_ID = t3.ROWID

Query without hint:

Capture

If you observed SQL Server optimizer rearranges joins order that it thinks will be optimal for your query to execute.

Query with hint:

SELECT *
FROM FACT t1
JOIN DIM_1 t2 ON t1.DIM_1_ID = t2.ROWID
JOIN DIM_2 t3 ON t1.DIM_2_ID = t3.ROWID
OPTION (FORCE ORDER)

Capture1.PNG

When you put this query hint on to your query, it tells SQL Server that when it executes the statement to not change the order of the joins in the query. It will join the tables in the exact order that is specified in the query.

The FORCE ORDER query hint is only used when you want to override the way that SQL Server wants to execute this query.

SQL Server Memory Has Been Paged Out

Microsoft SQL Server manages memory dynamically based on the memory requirements of server load. But sometime you experience problem such as…

  • The performance of SQL Server decreases suddenly.
  • A computer that is running SQL Server stops responding for a short time.
  • A time-out occurs for applications that connect to SQL Server.
  • Problems occur when you run even simple commands or use applications on the system

This problem occurs because the Windows operating system pages out the working set of the SQL Server process. While looking for workaround for this problem I came across a very good article “How to reduce paging of buffer pool memory in the 64-bit version of SQL Server” by Microsoft Support Team which solved my problem.

Resolving Sort Warnings

While googling for “Sort Warning” in execution plan I came a across a very nice article by Margarita Naumova. In this article Margarita has explained very well what exactly the Hash and Sort warning are and how they cause performance problem in SQL SERVER.
Further, How memory grant is related to Hash and Sort warning and the solution such as updating statistics, using compile hints etc. can be leverage to remove these warning.

Link:http://blogs.technet.com/b/magi/archive/2011/03/01/inside-hash-and-sort-warnings.aspx

Implementing Concurrency Control

Technical definition for Concurrency is the ability of a database management system to allow multiple users to access data at the same time while maintaining the integrity and consistency of the data. In other words, Concurrency control deals with the issues involved with allowing multiple users simultaneous access to shared entities, objects, data records, or some other representation.

To understand and implement the concurrency control you need to understand the transactions, detecting and understanding cause of concurrency such as deadlocks, lock timeouts etc. This helps you to understand and implement the concurrency control in your system as well as avoid retrieving the Dirty reads, Phantom reads.Concurrency control ensures the transactions in the database occur in an orderly manner.The main job of these control is to maintain the data consistency and integrity. Technically concurrency must preserve the four characteristics of database transactions: ACID (Atomicity, Isolation, Consistency and Durability). You may find different ways to control the concurrency but Alex Kuznetsov has written a very good explanatory article on Developing Modifications that Survive Concurrency.

I hope you like it. Enjoy reading 🙂 !

Do’s and Don’ts in T-SQL

“What are Do’s and Don’ts while writing the T-SQL?” I heard this many time from developers or new learners or even some of the interviewers. You may get a lot many articles on Google if you search for T-SQL Best practices. In this article I have summarized Do’s & Don’t for your quick reference. I hope this will help you next time you write the T-SQL.

Do’s

  • Finalize or decide upon the database naming convention across the environment.
  • SELECT <required fields>. This will result in less I/O and network traffic and thus better performance.
  • Frequently consider advance SQL or views or table variables. But excessive use of table variable can cause memory overhead.
  • Writing explanatory comments in Code. This helps others to understand the code plainly. Writing or maintain lengthy comments won’t impact the code performance
  • Opt for set based approach instead of procedural approach. In exceptional cases, CURSOR can be avoided using WHILE loops.
  • If your database contains heavy text based search opt for FULL text search instead of LIKE operator.
  • Using SET NOCOUNT hint at the beginning of the code this improves the performance by reducing the network traffic.
  • Avoid Dynamic SQL. Dynamic SQL likely to be slower than the static SQL, as it generates the execution plan every time at runtime.
  • Using Error handling in stored procedures. @@ERROR or TRY CATCH.
  • Use the Column list in INSERT statement.
  • Using Transaction Handling (BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION) if transaction involves more than one data manipulation (INSERT/UPDATE/DELETE) operation.
  • Use single (not double) blank lines to separate logical pieces of T-SQL code, and do so liberally

Don’ts

  • Use SELECT *.
  • Use temporary tables in processing data. It creates a more disk IO which could hit the performance.
  • Use the server side cursor
  • Use Sub queries in JOIN. In Sub-query Inner query is executed for each and every row in Outer query. This can be avoided using JOINS.
  • Use prefix for object “sp_”. The prefix “sp_” is reserved for System procedures.
  • Use the Index Name in Query.
  • Use wildcard character at the beginning of the word while searching using LIKE keyword.i causes index scan instead of index seek
  • Use locking Hints in SQL statement as SQL server automatically handles the locking in a way to minimize the level of locking.

For more Do’s and Don’t please navigate through the MSDN.

Index de-fragmentation with SQL Server

Here is the informational video by Kimberly L. Tripp for beginners as well as those who want to understand Index de-fragmentation more. In this video Kimberly explain about…

  • How Fragmentation occurs?
  • What Fragmentation means?
  • How to see and minimize fragmentation?
  • Automating the index Rebuilds etc.

Video Link = http://www.microsoft.com/emea/spotlight/sessionh.aspx?videoid=30

Few points you should consider while creating Index

The purpose of an index in SQL Server is to allow the server to retrieve requested data, in as few I/O operations as possible, in order to improve performance. To make right indexing choice, you might get the n number of recommendations or best practices document on the web. While I would recommend going on below link the video by Kimberly Tripp on Index creation with SQL Server 2005 (Note this video also applies to SQL Server 2008)

http://www.microsoft.com/emea/spotlight/sessionh.aspx?videoid=29

Hints that you should keep in mind while creating Clustered index:

  • Columns that contain a large number of distinct values.
  • Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=.
  • Columns that are accessed sequentially.
  • Queries that return large result sets.
  • Columns that are frequently accessed by queries which involves JOINs or GROUP BY clauses; typically these are foreign key columns. An index on the column(s) specified in the ORDER BY or GROUP BY clause eliminates
    the need for SQL Server to sort the data because the rows are already sorted. This improves query performance.
  • OLTP-type applications where very fast single row lookup is required, typically by means of the primary key.
    Create a clustered index on the primary key (this is the default state of SQL Server).

Hints that you should keep in mind while creating Non-Clustered index:

  • Columns that contain a large number of distinct values, such as a combination of last name and first name (if a clustered index is used for other columns). If there are very few distinct values, such as only 1 and 0, most queries will not use the index because a table scan is usually more efficient.
  • Queries that do not return large result sets
  • Columns frequently involved in search conditions of a query (WHERE clause) that return exact matches.
  • Decision-support-system applications for which joins and grouping are frequently required.
    Create multiple non-clustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns.
  • Covering all columns from one table in a given query. This eliminates accessing the table or clustered index altogether.