SQL Server Performance Forum – Threads Archive
Performance Problems Help
Hopefully this is the correct forum to post this in… We have 3 environments dev, test/stagging and production. Our dev group worked on database and it performed great. It was then backed up and restored to our test area for performance metrics and it performed great again. However when we restored it to our product server performance of the stored procedures went down dramatically. Basically a 7 sec stored procedure now took 30 sec. I’ve updated statics, recompile stored procedures and reindexed indexes and still did not help. Hardware configuration across the three systems are slightly different but nothing that I thought would cause the performance to degrade that much. I verified that all system has SQL 2005 SP 2 installed, along with all the necessary OS Service Packs and security updates. At the moment I’m at a lost at to what could be the cause. Has this happened to anyone else? Is there a configuration parameter that may not be set correctly in SQL 2005 on our production server? Any suggestions or help would be greatful. Thanks in advance,Rodney
Have tracing enabled when u ran it on production machine and compare results with test server. Probably you may find some clues. Cheers
Sat
http://www.sqljunkies.com/Article/D1B7C756-4725-4D31-A53D-C0A47976E6BB.scuk http://blog.stevex.net/index.php/why-is-sql-server-so-slow/ What are the database options set if thisis upgraded from previous version? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
techbabu303: By trace do you mean SQL Profiler?
satya: Thanks for the links I will look into them. Yes this database was upgraded from SQL 2000 to 2005. There were some issues that was resolved during the development and test cycles but those were resolved prior to posting the database on to our production server.
Again thanks for all the input.
Yes, GUI tool called SQL profiler…. Take a particular procedure which is taking 30 seconds on prod where as it was running 7 sec. on dev/test….
Run in Query window with stats i/o and stats time on with execution plan on and see what is there any difference in execution plan and/or stats in dev/test and prod… Run sp_configure and check the server configuration too…
specially parallalism and memory…
Check production server memory usage through perfmon using total server memory and target server memory…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Also look at the blog secitonhttp://sqlserver-qa.net/blogs/perftune/default.aspx that is based on the few counters you can take when the performance is stressed. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Thanks for the reply this has been highly educational. The frustrating part is that with SQL 2000 I never experienced this issue where I fine tuned a database in dev did a backup and restored to a production server and everything worked fine performance was never an issue. Now in SQL 2005 I am having to fine tune the restored database on our production server all over again. Running SQL Profiler, Reviewing Execution Plans, and running database engine tuning advisor. The fear our group has is that we also produce a commercial product that comes packaged with a SQL Express 2005 db. That we can optimize to the best of our ability in dev and qa but are not totally confident that the deployed db will perform the say way. Again thanks for all the help and info.
Confirm the edition of SQL 2005 your are running, also whether this database has been upgraded from SQL 2000 and have you lookied around Upgrade advisor for any links on the upgrade. Have you looked at Database Tuning Advisor in this case. Also you can take help performance dashboard reports :http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>