Performance Hit after upgrading to SQL 2005

I am hit with bad performance after upgrading my sql 2000 to sql 2005 version. All the infrastructure remains expect the performance :(

Please provide any pointers /guidelines so that I will check the same.

Thanks in Advance

Varun

I am seeing a huge performance problem right now.

I have to tagged to the TempDB right now. It seems to be leaving leftover objects and making the TempDB a mess. Check yours for a bunch of "#" + 8 digit Hex

If I stop and restart my SQL Services, which redoes the TempDB....I am good to go for a day or 2...then the same old problem. I have called MS about this but I am waiting for response from them

select * from tempdb.dbo.sysobjects where name like '#%' order by crdate desc

|||

What is the problem with performance, is it a particular query?

Have you rebuilt your indexes? This is a must do excercise after upgrading to SQL 2005.

|||

I had the same problem -- we got a new server to run sql server 2005. It was a major upgrade over the old server. When we started testing our code on the new machine, we found that a significant amount of our code ran a lot slower on the new machine with sql 2005.

Eventually we were able to change our code, mostly through trial and error, to get the performance we expected. In almost every case, the performance problems were caused by:

1. Sub-queries - apparently the sql 2005 optimizer isn't as good as the sql 2000 optimizer at figuring out when the sub-query needs to be re-evaluated -- so large recordsets that join to a sub-query can be really slow since the sub-query sometimes gets re-evaluated for each record it's being joined to even when the sub-query generates the exact same result for each parent record.

2. Table variables - when we substituted #temp tables for table variables, performance increased dramatically -- in some cases putting a primary key on the table variable fixed the problem.

Unfortunately, we had to spend a couple of weeks to change all of our code, but eventually we were able to fix the performance problems.

Hope that helps.

Performance degradation writing to a SAN / building a cube

Is anyone aware of performance degradation writing to a SAN over fibre optic network? We r using SQL Server 2000 (Enterprise version) with Compaq Proliant DL580 G2, writing to Compaq SAN. Is any network tuning needed? A gigabit ethernet card pushes the data over to the SAN.

Also, we r taking about 37 hours building a cube for a database barely 15GB. Database is shipped by Microsoft and is used by their product Microsoft Proect Server. So, database level tuning is out of question.

Any help would greatly be appreciated.

Thanx in advance,

BhushanYou should try the basic performance tuning practices to find out where your bottle neck is. Here are a few we use to help us troubleshoot our san...

*check CPU utilization on the sql node -- this could be caused by a lot of things
*Check disk idle time -- if your disk is below 20% idle, then you are spindle bound.
*check disk queue length. -- if the disk queue is higher than twice the number of disks in your raid array, then you have a problem. High disk queue could also be a problem with the NIC. it is possible it cant send data as fast as the cable and disks can handle it.
*check memory usage. -- if you are trying to cache everything and the pc starts paging, this will kill performance
*check your query plans (not sure if that is possible, but if it is running poorly it can make a huge difference.)
*check your network traffic between the pc and the san. are you seeing any collisions?

basically it comes down to finding the bottleneck and then fixing it.|||Originally posted by Pigeon
You should try the basic performance tuning practices to find out where your bottle neck is. Here are a few we use to help us troubleshoot our san...

*check CPU utilization on the sql node -- this could be caused by a lot of things
*Check disk idle time -- if your disk is below 20% idle, then you are spindle bound.
*check disk queue length. -- if the disk queue is higher than twice the number of disks in your raid array, then you have a problem. High disk queue could also be a problem with the NIC. it is possible it cant send data as fast as the cable and disks can handle it.
*check memory usage. -- if you are trying to cache everything and the pc starts paging, this will kill performance
*check your query plans (not sure if that is possible, but if it is running poorly it can make a huge difference.)
*check your network traffic between the pc and the san. are you seeing any collisions?

basically it comes down to finding the bottleneck and then fixing it.

Thanx a lot, Pigeon. I did check the Queue length originally; but was told by Compaq that the Queue length reported by Windows 2000 (for Compaq hardware) is not reliable for various reasons.

Anway! I personally think NIC card is slow and thats why I am seeing NETWORKIOWAIT within SQL Server. Just wanted to confirm, if I am not missing anything...

Thanx again.

- Bhushan|||The NIC can be a big bottleneck. WHat protocol does it use to send the data across the network? UDP, TCP, is it using a SCSI miniport driver (Encapsulating scsi calls into a udp or tcp packet) Many times, if this driver or protocol is not working, it will cause an increase in processor utilization, or the disk queue will be high. No matter what compaq tells you, if your queue is high, something is stopping the traffic from getting to the disk. Good Luck with it. I know these types of problems can sometimes be a real pain to find.

Perform upgrade method

hi

I have two versions of the same dataflow component , i need to use the new version component in the packages created using the old version . How can i do this using the perform upgrade method .

Thanks

Mani

Explanation about PerformUpgrade and a sample code can be found in book online or at:

http://msdn2.microsoft.com/ko-kr/library/microsoft.sqlserver.dts.pipeline.pipelinecomponent.performupgrade.aspx

Hope this helps,
Ovidiu Burlacu

|||

hi

thanks i got the solution the perform upgrade method only works if the current version property, that is stored in the package is less than the currentversion property of the component on the local computer.

Regards

Mani

|||

hi

If i removed the old DLL from the GAC i am getting errors while opening the package saved with the old version DLL

these are the errors ...

Error 1 Validation error. Data Flow Task: DTS.Pipeline: The component metadata for "component "Oracle Destination" (67)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. Package1.dtsx 0 0
Error 2 Error loading Package1.dtsx: The "component "Oracle Destination" (67)" failed to cache the component metadata object and returned error code 0x80131600. d:\testproject\integration services project5\Package1.dtsx 1 1
Error 3 Error loading Package1.dtsx: Component "component "Oracle Destination" (67)" could not be created and returned error code 0xC0047067. Make sure that the component is registered correctly. d:\testproject\integration services project5\Package1.dtsx 1 1
Error 4 Error loading Package1.dtsx: The component metadata for "component "Oracle Destination" (67)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. d:\testproject\integration services project5\Package1.dtsx 1 1

how can i correct these errors

thanks

Mani

|||

You should not remo0ve the old DLL from the GAC becuase the component can no longer be instantiated. The new DLL should be able to live in the GAC with the old DLL because they have different versions (file or assembly or even the PublicTokenKey)

Thanks,
Ovidiu

|||

Ovidiu, I don't see how you can do side by side with SSIS components in the GAC. File versions do not differentiate between assemblies, so you cannot use that to installside by side. If you change the assembly version or the key (and therefore the public key token) you can side by side them, but then your are stuffed since SSIS stores the full strong name in the package. Any change in the string name means you get the nice error about not being able to load the assembly. Hacking the XML would seem to be the only solution, shame it is not a supported one :)

|||Indeed you are right. I think is time for me to go in vacation |||

In fact you can automatically upgrade a dataflow component's metadata when you upgrade the component's assembly version.

Here's how:

1. Remove the old assembly from GAC.

2. Make sure your new component's CurrentVersion argument of the DtsPipelineComponent attribute is greater than the one saved in the old metadata. That'll ensure that PerformUpgrade of the new component will be called when SSIS opens the package.

3. In the new component override PerformUpgrade and in it make sure to include the following:

ComponentMetaData.CustomPropertyCollection["UserComponentTypeName"].Value = this.GetType().AssemblyQualifiedName;

This changes the metadata's type reference to refer to the new type and version. This will ensure that things like doubleclick on the component will work if you're using UITypeName argument of DtsPipelineComponent attribute for example.
Of course here you should also upgrade any metadata properties from the old version to the new one, including any UITypeEditor properties of custom properties which have their custom editors.

4. Install the new assembly to the GAC.

5. Create a policy assembly redirecting the old version to the new one and install it in GAC. For example of how to do that see here: http://samples.gotdotnet.com/quickstart/howto/doc/pubpolicy.aspx

Now when you open packages with the old version the pattern above will automatically upgrade to the new version.

Milen

Perform upgrade method

hi

I have two versions of the same dataflow component , i need to use the new version component in the packages created using the old version . How can i do this using the perform upgrade method .

Thanks

Mani

Explanation about PerformUpgrade and a sample code can be found in book online or at:

http://msdn2.microsoft.com/ko-kr/library/microsoft.sqlserver.dts.pipeline.pipelinecomponent.performupgrade.aspx

Hope this helps,
Ovidiu Burlacu

|||

hi

thanks i got the solution the perform upgrade method only works if the current version property, that is stored in the package is less than the currentversion property of the component on the local computer.

Regards

Mani

|||

hi

If i removed the old DLL from the GAC i am getting errors while opening the package saved with the old version DLL

these are the errors ...

Error 1 Validation error. Data Flow Task: DTS.Pipeline: The component metadata for "component "Oracle Destination" (67)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. Package1.dtsx 0 0
Error 2 Error loading Package1.dtsx: The "component "Oracle Destination" (67)" failed to cache the component metadata object and returned error code 0x80131600. d:\testproject\integration services project5\Package1.dtsx 1 1
Error 3 Error loading Package1.dtsx: Component "component "Oracle Destination" (67)" could not be created and returned error code 0xC0047067. Make sure that the component is registered correctly. d:\testproject\integration services project5\Package1.dtsx 1 1
Error 4 Error loading Package1.dtsx: The component metadata for "component "Oracle Destination" (67)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. d:\testproject\integration services project5\Package1.dtsx 1 1

how can i correct these errors

thanks

Mani

|||

You should not remo0ve the old DLL from the GAC becuase the component can no longer be instantiated. The new DLL should be able to live in the GAC with the old DLL because they have different versions (file or assembly or even the PublicTokenKey)

Thanks,
Ovidiu

|||

Ovidiu, I don't see how you can do side by side with SSIS components in the GAC. File versions do not differentiate between assemblies, so you cannot use that to installside by side. If you change the assembly version or the key (and therefore the public key token) you can side by side them, but then your are stuffed since SSIS stores the full strong name in the package. Any change in the string name means you get the nice error about not being able to load the assembly. Hacking the XML would seem to be the only solution, shame it is not a supported one :)

|||Indeed you are right. I think is time for me to go in vacation |||

In fact you can automatically upgrade a dataflow component's metadata when you upgrade the component's assembly version.

Here's how:

1. Remove the old assembly from GAC.

2. Make sure your new component's CurrentVersion argument of the DtsPipelineComponent attribute is greater than the one saved in the old metadata. That'll ensure that PerformUpgrade of the new component will be called when SSIS opens the package.

3. In the new component override PerformUpgrade and in it make sure to include the following:

ComponentMetaData.CustomPropertyCollection["UserComponentTypeName"].Value = this.GetType().AssemblyQualifiedName;

This changes the metadata's type reference to refer to the new type and version. This will ensure that things like doubleclick on the component will work if you're using UITypeName argument of DtsPipelineComponent attribute for example.
Of course here you should also upgrade any metadata properties from the old version to the new one, including any UITypeEditor properties of custom properties which have their custom editors.

4. Install the new assembly to the GAC.

5. Create a policy assembly redirecting the old version to the new one and install it in GAC. For example of how to do that see here: http://samples.gotdotnet.com/quickstart/howto/doc/pubpolicy.aspx

Now when you open packages with the old version the pattern above will automatically upgrade to the new version.

Milen