Filtered Indexes Work-Around

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 … Continue reading

Index Defrag Script Updates – Beta Testers Needed

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 … Continue reading

Replication Bug with Partitioned Tables

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 … Continue reading

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 … Continue reading

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 … Continue reading

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. … Continue reading

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 … Continue reading

#PASSAwesomeness

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 … Continue reading

Partitioning Tricks

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 … Continue reading

Why I’m Blogging Less

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, … Continue reading