June 18, 2010 at 12:09 pm
Hi all,
I have a simple table defined thus:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CullDetails](
[CullID] [bigint] IDENTITY(1,1) NOT NULL,
[TagID] [int] NOT NULL,
[Query] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DocCount] [int] NOT NULL,
[IsDeleted] [bit] NOT NULL CONSTRAINT [DF_CullDetails_IsDeleted] DEFAULT ((0)),
[CreatedBy] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_CullDetails_CreatedDate] DEFAULT (getdate()),
[ModifiedBy] [int] NOT NULL,
[LastModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_CullDetails_LastModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_CullDetails] PRIMARY KEY NONCLUSTERED
(
[CullID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
USE [MyDB]
GO
ALTER TABLE [dbo].[CullDetails] WITH CHECK ADD CONSTRAINT [FK_CullDetails_Tags] FOREIGN KEY([TagID])
REFERENCES [dbo].[Tags] ([TagID])
GO
(Query is an ntext rather than nvarchar because the contents are often well over 4000 chars, and may contain unicode data.) The Tags table it references is also a fairly simple table, with usually under 30 rows, for which TagID is an integer primary key clustered index.
I have two databases that have the same schema. In database A, this table contains 32 records and takes 65 seconds to query "select * from CullDetails". In database B, the table has 51 records and takes under 1 second to run the same query.
I have run a DBCC CHECKDB on the slow database and it found no errors.
I updated statistics, updated indexes, and full-text catalogs - no improvement.
When I turn statistics on for the queries on the two servers the main difference I see is in the LOB reads:
Database A:
Table 'CullDetails'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0,
lob logical reads 6276, lob physical reads 0, lob read-ahead reads 2468.
Database B:
Table 'CullDetails'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0, lob logical reads 171, lob physical reads 0, lob read-ahead reads 0.
The sizes of the "Query" field row contents (the only text data that should be read as lob) are roughly the same in both databases.
When I run execution plans on both databases they are nearly identical and don't estimate that A should be slower than B.
Neither database or table is partitioned as far as I can tell.
Both databases have roughly the same physical size and both have underwent the "autogrowth" process.
Just for yucks, I tried changing the clustered index to TagID, that did not make any difference.
I am not sure where to go from here in determining what the problem is in database A. Any suggestions you have would be greatly appreciated.
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant
June 18, 2010 at 12:22 pm
I would suspect that if you remove the Query field from the select statement it would speed up almost immediately. I also suspect that if you look at the contents of the two fields that the slower one has a great deal more data in the field.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 18, 2010 at 12:45 pm
I think you are right on both counts - at first glance it appeared that the contents of the Query fields in database B were about the same size but there are a few massive field sizes in database A.
I may just need to look at a strategy where I don't keep the query info in the database but just write a path to the query, and grab the individual text values as needed to display.
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply