September 3, 2016 at 1:06 am
Hi
I have a table with 11 G data space and 25,000,000 records.3 fields are configured for full text search, they are all nvarchar(4000) and the cataloge size is 22 G.
every day about 1,000,000 records,are inserted into this table.
But I have a lot of insert timeout.for example when i run sql profiler or in other times.
1.does population is the reason for timeouts?
or
2.does the size of cataloge have effect on performance of it?
September 3, 2016 at 3:35 am
mah_j (9/3/2016)
HiI have a table with 11 G data space and 25,000,000 records.3 fields are configured for full text search, they are all nvarchar(4000) and the cataloge size is 22 G.
every day about 1,000,000 records,are inserted into this table.
But I have a lot of insert timeout.for example when i run sql profiler or in other times.
1.does population is the reason for timeouts?
or
2.does the size of cataloge have effect on performance of it?
There are several possible reasons for the timeouts but without further information it is hard to tell what's the cause of the problem. Can you please elaborate further on the table structure, how it's used, how the inserts are done, relationships, indices etc.?
😎
September 3, 2016 at 6:36 am
There is just insert and select on this table .It has a clustered Index on bigint field.and one non_unique_clustered.
CREATE NONCLUSTERED INDEX [IX_ErrorLogFN] ON [dbo].[ErrorLog]
(
[ParentLogId] ASC,
[ActionDate] ASC,
[TermID] ASC,
[MerchID] ASC,
[ActivityType] ASC
)
The select queries does not run a lot in a day.
The table does not have any relationships.
Actually there is not any special cost on it,just insert and population.
September 3, 2016 at 7:26 am
Quick question, what is the relevant output of this query
😎
SELECT
OBJECT_NAME(SIDX.OBJECT_ID) AS TABLE_NAME
,SIDX.name AS INDEX_NAME
,SIPS.index_type_desc AS INDEX_TYPE
,SIPS.avg_fragmentation_in_percent AS IDX_FRAG_PRCT
,SIPS.page_count AS PAGE_COUNT
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) SIPS
INNER JOIN sys.indexes SIDX
ON SIDX.object_id = SIPS.object_id
AND SIDX.index_id = SIPS.index_id
WHERE SIPS.avg_fragmentation_in_percent > 0
ORDER BY SIPS.avg_fragmentation_in_percent DESC;
September 3, 2016 at 9:20 pm
This is the output:
ErrorLog IX_ErrorLogFN NONCLUSTERED INDEX 91.3237076278522 144405
ErrorLog PK_Log CLUSTERED INDEX 1.37709431333101 1301799
*and in addition the timeout occurs some times,may be we do not have it for 2 days.
September 4, 2016 at 5:51 am
mah_j (9/3/2016)
This is the output:ErrorLog IX_ErrorLogFN NONCLUSTERED INDEX 91.3237076278522 144405
ErrorLog PK_Log CLUSTERED INDEX 1.37709431333101 1301799
*and in addition the timeout occurs some times,may be we do not have it for 2 days.
Do you have any index maintenance jobs running on the server? The IX_ErrorLogFN index is 91% fragmented, suggest you address that first of all.
😎
September 5, 2016 at 5:05 am
You mean,the time out is because fragmentation on that index?
What about catalog size?Does is have any effect on it?
What is catalog size and what happen when we rebuild it?
September 5, 2016 at 11:01 pm
mah_j (9/5/2016)
You mean,the time out is because fragmentation on that index?
I'd say the level of fragmentation indicates that there is a lot of split pages occurring on inserting/updating data.
Those splits are what is causing the time outs, not just the fragmentation itself.
Look into the table design and see if you can minimise the necessity of page split.
Probably index padding would be a good idea.
What about catalog size?Does is have any effect on it?
What is catalog size and what happen when we rebuild it?
Catalogue is updated by a background job running asynchronously, on its own schedule.
Does not affect live updates at all.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply