May 31, 2010 at 3:23 am
I have following Table structure:
CREATE TABLE [dbo].[Contents](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[UserId] [bigint] NULL,
[TenantId] [int] NULL,
[Title] [nvarchar](256) NULL,
[FormatId] [int] NULL,
[ThumbImageURL] [nvarchar](1000) NULL,
[ContentTypeId] [int] NULL,
[CategoryId] [int] NULL,
[ProductionDate] [datetime] NULL CONSTRAINT [DF_Contents_ProductionDate] DEFAULT (getdate()),
[ProducedBy] [nvarchar](256) NULL,
[Summary] [nvarchar](4000) NULL,
[Description] [nvarchar](4000) NULL,
[IsDisabled] [bit] NULL CONSTRAINT [DF_Contents_IsDisabled] DEFAULT ((0)),
[IsBanned] [bit] NULL CONSTRAINT [DF_Contents_IsBanned] DEFAULT ((0)),
[AddedDateTime] [datetime] NULL CONSTRAINT [DF_Contents_AddedDateTime] DEFAULT (getdate()),
[UpdatedDateTime] [datetime] NULL CONSTRAINT [DF_Contents_UpdatedDateTime] DEFAULT (getdate()),
[TargetIndustry] [nvarchar](256) NULL,
[Copyrights] [nvarchar](1000) NULL,
[TotalRating] [float] NULL,
[Sizeinbytes] [bigint] NULL,
[LicenceInformation] [nvarchar](1000) NULL,
[Tag] [nvarchar](4000) NULL,
[HasAttachments] [bit] NULL CONSTRAINT [DF_Contents_HasAttachments] DEFAULT ((0)),
[IsScorm] [bit] NULL CONSTRAINT [DF_Contents_IsScorm] DEFAULT ((0)),
[IsPublic] [bit] NULL,
[UserFName] [nvarchar](255) NULL,
[UserLName] [nvarchar](255) NULL,
[IsImport] [bit] NULL CONSTRAINT [DF_Contents_IsImport] DEFAULT ((0)),
[ContentStatusId] [int] NULL,
[ContentGroupId] [bigint] NULL,
[IsLive] [bit] NULL,
[Size] [bigint] NULL,
[ContentProviderInfoId] [bigint] NULL,
[ContentAccessMode] [tinyint] NULL,
CONSTRAINT [PK_ENTopic] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[Contents] WITH NOCHECK ADD CONSTRAINT [FK_Contents_ExtendedServiceProvider] FOREIGN KEY([ContentProviderInfoId])
REFERENCES [dbo].[ContentProviderInfo] ([Id])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[Contents] NOCHECK CONSTRAINT [FK_Contents_ExtendedServiceProvider]
GO
ALTER TABLE [dbo].[Contents] WITH CHECK ADD CONSTRAINT [FK_SCSFContents_SCSFContentFormat] FOREIGN KEY([FormatId])
REFERENCES [dbo].[ContentFormat] ([Id])
GO
ALTER TABLE [dbo].[Contents] CHECK CONSTRAINT [FK_SCSFContents_SCSFContentFormat]
GO
ALTER TABLE [dbo].[Contents] WITH CHECK ADD CONSTRAINT [FK_SCSFContents_SCSFContentType] FOREIGN KEY([ContentTypeId])
REFERENCES [dbo].[ContentType] ([Id])
GO
ALTER TABLE [dbo].[Contents] CHECK CONSTRAINT [FK_SCSFContents_SCSFContentType]
By running following query I got some missing index detials about this table as below:
SELECT * FROM sys.dm_db_missing_index_details order by 7
Here is the suggested index list returned for this table:
The list is in excel sheet attached. I need help on how to rely on these indexes, How much they are correct and which one should I follow (create index) for improving the Performance (Select/Insert/update, we are not deleting - very rare to delete)?
Shamshad Ali.
June 1, 2010 at 1:34 am
I always suggest to support all FK with indexes, unless proven they hurt a specific case. Then only remove/disable (but document ! ) the hurting one if that case occurs frequently.
You need to keep in mind, the missing indexes suggested by the dmv still need to be evaluated.
(it only uses the info gathered after startup of your instance)
Don't just create the suggested indexes !
Evaluate them. (it may suggest very similar indexes !)
You'll have to analyse them yourself and only implement the one(s) that make sence to you, knowing your systems needs.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 1, 2010 at 9:51 am
The missing indexes engine (and Database Tuning Advisor) will create a bunch of BAD indexes if you allow it. Massive amounts of included columns, overlaps, etc. Your best solution BY FAR is to hire a performance tuning professional to do an indexing strategy session with your database application(s) while mentoring you on how to do the same.
If you fail to take my advice you may be like the several clients I have had over the last few years where I have spent HUNDREDS of manhours cleaning up the index crap they created by using those two systems and taking everything suggested.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 2, 2010 at 6:24 am
i would say
1 run the exec plan.
2 capture the scans and lookup there.
3 modify/create indexes.
4 again run the exec plan to see the improvement.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 2, 2010 at 6:54 am
There are 210 tables, 24 views, 280 SPs, 50 functions with DB size of 8 GB. I am looking for a quick way to figureout the indexes issues. Each table has only one clustered index, nothing else. How do I do this quickly to identify the proper indexes? I can search each table with foreign key and apply non clustered index individually. Or I have to check dependency and open the SPs script and see the query then test in execution plan by giving some parameters then tune it with indexes? Please suggest what to do?
Shamshad Ali
June 2, 2010 at 8:06 am
Regarding the FK indexes: http://www.sqlservercentral.com/scripts/Indexing/61391/
For the rest all I can say is: "With quick only comes dirty !!"
Capture your workload, and analyse it (maybe using Db Engine tuning advisor)
Only then you'll be able to create indexes that are needed.
Keep in mind an index can be a double bladed knife.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 2, 2010 at 11:34 pm
Shamshad Ali (6/2/2010)
There are 210 tables, 24 views, 280 SPs, 50 functions with DB size of 8 GB. I am looking for a quick way to figureout the indexes issues. Each table has only one clustered index, nothing else. How do I do this quickly to identify the proper indexes? I can search each table with foreign key and apply non clustered index individually. Or I have to check dependency and open the SPs script and see the query then test in execution plan by giving some parameters then tune it with indexes? Please suggest what to do?Shamshad Ali
you can find missing indexes by
SELECT DISTINCT
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (s.user_seeks + s.user_scans),0) ,
avg_user_impact ,
TableName = statement ,
[name] ,i.type_desc,
[index_size_in_MB] = (sum(a.total_pages) * 8) / 1024.00, -- Pages are 8 Bytes in size
[records_in_index]= sum(CASE WHEN a.type = 1 THEN p.rows ELSE 0 END), -- Only count the rows once
u.user_seeks,
u.user_scans,
u.user_lookups,
u.user_updates,
[EqualityUsage] = equality_columns,
[InequalityUsage] = inequality_columns,
[Include Cloumns] = included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
INNER JOIN sys.indexes i ON d.[object_id] = i.[object_id]
INNER JOIN sys.dm_db_index_usage_stats u ON d.[object_id] = u.[object_id]
INNER JOIN sys.partitions p ON i.[object_id] = p.[object_id]
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY
g.index_group_handle,
g.index_handle,
s.avg_total_user_cost,
s.avg_user_impact,
s.user_seeks,
s.user_scans,
u.user_lookups,
u.user_updates,
u.user_seeks,u.user_scans,
i.type_desc,
i.name,
d.statement,
d.equality_columns,
d.inequality_columns,
d.included_columns;
and for long running queries
select * from sys.dm_tran_database_transactions and also see link
http://www.sqlservercentral.com/scripts/Maintenance+and+Management/30908/
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 3, 2010 at 2:01 am
Thank you all for helping me solving my problem. My personal experience says there is no need for FKeys to have non-cluster index. From the performance point of view there is no improvement at all. I am confused now and i am looking each query and trying to see in profiler with values of Read/write/CPU/Duration columns and applying indexes and again checking if there is any performance gain. while the same was test on applying indexes on FKeys without any performance improvements (reads/CPU/Duration) No change.
If there is any one who can technically advise me at this point?
Also the system is new and there is no any live activity on the application so unless this goes on production I can't test with profiler unless i use it any web Load test environment and then apply engine tunning advisor. as Engine Tunning advisor tool results lots of only statistics to create etc. I mean CREATE STATISTICS
Shamshad Ali.
June 3, 2010 at 2:11 am
Shamshad Ali (6/3/2010)
while the same was test on applying indexes on FKeys without any performance improvements (reads/CPU/Duration) No change.
that depends on the volume of data.i guess you don't have high volume data in test server.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply