Microsoft SQL Server Development Customer Advisory Team

What you should know if you are using Logical IOs to measure query plan quality

One of the presenters at recent SQL Server Open World conference in Denmark (http://www.miracleas.dk/index.asp?page=168&page2=323)  suggested tuning SQL queries using logical IOs to measure quality of query plan. The IO count is returned for each object accessed in your query after you issue “set statistics IO on”. Here is an example of returned message:

Table 't1'. Scan count 10, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I have seen this approach many times in the past and I agree that in general, using less logical IOs is better and faster than using more. Therefore tuning query towards lower number of logical IOs in most cases produces query that is faster and uses less resources. But I would like to point out one potential pitfall when using this counter. In a nutshell  reading the same data may end up showing very different number of logical IOs depending on the “data access method”. This is caused by counting each new “seek” into any page (even repeatedly the same one) as new logical IO (in fact several  logical IOs because of the index navigation!) while reading all rows on the same page using “scan” counts as a single logical IO. 

If you run the following statements in SSMS after you create table t1 as shown in the appendix

set statistics io on
select * from t1 with (index=2) where a in (1,2,3,4,5,6,7,8,9,10)   -- Query 1
select * from t1 with (index=0) where a in (1,2,3,4,5,6,7,8,9,10)   -- Query 2

you will find following 2 messages in the “Messages” tab of the SSMS result window:

Table 't1'. Scan count 10, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 't1'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The first query is performing 10 index seeks showing “20 logical reads” using following query plan:   

|--Index Seek(OBJECT:([test].[dbo].[t1].[i1]), SEEK:([test].[dbo].[t1].[a]=(1) OR [test].[dbo].[t1].[a]=(2) OR [test].[dbo].[t1].[a]=(3) OR [test].[dbo].[t1].[a]=(4) OR [test].[dbo].[t1].[a]=(5) OR [test].[dbo].[t1].[a]=(6) OR [test].[dbo].[t1].[a]=(7) OR [test].[dbo].[t1].[a]=(8) OR [test].[dbo].[t1].[a]=(9) OR [test].[dbo].[t1].[a]=(10)) ORDERED FORWARD)

The reason why SQL Server counts 2 logical IOs for each seek is that it has to interrogate the “root” page of the index first to locate the correct “data” page. If an index has more levels, there will be as many logical IOs used to locate the data page as many non-leaf nodes must be accessed. Additionally, more than one “leaf” page has to be visited in some cases.

The second query returns different plan: 

|--Table Scan(OBJECT:([test].[dbo].[t1]), WHERE:([test].[dbo].[t1].[a]=(1) OR [test].[dbo].[t1].[a]=(2) OR [test].[dbo].[t1].[a]=(3) OR [test].[dbo].[t1].[a]=(4) OR [test].[dbo].[t1].[a]=(5) OR [test].[dbo].[t1].[a]=(6) OR [test].[dbo].[t1].[a]=(7) OR [test].[dbo].[t1].[a]=(8) OR [test].[dbo].[t1].[a]=(9) OR [test].[dbo].[t1].[a]=(10)))

The difference is in the “index seek” versus “table scan” operators. Accessing all rows by table scan counts each visited page as a single logical IO (one page visited in our case) while accessing rows in an index using seeks counts each “seek” and as pointed out above twice or even more times depending how many pages I need to visit to complete the seek.

Interestingly, if we remove index hint in the query, SQL Server optimizer will choose the query plan using more logical IO! To make situation even more complicated, we should be careful what counts as a single “seek” and what constitutes “multiple seeks”. If you slightly change our query to

select * from t1  where a between 1 and 10  -- Query 3

you will get following plan 

  |--Index Seek(OBJECT:([test].[dbo].[t1].[i1]), SEEK:([test].[dbo].[t1].[a] >= CONVERT_IMPLICIT(int,[@1],0) AND [test].[dbo].[t1].[a] <= CONVERT_IMPLICIT(int,[@2],0)) ORDERED FORWARD)

And the IO count for Query 3 is

Table 't1'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Query 3 is using single seek (therefore shows “scan count 1”) when returning all rows qualifying by “between 1 and 10” predicate. Similar Query 1 above used 10 seeks (“scan count 10”) because it’s seek predicate contains OR and each new “branch” of the OR predicate is using separate “seek”.

Conclusion: When you are using “logical IO” count to evaluate your query efficiency or compare various query modifications be aware that “logical reads” may have very different meaning depending on the data access methods chosen by the query optimizer.

Notes:

1. I have used CTP6 build of SQL Server 2008 to run the examples but the conclusion is valid also for SQL Server 2000 and 2005.
2. You can use SQL Profiler to capture Logical Reads in "Batch completed" event and the same numbers you can get using sys.dm_exec_query_stats. However, these are cummulative numbers for all accessed object as opposed to those returned by SET STATISTICS IO ON as described in the blog

Appendix:

create table t1 (a int, b char(10))
declare @i int
set @i=0
set nocount on

while (@i<10)
begin set @i=@i+1; insert into t1 values (@i,'x') end

create index i1 on t1 (a,b)

-- Following query shows how many pages are used by the table and its index

select OBJECT_NAME(id), indid, dpages from sys.sysindexes where id=OBJECT_ID('t1')

Lubor

Interesting observation with table > 1TB

Interesting observation with table > 1TB

We have a project using SQL 2005 with a table that is several terabytes.  This table has a varbinary(max) column with an average length of 150k bytes. 

We needed a second copy of the table on a smaller test server to do some other tests and decided that SELECT INTO was our best option because it is minimally logged.  The smaller test server is 4CPU with 8GB RAM.  Since we had all the SQL products installed on the test server, there was only 4GB allocated to SQL at first.  There was plenty of disk space available.

So we ran the SELECT INTO from the test server via a linked server connection to pull the data across.  It failed with error 802=Insufficient Memory in the Buffer Pool.  After some investigation it was discovered that this is due to the extent allocations needing an entry in the lock manager, which uses space in the buffer pool. Regardless of the hints or isolation level you set, extent locks are still acquired but only held if you have a text field (includes image and varchar(max)) that spans to another extent.  In other words, if you don’t have a text field or all the data fits in the same extent, then the extent locks are released immediately.  

In my case, with 150k average text size all rows required new extent allocations.  To calculate how many lock entries you will need for this scenario:  

Total data size / 64k * 128.  

Data writes are done in 64k chunks and each lock entry takes 128 bytes.  So a 1 TB table would need 2GB in buffer pool space to hold the locks for a SELECT INTO. The lock manager can have at most 60% of the total memory size available to SQL Server, so you would need a minimum of 4GB in the buffer pool.  Keeping some room for the OS and other memory used by SQL, a machine with 8GB RAM would barely be enough. 

You might think that the lock escalations that were so well explained by Sunil Agarwal in this blog http://blogs.msdn.com/sqlserverstorageengine/archive/2006/05/17/Lock-escalation.aspx would apply.  But it doesn’t because the locks in my situation are not normal table or page locks.  They are the locks acquired when allocating a new extent.  It will hold them until a commit occurs.  And a SELECT INTO has no control over batch or commit size, it happens all as one transaction.  In addition, the NOLOCK hint doesn’t do anything to the destination table, only the source table.

As a side note, the data writes are done in 64k chunks with SELECT INTO.  And it doesn’t matter if the text data is in row or out of row.  Out of row is where the row just contains a pointer the first page of text, which points to the next and so on.  Since this situation had a varchar(max) column with an average length of 150k bytes and SQL uses 8k pages, we had a lot of pages chained together for one row.  Fortunately in the source database, the read-ahead manager kicks in and puts the pages in memory before they are needed.  You can monitor this by starting Perfmon and watching the counter SQL Server Buffer Manager : Read Ahead Pages / sec.

I had an interesting idea that maybe partitioning the table might break it into smaller chunks, but there is no way to create a partitioned table using a SELECT INTO command.  The table cannot exist before you start the command and there is no syntax allowing the resulting table to end up being partitioned.  It would make no difference if the source table was partitioned because the memory is consumed by locks that track the new extents getting allocated while the data is written to the new table.

We could have done several other solutions, like SELECT INTO several tables, each with a single partition, then SWITCH the partitions into a final table.  Or we could have used SSIS bulk task and the result would have been the same if we had tried to do this all in one batch.  Making the batch size smaller in SSIS would cause more frequent commits and it would have released the memory used by the locks after each commit.

But we chose to turn off all other services and increase SQL’s max memory to 7.5GB and run it again. And it worked. But a larger table would have failed.

One last comment on a special situation, and that is when you have TEXT_IN_ROW option turned on anticipating that most of your text will fit in an 8k page.  The allocation locks for a row contained in a single page will be released immediately.  The allocation locks for all rows that span more than one page will also be released immediately unless it crosses an extent boundary.  Any multi-page inserts that cross an extent boundary will need a new allocation created and it’s lock will be held until the end of the transaction.   

SQL 2008:

This locking behavior does not change in SQL 2008.  Text/image/varchar(max) fields that cross extent boundaries will cause the extent locks to be held until the transaction is complete. However there are more solutions in SQL 2008.  Under the right circumstances you can get a large insert command to be minimally logged, which was our original goal for using the SELECT INTO. 

In SQL 2008 any insert can be minimally logged if you have the database in bulk-logged or simple recovery mode.  The table must not be replicated and you must use the TABLOCK command.  So the solution for my task would be to first INSERT newtable WITH (TABLOCK) SELECT FROM sourcetable WHERE (range 1 goes here).  Then repeat the INSERT with different ranges until I am done.  This would achieve minimal logging and not hold as many extent locks since they would be released after each batch.  NOTE:  The new WITH (TABLOCK) syntax does not work in CTP6 but should be completed by the final release.

Thanks to Srikumar Rangarajan from the SQL Product Team for helping me dive deeper into this subject and to Lubor Kollar for showing me the new WITH (TABLOCK) syntax in SQL 2008.

Kevin Cox

Backup More Than 1GB per Second Using SQL2008 Backup Compression

Backup Compression in SQL2008 performs very well and it is easy to use. You could either use “With Compression” along with the “Backup” command or simply enable default backup compression through sp_configure by setting ‘backup compression default’ value to 1. It is very convenient for backups of large databases in our customer lab environment.

A few things you could do to make the backup compression perform better. The hardware we used is 32way HP Superdome and StorageWorks XP12000 disk array.

· Test to find the ideal number of backup devices to use

First database we backed up is 1.27TB in size with 12 database files (on 12 LUNs). The second database is 2.95TB in size with 76 database files across 45 LUNs. We tested backup using different number of backup files (8, 16, 26 and 32), and 16 backup devices demonstrate best performance in our environment.

The following table shows database sizes, backup execution times and total size of all backup files. The database is not page or row compressed and the backup devices are local.

· Backup over the network (8 x 1G Ethernet, 2 files per NIC)

1. Jumbo Frame helps - we used 9014.
2. Set Buffer Count = 512

The backup volumes are a pair of 500GB SATA drives 7200 RPM configured as RAID 1 each. connected via 4 controllers in 16 disk shelfs.

In our test, 1.27TB is backed up over the network in 24.5 minutes (862MB/sec).

Backup operation is IO intensive. Backup compression writes less pages to disk comparing to uncompressed backup, as long as you system is not bottlenecked on CPU, backup compression should executes faster than backup without compression.

Hope you will enjoy this new SQL feature and getting even better backup performance.

Lindsey Allen, Thomas Grohser

SSIS 2008 - world record ETL performance

Using SQL Server 2008 and SSIS 2008 the SQL Server Performance team working with Unisys and SQL CAT have shown SSIS scaling to world record performance!  We loaded 1.18 TB in under 30 minutes.

You can find more information here: http://blogs.msdn.com/sqlperf/archive/2008/02/27/etl-world-record.aspx  Also watch for a paper from the performance team about how this was achieved and lessons learned in this exercise.

In addition, the SQL CAT team have been doing work on fast data loads using bulk inserts.  Watch sqlcat.com for TechNotes and best practices learned from this exercise.

Now is time to try SQL 2008

SQL Server 2008 is just around the corner. SQLCAT has been working with our customers and partners implementing pre-release builds. We have a lot good lessons learned to share, here are some of the upcoming best practices papers:

· Index Defragmentation Best Practices

· Best Practices for using Resource Governor with a mixed (OLTP and Reporting) workload

· Data Compression: Design and Implementation Best Practices, Performance Impact

· XEvents Toolkit

· SQL 2008 Usage-Based Optimization Efficacy (subject to name change)

· Scale Out Querying with Analysis Services 2008

· Best practices for Data Warehousing in SQL Server 2008

The latest version community technology preview (CTP6) includes many exciting new features like filtered indexes, sparse columns, data compression, Microsoft sync, integrated full text search, Report rendering in Microsoft Word, Data visualization enhancement and the first feature pack. Now is good time to see it yourself! Go here for instruction on how to download.

SQLCAT Toolbox

SQLCAT members have published several tools on Codeplex and other places. They are all consolidated here at SQLCAT.com.

http://sqlcat.com/toolbox/default.aspx

Introducing SQLCAT.com

The SQL Customer Advisory Team has put the final touches on the site http://sqlcat.com/ which is our new hub for all things that are part of the SQLCAT!  Our new site is a portal to all SQLCAT resources including:

• Top 10 Lists: Summary list of Best Practices and Recommendations
• Technical Notes: Deep level technical short papers
• Technical Spotlights: Technical end-to-end customer case studies
• Searchable and Tagger friendly; post comments and provide feedback!
• Search all of our SQLCAT Best Practices Whitepapers
• Easily find our SQLCAT Blogs and other materials

The site is regularly updated with deep technical information on enterprise SQL implementations.

New Partition Management Tool Available on Codeplex

As anyone working with partitioned tables knows by now, the process of creating stand-alone ‘staging’ tables to support switching  data into or out of a partition can be a management challenge, especially if the design of the partition table or its indices change over time.   A staging table for new data to be switched-in must include check constraints that match the partition boundaries of the target, and include all columns, constraints and indexes appearing in the partition table.   Often, the code to create the staging tables resides in a set of manually created management scripts that must remain carefully synchronized to any changes made in the partition table design.  When columns are added, column characteristics change (such as nullability or length) or index definitions are tweaked, the management script creating the staging tables must also be updated.     If management scripts fall out of synch, often we discover it only while in the middle of a critical month-end partition switch process, raising the risk of unplanned extension or delay of the maintenance window.

I’ve developed a simple, shared source Partition Management Tool for SQL Server 2005 to make the process of creating the staging tables for partition management nearly foolproof.  At runtime, the definition of all columns, indexes, and constraints for the partition table are read using the SMO object model, and a perfect clone table matching a specific partition is created. 

The tool also provides an option for creating a staging table without indexes, and then for building indexes later after the table is populated – for applications that prefer to bulk load into a new partition without indexes present.  It also includes a quick, transparent method to very quickly and simply clear the contents of a single partition within a partitioned table – a task that you may want to perform when replacing the complete contents of one partition.

Also rather than requiring you to have knowledge of the specific partition number that you are referencing, you may indicate the appropriate partition by passing a value of the partitioning column that falls within the desired partition range when invoking the tool. 

The Partition Management Tool runs from the command line, so unfortunately it cannot be used from within SQL Server stored procedures or batches.  That’s because SMO-based CLR routines cannot run in-proc in SQL Server 2005.  But the tool can be invoked from within SSIS (as an Execute Process task), so that runtime-dependent package variables can be integrated and the script can become part of an automated sliding window scenario orchestrated by SSIS.  When invoking from SSIS, the command line arguments of the tool can be populated by expressions within SSIS.

You can download the tool and associated C# source code from SQL Server 2005 Partition Management Tool . 

Stuart Ozer

FoxPro to SQL Server migration experience

At first I was going to write a detailed white paper about the conversion effort.  But someone has already done it.  There is a very good guide at:

http://nationalcom.com/home/download/Conversion-VFP-SQLServer.pdf

Since FoxPro is nearing its end of life, the company I was working with needed to convert the system to newer technology.  They chose Microsoft SQL Server and Microsoft .NET.  There was over 1 terabyte of data being processed in FoxPro.

The project was fairly easy as far as the data conversion goes.  There were a few decisions to make and one thing we learned about the difference in the NUMERIC data type between FoxPro and SQL Server.

The end result is pretty exciting.  Some of the processing jobs that took over 1 day in FoxPro now take 25 minutes in SQL Server.  The improvement is due to several facts:  1) The FoxPro code was doing row-at-a-time processing and the Transact-SQL code was written to do set processing;  2) FoxPro has a file limit of 2GB and with over 1TB of total data there were almost 1000 files to open and read through.

The most difficult part of the project was taking years worth of FoxPro code from different developers.  The code had started with DOS FoxPro and had been converted to Windows FoxPro and upgraded by different people with different coding styles over the years.  And as usual, the documentation was the code. J 

Here are the notes from the data conversion effort.  I’m only covering the data types that we had in our schema, which was not a very wide range.  If you have more data types they are most likely covered in the guides noted above.

1.       If you have MEMO data type in FoxPro you should use VARCHAR(MAX) in SQL Server.  We could have used TEXT but we know that is being phased out in some future version in favor of VARCHAR(MAX).  And VARCHAR(MAX) has better performance.

2.       There are many DATE types in FoxPro.  The decision was pretty easy to convert them all to DATETIME in SQL Server 2005.  In SQL Server 2008 there are more DATE types so you will have to study which FoxPro types map to the new SQL Server 2008 types.  DATETIME data was exported in character format using YYYYMMDDHHMMSS. 

3.       In FoxPro NUMERIC(5,2) allows a range of -99999 to 99999.  In SQL Server using the same precision the range is -999.99 to 999.99.  This was the only data type in our schema that needed to be altered at the data type level.  So in SQL Server we had to use NUMERIC (7,2) to get the same range.

4.       FoxPro LOGICAL fields were either exported as T/F or 0/1.  They either went into a BIT data type in SQL Server or a TINYINT.  The TINYINT was chosen if we wanted to do any calculations on the field in the future; i.e. SUM, AVG, etc.

