Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2002
    Posts
    71

    Unanswered:

    Jezemine,
    No, the number of reads is approximately the same. I can also confirm the disk read speed is the same on the test vs. production server. Update stats is run regularly on the production server - as I test, I ran sp_updatestats and then immediately ran the query a few times but it didn't affect the duration. Apart from the durations in the profiler traces, I can't see any differences. Clearly, something is causing the increased duration on the prod server but I don't know where to look to find it. It's definitely within SQL Server 2000.

    Clive

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Fragmented indexes? Although it should not really make much difference on singletons like this.

    Are those CPU durations you are quoting?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2002
    Posts
    71
    I did a simple test to rule out index fragmentation... Although the tables involved were not particularly fragmented, I defragged them and ran the queries again. No difference. As you said, shouldn't be an issue anyway with smallish tables. The durations are elapsed time as reporting in the 'duration' column of profiler trace output and also in query analzyer - bottom right.

    I just remembered that I should test the query on our DR server, which is basically the same server and configuration. It ran much quicker on the DR box... Quicker as one might have expected than on a DL380 test server. However, as I said, on the production server it runs 9 or 10 times slower than on a DL380 test server.

    Clive

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The tables are sufficiently large to be affected by defragmentation but this has the greatest impact on scans and I don't expect you to get much in the way of scans here.

    Any particular reason IPStateTransitionHistories is not indexed? I know it is not related to the difference but still.

    Have you scripted out the objects concerned from both dbs to check 100% they are identical?

    Do they contain identical data and return identical result sets?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2002
    Posts
    71
    Yes, all is identical. I tried another test with interesting results. I created a test database on the production server and restored the database in question to it. This gave me the same database on the same production server but just with a different name. The act of restoring it has significantly improved query peformance. I do think this is an indexing issue. Part of the problem was that I wasn't doing an update usage command regularly enough. This seemed to screw up my maintenance procedure because it simply wasn't picking up fragmentation issues that should have been reported by dbcc showcontig. I'll see how it goes tomorrow.

    Clive

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    hmm, I am surprised restoring from a backup would have helped. restoring shouldn't fix fragmentation issues should it?

  7. #7
    Join Date
    Nov 2002
    Posts
    71
    I don't know but restoring has some effect on something. Wish I knew! Last night I amended my db mainteance processes for the affected database by ensuring that nearly all indexes were rebuilt (DBCC DBREINDEX) but not much improvement this morning. Something is screwing performance and, so far, a restore is the only thing that actually fixes it.

    Clive

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Index fragmentation is restored right with the index. Again I don't think it is relevent.

    Info:
    http://www.microsoft.com/technet/pro.../ss2kidbp.mspx

    Why is IPStateTransitionHistories not indexed? What are the indexes on ChangeNotes?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Nov 2002
    Posts
    71
    I don't think it's an indexing issue but something in the act of restoring a database has had an effect. IPStateTransitionHistories isn't indexed because it isn't... It's become an issue as the number of rows in the table has increased. The developer responsible for this will put an index on it now but this is not a cause of the problem because the exact same tables, without an index, are used in test and the query completes in a fraction of the time. ChangeNotes is indexed appropriately and isn't an issue here.

    Clive

Posting Permissions

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