RE: Fill Factor on a clustered index

Tricky one.

Have a database getting alot of locks...Darn sql server and those
silly page locks.
Anyway had to change the application to use a disconnected methodology
and check for changes before doing an update. Love the asp.net
alternative. It just clobbers data (unless you write code to
check)...record locks have their uses but...right now need to reduce
any locking possible and one article on the web mentioned even tables
where you just add data too could do with a fill factor.

One table just keeps a track of changes by the record id.
There is a clustered index on the record id but it is not unique.
(E.g. there is a new record for each field changed for that record)
Will locks be reduced if a fill factor of say 70 is added, and is it
even relevant for a clustered index?
There's also the pad factor....can see that is more relevant for non-
clustered indexes but what about clustered?

Regards,
Tom
0
SmartbizAustralia
1/19/2010 2:19:16 AM
sqlserver.programming 1873 articles. 0 followers. Follow

1 Replies
784 Views

Similar Articles

[PageSpeed] 59

SmartbizAustralia wrote:
> 
> Tricky one.
> 
> Have a database getting alot of locks...Darn sql server and those
> silly page locks.
> Anyway had to change the application to use a disconnected methodology
> and check for changes before doing an update. Love the asp.net
> alternative. It just clobbers data (unless you write code to
> check)...record locks have their uses but...right now need to reduce
> any locking possible and one article on the web mentioned even tables
> where you just add data too could do with a fill factor.
> 
> One table just keeps a track of changes by the record id.
> There is a clustered index on the record id but it is not unique.
> (E.g. there is a new record for each field changed for that record)
> Will locks be reduced if a fill factor of say 70 is added, and is it
> even relevant for a clustered index?
> There's also the pad factor....can see that is more relevant for non-
> clustered indexes but what about clustered?
> 
> Regards,
> Tom

Unless you explicitely specify page locks (which you are probably not
doing), then changing the fillfactor to such a large number will
probably only waste space (with its potential negative effect on
performance). The same is true for the pad factor. If you disregard
explicit page locks, then these "factors" have no relation to blocking.

You will probably have to investigate when you lock rows, and whether
the used locking strategy is the best for your purpose. You could
investigate which indexes are causing the most blocking. You might come
to all kinds of conclusions, including the following:
- no blocking, just long lasting locks because of an I/O bottleneck
- too many long lasting transactions covering too many rows
- unintended lock escalation
- too many indexes
- unintended exclusive locks instead of shared locks
- inappropriate data model

Good luck,
Gert-Jan
0
Gert
1/20/2010 8:47:53 PM
Reply:

Similar Artilces:

hyperlinks to create an index
I'm trying to learn hoe to use a hyperlink to create a worksheet index. In one spread sheet I've got about 35-40 tabs, thus the index idea. Additionally, does anyone know how I can make my index always the sheet that excel opens to upon opening this workbook. David McRitchie posted this and it might help you: =HYPERLINK("#"&CELL("address",C5),C5) =HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5) =HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5) And you have another r...

CRM 1.2 connector on Exchange 2003 A/P Cluster
I know the CRM 1.2 Exchange Queue email connector is not supported by Microsoft on an Exchange 2003 Active/Passive cluster. However, there is someone out there that created a 'workaround' to make it send and receive emails. I pulled this from an old newgroup, July 30, 2004. "This has been done 0- and the details on how to do it were posted on this newsgroup a few months back. Now that *is* good. Search the archives for this newsgroup on Google Another alternative, which is supported, is to install the router on a front end Exchange server or servers, keeping the shared-sto...

How do I re-size/enlarge Building Blocks Preview Pane?
Using Word 2007. The Building Blocks preview pane is so tiny I can't read the displayed text. There doesn't seem to be any way of re-sizing the pane. Very grateful for any help. As far as I know, there is no way to enlarge the preview pane. If you are unsure if you need a particular Building Block, you could just insert it into your document. If it isn't what you want, use Undo (Ctrl+Z) to get rid of it. Note that sometimes you may have to Undo repeatedly. -- Stefan Blom Microsoft Word MVP "Min Chambers" <Min Chambers@discussions.microsoft.co...

Searching all mailboxes with indexing
Is it possible to search all mailboxes using full text indexing. How would this be done. With just Exchange 2000/2003, no. In order to make this happen, you would have to do the following: 1.) Setup a single user account that would have permission to access everyone's mailbox 2.) Write a custom application that would then issue a query against each mailbox and then correlate all the results. There is another solution that might work, but I have not tried nor verified and that would be to use Sharepoint Portal Server 2001 or 2003. SharePoint Portal Server can index the Public Folder...

filling weekly totals in new column
This seems like this would be a common question to me, but I've searche the forums and can't find it, so I'm sorry if it's already on here. Here's what I would like to do: I have one column of daily totals. I would like to have weekly total in a new column on another sheet without spaces between them, and want to be able to fill the weekly totals downwards, and have eac weekly total reflect a new set of 7 cells. So, say the first formula i the weekly total column is =SUM(B2:B8), and the second formula i =SUM(B9:B15). When I select these two cells, I want the next one t...

Re-enable account, no access to mailbox
Hello all, I need access to the mailbox of an account that was disabled. I enabled the account but cannot add the mailbox to any other user. I cannot even see the mailbox in Exchange Admin. I am running EX 5.5/Outlook 2000/Winnt 4.0. Any insight on this will be much appreciated. Ian is the mailbox hidden? you won't be able to access it, if so, unless you had configured a profile for the mailbox before it was hidden... "Ian B" <Ian B@discussions.microsoft.com> wrote in message news:75208EA5-D4C3-4DEC-8E88-9AAF764FDCBD@microsoft.com... > Hello all, > I need acc...

Re: MS Outlook XP (2002) Message Rules Problem(s)
frustrating problem. been tackling it for days. problem: when i create a rule, or more than one, to autorespond with a specifi template (regardless of whether the rules does other things or not, i it contains an autoresponse, this problem occurs) the FIRST incomin email that matches the criteria gets a response and NO others. why this is a problem: IF, for example, i had a autoR setup to send a pic of my God daughte to everyone that messages with the subject line: send me a pic of her ONLY the first person to send an email will get their email. also, IF i had another one setup w/ the cri...

Can RGB fill color be automated based on 3 cell values?
Is there any way to automatically adjust the fill color of a cell based on 3 values entered into 3 cells? Specifically, if I have entered the values 100, 120, and 50 into cells A1, A2, and A3, respectively, is there a way to automatically fill cell A4 with the RGB color Red=100, Green=120, Blue=50? Maybe: [A4].Interior.Color = Evaluate(RGB([A1], [A2], [A3])) Micky "Jaclyn" wrote: > Is there any way to automatically adjust the fill color of a cell based on 3 > values entered into 3 cells? > > Specifically, if I have entered the values 100, 120,...

Re: The Annoyance Of Having To Click Twice
OK. I tried it again with just /news -- as you recommended -- and it WORKS this time! EUREKA! Actually YOU found it. THANKS, Steve. Are you any relation to the famous writer? Nancy "Nancy Jackman" <nanwjackman@beaufort.com> wrote in message news:... > Catch 22! > > If you follow the steps below, with /newsonly -- WLM won't open in the > EMAIL view and you can't move to that screen in the program once you have > opened in newsgroups view! > > <G> > > So, yes, there are bugs in 64 bit WLM -- and "you ...

How Can We Determine Which Rollup We're Running?
I don't recall if we're running CRM 3 Rollup 1 or Rollup 2. How can we determine which rollup we're running? -- Brandon on CRM server add/remove program in control panel find the installtion of CRM server and under it you will find the Roullup1 /Rollup2. remember to check "show all updates". i believe one can also find it in crm registry . /Aamir ...

Zero Filling
What is the best way to increment the rows of a column, while maintaining zero filling Thanks in advance Hi not really sure what you're trying to do. Could you give an example? -- Regards Frank Kabel Frankfurt, Germany <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:0df401c4e45b$3f3dad20$a401280a@phx.gbl... > What is the best way to increment the rows of a column, > while maintaining zero filling > > Thanks in advance Want to # the rows as they came in to us, before working with the file. So if the 1st row is given the 'id' of 0001, ...

Access cluster from FE server
Another quick one, I have a 2 node A/P cluster running w2k3 enterprise - exchange 2003 sp2. I have set up the cluster, all resources are online, I have moved 5 users mailboxes to the cluster servers. They can access their mail via Outlook 2003, no trouble. They can access their mail via http @ http://mycluster.DomainA.com/exchange I can see and manage, storage groups, routing groups and mailboxes from any node on the cluster, from any server within the company. Connectivity is perfect, no errors anywhere. Users go to http://www.domainA.com/exchange (our FE server) and they can access their ma...

Gradients/fill effects in PUB 2002
I received a Pub 2000 file from a customer that had a gradient fill effect of a star. when opened in Pub 2002 it converts to a regular gradient and there are no features for shapes, only vert., horizontal., diamond, roound, etc. Is there any way to maintiain the original settings of the document and how so? Thanks.... Sean, Microsoft Publisher 2002 introduced a new file format, so some formatting can be lost. Have them convert the object to a graphic. Ask them to select the object, then go to Edit > Copy, Then go to Edit > Paste Special > As Picture. Then have them resave. ...

Fill in a field based on input of data in another field
Hi, I'd like to know if this can be done without VBA, since I'm not familiar with programming....Access 2007 My user has a table that contains countries: [tblAwardCountries]. When she enters a value in her [PermanentCountry] field, she would like the database to check to see if that country exists in [tblAwardCountries]. If it does exist, she would like another field to automatically update to "Eligible for Award". Can this be done without programming? I thought of the IN operator, but there are too many countries to have to type into the expression. Usi...

Re: Combine Name Fields
I have between 1 - 2 customers at the same address therefore I have the following fields first_name: last_name: second_customer_first_name: second_customer_last _name: How do I merge both firstnames together so they show up in one field I tried =[first_name]+" " +[second_customer_first_name] however if there is only 1 customer it comes in blank. Thanks Try this -- =[first_name] &" " & [second_customer_first_name] Or to get rid of any unused spaces if no second name use this -- =Trim([first_name] &" " & [second_cust...

Re: Strange Payday rule...
Hi Guys, Well, I know that Money 2005 is in full effect. I have been using MS Money Deluxe 2004 for about 6 months. I really love it! The only issue that still really bugs me is below. My wife still works for the School district and still gets paid on the 8th business day of each month. Does Money 2005 (any version) have the ability to set this up as a rule? If so I will prob buy, if not I will stick with 2004. :) Also, is there any other burning reasons to purchase 2005 (vice 2004)? I am just a regular user, that keeps track of debits (and use the payees, calendar, etc) and reconcile w...

Make a check box fill in a value?
Hi all, Is there a way in Excel to that when a user (on a protected document) checks or uncheks a Form Check Box to have that action fill in a value for a specific cell? Thanks for the help Bob I put a checkbox from the Forms toolbar on a worksheet. I pasted this into a General module: Option Explicit Sub testme() Dim myCell As Range Set myCell = ActiveSheet.Range("a1") If ActiveSheet.CheckBoxes(Application.Caller).Value = xlOn Then myCell.Value = "It's on" Else myCell.Value = "it's off" End If End Sub I r...

Re: Exchange 5.5 connector??
Oops...I meant "Specify by email domain"... "Susan" <susan@nospam.com> wrote in message news:... > you would have to configure the second IMS on another server...but you can > specify on the IMS where to send email for a specific domain...On the > "Connections" tab, click on "Specify by host"... > > "Jack Black" <JackBlack@hat.com> wrote in message > news:%23yYwaM38EHA.3820@TK2MSFTNGP11.phx.gbl... > > No, I didn't. Would that work? > > > > I just want to route SMTP traffic that goes to "...

Indexing
Hello: I´m using MNY2006 Portfolio and I´ld like to go some index from another countrys like China and son on? How Can I? ...

Re-receiving old e-mails
Have a machine at the office recieving old emails from 3-4 months ago repeatedly each day (in duplicates as well) wondering if anyone can help since the official microsoft support is being anal and not taking my product ID. ...

Re: Outlook spam
What do I need to do to be able to read selected spam messages in Outlook, MS Basic Edition 2003 On Mon, 26 Jan 2004 09:56:22 -0800, "nancy" <anonymous@discussions.microsoft.com> wrote: >What do I need to do to be able to read selected spam >messages in Outlook, MS Basic Edition 2003 Check in your folders for a folder named "Junk Mail" or something similar. If I remember correctly, that is where Outlook 2003 puts all suspected spam. -- Rich Cervenka Chicago, Illinois, USA Email Client: Microsoft Outlook 2002 News Client (Text): Fort´┐Ż Agent 1.93 www.forte...

Re-entering data into a Database
I have constructed a database and enter data three different times. For instance columns A-F are entered initially and conlumns G-Q are entered at later dates but must correspond with an existing inventory number. From using and index and nested matching funciton I have found the cells for which I need to enter the data such as "G7". However in writing my Macro I don't know how to place my cursor in this cell, since it changes with each entry, for instance the next entry may go in "G22", "P9", and so on. I am thinking it might require a loop starting ...

Re-implement CRM in New Server
Hi, Does anyone ever try to re-implement CRM in a new server by taking the data from the old one?? What are the things to consider if i want the new server will have the same url as the old one?? It will be a big help if you can give me a detail desription on how to perform it. Thanks Carrie Hi Carrie, The redeployment tools are made for this. There's a good description with the tool. Here you can get it: http://www.microsoft.com/downloads/details.aspx?FamilyID=BFCED393-61DB-49AF-9A50-4A90B311FA7D&displaylang=en Kind regards, -- Ronald Lemmen Avanade Netherlands "Ca...

Want websites to think you're running IE7 or IE8? Or running XP or Vista? Read on:
The following are registry entries that make websites think that you are running higher versions of IE or something other than 9x/me. Save the following three registry entries to their own .reg file, and then merge one of them into your registry as needed. The first one sets the default setting, IE6 running on win-9x/me. The second sets IE to make it appear as IE7 running on XP. The third is IE8 running on Vista. Don't forget to reverse the change, afterwards, by merging the default entry back to your registry, to remove the spoof, as some sites (like Windows Updates, fo...

Formatting not saved when file re-opened
I have a file that is not retaining the applied formatting when it is re-opened. I have columns formatted as dates and others formatted as dollars, also have cells highlighted and cells with top and bottom borders. The file is saved (as 2003 .xls) using Excel 2007. When the file is re-opened, the worksheet has no formatting. It is as though I imported a ..txt file. This file has been tested on two different machines with the same results. Please Help! Thanks in Advance. ...