A great day at SQL In The City by Redgate

Friday I had a great day at Redgate’s SQL In The City presentation by Steve Jones (@way0utwest). There were three sessions presented which are summarized below.

Session 1 – Team-based Database Development with Version Control

I thought that this session was going to mostly review for me since I am already familiar with the Redgate SQL Source control product, and use it most every day, however there were a few things that I learned that will help me going forward. One of the key points that I had not considered before with SQL Source Control is:

Adding lookup data to source control. After the session and after thinking about it a bit, this now seems obvious, but it is something I had not considered before.  I have always though of source control as a way of storing all the schema, stored procedures, functions, and other types of code that are in the database, but now I am going to look at adding lookup tables to the database.  Consider tables like a zip code table, a states table, a country table, or other tables that don’t change very often, but are used by your database application all the time. In the past when changing this type of tables, I have written a migration script to manually add the new rows, for instance an insert statement to add a new zip code.  But instead if I was to just insert the new zip code to the table that has source control in place for the data, I could then have that data stored in a consistent state that I could always get back to if it is needed. What I love about this is that I already have all the software licenses to do this, I just need to do it.

Session 2 – Automated Build and Test

This was my favorite session of the afternoon, with a task on my to do list to build a Continuous Integration (CI) server, Steve was able to provide all the solutions needed to accomplish my task.

tSQLt for Test Driven Development

Having recently started with TDD in the SQL Server environment I have recently created my own test harness, however this is not something that I want to be responsible for maintaining over time.  tSQLt is a database unit testing framework that allows you to do TDD with SQL Server, as well as allowing you to do continuous testing in the CI environment.

Once tSQLt has been installed, when your run the tests the first time you get results showing that 10 tests succeeded and 1 failed.

tSQLtInitialFailingTests

Next the tSQLt walks you through how to find the code causing the failure and shows you how to fix it. Once you have fixed the code that is failing, You then run the tests again and get a passing score as shown here:

tSQLtPassingTests

I can’t wait to replace the simple TDD system that I built. Redgate offers an integration tool so that all of the test cases can be shown in SSMS. I haven’t had a chance to try that part out yet.

Team City Continuous Integration

There is a free download of Team City for small environments.  This should be enough for me to try it out and determine if it is doing to do what I need. I will post a follow up article after I give Team City a try.

Session 3 – Best Practices for Database Deployment

The main focus of this session was on Continuous Deployment. It was a good overall course, but not something that I am ready for yet. I need to get the Continuous Integration working first.

A couple quotes from Steve Jones on Continuous development were:

You only have two known states, Production and Source Control.

and

ABCD stands for Always Be Continuously Deploying.

These may not be exact quotes, but it is as close as I could get while taking notes.

The concept of two known states, Production and Source Control is interesting.  Everything else can be moving or changing without everyone involved knowing about it.

Summary:

This was a great session, and I would like to thank Redgate and Steve Jones for providing me the information that I need to move forward to build a Continuous Integration system. If you have a chance to visit a SQL In The City session by Redgate, it is well worth time time.

Links:

SSMS Results In A Separate Tab

At the end of my SQL Saturday presentation today on Advanced Common Table Expressions, which is about the 12th time I have presented on CTEs, I was asked a question that I have been asked many times after I present.  I am blogging about this because I have been asked about it so many times. This one one of those hidden gems in SSMS that just makes working in SSMS that much easier.

The question was “I have my results and messages showing up below my T-SQL editor in SQL Server Management studio, how do you get it to show up as 3 tabs, one for the editor, one for results and oen for messages.

The default configuration of SSMS your results and messages windows are shown below the query window as shown below:

ResultsBelowQuery

This configuration is just fine when you are running at a higher resolution, but when you are running at a lower resolution, like most projectors require, it is easier to see what is going on when the Results and Mesages are displayed on separate tabs at the top level as shown in the next two screen shots.

CodeTab

ResultsTab

Based on the default configuration, it is not exactly obvious how to do this.

Here is how you do it.  From the Tools menu in SSMS choose Options. The following dialog will be shown.  Expand the Query Results setting, then SQL Server, then click on the Results to Grid (or Results to Text) depending on your preference.  There are two checkboxes that you should check to get the desired behavior.  These are highlighed in the image below.  “Display results in a separate tab”, and “Switch to results tab after the query executes”.  Click both of these then click the OK button.  Depending your your version of SSMS you may need to restart it for the changes to start working.

OptionsDialog

From this point forward SSMS will default to showing the results in a separate tab.  For SQL Saturday or other presentations this is a great way to share more of the result set with the audience.

Common Table Expressions – Terminating the Previous Statement

Day 4 of Common Table Expression Month (June) at SteveStedman.com, today I will cover the topic of terminating the previous T-SQL statement.

These queries will be using that database that was set up in a previous posting on the CTE_DEMO Sample Database, if you haven’t set up the sample database, download it and set it up now.

To start with having come from a background years ago of writing C programs, the concept of terminating statements is natural to me. I always feel a bit uncomfortable of a SQL Statement isn’t properly terminated with a semicolon. If I am doing a T-SQL code review for someone else, I usually will make at least one comment about terminating T-SQL statements, unless it has already been done. I know that is not a requirement to terminate every SQL Statement, but it does make it easier to read. However in most cases, other than readability the termination of the previous statement really doesn’t matter. . . In most cases.

For instance, the following two sets of queries work exactly the same with our without the semicolons.

Batch 1:


SELECT * FROM Departments
SELECT * FROM (SELECT id, department, parent FROM Departments) as Dept

Batch 2:


SELECT * FROM Departments;
SELECT * FROM (SELECT id, department, parent FROM Departments) as Dept;

In most cases, when writing multiple T-SQL statements in a batch, the semicolon to terminate the lines really doesn’t matter, but there are a few cases in SQL Server where it does matter, and common table expressions are one of them.

Improper Termination

To start with lets take a look at the sample code from our previous posting titled “Writing Your First Common Table Expression with SQL Server“. If we tried to run this SQL without any semicolons, it would run fine if it was run one line at a time, but if we run it as a batch, it throws an error.

USE cte_demo
GO

SELECT * FROM Departments

WITH departmentsCTE(id, department, parent) AS
(
 SELECT id, department, parent
 FROM Departments
)
SELECT *
 FROM departmentsCTE

When run produces the following results:

CTE_Termination

Msg 336, Level 15, State 1, Line 5
Incorrect syntax near 'departmentsCTE'. If this is intended to be a 
common table expression, you need to explicitly terminate the previous 
statement with a semi-colon.

Which is a descriptive enough message stating that we need to explicitly terminate the previous statement with a semi-colon.

Proper Termination

When there is another T-SQL Statement in a batch prior to a CTE, that statement must be terminated with a semi-colon like this:

USE cte_demo
GO

SELECT * FROM Departments;

WITH departmentsCTE(id, department, parent) AS
(
 SELECT id, department, parent
 FROM Departments
)
SELECT *
 FROM departmentsCTE

Note, the only required semi-colon is the one at the end of the SELECT * FROM Departments; query, and we get the desired results.
CTE_Termination2

GO Keyword in SSMS

When working in SQL Server Management Studio, an alternative is to terminate the batch, and the GO keyword is used to break a number of T-SQL statements into different batches, like this:


USE cte_demo;
GO

SELECT * FROM Departments

GO

WITH departmentsCTE(id, department, parent) AS
(
 SELECT id, department, parent
 FROM Departments
)
SELECT *
 FROM departmentsCTE

GO

CTE_Termination3

Clean Code

Being an advocate for clean code, and clearing specifying your intentions, the way that I would write is with a semi-colon at the end of every T-SQL statement, and an extra semi-colon at the beginning of any CTE just to be safe.


USE cte_demo;
GO

SELECT * FROM Departments;

;WITH departmentsCTE(id, department, parent) AS
(
 SELECT id, department, parent
 FROM Departments
)
SELECT *
 FROM departmentsCTE;

CTE_Termination4

How you decide to do it is up to your own coding standards, and as long as it is clearly defined and supports clean code it is just fine.

Related Links:

Common Table Expressions Book

If you enjoyed this posting, and want to learn more about common table expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions – Joes 2 Pros® – A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.

Sample Database for Common Table Expressions

As I have presented my Common Table Expressions presentation many times, and as part of writing the Common Table Expressions Book I have created some sample database scripts to use.

Once you load up the script into SSMS and refresh the databases tree item, you will see a new database called cte_demo that includes 2 tables.

CTE_DEMO_SCRIPT

dbo.Departments

The first table is called dbo.Departments and it contains 17 rows with a number of store departments with the top level departments being indicated by a parent of NULL, and subdepartments having actual parent ID’s.

CTE_DEMO_dbo_departments

dbo.Royalty

The dbo.Royalty table contains several generations of the British Royal Family family tree. We will use these for some interesting hierarchical queries.

CTE_DEMO_dbo_royalty

Stored Procedures

This database also includes 2 stored procedures to rebuild the Departments table as we will be changing data in that table as we test different scripts.

Downloadable Scripts

Download one of the following scripts and run it in SQL Server Management Studio to set up the CTE_DEMO database for use in several samples.

This script is intended to be run on a test or development server and is not intended to be run on a production system.

 

Common Table Expressions Book

If you enjoyed this posting, and want to learn more about common table expressions, please take a look at my book on CTE’s at Amazon.com

DBCC FreeProcCache

Being day 12 of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC FREEPROCCACHE.

Description:

DBCC FREEPROCCACHE is used for purge all of the parsed query plans out of memory. This is commonly used in development environments, but not as common in a production environment.

Use in a development environment is common, for instance when you are working on performance tuning, or parameterization of queries. You can clear the procedure cache with DBCC FreeProcCache, run the program or web page that may be using the database, then see what is in the procedure cache. This can be useful in finding queries that may need to be parameterized. Another way to use would be to find out what queries are being run by some program. To do this you would start by working with a database that is not being used by others, clearing the procedure cache with DBCC FreeProcCache, then run the program you are trying to figure out, then look at what is in the cache, again this is something that could be done in a development or test environment, but I wouldn’t recommend doing it in production.

Use in a production environment should be rare, this is one of the common things to try when SQL Server is having difficulty. If you are are the point that SQL Server is extremely slow to respond and you have been unable to find the cause, one thing to try is to free the procedure cache with DBCC FreeProcCache and see if that fixes the problem.

DBCC FreeProcCache Syntax:

dbcc freeproccache
[ ( @HANDLE | 'POOL NAME' ) ]
[ WITH NO_INFOMSGS ]

Example:

The following example is from a development envioronment using the AdventureWorks2012 Database.

First we connect to AdventureWorks2012 and see what is in the cache.

USE AdventureWorks2012;
GO

SELECT size_in_bytes, text
 FROM sys.dm_exec_cached_plans
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st;

DBCC_FreeProcCache1
Here we see that there is plenty in the cache. Next we cleare the cache with DBCC FreeProcCache and take another look at what is in the cache.

DBCC FREEPROCCACHE;

SELECT size_in_bytes, text
 FROM sys.dm_exec_cached_plans
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st;

After running DBCC FreeProcCache you can see that there is nothing left in the cache.
DBCC_FreeProcCache2When the very next query is run, it will need to be reparsed rather than using an already parsed query in the cache.  This will take a bit longer than if there was already a parsed plan to run.  Lets run 3 queries, then take a look at the cache.

GO
SELECT FirstName, MiddleName, LastName
 FROM person.Person p
 WHERE FirstName like 'John';

GO
SELECT FirstName, MiddleName, LastName
 FROM person.Person p
 WHERE FirstName like 'Mary';
GO
SELECT FirstName, MiddleName, LastName
 FROM person.Person p
 WHERE FirstName like 'Bill';

Notice the GO Statement between each query. This tells SSMS to run each query as a separate batch. Without the GO statement the 3 queries would have been parsed as a single batch.
DBCC_FreeProcCache3
Here we see the results from the three queries. The first two returned results, and the third one had no rows in the result set. Now lets take a look at the cache

SELECT size_in_bytes, text
 FROM sys.dm_exec_cached_plans
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st;

Below we see a total of 20 items in the cache now. the top item highlighted in blue is the query we used to see what was in the cache, the second block highlighted in red contains the 3 queries from above, and the third rest of them are queries being run by SQL or other supporting queries.  For instance line 13 is the dm_exec_sql_text which is called from the query above that checks the plan.DBCC_FreeProcCache4

If we wanted to filter this down to just the queries we had written you could do it by adding a WHERE text LIKE … clause to the query as shown here.

SELECT size_in_bytes, text
 FROM sys.dm_exec_cached_plans
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'SELECT FirstName%';

Here we see that only the three queries show up, and that each of those three takes up about 40K of memory on SQL Server. DBCC_FreeProcCache5So why are there three copies of the same SELECT statement, this seems a bit wasteful. Indeed it does, for more information see an earlier post called How much Procedure Cache memory does one SQL Statement take up? There are ways to correct this.

Using DBCC FreeProcCache For A Specific Plan Handle

If you wanted to clear just a single plan handle, and not all the plan handles, you could use the optional parameter called @handle.

To get the plan handle, we start by modifying our earlier query to show us what is in the plan cache. You could leave out the WHERE clause on your own system, but I have it here to show us just the three queries in question from above.


SELECT size_in_bytes, text, plan_handle
 FROM sys.dm_exec_cached_plans
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'SELECT FirstName%';

DBCC_FreeProcCache7

Here we see the same three query plans from earlier, with an addition column called plan_handle. To free a single plan handle, we would just copy the numeric plan handle, and add that into the DBCC FreeProcCache query.


DBCC FREEPROCCACHE(0x060007000100FF3310B8DA7D0600000001000000000000000000000000000000000000000000000000000000);

SELECT size_in_bytes, text, plan_handle
 FROM sys.dm_exec_cached_plans
 CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'SELECT FirstName%';

DBCC_FreeProcCache8

Where we only see 2 of the three original queries in the plan cache.

How big is my Procedure Cache?

You can run the following query to check the size of your procedure cache.


SELECT count(*) AS NumPlans, objtype as Type,
SUM(size_in_bytes)/(1024.0*1024.0) AS size_in_mb
FROM sys.dm_exec_cached_plans
GROUP BY objtype;

Which produces the following results on my test server.

DBCC_FreeProcCache9

Database Health Reports and the Plan Cache

You can also view the plan cache using the Database Health Reports application as shown here.

DBCC_FreeProcCache10

Notes:

For more information see TSQL Wiki DBCC freeproccache.

DBCC Command month at SteveStedman.com is almost as much fun as eating jello.

Installing Sample Databases for SQL Server 2008R2

As I work on testing the Database Health Reports project, I have configured several test servers running as virtual machines using Hyper-V. Part of setting these up for testing, I usually end up installing the sample databases for SQL Server. The sample databases give me something to test against, and what usually ends up happening is I end up polluting the sample database over time with lots of test tables, bad indexes, and all the stuff that you should keep in a test database, and never actually put in a production system. Occasionally I need to wipe the server clean and start over, so when I do this I usually end up re-installing the sample databases. I do this just often enough to forget exactly where to get the sample databases from. To solve that problem I have created this blog posting so that I have somewhere to look next time I need the sample databases.

Step 1. Download the sample files from Codeplex.

There are a couple different versions of sample files available. I usually use the AdventureWorks 2008 OLTP Script. The OLTP (OnLine Transaction Processing) sample doesn’t include the data warehouse samples, and it is just quick and easy to use.
Download from here:
http://msftdbprodsamples.codeplex.com/releases/view/89502
AdventureWorks is the name of the sample database, which replaces the old Pubs database from earlier versions of SQL Server, and the NorthWind sample database from SQL Server 2005.
Download the a zip file containing the scripts needed to install AdventureWorks. The filename should be AdventureWorks 2008 OLTP Script.zip.

Step 2: Run the scripts.

To run the scripts either unzip the file and save the contents somewhere.
AdventureWorks1

Then browse into the AdventureWorks 2008 OLTP Script directory.

AdventureWorks2

Open the instawdb.sql script with SQL Server Management Studio.

AdventureWorks3

You will want to enable Full Text Search before running the script.

SQLCMD mode will need to be enabled to run the script. If the script is run without SQL Command mode, the following errors will be generated. To Enable SQLCMD mode, choose the SQLCMD Mode option from the query menu in SSMS.
AdventureWorks4
If the script is run without setting up the directories correctly the following error will be displayed.

AdventureWorks5
A fatal scripting error occurred.
Variable SqlSamplesSourceDataPath is not defined.

To fix the error, just set up the directories edit the 2 lines at about lines 36 and 37, first uncomment them, then change the directories to match the directory of your SQL Server, and the directory that the samples were downloaded to as shown in the following directory.

AdventureWorks7 Once the directories have been edited, then just run the script. The script may run for a few minutes depending on your server speed. If the script runs successfully it will generate the following output.

AdventureWorks8

Then refresh the database tree in the object explorer and the AdventureWorks2008 database will show up. Go ahead and browse the tables and take a look around.

AdventureWorks9

Not exactly a straightforward process, but if you follow the steps you will get The AdventureWorks 2008 database running on SQL Server 2008R2.
Enjoy