How to organize the indexes?

Good day to all!
Read the documentation, but I do not understand - whether I did right or not, and whether you can do something else.
There is a table:
CREATE TABLE [Application].[PA] (
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [CRId] [int] NOT NULL,
 [AGId] [int] NOT NULL,
 [PPId] [int] NOT NULL,
 CONSTRAINT [PK_PA] PRIMARY KEY CLUSTERED 
([Id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [Application].[PA] WITH CHECK ADD CONSTRAINT [FK_PA_PAG] FOREIGN KEY([AGId])
REFERENCES [Application].[PAG] ([Id])
GO
ALTER TABLE [Application].[PA] CHECK CONSTRAINT [FK_PA_PAG]
GO
ALTER TABLE [Application].[PA] WITH CHECK ADD CONSTRAINT [FK_PA_PP] FOREIGN KEY([PPId])
REFERENCES [Application].[PP] ([Id])
GO
ALTER TABLE [Application].[PA] CHECK CONSTRAINT [FK_PA_PP]
GO


She has a nonclustered index
CREATE NONCLUSTERED INDEX [PA_NC_Index] ON [Application].[PA]
(
 [AGId] ASC,
 [PPId] 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) ON [PRIMARY]
GO


[CRId] is a record, which eventually need to to understand which strings need to select from another table.
[AGId] the main object unique
[PPId] - an optional object inside the main, but is unique among the main objects

Treatment mostly happens this way:
select [CRId] from [Application].[PA] where [PA].[AGId]=1 and [PA].[PPId]=21


The table over 1 million records, and began to be troubled by the question whether the organized storage and retrieval? Or you can do something to change / improve?

If make a connection to this table, you immediately get a strong brake (2 seconds, direct the sample to 0.02 sec), for example
select top(10) Id 
from [Application].T inner join
[Application].[PA] on T. id=PA.[CRId]
where [PA].[AGId]=1 and [PA].[PPId]=21
July 2nd 19 at 13:59
1 answer
July 2nd 19 at 14:01
Try this:
The key to [PA].[AGId] + [PA].[PPId] (unique clusters)
The Key Is [Application].id. (unique clusters)

Do 2 queries:
select [CRId] from [Application].[PA] where [PA].[AGId]=1 and [PA].[PPId]=21
select [CRId] from [Application].[T] where id IN (received ID in the query before)

Sometimes we must refuse to JOIN.
But first you need to decide what brakes.
Even with the query plan, of course.
Alex, please explain in more detail.
You write
The key to [PA].[AGId] + [PA].[PPId]
The Key Is [Application].id.
but if I understand correctly, the way it is now - Id is a clustered index and two nonclustered indexes. Right? Or on the basis of the above statements is not true?

the sample id in a separate request - I use temporary tables, but it seems you can use table variables - which is better? And are there any other ways to pass IN the list id? - Kenton.Cronin commented on July 2nd 19 at 14:04

Find more questions by tags SQL Server