call us toll-free +1 855 855 3600
 
DB Best Chronicles
 
Talks on Managing Data and Applications Anywhere

Free Summer Workshops!

Posted by | On August 17th, 2017 | In Kalen's Continuum, SQL Server, Training
 

Over the next month, I’ll be presenting a free workshop in six cities in the U.S. The workshop will include hands-on labs that allow you to explore new features of the relational engine for both SQL Server 2016 and SQL Server 2017.  For more details, and to register, check the links below.

Remember, the workshops are FREE and include both breakfast and lunch!

Summer Workshop

Read the rest of this entry »

A Visit to the ‘Other’ Washington

Posted by | On August 15th, 2017 | In indexes, Kalen's Continuum, Query Tuning, SQL Server
 

Last week I had a terrific opportunity to give a few SQL Server presentations in the Washington, DC area. I stayed in Reston, Virginia, and spoke at the Microsoft office in Chevy Chase, Maryland. On Tuesday evening I was able to take the metro into DC itself and spend several beautiful hours walking around the National Mall.

Read the rest of this entry »

Looking Inside Your Database Pages

Posted by | On August 7th, 2017 | In indexes, Kalen's Continuum, SQL Server
 

As I promised a couple of weeks ago, this post will show you how to look inside a page to see what is actually stored there. I’ll just be looking at data pages, and only a very basic example, just so you get the idea. But once you get familiar with looking inside pages, there are many mysteries about SQL Server data storage that you can solve on your own. I’ll mention a few of them at the end of this post.  In addition, looking at index pages can also be quite educational, but I’ll have to save that for another time. And remember that if the table has a clustered index, the data pages are also technically index pages, but as far as the tools I’m going to show you work, a page at the leaf level of a clustered index is a data page, and not an index page.

The tool we’re going to use is the undocumented DBCC PAGE command. There is no DMV yet for this DBCC command, like DBCC IND became sys.dm_db_database_page_allocations and DBCC SHOWCONTIG became sys.dm_db_index_physical_stats. Even though it’s officially undocumented, an online search can help you find other articles and blog posts that describe DBCC PAGE in detail; additionally, I provide lots of details in my SQL Server Internals books.

I’m going to use a very small table from the AdventureWorks database for the basic exploration of the contents. (Either AdventureWorks2104 or AdventureWorks2016 should work.)  We’ll also take a quick look at a bigger table so you can see the Next and Previous page values.

I’m going to create a copy of Sales.Territory as an empty table. Usually just doing a SELECT INTO with a TOP 0 can copy the empty table, but I want to remove the IDENTITY property from the TerritoryID column. To do that, I use a little trick. If a SELECT INTO is based on a UNION, certain column properties, including IDENTITY, will not be inherited, so my code to create an empty table with the same column definitions, except for IDENTITY, is shown below. I then build a clustered index on the table and copy the data from the original table.

USE AdventureWorks2014;
GO
DROP TABLE IF EXISTS Territories;
GO
SELECT TOP 0 * INTO Territories FROM Sales.SalesTerritory
UNION
SELECT TOP 0 * FROM Sales.SalesTerritory;
GO
CREATE CLUSTERED INDEX TerritoryNameIndex on Territories(Name);
GO
INSERT INTO dbo.Territories
SELECT * FROM sales.SalesTerritory;
GO

So why didn’t I just copy the data as I created the table, and left off the first TOP 0?  I wanted to create the clustered index on the table before I populated it, so that I could show you that even with the clustered index, the data is not stored in physical order on the page.

Read the rest of this entry »

Virtualization and consolidation of SQL Servers during a complex database system upgrade

Posted by | On July 31st, 2017 | In Customer Story, Database Upgrades, SQL Server | Tags: , , , , , , ,
 

One of the largest US national energy suppliers wanted to upgrade their SQL Server system and deploy it on VMware. As a consulting partner, we offered a new architecture for their virtualized system. In addition, we found some server consolidation opportunities which would improve the overall system’s performance.

As a result, we helped our customers deploy the newly created system in production. Moreover, we delivered a comprehensive list of best practices for evident and accessible management of their environment.

Learn more about the technical details of this project from the following video:

Read the rest of this entry »

SQL Server 2017 Workshops and the SQL Pass Summit

Posted by | On July 28th, 2017 | In Kalen's Continuum, Query Tuning, SQL Server, Training
 

I’ve been heads-down updating Microsoft’s Hands-on Labs, originally written for SQL Server 2016 CTP3, into something that showcases not only the SP1 release of SQL Server 2016, but also some of the new SQL Server 2017 features. One of the most exciting new features is Adaptive Query Processing, not to be confused with another awesome new feature called Automatic Query Tuning!

AQP

So I will not be able to continue my blog series on Clustered Indexes this week. (Don’t worry, it’s definitely still near the top of my blogging queue!) Instead, I’ll mention that these hands-on labs will be made available in a series of FREE workshops I’ll be giving around the country over the next couple of months. It looks like I’ll be presenting the workshop in three cities in Texas and three in the Midwest And then there will be more next year!

Great news on the conference front. After some extensive traveling from August through October, I get to go to the PASS Summit in the Emerald City. I’ll be a presenter at this 2017 Summit, held November 1 – 3, in Seattle, Washington.

If you would really like to attend the Summit, too, DB Best Technologies has something for you! As an exhibitor, we can give you a special discount code which gives you $100 off your conference admission fee. Just use the code EXHIBITODR when registering. And don’t forget to stop by the DB Best booth and say hello!

PASS_17_Speaking_600x315-A

Database Compare Suite update 4.10 brings Sybase IQ support

Posted by | On July 26th, 2017 | In Database Migration, Database Upgrades | Tags: , , , , , , , , , , ,
 

We are happy to announce the latest release of DB Best’s in-house tool Database Compare Suite. The new version marked 4.10 brings support for yet another data warehouse platform — Sybase IQ. Watch Database Compare Suite in action in the following video and learn more about other improvements of the latest update from our blog post.

Read the rest of this entry »

Finding My Pages

Posted by | On July 18th, 2017 | In indexes, Kalen's Continuum, SQL Server
 

In a post from two weeks ago, I told you that this week I would show you how to look inside pages to see the NEXTPAGE and PREVPAGE pointers. When I started to write this, I realized that before I can show you how to look inside the pages, I need to show you how to find out what pages to look at. So I’m going to juggle this blog schedule a bit, and this week I’ll show you how to find out what pages belong to a table and next time we’ll actually look inside the pages. However, the methods I’m going to show you to find page numbers will also provide the NEXTPAGE and the PREVPAGE values, so you don’t actually have to look inside the pages to get that information.

Starting in SQL Server 2014, Microsoft included the dynamic management object sys.dm_db_database_page_allocations. This function is technically undocumented but other people have blogged about it elsewhere, and it is a very useful function to be aware of.  The function has five parameters: database_id, object_id, index_id, partition_number, and mode.  For most of the parameters, a NULL means ALL. So a NULL for object_id would mean you want all objects. Once an ALL is specified, the rest of the parameters, except mode, are ignored. So if you specify NULL for object_id, the value for index_id and partition_number are basically ignored.

I’m not going to go into all the details, but I’ll show a few examples of the use of this function.

Read the rest of this entry »

Increasing database performance by migrating from Ingres to SQL Server

Posted by | On July 18th, 2017 | In Customer Story, Database Migration, SQL Server | Tags: , , , ,
 

One of our customers needed to migrate their Ingres Database system to Microsoft SQL Server wanting to improve the performance of their system. We came up with a migration solution and updated the business logic module of their application. As a result, the customer achieved a sufficient performance increase, improved the database system security, and got an optimized business logic architecture.

The video below depicts the migration process in details:

Read the rest of this entry »

Architecting highly available and scalable system when migrating to SQL Server on AWS

Posted by | On July 12th, 2017 | In AWS, Customer Story, Database Migration, SQL Server | Tags: , , , , , ,
 

Our customer needed to reduce the licensing costs of their Oracle database. We came up with a comprehensive solution by migrating their database to SQL Server platform. We also remediated all affected application components. As a result, our customer benefited from obtaining higher value SQL Server platform with enhanced scalability and high availability approaches.

Read the rest of this entry »

What Makes an Index Clustered?

Posted by | On July 10th, 2017 | In indexes, Kalen's Continuum, SQL Server
 

In my previous post, I told you about what it meant for an index in SQL Server to be ‘clustered’. Usually, when we talk about SQL Server indexes, we are talking about indexes organized as B-trees (or technically, B+-trees, but we won’t go into the difference here.) For these indexes there are two basic properties that set clustered indexes apart from nonclustered.

First, the index leaf level IS the data. It is not a copy of the data or pointers to the data. When you create a clustered index, your data is reorganized and the rows and pages become the leaf level of the index.

Second, the data is sorted. As I discussed last time, this doesn’t mean it is necessarily physically sorted in consecutive slots on the pages, in contiguous pages in the file. But by following the page pointers from the first page to the last page, SQL Server can retrieve the data in order without having to perform a sort operation.

Here’s a graphic from the SQL Server documentation showing you the general structure of clustered B-tree indexes.
btree

But not all clustered indexes have these properties. Let me tell you a bit about columnstore indexes. I won’t be able to tell you EVERYTHING about columnstore indexes, but I point you to another awesome resource for that. I’ll just tell you the basics.
SQL Server introduced a completely different way of organizing your data in SQL Server 2012 with nonclustered columnstore indexes. The name comes from the fact that the data is organized by column, not by row, as our ‘regular’ (B-tree) indexes are. Now that we have columnstore indexes, our original B-tree indexes are sometimes referred to as rowstore indexes.

Read the rest of this entry »