May 10, 2015 at 10:07 pm
Hi All,
I have a query that I want to tune , here it is :
SELECT DISTINCT "project_A"."created_dttm" AS "Created_Datetime",
"project_A"."proj_task_id" AS "Project_Task_Id",
"project_A"."text" AS "Text"
FROM
(
SELECT "P_T_id",
"created_dttm",
"created_id",
"proj_task_id",
"project_id",
"text_id",
"text_line_code",
"billing_status",
"internal_use_only",
CAST( "text" AS VARCHAR( 2000 ) ) AS "text"
FROM "T"."dbo"."P_T"
WHERE "text_line" = 'ABCD'
)
"project_A"
I have added index :
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname, >] ON [dbo].[P_T] ([text_line_code]) INCLUDE ([created_dttm],[proj_task_id])
But it doest use that index . it uses [P_T].[P_T_PK] instead ( in the execution plan shows : Clustered index scan )
FYi : Table [P_T] has 197075 rows
What should I do ?
Please kindly advice ….
Thanks so much
May 11, 2015 at 2:08 am
-- Write the query the simplest way. It doesn't get much simpler than this:
SELECT DISTINCT
created_dttm AS Created_Datetime,
proj_task_id AS Project_Task_Id,
CAST([text] AS VARCHAR(2000)) AS [text]
FROM dbo.P_T
WHERE text_line = 'ABCD'
-- The query would probably benefit from an index defined as
CREATE INDEX ix_Yourname ON dbo.P_T (text_line) INCLUDE (created_dttm, proj_task_id, [text])
-- HOWEVER, if the column [text] is TEXT datatype you won't be able to INCLUDE it.
-- If you can include it, it's only going to work well if it's sparsely populated.
-- If you can't INCLUDE it, then don't include any of the other columns either since
-- SQL Server will have to visit the clustered index anyway.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 11, 2015 at 2:15 am
First, when examining whether or not a missing index suggestion is good, please, change the name from [<Name of Missing Index, sysname, >] to anything else.
Next, while that may have been the suggested missing index, your SELECT list has all the columns, but the INCLUDE list only covers a couple. That means that index will not be covering for this query which is why it's choosing to go back and scan the clustered index index.
I think the suggested query and index above are probably good. If not, please post the execution plan so we can see what's being accessed and why.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply