March 17, 2010 at 10:39 am
yes that's correct there is no primary key. The main purpose of this table is too quickly load data from the source system. Aside from the defaults there are no constraints, PK, FK, unique or clustered indexes.
March 17, 2010 at 11:04 am
Do you think that you could grab that Statistics XML plan and post it please? As I mentioned, I mixed the two profiler events up, and the showplan doesn't have the necessary info.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2010 at 6:51 am
The easiest way to fix the problem is to make the index clustered instead of non-clustered. This would definitely solve the problem.
If it for some reason is impossible to change the index you will have to use some kind of hinting to force the SQL server to a specific execution plan.
There are many possible ways to solve the problem using hints:
* You can force it to use a specific named index
* You can force a recompile every time the query is executed
* You can use the OPTIMIZE FOR hint to optimize the query for a specific value
* If it is impossible to add a hint you might have to use a plan guide instead
* Write a stored procedure that returns the desired data instead of entering the SELECT statement directly in the SSIS data source. Add any necessary hints to the stored procedure.
My guess is that the reason you are getting this problem is that the distribution of the values in the table depends very much on the specific value you are looking for. I guess that there is at least one value that has very many entries in the table. The reason the SQL server chooses a table scan is because it has to generate an execution plan that works well for any variable value.
/SG
March 18, 2010 at 7:07 am
stefan.gustafsson 60897 (3/18/2010)
The easiest way to fix the problem is to make the index clustered instead of non-clustered. This would definitely solve the problem.
:blink: You want to create a clustered index on an NVARCHAR(50) column that contains GUIDs? :blink:
My guess is that the reason you are getting this problem is that the distribution of the values in the table depends very much on the specific value you are looking for.
Sort of. The optimizer makes a guess at cardinality (in this case) because it cannot 'sniff' the value of the local variable. Based on that, it estimates that a table scan will be cheaper than using the index. As I showed in my demonstration script earlier in the thread, if the data is stored as a UNIQUEIDENTIFIER (fixed size 16 bytes) rather than NVARCHAR(50) (variable size up to 100 bytes plus 2 bytes fixed overhead), the table scan is assessed as being more expensive, and the index is used.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 18, 2010 at 7:14 am
stefan.gustafsson 60897 (3/18/2010)
The easiest way to fix the problem is to make the index clustered instead of non-clustered. This would definitely solve the problem.
It would fix it, but GUIDs are bad for clustered indexes and a GUID stored as a unicode string is even worse.
If it for some reason is impossible to change the index you will have to use some kind of hinting to force the SQL server to a specific execution plan.
Maybe, but hints are a last resort. It may well be possible to fix this without explicitly forcing an index (which could cause more problems than it solves if the hint isn't tested and evaluated carefully)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2010 at 7:19 am
Paul White (3/18/2010)
stefan.gustafsson 60897 (3/18/2010)
The easiest way to fix the problem is to make the index clustered instead of non-clustered. This would definitely solve the problem.:blink: You want to create a clustered index on an NVARCHAR(50) column that contains GUIDs? :blink:
Sure, why not ? The only possible ill effect is that it might slow down insertions to the table slightly. This will only be a problem if he is really inserting very large amounts of data and the insertion performance is very critical.
My guess is that the reason you are getting this problem is that the distribution of the values in the table depends very much on the specific value you are looking for.
Sort of. The optimizer makes a guess at cardinality (in this case) because it cannot 'sniff' the value of the local variable. Based on that, it estimates that a table scan will be cheaper than using the index. As I showed in my demonstration script earlier in the thread, if the data is stored as a UNIQUEIDENTIFIER (fixed size 16 bytes) rather than NVARCHAR(50) (variable size up to 100 bytes plus 2 bytes fixed overhead), the table scan is assessed as being more expensive, and the index is used.
Not really.
The reason your example worked was not that you used UNIQUEIDENTIFIER instead of string, it was that you had a unique index and you had no duplicates in the table.
If you use a non-unique non-clustered index and you insert a large number of duplicates of a specific value you will get the same problem as the original poster had.
March 18, 2010 at 7:24 am
stefan.gustafsson 60897 (3/18/2010)
Sure, why not ? The only possible ill effect is that it might slow down insertions to the table slightly. This will only be a problem if he is really inserting very large amounts of data and the insertion performance is very critical.
Tom Van Harpen (3/17/2010)
The main purpose of this table is too quickly load data from the source system.
Guid cluster will also cause massive fragmentation that will slow down range-scans of the table and increase its size.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2010 at 7:36 am
GilaMonster (3/18/2010)
stefan.gustafsson 60897 (3/18/2010)
Sure, why not ? The only possible ill effect is that it might slow down insertions to the table slightly. This will only be a problem if he is really inserting very large amounts of data and the insertion performance is very critical.Tom Van Harpen (3/17/2010)
The main purpose of this table is too quickly load data from the source system.Guid cluster will also cause massive fragmentation that will slow down range-scans of the table and increase its size.
True, but the situation is not much worse than with the unclustered index. Having an unclustered index on a guid will also cause index fragmentation and slow down range scans using the index.
Of course it is slightly worse to have a fragmented clustered index than a fragmented non-clustered index but the difference is usually not so big.
Index fragmentation is no big deal. It is easy to add a maintenance job that rebuilds all indexes for a table regularly for example with:
ALTER INDEX ALL ON schema.table REBUILD
Anyway, I do not think you will ever have range scans with GUID:s. Looking up a specific value will work perfectly even if the index is heavily fragmented.
The only thing in my opinion that might make a clustered index bad is if the insertion performance would be too bad. The only way to find out is to test.
/SG
March 18, 2010 at 7:38 am
stefan.gustafsson 60897 (3/18/2010)
Sure, why not ? The only possible ill effect is that it might slow down insertions to the table slightly.
Slow insertions, poor page fillfactors, poor scan performance, fragmentation, extra logging (page splits), bloated non-clustered indexes...the list goes on. Clustering on a non-sequential GUID, particularly one that wide, is a truly terrible idea.
The reason your example worked was not that you used UNIQUEIDENTIFIER instead of string, it was that you had a unique index and you had no duplicates in the table.
Smaller key size = more rows per index page = lower logical I/O. Given good statistics, the optimizer usually makes the right choice.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 18, 2010 at 7:40 am
stefan.gustafsson 60897 (3/18/2010)
Anyway, I do not think you will ever have range scans with GUID:s. Looking up a specific value will work perfectly even if the index is heavily fragmented.
Sure you can. Anytime a full clustered index scan happens or if querying a single value that has lots and lots of matching rows (across lots of pages). Sure, single-row seeks are unaffected by fragmentation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2010 at 7:51 am
stefan.gustafsson 60897 (3/18/2010)
The reason your example worked was not that you used UNIQUEIDENTIFIER instead of string, it was that you had a unique index and you had no duplicates in the table.
Just retested with 256 duplicates of every GUID (50 unique overall) index changed to non-unique. Index seek plus key lookup. 😛
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 18, 2010 at 7:52 am
GilaMonster (3/18/2010)
stefan.gustafsson 60897 (3/18/2010)
Anyway, I do not think you will ever have range scans with GUID:s. Looking up a specific value will work perfectly even if the index is heavily fragmented.Sure you can. Anytime a full clustered index scan happens or if querying a single value that has lots and lots of matching rows (across lots of pages)
Anyway, there are really a limited number of options availabke to fix this kind of problem:
1) Use a clustered index
2) Make the non-clustered index covering (Inclkude all columns necessary for this query in the index)
3) Use some kind of hinting
4) Redesign the system
All alteratives have their own drawbacks.
Which alternative to choose depends on the uniqe requirements for this application.
So far you have said that he should not use a clustered index and he should use hinting only as a last resort.
I find it is more helpful to suggest solutions rather than saying what not to do.
March 18, 2010 at 7:57 am
stefan.gustafsson 60897 (3/18/2010)
I find it is more helpful to suggest solutions rather than saying what not to do.
I'll be very happy to give a solution once the actual execution plan (which I've requested from the OP) is posted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 18, 2010 at 7:57 am
Paul White (3/18/2010)
stefan.gustafsson 60897 (3/18/2010)
The reason your example worked was not that you used UNIQUEIDENTIFIER instead of string, it was that you had a unique index and you had no duplicates in the table.Just retested with 64 duplicates of every GUID, index changed to non-unique. Index seek plus key lookup. 😛
Test again with 50000 unique GUID:s and a single GUID with 50000 copies.
In this case if you use a hardcoded value it will use index lookup for one of the single guid:s and it will use table scan if you use the duplicated guid.
If you use a local variable you will get a table scan and if you use a parameter you will get either a table scan or a lookup depending on parameter sniffing.
If you enter a OPTION RECOMPILE you will always get the correct plan.
(At least that is what I got on my SQL 20008)
March 18, 2010 at 8:00 am
stefan.gustafsson 60897 (3/18/2010)
Test again with 50000 unique GUIDs and a single GUID with 50000 copies.
Now you are just being silly.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 16 through 30 (of 50 total)
You must be logged in to reply to this topic. Login to reply