Not knowing much about SQL Server, the database guys made a pretty good choice to dump the data to flat files in character mode, then used a simple Transact-SQL script to loop through the files and import them using the BULK INSERT command.   A quote from one of the lead developers on the project:  “We knew about Integration Services and could have used that but with our lack of experience with this tool we decided to use T-SQL scripts instead”.   The scripts and data conversion took one person about 3 weeks to write and tune.  This same person converted most of the other server side processing scripts to Transact-SQL in about 2 months.

Since FoxPro has a limit of 2GB file size and there was over 1TB of data in total, there were almost 1,000 files to convert.  Everything was exported from FoxPro as character using a tab delimiter.  It was determined that this was a safe delimiter after having to go through the memo fields to make sure there were no tab characters embedded. 

Just as a side note, the FoxPro screens were all rewritten and reengineered using ASP.NET and C#.  I will let someone else write about that experience if they wish. 

Thanks to Spyros Christodoulou and Petros Hadjigeorgis from Nielsen EMEA for their assistance in writing this blog and for making the project successful.

 kevin cox

New Best Practices Whitepaper published: "Best Practices for Migrating Non-Unicode Data Types to Unicode"

The database system in a company that is growing internationally must support multilingual characters in tables with Unicode data types. Existing databases that support only non-Unicode information must be migrated from non-Unicode data type to Unicode data type. This paper summarizes best practices for migrating database systems from non-Unicode to Unicode data type.

http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/SQL_bestpract_MigrationToUnicode.docx

SQL Server 2005 Best Practices Analyzer (January 2008) -- Now Available!

It's a new year and, with it, we come bearing gifts!  We have a pretty significant update to SQL Server 2005 Best Practices Analyzer.  It contains many new and updated rules for Analysis Services, a few important rules for the Relational Engine, a couple bug fixes for the UI and command line tools.  And all of these rules have rich documentation telling you what need to know.

New Rules

Analysis Services

In the last release, only a few rules for Analysis Services would be displayed.  These were mainly around security.  With this release, we detect potential issues with the design of your cubes.  If you have taken a look at SQL Server 2008, you’ll see that these are the same warnings you’ll find in Business Intelligence Development Studio.  Thus, the recommendations from SQL BPA will be perfectly in sync with the recommendations from SQL Server 2008.

Relational Engine

We have worked closely with CSS to identify a number of common issues that customers have been facing.  In SQL Server 2005 Service Pack 2, we started logging events regarding extensive amount of memory being paged out.  With this update of BPA, it will check to see if this happened and let you know if can determine the root cause.

Documentation

The documentation is what makes these rules worthwhile.  Each new rule has a breakdown of:

• How it was detected
• Why it’s important
• What you should do about it
• Links for more information

Not only will you be able to solve problems with your SQL instances more efficiently, you'll have a very targeted list of reading material to teach you why it's important.

Future of BPA

We will continue to release periodic updates to the rules for SQL Server 2005 BPA.  We will also fix any major bugs reported by customers.  However, our focus for development is on the next major version.  Our team is hard at work to incorporate BPA’s functionality into the core product for its debut in SQL Server 2008.  If you’d like to see how this works, download the latest CTP of SQL Server 2008 or search the web for Declarative Management Framework.

If you have any recommendations for rules, you can submit them to http://connect.microsoft.com/SQLServer/.

Latest Updates

The January 2008 package contains:

• BPA UI and command line tools
• Rules (previous rules + ~60 new rules)
• Rich documentation

Check out the latest release here: http://www.microsoft.com/downloads/details.aspx?FamilyId=da0531e4-e94c-4991-82fa-f0e3fbd05e63.

(Note: January 2008 is version 9.00.1007.00.  The text on the page may take a while to update.)

Paul A. Mestemaker II
Program Manager
SQLCAT – Best Practices

Querying Dimension Members In Analysis Services

Many Analysis Services applications query members to populate a dropdown or find those that match some criteria – starting or ending with a string, existing with other members, having a member property with a certain value, whatever. For example, say I have a bird hierarchy and I want to find all birds with the expression “eagle” somewhere in the member name. I could do something like this:

Select filter(birds.members, instr(1, Birds.currentmember.name, "eagle") > 0) on columns from [birds]

But this can lead to poor performance in some circumstances – particularly in ROLAP cubes or cubes with expensive calculations.

Why? It is because this query asks for and returns cell data as well. A query that asks for members on columns also returns data in the cells at each column position. This might be expensive. (And I’ve run into situations where it just destroys performance).  So two suggestions:

a)      For you application builders querying only for members either use the schema rowsets (see the member schema) or send queries that explicitly request an empty set on the first axis; ie, something like this:

Select {] on columns, filter(birds.members, instr(1, Birds.currentmember.name, "eagle") > 0) on rows from [birds]

                This way, no cell data is evaluated or returned.

b)      For those you building cubes relying on applications that aren’t following the advice above and you’ve determined that performance is a problem because of unnecessary cell evaluation, create an invisible calculated member with a null expression and make it the default measure. (And be sure to test!)

DB Autoshrink option may introduce high fragmentation

Issue

We have seeing some strange behavior when rebuilding indexes. 

·         Total Fragmentation prior to rebuild is 99%.

·         You rebuild the index. (using both the alter index and the dbcc dbreindex)

·         Fragmentation is at 0%

·         You recheck fragmentation about a minute later.

·         Fragmentation is at 99% again.

·         You have verified that there are no DML's being done on that table.

Analysis

We noticed that the database has “Auto Shrink” enabled.

Status

After we disabled the auto shrink, it’s working fine.

New Best Practices Whitepaper published: "Database Mirroring and Log Shipping Working Together"

Log shipping and database mirroring can work together to provide solutions for high availability and disaster recovery. You can convert an existing log shipping configuration to a database mirroring configuration. Although Microsoft only supports a single mirror database, you can set up log shipping from a database mirroring pair to additional servers, to provide multiple failover sites. Moreover, you can easily switch which pair of instances is using database mirroring and which instances are configured with log shipping.

New Best Practices Articles Published - Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques