SQL server 7 slower than SQL server 6.5

[poim1205]

To all,

I have been reading almost the entire posting regarding the performance issue of SQL 7.0.

Here is a brief explanation of my problem.

I have a database running on SQL 6.5 that contains lots of tables, lots of views, lots of store procedure and lots of indexes (clustered and non clustered). This database has a fair time response and his meeting the expectations of the clients.

I have a replicate of the exact same database. The replicate is working on a SQL server 7.0. However the time response for the SQL server 7.0 is very long.

Now, from experience with your forum, here are the answers to the usual questions

Q1: Is the hardware of the SQL 6.5 his better than the SQL 7.0?
R1: The SQL 6.5 is running on a dual processor PII-400MHz with 512 MB ram the SQL 7.0 is running on a dual processor PIII-600MHz with 1 GB ram

Q2: Is the structure of the queries are complexes?
R2: Yes, very complex. But admit to the fact that the same complex queries are running very efficiently on an SQL 6.5. Plus, at some point which I don’t know where and How, and this is why I am asking you GURUS to help me, the performance on SQL 7.0 are the same has on SQL 6.5, but with some time, the performance degenerate and it goes bad again.

Q3: Do you have indexes?
R3: Yes, Clustered and non-clustered

Q4: Did you let the server dynamic settings stabilize?
R4: The server as been in function for more than 6 months.

Here is a time frame:

Action: Creation of additional indexes on SQL 7.0
Effect: Improvement of performance for some time (about 5 days) then degeneration of performance again.

Action: Rebuild of indexes on SQL 7.0
Action: Modification of worse performing store procedure
Effect: Improvement of performance for a short time (about 1 day) then decreasing of performance again

Action: Rebuild of indexes on SQL 7.0
Action: Truncate the transaction log
Effect: No gain in performance

My future actions would be to go and modify the internal settings and configuration on the server.

Please help me on this matter, any tip, hint or good word of encouragement would be appreciated

Thanks in advance >:O>

 

[foxdev]

Perhaps start by running SQL Server Profiler and capturing the longest queries, and analyzing the number of reads and CPU time. Robert Bradley

http://www.foxdev.com/

Coming Soon:

http://www.lowcostprints.com

http://www.agrainofhope.org

 

[poim1205]

According to the explication above, the problem is more related to SQL internal settings and/or to an NT server setting ratter than SQL queries executed. The reason I am incline to pose such a judgement is that the queries at some points have work very well and then something is happening and cause the SQL 7 to slow down the processing power.

The analysis I have made for the longest running query implies a View, This view make a lot of joins. And this view is access through a store procedure, which interrogate the view with a parameter.

Here’s the sample:

Create procedure getInfoView1

Select * from view1 where equimentid = @equipmentid



Create view view1

Select t1.item1, t2.item2, t3.item3 ...
From table1 t1, table2 t2, table3 t3, ...
Where t1.condition1 = t2.condition1
And t2. condition2 = t3.condition2
And ...

To answer your question, here are the information requested:

CPU Time : 6781
Number of READ: 143834

Has you can see, and I will admit that this architecture is just sick, but I have no choice but to live with it, until budget is release to scrap all of this and to make it a lot more efficient.

So I will put my question clear and simple. I would like to have your opinion base on your experience. Is my performance problem related only to a poor design or more related to a server NT as well as SQL bad configuration?

I really appreciate the time and effort you pass on this problem.

Thanks again >:O>