I have 2 identical DB on the same server with the same indexes on both db, but when I run a query using query analyzer, on db A it will take 6 sec but run the same query on db B it will take 1 minute. I checked the indexes on all tables in 2 db's are the same.
I used show execution plan, it does show that it is not using some indexes on the 2nd db, I droped all indexes and rebuild them again, but the same result.
Any idea why it is taking so long on the other db.
SELECT a.AssetGuid, a.Name, a.LocationGuid FROM Asset a WHERE a.AssociationGuid IN ( SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
takes 30-60 seconds to run on my machine, due to a clustered index scan on our an index on asset [about half a million rows]. For this particular association less than 50 rows are returned.
expanding the inner select into a list of guids the query runs instantly:
SELECT a.AssetGuid, a.Name, a.LocationGuid FROM Asset a WHERE a.AssociationGuid IN ( '0F9C1654-9FAC-45FC-9997-5EBDAD21A4B4', '52C616C0-C4C5-45F4-B691-7FA83462CA34', 'C95A6669-D6D1-460A-BC2F-C0F6756A234D')
It runs instantly because of doing a clustered index seek [on the same index as the previous query] instead of a scan. The index in question IX_Asset_AssociationGuid is a nonclustered index on Asset.AssociationGuid.
The tables involved:
Asset, represents an asset. Primary key is AssetGuid, there is an index/FK on Asset.AssociationGuid. The asset table has 28 columns or so... Association, kind of like a place, associations exist in a tree where one association can contain any number of child associations. Each association has a ParentAssociationGuid pointing to its parent. Only leaf associations contain assets. AssociationDataAssociation, a table consisting of two columns, AssociationGuid, DataAssociationGuid. This is a table used to quickly find leaf associations [DataAssociationGuid] beneath a particular association [AssociationGuid]. In the above case the inner select () returns 3 rows.
I'd include .sqlplan files or screenshots, but I don't see a way to attach them.
I understand I can specify to use the index manually [and this also runs instantly], but for such a simple query it is peculiar it is necesscary. This is the query with the index specified manually:
SELECT a.AssetGuid, a.Name, a.LocationGuid FROM Asset a WITH (INDEX (IX_Asset_AssociationGuid)) WHERE a.AssociationGuid IN ( SELECT ada.DataAssociationGuid FROM AssociationDataAssociation ada WHERE ada.AssociationGuid = '568B40AD-5133-4237-9F3C-F8EA9D472662')
To repeat/clarify my question, why might this not be doing a clustered index seek with the first query?
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
I'm not the most knowledgable DBA, I've had to learn almost completely on my own, AND on a production server, because it's the only MS SQL Server I have access to.
Everything was fine before I took down the production server for maintenance. Someone suggested that I re-index my tables because I was having some performance issues with a particularly large table (it didn't help that table btw), so I did re-index.
Now, Everything works wonderfully, except for the performance issue mentioned AND one other thing that is going horribly wrong.
Here is the table:
create table ABMcontactlink ( classifier varchar(20) not null, /* Classification of contact. */ transmitter varchar(36) not null, contact integer not null, /* Link to ABMcontact (detail) table */ primary key (classifier,transmitter,contact), foreign key (contact) references ABMcontacts(identifier), group_name varchar(20) null,
last_modification_date datetime, /* Date/time record last touched */ last_modification_id varchar(40) /* Who last touched record */ )
go create index IndexABMcontactlink on ABMcontactlink(classifier,transmitter) go
create index CandidateABMcontactlink on ABMcontactlink(transmitter) go
As you can see, I have the primary key, which creates a clustered index, PK_ABMContactlink_Some Number, and two other indexes.
Now, this is a very busy production database, and most quick short queries benefit more from CandidateABMContactlink than from the other two indexes.
Unfortunately, in this production system, and this table, seconds count ALOT, so when I have roughly 3000-4000 quereies an hour pulling information from this table, I personally beleive I need to keep CandidateABMContactlink, and I'm not willing to find out on a production server.
** Now to the Problem at Hand **
I have one query that kicks off about 7 times a day, used to take less than 1 minute before the re-index. NOW it takes 30 Minutes. And it drags the system to a crawl.
I did some looking into it, and this query is using CandidateABMContactlink, and it takes 30 minutes. If it uses PK_Abmcontactlink it finishes in under 45 seconds.
Most queries are simple, "Select Column_names from abmcontacts where identifier in (select contact from abmcontactlink where transmitter = 'XXXXXX')"
This one is:
select * from ABMcontacts where ( (last_modification_date >= '2006-04-28 04:40:03' and last_modification_date <= '2006-05-09 16:41:14') and EXISTS(select contact from ABMcontactlink where contact = identifier and EXISTS(select transmitter_id from ABMtransmitter where transmitter_id = transmitter and (dealer = 'XXXX'))))
or (EXISTS(select contact from ABMcontactlink where (last_modification_date >= '2006-04-28 04:40:03' and last_modification_date <= '2006-05-09 16:41:14') and contact = identifier and EXISTS(select transmitter_id from ABMtransmitter where transmitter_id = transmitter and (dealer = 'XXXX'))))
I can't change the query, so how do I make it use the Index I want it to use without removing the index that it is using? (I know there are much better ways to write the above query, I'm not the culprit, if I could re-write it, I would)
I have a view (table_rol_contct) on a number of tables, one of which is a table of address data (table_address). This table has a unique, non-clustered index on Postcode, House Number and an Id column (idx_x_s_postcode). There is a clustered index on the Id column.
There are a number of queries which are run against this view, one of which selects addresses whose Postcode matches a full or partial Postcode entered through a Client Application.
This query :
Select distinct top 30 con.salutation+' '+con.first_name+' '+con.last_name as fullname, con.title, con.phone, con.x_housename, con.x_houseno, con.x_housename+' '+con.x_houseno+' '+con.address+', '+con.address_2+' '+con.city as fulladdress, from table_rol_contct con where con.x_s_postcode like 'wf18%' order by con.last_name asc, con.first_name asc
returns its results in under a second while this one:
Select distinct top 30 con.salutation+' '+con.first_name+' '+con.last_name as fullname, con.title, con.phone, con.x_housename, con.x_houseno, con.x_housename+' '+con.x_houseno+' '+con.address+', '+con.address_2+' '+con.city as fulladdress, from table_rol_contct con where con.x_s_postcode like 'wf3%' order by con.last_name asc, con.first_name asc
takes 38 seconds to returns its results.
The execution plans for these queries show that the slow query uses a Clustered Index Scan whereas the quick one uses an index seek on the ‘idx_x_s_postcode’ non-clustered index.
From this, you can see that ‘WF18%’ is wholly contained in a single step and the optimizer appears to happily perform an index seek, whereas ‘WF3%’ spans more than 1 so the optimizer seems to choose a clustered index scan.
Is there any way that I can force the use of the non-clustered index or, at least, make it more likely that the optimizer will use this index without coding an optimizer hint into the ‘Create View’ statement? The problem is that the view is used for other queries, all the queries run against it are generated from within a client application and the given search criteria could, for example, be ‘con.address’ rather than ‘con.x_s_postcode’.
Hi,I have a table t1 with columns a,b,c,d,e,f,g,h,i,j,k,lI have created a clustered index on a,b,d,e which forms the primarykey. I have created a covering index on all the columns of t1. Thereare 1 million rows in this table.My query chooses the TOP20 rows based on some filter conditions. WhenI use an "ORDER BY 1", it uses the clustered index and I get the resultin 1 second, whereas it takes around 1minute 48seconds when I use an"ORDER BY b or any other column". It is not using the covering / theclustered index.What is the best way to index this table so that it uses the index andI get the result within the shortest possible time (just like that ofORDER BY 1 which take hardly a second).Thanks..Sridhar
Hi,I am making as SELECT query to fill a repeater, and I need to retrieve the index of each line of the query.ie, I want to get a dataset like :"0", "dataCol1", "dataCol2" for the first line"1", "dataCol1", "dataCol2" for the second line"2", "dataCol1", "dataCol2" for the third lineetc.Anyone knows if there is a sql statement that does it ?ThanksJohann
hi, what are the tools that I can use to Optimize the query/ index.
I know that if I am running a query on a table I create index on the fields where I use in the where clause, is this a right thinking.
Someone told me how do I determine what columns should be indexed, I told him the fields that I use in the where clause should be indexed to speed up the process of retrieving the data. ..... is this answer correct. if not please advice the correct one. Thanks
I have recently upgraded my Access Database too use SQL server 2005 using the upsizing wizard. I have selected too use SQL Linked tables instead off the ADP project for ease off conversion. So now the tables are on the SQL server and the queries are still local.
On off my Access forms which returns a datasheet view by accessing the a query is now running really slowly now SQL server is the backend, I was wondering if I can put an index on one of the tables too speed it up, as the query is local too Access will this work? Just wondering if anyone ever come across this.
I am trying to resolve performance issues in a third party application. I have run the profiler and found a transaction that performs a table scan against a 6 million row table. This transaction occurs repeatedly, so I thought, just add an index on the columns in the where clause used here. After adding the index, I looked at the estimated execution plan in Query analyzer, and I find that it is still performing the table scan. If I run the query it takes over 60 seconds to run, if i add an index hint, it runs in under a second. I ran DBCC SHOW_STATISTICS to see if the statistics were up to date:
Statistics for INDEX 'IX_Finish_dept'. Updated Rows Rows Sampled Steps Density Average key length -------------------- -------------------- -------------------- ------ ------------------------ ------------------------ Jun 26 2007 5:18PM 6832336 6832336 150 2.1415579E-7 18.0
(1 row(s) affected)
All density Average Length Columns ------------------------ ------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 2.1875491E-7 8.0 finish 1.9796084E-7 18.0 finish, dept
use tempdb go if object_id('Data', 'u') is not null drop table Data go with temp as ( select top 10000 row_number() over (order by c1.object_id) Id from sys.columns c1 cross join sys.columns c2
[code]....
What index would be best for these three queries? With best I mean the execution time, I don't care about additional space.
This is the index I currently use: create nonclustered index Ix_Data on Data (StateId, PalletId, BoxId, Id) The execution plan is SELECT (0%) - Stream Aggregate (10%) - Index Scan (90%).
Can this be optimized (maybe to use Index Seek method)?
I've got this query that runs in 30 seconds and returns about 24000. The table variable returns about 145 rows (no performance issue here), and the TransactionTbl table has 14.2 Million rows, a compound, clustered primary key, and 6 non-clustered indexes, none of which meet the needs of the query.
Actual execution plan shows SQL is doing an index seek, then a nested loop join, and then fetching the remaining data from the TransactionTbl using a Key Lookup.
I designed a new indexes based on the query, which when I force it's usage via an index hint, reduces the run time to sub-second, but without the index hint the SQL optimiser won't use the new index, which looks like this:
CREATE INDEX IX_Test on GLSchemB.TransactionTbl (CltID, Date) include (Ledger_Code, Amount, CurrencyID, AssetID)and I tried this: CREATE INDEX IX_Test on GLSchemB.TransactionTbl (CltID, Date, Ledger_Code, CurrencyID, AssetID) include (Amount)and even a full covering index!
I did some testing, including disabling all indexes but the PK, and the optimiser tells me I've got a missing index and recommends I create one EXACTLY like the one I designed, but when I put my one back it doesn't use it.
I though this may be due to fragmentation and/or stats being out of date, so I rebuilt the PK and my index, and the optimiser started using my index, doing an index seek and running sub-second. Thinking I had solved the problem I rebuilt all the indexes, testing after each one, and my index was used BUT as soon as I flushed the related query plan, the optimiser went back to using a less optimal index, with a seek and key lookup plan and taking 30 seconds.
For now I've resorted to using the OPTION (TABLE HINT(G, INDEX(IX_Test))) to force this, but it's a work around only. Why the optimiser would select a less optimal query plan?
I have a table that seems to have a bad index. When I do the followingquery I get inconsistant and needless to say incorrect results.select count(*) from mytable where mycolumn = 1If I remove the index from "mycolumn" the query works correctly. If Iadd the index back (even with a new name etc...) it doesn't workright.Has anyone ran into this? or does anyone know how I can fix thisproblem?It seems that removing the index is not really removing everythingbecause when I add a new one I get this same problem... btw, this isisolated to this column on this table. all other indexes within thedatabase are fine.Any help would be appreciated.Thanks,dharper
SET @propname = NULL -- can be Null or have wild card values
SELECT col1, col2, col3, propertyname FROM testtable where col1 = 1 and col2 = 2 and col3 = 3 and (@propname IS NULL OR propertyname LIKE @propname)
col1, col2, col3 are part of a clustered index
propertyname is a nonclustered index
This is the predicament. If the "@propname is NULL" is first in the OR statement then the query will use the clustered index for finding the record. If I put "@propname is NULL" last then it uses the propertyname index no matter what. So I either get full index scans if NULL is ever used on property names or I get consistent two second long searches on the clustered index. Any way to have the best of both worlds? Or do I have to divide up my query into more stored procedures?
Create Index ind_Item_Name on Item(I_Name); Create Index ind_Item_BC on Item(I_BC); Create Index ind_Item_Company on Item(I_Company); Create Index ind_Item_CompanyFound on Item(I_CompanyFound); create Index ind_Item_i1 on Item(I_Company,I_CompanyFound); create Index ind_Item_i2 on Item(I_CompanyFound,I_Company);
Now this query DOES NOT use index: select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found from Item where (I_Company='102' or I_CompanyFound='102' )
While this one use:
select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found from Item where (I_Company='102' ) UNION select I_Name, I_Code, I_MatID, I_BC, I_Company,I_Info1, I_Acquired, I_CompanyFound, 0 as I_Found from Item where (I_CompanyFound='102' )
Both return the same rows. Is this a bug? I found the following: http://support.microsoft.com/kb/223423
We are using SQL2005. I have a stored proc that runs a Select query based on a complex view. The sproc has two input date parameters (StartDate and EndDate). We are experiencing SQL timeout problems when the sproc is run with certain Start and End Dates. We have run the SQL Profiler and created a trace (trc) file (We've used the 'Default' trace configuration). We have used the trace file in SQL Server Management Studio to try and automatically create indexes on some of our tables. Unfortunatly SQL Server Management does not make any index recommendations. I think we are not capturing the right information in our trace file to allow SQL Server Management Studio to do its job. How do I use SQL Profiler to capture a trace of my sprocs query, so that it can be used by SQL Server Management Studio, to recommend index changes? Any help appreciated. Reagrds, Paul.
I have an index that shows distribution statistics of 98.20%, which is very poor. I set show query plan and show statis I/O on. This table has 1113675 rows of data.
************* select orderID, custId, intertcsi from tblorders where intertcsi = '2815'
STEP 1 The type of query is SELECT FROM TABLE tblorders Nested iteration Index : indxInterTCSI orderID custId intertcsi ----------- ----------- --------- 1015245 1011313 2815 2556392 2556392 2815 ....
Table: tblOrders scan count 1, logical reads: 104, physical reads: 58, read ahead reads: 0 *************** Then I use the same select statement to force a table scan:
select orderID, custId, intertcsi from tblorders (index=0) where intertcsi = '2815'
STEP 1 The type of query is SELECT FROM TABLE tblorders Nested iteration Table Scan orderID custId intertcsi ----------- ----------- --------- 60472 61084 2815 102184 102333 2815 ... Table: tblOrders scan count 1, logical reads: 110795, physical reads: 6891, read ahead reads: 103980
When the index is not provided, the logical reads and physical reads increased dramatically. Does this tell me that I should keep that index though it is a poor selection? Is that because a huge table like this make the optimizer use the index. The query without using index takes longer time to run. Any idea or comment would be very appreciated.
Create Index ind_Item_Name on Item(I_Name); Create Index ind_Item_BC on Item(I_BC); Create Index ind_Item_Company on Item(I_Company);
It is populated with 50 000 records. Searching on indexed columns is fast, but I've run into the following problem: I need to get all distinct companies in the table. I've tried with these two queries, but they both are very slow!
1. "select I_Company from item group by I_Company " - This one takes 19 seconds
2. "select distinct(I_Company) from item" -This one takes 29 secons
When I ran them through the SQL Management Studio and checked the performance plan, I saw that the second one doesn't use index at all ! So I focused on the first... The first one used index (it took it 15% of the time), but then it ran the "stream aggregate" which took 85% of the time ! Actully 15% of 19 seconds - about 2 seconds is pretty much enough for me. But it looks that aggregate function is run for nothing! So is it possible to force the query engine of the SSCE not to run it, since there is actually no aggregate functions in my select clause? According to SQL CE Books online: Group By
"Specifies the groups (equivalence classes) that output rows are to be placed in. If aggregate functions are included in the SELECT clause <select list>, the GROUP BY clause calculates a summary value for each group." It seems the aggregate is run every time, not only when there is an aggregate function.
We have one LEDGER, where all the daily activities are stored. The LEDGER table has 4 indexes (1 clustered and 3 non-clustered). To get AR we use this table.
Well problem is some times in 1-2 months, any simple AR query takes a long time and every other client gets slow response (queries are very slow or sometimes block).
If we DROP any index on LEDGER table and again put it back (RECREATE), all our queries work fine and faster. This goes on till 1-2 months, till we see the same issue again.
This is a classic case happened today. Queries were running fine till morning 8 AM. We upload some 50 thousand records to Ledger table (Data Conversion). Well after 30 mins, all simple AR queries started taking a long time. We DROPPED an index in LEDGER table and everything was faster....Just to be same we added back the same index again.......everything is Faster.....
What is this. ....is it our QUERY, index or huge Transactions or no free space ???
We are scheduled to run SP4, next week. But is there any solution in the mean time on what is this?
Also is they any way to KILL all SQL server processes that take more than a mins. We just don't want ALL our client to Slow down because of one query????
I had some SQL queries which are using department ID for join , filter , Group By and Select so , i am having index on department ID of my table File Master scheme ..
CREATE TABLE [dbo].[FILE_MASTER]( [FILE_ID] [INT] IDENTITY(1,1) NOT NULL, [DEPARTMENT_ID] [INT] NULL, Â Â Â Â [CLIENT_ID] [INT] NULL Â Â Â Â ,[LEAD_DETAIL_ID] [INT] NULL
[code]....
The above index only working when there is condition or group by on department ID .and i when i am querying ..
SELECT DISTINCT CL.CLIENT_ID,CL.LOAN_SANCTION_DATE,MIN(CL.INWARD_DATE)AS Inward FROM dbo.FILE_MASTER AS CLÂ GROUP BY CL.CLIENT_ID,CL.LOAN_SANCTION_DATE
and the plan is showing Index scan on index Indx_FM_department_ID .. Why it is not using Index seek , i guess i have both group by Columns in cover index included columns what is the use of cover index then ?
because if i am giving where condition before group by for specific Client ID , Loan Sanction Date it is telling to create separate index on client ID , Loan Sanction Date as per Execution Plan missing index detail ..
I want to create index for hash table (#TEMPJOIN2) to reduce the update query run time. But I am getting "Warning!
The maximum key length is 900 bytes. The index 'R5IDX_TMP' has maximum length of 1013 bytes. For some combination of large values, the insert/update operation will fail". What is the right way to create index on temporary table.
Update query is running(without index) for 6 hours 30 minutes. My aim to reduce the run time by creating index.Â
And also I am not sure, whether creating index in more columns will create issue or not.
Attached the update query and index query.
CREATE NONCLUSTERED INDEX [R5IDX_TMP] ON #TEMPJOIN2 ( [PART] ASC, [ORG] ASC, [SPLRNAME] ASC, [REPITEM] ASC, [RFQ] ASC,Â
I 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.
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.
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?Â
hello friends i have table1 and 200 coulumn of table1 :) i have 647.600 records. i entered my records to table1 with for step to code lines in one day :) i select category1 category2 and category3 with select code but i have just one index.. it is productnumber and it is primarykey..So my select code lines is so slow.. it is 7-9 second.. how can i select in 0.1 second ? Should i create index for category1 and category2 and category3 ? But i dont know create index.. My select code lines is below.. Could you learn me and show me index for it ?? or Could you learn me and show me fast Select code lines and index or etc ??? Also my search code line have a dangerous releated to attaching table1 with hackers :) cheersi send 3 value of treview1 node and childnode and child.childnode to below page.aspx :) Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not Me.IsPostBack Then If Request("TextBox1") IsNot Nothing ThenTextBox1.Text = Request("TextBox1") End If If Request("TextBox2") IsNot Nothing ThenTextBox2.Text = Request("TextBox2") End If If Request("TextBox3") IsNot Nothing ThenTextBox3.Text = Request("TextBox3") End If End If Dim searchword As String If Request("TextBox3") = "" And Request("TextBox2") = "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "'" End If If Request("TextBox3") = "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "'" End If If Request("TextBox3") <> "" And Request("TextBox2") <> "" And Request("TextBox1") <> "" Then searchword = "Select * from urunlistesi where kategori= '" & Request("TextBox1") & "' and kategori1= '" & Request("TextBox2") & "' and kategori2= '" & Request("TextBox3") & "'" End If SqlDataSource1.SelectCommand = searchword End Sub
I'm running a merge replication on a sql2k machine to 6 sql2k subscribers. Since a few day's only one of the merge agents fail's with the following error:
The merge process could not retrieve generation information at the 'Subscriber'. The index entry for row ID was not found in index ID 3, of table 357576312, in database 'PBB006'.
All DBCC CHECKDB command's return 0 errors :confused: I'm not sure if the table that's referred to in the message is on the distribution side or the subscribers side? A select * from sysobjects where id=357576312 gives different results on both sides . .
Hi everyone, When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ?? In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ??
