Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQLServerCentral Runs sp_Blitz - Performance Part 1 Expand / Collapse
Author
Message
Posted Tuesday, January 22, 2013 12:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 7:12 AM
Points: 31,284, Visits: 15,746
Comments posted to this topic are about the item SQLServerCentral Runs sp_Blitz - Performance Part 1






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1409809
Posted Tuesday, January 22, 2013 2:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

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

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.
Post #1409872
Posted Tuesday, January 22, 2013 2:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 3:29 AM
Points: 367, Visits: 445
Please ignore my last post. Missed a bit when checking the fill factor.
Post #1409875
Posted Tuesday, January 22, 2013 2:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:10 AM
Points: 5,221, Visits: 5,119
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.





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1409876
Posted Tuesday, January 22, 2013 4:23 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:53 AM
Points: 3,422, Visits: 5,368
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1409922
Posted Tuesday, January 22, 2013 4:46 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 21, 2014 10:09 AM
Points: 68, Visits: 428
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/



Post #1409944
Posted Tuesday, January 22, 2013 7:57 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:23 PM
Points: 737, Visits: 5,457
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
Post #1410046
Posted Tuesday, January 22, 2013 8:03 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 21, 2014 10:09 AM
Points: 68, Visits: 428
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.



Post #1410050
Posted Tuesday, January 22, 2013 8:06 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:23 PM
Points: 737, Visits: 5,457
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
Post #1410053
Posted Tuesday, January 22, 2013 8:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 7:12 AM
Points: 31,284, Visits: 15,746
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.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1410064
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse