Link to home
Start Free TrialLog in
Avatar of sventhan
sventhanFlag for United States of America

asked on

Performance tuning SQL

Experts -

I've a very simple SQL which is performing very poorly.  Most of the time its waiting for Memory/CPU.

Clustered index on PrimaryKey (ID).
Non-Clustered index on NBR

I've nested loop with Non-clustered Seek and Key Lookup on NBR. Is that a normal?

This table has 2 million rows and the rows are not contained in 1 single page.

I can not remove the STARS in the SQL for this time.

Any ideas?

Thanks,

~sve.


ALTER PROCEDURE [dbo].[GetNbr]
	@nbr nvarchar(50)
WITH RECOMPILE	
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT * FROM big_2milrows_table with(nolock)
	WHERE Number=@NBR
	
END

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sventhan

ASKER

Its a varchar(50). Its actually a phone number. Thanks.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks. The problem is something else.
That got fixed.
@ Lowfatspread

It was the issue with data type mismatch.

Thanks.