Should I Re-design table?
-
This table has high transactions with select, insert, update, delete and It index got fragmentation about 90% after rebuild 3-4 hour, also deadlock
What should I do with this? -
No way to answer that question without a lot more details.
Start with the table design and the deadlock graphs
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
ok, problem with IE
-- Create date: 27-03-2015
-- Description:Move data from TBMESSAGEREDUCEQUEUE to TBMESSAGEREDUCESENT and TBMESSAGEREDUCEINFO to TBMESSAGEREDUCEINFOSENT
-- Exam: DECLARE @ResText VARCHAR(MAX) EXEC MCP_DEV.dbo.SP_AUTO_MOVE_TBMESSAGEREDUCE_DONE_AND_REJECT_TO_SENT @TopRecord = 10,@ManagementID = 13,@ResText = @ResText OUTPUT PRINT @ResText
-- =============================================
CREATE PROCEDURE [dbo].[SP_AUTO_MOVE_TBMESSAGEREDUCE_DONE_AND_REJECT_TO_SENT]
@TopRecord INT = 50000
, @ManagementID TINYINT
, @ResText VARCHAR(MAX) OUTPUT
AS
DECLARE
@NEWLINE CHAR(1)
,@SubLoop_Rows INT
,@Success_Rows INT
,@Error_Rows INT
,@TopSubRecord SMALLINT
,@TopSubRecord_Check SMALLINT
,@ISEndSubLoop TINYINT
,@Total_Rows INT /* @Success_Rows + @Error_Rows */
,@MaxLoop INT
,@Loop INT
--,@Row_Count INT
--,@Row_Total INT
--,@IDMESSAGEINFO BIGINT
--,@MANAGEMENTID TINYINT
BEGIN
SET NOCOUNT ON
SET @NEWLINE = CHAR(10)
SET @ResText = ''
SET @TopSubRecord = 1000
SET @TopSubRecord_Check = 1000
SET @Success_Rows = 0
SET @Error_Rows = 0
SET @Total_Rows = 0
SET @MaxLoop = 15
-- Drop Temp Table
--IF OBJECT_ID('tempdb..#TempMoveMsgQueuebyMNID') IS NOT NULL
--DROP TABLE #TempMoveMsgQueuebyMNID
/* Check IDMESSAGE Duplicate */
--IF EXISTS(
--SELECT 1 FROM MCP_DEV_SENT.dbo.TBMESSAGEREDUCESENT (READCOMMITTED) WHERE IDMESSAGE IN ( SELECT IDMESSAGE FROM MCP_DEV.dbo.TBMESSAGEREDUCEQUEUE (READCOMMITTED) )
--)
--BEGIN
--DELETE FROM MCP_DEV.dbo.TBMESSAGEREDUCEQUEUE WHERE IDMESSAGE IN ( SELECT IDMESSAGE FROM MCP_DEV_SENT.dbo.TBMESSAGEREDUCESENT (READCOMMITTED) )
--EXEC msdb.dbo.sp_send_dbmail
----@profile_name='f2u_admin', -- on OLD DB
--@profile_name = 'f2u_mail_system_alarm',-- on New DB
--@recipients = 'Pakpoom@future2you.com',
--@subject = 'Check IDMESSAGE Duplicate',
--@body = @@ROWCOUNT,
--@body_format = 'TEXT'
--END
IF EXISTS (SELECT TOP 1 1 FROM MCP_DEV.dbo.TBMESSAGEREDUCEQUEUE (NOLOCK) WHERE MANAGEMENTID = @ManagementID AND IDOPERATORHANDLESTATUS IN(2,4))
BEGIN
SET @ISEndSubLoop = 0
SET @Loop = 0
WHILE @Total_Rows < @TopRecord AND @ISEndSubLoop = 0 AND @Loop < @MaxLoop
BEGIN
BEGIN TRY
DELETE FROM MCP_DEV_TEMP.dbo.TempMoveMTByManagementID WHERE SESSION_ID = @ManagementID
SET @SubLoop_Rows = 0
IF @TopRecord - @Total_Rows < @TopSubRecord_Check
BEGIN
SET @TopSubRecord = @TopRecord - @Total_Rows
END
ELSE
BEGIN
SET @TopSubRecord = @TopSubRecord_Check
END--- Check MOVE FROM TBMESSAGEREDUCEQUEUE to TBMESSAGEREDUCESENT ---
INSERT INTO MCP_DEV_TEMP.dbo.TempMoveMTByManagementID
(
IDMESSAGE
, SESSION_ID
)
SELECT
TOP (@TopSubRecord)
IDMESSAGE
, @ManagementID
FROM MCP_DEV.dbo.TBMESSAGEREDUCEQUEUE (READCOMMITTED)
WHERE
MANAGEMENTID = @ManagementID
AND IDOPERATORHANDLESTATUS IN (2,4)
ORDER BY
MTSENTDATETIME ASC-- Check all sub rows
SET @SubLoop_Rows = @@ROWCOUNT
IF @SubLoop_Rows > 0
BEGIN
BEGIN TRY
BEGIN TRANSACTION
IF OBJECT_ID('tempdb..#TempMessageReduceQueue') IS NOT NULL
DROP TABLE #TempMessageReduceQueue
SELECT * INTO #TempMessageReduceQueue
FROM MCP_DEV.dbo.TBMESSAGEREDUCEQUEUE (NOLOCK)
WHERE IDMESSAGE IN (
SELECT IDMESSAGE
FROM MCP_DEV_TEMP.dbo.TempMoveMTByManagementID (NOLOCK)
WHERE SESSION_ID = @ManagementID
)
DELETE FROM MCP_DEV.dbo.TBMESSAGEREDUCEQUEUE
WHERE IDMESSAGE IN (
SELECT IDMESSAGE
FROM MCP_DEV_TEMP.dbo.TempMoveMTByManagementID (NOLOCK)
WHERE SESSION_ID = @ManagementID
)
INSERT INTO MCP_DEV_SENT.dbo.TBMESSAGEREDUCESENT
SELECT * FROM #TempMessageReduceQueue (NOLOCK)
SET @Success_Rows = @Success_Rows + @SubLoop_Rows-- Drop Temp Table
IF OBJECT_ID('tempdb..#TempMessageReduceQueue') IS NOT NULL
DROP TABLE #TempMessageReduceQueue
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'CATCH'
SET @ResText = @ResText + 'Execute error: ' + (CASE WHEN ERROR_PROCEDURE() IS NULL THEN '' ELSE ERROR_PROCEDURE() END) + ' ' + ERROR_MESSAGE() + @NEWLINE
SET @Error_Rows = @Error_Rows + @SubLoop_Rows
ROLLBACK TRANSACTION
--PRINT 'Error : ' + RTRIM(CAST(GETDATE() AS nvarchar(30)));
END CATCH
SET @Total_Rows = @Success_Rows + @Error_Rows
END
ELSE
BEGIN
SET @ISEndSubLoop = 1
END
DELETE FROM MCP_DEV_TEMP.dbo.TempMoveMTByManagementID WHERE SESSION_ID = @ManagementID
END TRY
BEGIN CATCH
PRINT 'CATCH2'
END CATCH
SET @Loop = @Loop + 1
PRINT CONVERT(VARCHAR, GETDATE(), 121) + @NEWLINE
WAITFOR DELAY '00:00:00.100'
END
--SET @Row_Count = @Row_Count + 1
-- Drop Temp Table
--IF OBJECT_ID('tempdb..#TempMoveMTByManagementID') IS NOT NULL
--DROP TABLE #TempMoveMTByManagementID
--END
-- Drop Temp Table
--IF OBJECT_ID('tempdb..#TempMoveMsgQueuebyMNID') IS NOT NULL
--DROP TABLE #TempMoveMsgQueuebyMNID
END
SET @ResText = @ResText + 'Success: ' + CAST(@Success_Rows AS VARCHAR(10)) + ' record(s)' + @NEWLINE
IF @Error_Rows > 0 AND @Success_Rows = 0
BEGIN
SET @ResText = @ResText + 'Execute error: ' + (CASE WHEN ERROR_PROCEDURE() IS NULL THEN '' ELSE ERROR_PROCEDURE() END) + ' ' + ERROR_MESSAGE() + @NEWLINE
RETURN 0
END
ELSE
BEGIN
RETURN 1
END
END
- GilaMonster - Monday, July 31, 2017 4:13 AM
Found lock on this SP
Sorry, I cannot put into SQL Code it always refresh the page
-
Gail asked for the table design. How many columns, their data types, the primary key, the clustered index, any additional nonclustered indexes, constraints, triggers, foreign keys. That's the information we need to start with.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
A picture's not very useful. Save the deadlock graph as a .xdl file, and attach it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - Grant Fritchey - Monday, July 31, 2017 5:32 AM
- GilaMonster - Monday, July 31, 2017 5:32 AM
First, I can't run a picture to create the table to run queries against. Second, the index name says nothing about what it's built on.
You have too many indexes though. The chance of all of those indexes being useful is about 0.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Yes. You need to look at redesigning.
Now, the bigger question is, what? That's going to be hard to say. Going off the index names (totally making an assumption, and Gail is right, the name isn't the index), you have duplicated indexes that are unlikely to be used, depending on the query in question. Just one example, you have an index for IDMESSAGEINFO and one for IDMESSAGEINFO_MNID_INCLUDE_DATA. Assuming both of these are the same leading column, one of them is unlikely to get picked for most queries. You're saying you're seeing lots of blocking and deadlocks. Those are caused by poor performance during data manipulation. Any data manipulation of your tables not only has to manipulate the data in the table, but also in all the indexes. Eliminating the duplicates will be a good start.A bunch of the indexes are labeled PARTITIONED. Are these partitioned tables? Is the TBMESSAGEREDUCEQUEUE table partitioned on the MANAGEMENTID column? If so, some of your queries are not referencing the MANAGEMENTID column. That will cause scans across the partitions. That's a problem.
Let's see, the table I referenced above has a clustered index sperate from the key, which is fine, but then you have a nonclustered index with the clustered index key as a leading column. Again, an index that is unlikely to ever be used because the first column is the only one used to create the histogram in the statistics, the primary driving force behind the choices made by the query optimizer.
My advice, get in a pro. You have a lot of big problems going on here. A few forum posts aren't likely to help much.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - Grant Fritchey - Monday, July 31, 2017 7:59 AM
I second that.
This is the kind of work I do, it takes time to fix a badly messed up system.Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - GilaMonster - Monday, July 31, 2017 8:04 AM
I third it. Gail is top notch. I would love to help to but am currently overwhelmed. Given what the design looks like currently and the fact that you are asking for help there is no hope that you can be successful in making this construct scale by yourself. Note this is not a condemnation, just a VERY experienced observation.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Redesign is highly recommended.
You have 4 different entities mixed in 1 table:
Message Info, Message Content, Activity, Activity Status (which should be actiually Activiuty Log)."
Table Queue should have 3 columns: MesageID, ScheduleTime, Added Time, may be something about Priority as well.
"Sent" is not a table, it's one of the statuses for a MesageID in ActivityLog.No records to be updated, only new ones to be added.
Chaged statuses of messages to be calculated from ActivityLog records by a view.P.S. WEB developers shouldnot be designing databases.
It never wiorks out._____________
Code for TallyGenerator -
Sorry, I'm new DBA and thanks for all information that helping me
deadlock graph -
Once more with feeling...
GilaMonster - Monday, July 31, 2017 5:32 AMSave the deadlock graph as a .xdl file, and attach it. Post scripts of the table and indexes. Post the actual code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - GilaMonster - Tuesday, August 1, 2017 1:35 AM
how to get .xdl file, from where?
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply