Lara's Blog

Moving Day...

2007-08-16T02:34:00Z

Thanks all for reading my intermittent missives on SQL Server. I am in the process of wrapping up wtih Digineer, which means wrapping up this blog. I will continue to blog with www.sqlblog.com, where I have been cross-posting for the past month.

Smart Index Defragmentation for an ONLINE World

2007-07-30T13:48:00Z

One of the many reasons I why I love consulting is the constant evolution and challenge. You may have something that works “just fine” until a new customer simple states “This is great. Can you make it do XYZ too?” I love those “could you just” statements, because they challenge the status quo and help you create some really meaningful solutions.

Recently I have been engaged with a customer who was migrating from SQL Server 7.0 to SQL Server 2005 Enterprise Edition for a high profile web application requiring high availability. They have large tables, and will be embracing partitioning in the near future. At this time, they cannot sustain the downtime necessary to address index fragmentation, and wanted to defragment their indexes ONLINE (Enterprise Edition feature which significantly reduces downtime when rebuilding indexes). In the past, I had developed a centralized “smart reindex” procedure but this did not account for ONLINE index processing. Available for download on this blog is an update to this procedure which will accommodate addressing index fragmentation online and/or offline. This is a single procedure which should be stored centrally. It can be executed against any user database in an instance. I will also include the SQL Server 2005 Standard Edition procedure which will not offer an option to execute using ONLINE.

I will not go into a full discussion of how SQL Server rebuilds an index ONLINE, as this is discussed in Books Online. I will discuss some of the nuances and caveats of building indexes ONLINE that directly relate to the attached procedure.

First, an index cannot be built ONLINE if it contains LOB data (image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml). For a clustered index, you cannot rebuild the clustered index ONLINE if any columns exist in the table with these data types. For a non-clustered index, you cannot rebuild the index ONLINE if the index contains any columns with these data types.

Second, a single partition of an index with multiple partitions cannot be rebuilt ONLINE. You can REORGANIZE a single index partition (which is by nature ONLINE), but you cannot REBUILD a single index partition ONLINE. You can rebuild an entire index (all partitions) ONLINE.

These important rules are handled in the attached procedure with the following logic:

The user specifies @online= ‘ON’ and @fullprocess = ‘ON’

An index partition from an index with multiple partitions will be processed with REORGANIZE

An index partition from an index with a single partition will be processed with REBUILD WITH(ONLINE=ON) if the index does not contain columns which of a data type not supported by ONLINE index processing.

An index partition from an index with a single partition which contains a column with a data type not supported by ONLINE index processing will be processed with REORGANIZE or REBUILD (OFFLINE) depending on the level of fragmentation.

The user specifies @online= ‘ON’ and @fullprocess = ‘OFF’

An index partition from an index with multiple partitions will be processed with REORGANIZE.

An index partition from an index which contains a column with a data type not supported by ONLINE index processing will not be processed.

The user specifies @online= ‘OFF’ and @fullprocess = ‘ON’

All indexes are processed with REORGANIZE or REBUILD (OFFLINE) depending on the level of fragmentation.

The user specifies @online= ‘OFF’ and @fullprocess = ‘OFF’

No indexes are processed.

This procedure will not support the capability to process an index with multiple partitions ONLINE. Remember, if an index has multiple partitions you can REORGANIZE each partition or you can REBUILD ONLINE the entire index (all partitions). This process assumes that REORGANIZE is acceptable for index partitions meeting these criteria. Since this is inherently ONLINE, it may be acceptable. The logic can be altered to ignore these indexes, if this is not acceptable. Since REORGANIZE may not be as complete as REBUILD, or may require more time to eliminate fragmentation, the user should know that they can specify @online= ‘OFF’ and @fullprocess = ‘ON’ during an acceptable maintenance window to address the fragmentation.

Memory Pressure on 64 Bit SQL Server 2005

2007-07-16T03:07:00Z

Do you remember the days when you first started learning about 64 bit capabilities and dreaming of the day when you were no longer confined by the barriers of the 32 bit virtual address space? Those ideas produced dreams of a truly capable scale up solution. Yes, 64 bit has been a blessing for many of our SQL Server databases. But as is often the case, there are some drawbacks. Such is the case for some 64 bit SQL Server 2005 database applications.

In the 32 bit OS, you have a total of 4 GB of virtual address space, with 2-3 GB of virtual address space available for the applications (depending on how you configured your boot.ini file). The 32 bit version of SQL Server is very busy managing the procedure cache, user cache, log cache, extended stored procedures, binaries and buffer (data) cache within the limited confines of the 2-3 GB virtual address space. When you enable AWE on 32 bit SQL Server, you are extending the buffer (data) cache, and not able to address the available memory for any of these other items – including the procedure cache. SQL Server must still manage the procedure cache within the constraints of the 2-3 GB of available virtual address space.

Now let’s consider 64 bit. These confines are history – and we can now enjoy the capabilities of a very large user cache, very large log cache, and as many are discovering a very large procedure cache. As was the case with one customer this week, the procedure cache was using over half the available RAM for the instance. Note: This happens in specific scenarios. I am not saying that 64 bit is bad, only that in some scenarios it is important to understand where you need to adjust your management of a database.

Consider an application which executes a large number of ad hoc statements and dynamic SQL. When a statement is executed, SQL Server searches the procedure cache to see if a plan exists for the statement. If one does not exist, SQL Server will save an execution plan in the procedure cache. This can quickly lead to a large procedure cache. These statements may never be used again, but SQL Server cannot differentiate between a recently executed ad hoc statement that may be reused and one that will not ever be reused. If the plan is considered expensive – or one which requires a lot of work to generate – SQL Server will not age this plan out very quickly. Since the 64 bit world has the luxury of larger volumes of RAM in which to manage the procedure cache, SQL Server may prefer to keep these large expensive plans in cache.

Why is this a problem? For starters, when SQL Server is under memory pressure it may instead flush pages from our valuable buffer (data) cache instead of flushing what it determines to be expensive plans in the procedure cache. A larger procedure cache may also impact the time for SQL Server to search the procedure cache to determine if a plan exists. When the procedure cache is full of meaningful and reusable plans, perhaps this is time well spent. When your procedure cache is full of plans which will never be reused, it is a waste of resources and a waste of time.

Currently, there are a few options to manage the problem if you are not able to fix the code. Of course, fixing the code is the best option. Reduce or eliminate dynamically built statements in application code. Reduce or eliminate ad hoc statements. When this is not an option:

On regular intervals execute DBCC FREEPROCCACHE.

This will flush the procedure cache. This is the best option when you have exhausted all other options. Flushing the procedure cache may cause your database application to slow down as SQL Server compiles and stores execution plans for procedures and statements.

Use Forced Parameterization on the databases which are causing this issue.

Forced parameterization will replace any constants in an eligible statement with a variable when the query is compiled. This is particularly suited when your application builds the same statement with differing constants. Consider the following statements:

SELECT * FROM Production.Product WHERE MakeFlag = 1 AND ListPrice > 0

SELECT * FROM Production.Product WHERE MakeFlag = 0 AND ListPrice > 1

A single parameterized statement is stored in the procedure cache:

(@0 int,@1 int)select * from Production . Product where MakeFlag = @0 and ListPrice > @1

In one customer situation, the procedure cache contained tens of thousands of large duplicate statements with differing constants in the where clause. Forced parameterization significantly reduced the number of statements stored in the procedure cache.

This option is best suited when there are a large number of statements that are regularly executed with varying constants in the WHERE clause. There are tradeoffs with this method, and I would strongly recommend reviewing the requirements and risks in BOL.

Use a Plan Guide to Force Parameterization at the Statement Level

This is the ideal option when you know that there are only a few statements which are causing the procedure cache to grow. This will be the least intrusive way to force parameterization. This option will allow you to still maintain a procedure cache and enjoy execution plan reuse. This may not be a reasonable solution when you have hundreds of statements, as you will need to build a plan guide for each statement.

New MVP!

2007-07-02T13:26:00Z

Wow! I received a great email from Microsoft yesterday with a subject "Congratulations! You have received the Microsoft MVP Award". Exciting, humbling, inspiring, and motivating. Wow – did I say that already?

The SQL Server MVPs are part of an extraordinary community which has promoted SQL Server education for all of us. The next year looks to be another great year for SQL Server, and I look forward to being part of this incredible community and continuting to improve all of our SQL Server knowledge.

Wow!

After Tech Ed... Whew! What a week!

2007-06-10T13:50:00Z

Back from Orlando, exhausted and excited! I arrived last night, and slept for 11 hours straight! It was an incredible week with a lot of product news (more on that below), insightful discussions, and a lot of catching up with friends from afar (and friends from homeJ). Of course, there was also an occasional roller coasterJ In fact, the Dueling Dragons ride at Universal Studios actually made me scream!

As you probably heard, the next release of SQL Server has officially been named SQL Server 2008 and the first CTP is available for download. In fact, you can read about the next release on Microsoft’s website. You can download webcasts, information briefs, and the latest CTP from this site. Do note: To download the CTP you must register. It is easy to do, and only takes a minute. If you don’t register you will receive an error when you try to download the CTP.

This next release promises some great enhancements for the developer, the DBA and it is a boon for business intelligence. I will be detailing some of these new features and changes in the next few weeks. A Product Overview document is available which provides a nice level of information.

As for me... Now that Tech Ed is over, I am off to install the latest CTP to look over some of the new capabilities. More to come soon!

In Anticipation of Tech-Ed 2007

2007-06-01T19:02:00Z

Well, I am making my final adjustments to demos and slides as I prepare to leave for Orlando. Tech-Ed begins Monday, and it promises to be another big year!

If you will be at Tech-Ed this year, be sure to stop over to say hi. I will be at the Ask The Experts area on Wednesday and Thursday (and probably spend time there throughout the rest of the week as well). My presentations are scheduled as follows:

DAT311 - High Performance Data Encryption with Microsoft SQL Server 2005

Thursday - 6/7/2007

4:30PM-5:45PM

N310 E

DAT205 - Choosing the Right Edition of Microsoft SQL Server 2005

Friday - 6/8/2007

9:00AM-10:15AM

S310 E

I am personally looking forward to a number of sessions, particularly those detailing Katmai, partitioning (with Kalen Delaney), advanced index strategies (with Kim Tripp), and varying BI presentations. I am also hoping to reconnect with friends and colleagues - and looking forward to the party at Universal Studios on Thursday where I will be on every roller coaster possible. I'll try to blog a bit over the week to talk about all of the great stuff happening in Orlando.

It looks like it will be scorching hot, so be sure to bring your swimsuit if you are attending this year!

A couple of new resources

2007-03-04T23:12:00Z

Wow, it has been a long time since I posted to my blog! Well, it isn’t because I have been lazy. I have actually been quite busy writing the past couple of months, although my blog has not been benefiting from this flurry of writing activity.

My team at Digineer and I completed a whitepaper for Microsoft, which was posted a couple of weeks ago. Empowering Enterprise Solutions with SQL Server 2005 Enterprise Edition details each feature available in SQL Server 2005 Enterprise Edition. If you ever questioned the value of SQL Server 2005 Enterprise Edition, this paper will help you better understand the features that may help you improve the performance, scalability, and capabilities of your database applications.

The second item I will selflessly promote is the upcoming Expert SQL Server 2005 Development book written by Adam Mechanic. I contributed a chapter on encryption, which the first time I have written a chapter for a book. If you have never written a technical book, I can tell you that it is a lot of work. I truly realize the effort that Adam put into the book – and others who choose to write technical books. It looks to be a great SQL Server 2005 reference, and is scheduled for release in May.

Following these two accomplishments, I retreated to Disney to bask in the sun with my family. I am back, and am hoping to be more diligent on posting. I have encountered numerous topics in the past couple of months, so watch for some interesting tidbits in the next few weeks.

PASS Summit 2006 Session: Securing Your Data with SQL Server 2005 Encryption

2006-11-16T00:16:00Z

Thank to all who attended our session on securing data using SQL Server 2005. As promised, attached are the demo scripts and the presentation.

I included the script used to represent the EncryptByPassPhrase function where the text is exposed in Profiler in some circumstances. I also included my script which helps estimate the increase in table size when encrypting data. Note: This assumes one column is encrypted. You may need to play with this a bit more to add multiple encrypted columns in the table.

Have fun encrypting!

Maximum Degree of Parallelism and Hyperthreading

2006-11-13T16:13:00Z

For those who are challenged with deciphering how to configure the max degree of parallelism on a server where hyperthreading is enabled, the max degree of parallelism configuration is based on physical processors and not logical processors.

I recently made a recommendation to a customer to reduce or disable parallelism in the SQL Server OLTP environment. If you know me at all, you know that I am not a big fan of parallelism and regularly ask people to disable the feature in their OLTP environments. Many customers are afraid to disable altogether, so I ask that they at least reduce the setting to ½ the available physical processors.

This customer had a server with 4 physical CPUs, with hyperthreading enabled. The max degree of parallelism was set to 4, which they interpreted to meaning a maximum of half of the available processors are used for any given query that requires (see the squishy language in the following link http://support.microsoft.com/default.aspx/kb/322385). I verified with a source at Microsoft, and this setting is based on physical processors. If you have 4 physical CPUs and have set the max degree of parallelism to 4, you are suggesting that all available processors may be used (if necessary) to complete a statement. If you are hoping to limit parallelism to using ½ the available processors, this particular server would require the configuration set to 2.

I would be remiss if I didn’t add another warning: Hyperthreading should not be enabled on a SQL Server server unless it has been proven to improve performance. Hyperthreading can cause performance issues in some cases (rather than rewrite an already well-written summary of the issue, please follow this link: http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx). I often work in environments where hyperthreading is enabled by default. This feature should be disabled by default and only enabled after tests prove the feature benefits SQL Server under a full production load.

PASSMN Meeting on October 17

2006-10-08T20:02:00Z

What fun! A pure database topic and a pure BI topic. Should be a fun evening for those who are challenged to settle in one camp or another.

Topic	Advance SQL Backup solution at nth degree
Details	Do you really have your backup solutions tuned to its nth degree? Do you really guarantee that full, filegroup, SAN and your transaction backup is the most tuned and best backup solution you could provide to your client or company? No matter whatever your backup solution is, have you tried some simple tricks to possibly reduce your backup maintenance window as much as 50%? Well if your need is to reduce your maintenance window by 99.999% what should be your final solution? CDP, Snapshot or same old SAN BCV split, let us find it together.
Speaker	Kahar Muhammad
Topic	Analysis Services, ProClarity, Business Scorecard Manager and Beyond
Details	In a 2006 survey conducted by Computerworld, business intelligence was identified as one of the top three areas for IT growth in the year 2010. This puts one of Microsoft’s hottest products at the forefront of this trend, SQL Server 2005. The presentation will first show how Business Scorecard Manager can highlight business drivers and be easily customized for various audiences. Next, it will highlight ProClarity’s capabilities in advanced data visualization. Finally, the presentation will dive into SQL Server Analysis Services. As you may know, in recognition of the growing market for business intelligence, SQL Server Analysis Services received a major overhaul in SQL Server 2005. Using this analysis engine with ProClarity and Business Scorecard Manager can bring tremendous value to an organization. Microsoft’s business intelligence roadmap has these three products officially merging next year with the planned release of Office PerformancePoint Server 2007.
Speaker	Jason Haley
Date	10/17/2006 4:30:00 PM
Agenda	4:30 pm - 5:00 pm Meet, greet and eat 5:00 pm - 5:15 pm PASSMN Annoucements 5:15 pm - 7:15 pm Presentations

Register for this Event at www.passmn.org
Location	Microsoft Office, 8300 Normandale Center Drive, 9th Floor

Launch of Visual Studio Team Edition for Database Professionals

2006-10-05T15:26:00Z

Sign up now for the Microsoft Launch of Visual Studio Team Systems for Database Professionals. http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032310684&Culture=en-US

It looks like a rousing lineup of presentations.

Joining Forces: Assimilating Data Experts into the Development Team and Process

Database Professionals have always been an integral part of the software development team. Now, not only is Microsoft providing a tailor-made Visual Studio Team Edition role-based experience, but also access to the full benefits of Visual Studio Team Foundation Server. Come learn how Visual Studio Team Edition for Database Professionals improves collaboration across the full development team and increases application quality with better control over database changes and automated database testing.

Sessions:

Take Control of Database Change
-Take control of your database schema by creating a baseline version of the database with an off-line source controlled project.
-Streamline changes to your database with tools that allow you automatically cascade changes throughout the database schema in a controlled and consistent manner.
-Reduce the risk involved with changing database schema by comparing the source controlled version against test and production systems and automating the creation of change scripts.

Automate Database Testing to Improve Quality
-Create full unit tests for your database schema that can be run independently or in combination with Visual Studio 2005 Team Suite to provide a comprehensive set of tests for the application and database tiers.
-Author tests in either T-SQL or managed code with extensible unit test functionality and a new test editor.

Panel Discussion:
Open discussion to panel of MS + Partner representatives.

AGENDA

8:30am - 9:30 am Joining Forces: Assimilating Data Experts into the Development Team and Process

9:30am - 10:30am Taking Control of Database Change

10:45am - 11:00am Break

11:00am - 12:00pm Automate Database Testing to Improve Quality

12:15pm - 1:00pm Lunch Panel Discussion

I don't have any information on who is presenting or who is on the lunch panel. When this information is available I will post to my blog.

If you haven't looked at the tool yet, have a peak: http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/default.aspx

August PASSMN Meeting

2006-08-22T01:34:00Z

Upcoming PASSMN meeting information below. My esteemed colleague Stan Sajous will be co-presenting with me on Encryption in SQL Server 2005. The topic focuses specifically on resolving performance issues in a scalable encryption scenario. Hope to see you there!

Topic	Database Documentation on a Budget
Details	Need to create database documentation? Is your budget too small to purchase data modeling tools? See how you can create database documentation using tools you already own.
Speaker	Mike Matthews
About the Speaker	Mike Matthews has been working exclusively with Microsoft SQL Server since version 6.0 was released. Currently, he is a Senior SQL Server DBA at NCS Pearson. While well versed in the design, development and administration of SQL Server databases, he has a passion for query tuning.
Topic	Securing Your Data with SQL Server 2005 Encryption
Details	Following a year of scandals involving lost and stolen data, more companies are requiring encryption of sensitive data. SQL Server 2005 introduces database level encryption to protect your company's most valuable asset. In this session we will explore best practices for implementing encryption, while maintaining performance levels necessary for large scale SQL Server environments.
Speaker	Lara Rubbelke
About the Speaker	Lara Rubbelke is an Enterprise Consultant with Digineer, where she focuses on architecting, implementing and improving SQL Server solutions. Her expertise involves both OLTP and OLAP systems, ETL, and the Business Intelligence lifecycle. She is an active board member of the local PASS chapter and brings her passion for SQL Server to the community through technical presentations at local, regional and national conferences and user groups.
Date	9/19/2006 4:30:00 PM
Time	5:00 pm - 7:15 pm

Capacity	120
Agenda	4:30 pm - 5:00 pm Meet, greet and eat 5:00 pm - 5:15 pm PASSMN Annoucements 5:15 pm - 7:15 pm Presentations

Register for this Event at www.passmn.org
Location	Microsoft Office, 8300 Normandale Center Drive, 9th Floor

Great Post on SSIS Destinations + More Information

2006-08-21T03:15:00Z

One of my favorite blogs on SQL Server Integration Services (SSIS) is by Jamie Thompson. He does a tremendous job sharing tips, tricks, and lessons learned from working with SSIS. Recently he posted a blog on three of the destination adapters and the performance implications of using each (http://blogs.conchango.com/jamiethomson/archive/2006/08/14/4344.aspx). The blog was pretty timely, as I had been having this exact conversation with a client the previous week. I wanted to add a couple of additional thoughts on the material.

First, after reading the blog, one would ask oneself "Self, why would I ever use the OLE-DB Destination without FastLoad?" Well, that is a good question. Undoubtedly, this method is extremely slow. Reiterate, extremely slow. OK, slow compared to the counterparts "with FastLoad" or SQL Server Destination. If you run Profiler while loading into each type of destination, you will quickly see that the OLE-DB Destination without FastLoad inserts each row individually. Obviously, this is very slow.

OLE-DB Destinations with FastLoad cannot be set to redirect error rows. Consider a situation where you would want to trap error records in the destination - such as if a record violates a primary key constraint, or a domain constraint. If you want to redirect these rows and allow the load to succeed while trapping those bad records for assessment and possible reload at a later time, you CANNOT use FastLoad. This only leaves OLE-DB without FastLoad, since the SQL Server Destination does not support this behavior either.

So you may then ask yourself “Self, why would I choose to use the OLE-DB Destination with FastLoad over the SQL Server Destination?” Another good question. Well, to use the SQL Server Destination, the destination database must be on the same server on which you are executing the SQL Server Integration Services package. From my experience, you will not see a significant performance difference between the two solutions. I generally prefer the OLE-DB Destination simply because it gives you better flexibility on where you execute the package.

Smart Index Defrag/Reindex for a Consolidated SQL Server 2005 Environment

2006-08-17T03:15:00Z

(Update: An ONLINE version of this procedure is now available)

I am sure we all have our own version of a "smart" reindex script for SQL Server 2000. This is a script that assesses the current level of fragmentation, and only executes a reindex or indexdefrag if the fragmentation falls below a certain threshold. In the 2000 world I have always preferred using "smart" index maintenance scripts for several reasons, including:

Index defragmentation is a logged activity. By running a dbcc dbreindex on all indexes in your larger tables you can artificially inflate your transaction log.
Address your index fragmentation can lock pages or tables (depending on how you decide to defrag your indexes). If your index is not heavily fragmented, you are needlessly locking resources and decreasing concurrency.
Performing index maintenance on all indexes in a database can take much longer than necessary. With shrinking maintenance windows, this typically is not acceptable.

Well, SQL Server 2005 offers options to rebuild indexes without locking resources, or rebuild only a partition (although we are talking Enterprise features here). These are certainly improvements in index maintenance. Even so, we all need to continue to use "smart" index maintenance scripts. We should only address index fragmentation where it is necessary and not arbitrarily take the "easy" way out by running some sp_msforeachtable statement with a dbcc dbreindex parameter.

Attached is one that I have developed which is quite handy in a consolidated environment. In such an environment, one would prefer to have a single copy of a script and simply pass in a database name as a parameter to rebuild or reorganize indexes that are deemed "fragmented". I envision this type of environment would have a database that is designed for DBA scripts; I sometimes see people name these DBAStuff, DBAScripts, DBA, or the like.

The script accepts three parameters. The first parameter, @maxfrag, specifies the level of minimum level of fragmentation before the index must be rebuilt/reorganized. The parameter @maxdensity specifies the minimum average page fullness before the index must be rebuilt/reorganized. The final parameter @databasename allows you to specify which database you want to assess.

The beauty of this script is in the use of the new dynamic management view sys.dm_db_index_physical_stats (one of a few reasons that this script will not work in SQL Server 2000 or on a SQL Server 2005 database which has the compatibility mode set to 80). This dmv allows you to pass in a database id as a parameter and returns a slew of useful index information in a table format.

I hope that the attached gives you something from which to base your own scripts. Let me know your opinions, and if you find this practice useful and necessary.

Monitoring SQL Server Using SQL Server Business Intelligence

2006-06-15T01:28:00Z

Thanks to all who attended the presentation at TechEd and at PASSMN. For all who are interested, attached are the projects to support the Analysis Services cubes and Reporting Services reports demonstrated during the presentation.

Have fun!