Integration Services :: Rebuild Index / Refresh Index And Stats Improves Ssis Package Performance
Oct 28, 2015
My SSIS package is running very slow taking so much time to execute, One task is taking 2hr for inserting 100k records, i have disabled unused index still it is taking time.I am rebuilding/Refreshing indexes and stats once in month if i try to execute on daily basis will it improve my SSIS Package performance?
This calls the Sp that does the Reindex. It fails at the update statistics with a very generic message. like " Command: UPDATE STATISTICS [xxxx_DB].[dbo].[xxxx_xxx] [_WA_Sys_00000007_49C3F6B7] [SQ... The step failed."
I suspect it has more error but this is all it is showing me when I right click on the job history. therefore, I updated the job step in the advance tab with log to a txt file. Am I on the right track or there is another way to see error some where else.
I looked at the logs but they didn't show any thing.
We face slow performance issue for like taking long time for same query execution after We apply index rebuild and reorganize index. But, after execution of query or procedure for 2 -3 times, performance will be faster. I have following questions
1 do we need to update stats after we rebuild an reorganize index. 2. is it will be slow for 1-2 times for every query and stored procedure execution after we rebuild and reorganize index?
Web Base application or PDA devices use to initiate the order from all over the country. The issue is this table is not Partioned but good HP with 30 GB RAM is installed. this is main table that receive 18,0000 hits or more. All brokers and users are using this table to see the status of their order.
The always search by OrderID, or ClientID or order_SubNo, or enter any two like (Client_ID+Order_Sub_ID) or any combination.
Query takes to much time when ever server receive more querys. some orther indexes are also created on the same table like (OrderDate, OrdCreate Date and Status)
My Question are:-
Q1. IF Person "A" query to DB on Client_ID, then what Index will use ? (If any one do Query on any two combination like Client_ID+Order_ID, So what index will be uesd.? How does MS-SQL SERVER deal with these kind of issues.?
Q2. If i create 3 more indexes on ClientID, ORderID and OrdersubID. will this improve the performance of query.if person "A" search record on orderNo so what index will be used. (Mind it their would be 3 seprate indexes for Each PK columns) and composite-Clustered index is also available.?
Q3. I want to check what indexes has been used? on what search?
Q4. How can i check what table was populated when, or last date of update (DML)?
My Limitation is i Dont Create a Partioned table. I dont have permission to do it.
In Teradata we had more than 4 tb record of CRM data with no issue. i am not new baby in db line but not expert in sql server 2003.
I am trying to refresh some excel sheet I get with powerquery. After that I load the data in an sql server table to build a dummy DWH for various testing.
My goal is to automate all task and this include also the excel queries. I have seen that exits on codeplex an excel refresh task but it doesn´t work in my enviorment (SSDT 2013) or at least I have been unable to do it.
I have seen too a pragmaticworks component but I have to pay so I have discarded it.
I have tested a code that I have found in this
thread with minor changes like increase the time to refresh the excel file to 60000 and change the line
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
I run the script but neither fails nor does anything. It run sucefully without refresh. This is the entire code I run
/* Microsoft SQL Server Integration Services Script Task Write scripts using Microsoft Visual C# 2008. The ScriptMain is the entry point class of the script. */ using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using Microsoft.Office.Interop.Excel; namespace ST_53932a75e92c44f086535fc017a56e6a.csproj { [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
[code]....
P.S: I have been playing with the time it needs to refresh and I have found that set the time in 80000 works fine.
One of the major syntax inside the SELECT statment is ..
WHERE FIELDA IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='A') AND WHERE FIELDB IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='B') AND WHERE FIELDC IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='C') AND WHERE FIELDD IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='D') AND WHERE FIELDE IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='E') AND WHERE FIELDF IN (SELECT PARAVALUE FROM PARATABLE WHERE SESSIONID = "XXXXX" AND PARATYPE='F')
(It's to compare the field content with some user input parameter inside a parameter table... )
I think properly is that the SELECT ... IN is causing much slowness in the sql statement. I have indexed FIELDA , FIELDB, FILEDC etc and those PARAVALUE and PARATYPE in the PARATABLE table. But perfromance is still slow and execution takes >20 seconds for 200000 rows of records.
Do any one know if still any chance to improvide the performance like this?
Why the index value is used in variable mapping while using for each loop container. In one of ssis package I saw the index value as 2. What 2 indicates in index?
I have a look up table with old data, which i need to truncate and load with the new set of data, however when loading I'm getting the following error
[OLE DB Destination [32]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Cannot insert duplicate key row in object 'dbo.CaseType' with unique index 'idx_CaseType'. The duplicate key value is (49, AH).".
I know , what it means that since CaseType column has a unique index we cannot insert duplicate key, but in real world the scenarios are different , the record in question is as follows: so what is the workaround in this kind of scenario other than making it Non-unique Index?
I 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.
Rebuild Index job for user db's is failing, one user db is a huge size 120 GB. The job scheduled to run every sunday 1 AM
I found the below error in log report
Rebuild Index Task (server name) Rebuild index on Local server connection Databases: All user databases Object: Tables and views Original amount of free space Task start: 01/13/2008 1:26 AM. Task end: 01/13/2008 2:38 AM. Failed-1073548784) Executing the query "ALTER INDEX [Idx_CISCO_WLC_EVENTID] ON [dbo].[CISCO_WLC_200711262137] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF ) " failed with the following error: "Cannot find the object "dbo.CISCO_WLC_200711262137" because it does not exist or you do not have permissions.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
SSIS script task fail with error message-SSIS script task fail error message Error: Cannot load Counter Name data because an invalid index '' was read from the registry.
I have a table with over 60 million rows (approx 20GB) which has an indexed column. I have tried using DBC DBReindex to rebuild the index, but after kicking it off on a friday, it is still running the following wednesday. Since managers and other finicky types access this database, that's not acceptable (it slows down their reporting).
Is there a way to speed up the reindexing process? Perhaps by adding space to the tempdb (it's 500MB) or putting it in RAM temporarily? I haven't seen any articles that specifically state that TEMPDB is used during an index rebuild, but it seems logical that it would be.
Any suggestions to speed up the process would be most appreciated!
I would like to completely understand the difference between index rebuild Maintenace plan and the customized script.Maintenance Plan rebuilds every single index.It will take the long time as it checks every index.If we use a custom script as a job, it will rebuild the index which has fragmentation >30%.So that, the job will not take much time.
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
Fellow MSSQL DBA's, I am stuck. I am getting a Msg 2511 on a production database. The message reads - Table Corrupt: Keys in leaf page should be in ascending order. I have dropped the offending index and rebuilt both through the application and through ISQL. Neither method fixed the problem. DBCC CHECKDB shows no errors as long as the index does not exist. I have checked out the data and see no problems. Any ideas? Thanks very much.
In SQL Server 2005 EE I created a maintenance plan to rebuild indexes for a few large tables. I have selected five specific tables, and I'm using both "sort results in tempdb" as well as "keep index online while reindexing".
If I execute this plan for all these tables, are the indexes guaranteed to remain online? There are all different types of indexes on these tables. For example, the table "Contacts" has 8 indexes: 1 Clustered, 1 PK Unique Non-Clustered, 2 Unique Non-Clustered, and 4 Non-Unique Non-Clustered. I've heard that only certain types of indexes can remain online during a reindex (Clustered and Non-Unique Non-Clustered??).
Will SQL Server rebuild an index that isn't compatible with the online reindex mode, or will it choose to ignore it?
SQL Server 2005 version: 2153 I created a maintplan for system and user databases includes rebuild index, maint cleanup tasks.
Job is failing for user databases It includes rebuild index task( online index enabled) and maintenance cleanup task, scheduled at every sunday 1 AM.
I receive following errors:
In eventvwr log
sql server scheduled job 'DBMP_RebuildIndex_User' status: failed-Invoked on 2007-12-02 -1:00 Message: The job failed. The job was invoked by schedule 8 ('DBMP_RebuildIndex_User-Schedule).The last step to run was step1 ('DBMP_RebuildIndex_User')[/red]
In log report:
Failed:(-1073548784) Excuting the query "ALTER INDEX [XPKact_log] ON [dbo].[act log] REBUILD WITH (PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON,SORT_IN_TEMPDB=OFF,ONLINE=ON) "failed with the following error "Online index operation cannot be performed for index 'XPKact_log' because the index contains column 'action_desc' of data type text, ntext.image.varchar(max),varbinary(max) or xml. For non clusterd index the column could be an include column of the index. for clusterd index it could be any column of the table .Incase of drop_existing the cloumn could be part of new or old index. The operation must be performed offline". Possible failure reasons : Problems with the querey .'" Resultset" property not set correctly, parameters not set correctly, or connection not established correctly.
Please anyone help me on this? I really appriciate
After rebuilding an index, it still shows as the same amount offragmentation. ANy ideas what's wrong?I'm determining which indexes to rebuild using the following query:SELECTOBJECT_NAME(i.object_id) AS TableName,i.name AS IndexName,ips.avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL,'DETAILED') ipsJOIN sys.indexes i ONi.object_id = ips.object_idAND i.index_id = ips.index_idWHERE ips.avg_fragmentation_in_percent > 10(I know 10% is not enough where a full rebuild is called for, justwanted to see my fragmentation)Then I rebuild w/:ALTER INDEX IX_CustomerName ON Customers REBUILDWhen I rerun the 1st query the same amount of fragmentation is shownas before the rebuild. I'd appreciate any help.
Hi Folks,SQL Server 2000 SP3 on Windows 2000. I have a database on which I ranthe command :dbcc dbreindex ('tablename')gofor all tables in the database. Then I compared the dbcc showcontigwith all_index output from before and after the reindex and on thelargest table in the database I found this. First output is prior toreindex:Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:7TABLE level scan performed.- Pages Scanned................................: 184867- Extents Scanned..............................: 23203- Extent Switches..............................: 23324- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 99.07% [23109:23325]- Logical Scan Fragmentation ..................: 11.13%- Extent Scan Fragmentation ...................: 35.46%- Avg. Bytes Free per Page.....................: 60.0- Avg. Page Density (full).....................: 99.26%Second output is from after the reindex:DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:8TABLE level scan performed.- Pages Scanned................................: 303177- Extents Scanned..............................: 37964- Extent Switches..............................: 42579- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 89.00% [37898:42580]- Logical Scan Fragmentation ..................: 43.19%- Extent Scan Fragmentation ...................: 24.78%- Avg. Bytes Free per Page.....................: 75.1- Avg. Page Density (full).....................: 99.07%Following are my concerns:The following numbers are all higher after reindex than before reindex:pages scanned, extent switches, logical scan fragmentation, avg bytesfree per page, avg page density.scan density is lower after reindex than before reindexSeems to me that the numbers that are higher after reindex should belower and numbers that are lower after reindex should be higher? Ididn't specify the fill factor in the dbcc reindex command so it shouldhave used the default fill factor. The fill factor has never beenchanged on this machine.Am I missing something?Thanks,Raziq.*** Sent via Developersdex http://www.developersdex.com ***
Rebuild 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)?