![]() ![]() * Another example of inequality vs equality: */ĪND SecondColumn IS NOT NULL /* INEQUALITY */ To see the difference, look in the execution plan XML, and you'll see theĮquality searches are listed first, then the inequality searches. * Still the same: FirstColumn, SecondColumn.īut now try equality on one field, inequality on the other: * Next, instead of equality searches ( = ), try inequality ( ).*/ It's kinda disappointing, but at least there's a little more logic than that.Īnd I know what you're thinking: selectivity matters, right? Wrong. Missing index column order is determined by column order in the table. poll "Now what missing index recommendations will we get?" "SQL will ask for two different indexes" "Both will ask for FirstColumn, SecondColumn" "Both will ask for SecondColumn, FirstColumn" anonymous If our where clause looks for one thing that doesn't exist, and one thing thatĭoes, will SQL Server put the thing that doesn't exist first so it's faster? poll "What missing index recommendations will we get?" "SQL will ask for two different indexes" "Both will ask for FirstColumn, SecondColumn" "Both will ask for SecondColumn, FirstColumn" anonymous ![]() * Turn on actual execution plans, and check the missing index requests: */ INSERT INTO dbo.DiningRoom (FirstColumn, SecondColumn, ThirdColumn, FourthColumn, FifthColumn, SixthColumn) * Let's create a table with a few columns, and insert 100,000 identical rows: */ (although the 10M row table creation can be pretty slow in Azure) * Any supported version of SQL Server or Azure SQL DB * What SQL Server doesn't consider: selectivity or statistics * How SQL Server picks missing index column order Here's what you're going to learn in this demo: Here’s the demo script Clippy used in the session: Wanna see more of this? Every Saturday & Sunday morning from 9AM-11AM Eastern (iCal), you can catch me on my Twitch, YouTube, or Facebook channels. I decided Clippy deserved his own webcast, so this weekend, I let him take over my stream and defend himself. Students are pretty disappointed at Clippy’s simplicity, but on the flip side, he gets these recommendations done in a millisecond – something you and I would be hard-pressed to pull off. After a few hours of the fundamentals, we switch gears and show how Clippy comes up with his column orders. In my Fundamentals of Index Tuning class, I explain how humans think about designing nonclustered indexes. “Hey buddy! It looks like you’re querying the Users table by Reputation. He knows better than to share his face in SSMS, but he toils away tirelessly suggesting indexes you should add. Sadly, the Office team told Clippy that he didn’t make the stack rankings cut, so he relocated over to the SQL Server team. Remember Clippy, the Microsoft Office assistant from the late 1990s? He would pop up at the slightest provocation and offer to help you do something – usually completely unrelated to the task you were trying to accomplish.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |