SQLServerCentral / Discuss Content Posted by Steve Jones / Article Discussions / Article Discussions by Author / SQLServerCentral Runs sp_Blitz - Performance Part 1 / Latest Posts

RE: SQLServerCentral Runs sp_Blitz - Performance Part 1

Terry Grignon — Fri, 22 Mar 2013 06:27:26 GMT

thanks!really enjoying the articles, well done on the blitzing!

RE: SQLServerCentral Runs sp_Blitz - Performance Part 1

Steve Jones - SSC Editor — Thu, 21 Mar 2013 08:49:46 GMT

Sorry, this should be fixed.

RE: SQLServerCentral Runs sp_Blitz - Performance Part 1

Terry Grignon — Thu, 21 Mar 2013 07:01:40 GMT

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

RE: SQLServerCentral Runs sp_Blitz - Performance Part 1

Steve Jones - SSC Editor — Tue, 22 Jan 2013 08:28:13 GMT

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.

RE: SQLServerCentral Runs sp_Blitz - Performance Part 1

jasona.work — Tue, 22 Jan 2013 08:06:14 GMT

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

RE: SQLServerCentral Runs sp_Blitz - Performance Part 1

Brent Ozar — Tue, 22 Jan 2013 08:03:04 GMT

[quote][b]jasona.work (1/22/2013)[/b][hr]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?[/quote]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.

RE: SQLServerCentral Runs sp_Blitz - Performance Part 1

jasona.work — Tue, 22 Jan 2013 07:57:39 GMT

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:[code="sql"]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[/code]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

RE: SQLServerCentral Runs sp_Blitz - Performance Part 1

Brent Ozar — Tue, 22 Jan 2013 04:46:47 GMT

[quote][b]richardmgreen1 (1/22/2013)[/b][hr]I'm just woring through this and I've found a database where all the indexes have a fill factor of 0 (yup, zero).[/quote]Fill factor 0 is the same as 100. Kendra's written about it here:http://www.brentozar.com/blitzindex/sp_blitzindex-self-loathing-indexes/

RE: SQLServerCentral Runs sp_Blitz - Performance Part 1

dwain.c — Tue, 22 Jan 2013 04:23:32 GMT

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.

RE: SQLServerCentral Runs sp_Blitz - Performance Part 1

anthony.green — Tue, 22 Jan 2013 02:32:10 GMT

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.

RE: SQLServerCentral Runs sp_Blitz - Performance Part 1

richardmgreen1 — Tue, 22 Jan 2013 02:31:44 GMT

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

RE: SQLServerCentral Runs sp_Blitz - Performance Part 1

richardmgreen1 — Tue, 22 Jan 2013 02:26:24 GMT

Morning allI'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):-[code]fill_factor object_id name index_id type_desc0 8 NULL 0 HEAP0 466100701 NULL 0 HEAP0 469576711 NULL 0 HEAP0 498100815 NULL 0 HEAP0 514100872 NULL 0 HEAP0 517576882 NULL 0 HEAP0 530100929 NULL 0 HEAP0 546100986 NULL 0 HEAP0 562101043 NULL 0 HEAP0 578101100 NULL 0 HEAP0 594101157 NULL 0 HEAP0 610101214 NULL 0 HEAP0 626101271 NULL 0 HEAP0 642101328 NULL 0 HEAP0 658101385 NULL 0 HEAP0 674101442 NULL 0 HEAP0 690101499 NULL 0 HEAP0 706101556 NULL 0 HEAP0 722101613 NULL 0 HEAP0 738101670 NULL 0 HEAP0 754101727 NULL 0 HEAP0 770101784 NULL 0 HEAP0 786101841 NULL 0 HEAP0 802101898 NULL 0 HEAP0 818101955 NULL 0 HEAP0 834102012 NULL 0 HEAP0 850102069 NULL 0 HEAP0 866102126 NULL 0 HEAP0 882102183 NULL 0 HEAP0 898102240 NULL 0 HEAP0 914102297 NULL 0 HEAP0 930102354 NULL 0 HEAP0 946102411 NULL 0 HEAP0 962102468 NULL 0 HEAP0 978102525 NULL 0 HEAP0 994102582 NULL 0 HEAP0 1010102639 NULL 0 HEAP0 1026102696 NULL 0 HEAP0 1042102753 NULL 0 HEAP0 1058102810 NULL 0 HEAP0 1074102867 NULL 0 HEAP0 1090102924 NULL 0 HEAP0 1106102981 NULL 0 HEAP0 1138103095 NULL 0 HEAP0 1154103152 NULL 0 HEAP0 1170103209 NULL 0 HEAP0 1186103266 NULL 0 HEAP0 1202103323 NULL 0 HEAP0 1218103380 NULL 0 HEAP0 1234103437 NULL 0 HEAP0 1250103494 NULL 0 HEAP0 1266103551 NULL 0 HEAP0 1282103608 NULL 0 HEAP0 1314103722 NULL 0 HEAP0 1330103779 NULL 0 HEAP0 1474104292 NULL 0 HEAP0 1490104349 NULL 0 HEAP0 101575400 NULL 0 HEAP0 130099504 NULL 0 HEAP0 146099561 NULL 0 HEAP0 1733581214 NULL 0 HEAP0 1749581271 NULL 0 HEAP0 1765581328 NULL 0 HEAP0 1781581385 NULL 0 HEAP0 1797581442 NULL 0 HEAP0 1813581499 NULL 0 HEAP0 1829581556 NULL 0 HEAP0 1845581613 NULL 0 HEAP0 1861581670 NULL 0 HEAP0 1877581727 NULL 0 HEAP0 1893581784 NULL 0 HEAP0 1909581841 NULL 0 HEAP0 1925581898 NULL 0 HEAP0 1941581955 NULL 0 HEAP0 1957582012 NULL 0 HEAP0 1989582126 NULL 0 HEAP0 309576141 NULL 0 HEAP0 373576369 NULL 0 HEAP0 325576198 NULL 0 HEAP0 2021582240 NULL 0 HEAP0 2037582297 NULL 0 HEAP0 2057058364 queue_clustered_index 1 CLUSTERED0 2089058478 PK__sysdiagrams__7D78A4E7 1 CLUSTERED0 2025058250 queue_clustered_index 1 CLUSTERED0 341576255 PK_employees 1 CLUSTERED0 354100302 PK_activities_planned 1 CLUSTERED0 322100188 PK_absences_planned 1 CLUSTERED0 290100074 ci_commit_ts 1 CLUSTERED0 245575913 PK_activities_weekly_copy_new 1 CLUSTERED0 274100017 FSTSClusIdx 1 CLUSTERED0 277576027 PK_activities_worked_schedule 1 CLUSTERED0 1993058136 queue_clustered_index 1 CLUSTERED0 149575571 PK_absences_weekly_copy 1 CLUSTERED0 181575685 PK_absences_worked_schedule 1 CLUSTERED0 213575799 PK_activities 1 CLUSTERED0 117575457 PK_absences 1 CLUSTERED0 98 clst 1 CLUSTERED0 94 cl 1 CLUSTERED0 95 cl 1 CLUSTERED0 96 clst 1 CLUSTERED0 97 clst 1 CLUSTERED0 17 cl 1 CLUSTERED0 3 clst 1 CLUSTERED0 5 clust 1 CLUSTERED0 7 clust 1 CLUSTERED0 19 cl 1 CLUSTERED0 23 cl 1 CLUSTERED0 24 clst 1 CLUSTERED0 25 clst 1 CLUSTERED0 27 clst 1 CLUSTERED0 29 clust 1 CLUSTERED0 34 clst 1 CLUSTERED0 41 clst 1 CLUSTERED0 44 clst 1 CLUSTERED0 46 cl 1 CLUSTERED0 49 clust 1 CLUSTERED0 50 clst 1 CLUSTERED0 51 clst 1 CLUSTERED0 54 clst 1 CLUSTERED0 55 clst 1 CLUSTERED0 58 clst 1 CLUSTERED0 59 clust 1 CLUSTERED0 60 clst 1 CLUSTERED0 64 clst 1 CLUSTERED0 65 clust 1 CLUSTERED0 67 clst 1 CLUSTERED0 68 clst 1 CLUSTERED0 69 clst 1 CLUSTERED0 71 clst 1 CLUSTERED0 72 cl 1 CLUSTERED0 73 cl 1 CLUSTERED0 74 clst 1 CLUSTERED0 75 clst 1 CLUSTERED0 78 cl 1 CLUSTERED0 82 clst 1 CLUSTERED0 85 clst 1 CLUSTERED0 90 clst 1 CLUSTERED0 91 cl 1 CLUSTERED0 92 cl 1 CLUSTERED0 93 cl 1 CLUSTERED0 93 nc1 2 NONCLUSTERED0 91 nc1 2 NONCLUSTERED0 90 nc1 2 NONCLUSTERED0 78 nc 2 NONCLUSTERED0 75 nc1 2 NONCLUSTERED0 74 nc1 2 NONCLUSTERED0 69 nc1 2 NONCLUSTERED0 69 nc2 3 NONCLUSTERED0 67 nc1 2 NONCLUSTERED0 67 nc2 3 NONCLUSTERED0 64 nc 2 NONCLUSTERED0 58 nc1 2 NONCLUSTERED0 55 nc1 2 NONCLUSTERED0 54 nc 2 NONCLUSTERED0 51 nc 2 NONCLUSTERED0 50 nc1 2 NONCLUSTERED0 50 nc2 3 NONCLUSTERED0 46 nc1 2 NONCLUSTERED0 46 nc2 3 NONCLUSTERED0 46 nc3 4 NONCLUSTERED0 44 nc 2 NONCLUSTERED0 41 nc 2 NONCLUSTERED0 34 nc1 2 NONCLUSTERED0 34 nc2 3 NONCLUSTERED0 34 nc3 4 NONCLUSTERED0 27 nc1 2 NONCLUSTERED0 27 nc2 3 NONCLUSTERED0 7 nc 2 NONCLUSTERED0 17 nc 2 NONCLUSTERED0 17 nc2 3 NONCLUSTERED0 97 nc1 2 NONCLUSTERED0 96 nc1 2 NONCLUSTERED0 96 nc2 3 NONCLUSTERED0 95 nc1 2 NONCLUSTERED0 95 nc3 3 NONCLUSTERED0 98 nc1 2 NONCLUSTERED0 213575799 indact 4 NONCLUSTERED0 213575799 indemp 5 NONCLUSTERED0 1993058136 queue_secondary_index 2 NONCLUSTERED0 1490104349 aaaaaNEWIRT_PK 2 NONCLUSTERED0 277576027 indact 4 NONCLUSTERED0 277576027 indemp 5 NONCLUSTERED0 274100017 FSTSNCIdx 2 NONCLUSTERED0 290100074 si_xdes_id 2 NONCLUSTERED0 2025058250 queue_secondary_index 2 NONCLUSTERED0 373576369 icasidind 11 NONCLUSTERED0 373576369 ifullnameind 12 NONCLUSTERED0 373576369 itel1ind 13 NONCLUSTERED0 373576369 itel2ind 14 NONCLUSTERED0 373576369 visidind 15 NONCLUSTERED0 373576369 siteind 20 NONCLUSTERED0 2089058478 UK_principal_name 2 NONCLUSTERED0 2057058364 queue_secondary_index 2 NONCLUSTERED0 2037582297 visidind 23 NONCLUSTERED[/code]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.

SQLServerCentral Runs sp_Blitz - Performance Part 1

Steve Jones - SSC Editor — Tue, 22 Jan 2013 00:03:37 GMT

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