Monthly Archives: December 2017

Speeding up database restores

This is a simple thing every DBA needs to do when they are done with installing SQL server. However it didn’t seem to be as popular as it should have been and I found out that most DBA knew about this only when Microsoft made it part of the SQL Server install process for SQL 2016.

The feature in question is Fast File Initialization.

The Theory

Before we begin on how this improves the speed of the database restores it might make more sense to understand what this feature can do for you. Fast file initialization is an OS feature which prevents the OS from preemptively zero filling free space before writing to it. Essentially when free space is being recovered such as in the case of auto growth etc. the OS first wipes out the database in the space allocated before writing to it. For small file size this process is hardly noticeable but when the database MDF, LDF file has to grow significantly it can add a serious overhead.

The Problem

E.g. when the database MDF file is already 200 GB and now needs to be restored Windows first has to grant and Zero fill 200GB space from the available free space in the disk and then grant the same to SQL Server. In this case the restore has to wait a significant amount of time before the actual file restore happens. The best way to identify this issue is to look for the resource wait.

ASYNC_IO_COMPLETION

As we can see the restore process is waiting for the IO process to complete. The query to identify his is given below:-

SELECT  
  wait_type AS [WaitType]
FROM sys.dm_exec_requests
WHERE Command LIKE '%RESTORE%'

The Solution

Slow disk speeds can affect the performance of the restore or any disk based activity but in this case we assume that the issue is with the lack of Fast file initialization as was for a client of mine for whom we were doing migration recently.

The solution actually doesn’t have much to do with SQL Server actually even though it has a great impact on the performance of the system.

First you need to grant the required permissions to the SQL Server Service account which in this case is

Perform volume maintenance tasks

Which can be granted by going to START>RUN>gpedit.msc>Computer Configuration>Windows Settings>Security Settings>Local Policies>User Rights Assignment>

Double click the permission and the add the user and then restart SQL SERVER to have the changes take effect. And that’s it.

For our client a database of around 700GB was taking over 10 minutes to complete even 10 percent of the restore. After that above we were able to improve the restore time so that the whole thing completed in 20 minutes.

PowerBI Connection error when logging into the cloud site

Recently in a training we encountered the below issue. I guess this will only occur in cases like a training room. Essentials the error message we get when we try to connect to PoweBI Cloud site is

“Connection error Your connection has been lost. Please check your network connection settings. As soon as your connection is restored, you can continue using Power BI.”

As mentioned in the link below the solution is actually fairly simple even if the error message doesn’t point to the correct root cause. In the training room the timezone of the system was set to Canada while the training was being conducted in Pune. The difference in time zone I guess results in the SSL certificate to the PowerBI site to become invalid and as a result we get the error message. Simpley change the time zone and time to the correct value and you should be able to connect to the site without issues.

References

https://community.powerbi.com/t5/Service/Connection-Error-Power-BI/td-p/18677