September 26, 2011 at 7:18 am
What type of performance improvement could i expect from a two node cluster, running sql server 2008 r2 x64 enterprise, over just a single server.
Both servers are dual Qual core with 32 GB ram, on a windows server 2008 r2 x64 ent o/s. Total sas drives, 26.
Thanks
September 26, 2011 at 7:21 am
None whatsoever.
Clustering is a high-availability solution, it's not for performance and it's no scale-out. With a 2-node cluster, SQL will be running on one of the cluster nodes and the other one will be idle
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
September 26, 2011 at 7:21 am
What do you have now?
Is the performance acceptable?
What's the growth you have to account for & over what period?
How long is that server supposed to last?
September 26, 2011 at 8:07 am
I have a variety of servers running DC's and Exchange 2010, 2007, 2003, CRM 2011, and two servers running seperate versions of SQL Server 2008 for different applications.
I have one application that has to search 4 Million documents in a FTI, and the performance is too slow for me.
Typically about 11 to 13 seconds to run the sql query, right after a reindex and reorg.
I have also run the execution plan to make sure i am not missing something.
I am trying to find ways to improve performance, i thought about partitioning, but someone said it would not help much.
September 26, 2011 at 8:17 am
Hardware is the last thing that you should consider when trying to fix performance. It often has the least effects and I've seen a couple of cases where the hardware upgrade severely degraded the performance.
Tune the queries, tune the indexes
or
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
or
Hire a consultant to do a performance review.
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
September 26, 2011 at 8:18 am
FTI is not my forte, and unless Gail studied it last week, it's not hers either afaik.
Did the other thread end or are you still getting replies?
September 26, 2011 at 8:26 am
isuckatsql (9/26/2011)
What type of performance improvement could i expect from a two node cluster, running sql server 2008 r2 x64 enterprise, over just a single server.
As Gail said, absolutely none. Windows Failover clustering is exactly that, failover, it does not load balance.
If this is your intended specification
isuckatsql (9/26/2011)
Both servers are dual Qual core with 32 GB ram, on a windows server 2008 r2 x64 ent o/s. Total sas drives, 26.Thanks
Look carefully at how you configure these
Total sas drives, 26.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 26, 2011 at 8:34 am
I am using RAID 10 for the data files
RAID1 for program files
RAID1 for log files
RAID0 for TempDB files
Thanks for the feedback everyone!
September 26, 2011 at 8:47 am
Clustering is all about availability in SQL Server and has nothing at all to do with performance.
If your query is slow, why is it slow? What does the execution plan look like? That will tell you what you might be able to do to improve performance.
What are the most common wait states on your server? That will start you down the path to identify what your sources of contention are. You can throw money at these problems, buying more and bigger hardware, but the benefits are usually incremental.
"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
September 26, 2011 at 10:37 am
USE [JobPortal]
GO
/****** Object: StoredProcedure [dbo].[GetAllResumesSearchedDynamicQuery2FTI9-26-11] Script Date: 09/26/2011 12:29:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--
-- =============================================
ALTER PROCEDURE [dbo].[GetAllResumesSearchedDynamicQuery2FTI9-26-11]
-- Add the parameters for the stored procedure here
@Name varchar(250),
@Name1 varchar(250),
@Date int,
@Stateid int,
@CityId int,
@Industryid int,
@Degree int,
@Skillsid int,
@FutureSkillsid int,
@ExpectedSalary int,
@ExpectedMaxSalary int,
@Position varchar(250),
@Phrase int,
@fromRec int,
@toRec int
AS
declare @Query as varchar(8000)
set @Query = 'Begin
WITH Res AS
(
select p.id as Id,p.regionid,p.cityid,p.countryid,
p.IndustryID,
p.AcademicExperienceTypeID,
case when len(w.position) < 30 then w.position
else
substring(w.position,1,30) + ''...'' end
as Position,
p.InputDate,
p.SalaryMinID,
p.SalaryMaxID,
p.HomePhone,
p.CellPhone,
p.WorkPhone,
p.PrimaryEmailAddress,
p.SecondaryEmailAddress,
isnull((select [Name] + '' '' from salutationtypes st
where p.SalutationId=st.id),'''') + isnull(p.FirstName,'''') + '' '' + isnull(p.MiddleName + '' '','''') + isnull(p.LastName,'''') as UserName,
ROW_NUMBER() OVER (ORDER BY p.id DESC) AS RowNumber
from profiles p left outer join workexperiences w on w.ProfileId=p.id
left outer join documents dc on p.id = dc.profileid ********** THIS JOIN IS KILLING ME ******
where p.allowrecruiters=1 and dc.documentstatusid=1 '
If (@Name <> '')
Begin
set @Query = @Query + ' and contains(dc.doccontent,''' + @Name1 + ''') '
End
if (@Position <> '')
Begin
if (@Phrase = 1)
Begin
set @Query = @Query + ' and contains(w.position,''' + @Position + ''') '
end
else if (@Phrase = 2)
Begin
set @Query = @Query + ' and contains(w.position,''' + @Position + ''') '
end
else if (@Phrase = 3)
Begin
set @Query = @Query + ' and contains(w.position,''' + @Position + ''') '
end
else if (@Phrase = 4)
Begin
set @Query = @Query + ' and w.position = ''' + cast(@Position as varchar(250)) + ''''
end
End
set @Query = @Query + '),
Resumes AS
(
SELECT p.id,
p.UserName,
InputDate,
substring(cast(InputDate as varchar(20)),1,12) as newdate,
isnull((select c.[Name] + '', '' from cities c where c.id=p.cityid),'''') +
isnull((select r.[abbreviatedName] from regions r where r.id=p.regionid),'''') as Location,
(select sum(endyear - startyear) from workexperiences w where w.ProfileId=p.id) as Experience,
case when len(p.Position) < 30 then p.Position
else
substring(p.Position,1,30) + ''...'' end
as Position,
Position as Title,
(select w.Name from AcademicExperienceTypes w where w.Id=p.AcademicExperienceTypeID) as Degree,
cast((select s.value from salaries s where p.SalaryMinID = s.id) as int) as Salary,
cast((select s.value from salaries s where p.SalaryMaxID = s.id) as int) as MaxSalary,
(select i.name from industries i where p.IndustryID = i.id) as Industry,
p.HomePhone,
p.CellPhone,
p.WorkPhone,
p.PrimaryEmailAddress,
p.SecondaryEmailAddress,
ROW_NUMBER() OVER (ORDER BY p.id DESC) AS RowNumber
FROM Res p
WHERE 1=1 '
if (@Stateid <>0)
Begin
set @Query = @Query + ' and p.regionid =' + cast(@Stateid as varchar(10))
End
if (@CityId <>0)
Begin
set @Query = @Query + ' and p.cityid =' + cast(@CityId as varchar(10))
End
if (@Industryid <>0)
Begin
set @Query = @Query + ' and p.IndustryId =' + cast(@Industryid as varchar(10))
End
if (@Degree <> 0)
Begin
set @Query = @Query + ' and p.AcademicExperienceTypeID =' + cast(@Degree as varchar(10))
End
if (@Date <> 0)
Begin
set @Query = @Query + ' and InputDate >=''' + cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20)) + ''''
End
if (@ExpectedSalary <> 0)
Begin
set @Query = @Query + ' and p.SalaryMinID >= ' + cast(@ExpectedSalary as varchar(10))
End
if (@ExpectedMaxSalary <> 0)
Begin
set @Query = @Query + ' and p.SalaryMaxID <= ' + cast(@ExpectedMaxSalary as varchar(10))
End
set @Query = @Query + ')
select
id,
case when len(UserName) < 40 then
UserName
else
substring(UserName,1,40) + ''...'' end
as UserName,
InputDate,
newdate,
Location,
Experience,
Position,
Title,
Degree,
Salary,
MaxSalary,
Industry,
HomePhone,
CellPhone,
WorkPhone,
PrimaryEmailAddress,
SecondaryEmailAddress,
RowNumber,
(select max(rownumber) from Resumes) as TotalCount
from Resumes
WHERE RowNumber BETWEEN ' + cast(@fromRec as varchar(10)) + ' AND ' + cast(@toRec as varchar(10)) +
'
ORDER BY RowNumber ASC
End
'
exec (@Query)
left outer join documents dc on p.id = dc.profileid
This join is killing me, without it the query runs in one second instead of 11 seconds!
I have uploaded the Actual Execuation Plan.
Thanks
September 26, 2011 at 10:50 am
Try some temp tables. Rather than a CTE for the Resumes, insert that into a temp table, index it and then select out just the ones you want. Also try limiting the row count being inserted into the temp table. You're returning almost 700000 rows from the full text search and you only want 9 of them in the end.
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
September 26, 2011 at 10:56 am
GilaMonster (9/26/2011)
Try some temp tables. Rather than a CTE for the Resumes, insert that into a temp table, index it and then select out just the ones you want. Also try limiting the row count being inserted into the temp table. You're returning almost 700000 rows from the full text search and you only want 9 of them in the end.
Thanks!
September 26, 2011 at 11:01 am
I'm going to say something that looks compeltely useless until you get it.
You say that join is taking 90% of the query? Then fine, whack it.
BTW left join + where lj.col = 1 => inner join.
As gail said, remove the CTE and use temp tables.
There must be a way to just save the first 100 or 1000 results and then apply the final filter later assuming you really need it (I've had that wrong assumption in the past).
September 26, 2011 at 12:10 pm
I'm not quite sure if this is what Gail meant, but i have a couple of errors and i'm not sure how to fix them.
Msg 156, Level 15, State 1, Line 39
Incorrect syntax near the keyword 'and'.
Msg 156, Level 15, State 1, Line 43
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 66
Incorrect syntax near ')'.
USE [JobPortal]
GO
/****** Object: StoredProcedure [dbo].[GetAllResumesSearchedDynamicQuery2FTI9-26-11] Script Date: 09/26/2011 14:05:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--
-- =============================================
ALTER PROCEDURE [dbo].[GetAllResumesSearchedDynamicQuery2FTI9-26-11]
-- Add the parameters for the stored procedure here
@Name varchar(250),
@Name1 varchar(250),
@Date int,
@Stateid int,
@CityId int,
@Industryid int,
@Degree int,
@Skillsid int,
@FutureSkillsid int,
@ExpectedSalary int,
@ExpectedMaxSalary int,
@Position varchar(250),
@Phrase int,
@fromRec int,
@toRec int
AS
declare @Query as varchar(8000)
set @Query = 'Begin
select p.id as Id,p.regionid,p.cityid,p.countryid,
p.IndustryID,
p.AcademicExperienceTypeID,
case when len(w.position) < 30 then w.position
else
substring(w.position,1,30) + ''...'' end
as Position,
p.InputDate,
p.SalaryMinID,
p.SalaryMaxID,
p.HomePhone,
p.CellPhone,
p.WorkPhone,
p.PrimaryEmailAddress,
p.SecondaryEmailAddress,
isnull((select [Name] + '' '' from salutationtypes st
where p.SalutationId=st.id),'''') + isnull(p.FirstName,'''') + '' '' + isnull(p.MiddleName + '' '','''') + isnull(p.LastName,'''') as UserName,
ROW_NUMBER() OVER (ORDER BY p.id DESC) AS RowNumber
from profiles p left outer join workexperiences w on w.ProfileId=p.id
inner join documents dc on p.id = dc.profileid
where p.allowrecruiters=1 and dc.documentstatusid=1
insert into Res
(
id,p.regionid,p.cityid.p.countryid,p.industryID,
p.AcademicExperienceTypeID,
Position,
p.InputDate,
p.SalaryMinID,
p.SalaryMaxID,
p.HomePhone,
p.CellPhone,
p.WorkPhone,
p.PrimaryEmailAddress,
p.SecondaryEmailAddress,
UserName,
RowNumber)
'
If (@Name <> '')
Begin
set @Query = @Query + ' and contains(dc.doccontent,''' + @Name1 + ''') '
End
if (@Position <> '')
Begin
if (@Phrase = 1)
Begin
set @Query = @Query + ' and contains(w.position,''' + @Position + ''') '
end
else if (@Phrase = 2)
Begin
set @Query = @Query + ' and contains(w.position,''' + @Position + ''') '
end
else if (@Phrase = 3)
Begin
set @Query = @Query + ' and contains(w.position,''' + @Position + ''') '
end
else if (@Phrase = 4)
Begin
set @Query = @Query + ' and w.position = ''' + cast(@Position as varchar(250)) + ''''
end
End
set @Query = @Query + '),
insert into Resumes
(
SELECT p.id,
p.UserName,
InputDate,
substring(cast(InputDate as varchar(20)),1,12) as newdate,
isnull((select c.[Name] + '', '' from cities c where c.id=p.cityid),'''') +
isnull((select r.[abbreviatedName] from regions r where r.id=p.regionid),'''') as Location,
(select sum(endyear - startyear) from workexperiences w where w.ProfileId=p.id) as Experience,
case when len(p.Position) < 30 then p.Position
else
substring(p.Position,1,30) + ''...'' end
as Position,
Position as Title,
(select w.Name from AcademicExperienceTypes w where w.Id=p.AcademicExperienceTypeID) as Degree,
cast((select s.value from salaries s where p.SalaryMinID = s.id) as int) as Salary,
cast((select s.value from salaries s where p.SalaryMaxID = s.id) as int) as MaxSalary,
(select i.name from industries i where p.IndustryID = i.id) as Industry,
p.HomePhone,
p.CellPhone,
p.WorkPhone,
p.PrimaryEmailAddress,
p.SecondaryEmailAddress,
ROW_NUMBER() OVER (ORDER BY p.id DESC) AS RowNumber
FROM Res p
WHERE 1=1 '
if (@Stateid <>0)
Begin
set @Query = @Query + ' and p.regionid =' + cast(@Stateid as varchar(10))
End
if (@CityId <>0)
Begin
set @Query = @Query + ' and p.cityid =' + cast(@CityId as varchar(10))
End
if (@Industryid <>0)
Begin
set @Query = @Query + ' and p.IndustryId =' + cast(@Industryid as varchar(10))
End
if (@Degree <> 0)
Begin
set @Query = @Query + ' and p.AcademicExperienceTypeID =' + cast(@Degree as varchar(10))
End
if (@Date <> 0)
Begin
set @Query = @Query + ' and InputDate >=''' + cast(((select dateadd(d,@Date,GETDATE()))) as varchar(20)) + ''''
End
if (@ExpectedSalary <> 0)
Begin
set @Query = @Query + ' and p.SalaryMinID >= ' + cast(@ExpectedSalary as varchar(10))
End
if (@ExpectedMaxSalary <> 0)
Begin
set @Query = @Query + ' and p.SalaryMaxID <= ' + cast(@ExpectedMaxSalary as varchar(10))
End
set @Query = @Query + ')
select
id,
case when len(UserName) < 40 then
UserName
else
substring(UserName,1,40) + ''...'' end
as UserName,
InputDate,
newdate,
Location,
Experience,
Position,
Title,
Degree,
Salary,
MaxSalary,
Industry,
HomePhone,
CellPhone,
WorkPhone,
PrimaryEmailAddress,
SecondaryEmailAddress,
RowNumber,
(select max(rownumber) from Resumes) as TotalCount
from Resumes
WHERE RowNumber BETWEEN ' + cast(@fromRec as varchar(10)) + ' AND ' + cast(@toRec as varchar(10)) +
'
ORDER BY RowNumber ASC
End
'
exec (@Query)
September 26, 2011 at 12:37 pm
Well, syntax errors aside, I said temp tables and you're using permanent tables. That's going to give you some fun race-conditions and incorrect results if 2 people run this at the same time.
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
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply