SQL SERVER – Root Cause to Performance Problems – Notes from the Field #002

[Note from Pinal]: This is a second episode of Notes from the Fields series. When there is a performance problem we all relentlessly work to fix the issue, however, we hardly spend time to find what has created the issues in the first place. For example, it is easy to turn off our car alarm if it goes off every hour but it is equally important for us to find out what is causing it to happen and prevent it. Just like that Root Cause Analysis (commonly known as RCA) has been a very important element.

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very common issue DBAs and Developer faces in their career – how you perform a performance related root cause analysis. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.


The first time completing a root cause analysis for a performance issue can be a stressful experience for a DBA who is new to performance root cause analysis or performance tuning. Many years ago during my first root cause analysis I honestly had no clue what I was doing. I had developers, managers and spectators over my shoulder pondering my every move. It was an experience I will never forget.  As an accidental DBA becoming a production DBA, I quickly discovered the difference between what I thought I knew and what I actually knew.

I never want to see you have a similar experience. The biggest performance tuning mistake I still see in the field is DBAs skipping the process of doing a root cause analysis and making knee-jerk reactions due to something they see or are told.  I have good news: a lot has changed in the last ten years with SQL Server.  In SQL Server 2012 you can find the root cause to performance problems without writing a single line of code [Youtube]. Even if you are not using SQL Server 2012 today, a Jr. DBA, accidental DBA or IT professional that wears multiple hats can easily do performance root cause analysis with SQL Server, all you need is a good checklist and some helpful scripts and you are ready.

The following scripts and documentation will get you started doing your own performance root cause analysis:

  • Baseline SQL Server Wait Statistics
    • Capture and measure your server’s wait statistics, as this gives deep inside in your server’s query waits and resource bottlenecks (link)
  • What SQL Statements are Causing Top Waits?
    • Identify queries offending server resources (link)
  • What is Running?
    • Understand what are the different activities going on in your server at any point of time. (link)

In additional to above three primary RCA practices it is very important to understand baseline for disk latency as well as query cache offendors. Hopefully, this will get you started with finding the root cause to your performance problems.

Are you doing Root Cause Analysis of your SQL Server Performance Problems? If not, you MUST do it. If you want to get started with the help of experts read more over here: Fix Your SQL Server.


Additional reading:

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

About these ads

SQL SERVER – Transaction Log Full – Transaction Log Larger than Data File – Notes from the Field #001

I am very excited to announce a new series on this blog – Notes from the Fields. I have been blogging for almost 7 years on this blog and it has been a wonderful experience. Though, I have extensive experience with SQL and Databases, it is always a good idea that we consult experts for their advice and opinion. Following the same thought process, I have started this new series of Notes from the Fields. In this series we will have notes from various experts in the database world.

My friends at Linchpin People have graciously decided to support me in my new initiation.  Linchpin People are database coaches and wellness experts for a data driven world. In this very first episode of the Notes from the Fields series database expert Tim Radney (partner at Linchpin People) explains a very common issue DBA and Developer faces in their career, when database logs fills up your hard-drive or your database log is larger than your data file. Read the experience of Tim in his own words.


As a consultant, I encounter a number of common issues with clients.  One of the more common things I encounter is finding a user database in the FULL recovery model that does not make a regular transaction log backups or ever had a transaction log backup. When I find this, usually the transaction log is several times larger than the data file.

Finding this issue is very significant to me in that it allows to me to discuss service level agreements with the client. I get to ask questions such as, are nightly full backups sufficient or do they need point in time recovery.  This conversation has now signed with the customer and gets them to thinking about their disaster recovery and high availability solutions.

This issue is also very prominent on SQL Server forums and usually has the title of “Help, my transaction log has filled up my disk” or “Help, my transaction log is many times the size of my database”.

In cases where the client only needs the previous full nights backup, I am able to change the recovery model to SIMPLE and shrink the transaction log using DBCC SHRINKFILE (2,1) or by specifying the transaction log file name by using DBCC SHRINKFILE (file_name, target_size).

When the client needs point in time recovery then in most cases I will still end up switching the client to the SIMPLE recovery model to truncate the transaction log followed by a full backup. I will then schedule a SQL Agent job to make the regular transaction log backups with an interval determined by the client to meet their service level agreements.

It should also be noted that typically when I find an overgrown transaction log the virtual log file count is also out of control. I clean up will always take that into account as well.  That is a subject for a future blog post.

If your SQL Server is facing any issue we can Fix Your SQL Server.


Additional reading:

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

SQL SERVER – Simple Trick to Backup Azure Database with SkyDrive

To ensure your SQL Server or Azure databases remain safe, you should backup your databases periodically. And it is important to store the backups in a reliable location. Microsoft SkyDrive currently offers 7GB free, Box offers 5GB free – both are reliable and it is simple to send your backups there. SQLBackupAndFTP in it’s latest version 9 added the option to backup to SkyDrive and Box ( in addition to local/network folder, NAS drive, FTP, Dropbox, Google Drive and Amazon S3). Just select the databases that you’d like to backup and select to store the backups in SkyDrive or Box. Below I will show you how to do it in details

Select databases to backup

First connect to your SQL Server or Azure Sql Database. Then select the databases you’d like to backup.

Connect to SkyDrive or Box cloud

If you have a free version of SQLBackupAndFTP Box destination is included, but SkyDrive destination will be disabled as it is available in the Standard version or above. Click “Try now” to get 30 days trial on all options

On the “SkyDrive Settings” form you’ll need to authorize SQLBackupAndFTP to access your SkyDrive. Click “Authorize…” to open SkyDrive authorization page in your browser, sign in your to SkyDrive account and click at “Allow” . On the next page you will see the field with an authorization code. Copy it to the clipboard. Box operation is just the same.

After that return to SQLBackupAndFTP, paste the authorization code and click “OK” .

After you are authorized, you can enter the path to a backup folder. SQLBackupAndFTP will create the folder if it does not exist.

That’s all what has to be done to backup to SkyDrive or Box cloud.  You can now click on “Run Now” button to test this job.

Conclusion

Whatever is your preference for storing SQL backups, it is easy with SQLBackupAndFTP. Note that at the time of this writing they are running a very rare promotion on volume licenses:

  • 5–9 licenses: 20% off
  • 10–19 licenses: 35% off
  • More than 20 licenses: 50% off

Please let me know your favorite options for storing the backups

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

SQL SERVER – Database in RESTORING State for Long Time

A very interesting question I received the other day.

“Our database has been in restoring stage for a long time. We have already restored all the necessary files there. After restoring the files we are expecting that  the database will be in operational mode, however, it is continuously in the restoring mode. Any suggestion?”

The question is very common. I sent user follow up emails to understand what is actually going on with the user. I realized after restoring their bak files and log files their database was in the restoring state because they had not restored the latest log file with RECOVERY options.

As they had completed all the database restore sequence (bak and log in order), the real need for them was to recover the database from norecovery state. User can restore log files till the database is no recovery mode. If the database is recovered it will be in operation and it can continue database operation. If the database has another operations we cannot restore further log as the chain of the log file after the database is recovered is meaningless. This is the reason why the database has to be norecovery state when it is restored.

There are three different ways to recover the database.

1) Recover the database manually with following command.

RESTORE DATABASE database_name WITH RECOVERY

2) Recover the database with the last log file.

RESTORE LOG database_name FROM backup_device WITH RECOVERY

3) Recover the database when bak is restored

RESTORE DATABASE database_name FROM backup_device WITH RECOVERY

To understand how the backup restores timeline works read Backup Timeline and Understanding of Database Restore Process in Full Recovery Model.

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

SQL SERVER – Azure SQL Databases Backup Made Easy with SQLBackupAndFTP

Azure SQL database backup used to be a difficult task. Not any more. With SQLBackupAndFTP with Azure it became trivial. Here’s what you basically need to do:

Once  SQLBackupAndFTP with Azure  is installed, click at “Connect to SQL Server / Azure” button and specify connection properties for your Azure SQL Databases:

Then click “Run Now” to backup your Azure SQL Database(s):

Scheduling backups is also very simple – just check “Schedule this job on the main form” to run once daily or go to Advanced Settings for more options

Sounds simple? There are just a couple more things you need for this to work: you should know your Azure Server Name and Azure should allow your IP to connect. Let me explain how to do it.

How to find your Azure SQL Databases Server Name/Connection String

If you do not know a server name of Azure SQL Database, you can find this out on  Windows Azure Management site.  Sign in with your Microsoft account and click the “SQL DATABASES” menu item, then click your database name

Then click “Show connection strings” link in the bottom right corner:

You will see the connection strings for many platforms. Just copy to clipboard “Server” property value of “ADO.NET” connection like in the screenshot below:

And paste it into the “Server Name” field of the “Connect to SQL Server / Azure” window in SQLBackupAndFTP:

Allow your IP address to connect  to your Azure SQL Databases server

For SQLBackupAndFTP to connect to Azure SQL Database, you need to configure Azure’s firewall. Otherwise you will be getting the error: Cannot open [server] requested by the login. Client with IP address [ip-address] is not allowed to access to the server…

Login to Windows Azure Management site with your Microsoft account, click “SQL DATABASES” menu  item, then click your database:

Then click “Manage allowed IP addresses” link in the bottom right corner::

You will see a page where you can specify an allowed IP addresses for your databases:

On this page you can configure your firewall to allow the machine where SQLBackupAndFTP is running access to Azure. After adding the proper IP you should have no problem connecting to Azure.

Summary

With  SQLBackupAndFTP with Azure it is very simple to take backups of Azure SQL databases as well as of regular SQL Server.

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

SQL SERVER – Restore SQL Database using SSMS – SQL in Sixty Seconds #044 – Video

“How do I restore my backup?”

I often receive this question from two kinds of users – i) who are panicking as they are not able to restore database ii) developers who have full backup but not sure how to restore it and use it.

In industry everywhere you go, everybody is taking backup but I noticed hardly anybody try to restore it. When users have not restored the backup for a long time, they have either no expertise to restore or have no idea if their backup have an issue while restoring.

Earlier I have created a SQL in Sixty Seconds Video on How to Take Backup of the database, in this video we learn how we can restore the same backup.

Let us see the same concept in following SQL in Sixty Seconds Video:

Related Tips in SQL in Sixty Seconds:

What would you like to see in the next SQL in Sixty Seconds video?

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

SQL SERVER – Backup and Restore Database Using Command Prompt – SQLCMD

Backup and Restore is one of the core tasks for DBAs. They often do this task more times than they would have ideally loved to do so. One thing I noticed in my career that every successful DBA knows how to automate their tasks and spend their time either playing games on a computer or learning something new!

Let us see how DBA can automate their task about Backup and Restore using SQLCMD. I am demonstrating a very simple example in this blog post. Please adapt the script based on your environment and needs.

Here is the basic script for Backup and Restore

Note: name of my database server is touch and I am connecting it with windows authentication.

Backup

BACKUP DATABASE AdventureWorks2012 TO DISK='d:\adw.bak'

Restore

RESTORE DATABASE AdventureWorks2012 FROM DISK='d:\adw.bak'

Here is the basic script for Backup and Restore using SQLCMD

Backup

C:\Users\pinaldave>SQLCMD -E -S touch -Q "BACKUP DATABASE AdventureWorks2012 TO DISK='d:\adw.bak'"

Restore

C:\Users\pinaldave>SQLCMD -E -S touch -Q "RESTORE DATABASE AdventureWorks2012 FROM DISK='d:\adw.bak'"

Please leave a comment if you are facing any issue. As mentioned earlier the scripts are very basic scripts, you may have to adapt them based on your environment. For example, if you are facing error there are chances that database files are already open or exists on the drive. You you should also have necessary permissions to do backup and restore as well file operations in your server.

Watch a 60 second video on this subject

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