SQLServerCentral Runs sp_Blitz - Performance Part 2

Question

SQLServerCentral Runs sp_Blitz - Performance Part 2

Steve Jones - SSC Editor

SSC Guru

Points: 736172
More actions
March 21, 2013 at 12:01 am

#74172

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

Viewing 5 posts - 1 through 4 (of 4 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Steve, I wonder if you don't have too many tempdb files. I have lost track of the number of clients I have come across that cause IO performance problems by having too many files (of any SQL Server type) on too few spindles. Causes head thrashing and you get killed by the disk access/seek times. Unless you have demonstrated tempdb object allocation issues or have a boatload of spindles (or SSDs) you may get better IO with fewer files.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 2

Good point. Our IT staff used the best advice at the time, which was 1 per core. It's possible that we should reduce down to 4 files.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

Nice article and some good links to useful videos

http://technet.microsoft.com/en-us/sqlserver/ff977043

quackhandle1975 SSChampion Points: 11055 More actions · Answer 4

Once again great article and great tool.

I am still trying to work out how to get Blitz to count the number of instances on one server. :ermm:

Cheers

qh

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]