SQL Server Performance Forum – Threads Archive

Bizzare Problem Of All Time

I consider this as the most Bizzare issue of All time <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Config: SQL Server 2005 Enterprise Edition SP2 on a Windows 2003 Advanced Server box which has 4CPU Xeon, 4GB RAM and has an EMC SAN attached for DB File Storage. Migration Process: I have an empty db server MYPROD. Tables and Indexes are created. Then a Custom Written utility brings one record at a time over to MYPROD from a database on the same server called OLDPROD. This custom written utility simply queries the OLDPROD and executes a Stored Procedure on MYPROD with the values of the source records as Parameters to the SP. The SP then Inserts and updates records in the MYPROD so accoumadate the change in database schema design we did. This also accoumplishes some level of Normalization. Like for example OLDPROD has the Student table, the SP normalizes the Student table and puts the values in Student, Address, StudentClass Tables. Problem: Once the utility brought over all the 1.2 million records to MYPROD from OLDPROD I executed a normal query that the Front End VB application normally calls. This query surprisingly took 3.5 mins and eventually timed out. I had executed the same process of migration step by step, unchanged , with exactly the same hardware in beta environment MYBETA and the query executed in 2 seconds. How come the same query takes so much time now ? We have strictly followed the same procedure and same database creation script. Bizzare: I feared that the Migration Process in MYPROD might have gone wrong, I wanted to confirm that so I took a backup of the MYBETA database from the BETA server and restored it on the PROD server as MYBETAONPROD. Now This database also started to have the same problem. So It was confirmed that it was not a migration issue. Bizzare 2: I then suspected that the Server Hardware was a problem, so I copied (backup>restore) the MYBETAONPROD back again on the BETA Server. and to add to the surprise now the BETA server started executing the query slow. When copying MYBETA to PROD server I had also copied MYBETA to a DEV server (almost same hardware). The MYBETA on DEV box ran ok with 2 seconds on the query. I am not sure what to blame here. Need Help Urgently. Had a huge fiasco as we could not launch our product. Any help will be greatly appreciated and thanked. Please feel free to ask me questions. Thanks a lot in advance. Thanks for even reading it and giving it a try if you do not have an answer.

Try running the same query with the MAXDOP 1 option … SELECT ………
FROM ………
WHERE ……..
OPTION (MAXDOP 1)

SEe the bloghttp://sqlserver-qa.net/blogs/tools…-performance-after-an-upgrade-is-so-slow.aspx and things to take carehttp://sqlserver-qa.net/blogs/perft…ow-after-upgrading-from-sql-2000-to-2005.aspx after & before upgrade. 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. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

]]>