slow performing query
-
Hi All,
I am seeing a lot of page splits happening on this table.
A lots INSERTS and DELETES happens on this table. Its a very high transactional table. A lot of concurrent operations happens on this table. Is there anything can be done to fix at table level to avoid page splits?
CREATE TABLE [dbo].[Applied_Lock](
[ROWID_LOCK] [nchar](50) NOT NULL,
[CREATE_DATE] [datetime2](7) NOT NULL,
[CREATOR] [nvarchar](50) NOT NULL,
[LAST_UPDATE_DATE] [datetime2](7) NULL,
[UPDATED_BY] [nvarchar](50) NULL,
[ROWID_TABLE] [nchar](50) NOT NULL,
[LOCK_GROUP_STR] [nvarchar](1000) NOT NULL,
[LOCK_EXCLUSIVE_IND] [bigint] NOT NULL,
[JOB_TYPE_STR] [nchar](100) NOT NULL,
[LOCK_QUERY_SQL] [nvarchar](2000) NULL,
[MODULE_NAME] [nvarchar](500) NULL,
[INTERACTION_ID] [nvarchar](100) NULL,
CONSTRAINT [PK_APPLIED_LOCK] PRIMARY KEY NONCLUSTERED
(
[ROWID_LOCK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
GO
Thanks,
Sam
-
Is there no clustered index?
-
Is this related to/a continuation of https://www.sqlservercentral.com/forums/topic/pagesplits-question?
Why don't you have a clustered index?
What column(s) are used in the where clause for doing deletes?
-
Are you actually seeing performance issues as a result of page splits?
- Ed B wrote:
Is there no clustered index?
There is no clustered index. Vendor specific database.
- ratbak wrote:
Are you actually seeing performance issues as a result of page splits?
Yes.
-
What type of values are used in ROWID_LOCK?
Are they numeric strings? alphanumeric?
Are they ordered or random?
- ratbak wrote:
Is this related to/a continuation of https://www.sqlservercentral.com/forums/topic/pagesplits-question?
Why don't you have a clustered index?
What column(s) are used in the where clause for doing deletes?
What difference will it make if I had to create a clustered index?
- ratbak wrote:
What type of values are used in ROWID_LOCK?
Are they numeric strings? alphanumeric? Are they ordered or random?
- vsamantha35 wrote:ratbak wrote:
Is this related to/a continuation of https://www.sqlservercentral.com/forums/topic/pagesplits-question?
Why don't you have a clustered index?
What column(s) are used in the where clause for doing deletes?
What difference will it make if I had to create a clustered index?
CREATE CLUSTERED INDEX [SVR1_13NC4ZI] ON [dbo].[APPLIED_LOCK]
(
[ROWID_TABLE] ASC,
[JOB_TYPE_STR] ASC,
[ROWID_LOCK] 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 = 70)
GO
select [ROWID_TABLE],[JOB_TYPE_STR],[ROWID_LOCK] from [dbo].[APPLIED_LOCK]
-
What do the execution plans look like? What do the queries look like. There's not a 'run faster' switch that can be flipped here. Without some understanding of the behaviors in question, all anyone can do is stab around blindly. I see some indexes on the table. Are they being used by the queries, or are you seeing lots of table scans? Is the foreign key enforced using WITH CHECK? Are the statistics up to date so that row estimates are accurate? You can tell a lot of this stuff by looking at the execution plans. Without them and without the queries, I honestly can't make a suggestion for improving performance.
"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 wrote:
What do the execution plans look like? What do the queries look like. There's not a 'run faster' switch that can be flipped here. Without some understanding of the behaviors in question, all anyone can do is stab around blindly. I see some indexes on the table. Are they being used by the queries, or are you seeing lots of table scans? Is the foreign key enforced using WITH CHECK? Are the statistics up to date so that row estimates are accurate? You can tell a lot of this stuff by looking at the execution plans. Without them and without the queries, I honestly can't make a suggestion for improving performance.
Okay Sir. Please find the information.
SET STATISTICS IO ON
select * from [dbo].[C_REPOS_APPLIED_LOCK];
(2 rows affected)
Table 'C_REPOS_APPLIED_LOCK'. Scan count 1, logical reads 285, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-
uploaded Actual execution plan. PFA zip file.
-
I'm afraid I don't understand. What's that? It's a picture of some data from a query running SELECT * returning 2 rows. I'm confused.
"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 wrote:
I'm afraid I don't understand. What's that? It's a picture of some data from a query running SELECT * returning 2 rows. I'm confused.
Yes sir. It returns 2 rows now. For most of the cases, it will be zero rows. Its a metadata table which will store information of what lock has been taken on which table. This is a very dynamic table. When a lock is acquired on a table, it does insert in the table and as and when the lock is released , corresponding row will be deleted from this table. Everything will be so fast, sometimes we see few records as output and for most of the time, it will show zero rows. Hope I tried to explain to best of my efforts.
Ask me if you need more info.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply