Major MTS Performance Problems

bwoods

HELP ASAP
I have some components installed on MTS that are working fine through Unit & System Testing with performance not too bad. When we tried some artificial Stress Testing (ie about (ONLY 10-15) people attacking the same part of the system at the same time) the system got hung up with some components in use for long periods of time and eventually timing out. Some updates where made but most of the transactions up and ended up timing out etc.

We have a Component that does all the update to the database and a component that does all the Business Logic. Both these components get stuck.

Cheers in advance.

BW

kayos

Right this sounds like a problem we used have in work I need a bit more info but heres a few things to try out

If your transactions are timing out the most likely cause is your db as contention issues are you using dynamic SQL or Stored Procedures ?? One way around this is to optimise your SP's like a good thing. If you using SQL 7 or above try using hints

eg
SELECT * FROM Table WITH (NOLOCK) <- where your just rwading
or
WITH (ROWLOCK,REPEATABLEREAD) <- where your Updating/Deleting/Inserting into a table

Make sure your using connection pooling if you can

If your using ADO to connect and are using Stored procedures do not for the love of god use ado to find out the needed parameters for a SP as this can lock out the whole db for a while this is fine with a hand full of users but it is a killer when you add more users.

If none of the above help just post make a little more info and I'll see what I can do.

kayos

bwoods

How can I do connection pooling. I have VB components that are running on MTS.

bwoods

Sorry for annoying your good self. Thanks for the help on Boards.ie.

Firstly we use very little stored procedures. (But I think we are going to need to use them a bit more.) We are using ADODB Updatable recordsets (In places) to update it. I changed it to a stored procedure and it helped a bit but not all. Also to get info from the database we call the sp sp_ExecuteSQL.

You mentioned connection pooling in you. Where or how do I do that? Let me know if you get the chance.

kayos

Right connection pooling can be used in a few dirrefent ways all depending on what you use to connect to the DB. Firstly just a quick overview of connection pooling Connection pooling allow a connection to be kept in a pool for a certain period of time so when an application calls to connect to the DB you pick up a free connection if one is available and if not it creates a new one. Hows this helps is that creating a connection to a DB can be the most costly part of a call so keeping existing connections helps here. If I remember rightly you have to connect with the same connection string for this to be used. Take a look at http://msdn.microsoft.com/training/free/chapters/mvs/mvs01244.htm and check around on msdn.microsoft.com you'll get some proper info on it there.

As for not using SP's this can really hit hard in two ways
1) If you every have to chage your SQL you have to change your VB code and your SQL statements can end up all over the place which is just a night mare in the long run.
2) SQL Server has to compile and do up a query plan every time you call a piece of sql even if you called it a second before. If you use sp's SQL keeps this plans until such a time as it needs to update them (say you change a table design). heres a few rough figures to show how nasty this is if you have a sp you can use the option WITH RECOMPILE at the top of the SP which causes SQL to trow away its old plans and create new ones which is justlike excuting dynamic SQL now one SP I was optimising (when moving from 6.5 to 7) was taking 3 seconds to run and this SP was called a hell of a lot. When I took the WITH RECOMPILE off and added a few locking hints it stayed the same for say the first two runs then started going down and down the more it ran I got it down as far as 0.03 of a second. The reason for this is that SQL 7 works out the best way to run your SP the more it is run and if you have WITH RECOMPILE on the SP it never keeps this info just like it never keeps info on dynamic SQL ( even when using sp_ExecuteSQL) .

just to wrap up
Find out how to implement Connection pooling for you set up

move you SQL into SP's (you thank god(maybe even me) you did this in th long run)

Even though ADO is very good if you use certain features it can and will kill preformance.

After this post my rates go up from free to £1000 per post . Honestly I hope this works for you.

kayos