June 6, 2018 at 8:57 am
The AuditTable contains data from October, 2013 through current. We want to create:
Audit2014Table
Audit2015Table
Audit2016Table
Audit2017Table
The field Transaction_time contains a date/time string YYYYMMDDHHMMSSSSS.
We can use: Select * into Audit2014Table from AuditTable where transaction_time < '2015-01-01'
Select * into Audit2015Table from AuditTable where transaction_time >= '2015-01-01 00:00:00.000' and transaction_time < '2016-01-01'
etc.
The problem is deleting the rows from AuditTable takes forever and hangs the sql server for other users.
Also, the new tables need indexes added to them. I'm concerned this will also hang the sql server for other users.
The sql server is under a heavy load throughout the day.
Thank you for any suggestions.
June 6, 2018 at 9:03 am
Break the job into smaller steps. Do a day (or an hour) at a time. Script it so that it runs without intervention overnight.
Thomas Rushton
blog: https://thelonedba.wordpress.com
June 6, 2018 at 9:12 am
Could the pre-2018 tables be loaded using a backup of the db on a separate server?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 6, 2018 at 10:33 am
What you've stated and shown is contradictory. Is transaction_time a "string", as you stated, or a datetime, as your code shows?
Assuming it's a datetime, you don't really need separate tables for each year. You just need to cluster the AuditTableHistory on transaction_time. Btw, that's almost certainly how the AuditTable should have been clustered anyway.
Trouble is, at this point, as you know, that sorting 1B+ rows will take huge amounts of time.
If you could post current DDL for the entire table that would be a huge help.
IF you apps don't normally need access to the AuditTable, I suggest a clean break. Script out the existing table and change that script to create a new "AuditTableNew", except that the clustering key in this new table will be/start with transaction_time. If the table has an ident, you can add ident to the clus key to make it unique if you prefer, although it's not really needed. Create a nonclus index on the ident, if any: the ident can still be the PK if it was before, it just will not be clustered.
Now copy in the last nn days of history you're most likely to need for app use / lookup. That's the tricky part! That's where the DDL is needed: to see if transaction_time is in any nonclus index now, and/or if the table has an ident.
When prepped, we'll eventually rename the current table to "AuditTableOld" and rename "AuditTableNew" to "AuditTable".
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".
June 6, 2018 at 10:45 am
ScottPletcher - Wednesday, June 6, 2018 10:33 AMWhat you've stated and shown is contradictory. Is transaction_time a "string", as you stated, or a datetime, as your code shows?Assuming it's a datetime, you don't really need separate tables for each year. You just need to cluster the AuditTableHistory on transaction_time. Btw, that's almost certainly how the AuditTable should have been clustered anyway.
Trouble is, at this point, as you know, that sorting 1B+ rows will take huge amounts of time.
If you could post current DDL for the entire table that would be a huge help.
IF you apps don't normally need access to the AuditTable, I suggest a clean break. Script out the existing table and change that script to create a new "AuditTableNew", except that the clustering key in this new table will be/start with transaction_time. If the table has an ident, you can add ident to the clus key to make it unique if you prefer, although it's not really needed. Create a nonclus index on the ident, if any: the ident can still be the PK if it was before, it just will not be clustered.
Now copy in the last nn days of history you're most likely to need for app use / lookup. That's the tricky part! That's where the DDL is needed: to see if transaction_time is in any nonclus index now, and/or if the table has an ident.
When prepped, we'll eventually rename the current table to "AuditTableOld" and rename "AuditTableNew" to "AuditTable".
Thank you.
/****** Object: Table [dbo].[AUDITTABLE] Script Date: 6/6/2018 12:42:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AUDITTABLE](
[TRANSACTION_TIME] [datetime] NULL,
[OBJECT_TYPE] [varchar](900) NULL,
[OBJECT_ID] [int] NULL,
[FIELD_NAME] [varchar](900) NULL,
[OLD_VALUE] [varchar](8000) NULL,
[NEW_VALUE] [varchar](8000) NULL,
[USER_CODE] [varchar](900) NULL,
[TRANSACTION_TYPE] [varchar](900) NULL,
[TRANSACTION_DATE] [varchar](900) NULL,
[SUBMITTER] [int] NULL,
[PATHOLOGIST] [int] NULL,
[PATIENT] [int] NULL,
[WORKSTATION] [int] NULL,
[VISIT] [int] NULL,
[WINDOW] [varchar](900) NULL,
[APPLICATION] [varchar](100) NULL,
[SUPEROBJECT_ID] [int] NULL,
[CASE] [int] NULL,
[AUDITINDEX] [int] NULL,
[CHECKSUM] [int] NULL,
[CLIENT_TIME] [varchar](1000) NULL,
[OLD_VALUE_EXT_X] [varchar](900) NULL,
[NEW_VALUE_EXT_X] [varchar](900) NULL,
[NEW_VALUE_TRUNCATED] [varchar](100) NULL,
[AuditText] [text] NULL,
[OLD_VALUE_EXT] [varchar](max) NULL,
[NEW_VALUE_EXT] [varchar](max) NULL
)
GO
June 6, 2018 at 11:26 am
You've got a field "TRANSACTION_TIME" as a date/time field - fair enough. But also a field "TRANSACTION_DATE" as a varchar(900)?
Thomas Rushton
blog: https://thelonedba.wordpress.com
June 6, 2018 at 11:32 am
Is(are) there any index(es) at all on this table? If not, you'll definitely want to "create a brand new table to insert into while the old table data is being processed". Can you handle lookups to this table going thru a view name, and not the actual table name?
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".
June 6, 2018 at 11:34 am
ThomasRushton - Wednesday, June 6, 2018 11:26 AMYou've got a field "TRANSACTION_TIME" as a date/time field - fair enough. But also a field "TRANSACTION_DATE" as a varchar(900)?
That is a very long date!
I would have expected the Transaction_Time column to have been NOT NULL, along with some of the other columns.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 6, 2018 at 11:41 am
Yes, nine of the columns are indexed. I don't think Transaction_date is used.
I like the idea of using a view for lookups.
June 6, 2018 at 12:07 pm
Need to see the definitions of indexes too, especially for the clustered index (or pk, as that will be the clustering key unless you explicitly say it isn't).
You can change the scripting Options to include indexes by going to:
Tools menu bar
Options...
SQL Server Object Explorer
Scripting
Script Indexes
Set to "True"
Btw, nine indexes is far too many for this type of table.
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".
June 6, 2018 at 12:19 pm
/****** Object: Table [dbo].[AUDITTABLE] Script Date: 6/6/2018 2:19:13 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AUDITTABLE](
[TRANSACTION_TIME] [datetime] NULL,
[OBJECT_TYPE] [varchar](900) NULL,
[OBJECT_ID] [int] NULL,
[FIELD_NAME] [varchar](900) NULL,
[OLD_VALUE] [varchar](8000) NULL,
[NEW_VALUE] [varchar](8000) NULL,
[USER_CODE] [varchar](900) NULL,
[TRANSACTION_TYPE] [varchar](900) NULL,
[TRANSACTION_DATE] [varchar](900) NULL,
[SUBMITTER] [int] NULL,
[PATHOLOGIST] [int] NULL,
[PATIENT] [int] NULL,
[WORKSTATION] [int] NULL,
[VISIT] [int] NULL,
[WINDOW] [varchar](900) NULL,
[APPLICATION] [varchar](100) NULL,
[SUPEROBJECT_ID] [int] NULL,
[CASE] [int] NULL,
[AUDITINDEX] [int] NULL,
[CHECKSUM] [int] NULL,
[CLIENT_TIME] [varchar](1000) NULL,
[OLD_VALUE_EXT_X] [varchar](900) NULL,
[NEW_VALUE_EXT_X] [varchar](900) NULL,
[NEW_VALUE_TRUNCATED] [varchar](100) NULL,
[AuditText] [text] NULL,
[OLD_VALUE_EXT] [varchar](max) NULL,
[NEW_VALUE_EXT] [varchar](max) NULL
)
GO
/****** Object: Index [AUDIT_TRANSACTION_TIME] Script Date: 6/6/2018 2:19:13 PM ******/
CREATE CLUSTERED INDEX [AUDIT_TRANSACTION_TIME] ON [dbo].[AUDITTABLE]
(
[TRANSACTION_TIME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
GO
/****** Object: Index [AUDIT_CASE] Script Date: 6/6/2018 2:19:13 PM ******/
CREATE NONCLUSTERED INDEX [AUDIT_CASE] ON [dbo].[AUDITTABLE]
(
[CASE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [AUDIT_FIELD_NAME] Script Date: 6/6/2018 2:19:13 PM ******/
CREATE NONCLUSTERED INDEX [AUDIT_FIELD_NAME] ON [dbo].[AUDITTABLE]
(
[FIELD_NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [AUDIT_NEW_VALUE_TRUNCATED] Script Date: 6/6/2018 2:19:13 PM ******/
CREATE NONCLUSTERED INDEX [AUDIT_NEW_VALUE_TRUNCATED] ON [dbo].[AUDITTABLE]
(
[NEW_VALUE_TRUNCATED] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [AUDIT_OBJECT_TYPE] Script Date: 6/6/2018 2:19:13 PM ******/
CREATE NONCLUSTERED INDEX [AUDIT_OBJECT_TYPE] ON [dbo].[AUDITTABLE]
(
[OBJECT_TYPE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [AUDIT_OBJECTID] Script Date: 6/6/2018 2:19:13 PM ******/
CREATE NONCLUSTERED INDEX [AUDIT_OBJECTID] ON [dbo].[AUDITTABLE]
(
[OBJECT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [AUDIT_TRANSACTION_DATE] Script Date: 6/6/2018 2:19:13 PM ******/
CREATE NONCLUSTERED INDEX [AUDIT_TRANSACTION_DATE] ON [dbo].[AUDITTABLE]
(
[TRANSACTION_DATE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [AUDIT_TRANSACTION_TYPE] Script Date: 6/6/2018 2:19:13 PM ******/
CREATE NONCLUSTERED INDEX [AUDIT_TRANSACTION_TYPE] ON [dbo].[AUDITTABLE]
(
[TRANSACTION_TYPE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [AUDIT_TRANSACTION_VISIT] Script Date: 6/6/2018 2:19:13 PM ******/
CREATE NONCLUSTERED INDEX [AUDIT_TRANSACTION_VISIT] ON [dbo].[AUDITTABLE]
(
[VISIT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [AUDIT_USER_CODE] Script Date: 6/6/2018 2:19:13 PM ******/
CREATE NONCLUSTERED INDEX [AUDIT_USER_CODE] ON [dbo].[AUDITTABLE]
(
[USER_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
June 6, 2018 at 1:40 pm
erb2000 - Wednesday, June 6, 2018 8:57 AMThe AuditTable contains data from October, 2013 through current. We want to create:
Audit2014Table
Audit2015Table
Audit2016Table
Audit2017Table
The field Transaction_time contains a date/time string YYYYMMDDHHMMSSSSS.We can use: Select * into Audit2014Table from AuditTable where transaction_time < '2015-01-01'
Select * into Audit2015Table from AuditTable where transaction_time >= '2015-01-01 00:00:00.000' and transaction_time < '2016-01-01'
etc.
The problem is deleting the rows from AuditTable takes forever and hangs the sql server for other users.
Also, the new tables need indexes added to them. I'm concerned this will also hang the sql server for other users.
The sql server is under a heavy load throughout the day.Thank you for any suggestions.
For this big table the delete take ever, Better to do the loop cleanup.
Also, add the partition on this table so you can easily remove the year worth of data in few second.
June 6, 2018 at 3:02 pm
The table's already clustered by [TRANSACTION_TIME]. That's fantastic, it'll make your job so much easier. We'll just want to increase the fill factor from 90 to 99 for the historical table(s). When people query this time, you need to stress how vital it is to include a transaction_time range, even if it's 6 months.
I suggest creating a new table to put current data into, then renaming the tables, as I kinda outlined before, I think.
Here's an overview of the steps:
(1) Create a new table, say "AUDITTABLE_NEW", with the same columns and clustered index (script (1) below).
(2) Copy current year data from Jan 1 through all of May 31 into the new table by week or however many days run comfortably in the time frame you need, even down to day-by-day if necessary. Use a "sargable" WHERE condition on Transaction_Time to identity the days to copy. Presumably this data is historic, so you can safely use NOLOCK on the reads.
(3) Create all the non-clustered indexes on AUDITTABLE_NEW (script (3) below).
(4) Copy data for June thru, say, 1 hour before the current time. Don't use NOLOCK on this, just to be safe.
(5) Finally, in one script:
A) Copy data from stop time in (3) to the current time, using an exclusive lock on AUDITTABLE.
B) Rename AUDITTABLE to AUDITTABLE_OLD.
C) Rename AUDITTABLE_NEW to AUDITTABLE.
At that point, AUDITTABLE will have only data for 2018. Then you can deal with the historical data without interfering with current logging.
I'm short of time, I'll have to come back later if needed.
--script (1)
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [dbo].[AUDITTABLE_NEW](
[TRANSACTION_TIME] [datetime] NULL,
[OBJECT_TYPE] [varchar](900) NULL,
[OBJECT_ID] [int] NULL,
[FIELD_NAME] [varchar](900) NULL,
[OLD_VALUE] [varchar](8000) NULL,
[NEW_VALUE] [varchar](8000) NULL,
[USER_CODE] [varchar](900) NULL,
[TRANSACTION_TYPE] [varchar](900) NULL,
[TRANSACTION_DATE] [varchar](900) NULL,
[SUBMITTER] [int] NULL,
[PATHOLOGIST] [int] NULL,
[PATIENT] [int] NULL,
[WORKSTATION] [int] NULL,
[VISIT] [int] NULL,
[WINDOW] [varchar](900) NULL,
[APPLICATION] [varchar](100) NULL,
[SUPEROBJECT_ID] [int] NULL,
[CASE] [int] NULL,
[AUDITINDEX] [int] NULL,
[CHECKSUM] [int] NULL,
[CLIENT_TIME] [varchar](1000) NULL,
[OLD_VALUE_EXT_X] [varchar](900) NULL,
[NEW_VALUE_EXT_X] [varchar](900) NULL,
[NEW_VALUE_TRUNCATED] [varchar](100) NULL,
[AuditText] [text] NULL,
[OLD_VALUE_EXT] [varchar](max) NULL,
[NEW_VALUE_EXT] [varchar](max) NULL
)
GO
CREATE CLUSTERED INDEX [AUDIT_TRANSACTION_TIME] ON [dbo].[AUDITTABLE_NEW]
(
[TRANSACTION_TIME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
GO
--script (3)
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE NONCLUSTERED INDEX [AUDIT_CASE]ON [dbo].[AUDITTABLE_NEW]
(
[CASE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [AUDIT_FIELD_NAME]ON [dbo].[AUDITTABLE_NEW]
(
[FIELD_NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [AUDIT_NEW_VALUE_TRUNCATED]ON [dbo].[AUDITTABLE_NEW]
(
[NEW_VALUE_TRUNCATED] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
CREATE NONCLUSTERED INDEX [AUDIT_OBJECT_TYPE]ON [dbo].[AUDITTABLE_NEW]
(
[OBJECT_TYPE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [AUDIT_OBJECTID]ON [dbo].[AUDITTABLE_NEW]
(
[OBJECT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [AUDIT_TRANSACTION_DATE]ON [dbo].[AUDITTABLE_NEW]
(
[TRANSACTION_DATE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [AUDIT_TRANSACTION_TYPE]ON [dbo].[AUDITTABLE_NEW]
(
[TRANSACTION_TYPE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [AUDIT_TRANSACTION_VISIT]ON [dbo].[AUDITTABLE_NEW]
(
[VISIT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [AUDIT_USER_CODE]ON [dbo].[AUDITTABLE_NEW]
(
[USER_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
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".
June 6, 2018 at 5:56 pm
I notice that your scripts are missing both compression and filegroup information - just to confirm could you please run the following script and supply us with the output.
And are you on standard or enterprise edition?
;with partitionedtables
as
(select distinct t.object_id
, t.name as table_name
from sys.Tables as t
join sys.indexes as si
on t.object_id = si.object_id
join sys.partition_schemes as sc
on si.data_space_id = sc.data_space_id
)
select pt.table_name
, si.index_id
, si.name as index_name
, stuff(
(select ',' + p.data_compression_desc
from (select distinct p.data_compression_desc
from sys.partitions p
where p.object_id = si.object_id
and p.index_id = si.index_id
) p
for xml path ('')
), 1, 1, '') as compression_type
, isnull(pf.name, 'NonAligned') as partition_function
, isnull(sc.name, fg.name) as partition_scheme_or_filegroup
, ic.partition_ordinal
, /* 0= not a partitioning column*/
ic.key_ordinal
, ic.is_included_column
, c.name as column_name
, t.name as data_type_name
, c.is_identity
, ic.is_descending_key
, si.filter_definition
from partitionedtables as pt
join sys.indexes as si
on pt.object_id = si.object_id
join sys.index_columns as ic
on si.object_id = ic.object_id
and si.index_id = ic.index_id
join sys.columns as c
on ic.object_id = c.object_id
and ic.column_id = c.column_id
join sys.types as t
on c.system_type_id = t.system_type_id
left join sys.partition_schemes as sc
on si.data_space_id = sc.data_space_id
left join sys.partition_functions as pf
on sc.function_id = pf.function_id
left join sys.filegroups as fg
on si.data_space_id = fg.data_space_id
where pt.table_name = 'AUDITTABLE'
order by 1
, 2
, 3
, 4
, 5
, 6 desc
, 7
, 8
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply