| Author |
Topic |
|
markos
Starting Member
13 Posts |
 Posted - 2004-08-18 : 16:15:00
|
| I have two identical SQL servers (MSSQL 2000 sp2) with two identical databases and two identical queries. Now the problem is that same query on same table on same indexes works fast on one server and slow on second server. I started to investigate and here's what I've found.Query is quite simple Select * from mytable where field_1 like '01234567%' and field_2 between '040101' and '040102'both fields 1 and 2 are varchar, i have nonclustered index on field_1, field_2Now, strange behavior on "slow" server is this:when i search field_1 like '012340567%'and check executionplan i get IndexSeek field_1>='012345'and of course that's the reason while the query is slow, because on "fast" server i have indexseek field_1>='0123450567' and field_1<='0123450569'Another strange thing is that if number doesn't have 0 at the end, i get indexseek field_1>='012345' and field_1<='012346' which is better (because at least there's upper limit, but again, interval is too large)Can somebody tell me how to fix this?! I really don't know what to do!Tnx in advance! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-18 : 16:22:55
|
| Have you updated statistics and reindexed on the slow system? Is the data different on the two systems? Might have a selectivity problem on the slow system.Tara |
 |
|
|
markos
Starting Member
13 Posts |
Posted - 2004-08-18 : 16:32:27
|
| Data is different, I havne't updated statistics and haven't reindexed slow system. I'll try that...but I don't think that's the solution... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-18 : 16:47:34
|
| For different data, the same indexes might not be valid for each system. It depends on the selectivity of the data. At my last job, we had a timesheet system. All departments of the company were on one system until one department wanted to split off on their own. So we created another database just for them. Well the first column of the PK for most of the tables was a department id. So on this second system where only this department was in it, the PK's index was never used as the first column had very low selectivity, very low meaning none. So the index was ignored. So we had to create different indexes for this second system based upon the data.Tara |
|
|
|
markos
Starting Member
13 Posts |
Posted - 2004-08-18 : 17:17:31
|
| It's the same type of data (telephone calls log) but tha data in the tables is not identical (different numbers...), so it's not the issue. As I've said it doesn't explain why does IndexSeek works this way... I've started update statistics fullscan, so we'll see if it will help. |
|
|
|
markos
Starting Member
13 Posts |
Posted - 2004-08-18 : 18:09:03
|
| No, after update statistics it's the same thing. But I found out the condition in which query becomes slow - now this is weird. If the 7th digit in the field_1 criteria is 0, then the query slows down, because IndexSeek has no upper limit (IndexSeek field_1>='012345'). For any other number IndexSeek has the upper limit and the query works fine (on slow server too). So "SELECT * from MYTABLE where field_1 like '1234567%' " returns rowset in 2 sec but "SELECT * from MYTABLE where field_1 like '1234560%' " takes 2 minutes... I'm going slightly mad already... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-18 : 18:14:38
|
| Is there more data for like '1234560%'? It there is a ton of data for that then it could be selectivity of the data.Now I'm sure you are just posting example queries, but I just want to be sure, you aren't using select *, are you?Tara |
|
|
|
markos
Starting Member
13 Posts |
Posted - 2004-08-18 : 18:25:41
|
| No, the funny part is that in this specific case (1234567 and 1234560) the both recordsets are empty:-)Of course, I'm not using select *, I use Select field_1, field_2.... from mytable... but I don't see it should be a problem... |
|
|
|
|
Index seek problem