.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Updating multiple Indexes on a table - Performance issue

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :Sql Server


I have a performance issue when trying to update a table with multiple indexes. The table itself has about 280 million rows. The selection of the records is fast, about 160 ms, as it has a suitable non-clustered index. However the update itself takes over 10 minutes.

When I look at the excution plan, it shows the update to the clustered index as well 5 other non clustered index which are being affected by the statement. Unfortunately it doesn't show me how those indexes are being accessed. I suspect that the update statement is performing a full index scan against each of the non-clustered indexes in order to do the update.

So my question is this, if i add the key columns of the other non-clustered indexes as included columns on the index used to select the records for update will sqlserver use them to access the additional non-clustered indexes?

any advice greatly appreciated


View Complete Post

More Related Resource Links

CURSOR performance on multiple delete/insert and maybe poor table design


We have table documents and table documents_additonal_data, joined via key (docnum, doctype), important to know is that in documents_additonal_data we just have two more fileds valuetype and value (varchar(MAX)), and every record in doc can have variable 0-n valuetypes so there is no schema enforced in any way.

Now I've made cursor that needs to transfer history data from one older table to docs, must use cursor with order by clause because transaction_id, some bussines logic etc.  In short it takes values, have some IF logic on cursor variables, checks if there is already record for that document, some more IF logic on variables (with few simple selects) and calls insert procedure.

About insert procedure, it doesn't update values but reads current, stores old to XMLvalue (xml made with select) in storage table, deletes current, and inserts new data, reason for that is probably multiple indexes  (docs is primarly 'read from' table) and millions of records, update was slower probably because in add_data on update it usually needs to delete, insert, update in the same time. Insert procedure takes XML parameter for additional_data which is then translated and inserted via exec sp_xml_preparedocum

Performance issue populating fact table


I have an ETL job that runs fine until it gets to the step to populate the fact table. It runs for ages, without adding the new data. It used to run in 2 minutes, but I have not changed the definition of the fact table or the dimension tables referenced in the joins.

If I copy the FROM statement from the table definition and use it to perform a SELECT COUNT(*) I get the total back in 5 seconds. There are only 1.3 million rows at present.

Does anyone have any ideas as to why the count runs so quickly yet the actual data upload seems to stall?

Thanks in advance.

updating multiple rows for multiple columns of the table from other table



I have source table called customer. And data from this table is transfered to another database  with same table name.

that is database1(customer table) ----> database 2(customer table)

now in database 2, First time my program runs and it randomly get any name and ssn and then updates the first name, last name, ssn of all the customer_ID's in the customer table (I use RAND function)

Now second when i run the program, it recreates the name and ssn again but different, for the same customer_id.

So i want that once the particular customer_ID is updated, next time if i run the program. it shold not update the those updated customer id's

Thanks, Any help is appreciated.


merging multiple tables in a single dataset to single table


 i have a stored procedure which returns three tables to a dataset ..... now i need to merge all three tables to a single table from d same dataset 

like dataset1 has table1 table2 and table3 .... i want all the three tabels to be merged into dataset1 itself .... instead of three diffrent tables so that i can show all three table data in a single datagrid  as a compact data and combination of 3 tables from d single dataset.....

can some1 help me please.....

Multiple Paramatized Threading issue


I need the funcitonality to have two methods run concurrently of each other and the parent thread (aka the method that makes these two calls) to halt until BOTH are done. After the threads are done, I need to perform logic on the returns of the method, so it is essential for both of them to be complete before the parent thread continues. I have searched the internet and tried varous attacks, and I always come up with the same result, this being the parent thread continues operation after the first thread finishes, leaving the second thread running.  Can someone show me a quick example of how I can do this. I have tried checking states of the threads, halting the threads, but I always end up with the same thing. Any help would be great. Thanks.

Under the Table: How Data Access Code Affects Database Performance


In this article, the author delves into some commonly used ways of writing data access code and looks at the effect they can have on performance.

Bob Beauchemin

MSDN Magazine August 2009

Performance r/w xml and dealing with multiple access.



I've working on a chat type project at the moment. I was originally using a DB but decided to move to XML which has improved speed a lot and also made the control more portable. I have two related issues that I was hoping you guys could pass comment on.

The first is dealing with multiple users either reading or trying to write to the xml file when someone else already has it open. I've had to be an Exception loop around the write process to 'queue' the writes from different users. This means that sometimes there is now an issue with speed of response.

The second issue is to do with the speed at which my code read and writes xml. Speeding it up would help my app generally and but would also help with issue one.

Here is my code. Just so you are aware, when someone makes a post it does Savemessage and then Getmessages. Getmessages also runs every 3 seconds regardless:

public List<Chat.Message> Getmessages(int numberOfMessages, int lastMsgID)
        List<Chat.Message> res = new List<Chat.Message>();

        DataSet xmlDB = new DataSet("xmlMSG");        

        if (xmlDB.Tables.Count > 0)
            // I only want a maximum of the last

Impersonation issue | Multiple domain

Hi,I am working on an Intranet site.The site is running with the following configurationWindows 2003 Server, IIS 6The users of the site come from 3 different domains (say Domain A, B, C). We have Integrated Windows Authentication turned on. App pool identity - Network Service Impersonation is turned Off in web.config. We try to impersonate the original caller programatically for one functionality where we try to access contents of a folder on shared drive by giving a UNC path. System.Security.Principal.WindowsImpersonationContext impersonationContext;impersonationContext = ((System.Security.Principal.WindowsIdentity)Context.User.Identity).Impersonate();...DirectoryInfo rootDir = new DirectoryInfo(path); DirectoryInfo[] dirContents = rootDir.GetDirectories();// We show the folders and files which can be downloaded after this ..impersonationContext.Undo();  The web server and the file share are on domain AIssue The above functionality works fine for all users coming from Domain A but for users coming in from Domain B and C get System.UnauthorizedAccessException at rootDir.GetDirectories(). But when the same users type the same UNC path from Win>Run or File Explorer, they are able to open it.The users are all coming in through IE6, so they do not have to provide their user name, password as trust has been established between all 3 domains. Can anyone please p

Updating table from a table on another server

I am updating a column in table from a table on another server and am having some troubles. My code looks like this...   UPDATE TABLE1 SET COLUMN1A = SERVER2.DATABASE2.SCHEMA2.TABLE2.COLUMN2A FROM TABLE1, SERVER2.DATABASE2.SCHEMA2.TABLE2 WHERE TABLE1.COLUMN1B = SERVER2.DATABASE2.SCHEMA2.TABLE2.COLUMN2B   I keep getting "The multi-part identifier ____ could not be bound."   Security rights are in place and I have access to Server2    Thoughts?

Multiple indexes using same column

I am writing an extensive query and have multiple non-clustered indexs on a table.  I have one index for a single column [Column1] and another index for [Column2], [Column1].  I am using different querys where I sometimes just use [Column1] and other times use many columns including both [Column1], and [Column2]. My question is: If you have multiple indexes that contain the same column will they ever cause a conflict?

Multiple databases on same instance - performance hit?

Hi I am not a SQL person to please excuse me if my terminology is wrong :) We are running SQL 2005 at the moment on Windows 2003 SP2 servers. We have multiple SQL servers spread around in each of our data centres. We have an application we're going to be bringing in that needs it's own SQL database. We need to decide whether we build a dedicated SQL server for this or just create a new database on an exisiting SQL instance. The advantages of the former are that we don't max out any existing servers and affect the performance of other databases, whereas with the latter we save the cost of having to build a new server. For those of you that use SQL heavily, what's the general way of doing things? I know that SQL is memory, disk intensive etc so not sure.

Performance Issue on SQL 2008 Box

Will SSRS and SSIS running on the same machine would hinders the perfromance of a database engine. 1. If so to what extent? 2. How do you tackle this kind of issue. 3. Is there a way you can separate these services from running on the same machine. The machine also has a OLTP database running on it.

How 2 join Multiple Keys based table???

I have a table INC with 2 Columns/Fields, i.e. YR and CL set as primary keys by selecting both the columns and selecting primary key symbol with right click. How to set up a FK with the other table INC_DTL's CL which I seek to be restricted to a combination of the INC's 2 fields? Thanx in advance.

Issue with Job schedule table?

Hi there, I'm trying to create a table/sheet that will display an employer's job schdeule for the week. Most of the information will come from several tables within a database. This table/sheet will consists of the following columns: Job ID Description Comments-> These 3 columns/fields represent actual colums within the database 7 columns representing days of the week, with the first column representing Today's date, the next column representing the next day...-> The 7 columns for the dates will not come from the database itself (as opposed to the other columns). These will be updated daily to represent the employer's schedule for the week. MY ISSUE IS THIS: One of the DB tables holds a column "StartDate" (which represents the start date of a phase within a particular job). I need to check whether this "StartDate" is equal to the date column (the image will make this more clear). Obviously, 1 job has MANY phases. An example of a such table would as follows (for the employee whose D_ID is = '555'):     Any ideas how I can create such a table?? Should it be done in recursive html? Or gridlist? repeater??... Thanks for any info   BTW, I posted this in the "Web forms" section, but I guess it is more suited to be posted here, as it is a databound issue.

Updating multiple tables simultaneously

Hi all, Was wondering if someone can help me out with this multi update problem I have a webform that populates a single table but due to certain dropdown data conditions it could also potentially populate 3 other tables The tables information populates a gridview with amounts from accounts etc.. e.g. Table 1 Site          DataType            account           update 34                  SS                       12.50              N Table 2 Site             account 34                    12.50   Say the above entry has the updated column changed to Y via the webform the amount 12.50 should disappear from the gridview. I was a bit shortsighted and added the additional tables afterward. The information on the gridview pulls from the additional tables and since the first table and the additio

MOSS 2007 Alerts - Multiple Emails Issue

I've been experiencing a strange issue with SharePoint alerts that seem to happen randomly.  I've setup a daily alert on a custom list that sends a summary of all changes at 5pm, but on some days I'll receive more than 1 summary email (150+ this past Tuesday).  Each email contains different information and the total number of emails is random along with the number of records in each email.  Some days the summary comes in one email and others it comes in multiple.  This is happening on our production server and I haven't been able to recreate the issue in development.  Does anyone have any thoughts as to how a daily alert would get split up into several emails?We have 1 MOSS 2007 app server, 1 index server and 1 SQL 2005 database server.

Issue with Alerts with multiple Managed MetaData fields?

i'm seeing an issue in SharePoint 2010 with alerts.  If a list has more than 1 Managed MetaData columns, alerts stop working.  If I take it down to only one managed metadata column, they start working again.  Is this a known issue?  I'm seeing it in multiple sites/mulitple users.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend