Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2005
    Posts
    71

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    First, all of those time are horrendous...even without indexes you should not be seeing thos times, I mean, hell, it's only 1,000 rows

    You can use sql profile to take a peek at what's going on...BUT I can tell you right now

    It ain't the database

    What's the 4GL application?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm betting the 4GL is using loops/cursors.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Mar 2005
    Posts
    71

    I know our current setup is horrible...

    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.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I know COBOL, Albeit on the mainframe, but Iguess it's all the same, and yes almost all developers code with cursors, nested cursor, nested cursors, neste...did I type that againn?

    In any case, I had a peoplesoft developer write an extract for us that took 9 hours.

    He had 9 levels of nested cursors

    I wrote it as an unlod and it wa done in 5 minutes

    I'm not sure I have any advice to offer, other than to create a list of suspect points in the process and test and document weach one

    Start with the dataabse and do profiler andd document the cpu usage and times..then go from there
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Hicpics:
    Your application is slow because it is crappy.
    You don't have time/resources to repair it now.
    You want a quick fix to make it run faster.

    Well, if there was a quick fix that would make things run faster, your application wouldn't be crappy, would it?

    But it is crappy, so there isn't a quick fix, and you have to rewrite it. That's all there is to it.

    There is no SET OPTION RUN_LIKE_A_SLUG = OFF in SQL Server.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    SET OPTION RUN_LIKE_A_SLUG = OFF in SQL Server.
    The syntax is wrong

    SET OPTION RUN_LIKE_A_SLUG OFF

    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Damn. That's why it wasn't working for me.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Upgraded to SS2005 or created in 2005?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Mar 2005
    Posts
    71

    Yes it is slow...

    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.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What're the differences between your test machine and client machine?
    Location, memory, CPU, Network speed etc etc.
    All of these can have an effect on performance.

    A team is only as good as its worst player.
    <insert a much more interesting anecdote/example>
    George
    Home | Blog

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Mar 2005
    Posts
    71

    Hardware/Database configuration...

    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...

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Has anyone made a site visit?

    Has anyone done a profile trace on their box?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Mar 2005
    Posts
    71

    FYI Follow up...

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •