September 16, 2003 at 1:23 am
An app on one of our servers is causing more and more locks and I would like any ideas/advice I can pass on to the developers to solve this. The table on which the locking appears is accessed via a web app and looks like this:
CREATE TABLE [dbo].[tb1] (
[c1] [varchar] (36) NOT NULL ,
[c2] [int] NOT NULL ,
[c3] [text] NULL ,
[c4] [int] NULL ,
[c5] [datetime] NULL ,
[c6] [varchar] (50) NULL ,
[c7] [varchar] (50) NULL ,
[c8] [int] NULL ,
[c9] [varchar] (15) NULL ,
[c10] [varchar] (15) NULL ,
[c11] [varchar] (50) NULL ,
[c12] [datetime] NULL ,
[c13] [varchar] (50) NULL ,
[c14] [varchar] (3) NULL ,
[c15] [varchar] (1) NULL ,
[c16] [varchar] (50) NULL ,
[c17] [text] NULL ,
[c18] [varchar] (36) NULL ,
[c19] [varchar] (50) NULL ,
[c20] [varchar] (20) NULL ,
[c21] [varchar] (255) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb1] WITH NOCHECK ADD
CONSTRAINT [tb1_cpk] PRIMARY KEY CLUSTERED
(
[c1]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
There are also 9 1-key-col non-clustered indexes on mostly varchar cols.
My suggestions are the following:
1. Change the clustered index to non-clustered to prevent page splitting (I don't think they ever do searches on ranges on this key which is generated by the app and looks like this: 00006317-6A81-42AD-A963-1C9E56FA0006 )
2. Try using varchar fields in stead of text fields to speed up updates. This may not be possible because the text cols contain xml code and may be too long.
Any more ideas will be appreciated.
September 16, 2003 at 2:47 am
Check also that c1 is not subject to updates. If it is then all NC index entries are updated too.
Cheers,
- Mark
Cheers,
- Mark
September 16, 2003 at 3:42 am
Some hints:
- Try to put your text data into a separate filegroup on a separate physical disk. That helps a lot.
- You should have a clustered index on your table, preferebly an a column which is not modified afterwards and on which you have range queries (or like queries as the columns are varchar) You tell us you never do range queries. Is this true also for like queries? (i.e. you never do a query like this: select ... from tb1 where c.. like 'dd%'? That one is a range query!
Bye
Gabor
Bye
Gabor
September 16, 2003 at 4:34 am
Could also look at using the textinrow option if a lot of your text fields are small but some still exceed the limit for varchar/row length.
Could pick up a little bit by changing datetime to small datetime - that will cut your row size by 4 bytes, so when you do a IO, its just a little bit faster.
If you really are using a GUID for your key, you should store it as a uniqueidentifier instead of a string (essentially as a number instead of a string representation). That'll save you 28 bytes (I think?!), which will definitely add up.
Look at how your indexes affect updates, consider using index and locking hints to customize things some.
Andy
September 17, 2003 at 6:34 am
Thanks to all. After scheduling a job to do frequent rowcounts on the table, I saw that there are really many inserts being done. I advised them to change the index on the GUID to a unique non-clustered index (they do no range searches on this column) and change the non-clustered index on another column to a non-unique clustered index. This contains a non-unique userid and is often used in range searches. I think this is the easiest and quickest way to improve performance and in a next release they will look at things like changing the data types for certain columns.
September 17, 2003 at 8:04 am
A few late thoughts:
The key assumption I'll make is that c1 is indeed a guid. Assuming this is true, you've got major pain in this table.
First off, you could be storing this data in the uniqueidentifier datatype. This datatype requires 16 bytes to store a value; varchar(36) requires 38 (I believe it's +2 for the overhead of allowing variable length). Even just switching to char(36) should be a minor improvement, from saving bytes and tossing the overhead of determining the length of every given value. Making this change should be a performance improvement, but probabaly won't affect locking.
Next, it's a clustered index, which means the table itself is physically sorted by these values. You say that ther are lots of inserts, and guids are (by definition) random, so you'll be inserting all over the table, not just at the "high end" of it. Lots of locks there to implement page splits!
Last, as it is the clustered index, all the other indexes will contain the key value in the leaf node. That's 38 bytes, which could be 36 bytes, which could be 16 bytes; cut the size by half, reduce the number of pages to be read. Insert a new value, and all the indexes need to be updated, and that's more locks. With a large key value you'll fill the leaf pages quickly and generate more page splits (and locks. To mention, nine would be a lot of indexes for an OLTP implementation, but acceptable for a Reporting one.)
It seems safe to assume that the guid would not be modified once entered. If it is, then the clustered index has to be resorted (with maybe a page split), and all the other indexes would need to be updated.
Philip
September 17, 2003 at 1:01 pm
Thanks again. And yes, the key is indeed a guid and will be changed in a next release. After more discussions with the developers it looks like the date field is the only field on which they can put a clustered index. It contains a mutation date which will always be the current date and time for new inserts and will therefor always be inserted at the bottom of the last page. They also do a lot of searches and range lookups on the date.
Unfortunately this is again one of those situations where the original designer is long gone - he probably knew what was best for him!
September 18, 2003 at 7:38 am
Somebody has advised to store the text fields in separate tables which will be linked to the original table with the GUID key. Will this improve performance? It will be on the same disk and there will now be an extra join in stead of just the readtext.
October 2, 2003 at 8:57 am
quote:
Somebody has advised to store the text fields in separate tables which will be linked to the original table with the GUID key. Will this improve performance? It will be on the same disk and there will now be an extra join in stead of just the readtext.
No. That GUID column is a very very bad clustered index: Always, always, keep clustered indexes as small, selective and unchangeing as possible. This goes double for each non-clustered index you add on top as their size is affected by the size of the clustered index. SQL doesn't store text in the table, and this is configurable also (look up textinrow in BOL)
Keith Henry
DBA/Developer/BI Manager
Keith Henry
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply