Monthly Archives: July 2017

Decimal datatype rounding

Recently I was asked this question about how decimal is rounding off values when doing a simple calculation. The question was

DECLARE @x DECIMAL(38,0)=0
DECLARE @y DECIMAL(32,4)=4.2
SELECT @x-@y
 
--Output -4.2
DECLARE @x DECIMAL(37,0)=0
DECLARE @y DECIMAL(32,4)=4.2
SELECT @x-@y

I am not able to find why output is getting round off in case of DECIMAL(38,0) but not in case of DECIMAL(37,0). As per BOL, decimal 37 and and 38 both should be having same size and that should not matter.

The answer is quite simple but not so obvious until you look closer. The maximum value for the precision (p) part of the decimal data type decimal (p,s) is 38. However the scale (s) comes from subtracting the number of digits required from the precision. So when you use something like decimal (10,6) your saying that the total number of digits on either side of the decimal point cannot exceed 10 with up to 6 digits allowed on the right of the decimal place so it looks something like (4,6) in our eyes. This is common knowledge and we are all aware of it. This logic is true for how values can be stored into this datatype and not really accurate when it comes to how mathematical operators behave when performing addition and subtraction on this data type.

When the precision is already at 38 and scale is set to zero then there is no more bytes available for decimal places to be stored in the data type. Hence the result for any addition / subtraction will result in zero decimal places to the right in order to make sure the precision is accurate. However when performing multiplication or division this doesn’t apply because then decimal places and the scale of it matters and we cannot just truncate the result to fit the datatype of the result. If you refer the link below you will see the explaination for this scenario just below the table explaining the precision for each case I mentioned above.

References

https://docs.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql

Identity value skips after restart

Recently a client of mine had complained to me about an issue they were facing when restarts happen on a system. After a restart the system skips the identity value by a 10 – 1000 depending on the data type of the identity column. This was a known issue in SQL 2012 and was touted as a feature which annoyed a lot of people btw.

However a fix was provided for it later on and I figured that was the end of the story. However after checking it out myself I found that the issue still persists in SQL 14 as well. But in a slightly different way. Previously the issue in SQL 2012 used to happen even with a controlled shutdown of SQL Server using SSMS or service control manager. But in SQL 2014 it doesn’t happen that way with a planned or controlled shutdown. e.g. if I insert 10 rows into a table then execute the “shutdown” command and restart the service the next row will have an id of 11. However if I simulate a power failure (end task SQL server service from task manager) and then insert a row the id will be 1004 which indicates the issue still exists and is noticeable only after a unplanned shutdown. Which in most cases is a problem because we aren’t looking for this problem at that time.

So is there a solution now?

Well yes and no. SQL 14 even with the latest service pack doesn’t really address this issue. But there is still hope if you add the Trace flag –T3597 you can get identity column reseed to stop happening for a dedicated install of SQL Server but unfortunately this doesn’t work if you’re in SQL Azure.

How to test

USE madworks  
 
go  
 
 
CREATE TABLE  identitycheck 
( id INT IDENTITY(1,1),
NAMES VARCHAR(100)
)
 
INSERT INTO identitycheck
SELECT 'Jayanth'
 
INSERT INTO identitycheck
SELECT 'Jayanth1'
 
INSERT INTO identitycheck
SELECT 'Jayanth2'
 
SELECT * FROM identitycheck
 
SHUTDOWN
 
INSERT INTO identitycheck
SELECT 'Jayanth3'
 
INSERT INTO identitycheck
SELECT 'Jayanth4'
 
INSERT INTO identitycheck
SELECT 'Jayanth5'
 
SELECT * FROM identitycheck
 
INSERT INTO identitycheck
SELECT 'Jayanth6'
 
INSERT INTO identitycheck
SELECT 'Jayanth7'
 
INSERT INTO identitycheck
SELECT 'Jayanth8'
 
SELECT * FROM identitycheck
-- after end task  
 
 
INSERT INTO identitycheck
SELECT 'Jayanth9'
 
INSERT INTO identitycheck
SELECT 'Jayanth10'
 
INSERT INTO identitycheck
SELECT 'Jayanth11'
SELECT * FROM identitycheck

 

References

https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity

Forcing Columnstore batch mode on Select * type query

Recently at a consulting project while working with tabular model I was asked about how we can force SQL Server to use batch mode on Columnstore index when doing a query which is essentially just a select *.

To demonstrate this I have written two queries below which will return the same number of rows and performs basically a select * on the data.

The table contains 5.8 M rows to allow for a realistic scenario. As you can see from the screenshot below we are executing in Row mode.

SELECT YEAR , carrier , airlineid 
FROM [dbo].[2014]

Now let’s modify the query by adding a filter that doesn’t actually filter anything

SELECT YEAR, carrier , airlineid 
FROM [dbo].[2014]
WHERE  MONTH >= 1 and MONTH < 13

As you can see from the screenshot below we can see that now the query execution mode is batch mode and we are still fetching the same number of rows.

So the next part of the question becomes while we are executing in batch mode do we get any significant performance improvement even though we are technically fetching all rows.

After three executions I found that batch mode does give a marginal improvement of about 6 seconds. I guess better results are possible on a server with multiple cores and better disk access.

Weird ways I explain seemingly complicated SQL topics

Over the years I have accumulated some seemingly weird ways to explain topics within SQL Server. Usually in a training I find that participants are able to relate to an example in real life compared to some code written on the screen. While often the example might not be a perfect fit it still conveys the important point I am trying to get across. Here are some of my most frequently used.

Explaining Dimensions and Facts

The purpose of Business Intelligence is to answer a question and when you think of it every question has two parts something I like to call the Qualifier and the Quantifier. E.g. Show me sales for Honda cars in Bangalore for 2010. In this question when it’s all said and done we are looking for a single number (the fact) which is sales. But we are not looking for sales across all cars, locations or years. We are slicing and dicing the data along three different axis (the edges of a cube) these axis we slice on are called dimensions (Qualifier).

Locking Blocking and Deadlock

Think of a bathroom stall. If a single person enters it and locks it from inside its just locking. Now while that person is inside anybody else waiting to use the stall is being blocked and should wait until the original lock is released. Now assume two people enter the stall simultaneously and one takes a seat while the other grabs the toilet paper. Neither can proceed without the other and there you have a deadlock.

Cursor vs Set based operations

RDBMS really on set based data access for performance. So it’s always better to access data in a set e.g. if I wanted to find all people with age =35 in a room I could ask each one individually like a row by row operation in a cursor or I could simply shout in the room that everybody with age =35 raise your hand like a set.

Principle of least privileges

Making sure that a person has the minimum permission to do the job is what we call principle of least privileges. Think of the server as a House obviously the admins will have the key to the front door through which they decide who gets into the house. Once within the house you have the different members of the house such as parents and children. Parents by nature behave as DBO while children can only access their room (database). However in the case where siblings share a room each has their own cupboard (table) with a key known only to them and thus they can share the room while enforcing privacy.

Wait stats

The little book in which your partner has made a list of all the things they don’t like about you in anticipation of the next fight.

Hashing and Encryption

Imagine a door with a secret password you can use to open it. In the case of hashing all that is needed is the password be the same so it doesn’t matter who says it as long as the word is the same (hash key) you will be granted access. Now imagine the same situation but this time the person on the other side also recognizes your voice. Now knowing the password isn’t enough you need to be recognized using your own voice (key) too. Therefore making your access unique in spite of the same password being used.

DBA and Developer

Developer the guy who drives the car, DBA the guy who fixes it when it’s broken. Neither can exists independent of the other. The Driver needs to know how to change a tire and the DBA needs to know how to start a car.

Type0, Type 1 and Type 2 Dimensions

Your parents are a type 0 dimension because they never change over time and will always remain your parents i.e. fixed. Your employer is a Type 1 dimension because while you may have worked for many companies in the past the only one that matters is the one you are working for now. Type 2 Dimension is best exemplified by siblings you will have siblings older than you and in future you may have new siblings arrive at a later date but I the end they all matter and just knowing the latest one (like type 1) isn’t acceptable.

There are obviously more but you get the idea. Often examples like this remain in memory far longer than anything else I have shown on the screen. If you know some nice example you have used or heard please feel free to share them.

 

Rebuild all indexes and statistics on Azure SQL database

Since Azure SQL databases are limited in what they expose to the management studio client we miss out on a lot of features within SSMS that we could normally use. One of the most common features we use frequently in this regard is the Rebuild Indexes and update statistics features within Maintenance Wizard.

Below is a very simple script that can be used to rebuild all indexes on all tables without regard to fragmentation. This could blow up the DTUs if you’re performing the common on a table with large number of records of in correct DTU sizing for the Azure SQL database.

SELECT 'ALTER INDEX ALL ON ['+ Table_schema+'].['+Table_name+'] REBUILD;' FROM  information_schema.tables 
WHERE Table_schema!='sys'
GO
EXEC SP_UPDATESTATS

This script is provided for use in databases managed by people who are not professional DBAs but still require scripts to perform basic tasks without having to worry about the complexity involved.

Additional References

https://thomaslarock.com/2012/03/march-madness-sql-azure-update-statistics/

http://pietschsoft.com/post/2011/11/10/SQL-Azure-REBUILD-All-Indexes-in-Database-e28093-Alternative-to-DBCC-DBREINDEX

BootMgr missing – 3 days of Hell

Just came out of a real deep mess after my laptop crashed 3 days back. The issue was a fairly common one but none of the solutions found on the internet were working and I had to fix things with extreme prejudice. All my data for the last 5 years exits on the same laptop. By the same laptop I mean even the same hardware. I have opened the guts of this HP Pavilion g4 at least 4 times in the past for everything from CPU fan not working to blown hard disk. But this was another thing all together.

So what happened? I ran Norton Power eraser on my laptop considering all the recent Malware going around (one often meets his destiny on the path he chooses to avoid it). During the process I guess a power failure happened causing things to fail. So when I started the laptop in the morning this is what greeted me.

Now when I installed windows I had a reserved system partition which is a hidden drive in which information regarding how to boot windows ( i.e. where windows is installed etc.) can be found. I figured it must have been corrupted and a simply chkdsk would fix the issue. However I needed a recovery cd. So went about creating system recovery disk. Using the instructions here. Unfortunately the USB wasn’t being detected. So I tried a CD instead by burning the files from my other laptop. This didn’t fix the issue either. So next I copied the files from a windows install cd into a USB and now it was being detected and I came to screen for repair your computer.

Next I tried system repair which would have normally fixed any boot issues, but instead I kept getting the error below “Boot Manager is missing or Corrupt.”

So next I choose the option for Command Prompt from the screen.

So I run sfc /scannow and then ran chkdsk /f /r to find and repair any errors on the disk.

Surprise check disk/sfc shows no errors. So the next step was to rebuild the BCD by running the instructions below:-

  • bootrec /fixboot
  • bootrec /fixmbr
  • bootrec /RebuildBCD

 

And then ran System repair 3 times as mentioned but this didn’t work either. First it didn’t find the Windows installation and the second issue was that even after rebuild of BCD the system wouldn’t boot without the install CD. So what did it fix then?

In the mean time I figured I should connect my HDD to another laptop and backup my data so that took another 7 hours.

Once the backup was done I went ahead and decided to nuke the system reserved partition and build it from scratch so I connected the HDD to the other laptop and formatted the drive using Disk management. Followed by the steps below to build a new system reserved partition.

https://www.sevenforums.com/tutorials/119151-system-reserved-create-using-disk-management.html

Since I figured the RebuildBCD wasn’t working I decided to create my own boot files by copying files from the recovery folder on the drive I had backed up using the instructions below

http://www.digitalcitizen.life/create-usb-memory-stick-system-recovery-tools

So after this step I needed to make the new system reserved partition active using the steps below:-

https://neosmart.net/wiki/set-partition-as-active/

So finally I was able to boot without the install media but it still kept coming to the system recovery screen and now I get a new error about

“The windows boot configuration data file does not contain a valid os entry windows 7”

It became obvious that the BCD I copied over was pointing to a drive that doesn’t contain the OS (because I rebuilt a new partition and the drive letter for the original drive on which windows was installed got reassigned). So I needed a way to edit the BCD and tell it where to find my OS.

 

So I followed the instructions at this site to build a BCD file only which pointed to my correct install directory.

http://windowsitpro.com/systems-management/build-bootable-bcd-scratch-bcdedit

 

Once this was done I got the error that winload.exe could not be found. This is the file that loads windows after the windows boot process is done. So now I was able to boot but it wasn’t able to find the exe to load windows. So next I copied over the file from another laptop and after boot I was able to starting trying to load windows. However I kept get missing driver files like config.sys etc

This was now the end of the second day and frustration level had reached its peak so I got lazy and simply went into C:\Windows\System32\Drivers\ and copied everything over to the bad drive. Naturally I end up with a blue screen error about ‘session manager failed to create protected prefixes system process terminated unexpectedly with a status of 0xc000003a

So in figured I should give up and called MS and the suggestion I got was to format and reinstall. Which I couldn’t because I have software installed for which I won’t even be able to get the installer anymore let alone product keys. So I went back to the internet for help. At this point my laptop is in a loop restarting every time it tried to get into windows because of the Blue screen of death.

That is when I came across the option to disable Checking Driver signatures. I used the temporary method.

http://en.miui.com/thread-237673-1-1.html

Finally I am able to boot to the desktop. Naturally the first thing I did was take a system image so that I can also get back to this point. After which I had a number of hardware showing bad drivers in Device manager. So went to the HP website and started downloading drivers for software. But even now every time I try to boot I need to press F8 so I needed a better solution to repair the drivers in system32 folder so I upgrade to windows 10. This seems to have fixed almost all the issues expect some new drivers that needed to be downloaded now that the OS was Windows 10 instead of Windows 7. But overall after three days My Laptop is up and running again and back from a near death experience and all my software is working and so is my data.

Was it worth it …. YES!!!! Was it easy ….. HELL NO!!!!