May 2, 2017 at 10:58 am
Got a new SQL Server, running SQL 2016 on Windows Server 2016. This VMWare at our hosting site.
Server properties show 16 G. Do I need to configure anything in SQL 2016 to make sure SQL is accessing RAM ?
My Table being searched is 500k rows, and the execution plan looks efficient from what I can tell. All the columns involved either are full-text index, or regular index.
Not sure how to troubleshoot this, but 45 seconds run time is crazy slow.
May 2, 2017 at 12:06 pm
Kind of hard to decide what constitutes slow when we have zero information beyond the amount of RAM allocated to the VM, and that you're using SQL 2016. You haven't said how much RAM the SQL instance can make use of, nor have you provided any details on the query itself, and yet you seem to expect a useful answer? I'd love to just "press the easy button" (see the Staples ads), and deliver the answer to you, but that's just not real life. We need a LOT more detail.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 2, 2017 at 1:11 pm
I wasn't expecting an "answer" ... My initial question was making sure SQL 2016 uses the available RAM, or if I need to configure it somehow as with earlier versions of SQL.
I should have given more info. Here's the Table and query involved
QUERY:
declare @rowsperpage int, @pagenumber int set @rowsperpage=50 set @pagenumber=1
select sod.*
from (select ms.mem_id, ms.MEM_firstName,ms.MEM_lastName, ms.MEMEPF_city,
ms.STA_ID, ms.memepf_zipcode, ms.MEMEPF_title, ms.MEMEPF_company,
ms.FUNC_fieldValue, ms.INDCODE_fieldValue, ms.MEMEPF_networkingProfile, ms.Resume_id,
ms.resume_status,ms.value_graphic, ms.RELOC_ID, ms.MEMEPF_showExec,ms.MEMEPF_showRec,
ms.MEM_LastVisitedDate, ms.M2RStatus, ms.M2MStatus,
row_number() over (order by mem_lastname ASC) as rownum
from member_search ms
where ms.M2RStatus ='1'
and (memepf_funccode1 in (12000, 12100, 12120, 12200, 12300, 12400, 12500, 12600, 12900)
or memepf_funccode2 in (12000, 12100, 12120, 12200, 12300, 12400, 12500, 12600, 12900)
or contains(ms.ECOMP_functioncode,'12000 or 12100 or 12120 or 12200 or 12300 or 12400 or 12500 or 12600 or 12900'))
and (memepf_indcode1 in (4130)
or memepf_indcode2 in (4130)
or contains(ms.ECOMP_industrycode,'4130'))
and (mem_firstname >= 'a')
and (mem_lastname >= 'a') ) as sod
where sod.rownum between ((@pagenumber-1)*@rowsperpage)+1 and @rowsperpage*(@pagenumber)
TABLE:
CREATE TABLE [dbo].[Member_Search](
[MEM_ID] [int] NOT NULL,
[MEM_firstName] [varchar](50) NULL,
[MEM_lastName] [varchar](50) NULL,
[MEMTYPE_ID] [int] NULL,
[MEMSRV_ID] [int] NULL,
[MEMST_ID] [int] NULL,
[Mem_ProfileAccessDate] [datetime] NULL,
[MEMEPF_city] [varchar](30) NULL,
[STA_ID] [varchar](2) NULL,
[MEMEPF_Province] [varchar](30) NULL,
[MEMEPF_zipCode] [varchar](10) NULL,
[MEMEPF_country] [varchar](30) NULL,
[MEMEPF_URL] [varchar](100) NULL,
[MEMEPF_networkingProfile] [varchar](max) NULL,
[MEMEPF_nfOffer] [varchar](50) NULL,
[MEMEPF_nfSeek] [varchar](50) NULL,
[MEMEPF_profile] [varchar](max) NULL,
[MEMEPF_FuncCode1] [varchar](5) NULL,
[MEMEPF_FuncCode2] [varchar](5) NULL,
[MEMEPF_IndCode1] [varchar](5) NULL,
[MEMEPF_IndCode2] [varchar](5) NULL,
[MEMEPF_lastTenYears] [varchar](max) NULL,
[MEMEPF_Awards] [varchar](max) NULL,
[MEMEPF_title] [varchar](100) NULL,
[RELOC_ID] [int] NULL,
[MEMEPF_showExec] [bit] NULL,
[MEMEPF_showRec] [bit] NULL,
[MEMEPF_profileLastUpdated] [datetime] NULL,
[MEMEPF_company] [varchar](100) NULL,
[MEMCOMP_ID] [int] NULL,
[M2MStatus] [smallint] NULL,
[M2RStatus] [smallint] NULL,
[resume_status] [smallint] NULL,
[workexperience_titles] [varchar](max) NULL,
[workexperience_companies] [varchar](max) NULL,
[resume_content] [varchar](max) NULL,
[MEM_LastVisitedDate] [datetime] NULL,
[ECOMP_Level_Current] [varchar](20) NULL,
[LastNetworkActivity] [datetime] NULL,
[ECOMP_Level_Previous] [varchar](2000) NULL,
[ECOMP_functionCode] [varchar](2000) NULL,
[ECOMP_industryCode] [varchar](2000) NULL,
[Resume_id] [int] NULL,
[Resume_type] [int] NULL,
[FUNC_fieldValue] [varchar](50) NULL,
[INDCODE_fieldValue] [varchar](50) NULL,
[value_graphic] [int] NULL,
CONSTRAINT [PK_Member_Search] PRIMARY KEY CLUSTERED
(
[MEM_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] TEXTIMAGE_ON [PRIMARY]
GO
Full-Text Index:
/****** Object: FullTextIndex Script Date: 5/2/2017 11:50:25 AM ******/
CREATE FULLTEXT INDEX ON [dbo].[Member_Search](
[ECOMP_functionCode] LANGUAGE 'English',
[ECOMP_industryCode] LANGUAGE 'English',
[MEMEPF_company] LANGUAGE 'English',
[MEMEPF_networkingProfile] LANGUAGE 'English',
[MEMEPF_profile] LANGUAGE 'English',
[MEMEPF_title] LANGUAGE 'English',
[resume_content] LANGUAGE 'English',
[workexperience_companies] LANGUAGE 'English',
[workexperience_titles] LANGUAGE 'English')
KEY INDEX [PK_Member_Search]ON ([MemberSearch], FILEGROUP [ftfg_MemberSearch])
WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM)
Table's Regular Indexes:
/****** Object: Index [IX_Country] Script Date: 5/2/2017 3:14:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_Country] ON [dbo].[Member_Search]
( [MEMEPF_country] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
SET ANSI_PADDING ON
/****** Object: Index [IX_Country_Province] Script Date: 5/2/2017 3:14:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_Country_Province] ON [dbo].[Member_Search]
( [MEMEPF_country] ASC,
[MEMEPF_Province] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
SET ANSI_PADDING ON
/****** Object: Index [IX_ECOMP_LevelCurrent] Script Date: 5/2/2017 3:14:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_ECOMP_LevelCurrent] ON [dbo].[Member_Search]
( [ECOMP_Level_Current] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
SET ANSI_PADDING ON
/****** Object: Index [IX_FirstName_LastName] Script Date: 5/2/2017 3:14:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_FirstName_LastName] ON [dbo].[Member_Search]
( [MEM_firstName] ASC,
[MEM_lastName] 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]
SET ANSI_PADDING ON
/****** Object: Index [IX_FuncCode1_FuncCode2_Indcode1_Indcode2] Script Date: 5/2/2017 3:14:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_FuncCode1_FuncCode2_Indcode1_Indcode2] ON [dbo].[Member_Search]
( [MEMEPF_FuncCode1] ASC,
[MEMEPF_FuncCode2] ASC,
[MEMEPF_IndCode1] ASC,
[MEMEPF_IndCode2] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
SET ANSI_PADDING ON
/****** Object: Index [IX_FuncCode2] Script Date: 5/2/2017 3:14:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_FuncCode2] ON [dbo].[Member_Search]
( [MEMEPF_FuncCode2] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
SET ANSI_PADDING ON
/****** Object: Index [IX_IndCode1_FuncCode1_FuncCode2] Script Date: 5/2/2017 3:14:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_IndCode1_FuncCode1_FuncCode2] ON [dbo].[Member_Search]
( [MEMEPF_IndCode1] ASC,
[MEMEPF_FuncCode1] ASC,
[MEMEPF_FuncCode2] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
SET ANSI_PADDING ON
/****** Object: Index [IX_IndCode2] Script Date: 5/2/2017 3:14:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_IndCode2] ON [dbo].[Member_Search]
( [MEMEPF_IndCode2] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
SET ANSI_PADDING ON
/****** Object: Index [IX_LastName_FirstName] Script Date: 5/2/2017 3:14:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_LastName_FirstName] ON [dbo].[Member_Search]
( [MEM_lastName] ASC,
[MEM_firstName] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
/****** Object: Index [IX_M2RStatus] Script Date: 5/2/2017 3:14:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_M2RStatus] ON [dbo].[Member_Search]
( [M2RStatus] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
/****** Object: Index [IX_MemcompID] Script Date: 5/2/2017 3:14:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_MemcompID] ON [dbo].[Member_Search]
( [MEMCOMP_ID] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
/****** Object: Index [IX_MemID_IgnoreDup] Script Date: 5/2/2017 3:14:50 PM ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_MemID_IgnoreDup] ON [dbo].[Member_Search]
( [MEM_ID] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
/****** Object: Index [IX_MEMLastVisitedDate] Script Date: 5/2/2017 3:14:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_MEMLastVisitedDate] ON [dbo].[Member_Search]
( [MEM_LastVisitedDate] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
/****** Object: Index [IX_ProfileAccessDate] Script Date: 5/2/2017 3:14:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_ProfileAccessDate] ON [dbo].[Member_Search]
( [Mem_ProfileAccessDate] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
/****** Object: Index [IX_ProfileLastUpdatred] Script Date: 5/2/2017 3:14:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_ProfileLastUpdatred] ON [dbo].[Member_Search]
( [MEMEPF_profileLastUpdated] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
/****** Object: Index [IX_ResumeStatus] Script Date: 5/2/2017 3:14:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_ResumeStatus] ON [dbo].[Member_Search]
( [resume_status] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
/****** Object: Index [IX_Service_Status] Script Date: 5/2/2017 3:14:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_Service_Status] ON [dbo].[Member_Search]
( [MEMSRV_ID] ASC,
[MEMST_ID] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
/****** Object: Index [IX_ShowExec] Script Date: 5/2/2017 3:14:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_ShowExec] ON [dbo].[Member_Search]
( [MEMEPF_showExec] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
/****** Object: Index [IX_ShowRec] Script Date: 5/2/2017 3:14:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_ShowRec] ON [dbo].[Member_Search]
( [MEMEPF_showRec] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
SET ANSI_PADDING ON
/****** Object: Index [IX_State_City] Script Date: 5/2/2017 3:14:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_State_City] ON [dbo].[Member_Search]
( [MEMEPF_city] ASC,
[STA_ID] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
SET ANSI_PADDING ON
/****** Object: Index [IX_ZipCode] Script Date: 5/2/2017 3:14:50 PM ******/
CREATE NONCLUSTERED INDEX [IX_ZipCode] ON [dbo].[Member_Search]
( [MEMEPF_zipCode] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
May 2, 2017 at 1:36 pm
There's an awful lot going on in that query. There are a total of 10 values that end up being searched for, and based on your run time, you're getting 11,111 records per second. As the field is varchar(2000), I'm also wondering what a typical field value looks like, the approximate average length of that field, and what the actual execution plan looks like. With 500k rows in that table, the full-text index might be rather large. Do you have a lot of different values to search for in that field, beyond just what this query needs to find? Sometimes, a sparse index is more useful than a full-text index, and more often when the number of values being searched for is limited in scope. If you can post the actual execution plan (as a .sqlplan file), we can at least see what the optimizer is trying to accomplish.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 2, 2017 at 1:37 pm
You have a new server. You expect it to be fast, because it's new?
Have you looked at:
1. The configuration of the VM.
a. How are the disks conmfigured? Are they think oir thin provisioned?
b. How mnay CPU's, are they set to "hot add"
c. 16 GB of RAM is not much. I have 32 GB in my laptop. Is it reserved? Is ballooning enabled?
2. How is SQL configured?
Please post the results of thiese queries:
sp_configure 'show advanced options', 1
RECONFIGURE
Then,
sp_configure
Look at the settings for 'cost threshold for parallelism', 'max degree of parallelism', 'max server memory (MB)'
What are these set to?
I have these questions from looking at the DDL statements:
1. There are 21 indexes on this table. Are they needed? Do they get used? 21 is a large number of indexes.
2. All of them have a fill factor of 90. Is this needed?
3. Where the full text indexes populated? What disk do they live on?
4. What is the point of this code:
and (mem_firstname >= 'a')
and (mem_lastname >= 'a')
Is it to get all of the names? The names columns are nullable. If they do not contain any nulls, then leave this out. If they do contain NULLS, then use WHERE IS NOT NULL for each field.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 2, 2017 at 1:39 pm
As to max memory that SQL Server will use, you can get that information easily enough and determine how it's configured. There's also a minimum memory setting. I've long forgotten the T-SQL to find that out, but I do remember you had to execute a RECONFIGURE to make changes.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 2, 2017 at 3:04 pm
Strange thing is our old "Stage" physical server running SQL 2005 with 4 gig ram, is blazing fast running the same query against a copy of the database.
The queries are built from a user interface where they can select various fields to search, then a query is built based on what they selected.
Partial answers:
Name.............................Min....Max......Config value....run value
cost threshold for parallelism....0....32767...........5..............5
max degree of parallelism.........0....32767...........0..............0
max server memory (MB)..........128...2147483647...2147483647....2147483647
Thick Provisioned. I get a slice of "High performance" disk. I have no idea how many spindles are used,
so I cannot spread databases across physical drives.
4 CPUs
Processor Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz, 2594 Mhz, 1 Core(s), 1 Logical Processor(s)
Processor Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz, 2594 Mhz, 1 Core(s), 1 Logical Processor(s)
Processor Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz, 2594 Mhz, 1 Core(s), 1 Logical Processor(s)
Processor Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz, 2594 Mhz, 1 Core(s), 1 Logical Processor(s)
Installed Physical Memory (RAM)____16.0 GB
Total Physical Memory______________16.0 GB
Available Physical Memory___________1.02 GB (Does this mean 15 G used, 1G remaining?)
Total Virtual Memory_______________20.0 GB
Available Virtual Memory____________4.38 GB
May 3, 2017 at 1:29 pm
Attached is the execution plan
May 3, 2017 at 1:51 pm
This looks like an estimated plan, not the actual plan.
Can you post the actual plan?
The only thing that stuck out was that, even with 20+ indexes, the optimizer picked a key lookup
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 4, 2017 at 12:11 pm
Ooops, Actual plan attached
The table does not have frequent inserts, so better to have a lot of indexes on all the possible fields they might search by ?
May 4, 2017 at 1:47 pm
homebrew01 - Thursday, May 4, 2017 12:11 PMOoops, Actual plan attachedThe table does not have frequent inserts, so better to have a lot of indexes on all the possible fields they might search by ?
In this case, none of the indexes are being used.
There is an implicit conversion happening.
These fields are varchar(5), but you are comparing them to integers:
and (memepf_funccode1 in (12000, 12100, 12120, 12200, 12300, 12400, 12500, 12600, 12900)
or memepf_funccode2 in (12000, 12100, 12120, 12200, 12300, 12400, 12500, 12600, 12900)
and (memepf_indcode1 in (4130)
or memepf_indcode2 in (4130)
When you moved to this new server, did you update statistics? It looks as if the statistics are out of date.
Have the fulltext indexes been rebuilt?
It also looks like this may be a "catch-all" query. You may have a problem with parameter sniffing.
Clear the cache, and then see what happens
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 4, 2017 at 3:25 pm
How do you know the indexes are not being used in the actual execution plan ?
Statistics are set to auto update, and I just ran: update statistics member_search with fullscan
Indexes are all less than 10% fragmentation.
I commented out "memepf_funccode1 in ('12000', '12100', '12120', '12200', '12300', '12400', '12500', '12600', '12900') "
and "memepf_funccode1 in ('12000', '12100', '12120', '12200', '12300', '12400', '12500', '12600', '12900') "
and it ran much faster, not not making use of those indexes ??
Attached is a new actual execution plan
I changed the query to put quotes around the varchar columns.
declare @rowsperpage int, @pagenumber int set @rowsperpage=50 set @pagenumber=1
select sod.*
from (select ms.mem_id, ms.MEM_firstName,ms.MEM_lastName, ms.MEMEPF_city,
ms.STA_ID, ms.memepf_zipcode, ms.MEMEPF_title, ms.MEMEPF_company,
ms.FUNC_fieldValue, ms.INDCODE_fieldValue, ms.MEMEPF_networkingProfile, ms.Resume_id,
ms.resume_status,ms.value_graphic, ms.RELOC_ID, ms.MEMEPF_showExec,ms.MEMEPF_showRec,
ms.MEM_LastVisitedDate, ms.M2RStatus, ms.M2MStatus,
row_number() over (order by mem_lastname ASC) as rownum
from member_search ms
where ms.M2RStatus ='1'
and (memepf_funccode1 in ('12000', '12100', '12120', '12200', '12300', '12400', '12500', '12600', '12900')
or memepf_funccode2 in ('12000', '12100', '12120', '12200', '12300', '12400', '12500', '12600', '12900')
or contains(ms.ECOMP_functioncode,'''12000'' or ''12100'' or ''12120'' or ''12200'' or ''12300''
or ''12400'' or ''12500'' or ''12600'' or ''12900'''))
and (memepf_indcode1 in ('4130')
or memepf_indcode2 in ('4130')
or contains(ms.ECOMP_industrycode,'4130'))
and (mem_firstname >= 'a')
and (mem_lastname >= 'a') ) as sod
where sod.rownum between ((@pagenumber-1)*@rowsperpage)+1 and @rowsperpage*(@pagenumber)
May 4, 2017 at 5:55 pm
I think it may make sense for you to do some learning.
Grant Fritchey wrote this article.
https://www.simple-talk.com/sql/performance/execution-plan-basics/
He has also written a good book.
When you look at each part of the execution plan, you can hover over each step and the statistics will appear in a tool tip. You can also right click and a whole menu of options will appear.
The "Key Lookup" on the table indicates that there is no good index that the optimizer can use, so it uses the primary key to get the rows it needs. It's an expensive operation. That's the simple explanation. If it had used one of the indexes, an "Index seek", an "Index Scan", or a few others would have been displayed.
Your logic of creating indexes for whatever and where ever is pretty flawed, Create indexes based upon actual usage, not semi-educated guesses.
You statistics are out of date. By comparing the estimated rows vs. the actual rows, you can see a big difference. Auto update stats typically is not enough. You need to update statistics as part of maintenance.
Buying a new server does not guarantee "better performance" This post has only scratched the surface on all of the things you need to be able to look at and be able to diagnose. Invest in yourself and spend some time learning.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 4, 2017 at 10:50 pm
homebrew01 - Tuesday, May 2, 2017 1:11 PMI wasn't expecting an "answer" ... My initial question was making sure SQL 2016 uses the available RAM, or if I need to configure it somehow as with earlier versions of SQL.I should have given more info. Here's the Table and query involved
QUERY:
declare @rowsperpage int, @pagenumber int set @rowsperpage=50 set @pagenumber=1select sod.*
from (select ms.mem_id, ms.MEM_firstName,ms.MEM_lastName, ms.MEMEPF_city,
ms.STA_ID, ms.memepf_zipcode, ms.MEMEPF_title, ms.MEMEPF_company,
ms.FUNC_fieldValue, ms.INDCODE_fieldValue, ms.MEMEPF_networkingProfile, ms.Resume_id,
ms.resume_status,ms.value_graphic, ms.RELOC_ID, ms.MEMEPF_showExec,ms.MEMEPF_showRec,
ms.MEM_LastVisitedDate, ms.M2RStatus, ms.M2MStatus,
row_number() over (order by mem_lastname ASC) as rownum
from member_search ms
where ms.M2RStatus ='1'
and (memepf_funccode1 in (12000, 12100, 12120, 12200, 12300, 12400, 12500, 12600, 12900)
or memepf_funccode2 in (12000, 12100, 12120, 12200, 12300, 12400, 12500, 12600, 12900)
or contains(ms.ECOMP_functioncode,'12000 or 12100 or 12120 or 12200 or 12300 or 12400 or 12500 or 12600 or 12900'))
and (memepf_indcode1 in (4130)
or memepf_indcode2 in (4130)
or contains(ms.ECOMP_industrycode,'4130'))
and (mem_firstname >= 'a')
and (mem_lastname >= 'a') ) as sod
where sod.rownum between ((@pagenumber-1)*@rowsperpage)+1 and @rowsperpage*(@pagenumber)
Why did you decide the full text index is even an issue here?
You have numerous OR's in you WHERE clause, each of those OR's means an extra scan of the data.
Because you're looking for a number of values, index seek is not an option, even if you turn all the integers to strings.
Each entry in the index needs to be validated against each value in the list.
Optimizer has a choice - to do 9 index seeks with unpredictable results, or a single index scan.
Obviously, a scan is preferable.
Then it faces another choice - 6 index scans followed by UNIONs (combining each of the OR's together) and merge - or a single table scan.
Considering the low selectivity of the filters (146k rows selected out of 500k) a single table scan wins hands down.
It might help doing a query like this:
SELECT *
FROM member_search ms
WHERE ms.M2RStatus ='1' AND ms.mem_id IN (
select mem_id
from member_search
WHERE memepf_funccode1 memepf_funccode1 LIKE '12_00'LIKE '12_00'
UNION
select mem_id
from member_search
WHERE memepf_funccode2 LIKE '12[0123456789]00'
UNION
select mem_id
from member_search
contains(ms.ECOMP_functioncode,'12000 or 12100 or 12120 or 12200 or 12300 or 12400 or 12500 or 12600 or 12900')
)
It might help a bit, but still the root of the problem is not here.
It's in you answer on the following question:
What is your opinion on data normalization?
_____________
Code for TallyGenerator
May 5, 2017 at 8:42 am
Here's the Actual execution plan from our "Stage"server. Slightly different for some reason, and completes in 2 seconds instead of 30 seconds.
The "Stage" server is an old SQL 2005 box with 4 gig of ram.
So it's not that I expect the new server to be faster, but why is it so much slower ?
I can't modify the query at this point, because it's built dynamically in a PHP application from a search screen, based on what fields are selected.
Thanks for everyone's help so far.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply