Query Performance – Function doesn’t use index seek

Indexes play an important role in SQL query performance, but improper knowledge of how indexes work, can lead to degraded performance. In this article I will throw some light on one such practice. We all know indexes help in select statements and make other statements (insert, update, delete) slower. At the same time if we use a function on indexed column in where statement, it does not use the index and hence query performs as it does not have a index. Lets look into real time example

Step 1. Create a table Person :

CREATE TABLE [dbo].[Person](
[ID] [char](800) NULL,
[FirstName] [char](2000) NULL,
[LastName] [char](3000) NULL,
[City] [char](500) NULL

Step 2. Insert some random data :

INSERT INTO Person (ID,FirstName,LastName,City)
ELSE ‘Mahesh’ END,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN ‘New Delhi’
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN ‘Chennai’
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN ‘Hyderabad’
ELSE ‘Bangalore’ END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b

Step 3.  Execute a select statement with where clause on City :

WHERE  City = ‘Chennai’

Note : Till now we do not have any index on City column.

Following is the execution plan

Execution Plan Without Index

Look at the query execution plan, SQL Server did a table scan which is expected because there is no index on the City column.

Step 4. Create a non clustered index on City column :

ON Person (City);

Step 5. Execute following select statement :

WHERE  City = ‘Chennai’

Notice the execution plan (below) now, SQL Server is using index seek.

Execution Plan With Index

Step 6. Now lets execute a select query which uses function on column name in where clause :

WHERE CONVERT(Varchar(500), City) = ‘CHENNAI’

Below is the Query Execution Plan. SQL Server is using Scan which contributes to 29% of the total cost of query whereas in step5, it used Seek which contributed to only 5% of the total cost.

Execution Plan With Index With Function

Conclusion :

So in summary , the above steps show that SQL server was able to perform a seek operation on City column but when a function is used on the column it was not able to perform seek and hence it performed scan operation.

Impact of sp_ prefix on stored procedure performance

Naming conventions are integral part of a quality code but sometimes we need to pay additional care while deciding on the naming patterns. There are two type of programmers, one who prefix everything and the other who don’t. In world of SQL server, the former group is further divided in two parts, one who use sp_ prefix for naming their stored procedures and others who use something else (usp_ or short app name). The recommendation has been to avoid sp_. In this article I will explain the reasons for this recommendation with supported data from my experiments.

sp_ does not stand for Store Procedure

The myth is sp_ stands for stored procedure, infact it means System Stored Procedure. SQL server has some system stored procedures defined in master database. These procedures starts with sp_ prefix and are accessible from any other database. One such example is sp_helptext. If you want to have the text of a stored procedure execute sp_helptext‘<SP Name>’.

Performance impact

To understand the impact on performance lets create a database say DummyDb. Create a table DummyTable and insert 100 rows

–Create database



–Create Table

CREATE TABLE [dbo].[DummyTable](

       [Id] [intNULL,


[varchar](100) NULL



–Insert 100 rows

DECLARE @Count int;

SET @Count = 100;



       INSERT INTO DummyTable VALUES (@Count, ‘Name_’+ CONVERT(varchar(100),@Count));

       SET @Count = @Count1;



Now lets create two stored procedures GetEmployee
and sp_GetEmployee

–Create GetEmployee




       SELECT ID, Name FROM DummyTable



–Create sp_GetEmployee




       SELECT ID, Name FROM DummyTable




Lets execute both the stored procedures

1. Execute GetEmployee

DECLARE @Count int;

SET @Count = 500;



       exec GetEmployee

       SET @Count =



 I executed the above statement three times and it took 38, 41 and 38 seconds which means it comes to be 117/1500 = 0.078 seconds per execution (Although it will not be same every time, but 1500 is a good sample for comparison).


DECLARE @Count int;

SET @Count = 500;



       exec sp_GetEmployee

       SET @Count =



 I executed the above statement three times and it took 40, 41 and 41 seconds which means it comes to be 122/1500  = 0.081 seconds per execution (Although it will not be same every time, but 1500 is a good sample for comparison).

The above data clearly prooves the sp_ prefix degrades the performance of stored procedure.

Why is it slow?

Whenever SQL server gets an command to execute a stored procedure with sp_ as a prefix, it first scans all the system stored procedures. If it doesn’t find a matching procedure there, then it scans the user defined stored procedures. This additional time causes the performance hit.

Why Strings Are Immutable In Dot Net?

Next Read : An Extensive Examination Of ArrayList in C#

Most of the developers know that strings in .Net are immutable. But a very few knows the reason behind this behavior.  I will try to explain the same in this article.

Before diving into the reason, let me first explain what do we mean by immutable?

What do we mean by Immutable strings? 

The dictionary meaning of immutable is “unchanging over time or unable to be changed”. This means once a value is assigned to String object, it can never be changed. Yes, you read it correctly. Consider the following code:


Output of this code will be.


Though it seems as if we just changed the value of myString from “abc” to “abcdef” and then to “abcdefghijkl”, but we really didn’t! Lets try to understand it. In first step, a new string object is allocated on the heap with value of “abc” and myString points to this memory location. At step no 2 (myString += “def”;), a new string object is allocated on heap with value of “abcdef” and myString now points to this new memory location. But the string “abc” still exists on heap. So we actually sit with two string objects on the heap, even though we’re only referencing one of them. Continuing same way, at the end of this code we will have four string objects, with only one object referenced and other three unused. The following memory allocation diagram of above code will make things more clear.

Memory Allocation of immutable strings
Memory Allocation of immutable strings

Now we will move on to the context of why.

Why strings are immutable in dot net?

Designers of .Net decided to implement immutable text strings. They have multiple reasons for this architecture. If programmers have multiple string variables with same value, it will avoid allocating memory for same string value multiple times. It will allocate memory to string once and all the variables will point to the same memory block.  Consider the following block of code.

same string

Memory allocation for this code will look like this:

Memory Allocation of Same String
Memory Allocation of Same String

If strings were mutable, changing the value of str1 would have changed the value of str2 and str3 also, which is unwanted.

Second, immutable strings eliminates race conditions in multi threaded applications. Any text amendment causes creation of a new variable so there is no need to set up the lock to avoid conflicts while multiple threads simultaneously access text. In some cases, those race conditions could be used to mount security attacks.  For example, you could satisfy a FileIOPermission demand with a string pointing to an publicly accessible section of the file system, and then use another thread to quickly change the string to point to a sensitive file before the underlying CreateFile occurs.

Another reason for string immutability is the well adapted use of strings as keys in hashtables. The objects on which the hash values are computed must be immutable to make sure that the hash values will be constant in time.

Another cool thing about string immutability is that even though System.String is a class, string objects get compared with equivalence, as a value type. This is possible because we can consider that the identity of an immutable object is its state. Consider following piece of code:

String Comparison

Even thought str1 and str2 reference 2 different objects ,the above code returns true.

StringBuilder : An alternative to avoid creation of unused strings

As we saw in figure “Memory Allocation of immutable strings”, there are unused strings allocated in memory. Its because of the way string behaves. If a code makes thousands of operations on string , heap will have thousands of unused string objects leading to unwanted memory wastage. Fortunately we can avoid this by using StringBuilder class. In my next article I will discuss about this class.

I hope you must have enjoyed the article. Please leave your comments and feedback in comments section at bottom. If you have any doubt or query, please feel free to ask in comments. Thanks for reading.

Click to read it on LinkedIn

Next Read : An Extensive Examination Of ArrayList in C#

An Extensive Examination Of ArrayList in C#

Next Read : Why Strings Are Immutable In Dot Net?

We all use Arrays in c# and other programming languages. Array creates some limitations on design. First, Arrays are homogeneous i.e. you can store only one type of elements. Secondly, when using arrays you must specifically allocate a certain number of elements. Often developers want something more flexible – specially for uncertainty in size of collection. The .Net Framework Base Class Library provides such a data structure called ArrayList located in System.Collections Namespace.

ArrayList is nothing but a Heterogeneous and Self Re-Dimensioning Array :

Elements of different types can be added to the ArrayList. Further, we do not have to concern ourselves with redimensioning the ArrayList. All of this is handled automatically for us. An example of the ArrayList in action can be seen in the code snippet below.

Array List Example
Array List Example

Behind The Scenes :

Behind the scenes the ArrayList uses System.Array of type Object. An object array can hold elements of any type Since all types are derived from Object (either directly or indirectly). By default the size of this array is 16, although it can be defined in constructor or by assigning capacity property. Elements can be added to ArrayList using Add() Method. Behind the scenes , Add() method first compares the no of elements in array with its capacity. If adding the new element causes the count to exceed the capacity, the array is redimensioned and the capacity is automatically doubled.

Performance :

ArrayList provides some additional flexibility compared to array, but this flexibility comes at cost of performance, majorly if you store value types. The ArrayList’s internal array is of object type, so every value type is boxed and stored on heap and each ArrayList element is a reference to a boxed value type. When you access a value type element it is unboxed before you can use it.

The boxing and unboxing, along with the extra level of indirection that comes with using value types in an ArrayList, can hamper the performance of your application when using large ArrayLists with many reads and writes.

ArrayList Data Structure Memory Allocation
The ArrayList contains a contiguous block of object references

The above diagram shows the memory allocation for ArrayList.

The sel-redimensioning of ArrayList should not cause a performance degradation if compared to array. Because you can turn off self-redimensioning by specifying the initial capacity in constructor. If you dont know the exact size, you may have to re-size even with array also when the number of elements inserted increases the size of array.

Memory Allocation on Redimensioning:

Why the size of ArrayList gets doubled when it gets redimensioned? Its a classic computer science  problem to find out how much extra memory should be allocated when running out of space in some buffer.

One option is to allocate just one more element in the array when redimensioning. i.e. if the initial size of array was 10 and when adding 11th element, resize the array to 11. This approach conserves most of the memory but becomes very costly as redimensioning is required at insertion of every additional element.

Second option is to redimension the array 100 or 200 times larger than the current size. i.e. if array is initially allocated 10 element, before inserting 11th element resize it to 1000 elements. This approach greatly reduces the redimensioning overhead , but , if only a few more elements needs to be added, the extra allocated space is wasted.

So after trying various options, the true compromise is to just double the size of array when it becomes exhausted. This is the precise approach that ArrayList takes and its all done automatically for us.

Summary :

  1. ArrayList internally uses array of object type.
  2. ArrayList provides more flexibility than simple array.
  3. Precise size of ArrayList can be set in constructor or by capacity property. By default its 16.
  4. While adding, if no of elements in array exceeds its capacity, array is redimensioned to double of its current size.
  5. Boxing and Unboxing of value types degrades performance of arraylist.

Next Read : Why Strings Are Immutable In Dot Net?


Shortcuts For Debugging In Visual Studio

Visual Studio DebuggingDebugging is one of the most important aspect of programming. It is crucial to successful software development, but even many experienced programmers find it challenging. With the help of keyboard shortcuts , it can be made faster. So here is the list of all shortcut keys available in visual studio for debugging.

Debugging Shortcuts in Visual Studio



Ctrl-Alt-V, A
Displays the Auto window to view the values of variables currently in the scope of the current line of execution within the current procedure
Temporarily stops execution of all processes in a debugging session. Available only in run mode
Displays the Breakpoints dialog, where you can add and modify breakpoints
Displays the Call Stack window to display a list of all active procedures or stack frames for the current thread of execution. Available only in break mode
Clears all of the breakpoints in the project
Displays the Disassembly window
Enables or disables the breakpoint on the current line of code. The line must already have a breakpoint for this to work
Displays the Exceptions dialog
Displays the Immediate window, where you can evaluate expressions and execute individual commands
Ctrl-Alt-V, L
Displays the Locals window to view the variables and their values for the currently selected procedure in the stack frame
Ctrl-Alt-M, 1
Displays the Memory 1 window to view memory in the process being debugged. This is particularly useful when you do not have debugging symbols available for the code you are looking at. It is also helpful for looking at large buffers, strings, and other data that does not display clearly in the Watch or Variables window
Ctrl-Alt-M, 2
Displays the Memory 2 window
Ctrl-Alt-M, 3
Displays the Memory 3 window
Ctrl-Alt-M, 4
Displays the Memory 4 window
Displays the Modules window, which allows you to view the .dll or .exe files loaded by the program. In multiprocess debugging, you can right-click and select Show Modules for all programs
Opens the New Breakpoint dialog
Displays the Quick Watch dialog with the current value of the selected expression. Available only in break mode. Use this command to check the current value of a variable, property, or other expression for which you have not defined a watch expression
Displays the Registers window, which displays CPU register contents
Terminates the current debugging session, rebuilds if necessary, and then starts a new debugging session. Available in break and run modes
Displays the Running Documents window that displays the set of HTML documents that you are in the process of debugging. Available in break and run modes
Starts or resumes execution of your code and then halts execution when it reaches the selected statement. This starts the debugger if it is not already running
Sets the execution point to the line of code you choose
Alt-NUM *
Highlights the next statement to be executed
If not currently debugging, this runs the startup project or projects and attaches the debugger. If in break mode, this allows execution to continue (i.e., it returns to run mode).
Runs the code without invoking the debugger. For console applications, this also arranges for the console window to stay open with a “Press any key to continue” prompt when the program finishes
Executes code one statement at a time, tracing execution into function calls
Executes the remaining lines of a function in which the current execution point lies
Executes the next line of code but does not step into any function calls
Available in break and run modes, this terminates the debugging session
Ctrl-Alt-V, T
Displays the This window, which allows you to view the data members of the object associated with the current method
Displays the Threads window to view all of the threads for the current process
Sets or removes a breakpoint at the current line
Displays the disassembly information for the current source file. Available only in break mode
Ctrl-Alt-W, 1
Displays the Watch 1 window to view the values of variables or watch expressions
Ctrl-Alt-W, 2
Displays the Watch 2 window
Ctrl-Alt-W, 3
Displays the Watch 3 window
Ctrl-Alt-W, 4
Displays the Watch 4 window
Displays the Processes dialog, which allows you to attach or detach the debugger to one or more running processes