none
Sql Server 2012 Speed Problem

    Question

  • Hello!

    I hope that someone can help me!

    I have a database Tebelle with> 6 million records

    This one table broke for a select out of the database about 8 seconds for an answer ...

    All other tables within the database are running fast ...

    To build the table:

    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [DEHODEH1].[LOG_INFO](
           [LOG_TS] [datetime2](6) NOT NULL DEFAULT (getdate()),
           [LOG_MAN] [char](3) NOT NULL DEFAULT (' '),
           [LOG_KATEGORIE] [char](30) NOT NULL DEFAULT (' '),
           [LOG_NTID] [char](8) NOT NULL DEFAULT (' '),
           [LOG_STATUS] [char](1) NOT NULL DEFAULT (' '),
           [LOG_FILE_ACTION] [char](1) NOT NULL DEFAULT (' '),
           [LOG_UP_INFO] [char](2) NOT NULL DEFAULT (' '),
           [LOG_MQ_TRANSFER] [char](1) NOT NULL DEFAULT (' '),
           [LOG_PROG] [char](8) NOT NULL DEFAULT (' '),
           [LOG_KEY] [char](50) NOT NULL DEFAULT (' '),
           [LOG_DATA] [varchar](3900) NOT NULL DEFAULT (''),
    CONSTRAINT [LOG_TS] PRIMARY KEY CLUSTERED 
    (
           [LOG_TS] ASC,
           [LOG_MAN] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
    )
    
    GO
    
    SET ANSI_PADDING OFF
    GO

    Now the Select:

                select                                                
                   log_ts,                                            
                   log_kategorie,                                     
                   log_ntid,                                          
                   log_key,                                           
                   log_data                                           
                from dehodeh1.dehodeh1.log_info                                
                where log_key       >= '210000009210        '  AND            
                      log_key       <= '210000009210ZZZZZZZZ'  AND            
                      log_kategorie  = 'ADRESSE'                      
                 order by log_ts 

    And the Database Informations:

    SQL Server     11.0.620.0
    MDAC             6.3.9600.1850
    OS                   6.3.9600

    The problem is not the call within the SQL Server but if the call is made externally, eg. via XA Resource

    I suspect that it is the structure of the table?




    Tuesday, July 10, 2018 12:32 PM

Answers

  • One of them does a clustered index scan, and then an order. The native one just does a scan.

    Can you re-issue your query like this?

    DECLARE @startTime DATETIME
    SET @startTime = GETDATE();
    SELECT                                      
       LOG_TS,                                  
       LOG_KATEGORIE,                           
       LOG_NTID,                                
       LOG_KEY,                                 
       LOG_DATA                                 
    FROM DEHODEH1.LOG_INFO                      
    WHERE LOG_KEY       >= '210000009210'   AND  
          LOG_KEY             <= '210000009210'   AND  
          LOG_KATEGORIE  = 'ADRESSE'            
    ORDER BY LOG_TS DESC 
     option (recompile)

    Note in the native one you two parameters are the same - '210000009210'   which is not what you are passing in your original query.

    • Marked as answer by Bernd Riemke Friday, July 20, 2018 7:50 PM
    Thursday, July 19, 2018 8:33 PM

All replies

  • Please look at formatting when  you post your question, I have formatted it but removed something liek table structure. Can you add it in the question again.

    Now as per your query a non clustered index on column Log_key which would include log_kategorie should make your query fast


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, July 10, 2018 1:14 PM
    Moderator
  • Sorry for the "format"!

    How should the Select look like?
    Tuesday, July 10, 2018 1:28 PM
  • Sorry for the "format"!

    How should the Select look like?
    It is not the select but the non clustered index which you should be worried about like I said above. Currently CI is on two columns which are not part of predicate. The where clause has column `log_key` and `log_kategorie`. You need to create NCI on Lo key and include log_kategorie. I hope this is clear, if not let me know

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, July 11, 2018 6:37 AM
    Moderator
  • Hi Bernd Riemke,

    Could you please share the query plan of your query?

    According to your description, it looks like that you did not create a non-clustered index on the column log_key and log_kategorie, if it is, it is normal to take 8 second when querying on a 6 million records table.

    After creating the non-clustered index, it will query faster, if it did not use the index, we can also use the WITH (INDEX(****)) to force using.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 11, 2018 6:48 AM
    Moderator
  • I will test it and come back ...

    Best Regards

    Bernd

    Wednesday, July 11, 2018 7:01 AM
  • Many Many Thanks for the Hint!

    I will test ist and come back!

    Best Regards

    Bernd

    Wednesday, July 11, 2018 7:02 AM
  • Can you have an index on (log_kategorie,log_key ) include (log_ts,log_ntid,log_data   )?

    )

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, July 12, 2018 5:04 AM
    Moderator
  • Here is the new Key, but it is slow again...

     
    USE [DEHODEH1]
    GO
     
    /****** Object:  Index [XLOGINF]    Script Date: 7/13/2018 3:33:00 PM ******/
    CREATE UNIQUE NONCLUSTERED INDEX [XLOGINF] ON [DEHODEH1].[LOG_INFO]
    (
           [LOG_TS] ASC,
           [LOG_MAN] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
    GO
     
    /*---------------------------------------------------------------------------------------------------*/
     
    USE [DEHODEH1]
    GO
     
    /****** Object:  Index [YLOGINF]    Script Date: 7/13/2018 3:33:06 PM ******/
    CREATE NONCLUSTERED INDEX [YLOGINF] ON [DEHODEH1].[LOG_INFO]
    (
           [LOG_MAN] ASC,
           [LOG_KATEGORIE] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
    GO
     
    /*---------------------------------------------------------------------------------------------------*/
     
    USE [DEHODEH1]
    GO
     
    /****** Object:  Index [ZLOGINF]    Script Date: 7/13/2018 3:33:31 PM ******/
    CREATE NONCLUSTERED INDEX [ZLOGINF] ON [DEHODEH1].[LOG_INFO]
    (
           [LOG_KEY] ASC,
           [LOG_KATEGORIE] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
    GO
     
    

    Have you and idea?

    Monday, July 16, 2018 1:04 PM
  • I only asked you to create index on Log_key and include log_categorie, whats reason for creating other indexes

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, July 16, 2018 1:36 PM
    Moderator
  • I have just received the settings from the DB Administartor.
    It's about the time behavior that no one can explain.
    Is there anything wrong that explains this timing?
    Now I have written everything from the table what I have ...
    An idea?

    Best Regards

    Bernd

    Monday, July 16, 2018 2:03 PM
  • I have just received the settings from the DB Administartor.
    It's about the time behavior that no one can explain.
    Is there anything wrong that explains this timing?
    Now I have written everything from the table what I have ...
    An idea?

    Best Regards

    Bernd

    I think in this case it would be better to put this question infront of him, he has better visibility to the database and its configuration, what is the time factor you are talking, sorry I cannot understand that

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, July 16, 2018 2:55 PM
    Moderator
  • He come to me... I work with a lot of Database but I have Never so a Problem...
    Monday, July 16, 2018 5:09 PM
  • Perhaps the problem is not with the query but with the server. Are other queries just as slow, or it just this one?

    Did you clear wait stats, running this query again, and then check wait stats?

    Does this index help?

    CREATE NONCLUSTERED INDEX [ZLOGINF] ON [DEHODEH1].[LOG_INFO]
    (
           [LOG_KATEGORIE] ASC,[LOG_KEY] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
    GO


    Monday, July 16, 2018 5:19 PM
  • It is only with this table
    Others with a similar size work perfectly

    I will test it tomorrow...

    Any other ideas?

    Monday, July 16, 2018 7:09 PM
  • Is this not the same:

    CREATE NONCLUSTERED INDEX [ZLOGINF] ON [DEHODEH1].[LOG_INFO]
    (
           [LOG_KEY] ASC,
           [LOG_KATEGORIE] ASC
    )WITH 
    (
    PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    SORT_IN_TEMPDB = OFF, 
    DROP_EXISTING = OFF, 
    ONLINE = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON, 
    FILLFACTOR = 80
    )
    GO
    

    Monday, July 16, 2018 7:20 PM
  • Hello!

    In the meantime I found out a phenomenon ...
    If the call is made "Native" by the SQL Manager, the table is fast.
    However, if this is called from the outside it comes to the slow time behavior ...
    What I'm wondering is that there is a table with> 40 million records that has no problems whatsoever.
    Does anyone have an idea on which button I have to turn?
    Tomorrow I can provide some protocols that represent the response.

    Maybe someone has already had such behavior and has an idea?

    Best Regards

    Bernd

    Wednesday, July 18, 2018 3:26 PM
  • What you are describing is almost always a problem with a bad plan cached.  That query you posted must not be the actual query running slowly.  Please use SQL Profiler to get the actual query being run and post it.

    Wednesday, July 18, 2018 3:37 PM
    Answerer
  • Hi,

    attached are the two traces where we ran first the “native”, means direct call 
    from within the SQL management studio and in addition the same via the Normal call(outside the Management Studio).

    We tested three scenarios for both cases:
    a)      As it’s already setup on our side, means with the non-clustered index “ZLOGINF”
    b)      After creating the new proposed non-clustered index from Microsoft “ZLOGINF2”
    c)      With none of both non-clustered indexes available

    We ran each scenario three times in a row, means the results in each of both traces represent:
    run 1 to 3 -> scenario a
    run 4 to 6 -> scenario b
    run 7 to 9 -> scenario c

    To me, it’s obvious, that the native call is using one of the non-clustered indexes (ZLOGINF or ZLOGINF2)
    if they are available, ending up in a really quick and expected run time of few milliseconds.
    If the index is not available the native call also needs thousands of milliseconds.

    When running the sql from the Normal side (via SQL Call), neither the already existing non-clustered index
    nor the new proposed one will be used, ending up in a run time of thousands of milliseconds.

    You can check that in the attached traces (the used plan is part of them).

    Trace1

    Trace2 (Native)

    I hope that someone sees the mistake ...

    Or has a tip / note what needs to be done ...

    Best Regards
    Bernd

    Thursday, July 19, 2018 10:31 AM
  • Can anyone help me? Best Regards Bernd
    Thursday, July 19, 2018 7:53 PM
  • One of them does a clustered index scan, and then an order. The native one just does a scan.

    Can you re-issue your query like this?

    DECLARE @startTime DATETIME
    SET @startTime = GETDATE();
    SELECT                                      
       LOG_TS,                                  
       LOG_KATEGORIE,                           
       LOG_NTID,                                
       LOG_KEY,                                 
       LOG_DATA                                 
    FROM DEHODEH1.LOG_INFO                      
    WHERE LOG_KEY       >= '210000009210'   AND  
          LOG_KEY             <= '210000009210'   AND  
          LOG_KATEGORIE  = 'ADRESSE'            
    ORDER BY LOG_TS DESC 
     option (recompile)

    Note in the native one you two parameters are the same - '210000009210'   which is not what you are passing in your original query.

    • Marked as answer by Bernd Riemke Friday, July 20, 2018 7:50 PM
    Thursday, July 19, 2018 8:33 PM
  • Thats it !!! :-)

    After a Long search...

    The Point was:

    OPTION(RECOMPILE)

    And one more:

    EXEC sp_updatestats

    After that the select Need 1-1,5 second

    Many Many Thanks for the Help!!!

    Best Regards

    Bernd

    Friday, July 20, 2018 7:50 PM