My search is very slow in sql server

Question

I have a table as you can see in my sql server :

CREATE TABLE [dbo].[Cars](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [VIN] [nvarchar](max) NULL,
    [ChassisNumber] [nvarchar](max) NULL,
    [PlaqueCoded] [nvarchar](max) NULL,
    [EngineNumber] [nvarchar](max) NULL,
    [SystemType] [nvarchar](max) NULL,
    [CarType] [nvarchar](max) NULL,
    [CarTipe] [nvarchar](max) NULL,
    [FuelType] [nvarchar](max) NULL,
    [FuelSystem] [nvarchar](max) NULL,
    [Model] [int] NULL,
    [Color] [nvarchar](max) NULL,
    [SubmitDatetime] [datetime] NOT NULL,
    [ExpireDatetime] [datetime] NOT NULL,
    [ReferenceOrganization] [nvarchar](max) NULL,
    [ReferenceId] [nvarchar](max) NULL,
    [Comment] [nvarchar](max) NULL,
 CONSTRAINT [PK_Cars] PRIMARY KEY NONCLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [FullOrderDateRangePScheme]([Model])

I have 16000000 records inside this table .So as you know this table stores all information about cars.so i decided to create filegroup based on car model ,it means i saved same model of cars inside one filegroup as you can see :

 FILEGROUP [Filegroup_1395] 
( NAME = N'data_1395', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\data_1395.ndf' , SIZE = 10240KB , MAXSIZE = 10240000KB , FILEGROWTH = 512000KB ), 
 FILEGROUP [Filegroup_1396] 
( NAME = N'data_1396', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\data_1396.ndf' , SIZE = 10240KB , MAXSIZE = 10240000KB , FILEGROWTH = 512000KB ), 
 FILEGROUP [Filegroup_1397] 
( NAME = N'data_1397', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\data_1397.ndf' , SIZE = 566976KB , MAXSIZE = 10240000KB , FILEGROWTH = 512000KB )

So i create this function to

CREATE PARTITION FUNCTION [FullOrderDateKeyRangePFN](int) AS RANGE LEFT FOR VALUES ( 1395, 1396, 1397)
GO
/****** Object:  PartitionScheme [FullOrderDateRangePScheme]    Script Date: 8/8/2017 11:51:38 PM ******/
CREATE PARTITION SCHEME [FullOrderDateRangePScheme] AS PARTITION [FullOrderDateKeyRangePFN] TO ( [Filegroup_1395], [Filegroup_1396], [Filegroup_1397])
GO

As a note the VIN is unique .my main search is vin.i create these indexes on my table :

CREATE CLUSTERED INDEX [ClusteredIndex-20170808-232559] ON [dbo].[Cars]
(
    [Model] 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 [FullOrderDateRangePScheme]([Model])
GO
SET ANSI_PADDING ON
GO

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20170808-232524] ON [dbo].[Cars]
(
    [Id] ASC
)
INCLUDE (   [VIN]) 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 [FullOrderDateRangePScheme]([Model])
GO

my query is this :

select * from cars where vin='IRFC1374GH7162K'

My questions is :
How can i do more thing to better performance?will my indexes help ?

I am so new in tuning

i recommend taking a look at this link(https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to imporve the question — TheGameiswar, Aug 08 '17 at 07:37
1. Please post details of your hardware setup. 2. what is the point of partitioning if all data is stored on the same drive. On C drive!!!!! I also assume that you don't delete from cars. 3. Generally you optimise for specific queries. At the very least you need to tell us what columns are being searched typically. — Alex, Aug 08 '17 at 09:12
@TheGameiswar select * from cars where vin='IRFC1374GH7162K' — Ehsan Akbar, Aug 08 '17 at 11:11

TheGameiswar · Accepted Answer · 2017-08-08T11:24:05.487

1

your query is this

select * from cars where vin='IRFC1374GH7162K'

your current indexes are not at all helpfull for your query..

so to make your query perform better,i recommend..try adding a non clustered index

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20170808-232524] ON [dbo].[Cars]
(
    [vin] ASC
)
INCLUDE (  remaining columns)

this will be a problem ,since maximum index key size is 900 bytes if you are using a version prior to sql2016 and limit is 1700 bytes for versions greater than equal to 2016..

Also i see,your search column is not that huge(IRFC1374GH7162K),so i recommend adjusting to nvarchar(100).ALso all the columns are needed since you are doing a select *

With this, you will have an overhead of maintainig a seperate structure...but this helps your query..if you don't require all the columns there will be a less overhead

edited Aug 08 '17 at 11:24

answered Aug 08 '17 at 11:15

TheGameiswar

27,855
8
56
94

thank you .as a last question i want to know it is better to have a filegroup with files or have a file group for each file ? – Ehsan Akbar Aug 08 '17 at 11:22
File group with files or each file group for files will not be of much help ,if you don't place them on seperate disks.Placing on seperate disks helps in faster backup.This is as per my understanding – TheGameiswar Aug 08 '17 at 11:26
you also need to follow best practices like ,data and log files on seperate drives and tempdb disk configurations.IF you see a lot of IO done for a single table,then creating a new file group and placing it on seperate disk may help – TheGameiswar Aug 08 '17 at 11:27

My search is very slow in sql server

1 Answers1