SQL Server Performance Forum – Threads Archive

Why SQL server 2005 performance is slow?

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..

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.

Do you have the correct indexes set up in the new SQL Server database?

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. *~

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

Hi Jimmy, Seems like you just started SQL? Do read SQL SERVER PERFORMANCE forum and you will learn a lot from here [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]. Open query analyzer and paste this code CREATE CLUSTERED INDEX Inv_Conf_Index ON Invoice_Confirm (InvoiceID,Invoice_PrefixID,InvoiceNo) GO CREATE CLUSTERED INDEX Inv_Deb_Index ON Invoice_Debtor (InvoiceID) GO CREATE CLUSTERED INDEX Inv_Bill_Index ON Invoice_BillTo (InvoiceID) GO CREATE CLUSTERED INDEX Inv_Bill_Index ON Invoice_BillTo (Invoice_Prefix_ID) GO And check if you have better performance? For your reading <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>. Check how is your performance and later you can create statistics for columns which you are using in your SELECT statement. Regards, Waqar. ________________________________________________ ~* Opinions are like a$$holes, everyone got one. *~

Hi, yup i just started to use SQL server. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> By excuting ur query in query analyzer, i get the error below. Server: Msg 1906, Level 11, State 1, Line 1 Cannot create an index on ‘Invoice_Confirm’, because this table does not exist in database ‘master’. Server: Msg 1906, Level 11, State 1, Line 1 Cannot create an index on ‘Invoice_Debtor’, because this table does not exist in database ‘master’. Server: Msg 1906, Level 11, State 1, Line 1 Cannot create an index on ‘Invoice_BillTo’, because this table does not exist in database ‘master’. Server: Msg 1906, Level 11, State 1, Line 1 Cannot create an index on ‘Invoice_BillTo’, because this table does not exist in database ‘master’.

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

Hi, ic, thanks…
But the performance seem to be the same. May i know wat for to create CLUSTERED INDEX? Regard,
Jimmy

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. *~

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

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. *~

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

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. *~

Hi, Is SQL server 2005 <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Thanks for ur link, i have tried that but still the same. Maybe i will try to run DBCC CHECKDB at my client site. Regard, Jimmy

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

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. *~

Wagar, Thanks for all ur advises… <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> i really learn a lot here.. N i have found out the cause of the performance issue. that is b’cos of the coding. 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? anyway so happy to solve the issue. <img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ /> Regards, Jimmy

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. *~

]]>