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?

Answer 1

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

Answer 2

Sorry for the "format"!

How should the Select look like?

Answer 3

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

Answer 4

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 [e-mail].

Answer 5

I will test it and come back ...

Best Regards

Bernd

Answer 6

Many Many Thanks for the Hint!

I will test ist and come back!

Best Regards

Bernd

Answer 7

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

Answer 8

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?

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

He come to me... I work with a lot of Database but I have Never so a Problem...

Answer 13

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

Answer 14

It is only with this table
Others with a similar size work perfectly

I will test it tomorrow...

Any other ideas?

Answer 15

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

Answer 16

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

Answer 17

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.

Answer 18

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

Answer 19

Can anyone help me? Best Regards Bernd

Answer 20

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.

Answer 21

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