I had an interesting time at the office recently. We were undertaking a performance tuning exercise for the canned reports of an OLTP that were migrated from Crystal Reports to SSRS, and had to come up with some ground rules for all database developers to follow when re-engineering queries.

One of the basic things to be ensured during a performance tuning exercise is that the queries must be written in such a way that once an execution plan is generated, SQL Server reuses a given execution plan for subsequent executions of the same query.

The above statement seems very simple, but there is a lot of between-the-lines reading that needs to be done before the true meaning of the statement can be understood.

Some basics – Execution & Query Plans

When a query is submitted to the SQL Server database engine for the first time, the query optimizer processes the query (query processing is a huge topic, and out-of-scope for this discussion. For more information, you can refer the BOL page on Query processing here) and generates a two execution plans:

1. For serial execution
2. For parallel execution

Each Execution plan (BOL Reference here) consists of:

1. Query Plan
   • This is the bulk of the query plan, and does not contain the Execution context
2. Execution Context
   • The execution context is unique for each user running the plan because it contains the parameter values for each execution per user

The prime concern is the re-use of the query plan portion of the Execution Plan. This is because generation of execution plans takes time & resources – both of which are scarce.

The importance of using the proper case when writing T-SQL Queries

The query plan, is internally stored as a text value – a string, which is what we see if we execute a query with the SHOWPLAN operator.

When a query is submitted, it is parsed and one of the first things that happens afterwards is that Microsoft SQL Server attempts to find a suitable match for the query plan from the plan cache. This is done via a simple, but crucial to understand string comparison. The problem is that most developers fail to realize that this comparison is a case-sensitive comparison.

Let’s understand this via a simple demo.

Demo

We will begin by clearing out the procedure cache.

DBCC FREEPROCCACHE
GO

Next, let us run a simple T-SQL query (twice) to fetch a list of all the employees in an organization:

USE AdventureWorks2008R2
GO

SELECT * FROM HumanResources.Employee
GO 2

Now, let us use the DMVs – sys.dm_exec_cached_plans, sys.dm_exec_query_plan and sys.dm_exec_sql_text to look at the procedure cache:

--Run this in a separate window

USE AdventureWorks2008R2
GO

SELECT cp.refcounts, cp.usecounts, cp.size_in_bytes, cp.cacheobjtype, cp.objtype, st.text, qp.query_plan, cp.plan_handle 
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) st
GO

You can see from the usecounts column that Microsoft SQL Server used the query plan it generated twice.

Now, assume that different developers working on the same application used some other variants of the same query:

USE AdventureWorks2008R2
GO

SELECT * FROM HUMANRESOURCES.EMPLOYEE
GO

SELECT * FROM HumanResources.Employee
GO;

Let us take a look at that plan usage again:

--Run this in a separate window

USE AdventureWorks2008R2
GO

SELECT cp.refcounts, cp.usecounts, cp.size_in_bytes, cp.cacheobjtype, cp.objtype, st.text, qp.query_plan, cp.plan_handle 
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) st
GO

You can clearly see that SQL Server used a different version of the query plan for each of the two query variants.

Moral of the story

When writing queries, please avoid:

1. Changes to letter case in the text
2. Changes to white space
3. Changes to literal values
4. Changes to text inside comments

Most important: If you are using Microsoft SQL Server 2008 and above – always use intelli-sense to be safe!

Until we meet next time,

Be courteous. Drive responsibly.

“Long years ago we made a tryst with destiny, and now the time comes when we shall redeem our pledge, not wholly or in full measure, but very substantially. At the stroke of the midnight hour, when the world sleeps, India will awake to life and freedom.” ~Pandit Jawaharlal Nehru

These words were spoken by India’s first Prime Minister as part of a speech delivered in the Constituent Assembly, New Delhi, on August 14, 1947, on the eve of the attainment of Independence. Today is India’s 64th Independence Day, and a time to remember the thousands of individuals who laid down their lives in the struggle for independence.

It is a holiday here in India, and if you are working today, I hope that you get to go home on time (if not early) and that you have an otherwise uneventful Monday. I am also on a long-pending holiday. It’s been non-stop work since the last 1.5 years and I was on the edge of pulling my hair out!

I will therefore, present a selection from the posts that I have written till date. These may not have the highest read counts, but they are somehow near & dear to my heart. Some have even come as a result of a query that you, the kind and respected reader, a friend or a colleague might have asked.

Posts:

1. CAST v/s CONVERT – A Performance Comparison
2. CAST v/s CONVERT – Is there a difference as far as SQL Server is concerned? Which is better?
3. Measuring the number of rows in a table – Are there any alternatives to COUNT(*)?
4. The multiple connections of SSMS
5. SQL Server Myth- Log files are removed when a database is made READ_ONLY
6. “GO” as Batch Separator – Customize batch separator in SSMS & SQLCMD – What would you use instead of GO?
7. The Ghost of the Y2K
8. UNIX timestamp and the Year 2038 problem
9. Questions from you!
• “Ad hoc update to system catalogs is not supported.” when using RECONFIGURE
• Clearing SSMS History
10. Other contributions:
• Securing TRUNCATE permissions in Microsoft SQL Server – SQLAuthority.com
• Connection Strings 101 – SQLServerCentral.com
• Deleting large number of rows from a table & a heap in a VLDB – SQLServerCentral.com
• Guest Post for Pinal’s series on Interview Questions & Answers – SQLAuthority.com

Series:

1. Underappreciated features of Microsoft SQL Server
2. VTD Recorded session & PPT – Learn Underappreciated Features of Microsoft SQL Server
3. Tech-Ed India 2011 – Part 01, Part 02, Part 03
4. T-SQL Worst Practices – a compilation from Jacob’s session at Tech-Ed 2011 – Part 01, Part 02, Part 03
5. T-SQL Debugging
• Enabling the Debugger
• Basic debugging demo
• Troubleshooting T-SQL Debugger – Connection Errors & Firewall warnings, MSDBG2.dll

On this day, I urge you all to Be courteous, Drive responsibly.

SQL 11 (“Denali”) has been around us for quite some time now and is almost ready for making a dash towards the “Release Candidate” (RC) state with Community Technology Preview (CTP) 03 being released last month. That being said, this post might seem a little late, but I tend to unconsciously study the new productivity features of SSMS that we, as the developer community tend to use more.

One such productivity feature that I welcome about the SQL 11 (“Denali”) is the fact that Intellisense in SSMS now uses a “like” search. To demonstrate my point, let’s try to search for the AdventureWorks database (While I am demonstrating this with database names, you would notice that it is true for all database objects).

SQL Server 2008 SP2	SQL 11 (“Denali”) CTP 03

Here’s how this feature is useful.

If we want to look for all sales orders related information, we can be pretty sure that the information would be in the Sales schema. We can search for the word “orders” and get all the objects that have the word “orders” in their name, giving us the opportunity to scan through a filtered list, thus increasing our chances of remembering the correct object name.

I am bad at remembering names and therefore, this is a productivity enhancement for me. As time goes along, I will keep sharing other such enhancements that I use with SQL 11 (“Denali”), CTP03.

Until we meet next time,

Be courteous. Drive responsibly.

Today, I attempt to bust a myth related to transaction log files for read-only databases in Microsoft SQL Server. Read-only databases, as the name suggests are databases containing static data. Quite often, applications use read-only copies of 3rd party data, restored in the data-center as read-only databases. For instance, there are services that provide a universal lookup for ISO codes for currencies & countries. Countries and currency codes do not change daily, and it makes great sense to convert the databases to read-only databases.

Just recently, I read in a book something that jolted me because it was an authoritative book. The sentence goes something like:

“When a database is placed in READ_ONLY mode, SQL Server removes any transaction log file that is specified for the database.”

The above statement is incorrect. What is correct is that the transaction log continues to exist – irrespective of whether or not the database is in READ_ONLY mode.

When in doubt, I always believe that a simple test should be done to confirm. So, here’s a very simple, do-it-yourself test that you can run on your test/development server:

--Step 01: Create a test database & confirm that it is not READ_ONLY
USE master
GO
CREATE DATABASE ReadOnlyTest
GO

SELECT sdb.is_read_only,
       sdb.* 
FROM sys.databases sdb 
WHERE sdb.name = 'ReadOnlyTest'

--Step 02: Confirm that the database has 2 files - one primary data file, one log file
USE ReadOnlyTest
GO
SELECT 'Before',* FROM sys.sysfiles
GO

--Step 03: Make Database READ_ONLY and confirm
USE master
GO
ALTER DATABASE ReadOnlyTest SET READ_ONLY
GO

SELECT sdb.is_read_only,
       sdb.* 
FROM sys.databases sdb 
WHERE sdb.name = 'ReadOnlyTest'

--Step 04: Confirm that the database still has 2 files - one primary data file, one log file
USE ReadOnlyTest
GO
SELECT 'After',* FROM sys.sysfiles
GO

--Step 05: Finally drop the test database as cleanup
USE master
GO
DROP DATABASE ReadOnlyTest
GO

Here’s what you would see:

What we know for sure is that the log files are not “removed”. Using the undocumented DBCC commands like DBCC LOGINFO and DBCC LOG, I could determine to a fair level of confidence that the log file is not used for some common read-only operations (SELECT, BACKUP).

Do you know why the transaction log file is required for READ_ONLY databases? Do let me know. I look forward eagerly to your response.

Until we meet next time,

Be courteous. Drive responsibly.

As most of the readers of my post know, I have always tried to find features of the SQL Server Management Studio (SSMS) that boost productivity & allow for efficient administration. One such feature is that SSMS allows us to customize the query options for any particular query window (Refer Appendix A at the end of this post).

Query Result options – Discard result after query executes

Recently, I was exploring the various query result settings available to us in the SSMS for Results to Grid and Results to Text modes. I spotted the “Discard result after query executes” option.

This option was new to me (I had never heard about it, let alone use it). So, I enabled the option for grid results and started exploring.

I ran the following query, after enabling the option:

SELECT * FROM HumanResources.Employee
GO

To my astonishment, no results were returned. I always have the actual execution plan enabled on my test instance, and surprisingly, the execution plan was produced.

1. Notice that in the screen-shot below, we do not have the results tab!
2. Upon studying the Properties window, we find that the query was executed and 290 records where fetched by the database engine
3. However, no records were returned to the SSMS query window

Performance Benefits?

I then started looking up in the Books On Line about this option, but found only one line:

BOL Page: http://msdn.microsoft.com/en-us/library/ms190144(SQL.110).aspx

Description: “Frees memory by discarding the query results after the screen display has received them.”

If the memory is being freed, it generally indicates a boost in performance. So, I generated a query that would take some noticeable execution time to execute on my box. I ran the query in two separate windows – one with the default query options, i.e. – “Discard results after query executes” switch was unchecked, whereas in the other connection, the switch was checked.

The result? A clear performance gain!

--Clear all buffers and plan cache
--WARNING: USE WITH CAUTION. THIS CODE IS PROVIDED AS-IS WITHOUT WARRANTY
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

SELECT msc.name + CONVERT(VARCHAR(10),ROUND(RAND()*1000,0))
FROM msdb.sys.objects mso (NOLOCK)
CROSS JOIN msdb.sys.columns msc (NOLOCK)

Results of query #1 (“Discard results after query executes” unchecked)

Results of query #2 (“Discard results after query executes” checked)

How long has this been around?

The final question that came in my mind was – how long has this option been around? I fired off my SQL 7 instance, and found that I was the one behind the times!

Conclusion

The only use I can think of for this configuration setting is to execute queries to study the actual execution plan – without returning any results. Are you aware of any other use? Do let me know.

Until we meet next time,

Be courteous. Drive responsibly.

Appendix A – How to customize the query options for a given query window?

Very briefly, I will show you how to customize the query options for any given query window in the SSMS:

In any window where a customized query option needs to be used, right-click in the query window to open the pop-up menu. Click on the “Query options” menu option.

This will open up the Query options window. Any configuration changes made this way remain in effect on the particular query window (all other windows follow the default settings) as long as the window is open.