SQL SERVER – Using Bitwise And (&) Instead of a Junction Table – Notes from the Field #053

[Note from Pinal]: This is a 53rdth episode of Notes from the Field series. Everyday I get 100s of emails and most of the emails have a similar request. Everyone wants to get maximum performance, but they want to make the least amount of changes in their code. Well, though both of them are contradictory requests, it is possible in most of the cases if you know the technology inside like Linchpin People do. Here in this blog post, my close friend Stuart Ainsworth explains a cool trick, which I just learned today after so many years of experience. Wow, Stuart – thanks for this amazing note from the fields – I learned something new and there will be so many who will enjoy this post.

In this episode of the Notes from the Field series database expert Stuart Ainsworth explains Using Bitwise And (&) Instead of a Junction Table.


 

Bitwise operations in SQL Server are not often used, but like many of the tools available to SQL Server developers, bitwise operations can provide some interesting alternatives when you’re faced with specific challenges. One of my clients has a large database that relies heavily on many-to-many relationships to identify matching interests on multiple characteristics. As a simple example, let’s assume that I’m talking about fruit baskets.

In the simplest version of a fruit basket, you’d have two database objects: the basket, and the assortment of fruit.  Baskets can use different combinations of fruit, and samples of fruit may appear in more than one basket, like so:

Basket 1: Apples
Basket 2: Apples, Bananas
Basket 3: Grapes, Apples
Basket 4: Strawberries, Bananas

The traditional method of modeling this relationship would be to use a junction table, as illustrated below.

 

However, my client  had 500,000 baskets, and roughly 50 different fruits to choose from. Assuming that every basket had at least 10 different fruits, the junction table would have at least 5,000,000 rows of data. Even though the junction table was well indexed and strongly typed, my client’s design was suffering from slow read times.  The client needed an alternative. Enter the bitwise AND (&).

Setting Up a Demo

Let’s set up a demo that illustrates both the junction table method and the bitwise AND alternative.  First, you’ll create the following three tables and populate them (using table valued constructors):

  1. Baskets, which includes a column for use with the Bitwise AND
  2. FruitID, which is set up for use with the Bitwise AND
  3. FruitBaskets, which is a junction table

Note that primary and foreign key references are not included for the simplicity of the demo. You’ll also be adding an extra column to the Baskets table to use for the Bitwise join. Finally, note that the ID column of the Fruit table mirrors the decimal values of the binary bit positions (e.g., 1, 2, 4, 8, 16, 32, 64, 128).

CREATE TABLE Baskets
(
BasketID INT
, BasketName VARCHAR(100)
,
FruitBitHash BIGINT
)
CREATE TABLE Fruit
(
FruitID BIGINT
, FruitName VARCHAR(20)
)
CREATE TABLE FruitBaskets
(
BasketID INT
, FruitID BIGINT
)
GO
INSERT  INTO Fruit
( FruitID, FruitName)
VALUES  ( 1, 'Apples'),
(
2, 'Bananas'),
(
4, 'Grapes'),
(
8, 'Strawberries')
GO
INSERT  INTO dbo.Baskets
( BasketID, BasketName, FruitBitHash)
VALUES  ( 1, 'Apples', 1),
(
2, 'Apples, Bananas', 1 + 2),
(
3, 'Grapes, Apples', 1 + 4),
(
4, 'Strawberries, Bananas', 8 + 2)
GO
INSERT  INTO dbo.FruitBaskets
( BasketID, FruitID)
VALUES  ( 1, 1),
(
2, 1 ),
(
2, 2 ),
(
3, 1 ),
(
3, 4 ),
(
4, 8 ),
(
4, 2 )
GO

Now that you’ve got your tables set up, let’s run a couple of queries. First, you’ll use a junction table (the traditional, normalized model), and then you’ll use the Bitwise AND (&).  In both cases, youy’re looking for baskets that contain apples:

/*Select the fruitbaskets containing Apples using the junction table*/
SELECT BasketID, BasketName
FROM dbo.Baskets b
WHERE EXISTS (SELECT *
FROM dbo.FruitBaskets fb
JOIN dbo.Fruit f ON fb.FruitID = f.FruitID
WHERE b.BasketID = fb.BasketID
AND f.FruitName = 'Apples')
GO
/*Select the fruitbaskets containing Apples using the bithash*/
SELECT BasketID, BasketName
FROM dbo.Baskets b
WHERE EXISTS (SELECT *
FROM dbo.Fruit f
WHERE b.FruitBitHash &amp; f.FruitID <>0
AND f.FruitName = 'Apples')
GO

If you run this demo, you’ll see that you get the exact same results from the two queries. However, the first query would need to read data from 3 tables, and the second query only needs 2. If the junction table is very large, the traditional method can be significantly slower than the second method.

But how does it work? An excellent explanation can be found here, but the short answer is that when you’re using the Bitwise AND (&) to compare two different integers, any value other than 0 that is returned from that comparison means that those integers share a common base. The magic happens with this line of code:

WHERE b.FruitBitHash & f.FruitID <>0

So, why don’t we do this all the time?

There’s an old expression, “If all you have is a hammer, then everything looks like a nail.” Different tools are best suited for different problems. The limitations of using the Bitwise method to remove a junction table include:

  1. Violation of relational integrity: The surrogate IDs in the lookup table (e.g., the Fruit table) have to have a specific order and meaning. If you make a mistake when setting up the key values, you can get wrong answers.
  2. A limited number of bitwise values can be stored in a bigint: In SQL Server, a bigint is 8 bytes, which means that there are 64 bits. When using a single bithash column, you can only have one value per bit. (Note that you can work around this by using multiple columns, but that gets complicated.)

The benefit of the Bitwise AND method is reduced disk I\O because it eliminates a large junction table. In this case, you did notice increased CPU usage using the Bitwise method, but the increase in performance was significant. However, on faster hardware, a junction table would probably have worked as well and still maintained relational integrity. For now, Bitwise AND is a useful tool for a very specific type of problem.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQLAuthority News – Get My Two Books FREE at #SQLPASS

Every year when I visit SQLPASS, I do book signing where I give away my books for free. This year there are two opportunities when I am going to give away my books for free.

Though the books are in great numbers they always run away early. I strongly suggest that you arrive early and secure your copy. I will be extremely delighted to shake your hands and talk SQL.

Book Signing will be at the booth of Embarcadero and here are the timings for the same.

Book: SQL Wait Stats - Thursday, November  6th from 1 PM to 2PM.

Book Secret Tool Box of Successful Bloggers – Friday, November 7th from 11 AM to 12 PM.

Do not miss this opportunity.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Unable to Restore From Backup After Detach Operation

While doing community work I travel a lot, speak at various conferences and get a chance to meet many new faces. The best part is that I get a chance to hear a variety of issues which people face while using SQL Server. This blog post is an outcome of one such interaction with a DBA from one of the organizations I had to meet.

After the conference a young guy came to me and said – “I found a bug in SQL Server Restore”. I was amazed with his confidence and asked him to tell more before concluding. He said that to restore a database from backup, you need to have same database created before restore. I told that there is something which is not right with the test which he is performing because that doesn’t sound correct. I gave him my email address and asked to contact me to find more. I was eagerly waiting for his mail as this was on top of my mind and I was restless for two days. Finally the mail landed-

He sent an email repro steps.

  1. Create new database.
  2. Take a backup of the database.
  3. Detach the database.
  4. Restore from backup taken in step 2. This step would fail.

I followed the same steps

CREATE DATABASE SQLAuth
GO
BACKUP DATABASE SQLAuth TO DISK = 'C:\Temp\SQLAuth.bak' WITH FORMAT
GO
sp_detach_db 'SQLAuth'
GO
RESTORE DATABASE SQLAuth FROM DISK = 'C:\Temp\SQLAuth.bak'
GO

As soon as I run the last command of restore, I get below error

Msg 3142, Level 16, State 1, Line 7
File "SQLAuth" cannot be restored over the existing "E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\SQLAuth.mdf". 
Reissue the RESTORE statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE to identify an alternate location.
Msg 3142, Level 16, State 1, Line 7
File "SQLAuth_log" cannot be restored over the existing "E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\SQLAuth_log.ldf". 
Reissue the RESTORE statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE to identify an alternate location.
Msg 3119, Level 16, State 1, Line 7
Problems were identified while planning for the RESTORE statement. Previous messages provide details.

Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.

Error Message is very clear about the cause of restore failure. Since we detached the database, the mdf and ldf files are still available at the location when the database was created. It’s good that SQL is not over writing the files by itself unless we specify explicitly.

If you want to over write the files then we can use the “WITH REPLACE” clause to the command as shown below.

RESTORE DATABASE SQLAuth FROM DISK = 'C:\Temp\SQLAuth.bak' WITH REPLACE

If we don’t detach the database and perform restore on top of existing database like below

CREATE DATABASE SQLAuth
GO
BACKUP DATABASE SQLAuth TO DISK = 'C:\Temp\SQLAuth.bak' WITH FORMAT
GO
RESTORE DATABASE SQLAuth FROM DISK = 'C:\Temp\SQLAuth.bak'
GO

Then we will get a slightly different message as shown below:

Msg 3159, Level 16, State 1, Line 5
The tail of the log for the database "SQLAuth" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

Again, this is a safety mechanism where a user has to confirm their actions. Recall the situation when you have an existing file in windows and you paste same file at same location – you always get warning. SQL Server is no different and I was pleasantly relived with the fact that this was not a bug inside SQL Server. I am glad the DBA did send me this information because it made me revalidate and play around with backups with SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – PowerShell way of Restarting SQL Server Service

I learn a lot when I go for conferences all around the world. There are styles, content and cultural differences that I get a chance to learn and enjoy as part of my trips. Most of these trips I make are geared towards at least one learning experience I can get end of the day. With these travels, I also attend and speak at local user groups to get a flavour of what is the neat and coolest way to work with SQL Server. Recently at our local user group (SQL Server Bangalore UG) UG meeting, I was vividly stumped by a question from one of the speakers. Here he was talking about various ways to shut down or start SQL Server.

This is was an interesting quiz and I was pleasantly surprised by the most common methods and wanted to participate in this quiz. The answers were flowing all over and here are some that I would like to share which were part of audience interactions:

  1. Object Explorer in SSMS (SQL server Management Studio)
  2. Net Start Command.
  3. SQL Server Configuration Manager.
  4. Windows Services.
  5. Using the Sqlservr.exe executable

I thought I had known most of these and the usage of sqlservr.exe was something I wanted to check as it had been ages. For complete list of startup parameters refer book online.

Getting back to UG meet, I couldn’t hold my curiosity back and I was quick to raise my hand to give what I thought could be yet another answer. I shouted out loud the “SHUTDOWN” command. This was something I thought would fit the bill too. The speaker was understanding to say, this was one of the partially correct answer. Taken aback, I eagerly waited for the explanation.

Though the answer was correct, it was to shut down the service and didn’t allow us to restart our SQL Server service. And the speaker, waited for more answers. This made me curious of the fact that there was more to what I knew.

Welcome PowerShell

The speaker was quick to say, now that we have exhausted all the options available – let me talk about Windows PowerShell and it can be yet another way to restart SQL Server Service. It completely slipped my mind about this option. PowerShell is a powerful way to script and do activities with SQL Server. If you haven’t played around, then I strongly suggest to have a look into the same.

Now, how can I restart SQL Server Service? In this example below, I have used the PowerShell ISE IDE to execute the command as identified below:

Restart-Service -Force MSSQLSERVER

 

Since there was no feedback from the console, I was curious to know if the SQL Server service did restart properly. On examining the Task manager I could confirm that the service did restart because the PID values were completely different after the command got executed.

Sometimes, we carry of knowledge that needs a refresher. Hence taking a student approach to everything we do can help a lot in this learning experience.

Do let me know if you found any other way to restart SQL Server and I would be more than happy to learn it from you.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Authority News – Presenting Session at #SQLPASS on November 5, 2014

I will be attending SQLPASS 2014 this year in Seattle. I have attended SQLPASS 5 times before and this is my sixth times for the event. I love this event and that is why every single year I attend the event.

This time I will be presenting on session Secrets of SQL Server: Database Worst Practices as well. Here is the details for my session.

Secrets of SQL Server: Database Worst Practices

Abstract: 

“Oh my God! What did I do?” Chances are you have heard, or even uttered, this expression. This demo-oriented session will show many examples where database professionals were dumbfounded by their own mistakes, and could even bring back memories of your own early DBA days. The goal of this session is to expose the small details that can be dangerous to the production environment and SQL Server as a whole, as well as talk about worst practices and how to avoid them.

Shedding light on some of these perils and the tricks to avoid them may even save your current job. After attending this session, developers will only need 60 seconds to improve performance of their database server in their SharePoint implementation. We will have a quiz during the session to keep the conversation alive. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately after the session.

Date: November 05, 2014

Time: 10:15 AM – 11:30 AM

Room: 6B

Why should you attend my session?

When I want to attend any session, I try to reason myself about why should I attend any particular session. Well, if you are wondering the same for me, here are my reasons for you.

  • We will have lots of FUN
  • You will LEARN something while you are having FUN
  • FUN and LEARN

Remember, we are going to have lots of fun and while having fun we are going to learn. This session is set at LEVEL 100, so I will be talking about at the beginning level. With that said if you are an expert DBA/Developer and want to have fun, join me in my session. We are going to have a great time together.

Here is one of the questions which I am going to ask in the session: If you get it right, I will give you a small token of appreciation.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – What are my Trace Flags Enabled on SQL Server?

In one of my recent interactions with a DBA friend of mine, I had to dig into one of the simplest yet a capability which is available inside SQL Server. SQL Server from time to time has an uncanny requirement to enable certain Trace Flags. Trace Flags are specifically enabled by administrators to mitigate certain behavior that SQL Server has in specific situations. We need to understand that, these trace flags are special purpose and shouldn’t be used without understanding what the implications are on the server as a whole. So please handle these with care.

Now with this context set let me take a moment about the scenario which my friend was asking. DBA friend of mine was curious to know the trace flags that are enabled on his production servers. He was wanting to know how to get this information easily. I quickly wanted to ask him – “buddy, why are you curious suddenly about this?”. He said, recently he got promoted to as a Senior DBA and the DBA who was managing one of the critical servers had left recently and this server was transferred to him.

My first question to him was, “Have you checked the Server Dashboard reports?”

Just get to the Server node on Object Explorer (SSMS) -> Right Click -> Reports -> Standard Reports -> “Server Dashboard”. Once you are here, you can expand the “Non Default Configuration Options” and there are these Trace Flags that are enabled “Globally” on a given server.

This was a great start for sure. And there was a silence for a moment.

After this, he quickly bounced back to say that, the DBA had told that there were many other Trace flags that are enabled than what the server did show on this report. I was pleasantly surprised that I had given him a half-baked solution and had to revert back soon.

DBCC Tracestatus

The best and fastest way to look at all the Trace Flags is to use the DBCC TraceStatus command. There are a number of ways we can look at the statuses using this command. The simplest way to look at all the trace flags enabled is using the DBCC command directly like shown below:

Here we see there are two trace flags that are enabled, one Globally and other Session specific trace flag. As the name suggests the Global flag is applicable for the whole server node while there can be specific statuses which are applicable just for a session duration.

If you want to check the status of a Trace Flag, then we can call the tracestatus command specifically using the trace number as indicated below.

I immediately called my friend and was quick in letting him know about this command which has been with SQL Server for a while and that it had missed my mind. This is a quick way to find what are the Trace Flags used within SQL Server using T-SQL commands. If you have any other way to do these checks, please let me know.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Why my Database is in Restoring Mode After Taking Backup?

Once upon a time there was a SharePoint consultant named Pepper. She wanted to learn about SQL Server Administration/DBA work. While she was a master SharePoint consultant at her office in Bethel she grew curious to learn SQL Server. She wanted to be independent when dealing with SQL server chores like creating maintaining database, taking backups, creating jobs and things alike. Being busy as she is at her current task on working with SharePoint she would sometimes squeeze in some time to poke around on SQL server and learn about it. Our coffee discussions would turn out to be SQL discussions and how SharePoint uses SQL as its backend.

Last week, I got a call from Pepper at noon timeframe Now that does not happen and I was sure there was something fishy…

Pepper: Tony, is it normal for database to get in RESTORING state after backup?
I said in my usual tone of ‘romcasim

Me: depends on what state you wanted it to be in

Pepper: I am in no mood to play ‘take-my-hint’ I need to get back to testing something please tell me what did I do wrong.

Me: OK OK.. hummmm tell me what all you did so I can help

Pepper: Let me ping you.

Here is what I learnt from her chat messages. Pepper wanted to test a few things which involved her taking database backup and transaction log backup. So she scrambled on the net and got the syntax quick without bothering to read much.  She did her test runs or code and ran the backup.

BACKUP DATABASE [BKP] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\full_backup.bak'
WITH NOFORMAT, NOINIT, NAME = N'BKP-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Then again some testing from SharePoint and now Ran below to take Tlog backup

BACKUP LOG [BKP] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\tlog_backup.trn'
WITH NO_TRUNCATE, NOFORMAT, NOINIT, NAME = N'BKP-TLog Backup', SKIP, NOREWIND, NOUNLOAD, NORECOVERY,   STATS = 10
GO

Now this is where I figured the mistake the “NORECOVERY” clause. As per MSDN documentation

NORECOVERY

Backs up the tail of the log and leaves the database in the RESTORING state. NORECOVERY is useful when failing over to a secondary database or when saving the tail of the log before a RESTORE operation.

To perform a best-effort log backup that skips log truncation and then take the database into the RESTORING state atomically, use the NO_TRUNCATE and NORECOVERY options together.

After having explained what that clause does, the obvious question pops out.

Pepper: Tony, all that is nice to know. How do I get out of the RESTORING state?

I gave her the following command, which she happily executed and voila DB was back online.

RESTORE DATABASE BKP WITH RECOVERY

Pepper: I owe you a coffee. Starbucks @ 5?

I was thinking in my head – One more midday coffee is going to be over the Tail Log backup discussion. As expected, she asked questions and I decided to share them here.

Question #1: Can this happen from user interface also?

Answer #1: Yes. Here is the option in SQL Server Management Studio (SSMS)

Question #2: But why would someone do that? Why would someone take production database into restoring state?

Answer #2: These kind of log backups are called as tail log backups. Imagine a situation where DBA has configured log shipping. As a part of DR drill, application team wants to move production workload to secondary server. One DR drill is complete, the old primary should again take primary role. If we don’t want to reinitialize the log-shipping via full backup then here are the steps.

  1. Disable all log shipping jobs (on primary and secondary)
  2. Restore all pending transaction logs which are not yet applied on secondary with norecovery.
  3. Take tail log backup with norecovery. This would leave primary database in restoring state.
  4. Restore this tail log backups on secondary database using “with recovery” clause.
  5. This would bring secondary open for read/write activities and testing.
  6. Once testing completes, take a tail log back-up from current primary (secondary initially)
  7. Restore that backup with recovery on current secondary (primary initially)
  8. Enabled all log shipping jobs which were disabled in first step.

That was an eye-opener for Pepper and at least, she paid for my coffee.

Reference: Pinal Dave (http://blog.sqlauthority.com)