Should I Re-design table?
- Sergiy - Monday, July 31, 2017 10:43 PM
tbmessagereducequeue link with tbmessagereduceinfo
the data comes to tbmessagereducequeue and I have to move into tbmessagereducesent for 3 days and then sent to Table history, so the logic is in SP that I posted - chonlatis.je - Tuesday, August 1, 2017 1:40 AM
Right click the deadlock graph -> save as.
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 3:56 AM
-
And the rest of the stuff?
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 5:03 AM
What do u mean?
- chonlatis.je - Tuesday, August 1, 2017 5:41 AM
All the other places where you've been posting pictures instead of supplying code.
To really dig into any kind of question of this sort, you have to have the code. You can't simply work off of a few facts and a couple of pictures. It all comes down to the code. That means not the index names, but the index definition as defined by T-SQL. We inferred and guessed at things based on the names, but how accurate were those guesses? I sure don't know. Not without seeing the code.
An additional request, if you really want to try to pursue answers to these questions through forums (still a bad idea considering the level of your problems), then we're going to need execution plans for the queries involved too. There's no other way to see which indexes were used and how they were used, if they were used, the joins, etc., without the execution plans.
I recognize that this seems like a lot of work. We're trying to help. However, we're hamstrung in the degree to which we can help by limiting the information you give us. We're left to guessing, and that's unlikely to be helpful. It might even be harmful.
"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 - chonlatis.je - Tuesday, August 1, 2017 5:41 AMGilaMonster - Tuesday, August 1, 2017 1:35 AM
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 6:21 AM
-
Wow, like blood from a stone here.
Ok, we now have 3 table definitions (no indexes, no constraints), the procedure and the deadlock graph.
Can we maybe get the index and constraint definitions now?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 - Wednesday, August 2, 2017 1:45 AM
sorry, I thought it was there
-
You have no primary keys, no foreign keys on any of these tables?
Is TBMESSAGEREDUCESENT partitioned? If so, what's the partition function and partition column?
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 - Wednesday, August 2, 2017 1:59 AM
This table is stored in different Database but same instance (I don't know why they separate for this)
ManagementID is a partition column and no partition function for these 3 tables , TBmessagereduceinfo doesn't have a primary key -
Oh for.....
PICTURES ARE NOT USEFUL!!!!!!!!!!!!If that table is partitioned, it must have a partition function (that's what makes it partitioned). If it's not partitioned, then there can't be a partition column, because it's not partitioned.
So, again, are there any foreign keys on these tables? If so, what are their definitions? What are the primary key definitions?
Are any of them partitioned, if so what are the partition function and column?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 - Wednesday, August 2, 2017 2:36 AM
No foreign key in this database they linked with idmessage and idmessageinfo, I don't know why is no primary key on tbmessagereduceinfo and only managementid is partitioned but another table they used this partition function (convert(tinyint,right([msisdn],(1)))) maybe they forgot to put it on these table
- chonlatis.je - Wednesday, August 2, 2017 3:55 AMNo foreign key in this database they linked with idmessage and idmessageinfo, I don't know why is no primary key on tbmessagereduceinfo and only managementid is partitioned but another table they used this partition function (convert(tinyint,right([msisdn],(1)))) maybe they forgot to put it on these table
It is possible that your SSMS settings aren't set to script the indexes, partition schemes and suchlike.
Within SSMS - Tools/Options/Scripting (on my 2016 copy) brings up the window shown below, where you can check it scripts the relevant components:
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply