Solved

SQL non clustered index

Posted on 2014-11-05
9
237 Views
Last Modified: 2014-11-05
Hi guys and girls,

Easy one for peoples who know SQL (MSSQL 2005).

Does creating non clustered index impact my sql query ?

I create a soft and it getting slower and slower when i run my sql query. I found a way to analyze my query and the result is that i should create non clustered index. I'm far from a pro in SQL so i dont know the impact on my code. Did i have to rewrite some query or just run the script MSSMS already give me ?

Thanks in advance !
0
Comment
Question by:RichDu
9 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 100 total points
Comment Utility
You can just run the script it gave you if you want - it might be helpful, but it might not.

Creating non-clustered indexes could speed up one query, but slow down performance elsewhere.

How important is your specific query. If it is one that is critical and needs to be the fastest thing in your database, go do it (but you might want to put the index code here first).

There may be alternative ways. For example, your query could be optimised instead. In theory, it could be good - but it might not be the best thing to do.
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>Does creating non clustered index impact my sql query ?
We suck at mind reading, so unless you post the T-SQL that defines 'my sql query', can't say for sure.

In general, SELECTS run much faster if the query 'covers' all columns that are used in indexes.   If not, then no.

INSERTS, UPDATES, and DELETES will all be slower with indexes, as a query doesn't just impact the table, it impacts every index.  

There are lots of articles here at EE and elsewhere on indexes, and covering it all is beyond the scope of a single EE question.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
For now, you can try adding the nonclus index.

If you want best performance overall, we need to review, at a minimum:
1) all existing indexes on the table;
2) any "missing indexes" reported by SQL Server.
If you want to do that, let me know.
0
 

Author Comment

by:RichDu
Comment Utility
There is the query i talk about

SELECT ItemsDossier.NombreItem, ItemsDossier.PrixItemNet, Portes.NomPorte FROM ItemsDossier INNER JOIN Portes ON
 (ItemsDossier.NumeroSoumission = '141031-01' AND Portes.NumeroSoumission = ItemsDossier.NumeroSoumission  AND
  ItemsDossier.Groupe = '012' AND Portes.GroupeQuinc = ItemsDossier.Groupe)

Open in new window


and there is the query MSSMS give me to make non clustered index

GO
CREATE NONCLUSTERED INDEX [groupeindex]
ON [dbo].[ItemsDossier] ([Groupe])
INCLUDE ([NumeroSoumission],[NombreItem],[PrixItemNet])
GO

Open in new window


The  only index i got on this table is PK_ItemsDossier (Clustered)
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Please change the db name to match your db, run the code, and post the results.  It's almost certain that better clustered indexes on the tables is what you really need.
 

USE [<your_db_name_here>]

SET DEADLOCK_PRIORITY LOW --probably irrelevant, but just in case

DECLARE @list_missing_indexes bit
DECLARE @table_name_pattern sysname

--NOTE: showing missing indexes can take some time; set to 0 if you don't want to wait.
SET @list_missing_indexes = 1
SET @table_name_pattern = 'ItemsDossier' --'%'=all tables.
--SET @table_name_pattern = '%'

PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)

IF @list_missing_indexes = 1
BEGIN
    SELECT
        GETDATE() AS capture_date,
        DB_NAME(mid.database_id) AS Db_Name,
        dps.row_count,
        OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,
        mid.equality_columns, mid.inequality_columns, mid.included_columns,
        user_seeks, user_scans, ca1.max_days_active, unique_compiles,
        last_user_seek, last_user_scan, avg_total_user_cost, avg_user_impact,
        system_seeks, system_scans, last_system_seek, last_system_scan, avg_total_system_cost, avg_system_impact,
        mid.statement, mid.object_id, mid.index_handle
    FROM sys.dm_db_missing_index_details mid WITH (NOLOCK)
    CROSS APPLY (
        SELECT DATEDIFF(DAY, create_date, GETDATE()) AS max_days_active FROM sys.databases WHERE name = 'tempdb'
    ) AS ca1
    LEFT OUTER JOIN sys.dm_db_missing_index_groups mig WITH (NOLOCK) ON
        mig.index_handle = mid.index_handle
    LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs WITH (NOLOCK) ON
        migs.group_handle = mig.index_group_handle
    LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
        dps.object_id = mid.object_id AND
        dps.index_id IN (0, 1)
    --order by
        --DB_NAME, Table_Name, equality_columns
    WHERE
        1 = 1
        AND mid.database_id = DB_ID() --only current db
        AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern
        --AND mid.object_id IN (OBJECT_ID('<table_name_1>'), OBJECT_ID('<table_name_2>'))
    ORDER BY
        --avg_total_user_cost * (user_seeks + user_scans) DESC,
        Db_Name, Table_Name, equality_columns, inequality_columns
END --IF

PRINT 'Midpoint @ ' + CONVERT(varchar(30), GETDATE(), 120)
-- list index usage stats (seeks, scans, etc.)
SELECT
    ius2.row_num, DB_NAME() AS db_name,
    CASE WHEN i.name LIKE ca2.table_name + '%'
         THEN '~' + SUBSTRING(i.name, LEN(ca2.table_name) + 1, 200)
         ELSE i.name END AS index_name,
    CASE WHEN i.is_unique = 0 THEN 'N' ELSE 'Y' END + '.' +
    CASE WHEN i.is_primary_key = 0 AND i.is_unique_constraint = 0 THEN 'N' ELSE 'Y' END AS [uniq?],
    ca2.table_name,
    i.index_id, --ius.user_seeks + ius.user_scans AS total_reads,
    dps.row_count,
    SUBSTRING(key_cols, 3, 8000) AS key_cols, SUBSTRING(nonkey_cols, 3, 8000) AS nonkey_cols,
    ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates,
    ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update,
    fk.Reference_Count AS fk_ref_count,
    DATEDIFF(DAY, CASE WHEN o.create_date > ca1.sql_startup_date THEN o.create_date
        ELSE ca1.sql_startup_date END, GETDATE()) AS max_days_active,
    FILEGROUP_NAME(i.data_space_id) AS filegroup_name,
    ius.system_seeks, ius.system_scans, ius.system_lookups, ius.system_updates,
    ius.last_system_seek, ius.last_system_scan, ius.last_system_lookup, ius.last_system_update
FROM sys.indexes i WITH (NOLOCK)
INNER JOIN sys.objects o WITH (NOLOCK) ON
    o.object_id = i.object_id
CROSS JOIN (
    SELECT create_date AS sql_startup_date FROM sys.databases WHERE name = 'tempdb'
) AS ca1
CROSS APPLY (
    SELECT OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name
) AS ca2
OUTER APPLY (
    SELECT
        ', ' + COL_NAME(object_id, ic.column_id)
    FROM sys.index_columns ic
    WHERE
        ic.key_ordinal > 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        ic.key_ordinal
    FOR XML PATH('')
) AS key_cols (key_cols)
OUTER APPLY (
    SELECT
        ', ' + COL_NAME(object_id, ic.column_id)
    FROM sys.index_columns ic
    WHERE
        ic.key_ordinal = 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        COL_NAME(object_id, ic.column_id)
    FOR XML PATH('')
) AS nonkey_cols (nonkey_cols)
LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
    dps.object_id = i.object_id AND
    dps.index_id = i.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius WITH (NOLOCK) ON
    ius.database_id = DB_ID() AND
    ius.object_id = i.object_id AND
    ius.index_id = i.index_id
LEFT OUTER JOIN (
    SELECT
        database_id, object_id, MAX(user_scans) AS user_scans,
        ROW_NUMBER() OVER (ORDER BY MAX(user_scans) DESC) AS row_num --user_scans|user_seeks+user_scans
    FROM sys.dm_db_index_usage_stats WITH (NOLOCK)
    WHERE
        database_id = DB_ID()
        --AND index_id > 0
    GROUP BY
        database_id, object_id
) AS ius2 ON
    ius2.database_id = DB_ID() AND
    ius2.object_id = i.object_id
LEFT OUTER JOIN (
    SELECT
        referenced_object_id, COUNT(*) AS Reference_Count
    FROM sys.foreign_keys
    WHERE
        is_disabled = 0
    GROUP BY
        referenced_object_id
) AS fk ON
    fk.referenced_object_id = i.object_id
WHERE
    i.object_id > 100 AND
    i.is_hypothetical = 0 AND
    i.type IN (0, 1, 2) AND
    o.type NOT IN ( 'IF', 'IT', 'TF', 'TT' ) AND
    (
     o.name LIKE @table_name_pattern AND
     o.name NOT LIKE 'dtprop%' AND
     o.name NOT LIKE 'filestream[_]' AND
     o.name NOT LIKE 'MSpeer%' AND
     o.name NOT LIKE 'MSpub%' AND
     --o.name NOT LIKE 'queue[_]%' AND
     o.name NOT LIKE 'sys%'
    )
    --AND OBJECT_NAME(i.object_id /*, DB_ID()*/) IN ('tbl1', 'tbl2', 'tbl3')
ORDER BY
    --row_count DESC,
    --ius.user_scans DESC,
    --ius2.row_num, --user_scans&|user_seeks
    db_name, table_name,
    -- list clustered index first, if any, then other index(es)
    CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END,
    key_cols

PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)


--NOTE: showing missing indexes can take some time; set to 0 if you don't want to wait.
SET @list_missing_indexes = 1
SET @table_name_pattern = 'Portes' --'%'=all tables.
--SET @table_name_pattern = '%'

PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)

IF @list_missing_indexes = 1
BEGIN
    SELECT
        GETDATE() AS capture_date,
        DB_NAME(mid.database_id) AS Db_Name,
        dps.row_count,
        OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,
        mid.equality_columns, mid.inequality_columns, mid.included_columns,
        user_seeks, user_scans, ca1.max_days_active, unique_compiles,
        last_user_seek, last_user_scan, avg_total_user_cost, avg_user_impact,
        system_seeks, system_scans, last_system_seek, last_system_scan, avg_total_system_cost, avg_system_impact,
        mid.statement, mid.object_id, mid.index_handle
    FROM sys.dm_db_missing_index_details mid WITH (NOLOCK)
    CROSS APPLY (
        SELECT DATEDIFF(DAY, create_date, GETDATE()) AS max_days_active FROM sys.databases WHERE name = 'tempdb'
    ) AS ca1
    LEFT OUTER JOIN sys.dm_db_missing_index_groups mig WITH (NOLOCK) ON
        mig.index_handle = mid.index_handle
    LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs WITH (NOLOCK) ON
        migs.group_handle = mig.index_group_handle
    LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
        dps.object_id = mid.object_id AND
        dps.index_id IN (0, 1)
    --order by
        --DB_NAME, Table_Name, equality_columns
    WHERE
        1 = 1
        AND mid.database_id = DB_ID() --only current db
        AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern
        --AND mid.object_id IN (OBJECT_ID('<table_name_1>'), OBJECT_ID('<table_name_2>'))
    ORDER BY
        --avg_total_user_cost * (user_seeks + user_scans) DESC,
        Db_Name, Table_Name, equality_columns, inequality_columns
END --IF

PRINT 'Midpoint @ ' + CONVERT(varchar(30), GETDATE(), 120)
-- list index usage stats (seeks, scans, etc.)
SELECT
    ius2.row_num, DB_NAME() AS db_name,
    CASE WHEN i.name LIKE ca2.table_name + '%'
         THEN '~' + SUBSTRING(i.name, LEN(ca2.table_name) + 1, 200)
         ELSE i.name END AS index_name,
    CASE WHEN i.is_unique = 0 THEN 'N' ELSE 'Y' END + '.' +
    CASE WHEN i.is_primary_key = 0 AND i.is_unique_constraint = 0 THEN 'N' ELSE 'Y' END AS [uniq?],
    ca2.table_name,
    i.index_id, --ius.user_seeks + ius.user_scans AS total_reads,
    dps.row_count,
    SUBSTRING(key_cols, 3, 8000) AS key_cols, SUBSTRING(nonkey_cols, 3, 8000) AS nonkey_cols,
    ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates,
    ius.last_user_seek, ius.last_user_scan, ius.last_user_lookup, ius.last_user_update,
    fk.Reference_Count AS fk_ref_count,
    DATEDIFF(DAY, CASE WHEN o.create_date > ca1.sql_startup_date THEN o.create_date
        ELSE ca1.sql_startup_date END, GETDATE()) AS max_days_active,
    FILEGROUP_NAME(i.data_space_id) AS filegroup_name,
    ius.system_seeks, ius.system_scans, ius.system_lookups, ius.system_updates,
    ius.last_system_seek, ius.last_system_scan, ius.last_system_lookup, ius.last_system_update
FROM sys.indexes i WITH (NOLOCK)
INNER JOIN sys.objects o WITH (NOLOCK) ON
    o.object_id = i.object_id
CROSS JOIN (
    SELECT create_date AS sql_startup_date FROM sys.databases WHERE name = 'tempdb'
) AS ca1
CROSS APPLY (
    SELECT OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name
) AS ca2
OUTER APPLY (
    SELECT
        ', ' + COL_NAME(object_id, ic.column_id)
    FROM sys.index_columns ic
    WHERE
        ic.key_ordinal > 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        ic.key_ordinal
    FOR XML PATH('')
) AS key_cols (key_cols)
OUTER APPLY (
    SELECT
        ', ' + COL_NAME(object_id, ic.column_id)
    FROM sys.index_columns ic
    WHERE
        ic.key_ordinal = 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        COL_NAME(object_id, ic.column_id)
    FOR XML PATH('')
) AS nonkey_cols (nonkey_cols)
LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
    dps.object_id = i.object_id AND
    dps.index_id = i.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius WITH (NOLOCK) ON
    ius.database_id = DB_ID() AND
    ius.object_id = i.object_id AND
    ius.index_id = i.index_id
LEFT OUTER JOIN (
    SELECT
        database_id, object_id, MAX(user_scans) AS user_scans,
        ROW_NUMBER() OVER (ORDER BY MAX(user_scans) DESC) AS row_num --user_scans|user_seeks+user_scans
    FROM sys.dm_db_index_usage_stats WITH (NOLOCK)
    WHERE
        database_id = DB_ID()
        --AND index_id > 0
    GROUP BY
        database_id, object_id
) AS ius2 ON
    ius2.database_id = DB_ID() AND
    ius2.object_id = i.object_id
LEFT OUTER JOIN (
    SELECT
        referenced_object_id, COUNT(*) AS Reference_Count
    FROM sys.foreign_keys
    WHERE
        is_disabled = 0
    GROUP BY
        referenced_object_id
) AS fk ON
    fk.referenced_object_id = i.object_id
WHERE
    i.object_id > 100 AND
    i.is_hypothetical = 0 AND
    i.type IN (0, 1, 2) AND
    o.type NOT IN ( 'IF', 'IT', 'TF', 'TT' ) AND
    (
     o.name LIKE @table_name_pattern AND
     o.name NOT LIKE 'dtprop%' AND
     o.name NOT LIKE 'filestream[_]' AND
     o.name NOT LIKE 'MSpeer%' AND
     o.name NOT LIKE 'MSpub%' AND
     --o.name NOT LIKE 'queue[_]%' AND
     o.name NOT LIKE 'sys%'
    )
    --AND OBJECT_NAME(i.object_id /*, DB_ID()*/) IN ('tbl1', 'tbl2', 'tbl3')
ORDER BY
    --row_count DESC,
    --ius.user_scans DESC,
    --ius2.row_num, --user_scans&|user_seeks
    db_name, table_name,
    -- list clustered index first, if any, then other index(es)
    CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END,
    key_cols

PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)

SET DEADLOCK_PRIORITY NORMAL
0
 

Author Comment

by:RichDu
Comment Utility
first part :

2014-11-05 11:35:46.247	Cadrofer	180878	ItemsDossier	[CatItem]	NULL	[NumeroSoumission], [DescriptItem]	668	0	20	64	2014-11-05 11:31:25.470	NULL	6.70952575666245	76.78	0	0	NULL	NULL	0	0	[Cadrofer].[dbo].[ItemsDossier]	245575913	5
2014-11-05 11:35:46.247	Cadrofer	180878	ItemsDossier	[EscompteItem]	NULL	[ItemJobId], [NumeroSoumission], [FournisItem]	114	0	20	45	2014-11-05 09:43:22.813	NULL	6.79178765459162	64.3	0	0	NULL	NULL	0	0	[Cadrofer].[dbo].[ItemsDossier]	245575913	400
2014-11-05 11:35:46.247	Cadrofer	180878	ItemsDossier	[Groupe]	NULL	[NumeroSoumission], [CatItem], [NombreItem], [SelectItem], [FiniItem], [FournisItem], [DescriptItem], [PrixItemNet]	25	0	20	8	2014-10-30 15:27:19.057	NULL	6.74242147439979	93.79	0	0	NULL	NULL	0	0	[Cadrofer].[dbo].[ItemsDossier]	245575913	2817
2014-11-05 11:35:46.247	Cadrofer	180878	ItemsDossier	[Groupe]	NULL	[ItemJobId], [NumeroSoumission], [DescriptItem], [NomPorte], [Billet]	453	0	20	184	2014-11-04 08:18:23.107	NULL	6.82380018402866	93.72	0	0	NULL	NULL	0	0	[Cadrofer].[dbo].[ItemsDossier]	245575913	874
2014-11-05 11:35:46.247	Cadrofer	180878	ItemsDossier	[Groupe]	NULL	[ItemJobId], [NumeroSoumission], [SelectItem], [DescriptItem]	48	0	20	32	2014-11-03 13:22:41.207	NULL	6.81256793586883	95.07	0	0	NULL	NULL	0	0	[Cadrofer].[dbo].[ItemsDossier]	245575913	731
2014-11-05 11:35:46.247	Cadrofer	180878	ItemsDossier	[Groupe]	NULL	[ItemJobId], [NumeroSoumission], [DescriptItem]	98	0	20	46	2014-11-05 10:40:08.033	NULL	6.79368178399131	96.43	0	0	NULL	NULL	0	0	[Cadrofer].[dbo].[ItemsDossier]	245575913	659
2014-11-05 11:35:46.247	Cadrofer	180878	ItemsDossier	[Groupe]	NULL	[NumeroSoumission], [CatItem], [NombreItem], [SelectItem], [FiniItem], [FournisItem], [DescriptItem], [PrixItemListe], [PrixItemNet], [EscompteItem], [EscompteQ], [Abrev], [CodeComplet], [Zero]	201	0	20	78	2014-11-05 11:32:12.123	NULL	6.73665213327062	92.17	0	0	NULL	NULL	0	0	[Cadrofer].[dbo].[ItemsDossier]	245575913	100
2014-11-05 11:35:46.247	Cadrofer	180878	ItemsDossier	[Groupe]	NULL	[NumeroSoumission], [NombreItem], [PrixItemNet], [NomPorte]	438	0	20	48	2014-11-05 08:52:24.003	NULL	6.70358018555084	96.72	0	0	NULL	NULL	0	0	[Cadrofer].[dbo].[ItemsDossier]	245575913	59
2014-11-05 11:35:46.247	Cadrofer	180878	ItemsDossier	[Groupe]	NULL	[NumeroSoumission], [CatItem], [NombreItem], [SelectItem], [FiniItem], [FournisItem], [DescriptItem]	3488	0	20	121	2014-11-05 11:06:47.110	NULL	6.73828697743228	93.77	0	0	NULL	NULL	0	0	[Cadrofer].[dbo].[ItemsDossier]	245575913	32
2014-11-05 11:35:46.247	Cadrofer	180878	ItemsDossier	[Groupe]	NULL	[NumeroSoumission], [CatItem], [NombreItem], [PrixItemNet]	3511	0	20	125	2014-11-05 11:06:46.377	NULL	9.03229413028653	73.06	0	0	NULL	NULL	0	0	[Cadrofer].[dbo].[ItemsDossier]	245575913	28
2014-11-05 11:35:46.247	Cadrofer	180878	ItemsDossier	[Groupe]	NULL	[NumeroSoumission], [NombreItem], [PrixItemNet]	11667	0	20	1302	2014-11-05 11:35:30.293	NULL	8.57730547975502	77.95	0	0	NULL	NULL	0	0	[Cadrofer].[dbo].[ItemsDossier]	245575913	24
2014-11-05 11:35:46.247	Cadrofer	180878	ItemsDossier	[Groupe]	NULL	[NumeroSoumission], [NomPorte]	2167	0	20	240	2014-11-05 11:35:29.793	NULL	6.72092708920129	96.73	0	0	NULL	NULL	0	0	[Cadrofer].[dbo].[ItemsDossier]	245575913	20
2014-11-05 11:35:46.247	Cadrofer	180878	ItemsDossier	[Groupe]	NULL	[NumeroSoumission], [NombreItem], [SelectItem], [FournisItem], [DescriptItem], [PrixItemNet]	1141	0	20	829	2014-11-05 11:33:08.827	NULL	6.81747818395362	94.05	0	0	NULL	NULL	0	0	[Cadrofer].[dbo].[ItemsDossier]	245575913	12
2014-11-05 11:35:46.247	Cadrofer	180878	ItemsDossier	[Groupe]	NULL	[ItemJobId], [NumeroSoumission]	10683	0	20	1238	2014-11-05 11:35:30.043	NULL	6.74762551304	97.79	0	0	NULL	NULL	0	0	[Cadrofer].[dbo].[ItemsDossier]	245575913	22
2014-11-05 11:35:46.247	Cadrofer	180878	ItemsDossier	[Groupe]	NULL	[ItemJobId], [NumeroSoumission], [SelectItem], [FournisItem], [DescriptItem]	1141	0	20	829	2014-11-05 11:33:08.873	NULL	6.84070370895363	93.74	0	0	NULL	NULL	0	0	[Cadrofer].[dbo].[ItemsDossier]	245575913	14
2014-11-05 11:35:46.247	Cadrofer	180878	ItemsDossier	[Groupe]	NULL	[ItemJobId], [NumeroSoumission], [CatItem], [NombreItem], [SelectItem], [FiniItem], [FournisItem], [DescriptItem], [Porte], [PrixItemListe], [PrixItemNet], [EscompteItem], [EscompteQ], [Abrev], [CodeComplet], [NomPorte], [Billet], [LivPorte], [Cadres], [Zero]	4408	0	20	149	2014-11-05 11:33:35.687	NULL	6.72814837051536	85.37	0	0	NULL	NULL	0	0	[Cadrofer].[dbo].[ItemsDossier]	245575913	7
2014-11-05 11:35:46.247	Cadrofer	180878	ItemsDossier	[Groupe]	[CatItem]	[NumeroSoumission], [NombreItem], [SelectItem], [FiniItem], [FournisItem], [DescriptItem], [PrixItemNet]	2	0	20	1	2014-10-24 08:12:06.870	NULL	6.76448841457603	93.81	0	0	NULL	NULL	0	0	[Cadrofer].[dbo].[ItemsDossier]	245575913	2819
2014-11-05 11:35:46.247	Cadrofer	180878	ItemsDossier	[Groupe]	[CatItem]	[NumeroSoumission], [NombreItem], [Porte], [PrixItemNet]	3511	0	20	126	2014-11-05 11:06:46.580	NULL	6.79085245232523	96.77	0	0	NULL	NULL	0	0	[Cadrofer].[dbo].[ItemsDossier]	245575913	30
2014-11-05 11:35:46.247	Cadrofer	180878	ItemsDossier	[Groupe]	[CatItem]	[ItemJobId], [NumeroSoumission]	201	0	20	110	2014-11-04 11:51:01.813	NULL	6.78331155422425	97.8	0	0	NULL	NULL	0	0	[Cadrofer].[dbo].[ItemsDossier]	245575913	861
2014-11-05 11:35:46.247	Cadrofer	180878	ItemsDossier	[Groupe]	[CatItem]	[ItemJobId], [NumeroSoumission], [NombreItem], [SelectItem], [FiniItem], [FournisItem], [DescriptItem], [PrixItemNet], [NomPorte], [Billet], [LivPorte]	1091	0	20	92	2014-11-05 07:23:28.973	NULL	6.78395715013944	89.67	0	0	NULL	NULL	0	0	[Cadrofer].[dbo].[ItemsDossier]	245575913	807

Open in new window


Second part:

5	Cadrofer	PK_ItemsDossier	Y.Y	ItemsDossier	1	180878	ItemJobId	NULL	6394	48979	0	20748	2014-11-05 07:24:20.940	2014-11-05 11:35:30.293	NULL	2014-11-05 11:35:30.043	NULL	20	PRIMARY	0	6	0	0	NULL	2014-11-02 01:32:27.530	NULL	NULL

Open in new window


Third is empty and las one is:

7	Cadrofer	PK_Portes	Y.Y	Portes	1	126629	PorteId	NULL	0	32594	0	5557	NULL	2014-11-05 11:35:30.217	NULL	2014-11-05 11:35:29.780	NULL	20	PRIMARY	0	4	0	0	NULL	2014-11-05 09:42:01.440	NULL	NULL

Open in new window

0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 400 total points
Comment Utility
Yep.  If you want to address all the performance issues at once, instead of having to constantly create and maintain "covering indexes", run the code below.

The nonclustered index on CatItem is optional, although I'd suggest creating it.

After 7+ days, re-run the queries above and see if SQL still shows any "missing indexes" on ItemsDossier.


------------------------------------------------------------------------------------------------------------------------
---- ItemsDossier Indexes ----------------------------------------------------------------------------------------------
ALTER TABLE ItemsDossier DROP CONSTRAINT PK_ItemsDossier
CREATE CLUSTERED INDEX CL_ItemsDossier ON ItemsDossier ( Groupe, NumeroSoumission ) WITH ( FILLFACTOR = 96 ) ON [PRIMARY]
ALTER TABLE ItemsDossier ADD CONSTRAINT PK_ItemsDossier PRIMARY KEY NONCLUSTERED ( ItemJobId ) WITH ( FILLFACTOR = 96 ) ON [PRIMARY]
--optional, depending on the importance of the CatItem lookup, but I suggest creating it
CREATE NONCLUSTERED INDEX IX_ItemsDossier ON ItemsDossier ( CatItem ) INCLUDE ( DescriptItem, NumeroSoumission ) WITH ( FILLFACTOR = 96 ) ON [PRIMARY]
---- Portes Indexes ----------------------------------------------------------------------------------------------------
ALTER TABLE Portes DROP CONSTRAINT PK_Portes
CREATE CLUSTERED INDEX CL_Portes ON Portes ( Groupe, NumeroSoumission ) WITH ( FILLFACTOR = 96 ) ON [PRIMARY]
------------------------------------------------------------------------------------------------------------------------
0
 

Author Closing Comment

by:RichDu
Comment Utility
Thanks !!!
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
You're welcome!

Btw, yes, creating a single non-clustered index is an "easy one", but properly identifying the best clustered index, which is critical for good performance, is not.  I've found that even most DBAs don't do that.  Most prefer to live by the false nursery rhyme-like saying of using identity as the "default" clustering key.  [Pure twaddle, but many believe it, including some "experts".]
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now