June 15, 2010 at 2:06 am
I have a table CREATE TABLE [dbo].[Item](
[Guid] [uniqueidentifier] NOT NULL,
[OwnerNSGuid] [uniqueidentifier] NOT NULL,
[ProductGuid] [uniqueidentifier] NOT NULL,
[SecurityGuid] [uniqueidentifier] NULL,
[ProductUninstalled] [int] NOT NULL DEFAULT ((0)),
[Attributes] [int] NOT NULL,
[Alias] [nvarchar](100) NULL,
[State] [ntext] NULL,
[Name] [nvarchar](250) NOT NULL,
[Description] [nvarchar](1000) NOT NULL,
[CreatedBy] [nvarchar](255) NULL,
[ModifiedBy] [nvarchar](255) NULL,
[CreatedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Guid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
the table contains 25000 rows.
when I executed select * from table the result takes almost 8 sec see the report after statistics io and time are on (25217 row(s) affected)
Table 'Item'. Scan count 1, logical reads 1015, physical reads 0, read-ahead reads 0, lob logical reads 371361, lob physical reads 0, lob read-ahead reads 108129.
SQL Server Execution Times:
CPU time = 1201 ms, elapsed time = 7693 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
the application developer said something it;s wrong with my hardware,but the machine is windows 2008 server cluster symantec plus mssql 2005 64k,12 G RAM for mssql.
Please advise me in this dispute.
June 16, 2010 at 8:15 am
Hadrian (6/15/2010)
Table 'Item'. Scan count 1, logical reads 1015, physical reads 0, read-ahead reads 0, lob logical reads 371361, lob physical reads 0, lob read-ahead reads 108129.SQL Server Execution Times:
CPU time = 1201 ms, elapsed time = 7693 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
You are reading 108129 pages of lob data (Large Object). This is probably the contents of the State column. This is over 860 MB of data.
It seems reasonable that it could take 8 seconds to read 800 MB of data from disk.
If you want the query to complete faster, you can do two things:
1) Reduce the amount of data read (for example by not reading the State column)
2) Get a faster disk system - use many disks in a RAID0 configuration to get maximum performance.
100 MB/s is not bad, but it is possible to get much faster IO bandwidth by throwing money at the problem.
/SG
June 17, 2010 at 12:13 pm
You probably also want to check for fragmentation on your clustered index. SQL Server will first try to store the LOB (your ntext column) in the data pages. If you initially insert the row with a null then it won't take much space. An update with just a few hundred characters in the ntext column will then cause page splits. Then, if another update is done with a much larger size, it will get stored out of row.
Another point is that unique identifiers do not make very good clustering keys. They tend to cause page splits on insert unless they are sequential.
Rebuilding the clustered index could get better performance.
Todd Fifield
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy