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.

Rarely, one needs to change the SQL Server configuration. However, when such a change is required, it needs to be planned and scheduled for execution during an outage. There too, the team implementing the change may or may not be the same as those recommending the change. In such cases, it becomes essential to script the changes made so that the change can be supplied to the implementation team for:

1. Record keeping – for future reference
2. Scheduled deployment – the required change has been scripted, so it can be applied whenever the outage window permits

Generally, configuration changes to the SQL Server are made via the use of the system stored procedure – sp_configure. However that is not true for all situations. For example, if a SQL Server needs to be configured to use Windows authentication only, this setting cannot be made via sp_configure, making it difficult to script. Today, I will show you one such method available within SSMS that allows you to script such “difficult” configuration changes.

NOTE: The methods described in this post are provided “as-is” and without warranty. Please do not use these in production without prior thorough testing. Incorrect use of these methods may result in serious, system-wide, irreversible configuration issues with your server. This site (BeyondRelational.com), the author or Microsoft and it’s affiliates are not responsible for damage resulting due to misuse of these methods. Please use these methods at your own risk.

1. Login to your SQL Server instance using SSMS
2. From the object explorer, right-click on the SQL server instance name to bring up the instance properties window
3. Go to the “Security” tab
4. The server I am connecting to had mixed-mode authentication on. I now want to change the authentication mode to “Windows authentication mode”. Do NOT apply the change
5. 
6. Click on the little arrow near the “script” button at the top
7. 
8. Choose “Script Action to New Query Window” or press Ctrl+Shift+N
9. Notice that a new query window opens up with the underlying change scripted and ready for execution
10. 

11. USE [master]
    GO
    EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer', N'LoginMode', REG_DWORD, 1
    GO

12. The change can now be executed during the regular maintenance window for my instance

Until we meet next time,

Be courteous. Drive responsibly.

I have maintained that SSMS has a lot of underappreciated features, and that most of the community is not utilizing SSMS to it’s maximum potential. Today, I will show you how a simple configuration change helped in speeding up one of the most common tasks – generating a csv from data stored in a table.

I had a requirement to extract some data out of a Microsoft SQL Server database, save it as a CSV (comma-separated value file) and then send it across to another team for further analysis. The logical order in which I addressed the task was:

1. Write the query
2. Export to a CSV
3. Send the CSV to the team in need

Write the query

Writing the query was simple, and for this example, let’s assume I came up with the following query:

USE AdventureWorks2008R2
GO
SELECT     Sales.SalesOrderHeader.SalesOrderID, Sales.SalesPerson.TerritoryID, HumanResources.Employee.BusinessEntityID, Sales.SalesOrderHeader.OrderDate, 
                      Sales.SalesOrderHeader.DueDate, Sales.SalesOrderHeader.Status, Sales.SalesOrderHeader.ShipDate, Sales.SalesOrderHeader.OnlineOrderFlag, 
                      Sales.SalesOrderHeader.PurchaseOrderNumber, Sales.SalesOrderHeader.SalesOrderNumber, Sales.SalesOrderHeader.CustomerID, 
                      Sales.SalesOrderHeader.AccountNumber
FROM         HumanResources.Employee INNER JOIN
                      Sales.SalesPerson ON HumanResources.Employee.BusinessEntityID = Sales.SalesPerson.BusinessEntityID INNER JOIN
                      Sales.SalesOrderHeader ON Sales.SalesPerson.BusinessEntityID = Sales.SalesOrderHeader.SalesPersonID
WHERE     (Sales.SalesOrderHeader.OnlineOrderFlag = 0)
ORDER BY Sales.SalesOrderHeader.OrderDate, Sales.SalesOrderHeader.DueDate, Sales.SalesPerson.TerritoryID DESC

Export to a CSV

When it comes to exporting to a CSV file, I have multiple options

1. Use BCP (https://nakulvachhrajani.com/2011/05/02/bcp-amp-bulk-inserts-underappreciated-features-of-microsoft-sql-server/)
2. Edit the query to include comma-separators and then copy-paste to notepad
3. Use SSMS to generate the comma-separated output and directly copy it over to notepad

I will not be considering the point about using BCP because it has already been addressed by my previous post, and second because the server I was originally running on was production instance and bcp was not an option available to us.

Option #2 – Edit the query to include comma-separators and then copy-paste to notepad

We can modify the query to something similar (please note, there can be multiple, much better implementations. The script below is for demonstration purposes only).

USE AdventureWorks2008R2
GO
SELECT     Sales.SalesOrderHeader.SalesOrderID, ',',
           Sales.SalesPerson.TerritoryID, ',',
           HumanResources.Employee.BusinessEntityID, ',',
           Sales.SalesOrderHeader.OrderDate, ',',
           Sales.SalesOrderHeader.DueDate, ',',
           Sales.SalesOrderHeader.Status, ',',
           Sales.SalesOrderHeader.ShipDate, ',',
           Sales.SalesOrderHeader.OnlineOrderFlag, ',',
           Sales.SalesOrderHeader.PurchaseOrderNumber, ',',
           Sales.SalesOrderHeader.SalesOrderNumber, ',',
           Sales.SalesOrderHeader.CustomerID, ',',
           Sales.SalesOrderHeader.AccountNumber, ','
FROM         HumanResources.Employee INNER JOIN
                      Sales.SalesPerson ON HumanResources.Employee.BusinessEntityID = Sales.SalesPerson.BusinessEntityID INNER JOIN
                      Sales.SalesOrderHeader ON Sales.SalesPerson.BusinessEntityID = Sales.SalesOrderHeader.SalesPersonID
WHERE     (Sales.SalesOrderHeader.OnlineOrderFlag = 0)
ORDER BY Sales.SalesOrderHeader.OrderDate, Sales.SalesOrderHeader.DueDate, Sales.SalesPerson.TerritoryID DESC

The problem that we run into is alignment. If the results are copy-pasted into Notepad, they would not turn out as expected, and some manual formatting will be required.

Options #3 – Use SSMS to generate the comma-separated output and directly copy it over to notepad

All this custom query editing may not be possible if this is the output of a stored procedure (changing the stored procedure may break the application). Instead, the simplest option is to run through the following:

1. Right-click in the Query editor window containing the query to be executed
2. Choose “Query Options”
3. 
4. In the Query Options window, navigate to the “Results” node and click on “Text”
5. Change the “Output format” to “comma delimited”
6. 
7. Now press “Ctrl + T” or go to Query –> Results to –> Text
8. Execute the query. Notice that the output is ready to be copy-pasted directly into Notepad as a CSV file – NO formatting operations need to be done
9. 

Isn’t this great? Have SSMS take care of all your troubles, and take the saved time to read something new on Just Learned!

Benefit:

The benefit of the method shown above is that it is specific to the particular query window. So, once the query window is closed, SSMS will start honouring the default settings under Tools –> Options.

Other cool SSMS productivity features:

I have written about other cool productivity improvements in SSMS here: Some underappreciated productivity features of SSMS. Also, you can search through my blogs for more content on SSMS & it’s utilities.

Until we meet next time,

Be courteous. Drive responsibly.