We are load testing SQL 2005 and I need to re-write the index scripts that we have from 2000. Is there an easier way to rewrite the scripts without having to go to each job, then each step and modify it?
Our current index script template is:
Create NonClustered Index [IdxABCDE]
On dbo.blahblah(blahID)
With FillFactor = 90, Statistics_NoRecompute
On [Index2]
Go
and I need to rewrite it as:
ALTER INDEX [IdxABCDE] ON [dbo].[blahblah]
REBUILD WITH (FILLFACTOR = 90, ONLINE = OFF,SORT_IN_TEMPDB=ON, STATISTICS_NORECOMPUTE = ON, MAXDOP=4)
I am thinking I would have to rewrite the scripts from scratch. We have hundreds of index scripts. So before I brace myself to lot of typing, just wanted to find out if there is any easier way..
Thanks..
Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
SELECT Loan.loan_No AS Loan_No, Loan.customer_No AS Customer_No, Customer.first_name AS First_name, Customer.second_name AS Second_name, Customer.surname AS Surname, Customer.initials AS Initials, Bank.Bank_name AS Bank_name, Branch.Branch_name AS Branch_name, Branch.branch_code AS Branch_code , Bank_detail.bank_acc_type AS Bank_acc_type, Transaction_Record.transaction_Amount AS Transaction_Amount, Transaction_Record.transaction_Date AS Transaction_Date, Loan.product AS Product, Product.product_Type AS Product_Type, Product_Type.loan_Type AS Loan_Type
FROM Transaction_Record INNER JOIN Loan ON Transaction_Record.loan_No = Loan.loan_No INNER JOIN Product ON Loan.product = Product.product INNER JOIN Customer ON Loan.customer_No = Customer.customer_no INNER JOIN Bank_detail ON Customer.customer_no = Bank_detail.customer_no INNER JOIN Branch ON Bank_detail.Branch = Branch.Branch INNER JOIN Bank ON Branch.Bank = Bank.Bank INNER JOIN Product_Type ON Product.product_Type = Product_Type.product_Type
Here's the code I'd like to update, and below it a set of sample data:
Declare @StartDate DateTime Declare @EndDate DateTime Set @StartDate = '20-mar-2008' Set @EndDate = '25-mar-2008'
SELECT
COUNT (iqs.childid) as Cnt ,CASE
--Search Categories and Create Initial Groupings-- WHEN Category LIKE '%Jewel%' THEN 'Accessories' WHEN Category LIKE '%Beauty%' THEN 'Accessories' WHEN Category LIKE '%Accs%' THEN 'Accessories' WHEN Category LIKE '%Gift%' THEN 'Accessories' WHEN Category LIKE '%Grooming%' THEN 'Accessories' WHEN Category LIKE '%Female%Prem%Brands%' THEN 'WomensPremiumOutsideBrand' WHEN Category LIKE '%Female%Prem%OB%' THEN 'WomensPremiumOwnBrand' WHEN Category LIKE '%Female%Brand%' THEN 'WomensOutsideBrand' WHEN Category LIKE '%Female%OB%%' THEN 'WomensOwnBrand' WHEN Category LIKE '%Female%' THEN 'Womenswear' WHEN Category LIKE '%Male%Prem%Brands%' THEN 'MensPremiumOutsideBrand' WHEN Category LIKE '%Male%Prem%OB%' THEN 'MensPremiumOwnBrand' WHEN Category LIKE '%Male%Brand%' THEN 'MensOutsideBrand' WHEN Category LIKE '%Male%OB%' THEN 'MensOwnBrand' WHEN Category LIKE '%Male%' THEN 'MensOwnBrand' END AS CategoryGroup ,CONVERT(VARCHAR(10), iqs.StatusDate, 103) AS StatusDate
FROM InventoryQueryStatus iqs JOIN InventoryStatus [is] ON [is].StatusID = iqs.StatusID JOIN Inventory i ON i.InventoryID = iqs.InventoryID JOIN InventoryCategory ic ON ic.CategoryID = i.CategoryID WHERE iqs.StatusID = 31000 and Category NOT LIKE 'Force%' --AND iqs.StatusDate >=GETDATE()-1 AND iqs.StatusDate BETWEEN @StartDate AND @EndDate GROUP BY CASE
--Search Categories and Create Initial Groupings-- WHEN Category LIKE '%Jewel%' THEN 'Accessories' WHEN Category LIKE '%Beauty%' THEN 'Accessories' WHEN Category LIKE '%Accs%' THEN 'Accessories' WHEN Category LIKE '%Gift%' THEN 'Accessories' WHEN Category LIKE '%Grooming%' THEN 'Accessories' WHEN Category LIKE '%Female%Prem%Brands%' THEN 'WomensPremiumOutsideBrand' WHEN Category LIKE '%Female%Prem%OB%' THEN 'WomensPremiumOwnBrand' WHEN Category LIKE '%Female%Brand%' THEN 'WomensOutsideBrand' WHEN Category LIKE '%Female%OB%%' THEN 'WomensOwnBrand' WHEN Category LIKE '%Female%' THEN 'Womenswear' WHEN Category LIKE '%Male%Prem%Brands%' THEN 'MensPremiumOutsideBrand' WHEN Category LIKE '%Male%Prem%OB%' THEN 'MensPremiumOwnBrand' WHEN Category LIKE '%Male%Brand%' THEN 'MensOutsideBrand' WHEN Category LIKE '%Male%OB%' THEN 'MensOwnBrand' WHEN Category LIKE '%Male%' THEN 'MensOwnBrand' END ,CONVERT(VARCHAR(10), iqs.StatusDate, 103)
I have a WHERE clause that could be an "=" or a "LIKE" depending uponif the passed variable is populated or not. I would like to know thebest way to write the WHERE clause to make it dynamically switchbetween the 2 and make best use of the indexes.CREATE TABLE myTable(ID INT PRIMARY KEY CLUSTERED, COUNTY VARCHAR(50))CREATE INDEX IDX_myTable_County ON myTable(COUNTY)DECLARE @COUNTY VARCHAR(50)SET @COUNTY = 'SANTA CLARA' -- Could also be SET @COUNTY = NULLSELECT ID FROM myTableWHERE COUNTY LIKE (CASE WHEN @COUNTY IS NOT NULL THEN @COUNTY ELSE '%'END)This does not seem like best practice to me because I am forced to use"LIKE" even when @COUNTY is populated with data. Ultimately I'd like:WHERE (CASE WHEN @COUNTY IS NOT NULL COUNTY = @COUNTY ELSE COUNTY LIKE'%' END)but that is incorrect syntax on "=".Also, I do not want to use a dynamically built statement. Is there away around this?Thanks,Josh
SELECT CASE WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END AS Population_Range, COUNT(CASE WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END) AS [No. Of Countries] FROM Country GROUP BY CASE WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END
I have a complex stored procedure that utilises inner joins, and in the WHERE clause there is defined a subquery. This subquery has now cause a performance hit to the ponit where the server is returning a timeout. Need to find an alternate fast solution.....
SELECT BE.BlogEntryID
FROM vw_BlogEntry BE
INNER JOIN @BlogView BC ON BC.CommonID = BE.BlogCommonID
INNER JOIN vw_Blog B ON B.BlogID = BC.BlogID
WHERE (
... ) AND (
.... )
AND
(
-- GET ENTRIES WHERE COMMENT COUNT IS AT LEAST @CommentCount (..or @CommentCount = 0)
@CommentCount <= 0
OR BE.CommonID IN (SELECT bc.EntryCommonID FROM vw_BlogComment_Current bc
INNER JOIN tblVersionDetails vd ON bc.VersionID = vd.VersionID
WHERE
IsNull(@CommentStatus,'') = ''
OR vd.Status IN (SELECT * FROM fn_CsvToString(@CommentStatus))
This works, but it's highly unefficient and generates a lot of IO. Is there another way to do it without the use of temp tables?
Code Block select eh.* from Equipment_History eh where Equipment_History_ID in (select top 2 Equipment_History_ID from Equipment_History eh1 where Equipment_ID in (select Equipment_ID from Equipment_History eh2 where Equipment_History_ID in (select min(Equipment_History_ID) from Equipment_History eh3 where eh2.Equipment_ID = eh3.Equipment_ID and eh2.Equipment_Status_Type in (1,2,3))) and eh1.Equipment_ID = eh.Equipment_ID order by Equipment_History_ID)
Equipment_History_ID is PK
Let me know what other information would be useful, I can barely understand my logic from looking at the code but it does in fact work.
I have to modify a stored procedure that is written by someone else.Basically the stored prcoedure uses a cursor to fetch the data from the table and then insert that data in another table. While fetching the code form another table, it also gets some distinct columns from another table Below is my code:
Declare data_cursor cursor for Select emp_no, emp_name, event_date, Test_no, Code, Test_result From test_table1 order by emp_no
[code]...
The reason, I have to modify the above stored proc because now because of application changes, I am getting around 50 distinct userID from test_table1 so the above subquery(SELECT @ProcessName = (select distinct userID from test_table1) won't work. How can I loop through the above stored proc so that each @ProcessName can get inserted in table TESTTable2 so in other words
I want to pass each userId one at a time and insert it in table test_table1 and other subsequent tables. I can declare another cursor to accomplish this, but I was wondering if there is any better way to rewrite this stored proc and not use the cursor at all.because of my application changes all these three statements above are throwing the error:
I have a query with 11 left joins. Some hits against tables with small amounts of reference data, whereas others are not so small. Should I rewrite this in another way, as performance is a requirement on this one? Or, should I do it another way?
I have a query which spends a lot of time calculating my CASE WHEN -statements.
My query looks like this
SELECT TOP 250
UserId, CASE WHEN
(someCol*0.4+someOtherCol*0.3) > 99 THEN 99 ELSE
(someCol*0.4+someOtherCol*0.3) END FROM
(
SELECT
UserId,
CASE WHEN @myparam > 50 THEN
CASE WHEN Col5+@myincrease > 99 THEN 99 ELSE Col5+@myincrease END ELSE
CASE WHEN Col6+@myincrease > 99 THEN 99 ELSE Col6+@myincrease ENDEND as someCol, CASE WHEN Col8+@myincrease3 > 99 THEN 99 ELSE Col8+@myincrease3 END as SomeOtherCol FROM
SomeTable ) t1
This is just a little bit of the full query. I cannot write the whole query since it contains alot of different views and calculations, but I have traced it down to that all these case when-statements is taking a lot of time to do. So I hope that this example will be enough for you to understand my problem.
I know about some tricks that can replace a CASE WHEN, for example using COALESCE or BETWEEN but that does not seem to work in my case.
Use the view master.sys.sql_logins (new in 2005) to get at the varbinary passwords like you did in your Sql Server 2000 scripts (instead of using passwords from master.dbo.sysxlogins).
I have altered the sp_help_revlogin (from Microsoft article # 246133 )
PLEASE TEST/FIX before you use this:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_help_revlogin_2005]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_help_revlogin_2005]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE sp_help_revlogin_2005 @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @logintype char(1)
DECLARE @logindisabled int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, type, is_disabled FROM master.sys.server_principals
WHERE name <> 'sa' and type in ('S','U','G')
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, type, is_disabled FROM master.sys.server_principals
WHERE name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @logintype, @logindisabled
I have a activeX script like below in DTS and I am trying to rewrite it in SSIS. What is the best way to do this? Can I do this using a look up table? or other transformers in SSIS '********************************************************************** ' Visual Basic Transformation Script '************************************************************************
' Copy each source column to the destination column Function Main() if DTSSource("RACE_AMERICAN_INDIAN_YN") = "1" then DTSDestination("RACE_NATIVE_AM_IND") = "Y" else if DTSSource("RACE_AMERICAN_INDIAN_YN") = "2" then DTSDestination("RACE_NATIVE_AM_IND") = "N" end if end if
if DTSSource("RACE_ASIAN_YN") = "1" then DTSDestination("RACE_ASIAN_IND") = "Y" else if DTSSource("RACE_ASIAN_YN") = "2" then DTSDestination("RACE_ASIAN_IND") = "N" end if end if
if DTSSource("RACE_AFRICAN_AMERICAN_YN") = "1" then DTSDestination("RACE_BLACK_IND") = "Y" else if DTSSource("RACE_AFRICAN_AMERICAN_YN") = "2" then DTSDestination("RACE_BLACK_IND") = "N" end if end if
if DTSSource("RACE_NATIVE_HAWAIIAN_YN") = "1" then DTSDestination("RACE_HAWAIIAN_IND") = "Y" else if DTSSource("RACE_NATIVE_HAWAIIAN_YN") = "2" then DTSDestination("RACE_HAWAIIAN_IND") = "N" end if end if
if DTSSource("RACE_CAUCASIAN_YN") = "1" then DTSDestination("RACE_WHITE_IND") = "Y" else if DTSSource("RACE_CAUCASIAN_YN") = "2" then DTSDestination("RACE_WHITE_IND") = "N" end if end if
if CInt (DTSSource("RACE_AMERICAN_INDIAN_YN")) + CInt (DTSSource("RACE_ASIAN_YN")) + CInt (DTSSource("RACE_AFRICAN_AMERICAN_YN")) + CInt (DTSSource("RACE_NATIVE_HAWAIIAN_YN")) + CInt (DTSSource("RACE_CAUCASIAN_YN")) = 9 then if DTSSource("RACE_AMERICAN_INDIAN_YN") = "1" then DTSDestination ("RACE_CD") = 40 DTSDestination ("RACE_MULT_IND") = "N" DTSDestination ("RACE_OTH_IND") = "N" else if DTSSource ("RACE_ASIAN_YN") = "1" then DTSDestination ("RACE_CD") = 16 DTSDestination ("RACE_MULT_IND") = "N" DTSDestination ("RACE_OTH_IND") = "N" else if DTSSource ("RACE_AFRICAN_AMERICAN_YN") = "1" then DTSDestination ("RACE_CD") = 32 DTSDestination ("RACE_MULT_IND") = "N" DTSDestination ("RACE_OTH_IND") = "N" else if DTSSource("RACE_NATIVE_HAWAIIAN_YN") = "1" then DTSDestination ("RACE_CD") = 51 DTSDestination ("RACE_MULT_IND") = "N" DTSDestination ("RACE_OTH_IND") = "N" else if DTSSource("RACE_CAUCASIAN_YN") = "1" then DTSDestination("RACE_CD") = 31 DTSDestination("RACE_MULT_IND") = "N" DTSDestination("RACE_OTH_IND") = "N" end if end if end if end if end if else if CInt (DTSSource("RACE_AMERICAN_INDIAN_YN")) + CInt (DTSSource("RACE_ASIAN_YN")) + CInt (DTSSource("RACE_AFRICAN_AMERICAN_YN")) + CInt (DTSSource("RACE_NATIVE_HAWAIIAN_YN")) + CInt (DTSSource("RACE_CAUCASIAN_YN")) = 10 then DTSDestination("RACE_CD") = 99 DTSDestination("RACE_MULT_IND") = "N" DTSDestination("RACE_OTH_IND") = "N" else if CInt (DTSSource("RACE_AMERICAN_INDIAN_YN")) + CInt (DTSSource("RACE_ASIAN_YN")) + CInt (DTSSource("RACE_AFRICAN_AMERICAN_YN")) + CInt (DTSSource("RACE_NATIVE_HAWAIIAN_YN")) + CInt (DTSSource("RACE_CAUCASIAN_YN")) < 9 then DTSDestination("RACE_CD") = 52 DTSDestination("RACE_MULT_IND") = "Y" DTSDestination("RACE_OTH_IND") = "N" end if end if end if
Suposse that some models are deployed in Report Server for a while, and users have developed some ad-hoc reports on them using Report Builder, (some of the models are SSAS Cubes).
Modifications are required for a Model, what is the procedure to deploy this modifications? What happens with ad-hoc reports of this Model? Rewrite all the reports is a VERY BAD option, I agree that some reports must be rewrote, but only if they reference objects no longer valids in new model.
I suposse that the procedure for SSAS Cube Models will be different for a Relational Database Source because metods of generating models are so different. (I am particularly curious about Cubes, I can't figure out how I can do it)
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
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 ??
I 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?
Quick question about the primary purpose of Full Text Index vs. Clustered Index.
The Full Text Index has the purpose of being accessible outside of the database so users can query the tables and columns it needs while being linked to other databases and tables within the SQL Server instance. Is the Full Text Index similar to the global variable in programming where the scope lies outside of the tables and database itself?
I understand the clustered index is created for each table and most likely accessed within the user schema who have access to the database.
Is this correct?
I am kind of confused on why you would use full text index as opposed to clustered index.
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?
I'm trying to find whether there is a dmv or system view that can help me see the last time an index was rebuilt or created. Assuming I rebuilt an index using tsql commands (not a job with a history), is there a way to find out the last time that index was rebuilt?
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?
Found out a while back that my facts-tabel has an non-clustered index on its facts_id. In a bunch of procedures an update is executed against a facts_id unfortunately on it's facts-table. I was wondering if changing it into a clustered index is worth the effort / would make sense considering a +110 million facts and re-indexing the other indexes as well? Facts are loaded sequentially, so I would suspect them facts are in the ordered already?
