SQL 2012 :: Index Maintenance For Large Tables?
Mar 8, 2014We are having very big tables in TBS and wanted to setup a strategy for index maintenance.
View 3 Replies
ADVERTISEMENT
SQL 2012 :: Index Maintenance And Update Stats
Jul 22, 2014I am using Ola Hallengrens scripts for index and stats maintenance but I am wondering what most people to in terms of the maintenance schedules. At present we do an index rebuild reorg weekly, but do people also do update stats nightly?
I suppose there is an element of "it depends" here in that the data may be fairly static so the update stats may not be required, or if heavily updated then perhaps rebuilding indexes may be required more frequently.
Reducing Large Tables, Re-index And Backup Them
Jan 2, 2003What is the best procedure/sequence to reduce some tables containing large number of rows of
a SQL 2000 server?
The idea is first to check which tables grow extremely fast (all statistics, user or log tables), reduce the table
according to the number of months the user wishes to keep in the table.
As a second step backup remaining rows of table as txt files on harddisk (using DTS), UPDATE STATISTICS and re-indexing reduced table.
Run DTS Package every month once (delete oldest month and backup newest month) and do the same as above to keep size of tables adequate.
What is a fast way to reduce number of rows of a large table - the following example produces an error (timeout expired) of my
ADO connection when executing:
SET @str = 'DELETE FROM ' + @ProcessTable + ' WHERE ' + @SelectedColumn + ' < DATEADD (m,' +' -' +
@KeepMonthsInDatabase + ',
+ GETDATE())'
EXEC (@str)
Adding ConnectionTimout = 0 did not help unfortunately.
What is the best way to re-index the table just maintained?
Thanks
mipo
SQL 2012 :: Create Clustered Index On A Very Large Table (500 GB)
May 7, 2014I need to create a Clustered Index (CI) on a very large SQL Server 2012 database table. This table has about approximately 10 billion rows, 500 GB in size. The job ran for about 20 hours into it and then fails with error: "Out of disk space in tempdb". My tempDB size is 1.8TB, but yet it's still not enough.
Here is my script:
CREATE CLUSTERED INDEX CI_IndexName
ON TableName(Column1,Column2)
WITH (MAXDOP= 4, ONLINE=ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION=PAGE)
ON sh_WeekDT(Day_DT)
GO
SQL Server 2012 :: Best Way To Handle Like Percentage On Column Too Large For Index
Sep 18, 2015We have a table to 100M rows and up until now we were fine with an non clustered index a varchar(4000) because we never went above 900 bytes (yes it is a bad design).We have the need to support international character sets now so the column was updated to nvarchar(4000) and now we have data past the 900 byte limit.
The data is long, seems useless but is needed by the business and they need to be able to search "where bigcolumn like 'test%'". With an index, even with a huge amount of data, it was 'fast'. Now of course without an index it is unusable. The wildcard is always at the end of the search. I made a full text index on the column and basic queries such as: select * from ourtable where contains(bigcolumn, 'AReallyLongStringofTextHere') works fine unless there is a space in the data. We loose thousands of returned rows because of spaces in the data.
I have tried select * from ourtable where contains(bigcolumn, '"AReallyLongStringofTextHere that includes spaces"') but not all of the data is returned. I get 112 rows with the contains statement. The table scanning statement of "select * from ourtable where bigcolumn like 'AReallyLongStringofTextHere that includes spaces%' returns 1939 rows.I understand that a full text index is breaking the long string up since it contains spaces. Is there a way to retain the entire string as 1 index entry or is there a way to fix my query to return all of the rows?
Impact Of Non-clustered Index With Included Columns On Large Tables
Nov 14, 2011I would like to know the impacts (if any) of adding nonclustered index with included columns on large tables (these tables are populated by bulk insert from text files).
View 3 Replies View RelatedSQL Server Admin 2014 :: Columnstore Index On Large Tables
Jul 1, 2015I created columnstore index on the table with 20 columns and about 1000 000 000 rows
every day added about 5M rows
"select" queries became faster because of batch mode and table demand less disk space then before
I have also 6 similar tables with 5 000 000 000 rows and plan to move them on columnstore index
server has 128 G RAM
What pitfalls I could face if I will have so many columnstore indexes on one server?
How a could see problems in DMV?
SQL Server 2012 :: Select Large Data From Multiple Tables
May 10, 2014In a Library Management database we have these tables
1) Document ( DocNo , Doc_type , permalink,inDate)
2)Title(id, DocNo,Main_Title, Other_Title)
3)Author(id , Author_Name , Author_Family,Type--Like:main author , translator ,....)
4)Publisher(id,DocNo , Name,Publisedate,address)
5)Subject(id,DocNo,Subject)
6)Description(id,DocNo,ISBN,description)--one document may have some ISBN,etc
In document table I have 500,000 records.
I want to search a word in these tables ,for example i want to search 'Computer' ,this word may be in subject or title or description and etc. How can I do this with best performance?
SQL Server 2012 :: Merging Two Large Tables (More Than 100m Rows)
Aug 18, 2014SQL 2012
I have a source table in the staging database stg.fact and it needs to be merged into the warehouse table whs.Fact.
stg.fact is not a delta feed it is basically an intra-day refresh.
Both tables have a last updated date so its easy to see which have changed.
It will be new (insert) or changed (update) data that I am interested in, there are no deletions.
As this could be in the millions of rows that are inserts or updates then this needs to be efficient.
I expect whs.Fact to go to >150 million rows.
When I have done this before I started with T-SQL Merge statement and that was not performant once I got to this size.
My original option was to do this is SSIS with a lookup task that marks the inserts and updates and deal with them seperately. However I set up the lookup tranformation the reference data set will have a package variable in the SQL commnd. This does not seem possible with the lookup in 2012! Currently looking at Merge Join transformation and any clever basic T-SQL that could work as this will need to be fast, and thats where I think that T-SQL may be the better route.
Both tables will have >100,000,000 rows
Both tables have the last updated date
The Tables are in different databases but on the same SQL Instance
Each table holds 5 integer columns, one Varchar, one datatime
Last time I used Merge it was a wider table with lots of columns so don't know if this would be an option.
SQL 2012 :: Generating CREATE TABLE Scripts For Large Number Of Tables
Feb 11, 2014Other than right-clicking on each individual table in SSMS and generating a CREATE script, is there a simple way to generate CREATE TABLE scripts for tables within a given database?
Background: I have a bunch of tables in one database, and I would like to add tables to a second database that have the same names and basic structures of some of the tables from the first database.
I do not need to transfer any data from the tables, this is a seperate project that will use a similar data structure. I just want to generate the CREATE TABLE scripts for 30ish tables within the first database, and then I'll tweak the scripts as appropriate and run them against the new database.
[URL] ....
SQL Server 2012 :: Index View Won't Work With Hierarchical Tables?
Apr 4, 2015Consider following code:
SELECT e1.EntityIdentity as CompanyID
FROM dbo.Entitye1 --company
JOIN dbo.EntityAssociationea
ON e1.EntityID = ea.EntityID1
JOIN dbo.Entitye2 --user
ON ea.EntityID2 = e2.EntityID
This query occurs as a sub-query in many stored procedures where exists a WHERE clause that includes CompanyID IN (above query).
Since dbo.Entity and dbo.EntityAssociation change infrequently I thought that an indexed view would really improve performance. But I've found one of the seemingly undocumented Microsoft features when trying to create the clustered index and get the following error msg:
Msg 1947, Level 16, State 1, Line 1
Cannot create index on view "ROICore.dbo.vEntityEntityAssociation_CompanyUser". The view contains a self join on "ROICore.dbo.Entity".
I really need to improve performance on this subquery. Entity currently has over 20m rows and EntityAssociation over 35m rows and both are growing.
How to improve performance? Indexes on both tables for the most part give index seeks, but I thought my saviour might be the index view. Obviously this will not work.
SQL 2012 :: Memory Optimized Tables And Updatable Column Stored Index
Aug 26, 2014We are planning to upgrade. We are using Sql 2008R2 now. Which is the better option migrating to SQL 2012 or migrating to 2014?I am thinking 2014 has memory optimized tables and updatable column stored index. So it is better option.
View 2 Replies View RelatedMaintenance A Very Large Db
Aug 31, 2001Hi, anyone who administering the pretty big database not less than 30 Gb with the average number of rows in a table about 2M and more, please share you experience with maintenace of such a db. Esspecially i'm interesting in:
1) Indexes maintenance (When and how - just regular dbcc, maint. plan or some script to split the job twice and so on.)
2) Remove unused space from db. (not major)
The serever works 24*7, and it's transactional environment. SQL 7 sp3 on claster.
I run the sp. to rebuild all the indexes it takes about 2-3 hrs to determin the objects withfragmentation less than 80% and actually rebuilding, during this process the users experience the performance (specially for update/insert) problem. It looks like I need to change the plan or strategy to do this. Any thoughts appreciated!
Thanks in advance.
Dmitri
Very Large Table Maintenance
Jul 8, 2007I have been asked to look at some performance isssues with an application that utilises a 800GB table. This table is huge and contains 4 int columns and 1 decimal column. The table has a clustered index that covers 4 of the int columns and is heavily fragmented and it has not been maintained for a long time. The system has limited free space to even attempt rebuilding the index. Does anyone have any experience of running a the Alter Index Reorganize command on such a large table? Any information on what storage would be required to attempt this, how long would this take?
View 4 Replies View RelatedIndex Maintenance Job
Jun 11, 2001My index maintenance job that was setup through Enterprise manager database maintenance fails with the following notice. It ran great for several weeks then it started failing. Any suggestions!!
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
2005 Index Maintenance Information
Jul 24, 2006Hi,
Does anyone here know of any on-line references on how to optimize index maintenance in sql 2005? Also do you know of any good DBA books that will explain database maintenance and or best practices?
Thanks.
:D
Rebuild Index Maintenance Is Failed?
May 11, 2015Rebuild index maintenance plan is failed, since we don't have space in the C:Drive we have left the option as it is to sort the results in user databases respectively. These user databases are in E: with sufficient space to rebuild index.
Check the below details.
SQL Server 2005: Microsoft SQL Server 2005 - 9.00.5000.00 (X64) Dec 10 2010 10:38:40 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Online reindexing supports in SQL Server 2005 Standard Edition? Job is failing because these options (sort results in tempdb & keep index online while reindexing) is not checked (enabled)?
Full-Text Index Maintenance And Backup
Apr 11, 2000Are there any examples of maintenance(ReBuild FULL or Incremental) for Full-Text indexes? Are there any index integrity checks that can be done? What is the best way to backup a full-text index?
View 2 Replies View RelatedChallenge With Fuzzy Lookup Index Maintenance
Mar 5, 2008Greetings
My Fuzzy Lookup task works beautifully when it generates the lookup index every time it runs, but as I'm planning on running this hundreds of times I'd like it to maintain the index via the trigger. However when it attempts to install the trigger via sp_FuzzyLookupTableMaintenanceInstall I get:
Description: "A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_FuzzyLookupTableMaintenanceInstall":
System.Data.SqlClient.SqlException:
Error number 8101 is invalid.
(I've not included the full stack trace as I figured this would be enough)
The table currently has an After Insert and After Update trigger. CLR integration is enabled in this database instance. Is there some other option I need to set somewhere?
Thanks!!
Fuzzy Lookup Match Index Maintenance
May 29, 2007
Hello,
For one of my SSIS projects that does a fuzzy lookup on a table, I opted to create an index and
to maintain the stored index. The index got created and subsequent project execution was able to
use that index.
Now I want to update certain rows in that table. When I run the update statement I get the following error.
How can I retain the index and still be able to update the table?
update location_stage set batchid = 'APR07N'
where batchid is null and eventid = '20070528020041';
Msg 6549, Level 16, State 1, Procedure sp_FuzzyLookupTableMaintenanceInvoke, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'sp_FuzzyLookupTableMaintenanceInvoke':
System.Data.SqlClient.SqlException: Transaction is not allowed to roll back inside a user defined routine, trigger or aggregate because the transaction is not started in that CLR level. Change application logic to enforce strict transaction nesting.
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnectionSmi.ExecuteTransaction(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction)
at System.Data.SqlClient.SqlInternalTransaction.Rollback()
at System.Data.SqlClient.SqlTransaction.Rollback()
at Microsoft.SqlServer.Dts.TxBestMatch.TableMaintenance.TranWrap(DataCleaningOperation c)
. User transaction, if any, will be rolled back.
The statement has been terminated.
SQL 2012 :: Full Text Index How To Make It NOT To Index Embedded Or Attached Documents
Sep 30, 2015I am using Full Text Index to index emails stored in BLOB column in a table. Index process parses stored emails, and, if there is one or more files attached to the email these documents get indexed too. In result when I'm querying the full text index for a word or phrase I am getting reference to the email containing the word of phrase if interest if the word was used in the email body OR if it was used in any document attached to the email.
How to distinguish in a Full Text query that the result came from an embedded document rather than from "main" document? Or if that's not possible how to disable indexing of embedded documents?
My goal is either to give a user an option if he or she wants to search emails (email bodies only) OR emails AND documents attached to them, or at least clearly indicate in the returned result the real source where the word or phrase has been found.
Online Index On Large DB (&> 4GB)
May 22, 2007Anyone using the ONLINE=ON option on large DB's? We have a db of 5 GB and we are doing some load testing for SQL 2005. We are modifying the Index scripts for the upgrade. We will run a load with the ONLINE=ON option but just wanted to find out if anyone already is doing it on a similar scale db and has seen any issues?
Also, we have auto-update stats off at the DB level. Does setting the ONLINE=ON require turning this auto-update stats to ON too? I didnt see anything to that effect in BOL, so was wondering.
Thanks for any feedback.
Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Index Reorg Maintenance Plan After SP2 Gets A System.OutOfMemoryException
May 8, 2007Recently upgraded SQL Server 2005 x64 to SP2 and upto Build 3159. Since then the Maintenance Plan for Index Reorgs has failed with a System.OutOfMemoryException error. No other errors are logged anywhere. The plan report file has no information either.
Has anybody come across a similar issue?
Thank you.
CREATE INDEX On Large Table
Jul 23, 2005SQL Server 7/2000: We have reasonably large tables (3,000,000 rows)that we need to add some indexes for. In a test, it took over 12 hoursto CREATE a new INDEX against this table. One of us suggested that wecreate a temp table with the new index and copy the data from the oldtable into the new one, then rename it. I understand this took 15minutes. Why the heck would it be faster to move the data and buildmultiple indexes incrementally vs adding an index??
View 11 Replies View RelatedTransact SQL :: Index Maintenance / Defrag Fails On CDC Enabled Database - 2008 R2
Oct 18, 2012We have a new database with cdc enabled on all of its tables. This causes the index maintenance task to fail with following message:
"Executing the query "EXEC DBName.dbo.IndexDefrag_sp" failed with the following error: "The unique index 'PK_TableName' on source table '[dbo].[TableName]' is used by Change Data Capture. To alter or drop the index, you must first disable Change Data Capture on the table. The transaction ended in the trigger. The batch has been aborted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly" We would like to run the index maintenance without losing the cdc data. We plan on installing SP2 on SQL Server 2008 R2 soon, would that solve the issue? Disabling the cdc prior to index maintenance and then re-enabling back upon completion; would delete the data as I found in most discussions, but we would like to retain it.
SQL 2012 :: Clustered Index Key Order In NC Index
Mar 5, 2015I have a clustered index that consists of 3 int columns in this order: DateKey, LocationKey, ItemKey (there are many other columns in this data warehouse table such as quantities, prices, etc.).
Now I want to add a non-clustered index on just one of the other columns, say LocationKey, like this:
CREATE INDEX IX_test on TableName (LocationKey)
I understand that the clustered index keys will also be added as key columns to any NC indexes. So, in this case the NC index will also get the other two columns from the clustered index added as key columns. But, in what order will they be added?
Will the resulting index keys on this new NC index effectively be:
LocationKey, DateKey, ItemKey
OR
LocationKey, ItemKey, DateKey
Do the clustering keys get added to a NC index in the same order as they are defined in the clustered index?
SQL Server Admin 2014 :: Rule Of Thumb For Sizing Log Space During Index Maintenance?
Sep 13, 2014I've been fixing some issues lately where weekly maintenance has been causing logs to grow and filling disks.
Is there any rule of thumb for allocating log space for doing reorgs and rebuilds in a worst case scenario? I'm thinking 3x the largest database size?
I've been watching them run on databases in the range of 50GB where the logs are growing well over that for rebuilds or even reorgs. Once you have a few databases like this on a server, you can suddenly eat through a lot of disk space just for holding logs during maintenance.
SQL Server 2014 :: Index Dates To Numbers With A Large Data Set?
Jun 16, 2015I am trying to index dates to numbers with a large data set.
The first colums is index, the next is FactorsS, the next is value and the next is Date and the last is Lag.
Would it be difficult to write code that would determine the lag values. The lag value is based on the date value.
Index FactorS Value Date Lag
1 XYZ 2.3 12/31/2014 1
2 XYZ 1.4 12/30/2014 2
3 XYZ 3.3 12/29/2014 3
4 ABC 1.8 12/31/2014 1
5 ABC 2.2 12/30/2014 2
6 CBA 1.7 12/31/2014 1
7 CBA 1.8 12/30/2014 2
8 CBA 1.9 12/29/2014 3
9 CBA 2.1 12/28/2014 4
SQL 2012 :: Log Write Denied When Using Maintenance Plan
Jul 1, 2014Under the maintenance plan, i run full backup at the same path and it was successful. The maintenance plan for selected database was run success.
BUT when i want to write the backup report "Write a report to a text file" i receive this error in maintenance plan "view history"
Access to the path 'F:MSSQL11.APPSPRDMSSQLBackupBackup LogCARSCARS DB Backup_Subplan_1_20140701200011.txt' is denied.
Basically i am using defaul NT service for SQL Agent
SQL 2012 :: Maintenance Plan Exporting To Other Servers?
Oct 7, 2014Server: SQL Server 2012 Enterprise 64bit
I created a maintenance plan with full user database backups, backup files cleanup, and database shrink.
I did it in SSMS and it also created "Reporting Task for subplan-{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}".
It works fine in the development server where I created the plan. I imported the same into other environments test, stage, and prod. I did use BIDS to change the connection string manual "Local server connection". The job runs fine in other environments but it fails at the end where the "Reporting Task for subplan" is executed. I looked at the dtsx file codes and I could not find anything wrong.
It seems to be a bug at the Microsoft side.
SQL 2012 :: Maintenance Cleanup Task Not Cleaning Up?
Jul 8, 2015I am running a maintenance cleanup task as shown below but despite the log saying "success" it is not successful as nothing is cleaned up.
Maintenance Cleanup Task (VPS20604)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 4 Weeks
Task start: 2015-07-08T16:14:24.
Task end: 2015-07-08T16:14:24.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''C:SQLBkp'',N'''',N''2015-06-10T16:14:24''
GO
SQL 2012 :: Changing SA Account Name Causes Maintenance Jobs To Fail
Sep 23, 2014SQL 2012 Standard on Windows Server 2012 Standard
After observing brute force attacks on our VPS myhosting instance against the SA login, I change the SA login name. Now my two new backup jobs are failing. The agent service logs in as NT ServiceSQLSERVERAGENT. Nothing changed there (so far as I know and I'm the only one who should be on the VPS). The job owner was SA and after changing the SA account that was reflected in the SSMS gui; job owner 'newsaname'. I'm sure I can just rebuild the maintenance plans but I'd like to know what happened.
Also, I would like to learn more about the brute force attacks and how to determine what port they are comming in on. I see an IP address associated with them. Does that mean they are coming in on 1433 or 1434?
SQL 2012 Standard VPS Windows 2012 Server Standard
Large Tables In SQL 7.0
Jul 12, 2001We currently have a data warehouse running on SQL 7.0, SP2. One of our primary fact tables is now well over 155 million rows in it. The table is not very wide, as it only contains 17 columns, most of which are defined as integers. The entire database is only 20 GB.
The issue is that the loads from the staging table to this fact table have significantly deteriorated over the last month or so, dropping from over 400 transactions per second to around 85. We drop all the indexes on the fact table before we load the data into it.
Are there issues with a manageable table size in SQL 7.0 that we need to be concerned about? And should we consider partitioning the table into several smaller tables and join them with a "union all" view?
I really need to get this performance issue resolved, as our IT support vendor is pushing us to port the data warehouse to UDB because they tell us that SQL server is not scalable enough to handle this volume of data.
Thanks for any help you can provide.
George M. Parker