SQL Server SP3a issue
-
Hi,
We installed SQL Server SP3a on SP2. Now the existing Stored Procs and User Defined Functions using temporary table variables are performing miserably. For e.g, a UDF (using Temporary Table Variables) returning 1600 rows used to take less than a second and now it takes 25 seconds.
Any help would be greatly appreciated!!!
-Tushar
-
- did you run sp_statistics on all the db after the upgrade ?
- execute your maintenanceplan or rebuild your indexes
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Yes....I have updated the statistics and rebuild the indexes....did not help though.....
-
Did you check whether your server and clients are running the same MDAC (Microsoft Data Access Components) version now? We've had that problem before, when a SP updgraded MDAC & we didn't realize the implications until later. Microsoft has an "MDAC Component Checker" available for free download; don't remember the location but searching Knowledge Base should point to it.
Also - Have you tried executing any of the SPs in a SQL window to see whether the plan is still reasonable?
- Elaine
esc
-
There is a performance bug related to using the ODBC driver for SQL Server that showed up with SP3. Does this apply to your environment?
FIX: Performance Degradation and Memory Leak in the SQL Server ODBC Driver
K. Brian Kelley
@kbriankelley -
Here is the location where you can find the utility by the way:
Yes, we have the same MDAC version on client and Server. But the problem is before even I go to client. I am trying to run a Stored Proc from Query Analyzer window. This SP uses table variables and used to take around a second before we installed SP3a. Now it takes around 25 secs. I also rewrote this SP to use Temporary Tables instead of Table Variables and it's performing just fine. It takes around one second. And we are using Table Variables all over and in many of our apps. Is there any known unusual behavior with SP3a and Table Variables?
Do appreciate the feedback!
-Tushar
-
I did notice that using table variables was slower than using temp tables when there was relatively large amount of data being insertd into the table variable. I found that if the table variable required more than a couple hundred rows performance was better with a temp table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Yes. Table Vaiables - it has been a controversial topic for some time. But interestingly enough, I did not experience that before I installed SP3a. Any explaination?
-Thanks
-
Interesting... changes to tempdb might result in concurrency issues, but apparently this appeared before SP3. So I'm not sure it's even applicable to your situation. The article cites mostly the .702 version which is prior to SP3 (.760). However, there is a note to use the rollup version .765.
FIX: Concurrency Enhancements for the Tempdb Database
K. Brian Kelley
@kbriankelley
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply