Unanswered: sql2005 and possible database auditing/monitoring...
Does 2005 have some kind of new feature that audits/monitors changes to a database kind of like an antivirus or something.
Reason for question:
1) inserting records into database, 1000 records takes about 2 minutes.
2) reading those 1000 records takes about 45 seconds
3) updating those 1000 records takes about 15 minutes
4) yes we are using ntwdblib.dll and a 4gl language
i was running a test program to add, read, update, delete 1000 records and that is when i noticed that insert, update, delete took a performance hit whereas reading didnt. i ran my test program on a control server (in house) and then at the clients side(matching OS, MSSQL 2005 SP2). Results from test program: The UPDATE process on client side took about 4x longer, INSERT about 2x longer, DELETE about 1.5x longer, READ was actually faster on the clients system.
so this made me wonder if their was some kind of database monitoring/auditing going on.
and not even close to the best solution. but i have to live with it.
yes it does use loops & cursors to access the data, 1 row at a time.
yes i know it aint the server/database that is the slowdown.
we use COBOL programs the use a RUNTIME that uses ntwdblib.dll to access the data. Most inefficient method possible. but keep in mind it is bandaid for now. we are writing all new apps with C# with new file structures. but that is a project that is still about 6 months from completion and it will take a while to get 100+ customers migrated/upgraded from the current database structure up to the new database structures.
for the most part of the 100+ customers the application response time is within reason. but now at 4 sites in particular, which have screaming servers with lots of RAM (upto 16GB at 2 sites), fast HD's and SQL2005 SP2, the application response is like i have described.
we monitored the server and work station while running the test program. while inserting, updating and deleting the network traffic just pretty much dies down. while reading the data the network traffic shot up.
the cpu usage stays about the same across the board.
So i am trying to figure out why during update the DATA being transferred just dies off. while all the other indicators dont show a hit.
But while it is crappy and slow we now have over 3000 customers using it and the list is growing. We were strictly using ISAM files, which we still are for customers that don't want to go to SQL systems. So by adding that SQL option to our software, without having to do any rewrite of the applications, we have added about 150 sql customers. We knew going into it that we would be writing a new application, which has been in the works for about 1 1/2 yrs now.
I wasn't looking for an option that made system run slow just an option for system doing some kind of auditing routine. trying to figure out if the customer might have turned on/installed it on their installation of sql2005. we ran the same test program on a test system that we set up in house and it takes about 3-4 minutes to complete the test. so i am looking for something that would slow down their system.
If I had my way i wouldnt be on the old application team but on the new applications team.
Well I guess you know ISAM techniques used on SQL Server negate pretty well all the performance advantages of SQL Server.
Please let me know the answer to my question. Sounds unlikely these are upgraded databases but still....
You could try googling for sql server auditing scripts. Run these on your in house stuff and on the client server. In particular I would check that they haven't piddled with indexes, auto generate stats is on, indexes are defragged regularly (though ironically the iterative nature of the processing should make this less of a factor).
Have you simulated the same load as your clients will be putting on their server? Iterative stuff like this will keep locks on the tables for quite a long time,locking out other processes which in turn will lock out further processes when they eventually acquire a lock.
They have a Dell quad processor,SATA drives (200+GB), 16GB RAM, OS=Server 2003, SQL 2005 SP2, Cisco equipment for network.(Dont know the specifics yet)
We have single CPU, IDE drive 150+G, 2GB RAM, OS=Server 2003, SQL 2005 SP2, Cisco equipment for network.
Both systems are new installs of SQL2005 (no upgrades from previous version)
Our server has been setup in test room, our IT dept got their configuration info and tried to duplicate as much as possible here. Main difference is our was cheaper equipment than theirs and slower.
As for the database it is the same. we copied theirs to ours.
when we ran our last round of tests we had them disconnect all other users from server.
That is why i was wondering, if in SQL 2005, they might have a new self auditing feature that i wasnt aware of. But obviously there isn't one. So it looks like we will devote our time to examining their SQL2005 setup to find something else...
In case someone else might run across a slow sql 2005 installation.
One of the sites ran a windows update on their sql servers. They installed all the latest fixes and so forth. (Yes it was verified that their sql 2005 was on SP2) After the did the updates I tested my program again and the performance issues were gone. So we had one of other problem childs do the same thing since they were OS 2003 Server & sql 2005 SP2. The slow update time is now gone at this site as well. We are working with the other 3 sites to do the same.