December 20, 2009 at 6:26 am
iam a running a jobsite with over 265000 members, it has a column in which entirecv is stored as varchar(max)
now when i query the table the results get timeout, its not working many a times, simple queries are timedout, already increased timeout, but still it doesnt work
can i have 2 clustered index?
the columns which are used in queries frequently are indexed but non-clustered, anyway that they can be done as clustered for increasing speed?
iam pasting below table structure and indexes, any help appreciated
CREATE TABLE [dbo].[candidatesRegistration](
[CANDIid] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[regdate] [datetime] NOT NULL CONSTRAINT [DF_candidatesRegistration_regdate]
DEFAULT (getdate()),
[Passw] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[emailAddress] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[firstname] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_firstname] DEFAULT (''),
[lastname] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_lastname] DEFAULT (''),
[dateofbirth] [datetime] NOT NULL CONSTRAINT
[DF_candidatesRegistration_dateofbirth] DEFAULT (getdate()),
[gender] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT
[DF_candidatesRegistration_gender] DEFAULT ('M'),
[address1] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_address1] DEFAULT (''),
[address2] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_address2] DEFAULT (''),
[address3] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_address3] DEFAULT (''),
[pincode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_pincode] DEFAULT (''),
[Telephone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_Telephone] DEFAULT (''),
[mobile] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT
[DF_candidatesRegistration_mobile] DEFAULT (''),
[cityid] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT
[DF_candidatesRegistration_cityid] DEFAULT (''),
[cityname] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_cityname] DEFAULT (''),
[stateid] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_stateid] DEFAULT (''),
[statename] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_statename] DEFAULT (''),
[countryname] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_countryname] DEFAULT (''),
[countryid] [int] NOT NULL CONSTRAINT [DF_candidatesRegistration_countryid]
DEFAULT ((1)),
[education] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_education] DEFAULT (''),
[TotalExp] [int] NOT NULL CONSTRAINT [DF_candidatesRegistration_TotalExp]
DEFAULT ((1)),
[CVlocation] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_CVlocation] DEFAULT (''),
[Enddate] [datetime] NOT NULL CONSTRAINT [DF_candidatesRegistration_Enddate]
DEFAULT (getdate()),
[LastLogin] [datetime] NOT NULL CONSTRAINT
[DF_candidatesRegistration_LastLogin] DEFAULT (getdate()),
[imagename] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_imagename] DEFAULT (''),
[imageext] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_imageext] DEFAULT (''),
[emailverified] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_emailverified] DEFAULT ('N'),
[Activated] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_Activated] DEFAULT ('Y'),
[Verified] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_Verified] DEFAULT ('Y'),
[ebayid] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_ebayid] DEFAULT (''),
[blocked] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT
[DF_candidatesRegistration_blocked] DEFAULT ('N'),
[settleabroad] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_settleabroad] DEFAULT (''),
[countrytosettle] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_countrytosettle] DEFAULT (''),
[willingtopay] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_willingtopay] DEFAULT (''),
[monstersign] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_monstersign] DEFAULT (''),
[mth] [int] NOT NULL CONSTRAINT [DF_candidatesRegistration_mth] DEFAULT ((0)),
[profiletype] [int] NOT NULL CONSTRAINT [DF_candidatesRegistration_profiletype]
DEFAULT ((1)),
[Friendshipzone] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_Friendshipzone] DEFAULT ('N'),
[Remail] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT
[DF_candidatesRegistration_Remail] DEFAULT ('N'),
[Purpose] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_Purpose] DEFAULT (''),
[referdby] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_referdby] DEFAULT (''),
[banned] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT
[DF_candidatesRegistration_banned] DEFAULT ('N'),
[showgads] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_showgads] DEFAULT ('Y'),
[ipaddress] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_ipaddress] DEFAULT (''),
[industryid] [int] NOT NULL CONSTRAINT [DF_candidatesRegistration_industryid]
DEFAULT ((1)),
[industryname] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_industryname] DEFAULT (''),
[jobcategoryid] [int] NOT NULL CONSTRAINT
[DF_candidatesRegistration_jobcategoryid] DEFAULT ((1)),
[jobcategoryname] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_jobcategoryname] DEFAULT (''),
[designation] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_designation] DEFAULT (''),
[companyname] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_companyname] DEFAULT (''),
[keywords] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_keywords] DEFAULT (''),
[university] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_university] DEFAULT (''),
[certification] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_certification] DEFAULT (''),
[certifications] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_certifications] DEFAULT (''),
[isbouncing] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_isbouncing] DEFAULT ('N'),
[adminemails] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_adminemails] DEFAULT ('Y'),
[Jobalerts] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_Jobalerts] DEFAULT ('Y'),
[EmailfromEmployers] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_EmailfromEmployers] DEFAULT ('Y'),
[emailsent] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_emailsent] DEFAULT ('N'),
[hide] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT
[DF_candidatesRegistration_hide] DEFAULT ('N'),
[filextension] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_filextension] DEFAULT (''),
[EntireCv] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_EntireCv] DEFAULT (''),
[isonlinenow] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_isonlinenow] DEFAULT ('N'),
[photo] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT
[DF_candidatesRegistration_photo] DEFAULT (''),
[photopassw] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_candidatesRegistration_photopassw] DEFAULT (''),
CONSTRAINT [PK_candidatesRegistration] PRIMARY KEY CLUSTERED
(
[CANDIid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [IX_candidatesRegistration_1] UNIQUE NONCLUSTERED
(
[emailAddress] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
------------------------Table Structure----------------------------
----------------indexes-----------------------------------------
/****** Object: Index [IX_candidatesRegistration] Script Date: 12/20/2009 06:11:25
******/
CREATE NONCLUSTERED INDEX [IX_candidatesRegistration] ON [dbo].[candidatesRegistration]
(
[CANDIid] ASC,
[cityid] ASC,
[countryid] ASC,
[stateid] ASC,
[TotalExp] ASC,
[emailAddress] ASC,
[Passw] ASC,
[industryid] ASC,
[jobcategoryid] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)
ON [PRIMARY]
/****** Object: Index [IX_candidatesRegistration_1] Script Date: 12/20/2009
06:17:58 ******/
ALTER TABLE [dbo].[candidatesRegistration] ADD CONSTRAINT
[IX_candidatesRegistration_1] UNIQUE NONCLUSTERED
(
[emailAddress] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
December 20, 2009 at 10:10 am
You cannot have two clustered indexes.
Can you post the queries that you're having performance problems with and their execution plans as per http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/
Looking at the indexes, they're a little weird.
The first NC index you have is partially redundant with the clustered index.
CREATE NONCLUSTERED INDEX [IX_candidatesRegistration] ON [dbo].[candidatesRegistration](
[CANDIid] ASC,
[cityid] ASC,
[countryid] ASC,
[stateid] ASC,
[TotalExp] ASC,
[emailAddress] ASC,
[Passw] ASC,
[industryid] ASC,
[jobcategoryid] ASC
)
If there's a search by candID, SQL will very likely just do a clustered index seek because it's about the most optimal way of getting the row. It's guaranteed to return one row as it's the pk. That NC index above cannot be used for a seek unless the query filters by candID. Hence, that very wide nonclustered index is probably not getting used all that often.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 23, 2009 at 6:46 pm
try an index defrag.
Also when you do your simple queries do the following:
check the db for locks (sp_who)
include appropriate filters on argable fields
return limited results
select top 1 id from table where id = (value)
you can also create additional nonclustered indexes (as long as they are not duplicates)
hope this helps
December 24, 2009 at 7:51 am
Freeman
You really meant the defrag index - http://msdn.microsoft.com/en-us/library/ms177571.aspx
Because after seeing your post, I checked in the above link, but they warned us to not use DBCC INDEXDEFRAG.
Kindly clarify.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 24, 2009 at 8:22 am
cthariharan (12/24/2009)
FreemanYou really meant the defrag index - http://msdn.microsoft.com/en-us/library/ms177571.aspx
Because after seeing your post, I checked in the above link, but they warned us to not use DBCC INDEXDEFRAG.
Kindly clarify.
You can still use DBCC INDEXDEFRAG on sql 2008, though it will be removed for future versions so it would be best to use
Alter Index instead to ensure your code will work on future releases of SQL server.
http://msdn.microsoft.com/en-us/library/ms188388.aspx"> http://msdn.microsoft.com/en-us/library/ms188388.aspx
December 25, 2009 at 8:30 am
Thank you.
So you want me to use Reorganize instead of DBCC INDEXDEFRAG. Am i right sir ?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 29, 2009 at 4:47 am
Hello
you can create a non clustered on the column which is mainly used in where clause with fill factor of 90%
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply