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.

SSMS continues to fascinate me with it’s immense wealth of features geared towards developer & administrator usability. Some time back, I wrote about how to use the Properties window during performance tuning and getting missing index information in SQL Server 2005.

Today, I will write about another small usability feature of the Properties window. Often, we multi-task and forget the exact set of SET options that a particular connection uses. The most frequently used SET options that one is worried about are:

• ANSI_NULLS
• QUOTED_IDENTIFIER
• ARTIHABORT
• NUMERIC_ROUNDABORT
• ANSI_WARNINGS

The properties window provides us a way to check the various SET options used for a particular connection. Here’s a step-by-step guide:

• Let’s run the following query in SQL Server Management Studio. Include the “Actual Execution Plan” by pressing Ctrl+M before running the query.

USE AdventureWorks2008R2
GO

SELECT e.LoginID, e.JobTitle, e.HireDate, e.CurrentFlag
FROM HumanResources.Employee e

• Switch to the Actual Execution plan in the Results pane
• Hit F4 to invoke the Properties Window (you may also use View->Properties Window menu option)
• Select the top-most SELECT operator
• Observe the Properties Window

As a cross-check, change the SET options on the query, or use the features of SSMS to change the SET options for just this connection.

USE AdventureWorks2008R2
GO

SET ANSI_NULLS OFF
GO

SELECT e.LoginID, e.JobTitle, e.HireDate, e.CurrentFlag
FROM HumanResources.Employee e

SET ANSI_NULLS ON
GO

Please note that this feature is available in both – SQL 2008 and SQL 2005.

Until we meet next time,

Be courteous. Drive responsibly.

SQL Server Performance tuning is an interesting exercise, and I for one, can enjoy tuning performance for hours at an end. I believe that if a system is facing a performance issue, the issue is almost never the SQL Server. The order in which I attack a performance issue is:

1. Look at the application code – this is always the bottle neck (by code, I mean both – T-SQL and the VC++/.NET, etc code)
2. Look at the underlying hardware configuration, more specifically I/O (we are not talking about capacities, this is the configuration)
3. Look at the underlying hardware capacities
4. Finally look at the platform software (Windows OS and SQL Server)

Indexes play a vital role in performance tuning – just as the key to a good reference book is the quality and usability of it’s Index, similarly, the key to a highly performing database is the high selectivity and usability of it’s index.

Please NOTE: Indexes are not the “fix-all” solution. Indexes can have a negative impact on performance, hence, please do the exercise due diligence before deploying an index. If you are in constant need of more indexes, it indicates a problem with your queries and overall database design. Index design is a science of it’s own, which is out of scope of today’s post.

A lot of people have come to me and said how to do we know if SQL Server needs an index. More often than not, the chain of thought begins from the fact that a given query is running slower than expected. SQL Server Management Studio does provide the option of suggesting missing indexes to the user. While most of the content of this post is SQL 2008 specific, I have highlighted how SSMS of SQL Server 2005 provides the Missing Index indication towards the end of the post.

If the SQL optimizer feels that an index would help it generate a better execution plan for any given query, SQL Server Management Studio provides us with an indication of it’s requirement when viewing the graphical execution plan.

If we run a query in SQL Server Management Studio with the graphical plan enabled, the missing index hint would show up in green-text as high-lighted in the screen-shot below.

USE AdventureWorks2008R2
GO

--Number of sales done in April, 2008
SELECT COUNT(*)
FROM Sales.SalesOrderHeader sh
WHERE MONTH(sh.OrderDate) = 4 AND YEAR(sh.OrderDate) = 2008
GO

SELECT COUNT(*)
FROM Sales.SalesOrderHeader sh
WHERE sh.OrderDate >= '2008-04-01' AND sh.OrderDate <= '2008-04-30'

From the screen-shot we see that the optimizer determines that the absence of the index has a 96.11% impact on the overall performance of the query. Since this is a huge impact, let’s right-click on the green-line and choose “Missing Index details”.

Notice that SQL Server opens up a new query editor window with the Missing Index template script ready for our use & modification.

/*
Missing Index Details from SQLQuery1.sql - VPCW2K8DENALI.AdventureWorks2008R2 (VPCW2K8Administrator (53))
The Query Processor estimates that implementing the following index could improve the query cost by 96.1176%.
*/

/*
USE [AdventureWorks2008R2]
GO
CREATE NONCLUSTERED INDEX [ < name sysname, Index, Missing of > ]
ON [Sales].[SalesOrderHeader] ([OrderDate])

GO
*/

Go to Query –> Specify Values for Template Parameters and fill in the template parameter values. Once the template values are filled in, the index may be created. The next time our batch is run, the performance impact should clearly be seen.

As you can see, the performance of the queries which used to run at 50% each has changed. The first query now takes up 92% of the processing time, whereas the 2nd query is able to utilize the indexes correctly (notice how the Index scans have turned to Index seeks) and therefore completes in 8% of the processing time.

In this case, clearly indexes suggested by Microsoft SQL Server were useful. If found that they do not impact any other area of the application, these indexes should most definitely be rolled out into production.

Missing Indexes via DMVs

DMVs (Dynamic Management Views) are very powerful, and therefore it is not surprising that the graphical interface of SQL Server Management Studio utilizes DMVs to get missing index information. If you too would like to experiment around with DMVs to identify missing indexes, please refer Books On Line at: http://msdn.microsoft.com/en-us/library/ms345417.aspx. This Books On Line page gives you:

• An introduction to the missing indexes DMVs
• Shows how to use this information to write CREATE INDEX statements
• Highlights the limitations of the Missing Index feature (Very Important to read!!!)
• Related Query tuning features

As mentioned above, it is my sincere request that you read about the limitations of the Missing Index feature before production use.

SQL 2005 Users

A lot of production systems are still running SQL Server 2005, and hence, I thought it best to highlight how Missing Index information is available in SQL Server 2005. The high-level steps to know about missing indexes in SQL Server 2005 is:

• I ran the same set of T-SQL code against a SQL Server 2005 instance in a SQL Server 2005 SSMS
• Once the execution plan is displayed, I clicked on the top-level SELECT statement in the plan
• Bring up the Properties window by pressing F4 (read more on the properties window here)
• Notice the section on Missing Indexes

Until we meet next time,

Be courteous. Drive responsibly.