January 29, 2013 at 8:44 am
Hi,
I executed the following query:
SELECT
[Extent1].[ExerciseYear] AS [ExerciseYear],
[Extent1].[Scenario] AS [Scenario],
[Extent3].[Enterprise] AS [Enterprise],
[Extent2].[Period] AS [Period],
[Extent1].[Period] AS [Period1],
[Extent4].[Model] AS [Model],
[Extent3].[Name] AS [Name],
[Extent1].[Blocked] AS [Blocked],
[Extent1].[Delivered] AS [Delivered],
[Extent1].[Validated] AS [Validated],
[Extent1].[Processed] AS [Processed],
[Extent1].[IsDefault] AS [IsDefault],
[Extent1].[Version] AS [Version]
FROM
[dbo].[XmlDataModel] AS [Extent1]
INNER JOIN [dbo].[Period] AS [Extent2] ON ([Extent1].[Id_Model] = [Extent2].[Id_Model]) AND ([Extent1].[Id_Enterprise] = [Extent2].[Id_Enterprise])
LEFT OUTER JOIN [dbo].[Enterprise] AS [Extent3] ON [Extent1].[Id_Enterprise] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[Model] AS [Extent4] ON [Extent1].[Id_Model] = [Extent4].[Id]
WHERE [Extent1].[ExerciseYear] = 2013
and SQL Server suggested and index to remove the index scan on the XmlDataModel table (this table has 105.000 rows and 46G of data)...
Looking at the plan I decided to create other indexes to try and remove the other scans, for example, the Model table is joined on XmlDataModel by the ID column and has the Model column on the output so I decided to create an index with ID and include Model... but SQL still makes scans.. on the new index but still scans....
CREATE INDEX IDX_Enterprise_ID_001 ON Enterprise (ID) INCLUDE (Enterprise, Name) WITH (PAD_INDEX = ON, FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
CREATE INDEX IDX_Model_ID_001 ON Model (ID) INCLUDE (Model) WITH (PAD_INDEX = ON, FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
CREATE INDEX IDX_Period_001 ON Period (Id_Model, Id_Enterprise) INCLUDE (Period) WITH (PAD_INDEX = ON, FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
CREATE INDEX IDX_XmlDataModel_ExerciseYear ON [XmlDataModel] ([ExerciseYear]) INCLUDE ([Id_Model], [Id_Enterprise], [Scenario], [Period], [Processed], [Validated], [Blocked], [Delivered], [IsDefault], [Version])
WITH (PAD_INDEX = ON, FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
The execution plan after index creation is also in the attachments...
Will "surfing" the net I came across an article that had some demos over AdventureWorks database and in SQL 2005 an index scan was turned into a seek doing indexes like I did here... but the article said to try it on SQL 2008 and we would have a surprise.... And so I did have... the same indexes on SQL 2005 were a seek but SQL 2008 returned a scan....
How can I turn those scans into seeks (if possible...)?
Thanks,
Pedro
January 29, 2013 at 9:32 am
How many rows in the table? How many rows are you returning? If you are returning more than say 25%* of the rows in the table, SQL Server may calculate a scan as cheaper than tons of seeks.
*don't quote me on this. Try it on your table.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 29, 2013 at 9:37 am
ChrisM@Work (1/29/2013)
How many rows in the table? How many rows are you returning? If you are returning more than say 25%* of the rows in the table, SQL Server may calculate a scan as cheaper than tons of seeks.*don't quote me on this. Try it on your table.
The 3 aux tables are returning 100%.... so it's faster a scan than seeks since it need all the data...
Thanks,
Pedro
January 29, 2013 at 9:53 am
ChrisM@Work (1/29/2013)
How many rows in the table? How many rows are you returning? If you are returning more than say 25%* of the rows in the table, SQL Server may calculate a scan as cheaper than tons of seeks.*don't quote me on this. Try it on your table.
0.1%-1%.
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
January 29, 2013 at 12:21 pm
The real key to performance is the proper clustered index.
If you (almost) always query [dbo].[XmlDataModel] by [ExerciseYear], then cluster by [ExerciseYear] followed by any other column(s) you (almost) always query on as long as the size stays reasonable (if you need other nonclus indexes on the table: often, once you get the correct clus index, you can get rid of a bunch of other indexes).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 29, 2013 at 12:56 pm
It has many more queries over other columns but I'll check the DMV for the most used indexes and see the one that's best for cluster since the actual one is the ID (guid... bad!!!).
Developers still don't understand the difference between clustered index and PK...
Thanks,
Pedro
January 29, 2013 at 1:26 pm
PiMané (1/29/2013)
It has many more queries over other columns but I'll check the DMV for the most used indexes and see the one that's best for cluster since the actual one is the ID (guid... bad!!!).Developers still don't understand the difference between clustered index and PK...
Thanks,
Pedro
True; but developers are not the only ones.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 29, 2013 at 2:13 pm
GilaMonster (1/29/2013)
ChrisM@Work (1/29/2013)
How many rows in the table? How many rows are you returning? If you are returning more than say 25%* of the rows in the table, SQL Server may calculate a scan as cheaper than tons of seeks.*don't quote me on this. Try it on your table.
0.1%-1%.
Blimey, I was miles out! Thanks for the correction, Gail.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 29, 2013 at 2:19 pm
ChrisM@home (1/29/2013)
GilaMonster (1/29/2013)
ChrisM@Work (1/29/2013)
How many rows in the table? How many rows are you returning? If you are returning more than say 25%* of the rows in the table, SQL Server may calculate a scan as cheaper than tons of seeks.*don't quote me on this. Try it on your table.
0.1%-1%.
Blimey, I was miles out! Thanks for the correction, Gail.
Depends on the row size too. For very wide rows, you may get up to 5% or possibly even around 20% with specific row sizes and requests.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply