I’ve been thinking through and experimenting a bit more with some of the concepts in SQL Azure – specifically I was considering the impact of fragmentation on both the storage (in terms of the storage limit) as well as the maintenance. This is not a new issue, DBA’s face fragmentation regularly and can deal with it in a variety of ways, but with SQL Azure the problem looks magnified by a lack of tools and working space. Whilst looking into this, I then realised that there is an unfortunate consequence of not knowing how much data space your index is actually using.
Each table in SQL Azure has to have a clustered index if data is going to be inserted into it and clustered indexes can suffer from fragmentation if chosen poorly. The combination of SQL Azure and the time-honoured fragmentation provides three consequences about it, fragmentation:
- will occur and you have no way in which to measure it due to the lack of DMV support.
- will create wasted space within your space allocation limit.
- will reduce your performance.
You could work it out if you knew how much space you had actually used vs. what the size of the data held is, but we are unable to measure either of those values. If you have chosen the data compression option on the index then even those values would not give you a fragmentation ratio.
This leaves us with a situation in which you can not know how much you are fragmented, meaning:
- You schedule a regular index rebuild.
- Hope SQL Azure performs index rebuilds for you.
I’m not aware of SQL Azure doing this for you – and you do not have SQL Agent facilities either.
So this seems very wrong, the concept of SQL Azure is to take away a lot of the implementation details and hassle from the subscriber – DR and failover is handled etc. But there looks to be as gap in which certain items such as fragmentation is falling – I have not seen any documentation saying SQL Azure handles it (but there could be some hidden somewhere and I hope there is!) and neither are you given the right tools in which to program and handle it yourself.
What happens when you hit that size limit?
Msg 40544, Level 20, State 5, Line 1 The database has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions. Code: 524289
That took a lot of time to get to, (SQL Azure is not fast), but was generated using a simple example that would also demonstrate fragmentation.
Create Table fragtest ( id uniqueidentifier primary key clustered, padding char(3000) )
Very simple stuff, deliberately using a clustered key on a GUID to cause a decent level of fragmentation, and also using the padding fixed with character field to ensure 2 rows per page only, maximising the page splits.
insert into fragtest values (newid(), replicate('a',1000)) go 200000
Because of the randomness of the newid() function, the level of fragmentation is not predictable but will certainly occur – in my test I hit the wall on 196,403 records and failed with an out of space message.
Given the 2 rows per page and the number of rows, with ~0% fragmentation the data should be able ~767Mb – that is considerably short of 1 Gb – so there is a significant level of fragmentation in there wasting space, about 23% of it. If you include the 2k per page being wasted by the awkward row size then the actual raw data stored is roughly ~60% of the overall size allowing for row overheads etc.
So there are two important points from this contrived example:
- You can lose significant space from bad design.
- Doing this backs you into a corner that you will not be able to get out of – this is the worst part.
How are you cornered? well, try work out how to get out of the situation and defrag the clustered index / free up the space, you could:
- Attempt an index rebuild.
- Try to rebuild it with SORT_IN_TEMP.
- Drop the index.
- Delete data.
The first three fail, the SORT_IN_TEMP is not supported and would not of rescued the situation either since you have no working space in which to write the newly sorted rows prior to removing the old ones. So do you really want to delete data? I don’t think we can consider that an option for now.
This all seems like a ‘rock’ and a ‘hard place’; whilst SQL Azure can support these data quantities, it seems prudent that you never consider actually going close to them at all – and that you equally are going to find it difficult to understand if you are close to them, since there is no way of measuring the fragmentation. The alternative is that you manually rebuild indexes on a regular basis to control fragmentation, but then enough free space is going to have to be left to allow you to rebuild your largest index without running out of space – reducing your data capacity significantly.
The corner is not entirely closed off, the way out of the corner would be to create another SQL Azure database within my account and select the data from database1.fragtest to database2.fragtest and then drop the original table and transfer it back – not ideal but it would work in an emergency.
I think the key is to design to make sure you do not have to face this issue; keep your data quantities very much under the SQL Azure size limits, and watch for the potential of tables being larger than the remaining space and preventing an re-indexing from occurring.
Interested to know your thoughts on this one, and what other consequences of being close to the limit will come out.
The PDC is just about over, the final sessions have finished and the place is emptying rapidly – the third day has included a lot of good information about SQL Azure, the progress made to date on it as well as the overall direction – including a new announcement by David Robinson, Senior PM on the Azure team about a project codenamed ‘Houston’ .
During the sessions today the 10Gb limit on a SQL Azure database was mentioned a number of times, but each time was caveated with the suggestion that this is purely the limit right now, and it will be increased. To get around this limit, you can partition your data across multiple SQL Azure databases, as long as your application logic understands which database to get the data from. There was no intrinsic way of creating a view across the databases, but it immediately made me consider that if you were able to use the linked server feature of the full SQL Server, you could link to multiple Azure databases and created a partitioned view across the SQL Azure databases – got to try that out when I get back to the office but I do not expect it to work.
SQL Azure handles all of the resilience, backup, DR modes etc, and it remains hidden from you – although when connected to the SQL Azure database you do see a ‘Master’ database present. It is not really a ‘Master’ in the same way that we think of one, and it quickly becomes apparent how limited that version of the ‘Master’ really is – it exists purely to give you a place to create logins and databases. It could have been called something else to make it a bit clearer but one of the SQL Azure team said it was to keep compatibility to other 3rd party applications that expected there to be a master.
SQL Azure supports transactions as mentioned before, but given the 10GB limit currently on a database you will be partitioning your data across databases. That will be a problem, because the system does not support distributed transactions, so any atomic work that is to be committed on multiple databases at once it going to have to be controlled manually / crufted in code, which is not ideal and a limitation to be aware of.
Equally cross database joins came up as an area with problems – they can be made, but it appears there are performance issues – interested to start running some more tests there and see whether you can mimic a partitioned view across databases using joins. The recommendation was to duplicate reference data between databases to avoid joins, so lookup tables would appear in each database in effect, removing the cross database join.
On the futures list:
- The ability to have dynamic partition splits looked interesting, regular SQL server does not have this facility within a partitioned table – so if Azure can do it across databases then this might come up on the SQL roadmap as a feature – that could be wishful thinking.
- Better tooling for developers and administrators – that is a standard future roadmap entry.
- Ability to Merge database partitions.
- Ability to Split database partitions.
So SQL Azure has grown up considerably and continues to grow, in the hands-on-labs today I got to have more of a play with it and start testing more of the subtle limitations and boundaries that are in place. Connecting to an azure database via SQL Server Management Studio is trivial and the object explorer contains a cut down version of the normal object tree, but includes all the things you would expect such as tables, views and stored procedures.
Some limitations of the lack of master and real admin access become apparent pretty fast, no DMV support, no ability to check your current size. No ability to change a significant number of options, in fact, the bulk of the options are not even exposed.
Two of my personal favourites I took an immediate look at, maxdop and parameterization.
- Maxdop is set at 1, although you can not see it, and attempting to override it throws an error from the query windows, telling you that it is not permitted. Do not plan on parallel query execution, you will not get it.
- I attempted to test the query parameterisation using the date literal trick and it appeared to remain parametrized, as though the database is in ‘forced’ parameterisation mode, so is more likely to get parameter sniffing problems but I have not been able to concretely prove it as yet, but the early indication is the setting is ‘Forced’
One other interesting concept was that a table had to have a clustered index, it was not optional if you wanted to get data into the table, although is did not stop me from creating a table without a clustered index, I had not attempted to populate data into it to see this limit in action – a case of too much to do and so little time.
On one of the final talks about SQL Azure, David Robinson announced a project codenamed ‘Houston’ – (there will be so many ‘we have a problem’ jokes on that one) which is basically a silverlight equivalent of SQL Server Management Studio. The concept comes from the SQL Azure being within the cloud, and if the only way to interact with it is by installing SSMS locally then it does not feel like a consistent story.
From the limited preview, it only contains the basics but it clearly let you create tables, stored procedures and views, edit them, even add data to tables in a grid view reminiscent of Microsoft Access. The UI was based around the standard ribbon bar, object window on the left and working pane on the right. It was lo-fi to say the least but you could see conceptually where it could go – given enough time it could become a very good SSMS replacement, but I doubt it will be taken that far. There was an import and Export button on the ribbon with what looked to be ‘Excel’ like icons but nothing was said / shown of them. Date wise ‘Targetting sometime in 2010’, so this has some way to go and is not even in beta as yet.
So that was PDC09, excellent event, roll on the next one!
The PDC has been an amazing place to be today, the buzz and excitement generated from the keynote this morning has permeated the entire convention centre and understandably so – this is primarily a conference for IT people and of course what is the best way to get IT folk on board? give them some hardware, usb sticks, usb drives, t-shirts – but Steve Sinofksy and Microsoft went one better this morning in the PDC keynote.
You could sense something was coming in that they were going through a number of netbook/ laptop devices talking about how they have learnt more about how the hardware is constructed and used. This led them to the creation of a kind of reference laptop device with it all built-in, so it became an ultimate development platform. I was half expecting something along the lines ‘and we are offering this to you today for a discount of… ‘ – since it is was clearly a very nice device that was being shown to the crowd, the spec we now know is:
- Acer Laptop
- Dual core Celeron U2300 chip
- 2 Gig of ram,
- 250 gig hard disk,
- win 7 ultimate 64 bit, preinstalled with office 2010 beta.
- Tablet style PC with touch screen
- 1366×768 resolution, Intel GMA 4500 MHD graphics
- Webcam, 3G Sim Card support, HDMI output, built-in memory card reader.
It manages to score 3.2 on the Windows Experience Index, which is pretty impressive for a semi netbook style laptop, the score is understandably pegged by the graphics performance.
What I did not expect and the hordes went wild at, was the fact that he said – “today we are giving you one of these laptops, for free” – queue complete madness.
But to be fair, there is a considerable amount of buzz from the announcements and features being demonstrated, I have to confess that they are not SQL / Data related which is of course my passion , but they are worth mentioning:
- Silverlight 4 – Entered beta today, and can be downloaded, release expected first half of 2010, so expect Mix010 to contain the release announcements on that.
- Silverlight 4 feature set for has been pumped up in all the key areas the technology was lacking, print support, context menus, access to media devices such as webcams and audio, drag / drop, rich text support, clipboard access,…. the list goes on.
- Office 2010 beta is now available and can be download, powerpivot (what was codenamed ‘Gemini’) is now available to all.
- Visual Studio 2010 beta is now available, which brings along a whole host of templates for all these new features.
- Sharepoint 2010 beta was released today and the integration between the development surface and the Sharepoint site looks to be a consistent story and got cheers from the Sharepoint developers in the audience. (I kind of feel sorry for the Sharepoint presenters and demos, they followed on the heels of Steve and then Scott, who had just made the most significant announcements for the conference and given away the best ‘goodie’ you could get, how can you follow that?
So did this leave anything for the database side of my passion?
Well yes, in a round about way, what is interesting is that the Silverlight is extending to include a trusted model which gives you a far wider access to the underlying OS and this starts bringing it into the realms of local data consumption. They have also allowed calls into the older COM object models to be made from within Silverlight when running in a full trusted mode, this means that technically, Silverlight can make direct calls into the database via the COM ADO libraries, instead of using the system.data namespace and using ADO.Net. Up until now there has been no availability for the platform to connect to a SQL server directly, but this provides a very round about way in which to do it.
That seems puzzling as to why you would allow that scenario but not give SL some form of direct access into the database itself – at the ask the experts session later in the day we posed the question as to whether a proper data access technology for connecting to SQL Server was being included and the answer indicated there would be something there to do it, but no specifics were mentioned. Also managed to spend some time in the big room and chat to some of the SQL guys at the booth as well as the patterns and practices team. I want to get a chance to go chat more to the SQL Azure team but will have to wait until tomorrow to fit that in.
As promised, I wanted to only blog about the bits of the PDC that relate to SQL / Database / Data Services, and not every session within the PDC that I am attending. Many of the sessions have been interesting, but I am viewing them with my Architect’s hat on, and not from the viewpoint of my personal passion for SQL Server. I feel fortunate to be here and listening to the speakers and chatting to them offline instead of watching the PDC on the released videos after the event.
The keynote today contained a number of very interesting looking prospects on the data side of the fence, primarily ‘compered’ by Ray Ozzie, Chief Software Architect at Microsoft. There were also some demos, some of which were quite good, whilst others suffered from over-scripting. I am sure twitter was going wild at times during the keynote as people were giving real-time feedback about what they thought. (Whether that is a good thing or not I am not sure, walking off stage to find a few hundred bad reviews can not be nice.) But this is not about the demos but about the SQL / Data stuff.
A lot of work Microsoft have been doing and the phrase repeated throughout was ‘3 screens and a cloud’, using the 3 screens of mobile, computer and tv to represent 3 different delivery paradigms, but fundamentally using the same technology stack to deliver all 3.
The Azure data centres were announced to be going into production on Jan 1st 2010, and billing for those services will commence on the 1st Feb. However, the European and far eastern data centres were not listed as coming online until late in 2010, so the only data centres that will be up and running will be the Chicago and San Antonio data centres.
This may not seem a big problem, and in fact having 3 pair’s of data centres around the world is far more ideal and a single centralised resource, but for Europeans there are data protection laws in place that prohibit the movement of personal data outside of the bounds of Europe. In effect, you may not move the data into another jurisdiction where the data laws remove the legal protection the data subject owns. So from a data angle, it will be more interesting when the Dublin / Amsterdam data centre comes online in 2010, at which point storing data in the Azure cloud has a better data protection story.
SQL Azure has clearly been ‘beefed’ up and can now be connected to via SQL Server Management Studio just like a normal database, and be administered / interacted with – even supporting transactions. The disaster recovery and physical administration of the SQL remains out of sight and handled by the cloud, and not the application / vendor. SQL Azure understands TDS, so connecting to the SQL Azure is pretty seamless and appears like a regular SQL server. It has clearly matured as a platform, and rightly so.
Another project, codenamed ‘Dallas’ was announced which forms part of pinpoint. Pinpoint is a products / services portal, which instantly made me think of Apple’s ‘AppStore’ but for windows products and companies offering services. The interesting part is the ‘Dallas’ section, which is something like a ‘Data Store’ – allowing the discovery and consumption of centralised data services.
There has always been an issue when consuming data from other sources, that you are required to download it, understand the schema of the data and often ETL it from the format it is being supplied in, such as CSV, XML, Atom etc into a format that you can work with. Each data source often has its own schema and delivery mechanism and handling updates to the data remains an operational issue.
With ‘Dallas’ you are buying into the data being held within the cloud and it will auto-generate the proxy class for the data being consumed, so the schema of the data is available to you within code on the development side. This is an awesome concept and if they can tie in some form of micro-payment structure, you could easily visualise a set of data services that you consume within an application on an as needed basis. Without the micro-payments, you would have to have purchased a license, whether that is a one off cost, or a monthly subscription, neither deals with the ‘elastic’ nature of the applications that are being placed onto the cloud and one of the key benefits in that the data centres can scale up / down as your apps require. Given the billing of that is based on usage and you specifically want to take advantage of the elasticity of the infrastructure provision, it would make sense to have a similar elasticity in the data service charging arena.
This is definitely a technology to keep a close eye on, and I will be signing up an account to get access to the free data services that they are going to expose.
I am at the PDC this week in Los Angeles – the session selection is massive and covers a wide range of topics, although there are only a few sessions involving SQL Server. I think this is a testament to how important the PASS conference is now for SQL Server.
If I fit a SQL session in I will blog about it, or any major announcements, but I will try keep this purely about SQL since that is my passion.
PDC is a great technology event as well as a good networking event, so shoot me a comment if you want to try locate me in the mass of people roaming the halls.
I have mentioned query parameterization before and the process by which SQL extracts literal values from a query and re-writes the query in effect to use parameters to get a query plan cache hit, which negates the need to recompile the plan costing both time and CPU. There are a lot of good articles and book chapters that cover the topic.
What has confused me for a while is witnessing date literals within a query being parameterized on one query and not on another, even though both databases have parameterization set to ‘Simple’ mode. When a date literal is not parameterized the chances of getting a query plan cache hit is obviously very low which has performance impacts. The problem to date has been that I had been unable to ascertain the commonality and requirements that allowed the query to get parameterized and when it just kept the literal. I was paying too much attention to the query and as it turns out not enough to the table.
Well, after a number of hours getting to know the brick wall very well I finally tracked it down, and oddly it had nothing to do with the query I was submitting, but I could reproduce reliably by using an unrelated non-clustered index, which is confusing to say the least and I can not yet think of any reason why, is it a bug or just ‘Weird and Odd’.
The following steps reproduce the issue in both SQL Server 2005 and 2008.
Create a database, default settings, nothing specific, then the second step is to create our test table, a simple structure is suffice.
CREATE TABLE [dbo].[paramtest]( [id] [int] IDENTITY(1,1) NOT NULL, [somedate] [datetime] NOT NULL, [somefield] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_paramtest] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
We need some data to work against just to make sure we are selecting results, and we can insert these trivially as follows:
insert into [paramtest] values (getdate(),'a') go 10000
So we now have 10k rows within the table, and a clustered primary index on the identity column.
The test starts with freeing up the procedure cache, and then running the select statement, the datetime I used was roughly in the middle of the range of values I had inserted but is not a deciding factor in the query plan results.
dbcc freeproccache select * from paramtest where somedate > '2009-11-12 21:14:50.000'
Using a standard query plan cache extraction query the specific line of the xml plan we are interested in is the SQL Statement.
<StmtSimple StatementText="(@1 varchar(8000))SELECT * FROM [paramtest] WHERE [somedate]>@1" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.0379857" StatementEstRows="3831.48" StatementOptmLevel="TRIVIAL">
From it you can see the literal varchar value was extracted as @1 with a type of varchar(8000) and the query altered to use this parameter – this is exactly the behaviour we would expect from parameter sniffing.
Next step is to create a non-clustered index on the varchar ‘somefield’ – completely unrelated to the date literal being used, and should have no impact on the query at all.
CREATE NONCLUSTERED INDEX [ix_test] ON [dbo].[paramtest] ([somefield] ASC )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
Free the procedure cache up again and rerun the query
dbcc freeproccache select * from paramtest where somedate > '2009-11-12 21:14:50.000'
Extract the query plan again from the cache, but this time it is noticable different, the parameterisation has not occurred. The literal has
<StmtSimple StatementText="select * from paramtest where somedate > '2009-11-12 21:14:50.000'" StatementId="1" StatementCompId="1" StatementType="SELECT" />
To revert to the old plan, drop the index and clear the cache again, then run the query once more.
DROP INDEX [ix_test] ON [dbo].[paramtest] WITH (ONLINE = OFF)
Then clear the cache again and run the query
select * from paramtest where somedate > '2009-11-12 21:14:50.000'
And we are back to being parameterized.
So the application of a single non-clustered index on a separate field to the one being queried is preventing the simple parameterization mode from parameter sniffing the date literal – this makes absolutely no sense, and you can play around with it a lot more knowing what it causing the effect on the query plan. Even placing the additional non-clustered index on the identity field, which already has a clustered index results in the parameterization failing. If this behaviour is be design, then it makes for an interesting design or limitation on the parameterization.
As soon as the database is in ‘Forced’ parameterization mode, the literal was converted each time, so this looks specific to simple mode, but is not explainable, just demonstratable.
Non-clustered indexes normally have a ‘tipping point’, which is the point at which the query engine decides to change strategies from seeking the index with a nested loop operator back to a seek on the underlying table or choosing to just scan the underlying table and ignore the index. Kimberley Tripp wrote a great article about ‘The Tipping Point‘ , and the guidance is at about the 25-33% the query engine will change strategies.
If the non-clustered index is a covering index (it contains all the fields within the query) the query engine does not take the same decision – it makes sense that if any change in strategy occurs, it would have to be at a far higher figure, and as we are about to see, it will not take that decision and tip.
To test what strategy the engine would use I created a test situation of 2 separate tables, with different page counts, due to the padding column forcing the second table to use far more pages (5953 pages vs 9233)
CREATE TABLE [dbo].[tblIxTest1]( [PersonnelID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [char](30) NULL, [LastName] [char](30) NULL, [Department] [char](30) NULL, [SomePadding] [char](10) NULL ) ON [PRIMARY]
CREATE TABLE [dbo].[tblIxTest2]( [PersonnelID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [char](30) NULL, [LastName] [char](30) NULL, [Department] [char](30) NULL, [SomePadding] [char](1000) NULL ) ON [PRIMARY]
Next step was to insert some data, I needed random data to be able to ensure the index was not unbalanced in some way, so I broke out my useful little random string generation function. I should mention how to create this, a SQL function will not directly support the inclusion of a Rand() call within them, any attempt to do this results in the error:
Msg 443, Level 16, State 1, Procedure test, Line 13 Invalid use of a side-effecting operator 'rand' within a function.
However, there is nothing stopping a view from using this, and the function from using the view to get around the limitation:
Create View [dbo].[RandomHelper] as Select Rand() as r
And then the function can be generated to use this, it is not necessarily the most efficient random string generation function, but it works nicely.
CREATE FUNCTION [dbo].[RandomString] (@Length int) RETURNS varchar(100) WITH EXECUTE AS CALLER AS BEGIN DECLARE @Result Varchar(100) SET @Result = '' DECLARE @Counter int SET @Counter = 0 WHILE @Counter <= @Length BEGIN SET @Result = @Result + Char(Ceiling((select R from randomhelper) * 26) + 64) SET @Counter = @Counter + 1 END RETURN(@Result) END
This now allows me to generate random data and insert it into the tables to get a nice data distribution, and this was run for both of the tables.
insert into tblIxTest1 values (dbo.RandomString(20),dbo.RandomString(20),dbo.RandomString(20),'') go 1000000
Two NC indexes are now needed, one for each table and both are identical and cover just the FirstName and PersonnelID fields within the table.
CREATE NONCLUSTERED INDEX [IX_Test1] ON [dbo].[tblIxTest1] ( [FirstName] ASC, [PersonnelID] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE NONCLUSTERED INDEX [IX_Test2] ON [dbo].[tblIxTest2] ( [FirstName] ASC, [PersonnelID] ASC )WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
The setup is complete and it is pretty easy to now show the NC covering index is not going to tip, the most extreme where clause is where I am allowing every record to be returned:
select personnelid , firstname from tblixtest1 where firstname >= 'a' and firstname <= 'zzzzzzzzzzzzzzzzzzzzz'
This still produces a query plan with a seek strategy, regardless of which of my two tables it was executed on:
select personnelid , firstname from tblixtest1 where firstname >= 'a' and firstname <= 'zzzzzzzzzzzzzzzzzzzzz' |--Index Seek(OBJECT:([FilteredIndexTest].[dbo].[tblIxTest1].[IX_Test1]), SEEK:([FilteredIndexTest].[dbo].[tblIxTest1].[FirstName] >= [@1] AND [FilteredIndexTest].[dbo].[tblIxTest1].[FirstName] <= [@2]) ORDERED FORWARD)
If we just select the entire table, unsurprisingly at that point it chooses to perform an index scan.
select personnelid , firstname from tblixtest1
Results in the following plan:
select personnelid , firstname from tblixtest1 |--Index Scan(OBJECT:([FilteredIndexTest].[dbo].[tblIxTest1].[IX_Test1]))
The row counts on both queries were identical at 1 million. Slightly more interesting is that if I use a Like clause instead of a direct string evaluation, the behaviour alters slightly when selecting all the values:
select personnelid , firstname from tblixtest1 where firstname like '[a-z]%'
Gives the query plan:
select personnelid , firstname from tblixtest1 where firstname like '[a-z]%' |--Index Scan(OBJECT:([FilteredIndexTest].[dbo].[tblIxTest1].[IX_Test1]), WHERE:([FilteredIndexTest].[dbo].[tblIxTest1].[FirstName] like '[a-z]%'))
So the query engine is potentially making an optimisation that it knows the like clause covers 100% and adopts an automatic scan, but it is not really very clear why it has this optimisation path. If the like clause changes to [a-y] then it reverts back to a seek, so it looks specific to covering all the values within the like statement. If a between statement is used, it remains a seek regardless.
So the result is that a Non-clustered covering index is very unlikely to tip, you either have to not give it a where clause, or use a like statement across all the values available, it will steadfastly refuse to seek and choose the scan.
Well the I/O cost of the operation remains the same, it has to read every page in the table and it considered the cost of traversing the B-Tree negligible, so the difference between seek and scan is not very great. Running the seek based query and scan based query in the same batch the relative percentages are 48% vs 52% – that is the scan scoring 52% even though they read the same number of rows.
Outputting the IO statistics when they are run side by side shows the same number of pages being read, but the seek is clearly being favoured and is slightly faster as far as SQL is concerned – it is quite weird to consider a seek of an entire index is more efficient than a scan of the index.
(1000000 row(s) affected) Table 'tblIxTest1'. Scan count 1, logical reads 5995, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (1000000 row(s) affected) Table 'tblIxTest1'. Scan count 1, logical reads 5995, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
So if you come across a covering index in a query plan that is scanning, it would be worth investigating as to whether it is intended. The chances are more likely the index field order is not supporting the predicates being used, than engine has chosen to tip the index like it would for the non-covering non-clustered indexes.