We are having a problem with slow queries on a certain table. We tried some things with indexes but that didn't make any difference. Then we discovered that the amount of data being transferred was excessively high.
The table definition is as follows:
/****** Object: Table [dbo].[survey_tbMultiLanguageText] Script Date: 23-6-2015 17:45:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[survey_tbMultiLanguageText](
[fMultiLanguageTextId] [int] IDENTITY(1,1) NOT NULL,
[fProjectID] [int] NOT NULL,
[fElementTypeID] [int] NOT NULL,
[fElementCode] [varchar](50) NOT NULL,
[fPropertyID] [int] NOT NULL,
[fLanguageID] [int] NOT NULL,
[fText] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_survey_tbMultiLanguageText] PRIMARY KEY CLUSTERED
(
[fMultiLanguageTextId] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[survey_tbMultiLanguageText] WITH CHECK ADD CONSTRAINT [FK_survey_tbMultiLanguageText_survey_tbLanguage] FOREIGN KEY([fLanguageID])
REFERENCES [dbo].[survey_tbLanguage] ([fLanguageID])
GO
ALTER TABLE [dbo].[survey_tbMultiLanguageText] CHECK CONSTRAINT [FK_survey_tbMultiLanguageText_survey_tbLanguage]
GO
ALTER TABLE [dbo].[survey_tbMultiLanguageText] WITH CHECK ADD CONSTRAINT [FK_survey_tbMultiLanguageText_survey_tbProject] FOREIGN KEY([fProjectID])
REFERENCES [dbo].[survey_tbProject] ([fProjectID])
GO
ALTER TABLE [dbo].[survey_tbMultiLanguageText] CHECK CONSTRAINT [FK_survey_tbMultiLanguageText_survey_tbProject]
GO
If I query this table like this:
select top 100 *
from survey_tbMultiLanguageText
This results in bytes received of 1.284202E+07 (12.25 MB)
Within these 100 records the max length of fText is 380, and if I convert to nvarchar(380) the number of bytes received is much lower (79339).
The database server I'm testing this on is (select @@VERSION
):
Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
Oddly enough, when querying for a specific project/language, it gives different results on different servers when querying the same data (normal on staging vs excessively high on live). Yet the top 100 query also gives the same problem on staging.