February 19, 2018 at 10:38 am
We use a program for EDI in our company. One of the screens shows us a list of sent documents. It is super slow.
I can make a change in the WHERE clause of the query and add AND seg_id = 1 and it runs super fast. Why is that?
Also, is there another index that would improve performance for the query when pulling all of the columns of the table with a WHERE clause of WHERE DateSent >= @dateVariable AND DateSent <= @dateVariable
Here is table DDL:
CREATE TABLE [dbo].[Sent](
[Sent_ID] [int] NOT NULL,
[Seg_ID] [int] NOT NULL,
[TP_PartID] [nvarchar](30) NULL,
[ICN] [nvarchar](20) NULL,
[GCN] [nvarchar](20) NULL,
[TCN] [nvarchar](20) NULL,
[DocType] [nvarchar](3) NULL,
[Segment] [ntext] NULL,
[DateSent] [smalldatetime] NULL,
[DocStatus] [nvarchar](1) NULL,
[DocRef] [nvarchar](50) NULL,
CONSTRAINT [PK_Sent] PRIMARY KEY CLUSTERED
(
[Sent_ID] ASC,
[Seg_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] TEXTIMAGE_ON [PRIMARY]
February 19, 2018 at 12:10 pm
First thing is to make sure you have the best clustering index on the table. Then we'll look to see if additional indexes or code changes or other things are needed. Please run the query below, which will show missing index and current index usage stats, and post both results.
SET NOCOUNT ON;
SET DEADLOCK_PRIORITY -8;
IF OBJECT_ID('tempdb.dbo.#index_specs') IS NOT NULL
DROP TABLE dbo.#index_specs
IF OBJECT_ID('tempdb.dbo.#index_missing') IS NOT NULL
DROP TABLE dbo.#index_missing
IF OBJECT_ID('tempdb.dbo.#index_usage') IS NOT NULL
DROP TABLE dbo.#index_usage
CREATE TABLE dbo.#index_specs (
object_id int NOT NULL,
index_id int NOT NULL,
min_compression int NULL,
max_compression int NULL,
drive char(1) NULL,
alloc_mb decimal(9, 1) NOT NULL,
alloc_gb AS CAST(alloc_mb / 1024.0 AS decimal(9, 2)),
used_mb decimal(9, 1) NOT NULL,
used_gb AS CAST(used_mb / 1024.0 AS decimal(9, 2)),
rows bigint NULL,
table_mb decimal(9, 1) NULL,
table_gb AS CAST(table_mb / 1024.0 AS decimal(9, 2)),
size_rank int NULL,
approx_max_data_width bigint NULL,
UNIQUE CLUSTERED ( object_id, index_id )
)
DECLARE @list_missing_indexes bit
DECLARE @list_missing_indexes_summary bit
DECLARE @include_schema_in_table_names bit
DECLARE @table_name_pattern sysname
DECLARE @order_by smallint --1=table_name; 2=size; -2=size DESC;.
DECLARE @format_counts smallint --1=with commas, no decimals; 2/3=with K=1000s,M=1000000s, with 2=0 dec. or 3=1 dec. places;.
DECLARE @debug smallint
--NOTE: showing missing indexes can take some time; set to 0 if you don't want to wait.
SET @list_missing_indexes = 1
SET @list_missing_indexes_summary = 0
SET @include_schema_in_table_names = 1
SET @table_name_pattern = 'sent'
SET @order_by = 1
SET @format_counts = 3
SET @debug = 0
PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)
DECLARE @is_compression_available bit
DECLARE @sql varchar(max)
IF CAST(SERVERPROPERTY('ProductVersion') AS varchar(30)) LIKE '9%'
OR (CAST(SERVERPROPERTY('Edition') AS varchar(40)) NOT LIKE '%Developer%' AND
CAST(SERVERPROPERTY('Edition') AS varchar(40)) NOT LIKE '%Enterprise%')
SET @is_compression_available = 0
ELSE
SET @is_compression_available = 1
SET @sql = '
INSERT INTO #index_specs ( object_id, index_id,' +
CASE WHEN @is_compression_available = 0 THEN '' ELSE '
min_compression, max_compression,' END + '
alloc_mb, used_mb, rows )
SELECT
base_size.object_id,
base_size.index_id, ' +
CASE WHEN @is_compression_available = 0 THEN '' ELSE '
base_size.min_compression,
base_size.max_compression,' END + '
(base_size.total_pages + ISNULL(internal_size.total_pages, 0)) / 128.0 AS alloc_mb,
(base_size.used_pages + ISNULL(internal_size.used_pages, 0)) / 128.0 AS used_mb,
base_size.row_count AS rows
FROM (
SELECT
dps.object_id,
dps.index_id, ' +
CASE WHEN @is_compression_available = 0 THEN '' ELSE '
ISNULL(MIN(p.data_compression), 0) AS min_compression,
ISNULL(MAX(p.data_compression), 0) AS max_compression,' END + '
SUM(dps.reserved_page_count) AS total_pages,
SUM(dps.used_page_count) AS used_pages,
SUM(CASE WHEN dps.index_id IN (0, 1) THEN dps.row_count ELSE 0 END) AS row_count
FROM sys.dm_db_partition_stats dps ' +
CASE WHEN @is_compression_available = 0 THEN '' ELSE '
INNER JOIN sys.partitions p /* WITH (NOLOCK)*/ ON
p.partition_id = dps.partition_id ' END + '
WHERE dps.object_id > 100
GROUP BY
dps.object_id,
dps.index_id
) AS base_size
LEFT OUTER JOIN (
SELECT
it.parent_id,
SUM(dps.reserved_page_count) AS total_pages,
SUM(dps.used_page_count) AS used_pages
FROM sys.internal_tables it /* WITH (NOLOCK)*/
INNER JOIN sys.dm_db_partition_stats dps /* WITH (NOLOCK)*/ ON
dps.object_id = it.parent_id
WHERE it.internal_type IN ( ''202'', ''204'', ''211'', ''212'', ''213'', ''214'', ''215'', ''216'' )
GROUP BY
it.parent_id
) AS internal_size ON base_size.index_id IN (0, 1) AND internal_size.parent_id = base_size.object_id
'
IF @debug >= 1
PRINT @sql
EXEC(@sql)
UPDATE [is]
SET approx_max_data_width = index_cols.approx_max_data_width
FROM #index_specs [is]
INNER JOIN (
SELECT index_col_ids.object_id, index_col_ids.index_id,
SUM(CASE WHEN c.max_length = -1 THEN 16 ELSE c.max_length END) AS approx_max_data_width
FROM (
SELECT ic.object_id, ic.index_id, ic.column_id
--,object_name(ic.object_id)
FROM sys.index_columns ic
WHERE
ic.object_id > 100
UNION
SELECT i_nonclus.object_id, i_nonclus.index_id, ic_clus.column_id
--,object_name(i_nonclus.object_id)
FROM sys.indexes i_nonclus
CROSS APPLY (
SELECT ic_clus2.column_id
--,object_name(ic_clus2.object_id),ic_clus2.key_ordinal
FROM sys.index_columns ic_clus2
WHERE
ic_clus2.object_id = i_nonclus.object_id AND
ic_clus2.index_id = 1 AND
ic_clus2.key_ordinal > 0 --technically superfluous, since clus index can't have include'd cols anyway
) AS ic_clus
WHERE
i_nonclus.object_id > 100 AND
i_nonclus.index_id > 1
) AS index_col_ids
INNER JOIN sys.columns c ON c.object_id = index_col_ids.object_id AND c.column_id = index_col_ids.column_id
GROUP BY index_col_ids.object_id, index_col_ids.index_id
) AS index_cols ON index_cols.object_id = [is].object_id AND index_cols.index_id = [is].index_id
UPDATE ispec
SET table_mb = ispec_ranking.table_mb,
size_rank = ispec_ranking.size_rank
FROM #index_specs ispec
INNER JOIN (
SELECT *, ROW_NUMBER() OVER(ORDER BY table_mb DESC, rows DESC, OBJECT_NAME(object_id)) AS size_rank
FROM (
SELECT object_id, SUM(alloc_mb) AS table_mb, MAX(rows) AS rows
FROM #index_specs
GROUP BY object_id
) AS ispec_allocs
) AS ispec_ranking ON
ispec_ranking.object_id = ispec.object_id
IF @list_missing_indexes = 1
BEGIN
SELECT
IDENTITY(int, 1, 1) AS ident,
DB_NAME(mid.database_id) AS Db_Name,
CONVERT(varchar(10), GETDATE(), 120) AS capture_date,
ispec.size_rank, ispec.table_mb,
CASE WHEN @format_counts = 1 THEN REPLACE(CONVERT(varchar(20), CAST(dps.row_count AS money), 1), '.00', '')
WHEN @format_counts = 2 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix
WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS int) AS varchar(20)) + ca1.row_count_suffix
WHEN @format_counts = 3 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix
WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS decimal(14, 1)) AS varchar(20)) + ca1.row_count_suffix
ELSE CAST(dps.row_count AS varchar(20)) END AS row_count,
CASE WHEN @include_schema_in_table_names = 1 THEN OBJECT_SCHEMA_NAME(mid.object_id) + '.'
ELSE '' END + OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,
mid.equality_columns, mid.inequality_columns, mid.included_columns,
user_seeks, user_scans, cj1.max_days_active, unique_compiles,
last_user_seek, last_user_scan,
CAST(avg_total_user_cost AS decimal(9, 2)) AS avg_total_user_cost,
CAST(avg_user_impact AS decimal(9, 2)) AS [avg_user_impact%],
system_seeks, system_scans, last_system_seek, last_system_scan,
CAST(avg_total_system_cost AS decimal(9, 2)) AS avg_total_system_cost,
CAST(avg_system_impact AS decimal(9, 2)) AS [avg_system_impact%],
mid.statement, mid.object_id, mid.index_handle
INTO #index_missing
FROM sys.dm_db_missing_index_details mid /*WITH (NOLOCK)*/
CROSS JOIN (
SELECT DATEDIFF(DAY, create_date, GETDATE()) AS max_days_active FROM sys.databases /*WITH (NOLOCK)*/ WHERE name = 'tempdb'
) AS cj1
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)
CROSS APPLY (
SELECT CASE WHEN dps.row_count >= 1000000 THEN 'M' WHEN dps.row_count >= 1000 THEN 'K' ELSE '' END AS row_count_suffix
) AS ca1
OUTER APPLY (
SELECT ispec.table_mb, ispec.size_rank
FROM dbo.#index_specs ispec
WHERE
ispec.object_id = mid.object_id AND
ispec.index_id IN (0, 1)
) AS ispec
WHERE
1 = 1
AND mid.database_id = DB_ID()
AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern
AND OBJECT_NAME(mid.object_id) NOT LIKE 'tmp%'
ORDER BY
--avg_total_user_cost * (user_seeks + user_scans) DESC,
Db_Name,
CASE WHEN @order_by IN (-2, 2) THEN ispec.size_rank * -SIGN(@order_by) ELSE 0 END,
Table_Name,
equality_columns, inequality_columns,
user_seeks DESC
SELECT *
FROM #index_missing
ORDER BY ident
IF @list_missing_indexes_summary = 1
BEGIN
SELECT
derived.Size_Rank, derived.table_mb,
derived.Table_Name, derived.Equality_Column, derived.Equality#, derived.User_Seeks,
ISNULL((SELECT SUM(user_seeks)
FROM #index_missing im2
CROSS APPLY DBA.dbo.DelimitedSplit8K (inequality_columns, ',') ds
WHERE im2.Size_Rank = derived.Size_Rank AND
LTRIM(RTRIM(ds.Item)) = derived.Equality_Column
), 0) AS Inequality_Seeks,
derived.User_Scans, derived.Last_User_Seek, derived.Last_User_Scan,
derived.Max_Days_Active, derived.Avg_Total_User_Cost, derived.Approx_Total_Cost
FROM (
SELECT
Size_Rank, MAX(table_mb) AS table_mb, Table_Name, LTRIM(RTRIM(ds.Item)) AS Equality_Column,
SUM(user_seeks) AS User_Seeks, SUM(user_scans) AS User_Scans,
MAX(last_user_seek) AS Last_User_Seek, MAX(last_user_scan) AS Last_User_Scan,
MIN(max_days_active) AS Max_Days_Active,
MAX(avg_total_user_cost) AS Avg_Total_User_Cost,
(SUM(user_seeks) + SUM(user_scans)) * MAX(avg_total_user_cost) AS Approx_Total_Cost,
MAX(ds.ItemNumber) AS Equality#
FROM #index_missing
CROSS APPLY DBA.dbo.DelimitedSplit8K (equality_columns, ',') ds
WHERE equality_columns IS NOT NULL
GROUP BY size_rank, Table_Name, LTRIM(RTRIM(ds.Item))
) AS derived
ORDER BY Size_Rank, Table_Name, Approx_Total_Cost DESC
END --IF
END --IF
PRINT 'Index Usage Stats @ ' + CONVERT(varchar(30), GETDATE(), 120)
-- list index usage stats (seeks, scans, etc.)
SELECT
IDENTITY(int, 1, 1) AS ident,
DB_NAME() AS db_name,
--ispec.drive AS drv,
ispec.size_rank, ispec.alloc_mb - ispec.used_mb AS unused_mb,
CASE WHEN @include_schema_in_table_names = 1 THEN OBJECT_SCHEMA_NAME(i.object_id /*, DB_ID()*/) + '.'
ELSE '' END + OBJECT_NAME(i.object_id /*, i.database_id*/) AS Table_Name,
CASE WHEN @format_counts = 1 THEN REPLACE(CONVERT(varchar(20), CAST(dps.row_count AS money), 1), '.00', '')
WHEN @format_counts = 2 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix
WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS int) AS varchar(20)) + ca1.row_count_suffix
WHEN @format_counts = 3 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix
WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS decimal(14, 1)) AS varchar(20)) + ca1.row_count_suffix
ELSE CAST(dps.row_count AS varchar(20)) END AS row_count,
ispec.table_gb, ispec.alloc_gb AS index_gb,
SUBSTRING('NY', CAST(i.is_primary_key AS int) + CAST(i.is_unique_constraint AS int) + 1, 1) +
CASE WHEN i.is_unique = CAST(i.is_primary_key AS int) + CAST(i.is_unique_constraint AS int) THEN ''
ELSE '.' + SUBSTRING('NY', CAST(i.is_unique AS int) + 1, 1) END AS [Uniq?],
REPLACE(i.name, oa1.table_name, '~') AS index_name,
--fc_row_count.formatted_value AS row_count,
i.index_id,
ispec.approx_max_data_width AS [data_width],
CAST(CAST(ispec.used_mb AS float) * 1024.0 * 1024.0 / NULLIF(dps.row_count, 0) AS int) AS cmptd_row_size,
key_cols AS key_cols,
LEN(nonkey_cols) - LEN(REPLACE(nonkey_cols, ',', '')) + 1 AS nonkey_count,
nonkey_cols AS nonkey_cols,
ius.user_seeks, ius.user_scans, --ius.user_seeks + ius.user_scans AS total_reads,
ius.user_lookups, ius.user_updates,
dios.leaf_delete_count + dios.leaf_insert_count + dios.leaf_update_count as leaf_mod_count,
dios.range_scan_count, dios.singleton_lookup_count,
DATEDIFF(DAY, STATS_DATE ( i.object_id , i.index_id ), GETDATE()) AS stats_days_old,
DATEDIFF(DAY, CASE
WHEN o.create_date > cj1.sql_startup_date AND o.create_date > o.modify_date THEN o.create_date
WHEN o.modify_date > cj1.sql_startup_date AND o.modify_date > o.create_date THEN o.modify_date
ELSE cj1.sql_startup_date END, GETDATE()) AS max_days_active,
dios.row_lock_count, dios.row_lock_wait_in_ms,
dios.page_lock_count, dios.page_lock_wait_in_ms,
ius.last_user_seek, ius.last_user_scan,
ius.last_user_lookup, ius.last_user_update,
fk.Reference_Count AS fk_ref_count,
i.fill_factor,
ius2.row_num,
CASE
WHEN ispec.max_compression IS NULL THEN '(Not applicable)'
WHEN ispec.max_compression = 2 THEN 'Page'
WHEN ispec.max_compression = 1 THEN 'Row'
WHEN ispec.max_compression = 0 THEN ''
ELSE '(Unknown)' END AS max_compression,
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,
GETDATE() AS capture_date
INTO #index_usage
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 /*WITH (NOLOCK)*/ WHERE name = 'tempdb'
) AS cj1
OUTER APPLY (
SELECT CASE WHEN EXISTS(SELECT 1 FROM #index_specs [is] WHERE [is].object_id = i.object_id AND [is].index_id = 1)
THEN 1 ELSE 0 END AS has_clustered_index
) AS cj2
LEFT OUTER JOIN dbo.#index_specs ispec ON
ispec.object_id = i.object_id AND
ispec.index_id = i.index_id
OUTER APPLY (
SELECT STUFF((
SELECT
', ' + COL_NAME(ic.object_id, ic.column_id)
FROM sys.index_columns ic /*WITH (NOLOCK)*/
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('')
), 1, 2, '')
) AS key_cols (key_cols)
OUTER APPLY (
SELECT STUFF((
SELECT
', ' + COL_NAME(ic.object_id, ic.column_id)
FROM sys.index_columns ic /*WITH (NOLOCK)*/
WHERE
ic.key_ordinal = 0 AND
ic.object_id = i.object_id AND
ic.index_id = i.index_id
ORDER BY
COL_NAME(ic.object_id, ic.column_id)
FOR XML PATH('')
), 1, 2, '')
) 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 /*WITH (NOLOCK)*/
WHERE
is_disabled = 0
GROUP BY
referenced_object_id
) AS fk ON
fk.referenced_object_id = i.object_id
LEFT OUTER JOIN (
SELECT *
FROM sys.dm_db_index_operational_stats ( DB_ID(), NULL, NULL, NULL )
) AS dios ON
dios.object_id = i.object_id AND
dios.index_id = i.index_id
OUTER APPLY (
SELECT OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name
--, CASE WHEN dps.row_count >= 1000000 THEN 'M' WHEN dps.row_count >= 1000 THEN 'K' ELSE '' END AS row_count_suffix
) AS oa1
CROSS APPLY (
SELECT CASE WHEN dps.row_count >= 1000000 THEN 'M' WHEN dps.row_count >= 1000 THEN 'K' ELSE '' END AS row_count_suffix
) AS ca1
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%'
)
ORDER BY
db_name,
CASE WHEN @order_by IN (-2, 2) THEN ispec.size_rank * -SIGN(@order_by) ELSE 0 END,
--ius.user_scans DESC,
--ius2.row_num, --user_scans&|user_seeks
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
SELECT *
FROM #index_usage
ORDER BY ident
PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)
SET DEADLOCK_PRIORITY NORMAL
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".
February 19, 2018 at 1:03 pm
Here is requested info in CSV format. That ok?
February 19, 2018 at 1:37 pm
Yep, that's fine. The results are interesting.
1) Do you often/very often search by a SentDate range?
2) Is a 1 year range typical for a date search? What % of rows is that?
If answer to #1 is yes, I'd consider clustering on SentDate instead. You could add Sent_ID and Seg_ID if you want to get a unique clus key.
You'd keep the same PK, just make it nonclus.
You'd have to decide if you need to add Sent_ID and/or Seg_ID to the DocType index.
To try this, the steps would be:
1) Drop index IX_Sent_01
2) Drop index IX_Sent_02
3) Drop constraint PK_Sent
4) Create clus index on SentDate [or on ( SendDate, Sent_ID, Seg_ID )].
5) Add constraint PK_Sent: ALTER TABLE dbo.Sent ADD CONSTRAINT PRIMARY KEY NONCLUSTERED ( Sent_ID, Seg_Id ) ...
6) Recreate the DocType index (IX_Sent_02).
Btw, you have a lot of clustered index scans on other tables in that query. You should probably review the indexes on those tables as well.
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".
February 19, 2018 at 2:08 pm
1. The form for searching sent docs is by date range, so pretty much any time someone uses that form. Pretty often.
2. A year is about 40K records or so. The table currently has about 22M records, so less than 1 percent.
I think i may have given you the wrong plan. I've made some updates to the query.
Here is more recent one.
February 19, 2018 at 2:45 pm
Wow, yeah, for sure cluster by DateSent if that's the case. The other conditions won't matter.
Btw, the other tables I was worried about being scanned, I had more time to look, and they are small anyway, so a scan on those doesn't matter (at least for now).
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".
February 19, 2018 at 3:15 pm
The primary key doesn't have to be clustered?
February 19, 2018 at 3:27 pm
Nope, it actually doesn't. By default SQL will make it so, but it doesn't have to be.
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".
February 19, 2018 at 4:14 pm
ScottPletcher - Monday, February 19, 2018 2:45 PMWow, yeah, for sure cluster by DateSent if that's the case. The other conditions won't matter.Btw, the other tables I was worried about being scanned, I had more time to look, and they are small anyway, so a scan on those doesn't matter (at least for now).
I'm not clear on the indexes i should end up with. No primary key, just a clustered index on DateSent?
February 19, 2018 at 5:36 pm
Jackie Lowery - Monday, February 19, 2018 4:14 PMScottPletcher - Monday, February 19, 2018 2:45 PMWow, yeah, for sure cluster by DateSent if that's the case. The other conditions won't matter.Btw, the other tables I was worried about being scanned, I had more time to look, and they are small anyway, so a scan on those doesn't matter (at least for now).
I'm not clear on the indexes i should end up with. No primary key, just a clustered index on DateSent?
You should have a primary key. It just doesn't need to be a Clustered primary key. If possible, the Clustered Index should be unique. You can do that by making the clustered index on DateSent and whatever column (hopefully, it's just one column) makes up the PK.
Also, from your original post, the following will NOT return a range of dates. It will only return data that has an exact match with the variable.
WHERE DateSent >= @dateVariable AND DateSent <= @dateVariable
--Jeff Moden
Change is inevitable... Change for the better is not.
February 20, 2018 at 1:25 am
Jackie Lowery - Monday, February 19, 2018 4:14 PMScottPletcher - Monday, February 19, 2018 2:45 PMWow, yeah, for sure cluster by DateSent if that's the case. The other conditions won't matter.Btw, the other tables I was worried about being scanned, I had more time to look, and they are small anyway, so a scan on those doesn't matter (at least for now).
I'm not clear on the indexes i should end up with. No primary key, just a clustered index on DateSent?
No, you definitely want to keep the PK, as it's used a lot for look ups as well. But you don't want to cluster on that, you want to cluster on DateSent.
I provided steps earlier, but wasn't really specific. I'll repeat the steps with specific commands here:
1) DROP INDEX IX_Sent_01 ON dbo.Sent;
2) DROP INDEX IX_Sent_02 ON dbo.Sent;
3) ALTER TABLE dbo.Sent DROP CONSTRAINT PK_Sent;
4) /* Add data compression if you'd like to and your SQL version supports it; change the filegroup from PRIMARY to some other filegroup if you need to. */
CREATE UNIQUE CLUSTERED INDEX [CL_Sent] ON dbo.Sent ( SentDate, Sent_ID, Seg_ID ) WITH ( /* DATA_COMPRESSION = PAGE, */ FILLFACTOR = 99, SORT_IN_TEMPDB = ON ) ON [PRIMARY];
Don't worry about using two extra ints in the PK. The total disk used will still be less and performance should be vastly better. You don't need a whole lot of nonclus indexes on this table anyway.
5) ALTER TABLE dbo.Sent ADD CONSTRAINT PRIMARY KEY NONCLUSTERED ( Sent_ID, Seg_Id ) WITH ( /* DATA_COMPRESSION = PAGE, */ FILLFACTOR = 99, SORT_IN_TEMPDB = ON ) ON [PRIMARY];
6) Recreate IX_Sent_02 / the DocType index. Sorry, I don't have the details for this one available right now, but you can script it out and re-run that script after the table has been reclustered.
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".
March 1, 2018 at 12:05 pm
Jeff Moden - Monday, February 19, 2018 5:36 PMAlso, from your original post, the following will NOT return a range of dates. It will only return data that has an exact match with the variable.
WHERE DateSent >= @dateVariable AND DateSent <= @dateVariable
However,
WHERE DateSent >= @dateVariable1 AND DateSent <= @dateVariable2
would deliver the rows within the range between the two different date variables, inclusive. Just don't use the same date variable (or value) for both comparisons.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply