Answered by:
Sql Server 2012 Speed Problem

-
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.9600The 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?
- Edited by Shanky_621MVP, Moderator Tuesday, July 10, 2018 1:12 PM formatting
Question
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
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- Proposed as answer by Teige GaoMicrosoft contingent staff, Moderator Wednesday, July 11, 2018 6:43 AM
-
-
Sorry for the "format"!
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
How should the Select look like?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 -
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. -
-
-
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
-
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?
-
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 -
-
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
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 -
-
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
-
-
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
-
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
-
-
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 availableWe 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 cTo 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).
I hope that someone sees the mistake ...
Or has a tip / note what needs to be done ...
Best Regards
Bernd -
-
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
-