Kimberly L. Tripp

Data Dude moving into lower priced VS Editions in VS 2010 - excellent!

kimberly.nospam@nospam.sqlskills.com (kltripp) — Wed, 18 Nov 2009 18:47:00 -0800

Data Dude (Visual Studio "DBPro") has generally been an expensive tool - only in the "Team Systems" Edition of Visual Studio. But, that's all going to change in VS 2010. Here's a picture of the new editions and which components of Data Dude are where:

This is a HUGE improvement (after the clarification isn't not quite as huge but still VERY useful that the tools are in different editions) and is going to allow many of you to use/leverage these key features (such as Data Generation and Static Code Analysis - those are some of my favorites :)) in smaller shops - without having to purchase the full-on Team Systems Edition of VS. If you're interested in more info - check out the following:

Doug Seven's DotNetRocks interview here: Doug Seven on Visual Studio 2010.
The VS2010 team blog here: http://blogs.msdn.com/vstsdb/archive/2009/10/19/what-s-new-for-data-dude-in-visual-studio-2010.aspx
Microsoft Visual Studio 2010 webpage - and where you can download the latest beta (Beta 2): http://www.microsoft.com/visualstudio/en-us/products/2010/default.mspx

CLARIFICATION!!

In 2010 “Team System” goes away (as a brand), and it's been replaced with VS 2010 Ultimate and Premium (VS Pro is still there). Basically VS Premium is similar to VSTS Development Edition + Database Edition (and is priced the same). Ultimate is similar to Team Suite.

The functionality didn't really move down the product line (or the price point), the big different is that there's more than only DB Pro tooling in there now!

Thanks to Doug "D7" Seven for setting me straight!

Enjoy!
kt

Travel tips - tagged by Kevin!

kimberly.nospam@nospam.sqlskills.com (kltripp) — Tue, 17 Nov 2009 07:28:00 -0800

This is a bit overdue as Kevin Kline tagged me in a meme post to help people get ready for their travel to PASS. However, we were too busy with travel (we were in Australia at the time) to get to it. And, while this year's PASS has past, it's not too late for travel in general OR for next year! I suspect that a few folks are already planning for PASS, or Connections, or other business travel! So, here we go - my travel recommendations/tips.

Travel tips:

Join the frequent flyer program even if you're not a frequent flyer. As long as you travel within another 12 months (and sometimes even longer) your miles won't expire... Miles (for upgrades or free tickets) are always good :) and even if you're only a 2 or 3 times a year flyer - they'll add up (again, as long as you keep traveling).
Try to pick an airline and stick with it. The more you fly, the higher the bonuses. In fact, I'm so focused on flying just one airline that I'll often fly two flights (for example, SEA -> DEN, DEN->MCO [Orlando]) even when another airline (like American) has a direct SEA->MCO. This happens more than I would like but it also means that I get things like extra bonuses as the end of the year. For example, as I hit each 25K after 100K (ah, I really do fly *A LOT*), I get 2 extra systemwide upgrades. These allow me to upgrade (at the time of booking) for any international flight from departure to destination. So, I can upgrade SEA->MEL (even though that's actually SEA->LAX, LAX->SYD and SYD->MEL). But, systemwides are only given at the highest levels... so, you really have to fly a lot and you MUST stick with the same airline. My airline of choice: United. Also, to help you earn points faster - be sure to check the airline promotions REGULARLY. For example, many airlines offered a "double your EQM (Elite Qualifying Miles)" for Sept to Dec flight... all solely for registering. This might get you to the next level with little work! Here's United's page on promotions: http://www.united.com/page/article/1,,52102,00.html?navSource=Dropdown07&linkTitle=earnmiles.
When you book, start by doing some research online. Try general travel sites (Expedia, Travelocity, etc.) first but be sure to check the specific airlines as well. And, you *might* need to get creative if your itinerary is messy. Paul and I recently had to travel to Dublin and then Warsaw on the same trip but when we booked the entire set of flights the tickets were OUT OF CONTROL expensive. We ended up booking SEA->DUB and then WAW->SEA (it's called an open jaw) and we saved over 60% (no, I'm NOT kidding). Now, don't get me wrong - we spent something like 5 hours coming up with different options/combinations both online and on the phone. And, in many cases this kind of thing costs more not less so it can get tricky; it really isn't all that common but can really help. One thing of which to be aware is that connecting flights on a different ticket may cause you A LOT of grief (even though they can save you $$$). If your final destination if DUB and you purchase one RT ticket (let's say SEA to LHR) and then a separate RT ticket LHR to DUB but you plan to connect in LHR to fly immediately to DUB... some airlines/partners do NOT allow the baggage to be checked all the way through - this means that you'll need to pick up baggage in LHR and check-in again (and since this is international - it means going through customs/immigration). What this means is that you'll need more time (so pick those connections with 2-3 hours - maybe more). And, if there are any delays, etc. you won't be a high priority for them... this is where it can get messy. But, I have to admit this is something I do OFTEN. I always *try* to fly on partner airlines (meaning Star Alliance) as I have "Gold" privileges BUT, this doesn't always help (when checking in). However, it can help while en route. Again, fly the same airline/group and it does have its benefits.
Seats – when you book, try and get your seats assigned. And, be sure to check out your seat on SeatGuru (http://www.SeatGuru.com). If you book online (which can often offer discounts) be sure to follow up by calling the specific airline to get your seats assigned. In fact, if the price isn’t all that much better you might be MUCH better off using the specific company’s website and/or phone system so that you CAN get your seats assigned. And, in the worst case, if you can’t get your seats assigned – get to the airport EARLY (really target the 1.5-2 hour rule) and then be VERY nice to the folks that check you in… see if you can get an exit row or bulk head. Hmmmm, bulkhead seating is debatable. On some planes, bulkhead

Getting ready for travel:

A couple of weeks prior – go through all of your dates and itineraries and double-check everything. Once, I arrived at an airport for a flight and I had been booked on the wrong day. I remember the phone call well – I had been going back/forth about specific dates (and costs) and in the end, my E-ticket was wrong. Had I double-checked it, it wouldn’t have been a problem… Luckily, seats were available and all was well (and, I had elite status) otherwise, it could have been a nightmare!
Is there anything special you’re taking? Does it need to make it to the cleaner, etc.? Are you going to remember to pick it up in time? Really, planning a bit for this is important. I’ve forgotten to go to the cleaner the night before a trip only to realize that my flight is so early that I can’t even stop on the way to the airport. Now I need to go to plan b. This is why Paul creates a comprehensive “Pack List” and he’s got it online. When he thinks of something he wants to take on a trip, he updates this doc. Then, the day before he packs – he double-checks it, prints it and then starts packing. I’m pretty good at remembering most things so I haven’t done this but I have forgotten a few things over the years and travels. So, I’d really recommend this and I’m probably going to do this moving forward too!

Making the travel less painful:

Sleeping. Does melatonin work for you? Or, have you even tried it? If you’re crossing many time zones, you might want to pick up a bottle of this. It’s an anti-stress/herbal sleep aid and taking it right before bedtime might help you sleep better and adjust more quickly.
Sinus rinse. I swear by this and I honestly think this helps me avoid getting sick on some flights. I rinse my sinuses twice daily regardless of travel but given that you’re traveling with so many other folks and breathing recycled air – rinsing your sinuses can’t hurt. And, you might want to pick up some saline solution for in-flight moisture. Here's a link for the specific sinus rinse I recommend: http://www.neilmed.com/usa/products.php#isotonic. And, they have a nasal moisturizer as well: http://www.neilmed.com/usa/products.php#nasogel.
Flight socks. Honestly, I just recently found out about these and I LOVE them. My feet can swell up a bit while flying (I just recently had foot surgery) and now, I wear these even for shorter flights.
Slippers. Yes, I actually travel with wool slippers. Seriously, I get cold...really cold... And, when I travel with these they make a HUGE difference. Especially for long haul flights. These aren't hard slippers - more like thick socks (easy to pack).
Snack. Even if you do get an upgrade – having a snack with you might make an otherwise hungry time a lot more bearable.
Water. I buy water in the airport prior to boarding so that I’m never without. Flying can be very dehydrating and that will only help you get sick.
Entertainment. Have something ready for you to read, watch, work-on… whatever. This is always my excuse to buy the trashy mags at the airport before I board. However, I have to admit that I know fewer and fewer of the stars referenced. Not sure how I’m supposed to feel about that?! J

Well… I’ll plan to come back to this and update it with other tips/tricks as I think about them!

Safe – and happy – travels to all! And, I look forward to seeing you at an event sometime soon…if not, at PASS, or Connections again next year.

Thanks for reading!
kt

Interviews with MidnightDBA are posted

kimberly.nospam@nospam.sqlskills.com (kltripp) — Mon, 16 Nov 2009 15:47:00 -0800

At PASS we hung out with Sean McCown and Jen McCown (of MidnightDBA) and chatted... and, despite our better judgement, it was recorded. ;-) These aren't overly technical but they are fun! Enjoy!!

And, our favorite "travel tip" from the video is to know your plane and check your seat on http://www.seatguru.com/. In fact, I have a few other travel tips and I'm overdue on a meme that Kevin Kline tagged me in so I'll post that shortly as well.

Enjoy!
kt

SQL Server MVP Deep Dives (book for charity)

kimberly.nospam@nospam.sqlskills.com (kltripp) — Wed, 11 Nov 2009 15:17:00 -0800

Well... it's finally been released and last week was the book launch. This book came together first as a goal by Paul Nielsen who was motivated by Steve Ballmer's request that MVPs "give back" even more and even outside of the direct technical community. Paul had the idea of a book - written for charity - and contributed to by MVPs. Well, here it is!

The book contains content from 53 MVPs with Paul Nielsen, Kalen Delaney, Greg Low, Adam Machanic, Paul S. Randal and I as editors. It was great fun going through all of the content and we're really happy that it's now available!

To purchase, click on the book (to the left) and purchase directly from Manning. When purchased through Manning a larger amount of the proceeds go to WarChild.

Finally, many of us were able to get together at PASS and "launch" the book. We gathered around a long table and people (if interested) had their copies signed. It was great fun! When it was all done - we took the group shot below.

Thanks for a great week (to all that were at PASS) and for great fun working on this title!

UPDATE November 16, 2009: After a few comments, I realized that a few people were tagged incorrectly in my facebook post of this photo and as a result, I had a few names switched around too. So, I just completely started over again and double-checked all the names... Also, I've added links to their websites so you can find out more information about the authors and editors of this title. Finally, I added that purchases should be made through the Manning website instead of other sites (so that more money goes to WarChild). And, finally, I added the entire chapter list with as many links as possible to everyone's websites. I'll keep this up-to-date as folks send me corrected lnks, etc. ENJOY!!

Present for the photo

Front row starting from the left: Louis Davidson, Andy Leonard, Robert C. Cain, Paul Nielsen, Scott Stauffer, Don Kiely, Erin Welker, Kevin Edward Kline
Second row starting from the left: Greg Low, Glenn Berry, Brad McGehee, Gail Shaw, Kalen Delaney, Erland Sommarskog, Allen White, Kimberly L. Tripp, Adam Machanic, Dejan Sarka, John Paul Cook
Third row starting from the left: Joe Webb, Paul S. Randal

Here's the full chapter list from the book - along with links to the authors/editors websites:

PART I

DATABASE DESIGN AND ARCHITECTURE
Paul Nielsen, Editor
1. Louis and Paul's 10 relational database design rules by Paul Nielsen and Louis Davidson
2. SQL Server tools for maintaining data integrity by Louis Davidson
3. Finding functional dependencies by Hugo Kornelis

PART II

DATABASE DEVELOPMENT
Adam Machanic, Editor
4. Set-based iteration: the third alternative by Hugo Kornelis
5. Gaps and islands by Itzik Ben-Gan
6. Error handling in SQL Server and applications by Bill Graziano
7. Pulling apart the FROM clause by Rob Farley
8. What makes a bulk insert a minimally logged operation? by Denis Gobo
9. Avoiding three common query mistakes by Kathi Kellenberger
10. Introduction to XQuery on SQL Server by Michael Coles
11. SQL Server XML frequently asked questions by Michael Coles
12. Using XML to transport relational data by Matija Lah
13. Full text searching by Robert C. Cain
14. Simil: an algorithm to look for similar strings by Tom van Stiphout
15. LINQ to SQL and the ADO.NET Entity Framework by Bob Beauchemin
16. Table-valued parameters by Don Kiely
17. Build your own index by Erland Sommarskog
18. Getting and staying connected—or not by William Vaughn
19. Extending your productivity in SSMS and Query Analyzer by Pawel Potasinski

20. Why every SQL developer needs to have a tools database by Denis Gobo
21. Deprecation feature by Cristian Lefter
22. Placing SQL Server in your pocket by Christopher Fairbairn
23. Mobile data strategies by John Baird

PART III

DATABASE ADMINISTRATION
Kimberly L. Tripp and Paul S. Randal, Editors
24. What does it mean to be a DBA? by Brad McGehee
25. Working with maintenance plans by Tibor Karaszi
26. PowerShell in SQL Server by Richard Siddaway
27. Automating SQL Server management using Server Management Objects by Allen White
28. Practical auditing in SQL Server 2008 by Jasper Smith
29. My favorite DMVs and why by Aaron Bertrand
30. Reusing space in a table by Joe Webb
31. Some practical issues in table partitioning by Ron Talmage
32. Partitioning for manageability (and maybe performance) by Dan Guzman
33. Faster, smaller, and more energy-efficient backups without indexes by Greg Linwood
34. Using database mirroring to become a superhero! by Glenn Berry
35. The poor man's SQL Server log shipping by Edwin Sarmiento
36. Some understated changes found in SQL Server 2005 replication by Paul Ibison
37. High performance Transactional Replication by Hilary Cotter
38. Successfully implementing Kerberos delegation by Scott Stauffer
39. Running SQL Server on Hyper-V by John Paul Cook

PART IV

PERFORMANCE TUNING AND OPTIMIZATION
Kalen Delaney, Editor
40. When is an unused index not an unused index? by Rob Farley
41. Speeding up queries with index covering by Alex Kuznetsov
42. Tracing the deadlock by Gail Shaw
43. How to optimize tempdb performance by Brad McGehee
44. Does the order of columns in an index matter? by Joe Webb
45. Correlating SQL Profiler with Windows Performance Monitor by Kevin Kline
46. Using correlation to improve query performance by Gert-Jan Strik
47. Dr. DMV: how to use Dynamic Management Views by Glenn Berry
48. Query performance and disk I/O counters by Linchi Shea
49. XEVENT: the next event infrastructure for SQL Server by Cristian Lefter

PART V

BI DEVELOPMENT
Greg Low, Editor
50. BI for the relational guy by Erin Welker
51. Unlocking the secrets of SQL Server 2008 Reporting Services by William Vaughn
52. Reporting Services tips and tricks by Bruce Loehle-Conger
53. SQL Server Audit: change tracking or change data capture? by Aaron Bertrand
54. Introduction to SSAS data mining by Dejan Sarka
55. To aggregate or not to aggregate: is there really a question? by Erin Welker
56. Incorporating data profiling in the ETL process by John Welch
57. Expressions in SQL Server Integration Services by Matthew Roche
58. SSIS performance tips by Phil Brammer
59. Incremental loads using T-SQL and SSIS by Andy Leonard

Filtered indexes and filtered stats might become seriously out-of-date

kimberly.nospam@nospam.sqlskills.com (kltripp) — Thu, 24 Sep 2009 09:45:00 -0800

Time flies when you're having fun, eh? This week Paul and I are in Dublin, Ireland delivering a deep technical "SQL Immersion Event" on Internals, Performance Tuning and Maintenance with our partner ProData (and having a blast - we have a really fun and very interactive group). And, today's an even more interesting day to be in Dublin as it's the 250th anniversary of the Guinness Brewery...everyone around Dublin will be toasting the most popular drink in Ireland today (Sept 24) at 5:59pm GMT. Unfortunately, our class runs until 6pm but Paul tells me that we're still going to celebrate. ;-)

For now, we'll just have to celebrate some SQL (I can hear minor grumblings everywhere :)...

Filtered indexes and filtered stats are an incredibly powerful feature in SQL Server 2008 - one that I've posted on before and will again. Today's post is about some of the gotchas with filtered index stats and filtered stats. Cutting to the punchline - you might have some trouble with their accuracy as your data changes. The SIMPLE solution is to update these specific stats manually and more frequently (meaning that you don't want to rely on the database option AUTO_UPDATE_STATISTICS). In fact, depending on your data volatility, you might want to update these statistics on a VERY frequent basis. Part of the question you'll have to answer is how much of the filtered set changes over the course of a day/week/month. And, in whatever frequency you reach 5-10% (let's say weekly), I'd suggest updating the statistics weekly. If you update 5-10% within the course of a day - then daily. Let me give you the full story...

Statistics are traditionally updated when roughly 20% (+ a minimum of 500 rows) of the data has changed. If you have a table with 100,000 rows then SQL Server will hit the threshold at 20,500 rows (just a tad over 20%) and a table with 10,000 rows will hit the threshold at 2,500 rows (which is 25%). Additionally, in SQL Server 200x (2000, 2005 and 2008) statistics are NOT immediately updated when the threshold is reached, instead they are invalidated. It's not until someone needs the statistic that SQL Server updates it. This reduces thrashing that occurred in SQL Server 7.0 when stats were updated immediately instead of just being invalidated. Another interesting point is what is meant by "20% of the data has changed?"... How is that defined? Is it based on updates to columns or inserts of rows? Of course the answer is... it depends - here, it depends on the version of SQL Server that you're using:

SQL Server 2000 defines 20% as 20% of the ROWS have changed. You can see this in sysindexes.rcmodctr.
SQL Server 2005/8 defines 20% as 20% of the COLUMN data has changed. You cannot see this unless you are accessing SQL Server through the DAC as it's in a base system table (2005: sysrowsetcolumns.rcmodified and for 2008: sysrscols.rcmodified).

Now, for regular indexes (those that do not include a filter) both strategies have pros and cons. Let's start with the con for SQL 2000... If you have a particularly volatile column then the rowmodctr will be increased quickly and then ALL statistics (even those that have not changed) are invalidated. Without a way to understand a column's volatility, you will invalidate some statistics too soon. So, 2005/2008 created a column modification counter to better define when a statistic should be invalidated. But, the con for SQL 2005/2008 (which is the pro for row-based algorithm of SQL 2000) is that a lot more data must change in a single column before the statistics get updated. If your modifications are fairly well balanced (across the table) then the row-based algorithm could suit you better. If your modifications are isolated (to a specific column) then the column-based algorithm will suit you better. But, neither is perfect... and, the new column-based algorithm poses another HUGE problem for filtered indexes.

For filtered indexes the threshold invalidation algorithm is tied SOLELY to the column and not just to the filtered set. So, if your table has 10,000 rows it takes 2,500 modifications in that column to update statistics. If your filtered index only has 1,000 rows... then you could theoretically modify this specific filtered set 2.5 times before IT would be updated. Yes, I realized that this sounds unlikely but it's really not. What if your filter is for ACTIVE = 1. It's only 1% of your table and it's the most active data set. You will effectively have to modify this set 20 times over before the statistics for your filtered index get updated.

But, having said that - I also have a simple solution. Don't wait... For filtered indexes (especially on volatile data) add a special UPDATE STATISTICS job that specifically updates their stats with a FULLSCAN. The good news is that they are likely to be smaller indexes (and therefore easier/less-costly statistics to update) and you'll clearly get a lot better uses out of them. Additional good news is that many filtered indexes are designed to be covering indexes, covering indexes aren't going to rely as heavily on statistics so there are many covered queries that won't care. The following query will get the list of all of your tables/indexes that have filters. Once you have this list you can create a loop to update statistics.

SELECT object_name(object_id) AS [Table Name]
, name AS [Index Name]
, stats_date(object_id, stats_id) AS [Last Updated]
FROM sys.stats
WHERE has_filter = 1

So, there are lots of options and definitely a few things of which to be aware... filtered indexes and filtered stats are a powerful option to leverage, just make sure you keep those stats up to date!

Cheers... it's time for a pint of Guinness!

PS - For more information on statistics, check out the SQL Server whitepapers:

Statistics Used by the Query Optimizer in Microsoft SQL Server 2008 (added this after comment #2, please review ALL of the comments as there's a good discussion on the 2008 filtered stats "multiplier" and whether or not it's having the desired effect?! I say no and want to reiterate - filtered index stats need more frequent updating based on your data volatility.)

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005

Statistics Used by the Query Optimizer in Microsoft SQL Server 2000

The fall line-up and event list - we hope to see you there!

kimberly.nospam@nospam.sqlskills.com (kltripp) — Mon, 24 Aug 2009 17:35:00 -0800

We've finally booked all of our tickets and started planning final details for all our of our classes and conferences this fall. We've got quite a few things planned and tons of great content at each. By continent, here's the plan:

Europe

Dublin, Ireland: September 21-25. Paul and I will be teaching a week-long Immersion Event - combining internals, performance tuning, database maintenance and more.

Warsaw, Poland: September 29-30. Paul and I will be presenting a full day of SQL Server sessions at the Microsoft Technology Summit 2009

Australia - Melbourne

October 15-16. Paul and I will be teaching a 2-day class "SQL Server 2008: New Features - Updating Your Administrations Skills in Database Infrastructure and Scalability"

October 19-22. Paul will be teaching a 4-day class "SQL Server 2005/2008: DB Maintenance and Availability: From Performance to Disaster Recovery"

October 19-22. I will be teaching a 4-day class "SQL Server 2005/2008: Performance Tuning – From Design to Indexing to Optimizing Procedural Code"

US

Seattle, WA for SQLPASS: November 2-5. Paul and I will be teaching two full-day workshops and a Spotlight Session each.

Las Vegas, NV for SQL Connections: Nobember 9-12: Paul and I will be teaching two full-day workshops and 5 sessions. Stacia will be teaching 4 sessions on BI.

Redmond, WA for SQL MCMs and Sharepoint MCM's through the end of this year

You can find more details and links to the various registration sites on our Upcoming Events page. And, if you have any questions, feel free to email me: Kimberly AT SQLskills.com or Paul AT SQLskills.com.

We hope to see you there and be sure to swing by and say hi!

Cheers,
kt

Column order doesn't matter... generally, but - IT DEPENDS!

kimberly.nospam@nospam.sqlskills.com (kltripp) — Mon, 10 Aug 2009 07:10:00 -0800

OK, for years I've been saying that SQL Server doesn't care about the order in which you define the columns of your table because internally SQL Server will re-arrange your columns to store all of the fixed width columns first and the variable columns last. In both the fixed-width portion of the row as well as the variable-width portion of the row, the columns are defined in the order in which they are declared. So, what does matter?

It's all in the cost of the variable array's offset values. If the large majority of your NULLable records are at the end, then SQL Server doesn't need to completely populate the variable block array (which saves 2 bytes per column). If you have a table where 36 columns are NULLable and generally they are NULL, then defining those columns at the end of the row can save you space.

The following script will show you how the maximum length of the row changes based on whether or not a later column in the variable block is NOT NULL - even when most/all of the prior columns are!

CREATE TABLE RowSizeVariableBlock
(
ID int NOT NULL identity,
c01 char(10) NOT NULL default 'test',
c02 datetime2(7) NOT NULL default sysdatetime(),
c03 char(80) NOT NULL default 'junk',
c04 varchar(100) NULL,
c05 varchar(100) NULL,
c06 varchar(100) NULL,
c07 varchar(100) NULL,
c08 varchar(100) NULL,
c09 varchar(100) NULL,
c10 varchar(100) NULL,
c11 varchar(100) NULL,
c12 varchar(100) NULL,
c13 varchar(100) NULL,
c14 varchar(100) NULL,
c15 varchar(100) NULL,
c16 varchar(100) NULL,
c17 varchar(100) NULL,
c18 varchar(100) NULL,
c19 varchar(100) NULL,
c20 varchar(100) NULL,
c21 varchar(100) NULL,
c22 varchar(100) NULL,
c23 varchar(100) NULL,
c24 varchar(100) NULL,
c25 varchar(100) NULL,
c26 varchar(100) NULL,
c27 varchar(100) NULL,
c28 varchar(100) NULL,
c29 varchar(100) NULL,
c30 varchar(100) NULL,
c31 varchar(100) NULL,
c32 varchar(100) NULL,
c33 varchar(100) NULL,
c34 varchar(100) NULL,
c35 varchar(100) NULL,
c36 varchar(100) NULL,
c37 varchar(100) NULL,
c38 varchar(100) NULL,
c39 varchar(100) NULL,
c40 varchar(100) NULL
)
go
insert RowSizeVariableBlock DEFAULT VALUES
go

select * from RowSizeVariableBlock
go

select * from sys.dm_db_index_physical_stats
(db_id(), object_id('RowSizeVariableBlock'), null, null, 'detailed')
-- review "max" record size = 114
go

insert RowSizeVariableBlock (c01, c03, c20)
values ('med row', 'up to c20', 'test')
go

select * from RowSizeVariableBlock
go

select * from sys.dm_db_index_physical_stats
(db_id(), object_id('RowSizeVariableBlock'), null, null, 'detailed')
-- review "max" record size = 154
go

insert RowSizeVariableBlock (c01, c03, c30)
values ('med+ row', 'up to c30', 'test')
go

select * from RowSizeVariableBlock
go

select * from sys.dm_db_index_physical_stats
(db_id(), object_id('RowSizeVariableBlock'), null, null, 'detailed')
-- review "max" record size = 174
go

insert RowSizeVariableBlock (c01, c03, c40)
values ('large row', 'up to c40', 'test')
go

select * from RowSizeVariableBlock
go

select * from sys.dm_db_index_physical_stats
(db_id(), object_id('RowSizeVariableBlock'), null, null, 'detailed')
-- review "max" record size = 194
go

While there are some other optimizations at this level, most tables cannot benefit from this as the data populations aren't as predictable nor are most tables filled with so many variable-width and NULLable columns. However, if you do have this data pattern, defining these columns at the end of your table's definition - MIGHT save a tremendous amount of space, especially when this table is very large!
Paul's blogged more on these structures as well as the NULL bitmap here: http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-null-bitmap-size.aspx.
Enjoy! And, thanks for reading,
kt

Public SQL Server Immersion Event in Dublin in September

kimberly.nospam@nospam.sqlskills.com (Admin) — Fri, 10 Jul 2009 15:58:00 -0800

Paul and I will be teaching a week-long public Immersion Event in Dublin, September 21-25, in partnership with our good friends at Prodata and Microsoft Ireland.

The class will cover:

Day 1: SQL Server Internals (On-disk structures, index internals, logging, recovery, transaction log architecture)
Day 2: Designing for Performance (data types, table and index partitioning)
Day 3: Indexing for Performance (Access patterns, covering, INCLUDE, indexing strategies)
Day 4: Essential Database Maintenance (data and log files, tempdb, index and statistics maintenance, backup and restore)
Day 5.1: Essential Database Maintenance (consistency checking and disaster recovery)
Day 5.2: SQL Consolidation and Virtualization

This is going to be a great workshop where we combine the best of all our various classes into a superb learning opportunity.

Checkout the registration site for more in-depth details about the content, location, and cost.

Hope to see you there!

Looking for security vulnerabilities in database code

kimberly.nospam@nospam.sqlskills.com (kltripp) — Tue, 30 Jun 2009 08:34:00 -0800

I've always been concerned with security and I've always stressed the importance of auditing the REAL user context not just the current user (see this post on EXECUTE AS and auditing). So, I generally try to avoid using dynamic string execution and if necessary create well tested/protected parameters (fyi - using QUOTENAME can be a fantasic solution to protectng identifiers as input parameters but it can't protect more complex strings).

Having said that, what if I'm looking at a database for the first time... just poking around trying to see if there's anything that needs further attention? I've come up with a quick query... And, while it's not going to "solve" your problem (as that's going to take some re-writing of code) or even truly verify if you're vulnerable, it gives you a "quick list" of where you should look first! If your code uses dynamic strings AND it's elevated - then start there!

SELECT OBJECT_NAME(object_id) AS [Procedure Name],
CASE
    WHEN sm.definition LIKE '%EXEC (%' OR sm.definition LIKE '%EXEC(%' THEN 'WARNING: code contains EXEC'
      WHEN sm.definition LIKE '%EXECUTE (%' OR sm.definition LIKE '%EXECUTE(%' THEN 'WARNING: code contains EXECUTE'
END AS [Dynamic Strings],
CASE
    WHEN execute_as_principal_id IS NOT NULL THEN N'WARNING: EXECUTE AS ' + user_name(execute_as_principal_id)
      ELSE 'Code to run as caller - check connection context'
END AS [Execution Context Status]
FROM sys.sql_modules AS sm
ORDER BY [Procedure Name]

Is this enough? Anything else you'd check? What do you think?

THANKS!
kt

The Tipping Point Query Answers

kimberly.nospam@nospam.sqlskills.com (kltripp) — Sun, 07 Jun 2009 21:29:00 -0800

OK, I'll definitely take a beating from all of you for having gone so long between my survey posts and now. I won't even go into the details but between some crazy work schedules, multiple sinus problems and even migraines... well, I've been a bit behind. Let's just say that April/May were rough at best. I'm feeling better and well, now I'm trying to catch up. I had really gotten the blogging bug in March but I completely lost it in April. But, this tipping point series is in dire need of lots of explaining so I'm really hoping to get a few posts done in this area for sure!

First, I started the discussion around this in a few surveys:

Survey/Question 1

Q1 was described as this: if a table has 1 million rows at 20 rows per page (50,000 pages), at what percentage (roughly) of the data would a nonclustered index no longer be used. Blogged here. Here's what the survey said as of today:

And, for Q1 the correct result (Between 0-2% of the rows) is actually the best result (but, by no means the overwhelming majority at only 28%). However, often people just "think" the answer is very small. So... I did a few more questions/surveys.

Survey/Question 2

Q2 was described as this: if a table has 1 million rows at 100 rows per page (10,000 pages), at what percentage (roughly) of the data would a nonclustered index no longer be used. Blogged here. Here's what the survey said as of today:

And, for Q2 the correct result (Less than .5% of the rows) is actually at a tie for the best (but, again, even a small percentage at only 22%). Again, often people just "think" the answer is very small. So... I did one more question/survey.

Survey/Question 3

Q3 was described as this: if a table has 1 million rows at 2 rows per page (500,000 pages), at what percentage (roughly) of the data would a nonclustered index no longer be used. Blogged here. Here's what the survey said as of today:

And, for Q3 the correct result (Between 10-20% of the rows) is actually NOT the highest answer. And, this is even more convincing that there's confusion around what's going on and why.

The Tipping Point

What is the tipping point?

It's the point where the number of rows returned is "no longer selective enough". SQL Server chooses NOT to use the nonclustered index to look up the corresponding data rows and instead performs a table scan.

When does the tipping point occur?

It depends... it's MOSTLY tied to the number of pages in the table. Generally, around 30% of the number of PAGES in the table is about where the tipping point occurs. However, parallelism, some server settings (processor affinity and I/O affinity), memory and table size - all can have an impact. And, since it can vary - I typically estimate somewhere between 25% and 33% as a rough tipping point (and, you'll see from a bunch of my examples, that number is not EXACT). Then, I translate that into rows.

Math for Tipping Point Query 3: If a table has 500,000 pages then 25% = 125,000 and 33% = 166,000. So, somewhere between 125,000 and 166,000 ROWS the query will tip. Turning that into a percentage 125,000/1million = 12.5% and 166,000/1million = 16.6%. So, if a table has 500,000 pages (and 1 million rows) then queries that return less than 12.5% of the data are likely to USE the nonclustered index to lookup the data and queries over 16.6% of the data are LIKELY to use a table scan. For this table, that percentage seems "reasonable". But, most of us say that the tipping point happens at a much lower percentage... why? Because row size - which determines table size (and therefore pages) is really what has the greatest impact. So, let's look at Tipping Point Query 2...

Math for Tipping Point Query 2: If a table has 10,000 pages then 25% = 2,500 and 33% = 3,333. So, somewhere between 2,500 and 3,333 ROWS the query will tip. Turning that into a percentage 2,500/1million = .25% and 3,333/1million = .33% (not even 1%). So, if a table has only 10,000 pages (and 1 million rows) then queries that return less than a quarter of 1% of the data are likely to USE the nonclustered index to lookup the data and queries over one third of one percent are LIKELY to use a table scan. For this table, that percentage seems really low BUT, at the same time it makes sense (to a point) that a small table would be scanned... but, for less than 1%. 1% is NOT selective enough. For small tables, it might not matter all that much (they're small, they fit in cache, etc.) but for bigger tables - it might be a big performance problem.

Math for Tipping Point Query 1: If a table has 50,000 pages then 25% = 12,500 and 33% = 16,666. So, somewhere between 12,500 and 16,666 ROWS the query will tip. Turning that into a percentage 12,500/1million = 1.25% and 16,666/1million = 1.66% (under 2%). So, if a table has 50,000 pages (and 1 million rows) then queries that return less than 1.25% of the data are likely to USE the nonclustered index to lookup the data and queries over 1.66% are LIKELY to use a table scan. Again, this seems like a low number. Again, for small tables, it might not matter all that much (they're small, they fit in cache, etc.) but as tables get larger and larger - it CAN be a big performance problem.

Why is the tipping point interesting?

It shows that narrow (non-covering) nonclustered indexes have fewer uses than often expected (just because a query has a column in the WHERE clause doesn't mean that SQL Server's going to use that index)
It happens at a point that's typically MUCH earlier than expected... and, in fact, sometimes this is a VERY bad thing!
Only nonclustered indexes that do not cover a query have a tipping point. Covering indexes don't have this same issue (which further proves why they're so important for performance tuning)
You might find larger tables/queries performing table scans when in fact, it might be better to use a nonclustered index. How do you know, how do you test, how do you hint and/or force... and, is that a good thing?

Real example of an interesting tipping point

Earlier today, I went on facebook and twitter and gave the following information - very vaguely - and I asked "why" is Q2 so much slower than Q1 if Q2 returns only 10 more rows. Same table and no hints (other than MAXDOP)...

Q1: SELECT * FROM table WHERE colx < 597420 OPTION (MAXDOP 1)

returns 197,419 rows

takes 116,031 ms (1 minute, 52 seconds)

1,197,700 logical reads, 5 physical reads, 137,861 read-ahead reads

7,562 ms CPU time
Q2: SELECT * FROM table WHERE colx < 597430 OPTION (MAXDOP 1)

returns 197,429 rows

takes 244,094 ms (4 minutes, 4 seconds)

801,685 logical reads, 1410 physical reads, 801,678 read-ahead reads

9,188 ms CPU time

There were lots of great guesses... but, it's the tipping point. SQL Server chose to "tip" the second query because it was "over the line". But, it's important to realize that there are cases when that's NOT a good idea. And, what are your options?

In SQL Server 2005 - the only option is to force the nonclustered index to be used:

Q2: SELECT * FROM table WITH (INDEX (NCInd)) WHERE colx < 597430 OPTION (MAXDOP 1)

But, this can be TERRIBLY bad on some machines where the IOs could be a lot faster (and where data might already be in cache). These specific numbers are exactly that - specific to this HARDWARE (and, I chose not-so-optimal HW in this case to highlight this problem). And, depending on what number you use (what if this is a parameter in sps?) you might force SQL Server to do WAY more IOs by forcing the index than allowing the tipping point to do its job. But, depending on your hardware (and/or what you know to be in cache at the time of execution), it might be better to force an index instead of letting SQL Server choose. So, should I force the index? Be careful, if you're wrong - it could take more time and actually be slower.

In SQL Server 2008 - there's a new hint - FORCESEEK:

Q2: SELECT * FROM table WITH (INDEX (FORCESEEK)) WHERE colx < 597430 OPTION (MAXDOP 1)

FORCESEEK is better because it doesn't tie you to a particular index directly but it also doesn't let SQL Server tip to a table scan. However, just like forcing an index - you can be wrong!

So, what should you do? It depends. If you know your data well and you do some extensive testing you might consider using a hint (there are some clever things you can do programmatically in sps, I'll try and dedicate a post to this soon). However, a much better choice (if at all possible) is to consider covering (that's really my main point :). In my queries, covering is unrealistic because my queries want all columns (the evil SELECT *) but, if your queries are narrower AND they are high-priority, you are better off with a covering index (in many cases) over a hint because an index which covers a query, never tips.

That's the answer to the puzzle for now but there's definitely a lot more to dive into. The Tipping Point can be a very good thing - and it usually works well. But, if you're finding that you can force an index and get better performance you might want to do some investigating and see if it's this. Then consider how likely a hint is to help and now you know where you can focus.

Thanks for reading,
kt