SQL Server Performance

upgrading sql server 7.0 to sql server 2000

Discussion in 'T-SQL Performance Tuning for Developers' started by oziiii, Dec 17, 2003.

  1. oziiii New Member

    hi,
    i have an visual basic programme using Sql Server 7.0.
    i can do some operations 4 times in a second.
    i should increase this number to 6 or more.
    is it possible to increase the speed this much by upgrading sql server 2000 ?
    or should ý try to optimize sql server 7.0 settings?

    thanks

  2. satya Moderator

    It depends on the type of query involved.
    Certainly you will get optimum performance on SQL 2K as compared to ver.7.
    BTW, what is the query involved.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. oziiii New Member

    in fact Query is not so complicated.
    since the problem is in miliseconds i don t know which one is slow.
    i am using winsock.and transfer data 4 times in a second but it should be minimum 6.
    i am sending the data according to this query:

    select top 1 * from A where
    (a=1 and b=2 and c in (" & sstring1 & ") and (d=1 or (d=0 and e in (" & sstring2 & ")
    and x in (select x from B where B.a=1 and B.b=0))) and f<>-1 and g in (" & sstring3 & ")) order by a,b

    and insert the reply message with insert into statement.

    some of my friends told me that i can increase the speed by using SQL server 2000.

    thanks..
  4. satya Moderator

    Check query execution plan from query analyzer and how about indexes on the queried table.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  5. Luis Martin Moderator

    True about SQL2k, but I suggest to work in performance in current version, like Satya said, before to upgrade. All work in that direction will work in SQL2k, so is not waisting time.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  6. oziiii New Member

    i searched the indexes from Query Analyser by 'Perform Ýndex Analysis'
    and add a new index which query analser suggested.but it didn't help.
    it is still slow.
    can you suggest anything else with sql server 7.0?
  7. satya Moderator

    How big is the database and userbase?
    How about dbcc checks, maint.plans for this database?
    Any other applications are sharing SQL server resources?
    What is the level of service pack on SQL & OS?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  8. Luis Martin Moderator

    Yes, run update statistics (sp_updatest) with fullscan. You can run without fullscan, but in non production time I suggest to run fullscan.
    Also see how defragmented your index are.


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  9. oziiii New Member

    thanks for your answer,
    what do you mean by 'fullscan'.
    can you explain more?

    thanks
  10. Luis Martin Moderator

    Sorry my mistake.<br /><br />With sp<br /><br />Syntax<br />sp_updatestats [[@resample =] 'resample']<br /><br />Return Code Values<br />0 (success) or 1 (failure)<br /><br />Arguments<br />[@resample=] 'resample'<br /><br />Specifies that sp_updatestats will use the RESAMPLE option of the UPDATE STATISTICS command. New statistics will inherit the sampling ratio from the old statistics. If 'resample' is not specified, sp_updatestats updates statistics using the default sampling. This parameter is varchar(<img src='/community/emoticons/emotion-11.gif' alt='8)' /> with a default value of 'NO'.<br /><br />if you want table by table:<br /><br />UPDATE STATISTICS table | view <br /> [ <br /> index <br /> | ( statistics_name [ ,...n ] ) <br /> ] <br /> [ WITH <br /> [ <br /> [ FULLSCAN ] <br /> | SAMPLE number { PERCENT | ROWS } ] <br /> | RESAMPLE <br /> ] <br /> [ [ , ] [ ALL | COLUMNS | INDEX ] <br /> [ [ , ] NORECOMPUTE ] <br /> ] <br /><br />FULLSCAN<br /><br />Specifies that all rows in table or view should be read to gather the statistics. FULLSCAN provides the same behavior as SAMPLE 100 PERCENT. FULLSCAN cannot be used with the SAMPLE option.<br /><br />HTH<br /><br />Luis Martin<br /><br />...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true. <br />Bertrand Russell

Share This Page