September 21, 2020 at 5:50 am
Hi All,
I have a SELECT query which is taking more 8 mins. Can you please help me in making the query run faster (if possible) . There is no blocking . Seeing PAGEIOLATCH_SH waittype for most time. The table is a clustered index table. There is no non-clustered idx on mule_batch_id column.
Query:
use <dbname>
go
set statistics io on
go
set statistics time on
go
select TOP 1 * from <dbname>.dbo.<tname> where mule_batch_id='EAIf200dg373B1'
go
-- output
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 5144 ms.
(1 row(s) affected)
Table 'tname'. Scan count 1, logical reads 2043149, physical reads 1, read-ahead reads 1719247, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 13672 ms, elapsed time = 356953 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
More details
=============
Table size : 15GB
Database size : 3.5TB
Memory : 32 GB
max server memory : 27 GB
wait_info : PAGEIOLATCH_SH
Note: Its a test server
Please attached Actual Execution plan and table structure
September 21, 2020 at 6:08 am
According to your table structure, there is no index on [MULE_BATCH_ID]. This means that SQL has to do a table scan to find the record.
You are doing a select *, so a covering index is simply going to duplicate the entire table.
At a minimum, you can try an index on [MULE_BATCH_ID]
CREATE NONCLUSTERED INDEX IX_tname_MuleBatchID
ON dbo.tname (MULE_BATCH_ID)
INCLUDE (ID);
[MULE_BATCH_ID] is also defined as NULLable, so it is not an option for a CLUSTERED index.
September 21, 2020 at 6:09 am
There is no non-clustered idx on mule_batch_id column.
It seems you already know what to do.
At least you identified the cause of the problem correctly.
_____________
Code for TallyGenerator
September 21, 2020 at 8:17 am
According to your table structure, there is no index on [MULE_BATCH_ID]. This means that SQL has to do a table scan to find the record.
You are doing a select *, so a covering index is simply going to duplicate the entire table.
At a minimum, you can try an index on [MULE_BATCH_ID]
CREATE NONCLUSTERED INDEX IX_tname_MuleBatchID
ON dbo.tname (MULE_BATCH_ID)
INCLUDE (ID);[MULE_BATCH_ID] is also defined as NULLable, so it is not an option for a CLUSTERED index.
Hi ,
I don't think we need to INCLUDE "ID" column because whenever we create a non-clustered idx , the clustering key (i.e. ID in this case) will be part of the non-clustered index.
Thanks,
Sam
September 21, 2020 at 1:56 pm
oh by the way, if you know [MULE_BATCH_ID] should be unique in that table, create a unique constraint or index.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 21, 2020 at 2:47 pm
DesNorton wrote:According to your table structure, there is no index on [MULE_BATCH_ID]. This means that SQL has to do a table scan to find the record.
You are doing a select *, so a covering index is simply going to duplicate the entire table.
At a minimum, you can try an index on [MULE_BATCH_ID]
CREATE NONCLUSTERED INDEX IX_tname_MuleBatchID
ON dbo.tname (MULE_BATCH_ID)
INCLUDE (ID);[MULE_BATCH_ID] is also defined as NULLable, so it is not an option for a CLUSTERED index.
Hi ,
I don't think we need to INCLUDE "ID" column because whenever we create a non-clustered idx , the clustering key (i.e. ID in this case) will be part of the non-clustered index.
Thanks,
Sam
You should still INCLUDE it, not just for documentary purposes, but in case the clustered index key were to change.
Btw, that table looks like it had no logical design done at all, resulting in an unnormalized mishmash. You really should consider normalizing that data structure.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 22, 2020 at 2:47 pm
vsamantha35 wrote:DesNorton wrote:According to your table structure, there is no index on [MULE_BATCH_ID]. This means that SQL has to do a table scan to find the record.
You are doing a select *, so a covering index is simply going to duplicate the entire table.
At a minimum, you can try an index on [MULE_BATCH_ID]
CREATE NONCLUSTERED INDEX IX_tname_MuleBatchID
ON dbo.tname (MULE_BATCH_ID)
INCLUDE (ID);[MULE_BATCH_ID] is also defined as NULLable, so it is not an option for a CLUSTERED index.
Hi ,
I don't think we need to INCLUDE "ID" column because whenever we create a non-clustered idx , the clustering key (i.e. ID in this case) will be part of the non-clustered index.
Thanks,
Sam
You should still INCLUDE it, not just for documentary purposes, but in case the clustered index key were to change.
Btw, that table looks like it had no logical design done at all, resulting in an unnormalized mishmash. You really should consider normalizing that data structure.
Scott,
We couldn't do much about the database design as it is a vendor specific database.
We had a meeting with the app team and they had to archive some years of data. They will do that and let us know.
Later , we will try to add this additional non-clustered idx on mule_batch_id.
Thanks everyone for pitching in and sharing your thoughts.
-Sam
September 22, 2020 at 3:38 pm
Quote: "We couldn't do much about the database design as it is a vendor specific database."
Understood. Lol, the only thing potentially worse than a "programmer (non)'designed' table" is a "vendor (non)'designed' table".
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 23, 2020 at 1:34 pm
It's allowed to have a clustered-key as nullable.
CREATE TABLE dbo.t
(
ID1INTNOT NULL
CONSTRAINT PK_t PRIMARY KEY NONCLUSTERED,
ID2INTNULL
INDEX cl_t_ID2 CLUSTERED,
TxtVARCHAR(20)NOT NULL
)
GO
INSERT INTO dbo.t (ID1, ID2, Txt) VALUES
(1, 11, 'aaa'),
(2, 12, 'bbb'),
(3, 13, 'ccc');
INSERT INTO dbo.t (ID1, ID2, Txt) VALUES
(4, NULL, 'ddd'),
(5, NULL, 'eee'),
(6, NULL, 'fff');
GO
SELECT *
FROM dbo.t;
September 23, 2020 at 2:35 pm
Just keep in min , SQLServer "uniquifies" every clustered index key !
Clustered Index Uniquifier Existence and Size
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 23, 2020 at 3:27 pm
An "uniquifies" is only an INT (4 byte). It can be a better solution for a table with a lot of NVARCHAR-columns instead of a nonclustered index, if many rows are returned and not only TOP (1). If other nonclustered index are created on this NVARCHAR columns the "uniquifies" don't matter.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply