Performance issues...SQL Server 2005

[Greg.NET]

Queries are taking up to 5 times longer to execute in sql server 2005 compared to sql 2000.

I'm sure these queries are far from optimal and the database design is flawed, for example there are no primary keys(id fields are not unique either, multiple entries with the same id, why it was done this way I have no idea). On viewing the execution plan, the most costly steps are "table scans" and from articles I read this indicates a problem with the index.

But the performance on the sql 2000 server is perfectly acceptable. So before I go redesigning this database I was wondering if anyone had any similar experiences when moving to sql 2005.

 

[giadich]

I didn't notice anything like you mentioned. But I'll share with you an incidence I ran into.

One of my developer had a query like this:

Select * from tablename

when he only needed the table schema.

The program worked very well for a couple of months but as the data accumulated, it got slower and slower. It took me a while to figure it out after the users complain about the slowness.

So how does this apply to your situation? Well, if your database wasn't designed correctly in the first place, chances are the queries are poorly written also. You might want to check the queries first.

 

[cjard]

So before I go redesigning this database I was wondering if anyone had any similar experiences when moving to sql 2005.

Nah, just redesign it; it's broken, it needs fixing. Get on with it before it grows too huge to do

 

[Greg.NET]

Thanks Im planning on it. But I can't help wonder why its so much slower on the 2005 server. When a table is imported do the index statistics carry over as well? could statistical info be to blame here?

 

[cjard]

On viewing the execution plan, the most costly steps are "table scans" and from articles I read this indicates a problem with the index..

Table scans mean the entire table is being read to find the value. There are valid times when this may occur, even when the column being searched is indexed. Either the index cannot be used because it is not suitable, or it lacks sufficient selectivity to make it worth using. Think about this:
Suppose you have an index in a book, and its just lists all the words used in the book, and the page they occur on. If you looked up in the index, for the word "the", you'd have a list of pages so long, that you wouldnt bother referring back and forth to the index, you'd just read the whole book and save on the page flipping back and forth to the index.

So, dont think that table scans are bad, and set about indexing everything - sometimes they are necessary and an index wont help.


If youre going to set about performance tuning SQL queries, you really really really need to know what youre doing. Read a lot, buy a book on it, understand how the optimizer works in certain conditions.

Stats gathering over time might help the optimizer choose a different path.. i dont know if the stats migrate with the data, sorry.. Bear in mind also, that the logic of the optimizer may have changed. You can examine the query plans of both, and see if you can coerce the optimizer to execute the same plan, but really it's that time where it needs looking at..

Lastly, I'd like to note that databases are huge, optimizers are complex, and there probably isnt a one line answer a guy on an internet forum can give you, never having seen your database, queries or production environment