mca

What is The Best SQL Resources on Internet?

Well, I always receive this question again and again, specially when delivering talks on technical communities. Veteran DBAs may just simply doing bing or googling, most of newbie find themsselves overwhelmed by tons of info from the net.

Let me share my favourite site that I always visit regularly:

 

SQLServerCentral

I subscribe to their newsletter since 2001, and never get bored since then. I even wrote 2 aqrticles for them, but now I just do ot have enough time anymore. This is the biggest SQL articles repository in the net, with very active community. SQL MVPs Brian Knight, Andy Warren, and Steve Jones are the founder and managing the site.

 

http://www.brentozar.com/

He is a SQL MVP and MCM as well, posted many interesting stuff on IO planning, virtualization and other random stuff

 

http://sqlskills.com

It containts the most advanced SQL Server stuff on the planet, maintained by highly respected SQL expert Paul Randall and Kimberly Trip. Paul is the creator of DBCC CheckDB when he was with Microsoft.

 

Technet and MSDN

Many resources such as video, how to, and whitepaper.

Resources on Capacity and IO Planning

I am posting some collection of links and resources related with capacity planning, plan for performance, and IO planning. This is also a kind of note to self, so it will be easier for me to find out those links in the future.

 

Estimating the Size of a Clustered Index
http://msdn.microsoft.com/en-us/library/ms178085.aspx

Estimating the Size of a Database
http://msdn.microsoft.com/en-us/library/ms187445.aspx>

Proper I/O Capacity Planning and Configuration Resources for SQL Server
http://blogs.msdn.com/b/askjay/archive/2011/03/25/proper-i-o-capacity-planning-and-configuration-resources-for-sql-server.aspx

Predeployment I/O Best Practices
http://technet.microsoft.com/en-us/library/cc966412.aspx

Physical Database Storage Design
http://technet.microsoft.com/en-us/library/cc966414.aspx

Storage and SQL Server capacity planning and configuration
http://technet.microsoft.com/en-us/library/cc298801.aspx

Storage Performance Testing with SQLIO
http://www.brentozar.com/archive/2008/11/storage-performance-testing-with-sqlio/

SQLIO Tutorial: How to Test Disk Performance
http://www.brentozar.com/archive/2008/09/finding-your-san-bottlenecks-with-sqlio/

Tuning SQL Server for SharePoint Performance

Updated on May 23, 2013

I had an opportunity to present on SQL Server User Group Indonesia monthly meeting on Oct 27, 2011 about SQL performance tuning from SharePoint perspective. Most of DBAs might be familiar with SQL stuff, but not on what SharePoint expects from SQL Server. Understanding how SharePoint works is really important in tuning SQL Server.

Most of the SharePoint contents are stored in SQL Server. Documents, picture, pages, Lists, and its configuration. Document and picture are stored as binary in SQL, so do not expect to find the physical files in SharePoint server.

Most of physical files that are not stored in database located under 12 hive (14 hive in SharePoint 2010). This folder contains customization files such as CSS, master pages, features, ASPX page. There are some occasion that developer prefer to store master page and layout in the library so it is actually in your SQL tables. So please communicate closely with the development team to understand exactly on file location. However, rest assure that 90% of SharePoint contents are inside SQL Server.

Below are some guidance to configure SQL Server for SharePoint performance:

Configure instance level index fill factor to 70%. Most of veteran DBAs now that this setting is optimized for read operation with moderate insert and update. You may think to optimize fill factor on table and index level, but this is not possible in SharePoint database. Microsoft does not recommend to alter table and index of SharePoint DBs. Default fill factor is 0 so you need to alter this setting. The 70% is not a magic bullet, do continuous monitoring and adjust fill factor accordingly.

Set MAXDOP to 1; contrary to popular belief, SharePoint does not benefit much from parallelism. There are lots of cases when SharePoint produces ad-hoc queries that SQL Server may struggle find the most optimal path. Setting the value to 1 means forcing SQL Server to not create parallel plan. More discussion and testing about this are here:

http://blogs.msdn.com/b/grahamk/archive/2009/06/15/troubleshooting-sql-server-deadlocks-and-performance-when-running-sharepoint.aspx

http://blogs.msdn.com/b/rmeure/archive/2011/12/23/optimizing-sql-for-sharepoint.aspx

 

Turn OFF update statistics; and configure proper schedule to refresh it. Make sure to set proper schedule for this to avoid outdated statistics.

Plan your data and log file growth. The default data file growth in SQL Server is 1 MB so it does not make any senses for SharePoint system. Imagine somebody is uploading 5 MB file and there are 100 uploads per day. It means your data file will grow 500 times/day; this will create bottleneck in IO system and usually escalated to CPU. I usually prefer to do capacity planning to forecast database size for 3-6 months. So if you think that your data growth will be 50 GB in 6 months, then set the data file size to 50 GB and configure its growth rate to something around 500 MB. Under previous scenario, your database only needs to grow once per day to accommodate 500 uploads. This reduce IO bottleneck significantly and speed up SharePoint performance.

Plan your tempdb; this can be a very long discussion and deserve separate post. There is a reference from Paul Randall about tempdb optimization here:

http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx

 

There is a long whitepaper from Microsoft about this:

http://www.microsoft.com/download/en/details.aspx?id=24282

 

KPI Report with Reporting Services 2008 R2

There is a new gem in SQL Server 2008 R2: Indicator control. It enables us to create KPI style report in easy way. You may ask how is it difference with Gauges that already exists in SQL Server 2008?

Well, Gauges is cool by displaying speedometer style indicator. But sometime they are just too much. I don’t want the fancy circle style, instead I want it to be a simple traffic light or dot indicator like this:

image

If you are a Reporting Services veteran then you know what to do: upload couple of traffic light symbols and bind them to a cell. Then write an expression to hide or unhide each symbol accordingly. We don’t need to do this anymore in R2. Here is the way to achieve it in easy way.

First, as usual we need to provide a cell as placeholder for this indicator. Second, place indicator control inside the cell then bind them to corresponding columns in the dataset.

image

The last step is configuring the expression logic for them. By default is only showing 3 state: Red, Yellow, and Green. But we can add as many states according to requirement. Once its done, just render the page to show KPI report.

Here is my sample KPI report. It requires Reporting Services 2008 R2 and AdventureWorksDW2008 database.

The same concept also applied to display trend in R2. I posted about creating trend sparklines in SSRS 2008, which is require to do a bit workaround. Not in R2 version, because It has dedicated sparklines control as well.

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.

Preparation
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 :).

backupdb

 

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.

backupkey

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.

r2db

r2db-2

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

restoreky

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 :)

reportserver

reportmanager

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:

sparklines1

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.

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:

thermo1

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:

gauge1

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:

sqlcatcompress1

sqlcatcompress2

The complete post for benchmark by SQLCAT is here

http://sqlcat.com/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx