Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mr. Chip Man
Starting Member
4 Posts |
 Posted - 2009-03-11 : 15:36:22
|
Hello,I am looking for advice on how to debug a performance issue. First let me explain that I am not a dba or programmer. I "know enough to be dangerous". Any suggestions that I cannot implement would be implemented by my .NET developer.I am in the process of upgrading the server for a a custom .NET app that uses SQL. The app uses .NET Framework 1.1 (yes I know this is old - we do plan to upgrade). The old server is a W2K box that is running SQL 2000 and IIS6. The new server is a 2008 box that is running SQL 2005 Standard and IIS 7.The problem is that some tasks that take 1 second on the old server take 20-30 seconds on the new server. We don't think this is IIS related, but am open to suggestions. One colleague (who is not a SQL expert) feels it may be something to do with the way SQL 2005 is interfacing with my .NET application. He said there is some way to trace/profile the server so that we can get an exhaustive list of executed SQL commands with time stamps. This way I can replicate the slow steps and see exactly which commands are causing the bottle neck. He thinks something may be timing out.Do you agree with his suggestion to "profile" the database? If so, how exactly do you do this? I would like to try to do this on my own because my developer (who is independent) is swamped on another project and has little time to work on this right now. Thanks in advance for your suggestions and help!Chip |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-03-11 : 22:23:20
|
quote:
Originally posted by Mr. Chip Man
Hello,I am looking for advice on how to debug a performance issue. First let me explain that I am not a dba or programmer. I "know enough to be dangerous". Any suggestions that I cannot implement would be implemented by my .NET developer.I am in the process of upgrading the server for a a custom .NET app that uses SQL. The app uses .NET Framework 1.1 (yes I know this is old - we do plan to upgrade). The old server is a W2K box that is running SQL 2000 and IIS6. The new server is a 2008 box that is running SQL 2005 Standard and IIS 7.The problem is that some tasks that take 1 second on the old server take 20-30 seconds on the new server. We don't think this is IIS related, but am open to suggestions. One colleague (who is not a SQL expert) feels it may be something to do with the way SQL 2005 is interfacing with my .NET application. He said there is some way to trace/profile the server so that we can get an exhaustive list of executed SQL commands with time stamps. This way I can replicate the slow steps and see exactly which commands are causing the bottle neck. He thinks something may be timing out.Do you agree with his suggestion to "profile" the database? If so, how exactly do you do this? I would like to try to do this on my own because my developer (who is independent) is swamped on another project and has little time to work on this right now. Thanks in advance for your suggestions and help!Chip
Any difference in execution plans?Were index rebuilt after migration?Check if Locking/Blocking. |
 |
|
|
ahmad.osama
Posting Yak Master
183 Posts |
Posted - 2009-03-12 : 16:56:21
|
quote:
Originally posted by Mr. Chip Man
Hello,I am looking for advice on how to debug a performance issue. First let me explain that I am not a dba or programmer. I "know enough to be dangerous". Any suggestions that I cannot implement would be implemented by my .NET developer.I am in the process of upgrading the server for a a custom .NET app that uses SQL. The app uses .NET Framework 1.1 (yes I know this is old - we do plan to upgrade). The old server is a W2K box that is running SQL 2000 and IIS6. The new server is a 2008 box that is running SQL 2005 Standard and IIS 7.The problem is that some tasks that take 1 second on the old server take 20-30 seconds on the new server. We don't think this is IIS related, but am open to suggestions. One colleague (who is not a SQL expert) feels it may be something to do with the way SQL 2005 is interfacing with my .NET application. He said there is some way to trace/profile the server so that we can get an exhaustive list of executed SQL commands with time stamps. This way I can replicate the slow steps and see exactly which commands are causing the bottle neck. He thinks something may be timing out.Do you agree with his suggestion to "profile" the database? If so, how exactly do you do this? I would like to try to do this on my own because my developer (who is independent) is swamped on another project and has little time to work on this right now. Thanks in advance for your suggestions and help!Chip
read [url]ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3ad5f33d-559e-41a4-bde6-bb98792f7f1a.htm[/url] in books onlineRegards,Ahmad Osama |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
|
|
Mr. Chip Man
Starting Member
4 Posts |
Posted - 2009-03-16 : 19:40:52
|
quote:
Any difference in execution plans?Were index rebuilt after migration?Check if Locking/Blocking.
Thanks sodeep. I am sorry for not replying sooner. I will ask the developer if this changed and will get back to you. |
|
|
|
Mr. Chip Man
Starting Member
4 Posts |
Posted - 2009-03-16 : 19:46:08
|
quote:
Originally posted by darkdusky
Sql Server 2008 (including the free Express Edition) has Activity Monitor which you can point at a SQL Server 2005 instance. It is useful for finding long running queries etc.You could install on a PC / test server and point at the new 2005 instance.http://sqlcat.com/top10lists/archive/2009/01/30/top-10-sql-server-2008-features-for-the-database-administrator-dba.aspxAlso have you installed the latest service pack to 2005 version ? - some early bugs caused delays.
Hi darkdusky,Sorry I did not reply sooner. I was pulled off this project for a bit. I will look into your idea about using the ActivityMonitor in 2008. It is getting a bit over my head. Could I install it on a Backup Domain Controller that is running 2003 Enterprise? The BDC is connected to the troubled web server on the same subnet.I currently have SP3 installed. I checked and there is a cumulative update for SP3 that includes many of the latest fixes for SP2. Some of them seem performance related. Should I install the cumulative update? How safe is it? Also, Microsoft has two files to download - one is SNA and one is not. Which one would I use?Thanks again! |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
|
|
|
|
|
|
|
Need Help Solving Performance Issue