Tag Archive for SQL Server 2008

Practical SQL Performance Troubleshooting

DBA daily life is always hectic. They receive many issues ranging from network, performance, security, or application. I had collected many scripts and tools since couple of years ago. They are very useful for ad-hoc troubleshooting and diagnose the problem on the spot.

When I mentioned ad-hoc means the ability to locate and diagnose the problem quickly, without collecting data with SQL Profiler or Perfmon. I’m not saying that ad-hoc approach is better, but there are many situations when we just do not have enough time to collect the data while my users are screaming. The DMVs become handy tools for this purpose. It shipped since SQL Server 2005, and has been improved in 2008. I always has those DMVs in my shortcut folder, or in most cases in my thumb drive.

I had the opportunity to do demo on this topic at Singapore SQL User Group meeting last month. The response was fantastic, knowing that we have many free scripts to solve our daily problem. I promised to upload all presentation and sample codes, so here they are:

How to prepare for Exam 70-432

I received this question all over again during my daily jobs teaching SQL Server. I was tempted to create a guide base on my personal experience, but the time really don’t like me. Not until I received invitation to deliver short lecture for NUS student last month.

They were preparing for Exam 70-432: SQL Server  2008 Implementation and Maintenance. It is a foundation exam for SQL DBA. It test our knowledge on daily administration and a bit of development job. I wrapped up couple of important clues and tips, and compile them in PPT slides. You may download it here:

Upgrading to Reporting Services 2008 R2

Well now SQL Server 2008 R2 has been released, now what? I want to take advantage all of cool stuff in Reporting Services 2008 R2. The obvious thing is I have to upgrade the existing report. There are couple of upgrade path supported:

  • From SSRS 2005 to SSRS 2008 R2
  • From SSRS 2008 to SSRS 2008 R2

In either path, there are couple of methods that we can use:

  1. In-place upgrade, easy but I strongly not recommend this.
  2. Side by side by attach and detach the database. This will be my main focus in this post.
  3. Obtain report source code, convert with VS2008 and deploy to new server with R2 enabled.

First method was definitely not my choice because does not provide roll back scenario. The 3rd method is nice but it means I have to reconfigure all security settings I have done. I did the second method which is pretty easy as long as we plan carefully, and retain all report permission intact.

I had SSRS 2005 when decided to upgrade. Make sure that it already upgraded to service pack 2. The reason is SSRS 2008 will upgrade the database schema automatically as long as it’s already SP2.

Step 1
Backup ReportServer and ReportServerTempDb from old server. Actually the first one is the most important, but just take both backup just in case :).



Step 2
Backup encryption key from old machine. It’s needed later when we restoring the configuration on R2 machine. The key is important to prove that we are the legitimate person to perform restoration.


Step 3
Install SSRS 2008, this can be in the same machine with different instance or separate machine. Take note that we can actually run it as a service only and still use SQL Server 2005 as a storage for ReportServerDB. I won’t explain about the installation steps because it’s not the interest of this post. I assume that most DBAs know how to do it, otherwise do your homework in search engine :). Make sure to not configure RS during the installation because we will do it manually later.

Step 4
Restore both databases to a new machine. Actually it’s not necessary to restore it to SQL 2008 R2 storage because RS 2008 R2 supports backward compatibility. There are many cases that company just want to have a new RS R2 but still want to utilize old SQL 2005 storage, which is fine. In my case, I restore them to the same instance of SQL 2005 with SP2 because I could not get SQL 2008 one because of licensing issues. Take note that I restore them as different name for example: ReportServer2008R2 and ReportServer2008R2TempDB. This to avoid overwrite of existing RS 2005 DB. If you have separate machine you do not need to pay attention of those naming anyway.

Step 5
Configure Reporting Service 2008 R2. Open Reporting Service Configuration manager to configure virtual directory, service account, and the most important thing: connect to existing ReportServer2008R2. I won’t explain in detail about the basic configuration here, since most DBA should be familiar with it.

The one that needed attention is to connect to existing database rather than creating a new database. In my case, I connect to ReportServer2008R2 which is the one I restored earlier. And here is the magic: RS 2008 R2 will convert and upgrade all RS 2005 schema to R2 automatically. All reports and security configuration are safe inside.



The last thing is to restore the encryption key. We won’t able to start the service without supplying a valid key.


Step 6
Test your server and report. Navigate to http://yourservername/reportserver to check that the service is alive. Then open http://yourservername/reports and see that all reports has been migrated to R2 :)



Presenting Trend with Reporting Services

I posted KPI report in previous entry, something that really cool in SSRS 2008. There is another method to display a trend without much attention on number and achievements. It’s called sparklines. There are many occasions when decision maker only need to know the trend regardless of actual values. A simple visualizations can help them to grab the fact quickly.

Below is an examples of sparklines:


How to create a sparklines report? It is actually simple. In general, there are 3 steps:

  • Create a chart of your choice. Lines chart is a popular form. Bind it to the data of interest such as sales amount, total cost, etc.
  • Squeeze the chart as small as possible but still maintain its readability. Remove all unnecessary details like X and Y axis, legend, series label, etc.
  • Drag and drop the chart in to data area of your report and tidy up for better presentation.

The good thing about sparklines is its dynamic behaviour. Since it is located inside data area, it follows whatever grouping setup in rows and column. The sparklines adopts to drill down report setup along with its grouping, like the following example:

collap expand

The most important thing of creating sparklines are data binding and removing unnecessary chart properties. In the following picture, the chart is bounded to Sum(SalesAmount). I removed X and Y axis and also its title. This is a straight forward task by right clicking every property and select disable or delete.


The last step is to add additional column in report table for sparklines container. I use “Total Trend” as presented in the first picture, then drag and drop the chart in to column and tidy up for better visualization. What about source code? Download sample .rdl file from here:

As usual, it needs AdventureWorksDW2008 sample database which freely available from codeplex.

KPI Report with Reporting Services 2008

Reporting Services 2008 brought many enhancements for data visualization. The queen is Gauge, which is ideal tool to create scorecard and KPI. Imagine the report popping up in front of manager with colourful and fancy indicators like this:


Gauge is only one among many other things. Charting is more fun than ever, with many 3D effects and customizable areas. I also like new feature such as calculated series that deliver moving average report in minutes. No more complex expression to calculate median or moving average data.

Gauge also comes with many type such as radial type with speedometer style. I can display sales indicator and turn the pointer to red, yellow, or green like this:


I did demo and discussion about this topic in Singapore SQL UG meeting on June 25, 2009. As I promised to upload my slide deck, you can grab it here:

Of course I release the source code too for deeper exploration. Apologize that I don’t have enough passion to tidy up the reports for best appearance. It is available in my skydrive:

All reports need AdventureWorks2008 and AdventureWorks2008DW sample, available to download from CodePlex.

Below are some pictures from the meeting:

SQLUG-25Jun 2009-06-25 006

SQLUG-25Jun 2009-06-25 009

Some Notes on SQL Server 2008 Backup compression

I blogged about backup compression here, and I mentioned that the main advantages are saving space, reduced backup and restore time. After discussion with some fellow MVPs, I have to revise my statement in previous post.

Yes, backup compression saves backup and restore time only if there are enough CPU and IO resources available. This is obvious because IO is the biggest bottleneck when reading/writing backup file. Thanks to Aaron and Andrew for remind me about this fact.

However, IO bottleneck is solvable if we use fast removable storage (eSATA for example) instead of internal hard disk. With this way, we can take full advantage of compression without affecting current machine performance.

The SQL Customer Advisory team did a very comprehensive benchmark on this. Below is some screen shoot I cut and paste from their blog post:



The complete post for benchmark by SQLCAT is here


New in SQL Server 2008: Backup Compression

Backing up database is a routine DBA job. This easy task become a headache when the machine runs out of disk space. We need to cleanup the disk or attach a new one to do the backup. Fortunately, SQL Server 2008 comes with built in compression feature of backup set. We can do backup and compress at the same time. What does it mean?

  • Save disk space, the compressed size is only around 10% of normal backup.
  • Faster backup time.
  • Faster restore time (surprise)!.

This is very convenience approach instead of doing normal backup and compress it with Winzip. According to benchmark I did, Winzip compression works slower than backup with compress on the fly. Not mention that we also have to unzip the file to restore from backup.

I am also surprise because restoring compressed backup is faster than non compressed. Logically, it needs to decompress and restore so it should be slower than restoring normal one. But my benchmark showed the opposite. I modified the AdventureWorks2008 sample database and insert additional data to make it bigger.

Below is my benchmark results with 900 MB size.

  • Backup with no compress: 115 seconds
  • Backup with compress: 49 seconds
  • Restore from non compressed backup : 123 seconds
  • Restore from compressed backup : 113 seconds

So, are you convinced? Here are the scripts to do compressed backup.

Specify with compression when doing a backup:

BACKUP DATABASE AdventureWorks2008
TO DISK = 'C:backupdbadvworksbackupcompress.bak'


The result is a compressed backup file, it’s smaller size then non compressed:


It’s reduced from 900+ MB to only 118 MB, not bad right?

Restoring from compressed backup is straight forward. No additional option required. Just normal restore syntax from the name of backup set.

RESTORE DATABASE AdventureWorks2008
FROM DISK = 'C:backupdbadvworksbackupcompress.bak'

I use with REPLACE option to override existing AdventureWorks2008 database.

Shrink and manage huge SQL log file

Updated on January 10, 2011:  This post is outdated

You should not shrink database file too often because It creates more fragmentation. Quote from MSDN documentation:

Best Practices

Consider the following information when you plan to shrink a database or file:

  • A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
  • Most databases require some free space for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
  • A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. For example, you should not shrink a database or data file after rebuilding indexes. This is another reason not to repeatedly shrink the database.
  • Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.



I had a bad experience with transaction log file, it was 8 years ago. But thanks to Mr.Bad, I learnt very important lesson as newbie DBA at that time. This is the story: my ERP application which sitting on top of SQL Server 2000 was fine at Friday afternoon. I went home for a weekend, only to find everybody was starred at me at Monday morning. What had happened? The ERP was crashed, actually it couldn’t start at all.

I went to my SQL Server box, and the guy was stopped. It’s not started. I checked everything from security permission to possibility of virus, nil. Then I found that the disk was out of space because of transaction log explosion. The accounting guy was doing quarterly stock journal in the system, and it caused transaction log growth exponentially. There was an audit table which recording all journal operation, and obviously the INSERT operation was the source of problem.

Moral of the story: make sure you have automated way to manage transaction log growth.

There are 2 way to shrink the log:

  • Detach database, delete the big log, and re-attach without log file. SQL Server will create a new, fresh log file with minimum initial size. It is 2MB by default. Disadvantage: the database will not be available during the process. Offline duration depends on how fast is the detach – attach operation takes time.
  • Better approach: use SHRINKFILE of DBCC command. This is better because I do not need to bring the system OFF.

Regardless of the approach, make sure to do full database backup. Shrinking the log deletes historical transaction that may be needed to restore database to specific point of time. Just in case, make sure to do full backup. You have been warned right :).

These are the steps to shrink a huge log file:

First, obtain the log filename, simply use sp_helpdb as follow:

sp_helpdb 'AdventureWorks'

It returns all files in AdventureWorks:


Then shrink AdventureWorks log file to 2 MB size:

USE AdventureWorks
DBCC SHRINKFILE (AdventureWorks_Log, 2)

The outcome is 20 GB log file has been shrunken to 2 MB:



I usually run DBCC SHRINKFILE on scheduled basis, by attaching SQL script to SQL Agent job. This is easier and painless :).