Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: Optimizing TSQL Server Performance?

    I have some simple TSQL running on a large block of data (565 million records). This process is estimated to take around 5 days and it is critical to get this running as quickly as possible.

    I'm watching Windows Performance Monitor and both disk and CPU use are really low and all data is local so there is no network access involved. This is the only task running on this database server. How could this be? The process is running; I just need it to run faster. Typically a system is CPU-bound, disk-bound, network-bound, or operating under maximum capacity. This seems to be none of the above.

    In Enterprise Manager, I see the process is sleeping with a Wait Type of "MISCELLANEOUS". What does that mean? Online help gives a pretty useless explanation of that.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So post your simple TSQL...
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That's a lot of rows.

    Can we assume it's an UPDATE statement?

    My bet is that you're blowing out your log, making pagefile.sys enormous and filing up tempdb...

    Probably need to do the update in batches...

    post the query like the blind dude suggests
    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.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Looks like another candidate for BCP OUT...BULK INSERT.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    While the query is running, run this in Query Analyzer:

    Code:
    select spid, cpu, physical_io, blocked, waittype, lastwaittype, waitresource
    from master..sysprocesses
    where spid = (spid of monster query)
      or blocked > 1
    The waittype column should be 0x0, unless you are actually waiting for something at that moment. If you are, then you can check KB article 244455 for some of the more common waittypes. This will shed a little more light on the query as it runs.

    Also, have you checked the query plan to make sure the query is taking advantage of any indexes?

  6. #6
    Join Date
    Oct 2003
    Posts
    268
    Thanks for the excellent feedback so far guys. The process actually finished much sooner than I thought but with fewer rows that I thought. It only output 80 million merged rows from the source set of 560 million. I wanted to check if there were any rows that were skipped:

    I had the following running for over 24 hours before I killed it:
    Code:
    SELECT TOP 1000 * FROM Stages WHERE NOT EXISTS (SELECT * FROM Recipients WHERE Stages.UserName = Recipients.UserName AND Stages.DomainID = Recipients.DomainID)
    This was disk bound (SCSI RAID) the whole time. The frustrating thing is I have no idea what it is doing (the execution plan looked OK) and I have no idea how much longer it will take (could be 5 minutes could be 5 weeks).

    I just started:

    Code:
    SELECT TOP 10 * FROM Stages
    LEFT OUTER JOIN Recipients ON (Stages.UserName = Recipients.UserName AND Stages.DomainID = Recipients.DomainID)
    WHERE Recipients.UserName IS NULL



    On another server I've had the following running for 35+ hours and I can't figure out why it would take so long:

    Code:
    INSERT INTO Removables (EmailUser, EmailDomain, BounceCategoryID, EntryDate)
    SELECT
    	dbo.getEmailUser(OldRemovables.EmailAddress) AS EmailUser
    	, dbo.getEmailDomain(OldRemovables.EmailAddress) AS EmailDomain
    	, 8 AS BounceCategoryID
    	, getdate() AS EntryDate
    FROM OldRemovables
    WHERE NOT EXISTS (SELECT * FROM Removables WHERE Removables.EmailUser = dbo.getEmailUser(OldRemovables.EmailAddress) AND Removables.EmailDomain = dbo.getEmailDomain(OldRemovables.EmailAddress))
    GROUP BY dbo.getEmailUser(OldRemovables.EmailAddress), dbo.getEmailDomain(OldRemovables.EmailAddress)

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This left outer join syntax MAY be faster, but those function calls linking the tables are killing you. They blow any chance of using an index.

    INSERT INTO Removables (EmailUser, EmailDomain, BounceCategoryID, EntryDate)
    SELECT dbo.getEmailUser(OldRemovables.EmailAddress) AS EmailUser,
    dbo.getEmailDomain(OldRemovables.EmailAddress) AS EmailDomain,
    8 AS BounceCategoryID,
    getdate() AS EntryDate
    FROM OldRemovables
    left outer join Removables on Removables.EmailUser = dbo.getEmailUser(OldRemovables.EmailAddress)
    AND Removables.EmailDomain = dbo.getEmailDomain(OldRemovables.EmailAddress)
    WHERE Removables.EmailUse is null --Better to use Removables.PrimaryKey here...
    GROUP BY dbo.getEmailUser(OldRemovables.EmailAddress), dbo.getEmailDomain(OldRemovables.EmailAddress)
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and what is really going to pi$$ you off is when this query is done running 35 hours from now:

    SELECT TOP 10 *
    FROM Stages
    LEFT OUTER JOIN Recipients ON Stages.UserName = Recipients.UserName AND Stages.DomainID = Recipients.DomainID
    WHERE Recipients.UserName IS NULL

    ...and you realize that since you didn't include a sort order absolutely any 10 records could have showed up in the result set.

    I have to ask. Do you have indexes on any of these tables?
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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