SQL Server Performance

Why SQL server 2005 performance is slow?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by JimmyChua, Jul 21, 2006.

  1. JimmyChua New Member

    I just migrate my Access DB to SQL Server 2005 using the MS Access upsizing wizard.
    After migration, the performance become very slow and this really give me a lot of trouble. May i know wat shd i do in order to speed up the response time of the page. By the way, i'm using ASP to code my appliaction. Thanks..
  2. Luis Martin Moderator

    Did you update statistics after DB migration?

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. merrillaldrich New Member

    Do you have the correct indexes set up in the new SQL Server database?
  4. waqar Member

    Jimmy,

    Ne hao.
    What is your table size and what query you are trying to execute?
    As Luis mention, have you created any index/statistic on access DB which you haven't rebuild at SQL server?

    A+
    Waqar.

    ________________________________________________
    ~* Opinions are like a$$holes, everyone got one. *~
  5. JimmyChua New Member

    i din create any index/statistic and what is index/statistics? how to update it after migration?

    i din setup any index in sql server db. how to setup index? setup index in sql server... ll it improve the performance?

    the table size... how to check the table size? the table consists of few ten thousand of records. the query is as below.. i think is just a simple query.

    Select C.InvoiceID,C.InvoiceNo,C.Invoice_ConfirmDateUpdated,D.Invoice_DebtorName,I.Invoice_SalesPerson1,I.Invoice_Total,C.Invoice_ConfirmStatus,D.Invoice_DebtorID, B.Invoice_BillToName ,C.Invoice_PrintStatus, P.Invoice_Prefix_Name
    from Invoice_Confirm C, Invoice_Debtor D, Invoice I,Invoice_BillTo B, Invoice_Prefix P
    where C.InvoiceID = I.InvoiceID
    and C.InvoiceID = D.InvoiceID
    and B.InvoiceID = C.InvoiceID
    AND C.Invoice_PrefixID = P.Invoice_Prefix_ID
    Order by C.InvoiceNo DESC
  6. waqar Member

    Hi Jimmy,<br /><br />Seems like you just started SQL?<br />Do read SQL SERVER PERFORMANCE forum and you will learn a lot from here [<img src='/community/emoticons/emotion-2.gif' alt=':D' />].<br /><br />Open query analyzer and paste this code<br /><br /><font color="blue">CREATE CLUSTERED INDEX Inv_Conf_Index ON Invoice_Confirm (InvoiceID,Invoice_PrefixID,InvoiceNo)<br />GO<br />CREATE CLUSTERED INDEX Inv_Deb_Index ON Invoice_Debtor (InvoiceID)<br />GO<br />CREATE CLUSTERED INDEX Inv_Bill_Index ON Invoice_BillTo (InvoiceID)<br />GO<br />CREATE CLUSTERED INDEX Inv_Bill_Index ON Invoice_BillTo (Invoice_Prefix_ID)<br />GO<br /></font id="blue"><br /><br />And check if you have better performance?<br /><br />For your reading<br /><a href='"http://www.sql-server-performance.com/statistics.asp"' target='_blank' title='"http://www.sql-server-performance.com/statistics.asp"'<a target="_blank" href=http://www.sql-server-performance.com/statistics.asp>http://www.sql-server-performance.com/statistics.asp</a></a>.<br /><br />Check how is your performance and later you can create statistics for columns which you are using in your SELECT statement.<br /><br /><br />Regards,<br />Waqar.<br /><br />________________________________________________<br />~* Opinions are like a$$holes, everyone got one. *~
  7. JimmyChua New Member

    Hi, <br /><br />yup i just started to use SQL server. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />By excuting ur query in query analyzer, i get the error below.<br /><br />Server: Msg 1906, Level 11, State 1, Line 1<br />Cannot create an index on 'Invoice_Confirm', because this table does not exist in database 'master'.<br />Server: Msg 1906, Level 11, State 1, Line 1<br />Cannot create an index on 'Invoice_Debtor', because this table does not exist in database 'master'.<br />Server: Msg 1906, Level 11, State 1, Line 1<br />Cannot create an index on 'Invoice_BillTo', because this table does not exist in database 'master'.<br />Server: Msg 1906, Level 11, State 1, Line 1<br />Cannot create an index on 'Invoice_BillTo', because this table does not exist in database 'master'.
  8. waqar Member

    Hi,<br /><br />You have to choose database where you wanted to execute these script, by default when you open query analyzer it choose master database.<br /><br />Or you can type<br /><br />USE databasename before executing scripts i given earlier.<br />where databasename is your database name where you dump your data.<br /><br />FYR here is image<br /><br /><img src='http://img223.imageshack.us/img223/9896/untitledcd4.jpg' border='0' /><br /><br />Hope this will help.<br /><br />Regards,<br />Waqar.<br /><br /><br />________________________________________________<br />~* Opinions are like a$$holes, everyone got one. *~
  9. JimmyChua New Member

    Hi,

    ic, thanks...
    But the performance seem to be the same.

    May i know wat for to create CLUSTERED INDEX?

    Regard,
    Jimmy
  10. waqar Member

    Hi Jimmy,

    Please read CREATE INDEX under SQL Help for more information.
    I will suggest you have a look at your query plan. and if you can upload picture then i will be able to assist you better.

    Waqar.

    ________________________________________________
    ~* Opinions are like a$$holes, everyone got one. *~
  11. JimmyChua New Member

    Hi Waqar,

    Thank you for ur advise.
    The performance issue, my client thinks to increase the RAM of the server.
    See whether this would help or not.

    But i face another prob now.... When attaching DB from another server i get an error..

    Error 602: Could not find row in sysindexes for database ID 9, object ID 1,
    index ID 1. Run DBCC CHECKTABLE on sysindexes.

    when i Run DBCC CHECKTABLE on sysindexes (DBCC CHECKTABLE sysindexes)in query analyzer. I get another error

    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'sysindexes'.

    Any advise for me....

    Regards,
    Jimmy
  12. waqar Member

    Hi Jimmy,

    Can you please explain what do you mean by connecting Database from other server?
    Do you mean database files are available at different server from SQL server itself? Or Do you mean you are trying to connect database server from a Client ?

    Waqar.

    ________________________________________________
    ~* Opinions are like a$$holes, everyone got one. *~
  13. JimmyChua New Member

    Hi Wagar,

    Database files available at different server.
    I detached a database files from my client server then i tried to attach at my own server.
    But i couldn't attach it, i get the error msg below.

    Error 602: Could not find row in sysindexes for database ID 9, object ID 1,
    index ID 1. Run DBCC CHECKTABLE on sysindexes.

    when i Run DBCC CHECKTABLE on sysindexes (DBCC CHECKTABLE sysindexes)in query analyzer. I get another error

    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'sysindexes'.

    Regard,
    Jimmy
  14. waqar Member

    Hi Jimmy,

    Just a stupid question, do your server is SQL 2005 or SQL 2000?

    Also check this link http://www.mcse.ms/archive81-2005-6-1696368.html

    WAQAR


    ________________________________________________
    ~* Opinions are like a$$holes, everyone got one. *~
  15. JimmyChua New Member

    Hi,<br /><br />Is SQL server 2005 <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Thanks for ur link, i have tried that but still the same. <br />Maybe i will try to run DBCC CHECKDB at my client site.<br /><br />Regard,<br />Jimmy
  16. JimmyChua New Member

    Hi,

    My client still complaining about the perfomance after adding 2Gb RAM.
    Any advises?

    Regard,
    Jimmy
  17. waqar Member

    Jimmy,

    Can you perform a Execution plan and see where it is taking more time to execute?

    Follow the picture how you can get Execution plan.

    http://img451.imageshack.us/img451/9702/solbw4.gif

    Instructures:
    1- Write query.
    2- Press Execution plan.
    3- Analyze which portion taking longer time.
    4- Take assistance from Tunning Wizard to optimize query.

    Good luck.

    ________________________________________________
    ~* Opinions are like a$$holes, everyone got one. *~
  18. JimmyChua New Member

    Wagar,<br /><br />Thanks for all ur advises... <img src='/community/emoticons/emotion-1.gif' alt=':)' /> i really learn a lot here..<br /><br />N i have found out the cause of the performance issue. that is b'cos of the coding. <br />i was using a for loop to retrieve the records from DB and within the for loop i open another connection to DB to retrieve another set of records. This causing the slow performance so i change the coding and make the two query become one query. now the performance is much more better le.. ") ... but i hv no idea y the previous method ll causing the performance issue? <br /><br />anyway so happy to solve the issue. <img src='/community/emoticons/emotion-4.gif' alt=':p' /><br /><br />Regards,<br />Jimmy
  19. waqar Member

    If you open and close DB connection in a loop this will cause your system to show such performance degrading issues.

    Anyway Good luck, and be cool [8D].

    Waqar.

    ________________________________________________
    ~* Opinions are like a$$holes, everyone got one. *~

Share This Page