| Author |
Topic |
|
Peace2007
Posting Yak Master
239 Posts |
 Posted - 2010-06-29 : 03:19:00
|
| hi, I'm asked to define required indexes on tables of a database, which has only clustered indexes defined. Since I'm a newcomer, I don't have access to the main server so I cannot use profiler and DTA to find required indexes. I have only an instance of the DB and the related application. What's the best approach to find required indexes? Any suggestion would be appreciated |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-06-29 : 05:09:08
|
| So you're not able to run queries on the main server? If you do then Brent Ozars missing indexes-query can help you out: http://sqlserverpedia.com/wiki/Find_Missing_IndexesIf not then analyzing the execution plans of your stored procedures are probably your best bet...- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-06-29 : 05:17:04
|
| You need to run profiler to do a proper analysis. Ask the DBA to take traces for you. The missing index DMV's a decent place to start, but the indexes it recommends need to be checked, tested and verified. It's fond of recommending near-duplicate indexes and too wide indexes.--Gail ShawSQL Server MVP |
|
|
|
peace2010
Starting Member
8 Posts |
Posted - 2010-06-30 : 01:35:20
|
| Thank you Gila and Lumbago for your replies. for now, I have run a profiler and executed each view to see the DTS recommendations for indexes. I'll try your suggestions as well. |
|
|
|
peace2010
Starting Member
8 Posts |
Posted - 2010-06-30 : 02:01:22
|
| One more Q, I'm checking the application that uses the mentioned DB. In some tables, application lets user, use filter on each of fields and also a combination of the filters can be used as well. Is is good idea to define non-clustered indexes on each field separately? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-06-30 : 02:36:10
|
| There is no "yes" or "no" answer to that question...the correct answer is "it depends" and I guess this wasn't exactly what you were hoping for :) Indexing and performance tuning a database is an artform. What I normally do is to look at all the join columns and the filtering columns, then consider the selectivity of them (how many unique values compared to the total number of values), consider the performance gain compared to the cost of maintaining an index and then make a decision. Whether you should have several columns in the index or separate indexes depends on other queries and the order of the columns in the index. For example:You have a members table with Columns ID, Firstname, Lastname where ID is clustered primary key. If you have a query where you always filter on both Firstname and Lastname, a 2-column index (Lastname ASC, Firstname ASC) will perform best and this index will also perform well if you filter by Lastname only since Lastname is the first column in the index (given that selectivity is pretty good). A filter on Firstname with this index will not perform as well since Firstname is second in the index list and will cause an index scan, hence a second index *might* be a good idea but again this depends on the selectivity again and the maintenance cost of an extra index.- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-06-30 : 02:40:07
|
quote:
Is is good idea to define non-clustered indexes on each field separately?
Unless there is something very unusual about a table and its usage, it should never have more than 3-4 indexes on it.- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-06-30 : 03:15:40
|
quote:
Originally posted by peace2010
Is is good idea to define non-clustered indexes on each field separately?
Typically no. Index columns based on how they're used in queries. Indexing for the point of indexing is a waste of time and space.Might be worth a read: [url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/[/url][url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/[/url]--Gail ShawSQL Server MVP |
|
|
|
peace2010
Starting Member
8 Posts |
Posted - 2010-06-30 : 03:32:10
|
| Thanks both for your useful replies and linksit seems my main problem is that I dont have access to the main server and I have to work on an instance of it locally.I'm just asked to look at the tables, stored procedures and views then create index! :) |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-06-30 : 04:24:23
|
| Then ask whoever does have access to the server to run you some traces. Once you have those, you can work just fine on a copy of the databases.--Gail ShawSQL Server MVP |
|
|
|
peace2010
Starting Member
8 Posts |
Posted - 2010-06-30 : 04:45:08
|
| ok :)by the way, there is a stored procedure for inserting a record and in its execution plan I have a "clustered index seek" which costs 20% and have predicate like: Seek Keys[1]: Prefix: [DEM].[Base].[CatalogValue].CatalogValueID = Scalar Operator([DEM].[Base].[CatalogValue].[ParentID])the table is: CREATE TABLE [Base].[CatalogValue]( [CatalogValueID] [int] NOT NULL, [CatalogGroupID] [int] NOT NULL, [Title] [varchar](150) NOT NULL, [ParentID] [int] NULL, [IsActive] [tinyint] NOT NULL, [Description] [varchar](max) NULL, [InsertUser] [varchar](50) NOT NULL, [InsertDate] [datetime] NOT NULL, [UpdateUser] [varchar](50) NULL, [UpdateDate] [datetime] NULL, [ClientID] [bigint] NOT NULL, CONSTRAINT [PK_CatalogValue] PRIMARY KEY CLUSTERED ( [CatalogValueID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]is creating a nonclustered index including [DEM].[Base].[CatalogValue].CatalogValueID and ([DEM].[Base].[CatalogValue].[ParentID])help? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-06-30 : 09:29:13
|
| Please post query, any other indexes on that table and, if possible, save and upload the execution plan somewhere. [url]http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Is that query slow? (if so, how slow and how many rows is it affecting?) Not because of blocking or high waits, but because it's running a long time.Clustered index seek is just about as efficient as you can get, there's generally little reason to worry about that. I would not recommend the index on CatalogValueID, it's the clustered index key so an nonclustered index would be mostly redundant.--Gail ShawSQL Server MVP |
|
|
|
peace2010
Starting Member
8 Posts |
Posted - 2010-07-03 : 08:50:03
|
I'll go back to my previous question later. By the way, could you let me know if we should create nonclustered indexes on foreign keys? bcz, I see a few Clustered index scan on a foreign key which exists in table below: Name Owner Type Created_datetime-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- -----------------------EntityDossier dbo user table 2009-09-17 14:07:00.750 Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------RowID bigint no 8 no (n/a) (n/a) NULLDossierID bigint no 8 no (n/a) (n/a) NULLEntityID bigint no 8 no (n/a) (n/a) NULLAccountNumber varchar no 50 yes no yes SQL_Latin1_General_CP1256_CI_ASInsertUser varchar no 50 no no no SQL_Latin1_General_CP1256_CI_ASInsertDate datetime no 8 no (n/a) (n/a) NULLUpdateUser varchar no 50 yes no yes SQL_Latin1_General_CP1256_CI_ASUpdateDate datetime no 8 yes (n/a) (n/a) NULLClientID bigint no 8 no (n/a) (n/a) NULL Identity Seed Increment Not For Replication-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- -------------------RowID 1 1 0 RowGuidCol--------------------------------------------------------------------------------------------------------------------------------No rowguidcol column defined. Data_located_on_filegroup--------------------------------------------------------------------------------------------------------------------------------PRIMARY index_name index_description index_keys-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------DossierID nonclustered located on PRIMARY EntityID, DossierIDPK_EntityDossier clustered, unique, primary key located on PRIMARY RowID constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys-------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------FOREIGN KEY FK_EntityDossier_Dossier1 Cascade No Action Enabled Is_For_Replication DossierID REFERENCES DEM.Credit.Dossier (DossierID)FOREIGN KEY FK_EntityDossier_Entity No Action No Action Enabled Is_For_Replication EntityID REFERENCES DEM.Credit.Entity (EntityID)PRIMARY KEY (clustered) PK_EntityDossier (n/a) (n/a) (n/a) (n/a) RowID No foreign keys reference table 'Credit.EntityDossier', or you do not have permissions on referencing tables.No views with schema binding reference table 'Credit.EntityDossier'. and you can find enclosed the execution plan clustered index scan occurs in these lines of the SP:DECLARE EDossier_Cursor CURSOR FOR SELECT DossierID FROM Credit.EntityDossier ed WHERE ed.EntityID = @EntityID --AND ed.RoleID = 63 OPEN EDossier_Cursor FETCH NEXT FROM EDossier_Cursor INTO @EDossierID WHILE @@FETCH_STATUS = 0 sorry if I couldn't have narrowed down the problem enough |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-07-03 : 09:56:42
|
| Yes, you should generally index foreign keys.I don't see the exec plan anywhere.p.s. Why are you using a cursor? That alone is a likely cause of poor performance.--Gail ShawSQL Server MVP |
|
|
|
peace2010
Starting Member
8 Posts |
Posted - 2010-07-04 : 00:01:12
|
| Thank You Gail :)Sorry I had forgotten to attach the exec plan, however it seems the main problem is the CURSOR I'll talk to the developers to see if we can replace it with WHILEBy The way, should I create nonclustered index for each foreign key separately? PS. I don't see the attachment button! |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-07-04 : 05:43:47
|
| No, no, no, no!!! you've missed the point.It's not that cursor = bad and while = good. It's cursor = bad, while = bad, set-based (non row-by-row code) = good. You will get minimal if any gains from replacing the cursor with a while. You need to get the devs to rethink the entire thing and get rid of looping code in general. SQL is not good at doing things row by row.Yes, index each foreign key. That is, one index per foreign key on the columns involved in that foreign keyThere isn't one on this forum. The article I linked is from SQLServerCentral (where they do have the capabilities of uploading an attachment). Stick it somewhere else (file service) and link--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-04 : 05:58:38
|
| "there is a stored procedure for inserting a record and in its execution plan I have a "clustered index seek" "If the Sproc is inserting one row then [this might be a bit of a sweeping statement!] I don't think indexes are going to make any difference. An UPDATE with a WHERE has to find the row(s) first, and then UPDATE - so appropriate index will help - particularly if the WHERE columns do not include the Clustered Index columns.But an INSERT INTO MyTable ... VALUES ... is not going to be assisted by an index.It may be slow because it has loads of referential integrity checks to perform, or the indexes have not been rebuilt recently and are completely "out of shape". |
|
|
|
peace2010
Starting Member
8 Posts |
Posted - 2010-07-06 : 06:51:38
|
quote:
Yes, index each foreign key.That is, one index per foreign key on the columns involved in that foreign key
what if we have 20 foreign keys defined in a table?! |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-07-06 : 07:41:33
|
| Then consider creating 20 indexes, one on each foreign key, assuming those foreign keys are used in queries. If you know they're seldom used, don't index them, then monitor and check that indexes aren't necessary.It is pointless creating one index over the columns used in multiple foreign keys as, if you do, effectively only the foreign key that appears as the left-most column of the index can use that index for seeks.Order matters in an index, and an index on (a,b) is not equivalent to two indexes on (a) and (b)--Gail ShawSQL Server MVP |
|
|
|
peace2010
Starting Member
8 Posts |
Posted - 2010-07-06 : 07:56:10
|
| Thank You indeed Gail :) |
|
|
|
|
A Question on Index Tuning