Post reply

Timeout Problem

  • June 7, 2006 at 1:09 am

    #113314

    I have a problem in SQL server 2000. I will be glad if you can highlight on the following.

    The problem is as follow:

    1. We have huge number of reports running on a daily basis, around 1500 reports with complex queries.
    2. The data queried against is around 13 Gigabytes.
    3. We are getting time out on some reports at the bottle neck time (concurrent scheduled reports, some that take 1-2 hours of execution time)
    4. The sever specification is 8 CPUs Pentium Xeon 3.2 GHz each with 16 GB RAM
    5. Some of the tables and views on this machine have replicated data from another server.
    6. I am thinking that one of the problems is because the replicated tables are not indexed so the estimated execution plan shows a full table scan.

    Do you have a good proposal on how to solve this critical issue?

    Best regards,

    Georges

  • June 7, 2006 at 10:17 am

    #642332

    Here's a reply I'd given Georges in response to a PM.

    Hi Georges,

    Yes, you probably need to analyze the queries being created to see whether inefficient access paths are being chosen. For common reports involving joins on infrequently changing data, you may be able to create indexed views to reduce the processing and IO required. Finally, you may find that your IO system can't deliver the throughput necessary or that you're maxing out your CPUs. Are you CPU or IO bound when the timeouts occur?

    Thanks

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • June 7, 2006 at 3:10 pm

    #642398

    Hi Paul,

    We tried to create materialized view in order to check performance but we face another problem.

    Suppose we have table X and we do 2 joins on it. The first is an INNER JOIN and the second is LEFT JOIN, the index fails to be created. Check the below example.

    -- If we remove the comments (marked in red) the index fails

    CREATE VIEW SheetsMtzed WITH SCHEMABINDING

    AS 

     SELECT SheetID

      ,lt1.LanguageID

      ,SheetTypeID

      ,lt1.TextString AS SheetDesc

      --,lt2.TextString AS SheetName

      ,SheetScore

      ,SheetStartDateTime

      ,SheetEndDateTime

     from [dbo].Sheets_Base b

      join [dbo].LanguageText lt1

       on lt1.LanguageTextID = b.SheetDesc_LanguageTextID

      --left join [dbo].LanguageText lt2

       --on lt2.LanguageTextID = b.SheetName_LanguageTextID

       --and lt2.LanguageID = lt1.LanguageID

    GO

    /* Create the Index */

    SET  ANSI_PADDING,

     ANSI_WARNINGS,

     CONCAT_NULL_YIELDS_NULL,

     ARITHABORT,

     QUOTED_IDENTIFIER,

     ANSI_NULLS ON

    GO

    SET NUMERIC_ROUNDABORT OFF

    GO

    CREATE UNIQUE CLUSTERED INDEX PK_SheetsMtzed ON [dbo].SheetsMtzed( SheetID, LanguageID )

  • June 7, 2006 at 3:29 pm

    #642399

    Outer joins are not supported in indexed views.

    If you want to implement this purely via views, you could keep this view and change it to an inner join, and then UNION ALL this view to another resultset that returns the rows using a WHERE NOT EXISTS to locate the rows that the outer join would have included.

  • June 8, 2006 at 7:14 am

    #642516

    It certainly sounds like you need to index your data a bit more to at least allow each report to avoid table scans - this should reduce contention.  The indexed view idea sounds very promising - the suggestion about a UNION replacing an outer join is very clever   Note that you have to have certain SET options enabled on your connections - the ANSI defaults essentially.  I tend to set these to be on by default at a server level.

    The other alternative is to log ship to a dedicated reporting server; applying the new logs between report runs.  This is expensive since you alreay have such a wonderful server

  • June 13, 2006 at 5:46 am

    #643322

    After we indexed the views we got the following error:

    DELETE failed because the following SET options have incorrect settings: 'ARITHABORT'.

    If you go to the following link http://support.microsoft.com/default.aspx?scid=kb;en-us;305333

    They suggest adding the following ADO code to theapplication after we open the connection to database:

    MyConnection.Execute "SET ARITHABORT ON"

    Is there any patch to solve this issue directly in the SQL server instead of going into lines of code to handle it. Please note that we have some stored pricdure that do insert in the base tables.

  • June 14, 2006 at 9:07 pm

    #643850

    I too have the same issue in my app as we use indexed views.

    Solution is to set the connection defaults to ANSI at the server level - which I think they should be anyway

    Look up sp_configure in Books Online.  The commands below should do the trick.  You can also set some of the defaults at the database level.

    exec sp_configure N'user options', 376

    GO

    RECONFIGURE WITH OVERRIDE

    GO

    Cheers

  • June 27, 2006 at 1:52 pm

    #646200

    Also found this happening when you index a calculated field.  The MyConnection.execute "SET ARITHABORT ON" anter openeing the connection did the trick.

    Kindest Regards,

    Robert Dean Waibel, Jr
    ilxresorts.com

  • June 27, 2006 at 6:00 pm

    #646260

    Very true.  I guess SQL needs some standard way to handle the NULLs, Arithmetic Overflows, when materializing the index, scanning the index, etc.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply