July 2, 2009 at 5:18 am
Hello.
I've encountered a problem on our SQL-SERVER 2k standard edition (SP4).
I have a database in which the SQL Server won't use indexes in the execution plan and the index tuning wizard won't recommend any indexes.
Even a simple query as follows is causing a slow performance:
SELECT *
FROM myTable
WHERE Rec_Date='2009-1-1'
The same query on a different database uses index and the index tuning wizard does recommend the correct index, even after I dropped all the indexes, including the primary keys on both databases and used about the same size tables.
So I created a new database and copied 2 of the tables to the new db (with creation of new tables and INSERT), using only primary keys . There was a slight "improvement" of the ITW with the new database, but still the ITW didn't recommend what seemed to me the right indexes for joined update queries or suggested a 10 columns index for a simple join select.
The tables look something like that:
Table A:
Row_ID int identity (clustered primary key)
Rec_Date datetime
Sale_Number int
Item_Code nvarchar(15)
Item_Price ....
and so on about 10 columns with 85 mil rows.
Table B:
Sale_Date datetime
Item_Code nvarchar(15)
Item_Cost float
about 20 mil rows, the primary key (clustered) consists from Sale_Date and Item_Code
I've checked almost anything I could think of, including same data types with the joined queries, the use of variable, updated statistics
any suggestions?
Thank you,
Doron
July 2, 2009 at 5:54 am
Most likely it's because the index isn't covering and there's too many rows returned for an index seek + bookmark lookups to be optimal.
See - http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/
If you want more specific suggestions, please post table definitions, index definitions and execution plan, as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2009 at 6:47 am
Thanks Gail.
I've read the posting rules, but as I mentioned in the original post, the behavior of the SQL Server changes from database to database on the same tables (definition and content).
For example, Lets take this simple table:
CREATE TABLE [Customer] (
[club] [varchar] (10) NULL ,
[CustomerID] [varchar] (20) NULL ,
[createdate] [varchar] (50) NULL ,
[initstore] [varchar] (20) NULL ,
[gender] [varchar] (10) NULL ,
[firstname] [varchar] (50) NULL ,
[lastname] [varchar] (50) NULL ,
[lastupdate] [varchar] (50) NULL ,
[mobile] [varchar] (50) NULL ,
[phone] [varchar] (50) NULL ,
[varchar] (100) NULL ,
[address] [varchar] (150) NULL ,
[towncity] [varchar] (100) NULL ,
[street] [varchar] (100) NULL ,
[housenumber] [varchar] (10) NULL
)
It's about 300K rows, no primary key and no indexes.
Lets run the following query in the ITW :
SELECT *
FROM Customers
WHERE CustomerID='012776'
Now, in the problematic database I'm getting no index recommendation at all from the ITW.
The same query on the same table on a different database (smaller in total size) will result with the recommendation to create an index on the CustomerID column.
So I presume that table size doesn't matter here and probably something else is interfering.
Thanks,
Doron
July 2, 2009 at 7:08 am
Honestly, I don't trust IDW and I don't use ITW. This is one of the reasons. Some other people have mentioned much the same behaviour with the 2005 DTA. I much prefer to tune indexes by hand.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply