Filtered Indexes Work-Around
Filed under: Performance & Tuning, SQL 2008, SQL Tips, Syndication
Recently, I needed to create a stored procedure that queried a rather large table. The table has a filtered index on a date column, and it covers the query. However, the Query Optimizer was not using the index, which was increasing the execution time (not to mention IO!) by at least 10x. This wasn’t the first time I’ve had the Optimizer fail to use a filtered index. Normally when this happens, I use a table hint to force the filtered index — after I verify that it is indeed faster, of course. However, since this was a stored procedure, I was receiving the following error message whenever I tried to execute the proc:
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
SQL Server would not allow me to execute the stored procedure using the filtered index hint. If I removed the hint, it executed, but it used a different, non-covering and far more expensive index. For those of you not familiar with this issue, allow me to illustrate the problem.
First, create a table to play with and populate it with some bogus data:
CREATE TABLE dbo.filteredIndexTest ( myID INT IDENTITY(1,3) , myDate SMALLDATETIME , myData CHAR(100) CONSTRAINT PK_filteredIndexTest PRIMARY KEY CLUSTERED(myID) ); SET NOCOUNT ON; DECLARE @DATE SMALLDATETIME = '2010-01-01'; WHILE @DATE < '2010-02-01' BEGIN INSERT INTO dbo.filteredIndexTest ( myDate , myData ) SELECT @DATE , 'Date: ' + CONVERT(VARCHAR(20), @DATE, 102); SET @DATE = DATEADD(MINUTE, 1, @DATE); END; SELECT COUNT(*) FROM dbo.filteredIndexTest;
It looks like this will generate 44,640 rows of test data… plenty enough for our purposes. Now, let’s create our filtered index and write a query that will use it:
CREATE NONCLUSTERED INDEX IX_filteredIndexTest_1 ON dbo.filteredIndexTest(myDate) Include (myData) WHERE myDate >= '2010-01-27'; SELECT DISTINCT myData FROM dbo.filteredIndexTest WHERE myDate >= '2010-01-28';
If you look at the execution plan for this query, you’ll notice that the Optimizer is using the filtered index. Perfect! Now let’s parameterize it.
DECLARE @myDate1 SMALLDATETIME = '2010-01-28'; SELECT DISTINCT myData FROM dbo.filteredIndexTest WHERE myDate >= @myDate1;
Uh oh. Looking at the execution plan, we see that SQL Server is no longer using the filtered index. Instead, it’s scanning the clustered index! Why is this? There’s actually a good explanation for it. The reason is that I could, in theory, pass a date to my parameter that fell outside of the filtered date range. If that’s the case, then SQL Server could not utilize the filtered index. Personally, I think it’s a bug and SQL Server should identify whether or not a filtered index could be used based on the actual value submitted, but… that’s a whole other blog post. 
So what can we do? Well, dynamic SQL may be able to help us out in this case. Let’s give it a go. First, let’s try parameterized dynamic SQL.
DECLARE @mySQL1 NVARCHAR(2000) , @myParam NVARCHAR(2000) = '@p_myDate2 smalldatetime' , @myDate2 SMALLDATETIME = '2010-01-28'; SET @mySQL1 = 'Select Distinct myData From dbo.filteredIndexTest Where myDate >= @p_myDate2'; EXECUTE SP_EXECUTESQL @mySQL1, @myParam, @p_myDate2 = @myDate2;
Looking at the execution plan, we see we’re still scanning on the clustered index. This is because the parameterized dynamic SQL resolves to be the exact same query as the one above it. Let’s try unparameterized SQL instead:
DECLARE @mySQL2 NVARCHAR(2000) , @myDate3 SMALLDATETIME = '2010-01-28'; SET @mySQL2 = 'Select Distinct myData From dbo.filteredIndexTest Where myDate >= ''' + CAST(@myDate3 AS VARCHAR(20)) + ''''; EXECUTE SP_EXECUTESQL @mySQL2; -- Drop Table dbo.filteredIndexTest;
Voila! We have a seek on our filtered index. Why? Because the statement resolves to be identical to our first query, where we hard-coded the date value in the WHERE clause.
Now, I want to stress this fact: you should always, ALWAYS use parameterized dynamic SQL whenever possible. Not only is it safer, but it’s also faster, because it can reuse cached plans. But sometimes you just cannot accomplish the same tasks with it. This is one of those times. If you do end up needing to use unparameterized dynamic SQL as a work-around, please make sure you’re validating your input, especially if you’re interfacing with any sort of external source.
There’s an even easier work-around for this problem that Dave (http://www.crappycoding.com) shared with me: recompile.
Adding “Option (Recompile)” to the end of your statements will force the Optimizer to re-evaluate which index will best meet the needs of your query every time the statement is executed. More importantly, it evaluates the plan based on the actual values passed to the parameter… just like in our hard-coded and dynamic SQL examples. Let’s see it in action:
DECLARE @myDate4 SMALLDATETIME = '2010-01-28'; SELECT DISTINCT myData FROM dbo.filteredIndexTest WHERE myDate >= @myDate4 OPTION (RECOMPILE); DECLARE @myDate5 SMALLDATETIME = '2010-01-20'; SELECT DISTINCT myData FROM dbo.filteredIndexTest WHERE myDate >= @myDate5 OPTION (RECOMPILE);
If we look at the execution plans for the 2 queries above, we see that the first query seeks on the filtered index, and the second query scans on the clustered index. This is because the second query cannot be satisfied with the filtered index because we initially limited our index to dates greater than or equal to 1/27/2010.
There are, of course, trade-offs associated with each approach, so use whichever one best meets your needs. Do you have another work-around for this issue? If so, please let me know.
Update:
Alex Kuznetsov (http://www.simple-talk.com/author/alex-kuznetsov/) shared this method too:
DECLARE @myDate1 SMALLDATETIME = '2010-01-28'; SELECT DISTINCT myData FROM dbo.filteredIndexTest WHERE myDate = @myDate1 AND myDate >= '2010-01-27';
Like the other examples, this will result in an index seek on the filtered index. Basically, by explicitly declaring the start date of your filter, you’re letting the Optimizer know that the filtered index can satisfy the request, regardless of the parameter value passed. Thanks for the tip, Alex!
Index Defrag Script Updates – Beta Testers Needed
Filed under: Miscellaneous, Performance & Tuning, SQL 2008, Syndication, T-SQL Scripts
Update: Wow! I’ve received a ton of responses to my request for beta testers. Thank you all! The SQL Community is really amazing. I’ll hopefully have the new version online in just a few days.
Over the last few months, I’ve received many great comments and suggestions regarding my Index Defrag Script v3.0. I’ve just recently had time to implement most of these suggestions, plus some other things that I thought would be useful.
Here’s some of what you can look forward to shortly:
- Probably the single most requested feature, the new version of the script allows you to set a time limit for index defrags.
- There’s now a static table for managing the status of index defrags. This way, when your time limit is reached, you can pick up where you left off the next day, without the need to rescan indexes.
- There’s now an option to prioritize defrags by range scan counts, fragmentation level, or page counts.
- For those using partitioning, there is now an option to exclude the right-most populated partition from defrags (in theory, the one you’re writing to in a sliding-window scenario).
- Options such as page count limits and SORT_IN_TEMPDB are now parameterized.
- I’ve enhanced error logging.
- … and more!
Right now, I’m looking for a few folks who are willing to beta test the script. If you’re interested, please send me an e-mail at michelle at sqlfool dot com with the editions of SQL Server you can test this on (i.e. 2005 Standard, 2008 Enterprise, etc.).
Thank you!
Replication Bug with Partitioned Tables
Filed under: Miscellaneous, SQL Tips, Syndication
Recently, we came across a bug in SQL Server 2005 on one of our production servers. Apparently, if you execute an ALTER TABLE statement on a replicated table with more than 128 partitions, the log reader will fail. A relatively obscure bug, I know. Microsoft has recognized this as a confirmed bug, but I couldn’t find it anywhere on the intertubes, thus the inspiration for this blog post. Microsoft’s official solution for this issue is to upgrade to SQL Server 2008.
For various reasons, we were unable to execute an upgrade at the time. And since this was a 2 terabyte database, we wanted to come up with a solution that wouldn’t involve reinitializing the entire publication. Our quick-fix while we were troubleshooting the issue was to create a linked server to the production box. Not ideal, I know, but it worked in a pinch and minimized exposure of the issue. Fortunately for us, we were able to solve the problem on the publication database pretty easily. All of the affected partition functions had empty partitions created several months in the future, so we simply merged any empty partition ranges for future dates. Our solution to our now-out-of-date subscribers was to apply static row filtering to any table with more than 100 million records. While this would introduce some overhead with the replication of these tables, it would allow us a much faster recovery time. We decided to use the start of the most recent hour as our filtering criteria, just to give us a “clean” filter, so we had to delete data from any table where we were going to apply the filter. After that, it was simply a matter of resuming replication.
All things considered, it took us a little over a day to recover from the issue. Most of that time was spent troubleshooting the problem and identifying a workable solution; actual execution of the changes was pretty quick. Moral of the story? Upgrade to SQL Server 2008.
Live Blogging: Women In Technology Luncheon
I was invited to live-blog for the Women In Technology (WIT) luncheon at the Summit on Tuesday. As was the case with all of my “live blogging,” I mostly updated Twitter with near-real-time updates, which I then attempt to transcribe into a blog post for later reference. So here follows that transcription.
11:49 AM PST
The room is filling up nicely! There’s a nice distribution of men and women in the room. This is great! I’m actually pleasantly surprised at the number of women at the Summit. If it weren’t for this luncheon, I probably wouldn’t have even noticed just how many SQL ladies there actually are.
12:00 PM PST
The WIT luncheon is underway! We start off with watching an energetic video displaying various types of women and men who each state “I am a technical woman” or “I support technical women.”
Rushabh Mehta, Executive VP of Finance for PASS, opens the WIT luncheon with the statement “PASS Supports Technical Women.” He then hands the floor over to Wendy Pastrick (@wendy_dance) for moderation of today’s panel.
Today’s panel is:
- Kathi Kellenberger (@auntkathi)
- Jessica Moss (@jessicammoss)
- Cathi Rodgveller (@IgniteGirls)
- Lynn Langit (@llangit)
12:10 PM PST
Kathi starts off with a great message: “I want to encourage women to enter technology, but I want to encourage boys too.” She started off as a pharmacist because “I was probably born 5 years too early” but later switched to technology. She makes less money but enjoys it more. Her daughter had to learn HTML for school and actually helped her get her first work in IT by showing her how to program. “I want both boys and girls to have the opportunity to discover lots of different things and to find what they love. And hopefully that’ll be technology.”
12:17 PM PST
The floor is then handed over to Jessica Moss, BI guru extraordinaire. She gives a great example of how influential a father can be in a young girl’s life: her father was the one who got her interested in technology and who encouraged her career. She says she never felt like she could *not* be technical because she was raised to believe she could do anything. She ends with a challenge for everyone at the Summit: talk to just one young woman and encourage her interest in technology.
12:23 PM PST
Cathi Rodgveller shares her background in education and how she started IGNITE (Inspiring Girls Now In Technology Evolution). The goal of IGNITE is to excite young women, minority races, and low-income youth, about technology and about technical careers. Rodgveller gives us a powerful message: “You can have an impact in your community. One [positive technology] event can change a young girl’s life.”
12:28 PM PST
Last, but certainly not least, Lynn Langit takes the floor. She starts off with a challenge to all audience members: tweet or text one person to say “I’m a technical women” or “I support technical women.” The room gets active while people are busy typing or texting, and Twitter is abuzz with various tweets and retweets. Lynn then takes the floor back and talks about her background and about her charity work. She mentions that every time someone buys one of her books, a donation is made to the MONA foundation. Langit also shares some of her experiences as a technical women: “I’m a developer evangelist. I’m often the only woman in the room, and I’M the one giving the presentation.”
12:35 PM PST
It’s now time for Q&A with the audience. I’ve also invited members of the Twitter community to send in their questions or comments, and we’ll do our best to get them answered. Following is a brief summary of the questions and answers provided:
Q: First up is a father of 2 teenage girls. He wants to know why WIT programs have continued to fall since 1985.
A: WIT is a low priority for schools. Schools have so many other priorities, and not enough time or funding, to address everything they need to. We need intervention from outside sources to stimulate change and ensure it’s being addressed. Rodgveller is working with her state Senator to try to enact change on the national level.
Moss mentions that studies show the top 2 issues for WIT are recruitment and retention. She also points out that middle school years are very formative and important for young women to foster their interest in technology. Rodgveller interjects that even high school is not “too late” to inspire young women.
Q: Another father asks, how can he remove stereotypes for children?
A: Parents are the best resources, period. Parents need to support their children at home and to let them know that stereotypes are negative and not okay. This includes not just gender issues, but also issues of race, religion, sexual orientation, etc.
Q: Today’s youth are concerned about the technical job market in US because of the prevalence of off-shores outsourcing. Is it still a good idea for young people to join today’s technical workforce, and how can we encourage them?
A: There are still plenty of opportunities in IT. In fact, one of the hottest trends today in technology is BI. The best way to ensure that your job is not outsourced is to stay relevant and keep up with the newest technology; those are not the jobs that are outsourced overseas.
Q: In the South, there are still lots of stereotypes. For instance, women frequently are not hired by companies for technical positions. Comments like “We can’t hire a woman for that job because it’s too valuable; what if she gets pregnant?” are still made. What can be done about this?
A: One of the executives from CA, the sponsor of the WIT luncheon, takes the stage to answer. He says in no uncertain terms that, at his company, those individuals making the disparaging remarks would be terminated. He says that the type of attitude described has to come from the top down, and the company is limiting itself by not hiring women. He ends with a message for employees to not tolerate discrimination and to go to HR whenever they see it happening.
Q: What can parents do to help WIT?
A: Parents are the greatest resource kids have. Parents set the attitude for their kids; if your attitude is positive, it will encourage your daughter to try new things and will open her up to opportunity whenever it presents itself. Also, parents need to raise the issue with schools, i.e. through PTA meetings, to make them realize that it’s important to you and it’s important for your children.
Moss: “But at the end of the day, it is the parent’s responsibility to expose your children to as much technology and as many experiences as possible.”
Kellenberger: “It is up to the parents to break stereotypes, for jobs, gender, race, etc. It’s the parent’s attitude that makes the difference.”
So that’s all I have for the Women In Technology luncheon. There was a lot of great content and some very positive messages from our panel. For more information on this topic, please check out the following resources:
- Steve Jone’s musings on the WIT luncheon
- What can men do? (to help WIT)
- IGNITE
Live Blogging: Keynote at PASS, Day 3
Today is the 3rd and final day of keynotes at the PASS Summit. Following is highlights of the keynotes. During the keynote, refresh often for updates!
8:36 AM PST
Keynote kicks off with Don’t Stop Believing by Journey. Awesome.
8:38 AM PST
PASS VP of Marketing Bill Graziano just takes the stage. He promises the shortest keynote of the conference, and appears to deliver on it. First up are Board announcements.
Outgoing Board Members are:
- Greg Low
- Pat Wright (@sqlAsylum)
- Kevin Kline (@kekline)
If you these folks at Summit, make sure to thank them for their hard work!
PASS President Wayne Snyder comes out to honor and thank Kevin Kline for his 10 YEARS of service. Yes, that’s right, 10 YEARS. Wayne doesn’t get far into his speech before he gets choked up. Great quote from Wayne: “Kevin (@kekline) is a man of honor and integrity. He’s… well, he’s full of it.” Kevin then gets a well-deserved standing ovation from the entire audience.
New Directors-at-Large are also announced:
- Brian Moran
- Jeremiah Peschka (@peschkaj)
- Tom LaRock (@SQLRockstar)
Next year’s PASS Summit is also announced. It will be in Seattle from November 8th – 11th, 2010. The decision was made to have the conference in Seattle because it’s a launch year, so access to Microsoft employees will be invaluable. The registration rate is $995 if you register soon. Details and registration can be found on the PASS site at www.sqlpass.org/summit/na2010.
8:52 AM PST
Dell keynote just started with Patrick Ortiz, Solution Architect with Dell’s Infrastructure Consulting Services for SQL Server & BI. The keynote is pretty uneventful.
9:17 AM PST
Woot! Dr. David DeWitt, Technical Fellow, Data & Storage Platform Division at Microsoft, takes the stage. His presentation is entitled, “From 1 to 1000 MIPS.” He promises a very technical talk, against Microsoft Marketing’s wishes. He’s not going to be announcing any products, but instead plans to discuss the changes in database technology and what’s in store for us in the next 10 years.
Highlights (or at least, the ones that my simple mind was able to grasp):
- Basic RDMS design is essentially unchanged, but the hardware has changed dramatically.
- Interesting statistics in disk trends last 30 years: 10,000x capacity, 65x transfer rate, 10x avg seek time… not balanced at all
- “CPU’s and disks are totally out-of-whack in terms of performance.”
- The benefits of 1,000x improvement in CPU is almost negated by lack of improvement in disk
- Transfer bandwidth/byte trends: 1980 = 0.015, in 2009 = 0.0001… 150x slower today! “It’s like trying to provide drinking water for the town through a garden hose.”
- “Can incur up to one L2 data cache miss per row processed if row size is greater than size of cache line.”
- DBMS transfers the ENTIRE ROW from disk to memory even though the query required just 3 attributes.
- “Takeaway: DBMS must avoid doing random disk I/O as much as possible.”
In short, DeWitt shows us the power of indexing and vertical partitioning in very technical terms. He also gives us a taste for column-oriented design, which we’ll catch a glimpse of in SQL Server 2008 R2. Awesomeness.
The keynote wraps up with a promise to include DeWitt’s presentation on the Summit DVD. If you missed the conference, then trust me, DeWitt’s presentation is worth the cost alone; all of the sessions are just a nice bonus on top of that.
Live Blogging: Keynote at PASS, Day 2
So today is day 2 of the PASS Summit conference. The conference has been excellent so far, with tons of great content and tons of great folks. Yesterday’s keynote revealed some interesting tidbits, including SQL Server 2008 R2 setting the world record for TPC-E (transactions per second) and QphH (queries per hour). Be sure to refresh this page frequently for updates, or follow along on Twitter.
8:00 AM PST
Michelle shows up extra early today, having learned her lesson from yesterday. Coffee in hand. Ah, much better than yesterday.
8:41 AM PST
Rushabh Mehta just took the stage to the tune of All Star.
8:46 AM PST
Interesting statistics:
- 15% reduction in revenue
- 40% growth in the SQL PASS community
- 67% reduction in IT expenses
Rushabh encourages support in the PASS community. He challenges everyone to volunteer at least an hour a month with your local user group. If you would like to become more involved in the local East Iowa SQL Server user group, please let me know!
8:51 AM PST
Wayne Snyder just took the stage to present the PASSion Awards. Folks recognized include:
- Tim Ford (@sqlagentman) – for his work on the Program Committee and Quiz Bowl. Tim was no where to be seen! Probably eating left-over bagels.
- Grant Fritchey (@GFritchey) – for his contributions to getting SQL Server Standard relaunched. Grant also had the opportunity to show off his fabulous kilt!
- Jacob Sebastian (@jacobsebastian) – for his contributions to the Asia community. He has started 6 chapters in India. Wow!
- Amy Lewis – for her contributions to the BI Virtual Chapters and growing the sub-chapters.
9:00 AM PST
2009 International PASSion Award recipient is Charlie Hanania. Accomplishments include Swiss Chapter Leader and managing the entire 2009 PASS European Conference.
2009 North American PASSion Award recipient is Allen Kinsel (@sqlinsaneo)! Allen’s accomplishments include 5 years with PASS, and Program Manager for the 2009 Program Committee. He helped tremendously with Summit 2009, so if you see him around Summit, be sure to thank him!
Read more about the PASSion Award winners here.
9:07 AM PST
Tom Casey just took the stage. His first challenge is for all Summit attendees to act as agents of change in their respective organizations. Interesting statistic: only 20% of business users have the data they need to do their job effectively. Tom’s call to arms is to improve that statistic.
9:13 AM PST
Tom Casey: “PASS Summit is the place to be for B-I.” Statistics from Summit 2009: 2 dedicated BI tracks with 50+ BI-related sessions. Last year, 20% of attendees expressed interest in BI; this year, that number is 31%.
9:16 AM PST
Ron VanZanten, Directing Officer of Business Intelligence for Premier Bank Card, just joined Tom on stage. He works with over 25 TB of data and has used BI to leverage the data that they collect. His 3,200 employees use this information to perform their job. VanZanten wanted a flexible BI stack that would grow with the company. He’s successfully tested Madison to improve performance & scale out his environment. This is very encouraging news for any large data warehouse environment.
9:23 AM PST
Tom recognizes that data is available in a wide variety of mediums and that the numbers of sources will only continue to increase. He also introduces a new term: “spreadmart” – spreadsheets that are used as data marts.
9:30 AM PST
Amir Netz just takes the stage to give everyone a demo of the self-service BI offerings in SQL Server 2008 R2. Features include PowerPivot and SharePoint services. Amir shows us an Excel spreadsheet PowerPivot table with 100m rows. It’s amazingly fast. While I shudder to think of end users actually asking for 100m rows in Excel (and trust me, I’m sure it’ll happen), this is going to be a great breakthrough for our BI admins and our power IW users. It’s a time saver and gives greater visibility into data. While I realize the self-service BI offerings are still young, I’m excited for the future of BI. I can’t wait to implement what I’ve seen in our reporting environments.
11:55 AM PST
Tom Casey returns to center stage. Tom announces a contest to win an X-Box 360. To enter, follow @powerpivot & retweet: “Want to learn more, go to http://bit.ly/4n5vpd & sign up for the November CTP #powerpivot.” Tom concludes the keynote with his call to arms to increase the number of business users who have access to the BI data that they need to do their job effectively (currently 20%).
So there you have it, today’s keynote. Check back tomorrow for details on Thursday’s keynote; it looks to be the best yet.
Live Blogging: Keynote at PASS, Day 1
I’m honored to be invited to live-blog during the Summit keynotes. Most of my updates will be via Twitter, but I’ll periodically consolidate and update into blog posts throughout the keynote. Please check back frequently during the Summit for updates.
8:00 AM PST
Michelle gets situated at the blog table, running late and doesn’t even have time for coffee. Ack!
8:05 AM PST
Computer finishes booting up, Twitter loaded. Interesting statistics: 31% of folks expressed interest in Business Intelligence, up significantly from previous years. More than 400 Microsoft product developers and managers, and 98 SQL Server MVP’s, are in attendance.
8:06 AM PST
Wayne Snyder talks about Virtual Chapters. Did you know there are 5 sub-chapters in the Business Intelligence virtual chapter?
8:07 AM PST
Wayne reviews 24HoursOfPASS. Interesting statistics: 50,123 registrations, and 3,524 folks in 70+ countries.
8:09 AM PST
Wayne announces the return of SQL Server Standard. The first article was posted this past week. Content is free but only available online. Seasoned SQL Server authors can earn $500 per article. Interested parties should contact Grant Fritchey (@GFritchey).
8:18 AM PST
Closing words by Wayne: “Remember, no one should be a stranger at Summit.” Make sure to say “hi” to at least one person you have never met before. The more people feel welcome, the more successful the event!
8:24 AM PST
I receive my first-ever press announcement and immediately start leaking sharing the news. First up: SQL Server 2008 R2 CTP scheduled for November release! Also, looks like Madison is being rebranded as “SQL Server 2008 R2 Parallel Data Warehouse.” I think I prefer the simplicity of “Madison.
8:26 AM PST
Microsoft announces a new TPC-E record of 2,012 tpsE. This is a platform-independent world record for *any* OLTP system. Oracle, eat your heart out.
8:30 AM PST
More details on the TPC-E benchmark world record: Unisys ES7000 model 7600R Enterprise Server on 96-core Xeon platform (first server with >64 cores). The same Unisys server also reached 102,778 QphH (queries per hour), a data warehousing performance improvement of 70%.
8:39 AM PST
Bob tells us that the IO strain on virtualized machines is there but negated by Hyper-V. We’re given a demo of live migration: it appears that migration is seamless and that transactions are unaffected by the process. Very cool.
8:42 AM PST
Bob talks about the future of SQL Server as more companies move toward the cloud.
8:50 AM PST
Bob concludes his speech by discussing the future of the data professional, and how the DBA role will not become obsolete but merely transformed.
8:52 AM PST
Ted Kummert, Senior VP of SQL Server, takes the stage.
8:56 AM PST
Ted’s Top 5 Reasons to be at PASS Summit:
#1 You are part of the world’s largest gathering of SQL Server professionals
#2 You can take your questions directly to the “source”
#3 We’ve got Wayne and Rushabh
#4 You can work hard and PLAY hard
#5 You will build skills & knowledge on the #1 database in the world
9:16 AM PST
Dan Jones, Principle Group Program Manager for SQL Server Manageability, takes the stage.
9:20 AM PST
Dan gives us a demo of SQL Server 2008 R2, including Utility Control Points.
9:30 AM PST
I need to leave to check in for my 10:30 am presentation. Bummed I’m going to miss the last of the keynote. 
#PASSAwesomeness
Filed under: Miscellaneous, PASS, Performance & Tuning, Presentations, Syndication
Allen Kinsel on Twitter (@sqlinsaneo) recently started a new Twitter tag, #PASSAwesomeness, about all of the cool things about PASS Summit. I really like the tag, so I’m going to blatantly steal borrow it for this post.
First, and long overdue, I want to give a brief recap of the East Iowa SQL Saturday. On October 17th, our local PASS chapter, 380PASS, sponsored our first ever SQL Saturday at the University of Iowa in Iowa City. By all accounts, the event was a great success! We had 90 attendees, 11 speakers, and 21 sessions. We received numerous compliments on the quality of the speakers, the niceness of the facilities, and the abundance of food. Not too shabby for our first time hosting the event, if I do say so myself.
I’d like to thank all of our wonderful speakers, especially those who traveled from out of town and out of state, for making this event such a success. I’d also like to thank our amazing volunteers for helping put this all together. Lastly, but certainly not least, I’d like to thank our generous sponsors, without whom this event would not be possible. Because this event went so smoothly and was so well received in the community, we’ve already started planning our next big SQL event! In the meantime, don’t forget to check out our monthly 380PASS meetings to tide you over.
I’d also like to take a moment to discuss the PASS Summit. Unless you’re a DBA who’s been living under a rock, you’ve probably heard of the PASS Summit. If you *have* been living under a rock — and hey, I’m not poking fun, I used to live under a rock, too! — then what you need to know is that the Summit is the largest SQL Server conference in the world. It’s a gathering of Microsoft developers and SQL Server gurus; the rest of us show up to try to absorb as much from them as possible. Since I’ve recently moved to the Business Intelligence team, I’m extremely excited to delve into the amazing amount of BI content offered.
I’m also deeply honored to be presenting at the Summit this year on some of the performance tuning techniques I’ve used with great success in my production environments. The session is titled, Super Bowl, Super Load – A Look At Performance Tuning for VLDB’s. If you’re interested in performance tuning or VLDB (very large database) topics, consider stopping by to catch my session. From what I can tell, I’ll be presenting on Tuesday from 10:15am – 11:30am in room(s?) 602-604.
If you read my blog, or if we’ve ever interacted in any way on the internet — Twitter, LinkedIn, e-mails, blog comments, etc. — please stop by and say “hi”! Aside from all of the awesome SQL Server content, I’m really looking forward to meeting as many new folks as possible.
And on that note…
Getting to meet all of the amazing SQL Server professionals out there who have inspired and encouraged me in so many ways #PASSAwesomeness
Partitioning Tricks
Filed under: Miscellaneous, Performance & Tuning, SQL 2008, SQL Tips, Syndication
For those of you who are using partitioning, or who are considering using partitioning, allow me to share some tips with you.
Easy Partition Staging Tables
Switching partitions (or more specifically, hobts) in and out of a partitioned table requires the use of a staging table. The staging table has very specific requirements: it must be completely identical to the partitioned table, including indexing structures, and it must have a check constraint that limits data to the partitioning range. Thanks to my co-worker Jeff, I’ve recently started using the SQL Server Partition Management tool on CodePlex. I haven’t used the automatic partition switching feature — frankly, using any sort of data modification tool in a production environment makes me nervous — but I’ve been using the scripting option to create staging tables in my development environment, which I then copy to production for use. It’s nothing you can’t do yourself, but it does make the whole process easy and painless, plus it saves you from annoying typos. But be careful when using this tool to just create the table and check constraints automatically, because you may need to…
Add Check Constraints After Loading Data
Most of the time, I add the check constraint when I create the staging table, then I load data and perform the partition switch. However, for some reason, I was receiving the following error:
.Net SqlClient Data Provider: Msg 4972, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table ‘myStagingTable’ allows values that are not allowed by check constraints or partition function on target table ‘myDestinationTable’.
This drove me crazy. I confirmed my check constraints were correct, that I had the correct partition number, and that all schema and indexes matched identically. After about 30 minutes of this, I decided to drop and recreate the constraint. For some reason, it fixed the issue. Repeat tests produced the same results: the check constraint needed to be added *after* data was loaded. This error is occurring on a SQL Server 2008 SP1 box; to be honest, I’m not sure what’s causing the error, so if you know, please leave me a comment. But I figured I’d share so that anyone else running into this issue can hopefully save some time and headache.
Replicating Into Partitioned and Non-Partitioned Tables
Recently, we needed to replicate a non-partitioned table to two different destinations. We wanted to use partitioning for Server A, which has 2008 Enterprise; Server B, which is on 2005 Standard, could not take advantage of partitioning. The solution was really easy: create a pre-snapshot and post-snapshot script for the publication, then modify to handle each server group differently. Using pseudo-code, it looked something like this:
/* Identify which servers get the partitioned version */ IF @@SERVERNAME In ('yourServerNameList') BEGIN /* Create your partitioning scheme if necessary */ IF Not Exists(SELECT * FROM sys.partition_schemes WHERE name = 'InsertPartitionScheme') CREATE PARTITION SCHEME InsertPartitionScheme AS PARTITION InsertPartitionFunction ALL TO ([PRIMARY]); /* Create your partitioning function if necessary */ IF Not Exists(SELECT * FROM sys.partition_functions WHERE name = 'InsertPartitionFunction') CREATE PARTITION FUNCTION InsertPartitionFunction (SMALLDATETIME) AS RANGE RIGHT FOR VALUES ('insertValues'); /* Create a partitioned version of your table */ CREATE TABLE [dbo].[yourTableName] ( [yourTableSchema] ) ON InsertPartitionScheme([partitioningKey]); END ELSE BEGIN /* Create a non-partitioned version of your table */ CREATE TABLE [dbo].[yourTableName] ( [yourTableSchema] ) ON [PRIMARY]; END
You could also use an edition check instead of a server name check, if you prefer. The post-snapshot script basically looked the same, except you create partitioned indexes instead.
Compress Old Partitions
Did you know you can set different compression levels for individual partitions? It’s true! I’ve just completed doing this on our largest partitioned table. Here’s how:
/* Apply compression to your partitioned table */ ALTER TABLE dbo.yourTableName Rebuild Partition = All WITH ( Data_Compression = Page ON Partitions(1 TO 9) , Data_Compression = ROW ON Partitions(10 TO 11) , Data_Compression = NONE ON Partitions(12) ); /* Apply compression to your partitioned index */ ALTER INDEX YourPartitionedIndex ON dbo.yourTableName Rebuild Partition = All WITH ( Data_Compression = Page ON Partitions(1 TO 9) , Data_Compression = ROW ON Partitions(10 TO 11) , Data_Compression = NONE ON Partitions(12) ); /* Apply compression to your unpartitioned index */ ALTER INDEX YourUnpartitionedIndex ON dbo.yourTableName Rebuild WITH (Data_Compression = ROW);
A couple of things to note. In all of our proof-of-concept testing, we found that compression significantly reduced query execution time, reads (IO), and storage. However, CPU was also increased significantly. The results were more dramatic, both good and bad, with page compression versus row compression. Still, for our older partitions, which aren’t queried regularly, it made sense to turn on page compression. The newer partitions receive row compression, and the newest partitions, which are still queried very regularly by routine processes, were left completely uncompressed. This seems to strike a nice balance in our environment, but of course, results will vary depending on how you use your data.
Something to be aware of is that compressing your clustered index does *not* compress your non-clustered indexes; those are separate operations. Lastly, for those who are curious, it took us about 1 minute to apply row compression and about 7 minutes to apply page compression to partitions averaging 30 million rows.
Looking for more information on table partitioning? Check out my overview of partitioning, my example code, and my article on indexing on partitioned tables.
Why I’m Blogging Less
Filed under: Business Intelligence, Miscellaneous, PASS, Presentations, Syndication
I’ve received a few questions asking why I’ve been blogging less frequently, and even one inquiry after my health. Rest assured, I’m completely fine. But there are 2 perfectly good reasons why I’ve been blogging less these days.
East Iowa SQL Saturday:
I’m the event organizer for East Iowa SQL Saturday, which is eating up a lot of my free time. If you haven’t yet heard about our SQL Saturday event, let me give you a brief overview. It’s a FREE, one-day training event geared toward SQL Server professionals and anyone who wants to learn more about SQL Server. We have 22 sessions planned covering a variety of topics, from Business Intelligence to Disaster Recovery to SQL Server 2008 topics. And if you’re a .NET developer, we also have some .NET-related presentations, including PowerShell and MVC.
We’re very fortunate to have snagged an excellent set of speakers. Jessica Moss, Louis Davidson, Timothy Ford, Jason Strate, and Alex Kuznetsov are just a few of the great speakers we have lined up.
There’s only a handful of spots left, so if you’re interested in attending, you should register soon. To find out more details about the speakers and sessions, or to register, be sure to check out our website at http://sqlsaturday.380pass.org.
The Other Reason:
Yes, that’s right, I’m with child. Expecting. Eating for two. Bun in the oven. In the family way. You get the idea.
So when I’m not at work, planning SQL Saturday, or playing Civilization Revolution, I’m sleeping. For those who remotely care, I’m due around Super Bowl time in February 2010.
2010: The Year I Make Contact
Rest assured, this blog isn’t going away. And hopefully once I get through SQL Saturday and then PASS Summit, I’ll have more free time again.
Subscribe to my blog!
It's easy, free, and will keep you informed of my latest SQL tips and scripts.Like what you see? Subscribe! 
Around the Web
SQLRockstar Blog Ranking
