ADO Timeout/Query Analyzer is not

Question

ADO Timeout/Query Analyzer is not

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

Unless your sp has been defined using the "with recompile" keyword, it will only compile at first run (when loaded into proc-cache). Whenever it is kicked out of proc-cache it will need to be recompiled when loaded again.

This compilation includes choosing an execution plan for each query, included in the proc.

Dynamic sql will need to be compiled each time when used.

Because with the dynamic sql predicates are "hard coded", there's a good chance for sqlserver to choose a differend execution plan because of the individual statistics for the used predicates.

This means that when a user is complaining for is sp to take a long time, one should ask for the used parameters to determine the effectiveness of a comparable dynamic sql.

I'd roughly compare sp with a takeaway restaurant and dynamic sql with a haute cuisine. The takeaway will serve more people for less cost and the ones that are not happy with it will have to pay more in a haute cuisine restaurant.

Offcourse it's a dba's goal to have as much takeaway as possible with haute cuisine quality on his db-server(s).

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

gbrockman SSC-Addicted Points: 431 More actions · Answer 2

We have noticed that our SYS_SPROC_VERSION is different (8.00.178 vs 8.00.375 on an instance that doesn't have the same problem with a restore of the same database) from the rest of our database servers and different from the instscat.sql file found in the SQL/Server install directory of the failing instance. Microsoft has told us to run that script against the master database on the failing instance. They believe that will fix the problem. I will post back later this week and let everybody know the results.

Antares686 SSC Guru Points: 125444 More actions · Answer 3

I have heard that before in an article I read somewhere but here is some on what the big deal is.

http://www.winnetmag.com/SQLServer/Article/ArticleID/21752/21752.html

However the file is instcat.sql can also be an issue with MDAC updates on the server or the clients.

To correct you may actually want to search for all instances of instcat.sql left various installs and which every has the highest SYS_SPROC_VERSION values being inserted may be the one you want to install.

Note thou, this is not a guaranteed fix and of course you shoul have a backup of master db before you run for safety.

gbrockman SSC-Addicted Points: 431 More actions · Answer 4

Running the Instcat.sql seems to have fixed the problem.

Brett Slaski Old Hand Points: 365 More actions · Answer 5

You need to extend the connection timeout period for ado. The default is 15 seconds.

assuming you are using a connection object of cn:

cn.ConnectionTimeout = 30

Extend it more if you need to.