March 26, 2019 at 11:54 am
I am pretty new to this, so my apologies for any SQL-related misstatements and confusions.
I have inherited an application that dumps events into a table with the following structure:
recordid (bigint, not null)
productid (int, null)
productsubid (int, null)
actionid (int, null)
datetime (datetime, null)
userdn (varchar(255), null)
data1 (varchar(2300), null)
data2 (varchar(2300), null)
data3 (varchar(2300), null)
useragent (varchar(500), null)
langcode (varchar(50), null)
countrycode (varchar(50), null)
clienthost (varchar(100), null)
The table was set up as a heap; recordid is an auto-increment identity column. Usage of the application has grown over the past few years, but appears to be relatively settled. I came in to my current position with the database at roughly 80 GB / 250 million rows in size, with around 300k-400k events written per day for a user base of roughly 40k.
As best as I can tell, somewhere between 120 and 200 million rows, the reports that query the table became unusable, and started to cause problems with the host server (they have currently been made inaccessible for this reason). However, even the size of the table itself has been pointed to by the application vendor as a possible culprit for causing issues with the application (specifically, TCP behavior -- the application is load-balanced, and occasionally one of the web servers will see a flood of thousands of connections getting stuck in CLOSE_WAIT status, making that server unresponsive and yet not appear offline to prevent the load-balancer from establishing new connections).
The application has high expected uptime, but the owner of the application also has said he needs access to more than a year's worth of data (a year averages about 70 million rows, now). Although the rows should be very close to being in order by recordid and datetime since there are no updates or deletes on the table, any attempt to actually perform sort operations on those columns is infeasible. We were considering taking the application offline long enough to:
1) backup the database,
2) find the maximum recordid,
3) truncate the table,
4) create appropriate indexes,
5) set the identity seed of the recordid column to the maximum current value + 1, and
6) index the backed-up data on a server that doesn't host critical applications and re-insert the data at a later date, as performance allows.
(4) is where my first trouble is. I see no potential performance issues with making the recordid the primary key and CI of the table. What I'm not sure on is if there would be any value to making the CI a composite index of datetime and recordid (a number of the report queries have ORDER BY clauses using datetime). Or, if it would be better for the recordid to be the PK and CI of the table, and create an NCI on datetime.
I'm also unsure if NCIs would be helpful to the other report queries that involve sorting actionid, productid, data2, and a couple other columns; or if NCIs for those columns would be a terrible idea because of the impact on INSERT operations for an application that needs to be able to make upwards of twenty inserts a second at peak usage, especially as the table grows. It feels like any indexing beyond the datetime and auto-incrementing recordid would quickly become a problem with a table that grows this quickly.
My understanding (which could very well be wrong) is that when calculating the memory grant for SORT operations, SQL Server assumes an average of x/2 bytes for varchar(x), and this would cause excessive grant issues, whether the sort was for inserts for the NCI, or for a report query attempting to sort one of the varchar(2300) columns.
Related to (4), I see trouble with (6) -- attempting to re-insert such a large amount of records once the table has a CI.
The other option I have considered is:
Since the table appears to be very nearly in ascending datetime order, it seems that I could loop a "DELETE TOP 100000 FROM
OUTPUT DELETED to [archive table]" statement until "SELECT TOP 1 datetime" reaches the oldest date we want to keep (with a pause between deletes if needed), in order to trim the table back to 120-150 million rows or until the analytics reports become functional again.
However, since the report queries that have hit the table have caused SQL services to go down on this important shared server, management is wary of any sort of big operations on this table.
Would there be any potential concerns with respect to memory usage, or transaction log growth, from trimming in this way? Would setting the recovery mode to SIMPLE before trimming alleviate log file growth issues?
It also seems that we would probably not be able to index the table without truncating and starting fresh, or severely trimming the table well below the 70 million or so records that make up the average year.
I'm not sure if there are any good options, but I very much appreciate any insight.
March 26, 2019 at 1:41 pm
The table is a heap, so there is no guarantee that DELETE TOP 100000 will delete rows in date order. You also may run into issues with the space used by deleted rows is not reclaimed, and the table doesn't shrink.
Starting with a backup is a good idea no matter what you plan on doing next.
I would suggest:
If the identity column is not used often for joins or lookups, and the datetime column is unique, you would get better performance using the datetime as the clustered index since it is frequently used in WHERE clauses. If it is not unique the index will be larger, as a hidden column is added to make it unique. The performance improvement might be enough to make it worthwhile in spite of the extra column.
March 26, 2019 at 3:25 pm
Scott Coleman - Tuesday, March 26, 2019 1:41 PMThe table is a heap, so there is no guarantee that DELETE TOP 100000 will delete rows in date order. You also may run into issues with the space used by deleted rows is not reclaimed, and the table doesn't shrink.Starting with a backup is a good idea no matter what you plan on doing next.
- Back up the database and restore to another server. Or just copy the table to an archive database.
- Truncate the original table
- Add a clustered index to the empty table on the identity (or datetime) column
- Load the table from the backup copy WHERE datetime >= <oldest date to keep>
If the identity column is not used often for joins or lookups, and the datetime column is unique, you would get better performance using the datetime as the clustered index since it is frequently used in WHERE clauses. If it is not unique the index will be larger, as a hidden column is added to make it unique. The performance improvement might be enough to make it worthwhile in spite of the extra column.
The identity column is not used in any of the queries, but the datetime column is not unique. I was thinking that a composite index on (datetime, recordid) would avoid the need for the extra hidden column to make the datetime unique, and the extra width of the bigint "recordid" wouldn't matter if I wasn't creating a non-clustered index on any of the other columns.
For the "Load the table from the backup copy WHERE datetime >= <oldest date to keep>" step -- when we have tried a simple SELECT * WHERE datetime >= <two months ago> query, that alone (selecting maybe 15 million of the 250 million records) has wreaked havoc on the host server. My chief concern now is probably going to be with coming up with a reasonably accurate downtime estimate for the backup and load steps. The vendor recommends periodically trimming the table, which seems like it makes indexing on datetime important going forward, but I'm concerned with how long the load step would take if they are looking to keep maybe 150 million rows. I know I'm going to have to do some research, but I'm not exactly sure where to start.
March 26, 2019 at 4:46 pm
You stated that currently reports of that table are disabled.
And also that updates/deletes are not done at all - being insert only makes it a bit easier.
If this is indeed correct I would probably consider the following.
It assumes that your client accepts a period of time where the full data will not be available on the source table.
get hands of a production server that has enough storage to hold a copy of the database plus possibly extra space for creating the clustered index as required.
disable access to the database at this stage for a short period - few mins.
Possibly bring db to single user mode
- backup db
- in an explicit transaction
-- retrive current value of identity column using IDENT_CURRENT - with db in single user mode you know this is going to be correct.
--- rename source table to <-source_bkp-> - this ensures that any new record since backup is "saved"
--- create new table with same layout - set identity value to found identity_current + 1
- commit
at this point your users can insert data into an empty table and you ensured you have full copy of all data (backup + rename)
- create new table <-source_new->, same as source, with clustered index as required
--- possibly create it partitioned by the datetime column to allow you to
--- delete older data at a later stage if client agrees with it.
--- performance with partitioned index will be good/acceptable if majority of queries specify the datetime column as a filter. If not it will be possibly be degraded so use with care
- restore db to second server - ensure backup/restore is good
- if restore is good go back to source server and drop table <-source_bkp->
on the second server
- drop all tables except <-source_bkp-> This is what needs the clustered index created. This allows for free space to create the clustered index minimizing possible extra growth space
- create clustered index as required.
- copy records from this table onto the original server table <-source_new-> - in batches so load on main prod server is minimal. Retrieve the records in datetime order, oldest first
once all old data is copied over to the original server you now have the following
- main table - smaller number of records - these will need to be copied over to <-source_new->
- table <-source_new->
disable access to the database at this stage for a short period - few mins.
Possibly bring db to single user mode
- in an explicit transaction
--store/print the exact datetime
-- insert into <-source_new-> with (tablockx) select * from main table with (tablockx) -- copy new records onto what will be our final table
-- rename source table to <-source_bkp2->
-- rename <-source_new-> to main table
- commit
at this point the main table will have all historical records and is ready to use.
<-source_bkp2-> could be deleted at this point.
March 26, 2019 at 5:53 pm
I'm thinking that there's no need to change this table despite the angst that some folks have with a heap this size. As was stated, this is an "insert only" table which also means that it's an "append only" table even if it's not truly a table. That also means that there's no fragmentation because there are no updates and the inserts are "append only". With no fragmentation, there's no need for defragmentation of the data.
The big problem is with performance. You CAN use indexes to solve such a problem. There's nothing wrong with adding NCIs to heaps unless you're incorrectly of the opinion that such a table must have a clustered index first.
Since there is no unique clustered index on the table, you need to make a unique non-clustered index whenever you can. Since most queries are supposed based on the DateTime column, then I agree with what the OP said and you need to add a unique nonclustered composite index based on the DateTime and RecordID columns.
Try that first before you go through the rebuilding of this table as something else that may not solve the issue and could, in fact, make the issue worse because a clustered index lookup on rows as wide as 7k Bytes isn't going to compare to finding things in a super skinny NCI and having to look rows up from there. In fact, I'd be tempted to force the longer varchars to OUT OF ROW VARCHAR(MAX)es but that would be well after trying the simple fix of adding the appropriate unique composite NCI.
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply