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