Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQLServerCentral Runs sp_Blitz - Performance Part 1


SQLServerCentral Runs sp_Blitz - Performance Part 1

Author
Message
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: Administrators
Points: 40965 Visits: 18865
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
My Blog: www.voiceofthedba.com
richardmgreen1
richardmgreen1
SSC Eights!
SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)

Group: General Forum Members
Points: 813 Visits: 918
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.
richardmgreen1
richardmgreen1
SSC Eights!
SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)

Group: General Forum Members
Points: 813 Visits: 918
Please ignore my last post. Missed a bit when checking the fill factor.
anthony.green
anthony.green
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6989 Visits: 6145
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
When a question, really isn't a question - Jeff Smith
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


dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4917 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Brent Ozar
Brent Ozar
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 494
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/



jasona.work
jasona.work
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2321 Visits: 11204
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
Brent Ozar
Brent Ozar
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 494
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.



jasona.work
jasona.work
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2321 Visits: 11204
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
Steve Jones
Steve Jones
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: Administrators
Points: 40965 Visits: 18865
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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search