Post reply

SQLServerCentral Runs sp_Blitz - Performance Part 1

  • January 22, 2013 at 12:03 am

    #75670

    Comments posted to this topic are about the item SQLServerCentral Runs sp_Blitz - Performance Part 1

  • January 22, 2013 at 2:26 am

    #1579492

    Morning all

    I'm just woring through this and I've found a database where all the indexes have a fill factor of 0 (yup, zero).

    Here's the output from the fill-factor query in the article (adjusted to collect all indexes):-

    fill_factorobject_idnameindex_idtype_desc

    08NULL0HEAP

    0466100701NULL0HEAP

    0469576711NULL0HEAP

    0498100815NULL0HEAP

    0514100872NULL0HEAP

    0517576882NULL0HEAP

    0530100929NULL0HEAP

    0546100986NULL0HEAP

    0562101043NULL0HEAP

    0578101100NULL0HEAP

    0594101157NULL0HEAP

    0610101214NULL0HEAP

    0626101271NULL0HEAP

    0642101328NULL0HEAP

    0658101385NULL0HEAP

    0674101442NULL0HEAP

    0690101499NULL0HEAP

    0706101556NULL0HEAP

    0722101613NULL0HEAP

    0738101670NULL0HEAP

    0754101727NULL0HEAP

    0770101784NULL0HEAP

    0786101841NULL0HEAP

    0802101898NULL0HEAP

    0818101955NULL0HEAP

    0834102012NULL0HEAP

    0850102069NULL0HEAP

    0866102126NULL0HEAP

    0882102183NULL0HEAP

    0898102240NULL0HEAP

    0914102297NULL0HEAP

    0930102354NULL0HEAP

    0946102411NULL0HEAP

    0962102468NULL0HEAP

    0978102525NULL0HEAP

    0994102582NULL0HEAP

    01010102639NULL0HEAP

    01026102696NULL0HEAP

    01042102753NULL0HEAP

    01058102810NULL0HEAP

    01074102867NULL0HEAP

    01090102924NULL0HEAP

    01106102981NULL0HEAP

    01138103095NULL0HEAP

    01154103152NULL0HEAP

    01170103209NULL0HEAP

    01186103266NULL0HEAP

    01202103323NULL0HEAP

    01218103380NULL0HEAP

    01234103437NULL0HEAP

    01250103494NULL0HEAP

    01266103551NULL0HEAP

    01282103608NULL0HEAP

    01314103722NULL0HEAP

    01330103779NULL0HEAP

    01474104292NULL0HEAP

    01490104349NULL0HEAP

    0101575400NULL0HEAP

    0130099504NULL0HEAP

    0146099561NULL0HEAP

    01733581214NULL0HEAP

    01749581271NULL0HEAP

    01765581328NULL0HEAP

    01781581385NULL0HEAP

    01797581442NULL0HEAP

    01813581499NULL0HEAP

    01829581556NULL0HEAP

    01845581613NULL0HEAP

    01861581670NULL0HEAP

    01877581727NULL0HEAP

    01893581784NULL0HEAP

    01909581841NULL0HEAP

    01925581898NULL0HEAP

    01941581955NULL0HEAP

    01957582012NULL0HEAP

    01989582126NULL0HEAP

    0309576141NULL0HEAP

    0373576369NULL0HEAP

    0325576198NULL0HEAP

    02021582240NULL0HEAP

    02037582297NULL0HEAP

    02057058364queue_clustered_index1CLUSTERED

    02089058478PK__sysdiagrams__7D78A4E71CLUSTERED

    02025058250queue_clustered_index1CLUSTERED

    0341576255PK_employees1CLUSTERED

    0354100302PK_activities_planned1CLUSTERED

    0322100188PK_absences_planned1CLUSTERED

    0290100074ci_commit_ts1CLUSTERED

    0245575913PK_activities_weekly_copy_new1CLUSTERED

    0274100017FSTSClusIdx1CLUSTERED

    0277576027PK_activities_worked_schedule1CLUSTERED

    01993058136queue_clustered_index1CLUSTERED

    0149575571PK_absences_weekly_copy1CLUSTERED

    0181575685PK_absences_worked_schedule1CLUSTERED

    0213575799PK_activities1CLUSTERED

    0117575457PK_absences1CLUSTERED

    098clst1CLUSTERED

    094cl1CLUSTERED

    095cl1CLUSTERED

    096clst1CLUSTERED

    097clst1CLUSTERED

    017cl1CLUSTERED

    03clst1CLUSTERED

    05clust1CLUSTERED

    07clust1CLUSTERED

    019cl1CLUSTERED

    023cl1CLUSTERED

    024clst1CLUSTERED

    025clst1CLUSTERED

    027clst1CLUSTERED

    029clust1CLUSTERED

    034clst1CLUSTERED

    041clst1CLUSTERED

    044clst1CLUSTERED

    046cl1CLUSTERED

    049clust1CLUSTERED

    050clst1CLUSTERED

    051clst1CLUSTERED

    054clst1CLUSTERED

    055clst1CLUSTERED

    058clst1CLUSTERED

    059clust1CLUSTERED

    060clst1CLUSTERED

    064clst1CLUSTERED

    065clust1CLUSTERED

    067clst1CLUSTERED

    068clst1CLUSTERED

    069clst1CLUSTERED

    071clst1CLUSTERED

    072cl1CLUSTERED

    073cl1CLUSTERED

    074clst1CLUSTERED

    075clst1CLUSTERED

    078cl1CLUSTERED

    082clst1CLUSTERED

    085clst1CLUSTERED

    090clst1CLUSTERED

    091cl1CLUSTERED

    092cl1CLUSTERED

    093cl1CLUSTERED

    093nc12NONCLUSTERED

    091nc12NONCLUSTERED

    090nc12NONCLUSTERED

    078nc2NONCLUSTERED

    075nc12NONCLUSTERED

    074nc12NONCLUSTERED

    069nc12NONCLUSTERED

    069nc23NONCLUSTERED

    067nc12NONCLUSTERED

    067nc23NONCLUSTERED

    064nc2NONCLUSTERED

    058nc12NONCLUSTERED

    055nc12NONCLUSTERED

    054nc2NONCLUSTERED

    051nc2NONCLUSTERED

    050nc12NONCLUSTERED

    050nc23NONCLUSTERED

    046nc12NONCLUSTERED

    046nc23NONCLUSTERED

    046nc34NONCLUSTERED

    044nc2NONCLUSTERED

    041nc2NONCLUSTERED

    034nc12NONCLUSTERED

    034nc23NONCLUSTERED

    034nc34NONCLUSTERED

    027nc12NONCLUSTERED

    027nc23NONCLUSTERED

    07nc2NONCLUSTERED

    017nc2NONCLUSTERED

    017nc23NONCLUSTERED

    097nc12NONCLUSTERED

    096nc12NONCLUSTERED

    096nc23NONCLUSTERED

    095nc12NONCLUSTERED

    095nc33NONCLUSTERED

    098nc12NONCLUSTERED

    0213575799indact4NONCLUSTERED

    0213575799indemp5NONCLUSTERED

    01993058136queue_secondary_index2NONCLUSTERED

    01490104349aaaaaNEWIRT_PK2NONCLUSTERED

    0277576027indact4NONCLUSTERED

    0277576027indemp5NONCLUSTERED

    0274100017FSTSNCIdx2NONCLUSTERED

    0290100074si_xdes_id2NONCLUSTERED

    02025058250queue_secondary_index2NONCLUSTERED

    0373576369icasidind11NONCLUSTERED

    0373576369ifullnameind12NONCLUSTERED

    0373576369itel1ind13NONCLUSTERED

    0373576369itel2ind14NONCLUSTERED

    0373576369visidind15NONCLUSTERED

    0373576369siteind20NONCLUSTERED

    02089058478UK_principal_name2NONCLUSTERED

    02057058364queue_secondary_index2NONCLUSTERED

    02037582297visidind23NONCLUSTERED

    I'm seriously changing some of these to around the 90 mark (we update the tables daily) but I'm unsure of the impact on performance and space.

    Can someone have a quick look (apologies for the fomatting) and advise please?

    I'm assuming that adjusting the fill factor will reduce the amount of space needed for the index and data but I'd like confirmation before I do anything.

  • January 22, 2013 at 2:31 am

    #1579495

    Please ignore my last post. Missed a bit when checking the fill factor.

  • January 22, 2013 at 2:32 am

    #1579496

    Setting a fill factor will increase the amount of space needed for the index, how much extra space depends on the fill factor being used and the index definitions.

  • January 22, 2013 at 4:23 am

    #1579536

    I might actually have a chance to get on this horse and ride it in the next few days.

    Thanks for the heads up on this thing Steve!

    I'll let y'all know if I find anything interesting.

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • January 22, 2013 at 4:46 am

    #1579556

    richardmgreen1 (1/22/2013)

    I'm just woring through this and I've found a database where all the indexes have a fill factor of 0 (yup, zero).

    Fill factor 0 is the same as 100. Kendra's written about it here:

    http://www.brentozar.com/blitzindex/sp_blitzindex-self-loathing-indexes/

  • January 22, 2013 at 7:57 am

    #1579646

    OK, so I'd like to confirm something, and then toss out a variation on Steve's fill factor script.

    Fill factor 0 = 100% full on each page (no free space) so adding ANY new rows to the table will cause a page split, correct?

    As for the script, some of us may not know which index names go where, so it was easy enough to join up the results from Steve's script to sys.objects:

    select

    SI.fill_factor

    , SI.object_id

    , SI.name as 'Index Name'

    , so.name as 'Table Name'

    , SI.index_id

    , SI.type_desc as 'Index Type'

    from sys.indexes as SI

    inner join sys.objects as SO

    on SO.object_id = SI.object_id

    where SI.type_desc <> 'HEAP'

    and SO.type_desc <> 'SYSTEM_TABLE'

    and fill_factor > 0

    order by SO.name

    As you can see, I set it to leave out Heaps and System tables from the results.

    Comments?

    Boneheaded, I should know all my indexes by name? 😉

    Jason

  • January 22, 2013 at 8:03 am

    #1579649

    jasona.work (1/22/2013)

    Fill factor 0 = 100% full on each page (no free space) so adding ANY new rows to the table will cause a page split, correct?

    Not exactly. SQL Server can't realistically cram the pages 100% full because your records don't add up to exactly the page size. For example, say there's 8060 bytes available per page for data, but your average row size is 1300 bytes. That means around 6 rows per page, which is 7800 bytes. On any given page, you'll have some space to play with there for updates.

    Now for inserts, think about the first field in each index. Sometimes it's an identity field, and inserts will always happen at the end. Sometimes it's a sales date, and same thing there - inserts will hit at the end. However, sometimes you've got a phone book type index where it's based on last name, first name. As you add new customers, you're going to add them throughout the index. You could theoretically lower fill factor there, but by lowering it throughout the index, you're making the object size larger and using more memory to cache the same data. Make sure you're willing to sacrifice RAM in exchange for less page splits - often I see people monkeying with that number, and the cure is worse than the disease.

  • January 22, 2013 at 8:06 am

    #1579652

    Cool, gotcha!

    So seeing as the DB / tables in our app all have identity columns (Int, increasing) inserts will happen at the end, so there's not really a driving reason (for now) to have a lower fill factor. (or, the default answer: "it depends")

    Thanks Brent!

    Jason

  • January 22, 2013 at 8:28 am

    #1579661

    Great explanation from Brent and sorry for the delay in responding. I wouldn't change fill factor without looking at my RAM (target v actual) and the page splits. I'd have to make that decision based on ensuring that the page splits are more of a problem than the space I'd eat up in RAM and on disk.

  • March 21, 2013 at 7:01 am

    #1599359

    The links to the Security and the Reliability columns are the same so I can't get to the Reliability article.

  • March 21, 2013 at 8:49 am

    #1599420

    Sorry, this should be fixed.

  • March 22, 2013 at 6:27 am

    #1599741

    thanks!

    really enjoying the articles, well done on the blitzing!

  • September 3, 2014 at 5:56 am

    #1742743

    I see no paragraph about "High VLF Count". Was it missed by you, Steve?

    And there is a small "bug" in the table with new Autogrowth values - for msdb we see "50MB log, 20MB log" - two times "log".

    Regards,

    AS

  • September 3, 2014 at 8:07 am

    #1742802

    arkadiusz.stasiak (9/3/2014)

    I see no paragraph about "High VLF Count". Was it missed by you, Steve?

    And there is a small "bug" in the table with new Autogrowth values - for msdb we see "50MB log, 20MB log" - two times "log".

    Regards,

    AS

    The VLFs are in part 2.

    Thanks for the catch. Corrected.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply