Jun 6, 2013

What is Adhoc query in sql server: cached Query Plan


Just like any programming language, there are two steps to execute any queries:

1. Compilation
2. Execution

I am showing it by examples. For this we are creating a table tblEmployee and inserting few records into it:

CREATE TABLE SqlMessage(
     MsgId BIGINT IDENTITY PRIMARY KEY,
     Severity INT,
     Msg NVARCHAR(4000)
)

INSERT INTO SqlMessage
SELECT severity,[text] FROM sys.messages

Now I am enabling the statistics time to get the execution and compilation time of a SQL query:

SET STATISTICS TIME ON

Now we are executing following sql query:

SELECT DISTINCT MsgId * Severity, Msg 
FROM SqlMessage
WHERE MsgId  > 1

In message tab we will find:

SQL Server parse and compile time: CPU time = 514 ms, elapsed time = 724 ms.
SQL Server Execution Times: CPU time = 172 ms, elapsed time = 2114 ms.

Here,
Parse time: Total time to validate the syntax of the query.
Compile time: Total time to generate the execution plan of a query and keep in a cached plan.
Execution time: Total time to execute the SQL query.
Elapsed time: Total time perform compilation or execution that is from beginning to end.
CPU time:  In total elapsed time how much time is used by CPU is called CPU time.

It is obvious that first query has got complied then executed. Now we are again executing the same query. In this query will be compiled or not? 
In message table we will find:

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times: CPU time = 297 ms, elapsed time = 2544 ms.

The query has not been compiled!! Ok, we are changing the parameter value in the same query:

SELECT DISTINCT MsgId * Severity, Msg 
FROM SqlMessage
WHERE MsgId  > 2

Now, in this time query will be compiled or not? Let's execute the query and check the message tab:

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 3 ms.
SQL Server Execution Times: CPU time = 296 ms, elapsed time = 2335 ms.

In this case, the query has been compiled but took less time than previous. If you change the case or put some extra spaces in the query, SQL server will first compile the query before executing it. 

How SQL server know when a query has to compile or not?

The answer is pretty simple. Before executing any query first SQL server checks the cached plan if it doesn't find the query in it then it compiles the query and adds in cached plan otherwise directly execute the query. Now we are going to check the cached plan. Before this it would be good first empty the cached plan:

--To empty cached Query Plan
DBCC FREEPROCCACHE

Now we are executing the first query:

SELECT DISTINCT MsgId * Severity, Msg 
FROM SqlMessage
WHERE MsgId  > 1

To check the cached plan execute following sql query:

SELECT objtype,[text]
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'%SqlMessage%'
     AND text NOT LIKE '%sys.dm_exec_cached_plans%'

Output:
objtype
text
Adhoc
SELECT DISTINCT MsgId * Severity, Msg    FROM SqlMessage WHERE MsgId  > 1 

So, SQL server has kept the cached plan. If you will execute the same query you will not find any new entry it. Now if we will change the parameter:

SELECT DISTINCT MsgId * Severity, Msg 
FROM SqlMessage
WHERE MsgId  > 2

And executes the query, in cached plan we will find:

objtype
text
Adhoc
SELECT DISTINCT MsgId * Severity, Msg    FROM SqlMessage WHERE MsgId  > 2
Adhoc
SELECT DISTINCT MsgId * Severity, Msg    FROM SqlMessage WHERE MsgId  > 1

There is one more entry with different parameters. These types of SQL queries are called ad-hoc queries.

Properties of adhoc query:

1. It is case sensitive
2. Space sensitive
3. Parameter sensitive

I am explaining it by the demo that ad-hoc query is how much worse for us. I am writing in c# code. You can write equivalent code any other programming language:

using System;
using System.Data.SqlClient;
using System.Data;

Namespace AddHoc1
{
    Class Program
    {
        static void Main(string[] args)
        {
            SqlConnection Conn = new SqlConnection();
            SqlDataReader Reader = null;
       
            try
            {
                Conn = new SqlConnection(@"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MFS;Data Source=RITESHK7");
                Conn.Open();
                SqlCommand Command = Conn.CreateCommand();


                for ( int Id = 0; Id < 10000; Id++)
                {
                    Command.CommandText = "SELECT MsgID, Severity FROM SqlMessage WHERE MsgID =" + Id.ToString();

                    Reader = Command.ExecuteReader();

                    while (Reader.Read())
                    {
                        Console.WriteLine("{0}\t{1}", Reader["MsgID"], Reader["Severity"]);
                    }

                    Reader.Close();

                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
                Console.ReadKey();
            }
            finally
            {
                Conn.Close();
                Console.ReadKey();
            }
        }
    }
}

This c# code executes the same sql query in 10000 times with different MsgID (parameter). Now we are executing this C# code. If will check the cached query we will find there are 10000 entries in it with value of MsgID:

objtype
text
Adhoc
SELECT MsgID, Severity FROM SqlMessage WHERE MsgID =9999
Adhoc
SELECT MsgID, Severity FROM SqlMessage WHERE MsgID =9998
Adhoc
SELECT MsgID, Severity FROM SqlMessage WHERE MsgID =9997
Adhoc
SELECT MsgID, Severity FROM SqlMessage WHERE MsgID =9996
Adhoc
SELECT MsgID, Severity FROM SqlMessage WHERE MsgID =9995
Adhoc
SELECT MsgID, Severity FROM SqlMessage WHERE MsgID =9994
-----
-------------------------------------------------------
Adhoc
SELECT MsgID, Severity FROM SqlMessage WHERE MsgID =5
Adhoc
SELECT MsgID, Severity FROM SqlMessage WHERE MsgID =4
Adhoc
SELECT MsgID, Severity FROM SqlMessage WHERE MsgID =3
Adhoc
SELECT MsgID, Severity FROM SqlMessage WHERE MsgID =2
Adhoc
SELECT MsgID, Severity FROM SqlMessage WHERE MsgID =1
Adhoc
SELECT MsgID, Severity FROM SqlMessage WHERE MsgID =0
(It is part of total output)

Effect of this faulty c# code:

1. SQL server will take extra (n * Compilation time) ms to get records
2. Extra time to insert records in the cached plan.
3. SQL server has to frequently fire a job to delete the cached plan since it will reach the max limit very soon.
4. It will not only decrease the performance of this SQL query but also other SQL queries of application since this faulty code will force to delete cached query plans of another sql statement.

Now we are writing same c# code in different ways. In this code we will pass the value of parameter MsgID in parameterized ways:

using System;
using System.Data.SqlClient;
using System.Data;

namespace AddHoc2
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection Conn = new SqlConnection();
            SqlDataReader Reader = null;
            SqlParameter Param;

            try
            {
                Conn = new SqlConnection(@"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MFS;Data Source=RITESHK7");
                Conn.Open();
                SqlCommand Command = Conn.CreateCommand();

                Command.CommandText = "SELECT MsgID, Severity FROM SqlMessage WHERE MsgID = @Msgid";
                Param = new SqlParameter("@Msgid", SqlDbType.Int, 0);
                Command.Parameters.Add(Param);

                for (int Id = 0; Id < 10000; Id++)
                {

                    Param.Value = Id;

                    Reader = Command.ExecuteReader();

                    while (Reader.Read())
                    {
                        Console.WriteLine("{0}\t{1}", Reader["MsgID"], Reader["Severity"]);
                    }

                    Reader.Close();
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
                Console.ReadKey();
            }
            finally
            {
                Conn.Close();
                Console.ReadKey();
            }
        }
    }
}


Before executing this c# code first we are cleaning the cached plan:

--To empty cached Query Plan
DBCC FREEPROCCACHE

If we will execute the above c# code and check the cached plan we will find:
objtype
text
Prepared
(@Msgid int)SELECT MsgID, Severity FROM SqlMessage WHERE MsgID = @Msgid

There is only one entry in the cached plan. It is called prepared query. In another word, we can say any non-parameterized queries are called ad-hoc query while a parameterized query is called prepared query. We should write our programming code in such way that it is not ad-hoc type. It should be prepared type so that SQL Server will reuse the compiled execution plan.


Indexes best practices in SQL server: Where to start creating indexes?
SQL Server query optimization tips: Tuning best practices with examples
Fastest or most efficient way to insert data in SQL server
Index in SQL server with examples
What is clustered index in SQL server
Write SQL queries in set-based approach SQL server

4 comments:

  1. Hi,
    Both the code samples are exactly the same. I think instead of copying the prepared statement code, you have put in the prev code sample.
    arun

    ReplyDelete
    Replies
    1. Dude, just open ur eyes, and dont just read the article, rather try to put the code in program ,SQL window, it will clear u more.

      well for ur info, first code contains WHERE MsgID =" + Id.ToString(); and the second code containf a parameter WHERE MsgID = @Msgid";

      try to follow the steps, gud article to clear concepts!

      Delete
  2. Anonymous2/14/2014

    Hi Muhammad Raza,

    Today i saw your article. Nice article. Good Job Raza

    ReplyDelete
  3. Thanks Ritesh,
    Great Article to know the fundamentals that how SQL Server is doing its work behind the scene.
    How bad code can impact the performance of application.

    ReplyDelete